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