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