]>
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 |
3602405e | 8 | * @license http://opensource.org/licenses/MIT see COPYING file |
14890de3 | 9 | */ |
10 | ||
bc1ee852 | 11 | class Database { |
b3cda72e | 12 | |
14890de3 | 13 | var $handle; |
4e067cea NL |
14 | private $order = array ( |
15 | 'ia' => 'ORDER BY entries.id', | |
16 | 'id' => 'ORDER BY entries.id DESC', | |
17 | 'ta' => 'ORDER BY lower(entries.title)', | |
18 | 'td' => 'ORDER BY lower(entries.title) DESC', | |
19 | 'default' => 'ORDER BY entries.id' | |
032e0ca1 MR |
20 | ); |
21 | ||
182faf26 | 22 | function __construct() |
68857cea NL |
23 | { |
24 | switch (STORAGE) { | |
25 | case 'sqlite': | |
211068ce | 26 | // Check if /db is writeable |
b668db24 | 27 | if ( !is_writable(STORAGE_SQLITE) || !is_writable(dirname(STORAGE_SQLITE))) { |
211068ce MR |
28 | die('An error occured: "db" directory must be writeable for your web server user!'); |
29 | } | |
68857cea NL |
30 | $db_path = 'sqlite:' . STORAGE_SQLITE; |
31 | $this->handle = new PDO($db_path); | |
32 | break; | |
33 | case 'mysql': | |
054c9d88 TC |
34 | if (MYSQL_USE_UTF8MB4) { |
35 | $db_path = 'mysql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB . ';charset=utf8mb4'; | |
36 | $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD, array( | |
37 | PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4', | |
38 | )); | |
39 | } else { | |
40 | $db_path = 'mysql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB; | |
41 | $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD); | |
42 | } | |
68857cea NL |
43 | break; |
44 | case 'postgres': | |
45 | $db_path = 'pgsql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB; | |
182faf26 | 46 | $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD); |
68857cea | 47 | break; |
8af31ae0 | 48 | default: |
49 | die(STORAGE . ' is not a recognised database system !'); | |
68857cea NL |
50 | } |
51 | ||
14890de3 | 52 | $this->handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); |
3602405e | 53 | $this->_checkTags(); |
580d60b9 | 54 | Tools::logm('storage type ' . STORAGE); |
14890de3 | 55 | } |
56 | ||
b3cda72e NL |
57 | private function getHandle() |
58 | { | |
14890de3 | 59 | return $this->handle; |
60 | } | |
61 | ||
b3cda72e NL |
62 | private function _checkTags() |
63 | { | |
5cfafc61 NL |
64 | |
65 | if (STORAGE == 'sqlite') { | |
66 | $sql = ' | |
67 | CREATE TABLE IF NOT EXISTS tags ( | |
68 | id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, | |
69 | value TEXT | |
70 | )'; | |
71 | } | |
72 | elseif(STORAGE == 'mysql') { | |
73 | $sql = ' | |
74 | CREATE TABLE IF NOT EXISTS `tags` ( | |
75 | `id` int(11) NOT NULL AUTO_INCREMENT, | |
76 | `value` varchar(255) NOT NULL, | |
77 | PRIMARY KEY (`id`) | |
78 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; | |
79 | '; | |
80 | } | |
81 | else { | |
82 | $sql = ' | |
5ce39784 | 83 | CREATE TABLE IF NOT EXISTS tags ( |
5cfafc61 NL |
84 | id bigserial primary key, |
85 | value varchar(255) NOT NULL | |
86 | ); | |
87 | '; | |
88 | } | |
aa8c9f2a | 89 | |
5cfafc61 NL |
90 | $query = $this->executeQuery($sql, array()); |
91 | ||
92 | if (STORAGE == 'sqlite') { | |
93 | $sql = ' | |
a562e390 | 94 | CREATE TABLE IF NOT EXISTS tags_entries ( |
5cfafc61 NL |
95 | id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, |
96 | entry_id INTEGER, | |
97 | tag_id INTEGER, | |
98 | FOREIGN KEY(entry_id) REFERENCES entries(id) ON DELETE CASCADE, | |
99 | FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE | |
100 | )'; | |
101 | } | |
102 | elseif(STORAGE == 'mysql') { | |
103 | $sql = ' | |
104 | CREATE TABLE IF NOT EXISTS `tags_entries` ( | |
105 | `id` int(11) NOT NULL AUTO_INCREMENT, | |
106 | `entry_id` int(11) NOT NULL, | |
107 | `tag_id` int(11) NOT NULL, | |
108 | FOREIGN KEY(entry_id) REFERENCES entries(id) ON DELETE CASCADE, | |
109 | FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE, | |
110 | PRIMARY KEY (`id`) | |
111 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; | |
112 | '; | |
113 | } | |
114 | else { | |
115 | $sql = ' | |
5ce39784 | 116 | CREATE TABLE IF NOT EXISTS tags_entries ( |
5cfafc61 NL |
117 | id bigserial primary key, |
118 | entry_id integer NOT NULL, | |
119 | tag_id integer NOT NULL | |
120 | ) | |
121 | '; | |
122 | } | |
123 | ||
124 | $query = $this->executeQuery($sql, array()); | |
aa8c9f2a NL |
125 | } |
126 | ||
046b9316 | 127 | public function install($login, $password, $email = '') |
b3cda72e | 128 | { |
b916bcfc | 129 | $sql = 'INSERT INTO users ( username, password, name, email) VALUES (?, ?, ?, ?)'; |
046b9316 | 130 | $params = array($login, $password, $login, $email); |
b916bcfc NL |
131 | $query = $this->executeQuery($sql, $params); |
132 | ||
133 | $sequence = ''; | |
134 | if (STORAGE == 'postgres') { | |
135 | $sequence = 'users_id_seq'; | |
136 | } | |
137 | ||
138 | $id_user = intval($this->getLastId($sequence)); | |
139 | ||
140 | $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; | |
00dbaf90 | 141 | $params = array($id_user, 'pager', PAGINATION); |
b916bcfc NL |
142 | $query = $this->executeQuery($sql, $params); |
143 | ||
144 | $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; | |
00dbaf90 NL |
145 | $params = array($id_user, 'language', LANG); |
146 | $query = $this->executeQuery($sql, $params); | |
182faf26 | 147 | |
00dbaf90 NL |
148 | $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; |
149 | $params = array($id_user, 'theme', DEFAULT_THEME); | |
7ce7ec4c | 150 | $query = $this->executeQuery($sql, $params); |
aa8c9f2a | 151 | |
7ce7ec4c NL |
152 | return TRUE; |
153 | } | |
aa8c9f2a | 154 | |
b3cda72e NL |
155 | public function getConfigUser($id) |
156 | { | |
7ce7ec4c NL |
157 | $sql = "SELECT * FROM users_config WHERE user_id = ?"; |
158 | $query = $this->executeQuery($sql, array($id)); | |
4a746679 TC |
159 | $result = ($query) ? $query->fetchAll() : false; |
160 | $user_config = false; | |
161 | if ($query) { | |
162 | $user_config = array(); | |
182faf26 | 163 | |
4a746679 TC |
164 | foreach ($result as $key => $value) { |
165 | $user_config[$value['name']] = $value['value']; | |
166 | } | |
aa8c9f2a NL |
167 | } |
168 | ||
7ce7ec4c | 169 | return $user_config; |
aa8c9f2a NL |
170 | } |
171 | ||
b3cda72e NL |
172 | public function userExists($username) |
173 | { | |
027b4e15 DS |
174 | $sql = "SELECT * FROM users WHERE username=?"; |
175 | $query = $this->executeQuery($sql, array($username)); | |
176 | $login = $query->fetchAll(); | |
4a746679 | 177 | return (isset($login[0]) && $query) ? true : false; |
027b4e15 DS |
178 | } |
179 | ||
b3cda72e NL |
180 | public function login($username, $password, $isauthenticated = FALSE) |
181 | { | |
6af66b11 | 182 | if ($isauthenticated) { |
4e067cea NL |
183 | $sql = "SELECT * FROM users WHERE username=?"; |
184 | $query = $this->executeQuery($sql, array($username)); | |
6af66b11 | 185 | } else { |
4e067cea NL |
186 | $sql = "SELECT * FROM users WHERE username=? AND password=?"; |
187 | $query = $this->executeQuery($sql, array($username, $password)); | |
6af66b11 | 188 | } |
4a746679 | 189 | $login = ($query) ? $query->fetchAll() : false; |
aa8c9f2a | 190 | |
7ce7ec4c | 191 | $user = array(); |
4a746679 | 192 | if ($login[0]) { |
7ce7ec4c NL |
193 | $user['id'] = $login[0]['id']; |
194 | $user['username'] = $login[0]['username']; | |
195 | $user['password'] = $login[0]['password']; | |
196 | $user['name'] = $login[0]['name']; | |
197 | $user['email'] = $login[0]['email']; | |
198 | $user['config'] = $this->getConfigUser($login[0]['id']); | |
199 | } | |
aa8c9f2a | 200 | |
7ce7ec4c | 201 | return $user; |
aa8c9f2a NL |
202 | } |
203 | ||
00dbaf90 | 204 | public function updatePassword($userId, $password) |
6499b26a | 205 | { |
8d3275be | 206 | $sql_update = "UPDATE users SET password=? WHERE id=?"; |
1e98ee1d NL |
207 | $params_update = array($password, $userId); |
208 | $query = $this->executeQuery($sql_update, $params_update); | |
00dbaf90 | 209 | } |
182faf26 | 210 | |
b3cda72e NL |
211 | public function updateUserConfig($userId, $key, $value) |
212 | { | |
58ace494 | 213 | $config = $this->getConfigUser($userId); |
182faf26 | 214 | |
d1d3498b | 215 | if (! isset($config[$key])) { |
da5fc42f | 216 | $sql = "INSERT INTO users_config (value, user_id, name) VALUES (?, ?, ?)"; |
58ace494 NL |
217 | } |
218 | else { | |
da5fc42f | 219 | $sql = "UPDATE users_config SET value=? WHERE user_id=? AND name=?"; |
58ace494 NL |
220 | } |
221 | ||
222 | $params = array($value, $userId, $key); | |
223 | $query = $this->executeQuery($sql, $params); | |
6499b26a NL |
224 | } |
225 | ||
b3cda72e NL |
226 | private function executeQuery($sql, $params) |
227 | { | |
14890de3 | 228 | try |
229 | { | |
230 | $query = $this->getHandle()->prepare($sql); | |
231 | $query->execute($params); | |
232 | return $query; | |
233 | } | |
234 | catch (Exception $e) | |
235 | { | |
eb1af592 | 236 | Tools::logm('execute query error : '.$e->getMessage()); |
bc1ee852 | 237 | return FALSE; |
14890de3 | 238 | } |
239 | } | |
4d99bae8 | 240 | |
b3cda72e NL |
241 | public function listUsers($username = NULL) |
242 | { | |
4d99bae8 | 243 | $sql = 'SELECT count(*) FROM users'.( $username ? ' WHERE username=?' : ''); |
244 | $query = $this->executeQuery($sql, ( $username ? array($username) : array())); | |
4a746679 | 245 | list($count) = ($query) ? $query->fetch() : false; |
4d99bae8 | 246 | return $count; |
247 | } | |
248 | ||
b3cda72e NL |
249 | public function getUserPassword($userID) |
250 | { | |
4d99bae8 | 251 | $sql = "SELECT * FROM users WHERE id=?"; |
252 | $query = $this->executeQuery($sql, array($userID)); | |
253 | $password = $query->fetchAll(); | |
4a746679 | 254 | return ($query) ? $password[0]['password'] : false; |
4d99bae8 | 255 | } |
256 | ||
b3cda72e NL |
257 | public function deleteUserConfig($userID) |
258 | { | |
4d99bae8 | 259 | $sql_action = 'DELETE from users_config WHERE user_id=?'; |
260 | $params_action = array($userID); | |
261 | $query = $this->executeQuery($sql_action, $params_action); | |
4a746679 | 262 | return ($query) ? $query : false; |
4d99bae8 | 263 | } |
264 | ||
b3cda72e NL |
265 | public function deleteTagsEntriesAndEntries($userID) |
266 | { | |
4d99bae8 | 267 | $entries = $this->retrieveAll($userID); |
4a746679 | 268 | if ($entries) { |
c1293741 TC |
269 | foreach($entries as $entry) { |
270 | $tags = $this->retrieveTagsByEntry($entry['id']); | |
4a746679 | 271 | foreach($tags as $tag) { |
c1293741 | 272 | $this->removeTagForEntry($entry['id'], $tags); |
4a746679 TC |
273 | } |
274 | $this->deleteById($entryid,$userID); | |
4d99bae8 | 275 | } |
4a746679 TC |
276 | } else { |
277 | return false; | |
4d99bae8 | 278 | } |
279 | } | |
280 | ||
b3cda72e NL |
281 | public function deleteUser($userID) |
282 | { | |
4d99bae8 | 283 | $sql_action = 'DELETE from users WHERE id=?'; |
284 | $params_action = array($userID); | |
285 | $query = $this->executeQuery($sql_action, $params_action); | |
286 | } | |
14890de3 | 287 | |
b3cda72e NL |
288 | public function updateContentAndTitle($id, $title, $body, $user_id) |
289 | { | |
53e3158d NL |
290 | $sql_action = 'UPDATE entries SET content = ?, title = ? WHERE id=? AND user_id=?'; |
291 | $params_action = array($body, $title, $id, $user_id); | |
292 | $query = $this->executeQuery($sql_action, $params_action); | |
53e3158d NL |
293 | return $query; |
294 | } | |
295 | ||
b3cda72e NL |
296 | public function retrieveUnfetchedEntries($user_id, $limit) |
297 | { | |
53e3158d NL |
298 | |
299 | $sql_limit = "LIMIT 0,".$limit; | |
300 | if (STORAGE == 'postgres') { | |
301 | $sql_limit = "LIMIT ".$limit." OFFSET 0"; | |
302 | } | |
303 | ||
fde4cf06 | 304 | $sql = "SELECT * FROM entries WHERE (content = '' OR content IS NULL) AND title LIKE '%Import%' AND user_id=? ORDER BY id " . $sql_limit; |
53e3158d NL |
305 | $query = $this->executeQuery($sql, array($user_id)); |
306 | $entries = $query->fetchAll(); | |
307 | ||
4a746679 | 308 | return ($query) ? $entries : false; |
53e3158d NL |
309 | } |
310 | ||
b3cda72e NL |
311 | public function retrieveUnfetchedEntriesCount($user_id) |
312 | { | |
fde4cf06 | 313 | $sql = "SELECT count(*) FROM entries WHERE (content = '' OR content IS NULL) AND title LIKE '%Import%' AND user_id=?"; |
182faf26 MR |
314 | $query = $this->executeQuery($sql, array($user_id)); |
315 | list($count) = $query->fetch(); | |
316 | ||
317 | return $count; | |
318 | } | |
319 | ||
b3cda72e NL |
320 | public function retrieveAll($user_id) |
321 | { | |
182faf26 | 322 | $sql = "SELECT * FROM entries WHERE user_id=? ORDER BY id"; |
8d3275be | 323 | $query = $this->executeQuery($sql, array($user_id)); |
44e77bfa | 324 | $entries = $query->fetchAll(); |
325 | ||
4a746679 | 326 | return ($query) ? $entries : false; |
44e77bfa | 327 | } |
328 | ||
f9c8087f VM |
329 | public function retrieveAllWithTags($user_id) |
330 | { | |
331 | $entries = $this->retrieveAll($user_id); | |
4a746679 TC |
332 | if ($entries) { |
333 | $count = count($entries); | |
334 | for ($i = 0; $i < $count; $i++) { | |
335 | $tag_entries = $this->retrieveTagsByEntry($entries[$i]['id']); | |
336 | $tags = []; | |
337 | foreach ($tag_entries as $tag) { | |
338 | $tags[] = $tag[1]; | |
339 | } | |
340 | $entries[$i]['tags'] = implode(',', $tags); | |
341 | } | |
f9c8087f VM |
342 | } |
343 | return $entries; | |
344 | } | |
345 | ||
b3cda72e NL |
346 | public function retrieveOneById($id, $user_id) |
347 | { | |
8d3275be NL |
348 | $sql = "SELECT * FROM entries WHERE id=? AND user_id=?"; |
349 | $params = array(intval($id), $user_id); | |
14890de3 | 350 | $query = $this->executeQuery($sql, $params); |
351 | $entry = $query->fetchAll(); | |
352 | ||
4a746679 | 353 | return ($query) ? $entry[0] : false; |
14890de3 | 354 | } |
355 | ||
b3cda72e NL |
356 | public function retrieveOneByURL($url, $user_id) |
357 | { | |
182faf26 | 358 | $sql = "SELECT * FROM entries WHERE url=? AND user_id=?"; |
488fc63b MR |
359 | $params = array($url, $user_id); |
360 | $query = $this->executeQuery($sql, $params); | |
361 | $entry = $query->fetchAll(); | |
362 | ||
4a746679 | 363 | return ($query) ? $entry[0] : false; |
488fc63b MR |
364 | } |
365 | ||
b3cda72e NL |
366 | public function reassignTags($old_entry_id, $new_entry_id) |
367 | { | |
488fc63b MR |
368 | $sql = "UPDATE tags_entries SET entry_id=? WHERE entry_id=?"; |
369 | $params = array($new_entry_id, $old_entry_id); | |
370 | $query = $this->executeQuery($sql, $params); | |
371 | } | |
372 | ||
b3cda72e NL |
373 | public function getEntriesByView($view, $user_id, $limit = '', $tag_id = 0) |
374 | { | |
032e0ca1 MR |
375 | switch ($view) { |
376 | case 'archive': | |
182faf26 | 377 | $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? "; |
032e0ca1 | 378 | $params = array($user_id, 1); |
14890de3 | 379 | break; |
032e0ca1 | 380 | case 'fav' : |
182faf26 | 381 | $sql = "SELECT * FROM entries WHERE user_id=? AND is_fav=? "; |
032e0ca1 | 382 | $params = array($user_id, 1); |
14890de3 | 383 | break; |
032e0ca1 MR |
384 | case 'tag' : |
385 | $sql = "SELECT entries.* FROM entries | |
386 | LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id | |
182faf26 | 387 | WHERE entries.user_id=? AND tags_entries.tag_id = ? "; |
032e0ca1 | 388 | $params = array($user_id, $tag_id); |
14890de3 | 389 | break; |
390 | default: | |
182faf26 | 391 | $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? "; |
032e0ca1 | 392 | $params = array($user_id, 0); |
14890de3 | 393 | break; |
394 | } | |
395 | ||
032e0ca1 MR |
396 | $sql .= $this->getEntriesOrder().' ' . $limit; |
397 | ||
398 | $query = $this->executeQuery($sql, $params); | |
399 | $entries = $query->fetchAll(); | |
400 | ||
4a746679 TC |
401 | return ($query) ? $entries : false; |
402 | ||
b3cda72e | 403 | } |
032e0ca1 | 404 | |
b3cda72e NL |
405 | public function getEntriesByViewCount($view, $user_id, $tag_id = 0) |
406 | { | |
53e3158d | 407 | switch ($view) { |
14890de3 | 408 | case 'archive': |
182faf26 | 409 | $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? "; |
bc1ee852 | 410 | $params = array($user_id, 1); |
14890de3 | 411 | break; |
412 | case 'fav' : | |
182faf26 | 413 | $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_fav=? "; |
bc1ee852 | 414 | $params = array($user_id, 1); |
14890de3 | 415 | break; |
53e3158d NL |
416 | case 'tag' : |
417 | $sql = "SELECT count(*) FROM entries | |
418 | LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id | |
182faf26 | 419 | WHERE entries.user_id=? AND tags_entries.tag_id = ? "; |
53e3158d NL |
420 | $params = array($user_id, $tag_id); |
421 | break; | |
14890de3 | 422 | default: |
182faf26 | 423 | $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? "; |
8d3275be | 424 | $params = array($user_id, 0); |
14890de3 | 425 | break; |
426 | } | |
427 | ||
8d3275be | 428 | $query = $this->executeQuery($sql, $params); |
4a746679 | 429 | list($count) = ($query) ? $query->fetch() : array(false); |
14890de3 | 430 | |
53e3158d | 431 | return $count; |
14890de3 | 432 | } |
f76dab12 | 433 | public function getRandomId($user_id, $view) { |
cdee5e65 | 434 | $random = (STORAGE == 'mysql') ? 'RAND()' : 'RANDOM()'; |
f76dab12 TC |
435 | switch ($view) { |
436 | case 'archive': | |
437 | $sql = "SELECT id FROM entries WHERE user_id=? AND is_read=? ORDER BY ". $random . " LIMIT 1"; | |
438 | $params = array($user_id,1); | |
439 | break; | |
440 | case 'fav': | |
441 | $sql = "SELECT id FROM entries WHERE user_id=? AND is_fav=? ORDER BY ". $random . " LIMIT 1"; | |
442 | $params = array($user_id,1); | |
443 | break; | |
444 | default: | |
445 | $sql = "SELECT id FROM entries WHERE user_id=? AND is_read=? ORDER BY ". $random . " LIMIT 1"; | |
446 | $params = array($user_id,0); | |
447 | break; | |
448 | } | |
cdee5e65 TC |
449 | $query = $this->executeQuery($sql, $params); |
450 | $id = $query->fetchAll(); | |
6b607416 | 451 | |
4a746679 | 452 | return ($query) ? $id : false; |
6b607416 TC |
453 | } |
454 | ||
059a3380 TC |
455 | public function getPreviousArticle($id, $user_id) |
456 | { | |
828d008b TC |
457 | $sqlcondition = "is_read=0"; |
458 | if (STORAGE == 'postgres') { | |
459 | $sqlcondition = "is_read=false"; | |
460 | } | |
461 | $sql = "SELECT id FROM entries WHERE id = (SELECT max(id) FROM entries WHERE id < ? AND " . $sqlcondition . ") AND user_id=? AND " . $sqlcondition; | |
059a3380 TC |
462 | $params = array($id, $user_id); |
463 | $query = $this->executeQuery($sql, $params); | |
828d008b | 464 | $id_entry = ($query) ? $query->fetchAll() : false; |
4a746679 | 465 | $id = ($query) ? $id_entry[0][0] : false; |
059a3380 TC |
466 | return $id; |
467 | } | |
468 | ||
469 | public function getNextArticle($id, $user_id) | |
470 | { | |
828d008b TC |
471 | $sqlcondition = "is_read=0"; |
472 | if (STORAGE == 'postgres') { | |
473 | $sqlcondition = "is_read=false"; | |
474 | } | |
475 | $sql = "SELECT id FROM entries WHERE id = (SELECT min(id) FROM entries WHERE id > ? AND " . $sqlcondition . ") AND user_id=? AND " . $sqlcondition; | |
059a3380 TC |
476 | $params = array($id, $user_id); |
477 | $query = $this->executeQuery($sql, $params); | |
828d008b | 478 | $id_entry = ($query) ? $query->fetchAll() : false; |
4a746679 | 479 | $id = ($query) ? $id_entry[0][0] : false; |
059a3380 TC |
480 | return $id; |
481 | } | |
482 | ||
14890de3 | 483 | |
b3cda72e NL |
484 | public function updateContent($id, $content, $user_id) |
485 | { | |
b58e261d NL |
486 | $sql_action = 'UPDATE entries SET content = ? WHERE id=? AND user_id=?'; |
487 | $params_action = array($content, $id, $user_id); | |
488 | $query = $this->executeQuery($sql_action, $params_action); | |
489 | return $query; | |
490 | } | |
491 | ||
182faf26 MR |
492 | /** |
493 | * | |
494 | * @param string $url | |
495 | * @param string $title | |
496 | * @param string $content | |
497 | * @param integer $user_id | |
498 | * @return integer $id of inserted record | |
499 | */ | |
b3cda72e NL |
500 | public function add($url, $title, $content, $user_id, $isFavorite=0, $isRead=0) |
501 | { | |
6400371f NL |
502 | $sql_action = 'INSERT INTO entries ( url, title, content, user_id, is_fav, is_read ) VALUES (?, ?, ?, ?, ?, ?)'; |
503 | $params_action = array($url, $title, $content, $user_id, $isFavorite, $isRead); | |
5425b0dd | 504 | |
182faf26 MR |
505 | if ( !$this->executeQuery($sql_action, $params_action) ) { |
506 | $id = null; | |
507 | } | |
508 | else { | |
1bcbe8be | 509 | $id = intval($this->getLastId( (STORAGE == 'postgres') ? 'entries_id_seq' : '') ); |
182faf26 MR |
510 | } |
511 | return $id; | |
14890de3 | 512 | } |
513 | ||
b3cda72e NL |
514 | public function deleteById($id, $user_id) |
515 | { | |
8d3275be NL |
516 | $sql_action = "DELETE FROM entries WHERE id=? AND user_id=?"; |
517 | $params_action = array($id, $user_id); | |
14890de3 | 518 | $query = $this->executeQuery($sql_action, $params_action); |
29c6fd46 | 519 | return $query; |
14890de3 | 520 | } |
521 | ||
b3cda72e NL |
522 | public function favoriteById($id, $user_id) |
523 | { | |
bc1ee852 | 524 | $sql_action = "UPDATE entries SET is_fav=NOT is_fav WHERE id=? AND user_id=?"; |
8d3275be | 525 | $params_action = array($id, $user_id); |
14890de3 | 526 | $query = $this->executeQuery($sql_action, $params_action); |
527 | } | |
528 | ||
b3cda72e NL |
529 | public function archiveById($id, $user_id) |
530 | { | |
bc1ee852 | 531 | $sql_action = "UPDATE entries SET is_read=NOT is_read WHERE id=? AND user_id=?"; |
8d3275be | 532 | $params_action = array($id, $user_id); |
14890de3 | 533 | $query = $this->executeQuery($sql_action, $params_action); |
534 | } | |
535 | ||
b3cda72e NL |
536 | public function archiveAll($user_id) |
537 | { | |
f14807de NL |
538 | $sql_action = "UPDATE entries SET is_read=? WHERE user_id=? AND is_read=?"; |
539 | $params_action = array($user_id, 1, 0); | |
540 | $query = $this->executeQuery($sql_action, $params_action); | |
541 | } | |
542 | ||
b3cda72e NL |
543 | public function getLastId($column = '') |
544 | { | |
b916bcfc | 545 | return $this->getHandle()->lastInsertId($column); |
14890de3 | 546 | } |
5ce39784 | 547 | |
b3cda72e NL |
548 | public function search($term, $user_id, $limit = '') |
549 | { | |
a4585f7e MR |
550 | $search = '%'.$term.'%'; |
551 | $sql_action = "SELECT * FROM entries WHERE user_id=? AND (content LIKE ? OR title LIKE ? OR url LIKE ?) "; //searches in content, title and URL | |
552 | $sql_action .= $this->getEntriesOrder().' ' . $limit; | |
553 | $params_action = array($user_id, $search, $search, $search); | |
554 | $query = $this->executeQuery($sql_action, $params_action); | |
4a746679 | 555 | return ($query) ? $query->fetchAll() : false; |
a4585f7e | 556 | } |
5bea1a4d | 557 | |
b3cda72e NL |
558 | public function retrieveAllTags($user_id, $term = NULL) |
559 | { | |
fb26cc93 | 560 | $sql = "SELECT DISTINCT tags.*, count(entries.id) AS entriescount FROM tags |
b89d5a2b NL |
561 | LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id |
562 | LEFT JOIN entries ON tags_entries.entry_id=entries.id | |
182faf26 | 563 | WHERE entries.user_id=? |
fb26cc93 MR |
564 | ". (($term) ? "AND lower(tags.value) LIKE ?" : '') ." |
565 | GROUP BY tags.id, tags.value | |
566 | ORDER BY tags.value"; | |
567 | $query = $this->executeQuery($sql, (($term)? array($user_id, strtolower('%'.$term.'%')) : array($user_id) )); | |
4a746679 | 568 | $tags = ($query) ? $query->fetchAll() : false; |
2e2ebe5e NL |
569 | |
570 | return $tags; | |
571 | } | |
572 | ||
b3cda72e NL |
573 | public function retrieveTag($id, $user_id) |
574 | { | |
e83cf5a7 | 575 | $sql = "SELECT DISTINCT tags.* FROM tags |
b89d5a2b NL |
576 | LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id |
577 | LEFT JOIN entries ON tags_entries.entry_id=entries.id | |
182faf26 | 578 | WHERE tags.id=? AND entries.user_id=?"; |
b89d5a2b | 579 | $params = array(intval($id), $user_id); |
4886ed6d | 580 | $query = $this->executeQuery($sql, $params); |
4a746679 TC |
581 | $tags = ($query) ? $query->fetchAll() : false; |
582 | $tag = ($query) ? $tags[0] : false; | |
4886ed6d | 583 | |
4a746679 | 584 | return $tag[0]; |
4886ed6d NL |
585 | } |
586 | ||
b3cda72e NL |
587 | public function retrieveEntriesByTag($tag_id, $user_id) |
588 | { | |
182faf26 | 589 | $sql = |
9de34d4e | 590 | "SELECT entries.* FROM entries |
4886ed6d | 591 | LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id |
25052a76 | 592 | WHERE tags_entries.tag_id = ? AND entries.user_id=? ORDER by entries.id DESC"; |
b89d5a2b | 593 | $query = $this->executeQuery($sql, array($tag_id, $user_id)); |
4a746679 | 594 | $entries = ($query) ? $query->fetchAll() : false; |
4886ed6d NL |
595 | |
596 | return $entries; | |
597 | } | |
598 | ||
b3cda72e NL |
599 | public function retrieveTagsByEntry($entry_id) |
600 | { | |
182faf26 | 601 | $sql = |
9de34d4e | 602 | "SELECT tags.* FROM tags |
5bea1a4d NL |
603 | LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id |
604 | WHERE tags_entries.entry_id = ?"; | |
2e2ebe5e | 605 | $query = $this->executeQuery($sql, array($entry_id)); |
4a746679 | 606 | $tags = ($query) ? $query->fetchAll() : false; |
5bea1a4d NL |
607 | |
608 | return $tags; | |
609 | } | |
c432fa16 | 610 | |
b3cda72e NL |
611 | public function removeTagForEntry($entry_id, $tag_id) |
612 | { | |
c432fa16 NL |
613 | $sql_action = "DELETE FROM tags_entries WHERE tag_id=? AND entry_id=?"; |
614 | $params_action = array($tag_id, $entry_id); | |
615 | $query = $this->executeQuery($sql_action, $params_action); | |
4a746679 | 616 | return ($query) ? $query : false; |
c432fa16 | 617 | } |
9c743ab9 | 618 | |
b3cda72e NL |
619 | public function cleanUnusedTag($tag_id) |
620 | { | |
24696800 | 621 | $sql_action = "SELECT tags.* FROM tags JOIN tags_entries ON tags_entries.tag_id=tags.id WHERE tags.id=?"; |
622 | $query = $this->executeQuery($sql_action,array($tag_id)); | |
4a746679 | 623 | $tagstokeep = ($query) ? $query->fetchAll() : false; |
24696800 | 624 | $sql_action = "SELECT tags.* FROM tags LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id WHERE tags.id=?"; |
625 | $query = $this->executeQuery($sql_action,array($tag_id)); | |
4a746679 TC |
626 | $alltags = ($query) ? $query->fetchAll() : false; |
627 | ||
628 | if ($tagstokeep && $alltags) { | |
629 | foreach ($alltags as $tag) { | |
630 | if ($tag && !in_array($tag,$tagstokeep)) { | |
631 | $sql_action = "DELETE FROM tags WHERE id=?"; | |
632 | $params_action = array($tag[0]); | |
633 | $this->executeQuery($sql_action, $params_action); | |
634 | return true; | |
635 | } | |
9c743ab9 | 636 | } |
4a746679 TC |
637 | } else { |
638 | return false; | |
9c743ab9 | 639 | } |
640 | } | |
c432fa16 | 641 | |
b3cda72e NL |
642 | public function retrieveTagByValue($value) |
643 | { | |
c432fa16 NL |
644 | $sql = "SELECT * FROM tags WHERE value=?"; |
645 | $params = array($value); | |
646 | $query = $this->executeQuery($sql, $params); | |
4a746679 | 647 | $tag = ($query) ? $query->fetchAll() : false; |
c432fa16 | 648 | |
4a746679 | 649 | return ($query) ? $tag[0] : false; |
c432fa16 NL |
650 | } |
651 | ||
b3cda72e NL |
652 | public function createTag($value) |
653 | { | |
c432fa16 NL |
654 | $sql_action = 'INSERT INTO tags ( value ) VALUES (?)'; |
655 | $params_action = array($value); | |
656 | $query = $this->executeQuery($sql_action, $params_action); | |
4a746679 | 657 | return ($query) ? $query : false; |
c432fa16 NL |
658 | } |
659 | ||
b3cda72e NL |
660 | public function setTagToEntry($tag_id, $entry_id) |
661 | { | |
c432fa16 NL |
662 | $sql_action = 'INSERT INTO tags_entries ( tag_id, entry_id ) VALUES (?, ?)'; |
663 | $params_action = array($tag_id, $entry_id); | |
664 | $query = $this->executeQuery($sql_action, $params_action); | |
4a746679 | 665 | return ($query) ? $query : false; |
c432fa16 | 666 | } |
032e0ca1 | 667 | |
b3cda72e NL |
668 | private function getEntriesOrder() |
669 | { | |
670 | if (isset($_SESSION['sort']) and array_key_exists($_SESSION['sort'], $this->order)) { | |
671 | return $this->order[$_SESSION['sort']]; | |
032e0ca1 | 672 | } |
b3cda72e NL |
673 | else { |
674 | return $this->order['default']; | |
675 | } | |
676 | } | |
2cd263e0 | 677 | } |