diff options
Diffstat (limited to 'inc/poche/Database.class.php')
-rwxr-xr-x | inc/poche/Database.class.php | 144 |
1 files changed, 79 insertions, 65 deletions
diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php index 7be7a394..7ec1602d 100755 --- a/inc/poche/Database.class.php +++ b/inc/poche/Database.class.php | |||
@@ -156,11 +156,14 @@ class Database { | |||
156 | { | 156 | { |
157 | $sql = "SELECT * FROM users_config WHERE user_id = ?"; | 157 | $sql = "SELECT * FROM users_config WHERE user_id = ?"; |
158 | $query = $this->executeQuery($sql, array($id)); | 158 | $query = $this->executeQuery($sql, array($id)); |
159 | $result = $query->fetchAll(); | 159 | $result = ($query) ? $query->fetchAll() : false; |
160 | $user_config = array(); | 160 | $user_config = false; |
161 | if ($query) { | ||
162 | $user_config = array(); | ||
161 | 163 | ||
162 | foreach ($result as $key => $value) { | 164 | foreach ($result as $key => $value) { |
163 | $user_config[$value['name']] = $value['value']; | 165 | $user_config[$value['name']] = $value['value']; |
166 | } | ||
164 | } | 167 | } |
165 | 168 | ||
166 | return $user_config; | 169 | return $user_config; |
@@ -171,11 +174,7 @@ class Database { | |||
171 | $sql = "SELECT * FROM users WHERE username=?"; | 174 | $sql = "SELECT * FROM users WHERE username=?"; |
172 | $query = $this->executeQuery($sql, array($username)); | 175 | $query = $this->executeQuery($sql, array($username)); |
173 | $login = $query->fetchAll(); | 176 | $login = $query->fetchAll(); |
174 | if (isset($login[0])) { | 177 | return (isset($login[0]) && $query) ? true : false; |
175 | return true; | ||
176 | } else { | ||
177 | return false; | ||
178 | } | ||
179 | } | 178 | } |
180 | 179 | ||
181 | public function login($username, $password, $isauthenticated = FALSE) | 180 | public function login($username, $password, $isauthenticated = FALSE) |
@@ -187,10 +186,10 @@ class Database { | |||
187 | $sql = "SELECT * FROM users WHERE username=? AND password=?"; | 186 | $sql = "SELECT * FROM users WHERE username=? AND password=?"; |
188 | $query = $this->executeQuery($sql, array($username, $password)); | 187 | $query = $this->executeQuery($sql, array($username, $password)); |
189 | } | 188 | } |
190 | $login = $query->fetchAll(); | 189 | $login = ($query) ? $query->fetchAll() : false; |
191 | 190 | ||
192 | $user = array(); | 191 | $user = array(); |
193 | if (isset($login[0])) { | 192 | if ($login[0]) { |
194 | $user['id'] = $login[0]['id']; | 193 | $user['id'] = $login[0]['id']; |
195 | $user['username'] = $login[0]['username']; | 194 | $user['username'] = $login[0]['username']; |
196 | $user['password'] = $login[0]['password']; | 195 | $user['password'] = $login[0]['password']; |
@@ -243,7 +242,7 @@ class Database { | |||
243 | { | 242 | { |
244 | $sql = 'SELECT count(*) FROM users'.( $username ? ' WHERE username=?' : ''); | 243 | $sql = 'SELECT count(*) FROM users'.( $username ? ' WHERE username=?' : ''); |
245 | $query = $this->executeQuery($sql, ( $username ? array($username) : array())); | 244 | $query = $this->executeQuery($sql, ( $username ? array($username) : array())); |
246 | list($count) = $query->fetch(); | 245 | list($count) = ($query) ? $query->fetch() : false; |
247 | return $count; | 246 | return $count; |
248 | } | 247 | } |
249 | 248 | ||
@@ -252,7 +251,7 @@ class Database { | |||
252 | $sql = "SELECT * FROM users WHERE id=?"; | 251 | $sql = "SELECT * FROM users WHERE id=?"; |
253 | $query = $this->executeQuery($sql, array($userID)); | 252 | $query = $this->executeQuery($sql, array($userID)); |
254 | $password = $query->fetchAll(); | 253 | $password = $query->fetchAll(); |
255 | return isset($password[0]['password']) ? $password[0]['password'] : null; | 254 | return ($query) ? $password[0]['password'] : false; |
256 | } | 255 | } |
257 | 256 | ||
258 | public function deleteUserConfig($userID) | 257 | public function deleteUserConfig($userID) |
@@ -260,18 +259,22 @@ class Database { | |||
260 | $sql_action = 'DELETE from users_config WHERE user_id=?'; | 259 | $sql_action = 'DELETE from users_config WHERE user_id=?'; |
261 | $params_action = array($userID); | 260 | $params_action = array($userID); |
262 | $query = $this->executeQuery($sql_action, $params_action); | 261 | $query = $this->executeQuery($sql_action, $params_action); |
263 | return $query; | 262 | return ($query) ? $query : false; |
264 | } | 263 | } |
265 | 264 | ||
266 | public function deleteTagsEntriesAndEntries($userID) | 265 | public function deleteTagsEntriesAndEntries($userID) |
267 | { | 266 | { |
268 | $entries = $this->retrieveAll($userID); | 267 | $entries = $this->retrieveAll($userID); |
269 | foreach($entries as $entryid) { | 268 | if ($entries) { |
270 | $tags = $this->retrieveTagsByEntry($entryid); | 269 | foreach($entries as $entry) { |
271 | foreach($tags as $tag) { | 270 | $tags = $this->retrieveTagsByEntry($entry['id']); |
272 | $this->removeTagForEntry($entryid,$tags); | 271 | foreach($tags as $tag) { |
272 | $this->removeTagForEntry($entry['id'], $tags); | ||
273 | } | ||
274 | $this->deleteById($entryid,$userID); | ||
273 | } | 275 | } |
274 | $this->deleteById($entryid,$userID); | 276 | } else { |
277 | return false; | ||
275 | } | 278 | } |
276 | } | 279 | } |
277 | 280 | ||
@@ -302,7 +305,7 @@ class Database { | |||
302 | $query = $this->executeQuery($sql, array($user_id)); | 305 | $query = $this->executeQuery($sql, array($user_id)); |
303 | $entries = $query->fetchAll(); | 306 | $entries = $query->fetchAll(); |
304 | 307 | ||
305 | return $entries; | 308 | return ($query) ? $entries : false; |
306 | } | 309 | } |
307 | 310 | ||
308 | public function retrieveUnfetchedEntriesCount($user_id) | 311 | public function retrieveUnfetchedEntriesCount($user_id) |
@@ -320,44 +323,44 @@ class Database { | |||
320 | $query = $this->executeQuery($sql, array($user_id)); | 323 | $query = $this->executeQuery($sql, array($user_id)); |
321 | $entries = $query->fetchAll(); | 324 | $entries = $query->fetchAll(); |
322 | 325 | ||
323 | return $entries; | 326 | return ($query) ? $entries : false; |
324 | } | 327 | } |
325 | 328 | ||
326 | public function retrieveAllWithTags($user_id) | 329 | public function retrieveAllWithTags($user_id) |
327 | { | 330 | { |
328 | $entries = $this->retrieveAll($user_id); | 331 | $entries = $this->retrieveAll($user_id); |
329 | $count = count($entries); | 332 | if ($entries) { |
330 | for ($i = 0; $i < $count; $i++) { | 333 | $count = count($entries); |
331 | $tag_entries = $this->retrieveTagsByEntry($entries[$i]['id']); | 334 | for ($i = 0; $i < $count; $i++) { |
332 | $tags = []; | 335 | $tag_entries = $this->retrieveTagsByEntry($entries[$i]['id']); |
333 | foreach ($tag_entries as $tag) { | 336 | $tags = []; |
334 | $tags[] = $tag[1]; | 337 | foreach ($tag_entries as $tag) { |
335 | } | 338 | $tags[] = $tag[1]; |
336 | $entries[$i]['tags'] = implode(',', $tags); | 339 | } |
340 | $entries[$i]['tags'] = implode(',', $tags); | ||
341 | } | ||
337 | } | 342 | } |
338 | return $entries; | 343 | return $entries; |
339 | } | 344 | } |
340 | 345 | ||
341 | public function retrieveOneById($id, $user_id) | 346 | public function retrieveOneById($id, $user_id) |
342 | { | 347 | { |
343 | $entry = NULL; | ||
344 | $sql = "SELECT * FROM entries WHERE id=? AND user_id=?"; | 348 | $sql = "SELECT * FROM entries WHERE id=? AND user_id=?"; |
345 | $params = array(intval($id), $user_id); | 349 | $params = array(intval($id), $user_id); |
346 | $query = $this->executeQuery($sql, $params); | 350 | $query = $this->executeQuery($sql, $params); |
347 | $entry = $query->fetchAll(); | 351 | $entry = $query->fetchAll(); |
348 | 352 | ||
349 | return isset($entry[0]) ? $entry[0] : null; | 353 | return ($query) ? $entry[0] : false; |
350 | } | 354 | } |
351 | 355 | ||
352 | public function retrieveOneByURL($url, $user_id) | 356 | public function retrieveOneByURL($url, $user_id) |
353 | { | 357 | { |
354 | $entry = NULL; | ||
355 | $sql = "SELECT * FROM entries WHERE url=? AND user_id=?"; | 358 | $sql = "SELECT * FROM entries WHERE url=? AND user_id=?"; |
356 | $params = array($url, $user_id); | 359 | $params = array($url, $user_id); |
357 | $query = $this->executeQuery($sql, $params); | 360 | $query = $this->executeQuery($sql, $params); |
358 | $entry = $query->fetchAll(); | 361 | $entry = $query->fetchAll(); |
359 | 362 | ||
360 | return isset($entry[0]) ? $entry[0] : null; | 363 | return ($query) ? $entry[0] : false; |
361 | } | 364 | } |
362 | 365 | ||
363 | public function reassignTags($old_entry_id, $new_entry_id) | 366 | public function reassignTags($old_entry_id, $new_entry_id) |
@@ -395,7 +398,8 @@ class Database { | |||
395 | $query = $this->executeQuery($sql, $params); | 398 | $query = $this->executeQuery($sql, $params); |
396 | $entries = $query->fetchAll(); | 399 | $entries = $query->fetchAll(); |
397 | 400 | ||
398 | return $entries; | 401 | return ($query) ? $entries : false; |
402 | |||
399 | } | 403 | } |
400 | 404 | ||
401 | public function getEntriesByViewCount($view, $user_id, $tag_id = 0) | 405 | public function getEntriesByViewCount($view, $user_id, $tag_id = 0) |
@@ -422,7 +426,7 @@ class Database { | |||
422 | } | 426 | } |
423 | 427 | ||
424 | $query = $this->executeQuery($sql, $params); | 428 | $query = $this->executeQuery($sql, $params); |
425 | list($count) = $query->fetch(); | 429 | list($count) = ($query) ? $query->fetch() : array(false); |
426 | 430 | ||
427 | return $count; | 431 | return $count; |
428 | } | 432 | } |
@@ -445,26 +449,34 @@ class Database { | |||
445 | $query = $this->executeQuery($sql, $params); | 449 | $query = $this->executeQuery($sql, $params); |
446 | $id = $query->fetchAll(); | 450 | $id = $query->fetchAll(); |
447 | 451 | ||
448 | return $id; | 452 | return ($query) ? $id : false; |
449 | } | 453 | } |
450 | 454 | ||
451 | public function getPreviousArticle($id, $user_id) | 455 | public function getPreviousArticle($id, $user_id) |
452 | { | 456 | { |
453 | $sql = "SELECT id FROM entries WHERE id = (SELECT max(id) FROM entries WHERE id < ? AND is_read=0) AND user_id=? AND is_read=0"; | 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; | ||
454 | $params = array($id, $user_id); | 462 | $params = array($id, $user_id); |
455 | $query = $this->executeQuery($sql, $params); | 463 | $query = $this->executeQuery($sql, $params); |
456 | $id_entry = $query->fetchAll(); | 464 | $id_entry = ($query) ? $query->fetchAll() : false; |
457 | $id = $id_entry[0][0]; | 465 | $id = ($query) ? $id_entry[0][0] : false; |
458 | return $id; | 466 | return $id; |
459 | } | 467 | } |
460 | 468 | ||
461 | public function getNextArticle($id, $user_id) | 469 | public function getNextArticle($id, $user_id) |
462 | { | 470 | { |
463 | $sql = "SELECT id FROM entries WHERE id = (SELECT min(id) FROM entries WHERE id > ? AND is_read=0) AND user_id=? AND is_read=0"; | 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; | ||
464 | $params = array($id, $user_id); | 476 | $params = array($id, $user_id); |
465 | $query = $this->executeQuery($sql, $params); | 477 | $query = $this->executeQuery($sql, $params); |
466 | $id_entry = $query->fetchAll(); | 478 | $id_entry = ($query) ? $query->fetchAll() : false; |
467 | $id = $id_entry[0][0]; | 479 | $id = ($query) ? $id_entry[0][0] : false; |
468 | return $id; | 480 | return $id; |
469 | } | 481 | } |
470 | 482 | ||
@@ -540,7 +552,7 @@ class Database { | |||
540 | $sql_action .= $this->getEntriesOrder().' ' . $limit; | 552 | $sql_action .= $this->getEntriesOrder().' ' . $limit; |
541 | $params_action = array($user_id, $search, $search, $search); | 553 | $params_action = array($user_id, $search, $search, $search); |
542 | $query = $this->executeQuery($sql_action, $params_action); | 554 | $query = $this->executeQuery($sql_action, $params_action); |
543 | return $query->fetchAll(); | 555 | return ($query) ? $query->fetchAll() : false; |
544 | } | 556 | } |
545 | 557 | ||
546 | public function retrieveAllTags($user_id, $term = NULL) | 558 | public function retrieveAllTags($user_id, $term = NULL) |
@@ -553,23 +565,23 @@ class Database { | |||
553 | GROUP BY tags.id, tags.value | 565 | GROUP BY tags.id, tags.value |
554 | ORDER BY tags.value"; | 566 | ORDER BY tags.value"; |
555 | $query = $this->executeQuery($sql, (($term)? array($user_id, strtolower('%'.$term.'%')) : array($user_id) )); | 567 | $query = $this->executeQuery($sql, (($term)? array($user_id, strtolower('%'.$term.'%')) : array($user_id) )); |
556 | $tags = $query->fetchAll(); | 568 | $tags = ($query) ? $query->fetchAll() : false; |
557 | 569 | ||
558 | return $tags; | 570 | return $tags; |
559 | } | 571 | } |
560 | 572 | ||
561 | public function retrieveTag($id, $user_id) | 573 | public function retrieveTag($id, $user_id) |
562 | { | 574 | { |
563 | $tag = NULL; | ||
564 | $sql = "SELECT DISTINCT tags.* FROM tags | 575 | $sql = "SELECT DISTINCT tags.* FROM tags |
565 | LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id | 576 | LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id |
566 | LEFT JOIN entries ON tags_entries.entry_id=entries.id | 577 | LEFT JOIN entries ON tags_entries.entry_id=entries.id |
567 | WHERE tags.id=? AND entries.user_id=?"; | 578 | WHERE tags.id=? AND entries.user_id=?"; |
568 | $params = array(intval($id), $user_id); | 579 | $params = array(intval($id), $user_id); |
569 | $query = $this->executeQuery($sql, $params); | 580 | $query = $this->executeQuery($sql, $params); |
570 | $tag = $query->fetchAll(); | 581 | $tags = ($query) ? $query->fetchAll() : false; |
582 | $tag = ($query) ? $tags[0] : false; | ||
571 | 583 | ||
572 | return isset($tag[0]) ? $tag[0] : NULL; | 584 | return $tag[0]; |
573 | } | 585 | } |
574 | 586 | ||
575 | public function retrieveEntriesByTag($tag_id, $user_id) | 587 | public function retrieveEntriesByTag($tag_id, $user_id) |
@@ -579,7 +591,7 @@ class Database { | |||
579 | LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id | 591 | LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id |
580 | WHERE tags_entries.tag_id = ? AND entries.user_id=? ORDER by entries.id DESC"; | 592 | WHERE tags_entries.tag_id = ? AND entries.user_id=? ORDER by entries.id DESC"; |
581 | $query = $this->executeQuery($sql, array($tag_id, $user_id)); | 593 | $query = $this->executeQuery($sql, array($tag_id, $user_id)); |
582 | $entries = $query->fetchAll(); | 594 | $entries = ($query) ? $query->fetchAll() : false; |
583 | 595 | ||
584 | return $entries; | 596 | return $entries; |
585 | } | 597 | } |
@@ -591,7 +603,7 @@ class Database { | |||
591 | LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id | 603 | LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id |
592 | WHERE tags_entries.entry_id = ?"; | 604 | WHERE tags_entries.entry_id = ?"; |
593 | $query = $this->executeQuery($sql, array($entry_id)); | 605 | $query = $this->executeQuery($sql, array($entry_id)); |
594 | $tags = $query->fetchAll(); | 606 | $tags = ($query) ? $query->fetchAll() : false; |
595 | 607 | ||
596 | return $tags; | 608 | return $tags; |
597 | } | 609 | } |
@@ -601,38 +613,40 @@ class Database { | |||
601 | $sql_action = "DELETE FROM tags_entries WHERE tag_id=? AND entry_id=?"; | 613 | $sql_action = "DELETE FROM tags_entries WHERE tag_id=? AND entry_id=?"; |
602 | $params_action = array($tag_id, $entry_id); | 614 | $params_action = array($tag_id, $entry_id); |
603 | $query = $this->executeQuery($sql_action, $params_action); | 615 | $query = $this->executeQuery($sql_action, $params_action); |
604 | return $query; | 616 | return ($query) ? $query : false; |
605 | } | 617 | } |
606 | 618 | ||
607 | public function cleanUnusedTag($tag_id) | 619 | public function cleanUnusedTag($tag_id) |
608 | { | 620 | { |
609 | $sql_action = "SELECT tags.* FROM tags JOIN tags_entries ON tags_entries.tag_id=tags.id WHERE tags.id=?"; | 621 | $sql_action = "SELECT tags.* FROM tags JOIN tags_entries ON tags_entries.tag_id=tags.id WHERE tags.id=?"; |
610 | $query = $this->executeQuery($sql_action,array($tag_id)); | 622 | $query = $this->executeQuery($sql_action,array($tag_id)); |
611 | $tagstokeep = $query->fetchAll(); | 623 | $tagstokeep = ($query) ? $query->fetchAll() : false; |
612 | $sql_action = "SELECT tags.* FROM tags LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id WHERE tags.id=?"; | 624 | $sql_action = "SELECT tags.* FROM tags LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id WHERE tags.id=?"; |
613 | $query = $this->executeQuery($sql_action,array($tag_id)); | 625 | $query = $this->executeQuery($sql_action,array($tag_id)); |
614 | $alltags = $query->fetchAll(); | 626 | $alltags = ($query) ? $query->fetchAll() : false; |
615 | 627 | ||
616 | foreach ($alltags as $tag) { | 628 | if ($tagstokeep && $alltags) { |
617 | if ($tag && !in_array($tag,$tagstokeep)) { | 629 | foreach ($alltags as $tag) { |
618 | $sql_action = "DELETE FROM tags WHERE id=?"; | 630 | if ($tag && !in_array($tag,$tagstokeep)) { |
619 | $params_action = array($tag[0]); | 631 | $sql_action = "DELETE FROM tags WHERE id=?"; |
620 | $this->executeQuery($sql_action, $params_action); | 632 | $params_action = array($tag[0]); |
621 | return true; | 633 | $this->executeQuery($sql_action, $params_action); |
634 | return true; | ||
635 | } | ||
622 | } | 636 | } |
637 | } else { | ||
638 | return false; | ||
623 | } | 639 | } |
624 | |||
625 | } | 640 | } |
626 | 641 | ||
627 | public function retrieveTagByValue($value) | 642 | public function retrieveTagByValue($value) |
628 | { | 643 | { |
629 | $tag = NULL; | ||
630 | $sql = "SELECT * FROM tags WHERE value=?"; | 644 | $sql = "SELECT * FROM tags WHERE value=?"; |
631 | $params = array($value); | 645 | $params = array($value); |
632 | $query = $this->executeQuery($sql, $params); | 646 | $query = $this->executeQuery($sql, $params); |
633 | $tag = $query->fetchAll(); | 647 | $tag = ($query) ? $query->fetchAll() : false; |
634 | 648 | ||
635 | return isset($tag[0]) ? $tag[0] : null; | 649 | return ($query) ? $tag[0] : false; |
636 | } | 650 | } |
637 | 651 | ||
638 | public function createTag($value) | 652 | public function createTag($value) |
@@ -640,7 +654,7 @@ class Database { | |||
640 | $sql_action = 'INSERT INTO tags ( value ) VALUES (?)'; | 654 | $sql_action = 'INSERT INTO tags ( value ) VALUES (?)'; |
641 | $params_action = array($value); | 655 | $params_action = array($value); |
642 | $query = $this->executeQuery($sql_action, $params_action); | 656 | $query = $this->executeQuery($sql_action, $params_action); |
643 | return $query; | 657 | return ($query) ? $query : false; |
644 | } | 658 | } |
645 | 659 | ||
646 | public function setTagToEntry($tag_id, $entry_id) | 660 | public function setTagToEntry($tag_id, $entry_id) |
@@ -648,7 +662,7 @@ class Database { | |||
648 | $sql_action = 'INSERT INTO tags_entries ( tag_id, entry_id ) VALUES (?, ?)'; | 662 | $sql_action = 'INSERT INTO tags_entries ( tag_id, entry_id ) VALUES (?, ?)'; |
649 | $params_action = array($tag_id, $entry_id); | 663 | $params_action = array($tag_id, $entry_id); |
650 | $query = $this->executeQuery($sql_action, $params_action); | 664 | $query = $this->executeQuery($sql_action, $params_action); |
651 | return $query; | 665 | return ($query) ? $query : false; |
652 | } | 666 | } |
653 | 667 | ||
654 | private function getEntriesOrder() | 668 | private function getEntriesOrder() |