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