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