]>
Commit | Line | Data |
---|---|---|
14890de3 | 1 | <?php |
2 | /** | |
c95b78a8 | 3 | * wallabag, self hostable application allowing you to not miss any content anymore |
14890de3 | 4 | * |
c95b78a8 NL |
5 | * @category wallabag |
6 | * @author Nicolas Lœuillet <nicolas@loeuillet.org> | |
14890de3 | 7 | * @copyright 2013 |
8 | * @license http://www.wtfpl.net/ see COPYING file | |
9 | */ | |
10 | ||
bc1ee852 | 11 | class Database { |
14890de3 | 12 | var $handle; |
13 | ||
68857cea NL |
14 | function __construct() |
15 | { | |
16 | switch (STORAGE) { | |
17 | case 'sqlite': | |
18 | $db_path = 'sqlite:' . STORAGE_SQLITE; | |
19 | $this->handle = new PDO($db_path); | |
20 | break; | |
21 | case 'mysql': | |
22 | $db_path = 'mysql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB; | |
23 | $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD); | |
24 | break; | |
25 | case 'postgres': | |
26 | $db_path = 'pgsql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB; | |
27 | $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD); | |
28 | break; | |
29 | } | |
30 | ||
14890de3 | 31 | $this->handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); |
580d60b9 | 32 | Tools::logm('storage type ' . STORAGE); |
14890de3 | 33 | } |
34 | ||
35 | private function getHandle() { | |
36 | return $this->handle; | |
37 | } | |
38 | ||
aa8c9f2a | 39 | public function isInstalled() { |
b916bcfc NL |
40 | $sql = "SELECT username FROM users"; |
41 | $query = $this->executeQuery($sql, array()); | |
5cfafc61 NL |
42 | if ($query == false) { |
43 | die(STORAGE . ' database looks empty. You have to create it (you can find database structure in install folder).'); | |
44 | } | |
b916bcfc | 45 | $hasAdmin = count($query->fetchAll()); |
aa8c9f2a | 46 | |
b916bcfc | 47 | if ($hasAdmin == 0) |
5cfafc61 NL |
48 | return false; |
49 | ||
50 | return true; | |
51 | } | |
52 | ||
53 | public function checkTags() { | |
54 | ||
55 | if (STORAGE == 'sqlite') { | |
56 | $sql = ' | |
57 | CREATE TABLE IF NOT EXISTS tags ( | |
58 | id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, | |
59 | value TEXT | |
60 | )'; | |
61 | } | |
62 | elseif(STORAGE == 'mysql') { | |
63 | $sql = ' | |
64 | CREATE TABLE IF NOT EXISTS `tags` ( | |
65 | `id` int(11) NOT NULL AUTO_INCREMENT, | |
66 | `value` varchar(255) NOT NULL, | |
67 | PRIMARY KEY (`id`) | |
68 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; | |
69 | '; | |
70 | } | |
71 | else { | |
72 | $sql = ' | |
73 | CREATE TABLE tags ( | |
74 | id bigserial primary key, | |
75 | value varchar(255) NOT NULL | |
76 | ); | |
77 | '; | |
78 | } | |
aa8c9f2a | 79 | |
5cfafc61 NL |
80 | $query = $this->executeQuery($sql, array()); |
81 | ||
82 | if (STORAGE == 'sqlite') { | |
83 | $sql = ' | |
a562e390 | 84 | CREATE TABLE IF NOT EXISTS tags_entries ( |
5cfafc61 NL |
85 | id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, |
86 | entry_id INTEGER, | |
87 | tag_id INTEGER, | |
88 | FOREIGN KEY(entry_id) REFERENCES entries(id) ON DELETE CASCADE, | |
89 | FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE | |
90 | )'; | |
91 | } | |
92 | elseif(STORAGE == 'mysql') { | |
93 | $sql = ' | |
94 | CREATE TABLE IF NOT EXISTS `tags_entries` ( | |
95 | `id` int(11) NOT NULL AUTO_INCREMENT, | |
96 | `entry_id` int(11) NOT NULL, | |
97 | `tag_id` int(11) NOT NULL, | |
98 | FOREIGN KEY(entry_id) REFERENCES entries(id) ON DELETE CASCADE, | |
99 | FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE, | |
100 | PRIMARY KEY (`id`) | |
101 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; | |
102 | '; | |
103 | } | |
104 | else { | |
105 | $sql = ' | |
106 | CREATE TABLE tags_entries ( | |
107 | id bigserial primary key, | |
108 | entry_id integer NOT NULL, | |
109 | tag_id integer NOT NULL | |
110 | ) | |
111 | '; | |
112 | } | |
113 | ||
114 | $query = $this->executeQuery($sql, array()); | |
aa8c9f2a NL |
115 | } |
116 | ||
117 | public function install($login, $password) { | |
b916bcfc NL |
118 | $sql = 'INSERT INTO users ( username, password, name, email) VALUES (?, ?, ?, ?)'; |
119 | $params = array($login, $password, $login, ' '); | |
120 | $query = $this->executeQuery($sql, $params); | |
121 | ||
122 | $sequence = ''; | |
123 | if (STORAGE == 'postgres') { | |
124 | $sequence = 'users_id_seq'; | |
125 | } | |
126 | ||
127 | $id_user = intval($this->getLastId($sequence)); | |
128 | ||
129 | $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; | |
00dbaf90 | 130 | $params = array($id_user, 'pager', PAGINATION); |
b916bcfc NL |
131 | $query = $this->executeQuery($sql, $params); |
132 | ||
133 | $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; | |
00dbaf90 NL |
134 | $params = array($id_user, 'language', LANG); |
135 | $query = $this->executeQuery($sql, $params); | |
136 | ||
137 | $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; | |
138 | $params = array($id_user, 'theme', DEFAULT_THEME); | |
7ce7ec4c | 139 | $query = $this->executeQuery($sql, $params); |
aa8c9f2a | 140 | |
7ce7ec4c NL |
141 | return TRUE; |
142 | } | |
aa8c9f2a | 143 | |
5846b0f1 | 144 | public function getConfigUser($id) { |
7ce7ec4c NL |
145 | $sql = "SELECT * FROM users_config WHERE user_id = ?"; |
146 | $query = $this->executeQuery($sql, array($id)); | |
147 | $result = $query->fetchAll(); | |
148 | $user_config = array(); | |
149 | ||
150 | foreach ($result as $key => $value) { | |
151 | $user_config[$value['name']] = $value['value']; | |
aa8c9f2a NL |
152 | } |
153 | ||
7ce7ec4c | 154 | return $user_config; |
aa8c9f2a NL |
155 | } |
156 | ||
027b4e15 DS |
157 | public function userExists($username) { |
158 | $sql = "SELECT * FROM users WHERE username=?"; | |
159 | $query = $this->executeQuery($sql, array($username)); | |
160 | $login = $query->fetchAll(); | |
161 | if (isset($login[0])) { | |
162 | return true; | |
163 | } else { | |
164 | return false; | |
165 | } | |
166 | } | |
167 | ||
6af66b11 MR |
168 | public function login($username, $password, $isauthenticated=false) { |
169 | if ($isauthenticated) { | |
170 | $sql = "SELECT * FROM users WHERE username=?"; | |
171 | $query = $this->executeQuery($sql, array($username)); | |
172 | } else { | |
173 | $sql = "SELECT * FROM users WHERE username=? AND password=?"; | |
174 | $query = $this->executeQuery($sql, array($username, $password)); | |
175 | } | |
8d3275be | 176 | $login = $query->fetchAll(); |
aa8c9f2a | 177 | |
7ce7ec4c NL |
178 | $user = array(); |
179 | if (isset($login[0])) { | |
180 | $user['id'] = $login[0]['id']; | |
181 | $user['username'] = $login[0]['username']; | |
182 | $user['password'] = $login[0]['password']; | |
183 | $user['name'] = $login[0]['name']; | |
184 | $user['email'] = $login[0]['email']; | |
185 | $user['config'] = $this->getConfigUser($login[0]['id']); | |
186 | } | |
aa8c9f2a | 187 | |
7ce7ec4c | 188 | return $user; |
aa8c9f2a NL |
189 | } |
190 | ||
00dbaf90 | 191 | public function updatePassword($userId, $password) |
6499b26a | 192 | { |
8d3275be | 193 | $sql_update = "UPDATE users SET password=? WHERE id=?"; |
1e98ee1d NL |
194 | $params_update = array($password, $userId); |
195 | $query = $this->executeQuery($sql_update, $params_update); | |
00dbaf90 NL |
196 | } |
197 | ||
198 | public function updateUserConfig($userId, $key, $value) { | |
58ace494 NL |
199 | $config = $this->getConfigUser($userId); |
200 | ||
d1d3498b | 201 | if (! isset($config[$key])) { |
da5fc42f | 202 | $sql = "INSERT INTO users_config (value, user_id, name) VALUES (?, ?, ?)"; |
58ace494 NL |
203 | } |
204 | else { | |
da5fc42f | 205 | $sql = "UPDATE users_config SET value=? WHERE user_id=? AND name=?"; |
58ace494 NL |
206 | } |
207 | ||
208 | $params = array($value, $userId, $key); | |
209 | $query = $this->executeQuery($sql, $params); | |
6499b26a NL |
210 | } |
211 | ||
14890de3 | 212 | private function executeQuery($sql, $params) { |
213 | try | |
214 | { | |
215 | $query = $this->getHandle()->prepare($sql); | |
216 | $query->execute($params); | |
217 | return $query; | |
218 | } | |
219 | catch (Exception $e) | |
220 | { | |
eb1af592 | 221 | Tools::logm('execute query error : '.$e->getMessage()); |
bc1ee852 | 222 | return FALSE; |
14890de3 | 223 | } |
224 | } | |
225 | ||
8d3275be NL |
226 | public function retrieveAll($user_id) { |
227 | $sql = "SELECT * FROM entries WHERE user_id=? ORDER BY id"; | |
228 | $query = $this->executeQuery($sql, array($user_id)); | |
44e77bfa | 229 | $entries = $query->fetchAll(); |
230 | ||
231 | return $entries; | |
232 | } | |
233 | ||
8d3275be | 234 | public function retrieveOneById($id, $user_id) { |
14890de3 | 235 | $entry = NULL; |
8d3275be NL |
236 | $sql = "SELECT * FROM entries WHERE id=? AND user_id=?"; |
237 | $params = array(intval($id), $user_id); | |
14890de3 | 238 | $query = $this->executeQuery($sql, $params); |
239 | $entry = $query->fetchAll(); | |
240 | ||
0c2f4537 | 241 | return isset($entry[0]) ? $entry[0] : null; |
14890de3 | 242 | } |
243 | ||
8d3275be | 244 | public function getEntriesByView($view, $user_id, $limit = '') { |
14890de3 | 245 | switch ($_SESSION['sort']) |
246 | { | |
247 | case 'ia': | |
248 | $order = 'ORDER BY id'; | |
249 | break; | |
250 | case 'id': | |
251 | $order = 'ORDER BY id DESC'; | |
252 | break; | |
253 | case 'ta': | |
254 | $order = 'ORDER BY lower(title)'; | |
255 | break; | |
256 | case 'td': | |
257 | $order = 'ORDER BY lower(title) DESC'; | |
258 | break; | |
259 | default: | |
260 | $order = 'ORDER BY id'; | |
261 | break; | |
262 | } | |
263 | ||
264 | switch ($view) | |
265 | { | |
266 | case 'archive': | |
8d3275be | 267 | $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? " . $order; |
bc1ee852 | 268 | $params = array($user_id, 1); |
14890de3 | 269 | break; |
270 | case 'fav' : | |
8d3275be | 271 | $sql = "SELECT * FROM entries WHERE user_id=? AND is_fav=? " . $order; |
bc1ee852 | 272 | $params = array($user_id, 1); |
14890de3 | 273 | break; |
274 | default: | |
8d3275be NL |
275 | $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? " . $order; |
276 | $params = array($user_id, 0); | |
14890de3 | 277 | break; |
278 | } | |
279 | ||
6a361945 NL |
280 | $sql .= ' ' . $limit; |
281 | ||
8d3275be NL |
282 | $query = $this->executeQuery($sql, $params); |
283 | $entries = $query->fetchAll(); | |
14890de3 | 284 | |
285 | return $entries; | |
286 | } | |
287 | ||
b58e261d NL |
288 | public function updateContent($id, $content, $user_id) { |
289 | $sql_action = 'UPDATE entries SET content = ? WHERE id=? AND user_id=?'; | |
290 | $params_action = array($content, $id, $user_id); | |
291 | $query = $this->executeQuery($sql_action, $params_action); | |
292 | return $query; | |
293 | } | |
294 | ||
8d3275be | 295 | public function add($url, $title, $content, $user_id) { |
8d3275be NL |
296 | $sql_action = 'INSERT INTO entries ( url, title, content, user_id ) VALUES (?, ?, ?, ?)'; |
297 | $params_action = array($url, $title, $content, $user_id); | |
298 | $query = $this->executeQuery($sql_action, $params_action); | |
29c6fd46 | 299 | return $query; |
14890de3 | 300 | } |
301 | ||
8d3275be | 302 | public function deleteById($id, $user_id) { |
8d3275be NL |
303 | $sql_action = "DELETE FROM entries WHERE id=? AND user_id=?"; |
304 | $params_action = array($id, $user_id); | |
14890de3 | 305 | $query = $this->executeQuery($sql_action, $params_action); |
29c6fd46 | 306 | return $query; |
14890de3 | 307 | } |
308 | ||
8d3275be | 309 | public function favoriteById($id, $user_id) { |
bc1ee852 | 310 | $sql_action = "UPDATE entries SET is_fav=NOT is_fav WHERE id=? AND user_id=?"; |
8d3275be | 311 | $params_action = array($id, $user_id); |
14890de3 | 312 | $query = $this->executeQuery($sql_action, $params_action); |
313 | } | |
314 | ||
8d3275be | 315 | public function archiveById($id, $user_id) { |
bc1ee852 | 316 | $sql_action = "UPDATE entries SET is_read=NOT is_read WHERE id=? AND user_id=?"; |
8d3275be | 317 | $params_action = array($id, $user_id); |
14890de3 | 318 | $query = $this->executeQuery($sql_action, $params_action); |
319 | } | |
320 | ||
f14807de NL |
321 | public function archiveAll($user_id) { |
322 | $sql_action = "UPDATE entries SET is_read=? WHERE user_id=? AND is_read=?"; | |
323 | $params_action = array($user_id, 1, 0); | |
324 | $query = $this->executeQuery($sql_action, $params_action); | |
325 | } | |
326 | ||
b916bcfc NL |
327 | public function getLastId($column = '') { |
328 | return $this->getHandle()->lastInsertId($column); | |
14890de3 | 329 | } |
5bea1a4d | 330 | |
2e2ebe5e NL |
331 | public function retrieveAllTags() { |
332 | $sql = "SELECT * FROM tags"; | |
333 | $query = $this->executeQuery($sql, array()); | |
334 | $tags = $query->fetchAll(); | |
335 | ||
336 | return $tags; | |
337 | } | |
338 | ||
4886ed6d NL |
339 | public function retrieveTag($id) { |
340 | $tag = NULL; | |
341 | $sql = "SELECT * FROM tags WHERE id=?"; | |
342 | $params = array(intval($id)); | |
343 | $query = $this->executeQuery($sql, $params); | |
344 | $tag = $query->fetchAll(); | |
345 | ||
346 | return isset($tag[0]) ? $tag[0] : null; | |
347 | } | |
348 | ||
349 | public function retrieveEntriesByTag($tag_id) { | |
350 | $sql = | |
9de34d4e | 351 | "SELECT entries.* FROM entries |
4886ed6d NL |
352 | LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id |
353 | WHERE tags_entries.tag_id = ?"; | |
354 | $query = $this->executeQuery($sql, array($tag_id)); | |
355 | $entries = $query->fetchAll(); | |
356 | ||
357 | return $entries; | |
358 | } | |
359 | ||
5bea1a4d | 360 | public function retrieveTagsByEntry($entry_id) { |
2e2ebe5e | 361 | $sql = |
9de34d4e | 362 | "SELECT tags.* FROM tags |
5bea1a4d NL |
363 | LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id |
364 | WHERE tags_entries.entry_id = ?"; | |
2e2ebe5e NL |
365 | $query = $this->executeQuery($sql, array($entry_id)); |
366 | $tags = $query->fetchAll(); | |
5bea1a4d NL |
367 | |
368 | return $tags; | |
369 | } | |
c432fa16 NL |
370 | |
371 | public function removeTagForEntry($entry_id, $tag_id) { | |
372 | $sql_action = "DELETE FROM tags_entries WHERE tag_id=? AND entry_id=?"; | |
373 | $params_action = array($tag_id, $entry_id); | |
374 | $query = $this->executeQuery($sql_action, $params_action); | |
375 | return $query; | |
376 | } | |
377 | ||
378 | public function retrieveTagByValue($value) { | |
379 | $tag = NULL; | |
380 | $sql = "SELECT * FROM tags WHERE value=?"; | |
381 | $params = array($value); | |
382 | $query = $this->executeQuery($sql, $params); | |
383 | $tag = $query->fetchAll(); | |
384 | ||
385 | return isset($tag[0]) ? $tag[0] : null; | |
386 | } | |
387 | ||
388 | public function createTag($value) { | |
389 | $sql_action = 'INSERT INTO tags ( value ) VALUES (?)'; | |
390 | $params_action = array($value); | |
391 | $query = $this->executeQuery($sql_action, $params_action); | |
392 | return $query; | |
393 | } | |
394 | ||
395 | public function setTagToEntry($tag_id, $entry_id) { | |
396 | $sql_action = 'INSERT INTO tags_entries ( tag_id, entry_id ) VALUES (?, ?)'; | |
397 | $params_action = array($tag_id, $entry_id); | |
398 | $query = $this->executeQuery($sql_action, $params_action); | |
399 | return $query; | |
400 | } | |
2cd263e0 | 401 | } |