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://www.wtfpl.net/ 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         }
  37
  38         $this->handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  39         Tools::logm('storage type ' . STORAGE);
  40     }
  41
  42     private function getHandle() {
  43         return $this->handle;
  44     }
  45
  46     public function isInstalled() {
  47         $sql = "SELECT username FROM users";
  48         $query = $this->executeQuery($sql, array());
  49         if ($query == false) {
  50             die(STORAGE . ' database looks empty. You have to create it (you can find database structure in install folder).');
  51         }
  52         $hasAdmin = count($query->fetchAll());
  53
  54         if ($hasAdmin == 0)
  55             return false;
  56
  57         return true;
  58     }
  59
  60     public function checkTags() {
  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) {
 125         $sql = 'INSERT INTO users ( username, password, name, email) VALUES (?, ?, ?, ?)';
 126         $params = array($login, $password, $login, ' ');
 127         $query = $this->executeQuery($sql, $params);
 128
 129         $sequence = '';
 130         if (STORAGE == 'postgres') {
 131             $sequence = 'users_id_seq';
 132         }
 133
 134         $id_user = intval($this->getLastId($sequence));
 135
 136         $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)';
 137         $params = array($id_user, 'pager', PAGINATION);
 138         $query = $this->executeQuery($sql, $params);
 139
 140         $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)';
 141         $params = array($id_user, 'language', LANG);
 142         $query = $this->executeQuery($sql, $params);
 143
 144         $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)';
 145         $params = array($id_user, 'theme', DEFAULT_THEME);
 146         $query = $this->executeQuery($sql, $params);
 147
 148         return TRUE;
 149     }
 150
 151     public function getConfigUser($id) {
 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         $sql = "SELECT * FROM users WHERE username=?";
 166         $query = $this->executeQuery($sql, array($username));
 167         $login = $query->fetchAll();
 168         if (isset($login[0])) {
 169             return true;
 170         } else {
 171             return false;
 172         }
 173     }
 174
 175     public function login($username, $password, $isauthenticated=false) {
 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         $config = $this->getConfigUser($userId);
 207
 208         if (! isset($config[$key])) {
 209             $sql = "INSERT INTO users_config (value, user_id, name) VALUES (?, ?, ?)";
 210         }
 211         else {
 212             $sql = "UPDATE users_config SET value=? WHERE user_id=? AND name=?";
 213         }
 214
 215         $params = array($value, $userId, $key);
 216         $query = $this->executeQuery($sql, $params);
 217     }
 218
 219     private function executeQuery($sql, $params) {
 220         try
 221         {
 222             $query = $this->getHandle()->prepare($sql);
 223             $query->execute($params);
 224             return $query;
 225         }
 226         catch (Exception $e)
 227         {
 228             Tools::logm('execute query error : '.$e->getMessage());
 229             return FALSE;
 230         }
 231     }
 232
 233     public function updateContentAndTitle($id, $title, $body, $user_id) {
 234         $sql_action = 'UPDATE entries SET content = ?, title = ? WHERE id=? AND user_id=?';
 235         $params_action = array($body, $title, $id, $user_id);
 236         $query = $this->executeQuery($sql_action, $params_action);
 237
 238         return $query;
 239     }
 240
 241     public function retrieveUnfetchedEntries($user_id, $limit) {
 242
 243         $sql_limit = "LIMIT 0,".$limit;
 244         if (STORAGE == 'postgres') {
 245             $sql_limit = "LIMIT ".$limit." OFFSET 0";
 246         }
 247
 248         $sql        = "SELECT * FROM entries WHERE (content = '' OR content IS NULL) AND user_id=? ORDER BY id " . $sql_limit;
 249         $query      = $this->executeQuery($sql, array($user_id));
 250         $entries    = $query->fetchAll();
 251
 252         return $entries;
 253     }
 254
 255     public function retrieveUnfetchedEntriesCount($user_id) {
 256       $sql        = "SELECT count(*) FROM entries WHERE (content = '' OR content IS NULL) AND user_id=?";
 257       $query      = $this->executeQuery($sql, array($user_id));
 258       list($count) = $query->fetch();
 259
 260       return $count;
 261     }
 262
 263     public function retrieveAll($user_id) {
 264         $sql        = "SELECT * FROM entries WHERE user_id=? ORDER BY id";
 265         $query      = $this->executeQuery($sql, array($user_id));
 266         $entries    = $query->fetchAll();
 267
 268         return $entries;
 269     }
 270
 271     public function retrieveOneById($id, $user_id) {
 272         $entry  = NULL;
 273         $sql    = "SELECT * FROM entries WHERE id=? AND user_id=?";
 274         $params = array(intval($id), $user_id);
 275         $query  = $this->executeQuery($sql, $params);
 276         $entry  = $query->fetchAll();
 277
 278         return isset($entry[0]) ? $entry[0] : null;
 279     }
 280
 281     public function retrieveOneByURL($url, $user_id) {
 282         $entry  = NULL;
 283         $sql    = "SELECT * FROM entries WHERE url=? AND user_id=?";
 284         $params = array($url, $user_id);
 285         $query  = $this->executeQuery($sql, $params);
 286         $entry  = $query->fetchAll();
 287
 288         return isset($entry[0]) ? $entry[0] : null;
 289     }
 290
 291     public function reassignTags($old_entry_id, $new_entry_id) {
 292         $sql    = "UPDATE tags_entries SET entry_id=? WHERE entry_id=?";
 293         $params = array($new_entry_id, $old_entry_id);
 294         $query  = $this->executeQuery($sql, $params);
 295     }
 296
 297     public function getEntriesByView($view, $user_id, $limit = '', $tag_id = 0) {
 298         switch ($view) {
 299             case 'archive':
 300                 $sql    = "SELECT * FROM entries WHERE user_id=? AND is_read=? ";
 301                 $params = array($user_id, 1);
 302                 break;
 303             case 'fav' :
 304                 $sql    = "SELECT * FROM entries WHERE user_id=? AND is_fav=? ";
 305                 $params = array($user_id, 1);
 306                 break;
 307             case 'tag' :
 308                 $sql    = "SELECT entries.* FROM entries
 309                 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
 310                 WHERE entries.user_id=? AND tags_entries.tag_id = ? ";
 311                 $params = array($user_id, $tag_id);
 312                 break;
 313             default:
 314                 $sql    = "SELECT * FROM entries WHERE user_id=? AND is_read=? ";
 315                 $params = array($user_id, 0);
 316                 break;
 317         }
 318
 319                 $sql .= $this->getEntriesOrder().' ' . $limit;
 320
 321                 $query = $this->executeQuery($sql, $params);
 322                 $entries = $query->fetchAll();
 323
 324                 return $entries;
 325         }
 326
 327     public function getEntriesByViewCount($view, $user_id, $tag_id = 0) {
 328         switch ($view) {
 329             case 'archive':
 330                     $sql    = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? ";
 331                 $params = array($user_id, 1);
 332                 break;
 333             case 'fav' :
 334                     $sql    = "SELECT count(*) FROM entries WHERE user_id=? AND is_fav=? ";
 335                 $params = array($user_id, 1);
 336                 break;
 337             case 'tag' :
 338                 $sql    = "SELECT count(*) FROM entries
 339                     LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
 340                     WHERE entries.user_id=? AND tags_entries.tag_id = ? ";
 341                 $params = array($user_id, $tag_id);
 342                 break;
 343             default:
 344                 $sql    = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? ";
 345                 $params = array($user_id, 0);
 346                 break;
 347         }
 348
 349         $query = $this->executeQuery($sql, $params);
 350         list($count) = $query->fetch();
 351
 352         return $count;
 353     }
 354
 355     public function updateContent($id, $content, $user_id) {
 356         $sql_action = 'UPDATE entries SET content = ? WHERE id=? AND user_id=?';
 357         $params_action = array($content, $id, $user_id);
 358         $query = $this->executeQuery($sql_action, $params_action);
 359         return $query;
 360     }
 361
 362     /**
 363      *
 364      * @param string $url
 365      * @param string $title
 366      * @param string $content
 367      * @param integer $user_id
 368      * @return integer $id of inserted record
 369      */
 370     public function add($url, $title, $content, $user_id, $isFavorite=0, $isRead=0) {
 371         $sql_action = 'INSERT INTO entries ( url, title, content, user_id, is_fav, is_read ) VALUES (?, ?, ?, ?, ?, ?)';
 372         $params_action = array($url, $title, $content, $user_id, $isFavorite, $isRead);
 373         if ( !$this->executeQuery($sql_action, $params_action) ) {
 374           $id = null;
 375         }
 376         else {
 377           $id = intval($this->getLastId( (STORAGE == 'postgres') ? 'entries_id_seq' : '') );
 378         }
 379         return $id;
 380     }
 381
 382     public function deleteById($id, $user_id) {
 383         $sql_action     = "DELETE FROM entries WHERE id=? AND user_id=?";
 384         $params_action  = array($id, $user_id);
 385         $query          = $this->executeQuery($sql_action, $params_action);
 386         return $query;
 387     }
 388
 389     public function favoriteById($id, $user_id) {
 390         $sql_action     = "UPDATE entries SET is_fav=NOT is_fav WHERE id=? AND user_id=?";
 391         $params_action  = array($id, $user_id);
 392         $query          = $this->executeQuery($sql_action, $params_action);
 393     }
 394
 395     public function archiveById($id, $user_id) {
 396         $sql_action     = "UPDATE entries SET is_read=NOT is_read WHERE id=? AND user_id=?";
 397         $params_action  = array($id, $user_id);
 398         $query          = $this->executeQuery($sql_action, $params_action);
 399     }
 400
 401     public function archiveAll($user_id) {
 402         $sql_action     = "UPDATE entries SET is_read=? WHERE user_id=? AND is_read=?";
 403         $params_action  = array($user_id, 1, 0);
 404         $query          = $this->executeQuery($sql_action, $params_action);
 405     }
 406
 407     public function getLastId($column = '') {
 408         return $this->getHandle()->lastInsertId($column);
 409     }
 410
 411     public function search($term, $user_id, $limit = '') {
 412         $search = '%'.$term.'%';
 413         $sql_action = "SELECT * FROM entries WHERE user_id=? AND (content LIKE ? OR title LIKE ? OR url LIKE ?) "; //searches in content, title and URL
 414         $sql_action .= $this->getEntriesOrder().' ' . $limit;
 415         $params_action = array($user_id, $search, $search, $search);
 416         $query = $this->executeQuery($sql_action, $params_action);
 417         return $query->fetchAll();
 418         }
 419
 420     public function retrieveAllTags($user_id, $term = null) {
 421         $sql = "SELECT DISTINCT tags.*, count(entries.id) AS entriescount FROM tags
 422           LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
 423           LEFT JOIN entries ON tags_entries.entry_id=entries.id
 424           WHERE entries.user_id=?
 425             ". (($term) ? "AND lower(tags.value) LIKE ?" : '') ."
 426           GROUP BY tags.id, tags.value
 427           ORDER BY tags.value";
 428         $query = $this->executeQuery($sql, (($term)? array($user_id, strtolower('%'.$term.'%')) : array($user_id) ));
 429         $tags = $query->fetchAll();
 430
 431         return $tags;
 432     }
 433
 434     public function retrieveTag($id, $user_id) {
 435         $tag  = NULL;
 436         $sql    = "SELECT DISTINCT tags.* FROM tags
 437           LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
 438           LEFT JOIN entries ON tags_entries.entry_id=entries.id
 439           WHERE tags.id=? AND entries.user_id=?";
 440         $params = array(intval($id), $user_id);
 441         $query  = $this->executeQuery($sql, $params);
 442         $tag  = $query->fetchAll();
 443
 444         return isset($tag[0]) ? $tag[0] : null;
 445     }
 446
 447     public function retrieveEntriesByTag($tag_id, $user_id) {
 448         $sql =
 449             "SELECT entries.* FROM entries
 450             LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
 451             WHERE tags_entries.tag_id = ? AND entries.user_id=?";
 452         $query = $this->executeQuery($sql, array($tag_id, $user_id));
 453         $entries = $query->fetchAll();
 454
 455         return $entries;
 456     }
 457
 458     public function retrieveTagsByEntry($entry_id) {
 459         $sql =
 460             "SELECT tags.* FROM tags
 461             LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
 462             WHERE tags_entries.entry_id = ?";
 463         $query = $this->executeQuery($sql, array($entry_id));
 464         $tags = $query->fetchAll();
 465
 466         return $tags;
 467     }
 468
 469     public function removeTagForEntry($entry_id, $tag_id) {
 470         $sql_action     = "DELETE FROM tags_entries WHERE tag_id=? AND entry_id=?";
 471         $params_action  = array($tag_id, $entry_id);
 472         $query          = $this->executeQuery($sql_action, $params_action);
 473         return $query;
 474     }
 475
 476     public function retrieveTagByValue($value) {
 477         $tag  = NULL;
 478         $sql    = "SELECT * FROM tags WHERE value=?";
 479         $params = array($value);
 480         $query  = $this->executeQuery($sql, $params);
 481         $tag  = $query->fetchAll();
 482
 483         return isset($tag[0]) ? $tag[0] : null;
 484     }
 485
 486     public function createTag($value) {
 487         $sql_action = 'INSERT INTO tags ( value ) VALUES (?)';
 488         $params_action = array($value);
 489         $query = $this->executeQuery($sql_action, $params_action);
 490         return $query;
 491     }
 492
 493     public function setTagToEntry($tag_id, $entry_id) {
 494         $sql_action = 'INSERT INTO tags_entries ( tag_id, entry_id ) VALUES (?, ?)';
 495         $params_action = array($tag_id, $entry_id);
 496         $query = $this->executeQuery($sql_action, $params_action);
 497         return $query;
 498     }
 499
 500         private function getEntriesOrder() {
 501             if (isset($_SESSION['sort']) and array_key_exists($_SESSION['sort'], $this->order)) {
 502                 return $this->order[$_SESSION['sort']];
 503             }
 504             else {
 505                 return $this->order['default'];
 506             }
 507         }
 508
 509 }