src/Wallabag/Wallabag/Database.php

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