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