aboutsummaryrefslogtreecommitdiffhomepage
path: root/inc/poche/Database.class.php
diff options
context:
space:
mode:
authorNicolas LÅ“uillet <nicolas@loeuillet.org>2015-08-03 13:21:11 +0200
committerNicolas LÅ“uillet <nicolas@loeuillet.org>2015-08-03 13:21:11 +0200
commitc766284d522206578e7ec154961e0fce456f70c8 (patch)
treea5246151bf8278a0e8053b9dd1ce9d770073c50b /inc/poche/Database.class.php
parentc0cb52c07439c974075c302a190a034a8cce4f92 (diff)
parente968ec6c2244aee600358b3c87648b2409c97945 (diff)
downloadwallabag-c766284d522206578e7ec154961e0fce456f70c8.tar.gz
wallabag-c766284d522206578e7ec154961e0fce456f70c8.tar.zst
wallabag-c766284d522206578e7ec154961e0fce456f70c8.zip
Merge pull request #1267 from wallabag/dev1.9.1
1.9.1
Diffstat (limited to 'inc/poche/Database.class.php')
-rwxr-xr-xinc/poche/Database.class.php165
1 files changed, 113 insertions, 52 deletions
diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php
index 65675afe..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,29 +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
326 return ($query) ? $entries : false;
327 }
328
329 public function retrieveAllWithTags($user_id)
330 {
331 $entries = $this->retrieveAll($user_id);
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 }
342 }
323 return $entries; 343 return $entries;
324 } 344 }
325 345
326 public function retrieveOneById($id, $user_id) 346 public function retrieveOneById($id, $user_id)
327 { 347 {
328 $entry = NULL;
329 $sql = "SELECT * FROM entries WHERE id=? AND user_id=?"; 348 $sql = "SELECT * FROM entries WHERE id=? AND user_id=?";
330 $params = array(intval($id), $user_id); 349 $params = array(intval($id), $user_id);
331 $query = $this->executeQuery($sql, $params); 350 $query = $this->executeQuery($sql, $params);
332 $entry = $query->fetchAll(); 351 $entry = $query->fetchAll();
333 352
334 return isset($entry[0]) ? $entry[0] : null; 353 return ($query) ? $entry[0] : false;
335 } 354 }
336 355
337 public function retrieveOneByURL($url, $user_id) 356 public function retrieveOneByURL($url, $user_id)
338 { 357 {
339 $entry = NULL;
340 $sql = "SELECT * FROM entries WHERE url=? AND user_id=?"; 358 $sql = "SELECT * FROM entries WHERE url=? AND user_id=?";
341 $params = array($url, $user_id); 359 $params = array($url, $user_id);
342 $query = $this->executeQuery($sql, $params); 360 $query = $this->executeQuery($sql, $params);
343 $entry = $query->fetchAll(); 361 $entry = $query->fetchAll();
344 362
345 return isset($entry[0]) ? $entry[0] : null; 363 return ($query) ? $entry[0] : false;
346 } 364 }
347 365
348 public function reassignTags($old_entry_id, $new_entry_id) 366 public function reassignTags($old_entry_id, $new_entry_id)
@@ -380,7 +398,8 @@ class Database {
380 $query = $this->executeQuery($sql, $params); 398 $query = $this->executeQuery($sql, $params);
381 $entries = $query->fetchAll(); 399 $entries = $query->fetchAll();
382 400
383 return $entries; 401 return ($query) ? $entries : false;
402
384 } 403 }
385 404
386 public function getEntriesByViewCount($view, $user_id, $tag_id = 0) 405 public function getEntriesByViewCount($view, $user_id, $tag_id = 0)
@@ -407,17 +426,57 @@ class Database {
407 } 426 }
408 427
409 $query = $this->executeQuery($sql, $params); 428 $query = $this->executeQuery($sql, $params);
410 list($count) = $query->fetch(); 429 list($count) = ($query) ? $query->fetch() : array(false);
411 430
412 return $count; 431 return $count;
413 } 432 }
414 public function getRandomId($user_id) { 433 public function getRandomId($user_id, $view) {
415 $random = (STORAGE == 'mysql') ? 'RAND()' : 'RANDOM()'; 434 $random = (STORAGE == 'mysql') ? 'RAND()' : 'RANDOM()';
416 $sql = "SELECT id FROM entries WHERE user_id=? ORDER BY ". $random . " LIMIT 1"; 435 switch ($view) {
417 $params = array($user_id); 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 }
418 $query = $this->executeQuery($sql, $params); 449 $query = $this->executeQuery($sql, $params);
419 $id = $query->fetchAll(); 450 $id = $query->fetchAll();
420 451
452 return ($query) ? $id : false;
453 }
454
455 public function getPreviousArticle($id, $user_id)
456 {
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;
462 $params = array($id, $user_id);
463 $query = $this->executeQuery($sql, $params);
464 $id_entry = ($query) ? $query->fetchAll() : false;
465 $id = ($query) ? $id_entry[0][0] : false;
466 return $id;
467 }
468
469 public function getNextArticle($id, $user_id)
470 {
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;
476 $params = array($id, $user_id);
477 $query = $this->executeQuery($sql, $params);
478 $id_entry = ($query) ? $query->fetchAll() : false;
479 $id = ($query) ? $id_entry[0][0] : false;
421 return $id; 480 return $id;
422 } 481 }
423 482
@@ -493,7 +552,7 @@ class Database {
493 $sql_action .= $this->getEntriesOrder().' ' . $limit; 552 $sql_action .= $this->getEntriesOrder().' ' . $limit;
494 $params_action = array($user_id, $search, $search, $search); 553 $params_action = array($user_id, $search, $search, $search);
495 $query = $this->executeQuery($sql_action, $params_action); 554 $query = $this->executeQuery($sql_action, $params_action);
496 return $query->fetchAll(); 555 return ($query) ? $query->fetchAll() : false;
497 } 556 }
498 557
499 public function retrieveAllTags($user_id, $term = NULL) 558 public function retrieveAllTags($user_id, $term = NULL)
@@ -506,23 +565,23 @@ class Database {
506 GROUP BY tags.id, tags.value 565 GROUP BY tags.id, tags.value
507 ORDER BY tags.value"; 566 ORDER BY tags.value";
508 $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) ));
509 $tags = $query->fetchAll(); 568 $tags = ($query) ? $query->fetchAll() : false;
510 569
511 return $tags; 570 return $tags;
512 } 571 }
513 572
514 public function retrieveTag($id, $user_id) 573 public function retrieveTag($id, $user_id)
515 { 574 {
516 $tag = NULL;
517 $sql = "SELECT DISTINCT tags.* FROM tags 575 $sql = "SELECT DISTINCT tags.* FROM tags
518 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id 576 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
519 LEFT JOIN entries ON tags_entries.entry_id=entries.id 577 LEFT JOIN entries ON tags_entries.entry_id=entries.id
520 WHERE tags.id=? AND entries.user_id=?"; 578 WHERE tags.id=? AND entries.user_id=?";
521 $params = array(intval($id), $user_id); 579 $params = array(intval($id), $user_id);
522 $query = $this->executeQuery($sql, $params); 580 $query = $this->executeQuery($sql, $params);
523 $tag = $query->fetchAll(); 581 $tags = ($query) ? $query->fetchAll() : false;
582 $tag = ($query) ? $tags[0] : false;
524 583
525 return isset($tag[0]) ? $tag[0] : NULL; 584 return $tag[0];
526 } 585 }
527 586
528 public function retrieveEntriesByTag($tag_id, $user_id) 587 public function retrieveEntriesByTag($tag_id, $user_id)
@@ -532,7 +591,7 @@ class Database {
532 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id 591 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
533 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";
534 $query = $this->executeQuery($sql, array($tag_id, $user_id)); 593 $query = $this->executeQuery($sql, array($tag_id, $user_id));
535 $entries = $query->fetchAll(); 594 $entries = ($query) ? $query->fetchAll() : false;
536 595
537 return $entries; 596 return $entries;
538 } 597 }
@@ -544,7 +603,7 @@ class Database {
544 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id 603 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
545 WHERE tags_entries.entry_id = ?"; 604 WHERE tags_entries.entry_id = ?";
546 $query = $this->executeQuery($sql, array($entry_id)); 605 $query = $this->executeQuery($sql, array($entry_id));
547 $tags = $query->fetchAll(); 606 $tags = ($query) ? $query->fetchAll() : false;
548 607
549 return $tags; 608 return $tags;
550 } 609 }
@@ -554,38 +613,40 @@ class Database {
554 $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=?";
555 $params_action = array($tag_id, $entry_id); 614 $params_action = array($tag_id, $entry_id);
556 $query = $this->executeQuery($sql_action, $params_action); 615 $query = $this->executeQuery($sql_action, $params_action);
557 return $query; 616 return ($query) ? $query : false;
558 } 617 }
559 618
560 public function cleanUnusedTag($tag_id) 619 public function cleanUnusedTag($tag_id)
561 { 620 {
562 $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=?";
563 $query = $this->executeQuery($sql_action,array($tag_id)); 622 $query = $this->executeQuery($sql_action,array($tag_id));
564 $tagstokeep = $query->fetchAll(); 623 $tagstokeep = ($query) ? $query->fetchAll() : false;
565 $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=?";
566 $query = $this->executeQuery($sql_action,array($tag_id)); 625 $query = $this->executeQuery($sql_action,array($tag_id));
567 $alltags = $query->fetchAll(); 626 $alltags = ($query) ? $query->fetchAll() : false;
568 627
569 foreach ($alltags as $tag) { 628 if ($tagstokeep && $alltags) {
570 if ($tag && !in_array($tag,$tagstokeep)) { 629 foreach ($alltags as $tag) {
571 $sql_action = "DELETE FROM tags WHERE id=?"; 630 if ($tag && !in_array($tag,$tagstokeep)) {
572 $params_action = array($tag[0]); 631 $sql_action = "DELETE FROM tags WHERE id=?";
573 $this->executeQuery($sql_action, $params_action); 632 $params_action = array($tag[0]);
574 return true; 633 $this->executeQuery($sql_action, $params_action);
634 return true;
635 }
575 } 636 }
637 } else {
638 return false;
576 } 639 }
577
578 } 640 }
579 641
580 public function retrieveTagByValue($value) 642 public function retrieveTagByValue($value)
581 { 643 {
582 $tag = NULL;
583 $sql = "SELECT * FROM tags WHERE value=?"; 644 $sql = "SELECT * FROM tags WHERE value=?";
584 $params = array($value); 645 $params = array($value);
585 $query = $this->executeQuery($sql, $params); 646 $query = $this->executeQuery($sql, $params);
586 $tag = $query->fetchAll(); 647 $tag = ($query) ? $query->fetchAll() : false;
587 648
588 return isset($tag[0]) ? $tag[0] : null; 649 return ($query) ? $tag[0] : false;
589 } 650 }
590 651
591 public function createTag($value) 652 public function createTag($value)
@@ -593,7 +654,7 @@ class Database {
593 $sql_action = 'INSERT INTO tags ( value ) VALUES (?)'; 654 $sql_action = 'INSERT INTO tags ( value ) VALUES (?)';
594 $params_action = array($value); 655 $params_action = array($value);
595 $query = $this->executeQuery($sql_action, $params_action); 656 $query = $this->executeQuery($sql_action, $params_action);
596 return $query; 657 return ($query) ? $query : false;
597 } 658 }
598 659
599 public function setTagToEntry($tag_id, $entry_id) 660 public function setTagToEntry($tag_id, $entry_id)
@@ -601,7 +662,7 @@ class Database {
601 $sql_action = 'INSERT INTO tags_entries ( tag_id, entry_id ) VALUES (?, ?)'; 662 $sql_action = 'INSERT INTO tags_entries ( tag_id, entry_id ) VALUES (?, ?)';
602 $params_action = array($tag_id, $entry_id); 663 $params_action = array($tag_id, $entry_id);
603 $query = $this->executeQuery($sql_action, $params_action); 664 $query = $this->executeQuery($sql_action, $params_action);
604 return $query; 665 return ($query) ? $query : false;
605 } 666 }
606 667
607 private function getEntriesOrder() 668 private function getEntriesOrder()