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 listUsers($username=null) {
 234         $sql = 'SELECT count(*) FROM users'.( $username ? ' WHERE username=?' : '');
 235         $query = $this->executeQuery($sql, ( $username ? array($username) : array()));
 236         list($count) = $query->fetch();
 237         return $count;
 238     }
 239
 240     public function getUserPassword($userID) {
 241         $sql = "SELECT * FROM users WHERE id=?";
 242         $query = $this->executeQuery($sql, array($userID));
 243         $password = $query->fetchAll();
 244         return isset($password[0]['password']) ? $password[0]['password'] : null;
 245     }
 246
 247     public function deleteUserConfig($userID) {
 248         $sql_action = 'DELETE from users_config WHERE user_id=?';
 249         $params_action = array($userID);
 250         $query = $this->executeQuery($sql_action, $params_action);
 251         return $query;
 252     }
 253
 254     public function deleteTagsEntriesAndEntries($userID) {
 255         $entries = $this->retrieveAll($userID);
 256         foreach($entries as $entryid) {
 257             $tags = $this->retrieveTagsByEntry($entryid);
 258             foreach($tags as $tag) {
 259                 $this->removeTagForEntry($entryid,$tags);
 260             }
 261             $this->deleteById($entryid,$userID);
 262         }
 263     }
 264
 265     public function deleteUser($userID) {
 266         $sql_action = 'DELETE from users WHERE id=?';
 267         $params_action = array($userID);
 268         $query = $this->executeQuery($sql_action, $params_action);
 269     }
 270
 271     public function updateContentAndTitle($id, $title, $body, $user_id) {
 272         $sql_action = 'UPDATE entries SET content = ?, title = ? WHERE id=? AND user_id=?';
 273         $params_action = array($body, $title, $id, $user_id);
 274         $query = $this->executeQuery($sql_action, $params_action);
 275         return $query;
 276     }
 277
 278     public function retrieveUnfetchedEntries($user_id, $limit) {
 279
 280         $sql_limit = "LIMIT 0,".$limit;
 281         if (STORAGE == 'postgres') {
 282             $sql_limit = "LIMIT ".$limit." OFFSET 0";
 283         }
 284
 285         $sql        = "SELECT * FROM entries WHERE (content = '' OR content IS NULL) AND title LIKE 'Untitled - Import%' AND user_id=? ORDER BY id " . $sql_limit;
 286         $query      = $this->executeQuery($sql, array($user_id));
 287         $entries    = $query->fetchAll();
 288
 289         return $entries;
 290     }
 291
 292     public function retrieveUnfetchedEntriesCount($user_id) {
 293       $sql        = "SELECT count(*) FROM entries WHERE (content = '' OR content IS NULL) AND title LIKE 'Untitled - Import%' AND user_id=?";
 294       $query      = $this->executeQuery($sql, array($user_id));
 295       list($count) = $query->fetch();
 296
 297       return $count;
 298     }
 299
 300     public function retrieveAll($user_id) {
 301         $sql        = "SELECT * FROM entries WHERE user_id=? ORDER BY id";
 302         $query      = $this->executeQuery($sql, array($user_id));
 303         $entries    = $query->fetchAll();
 304
 305         return $entries;
 306     }
 307
 308     public function retrieveOneById($id, $user_id) {
 309         $entry  = NULL;
 310         $sql    = "SELECT * FROM entries WHERE id=? AND user_id=?";
 311         $params = array(intval($id), $user_id);
 312         $query  = $this->executeQuery($sql, $params);
 313         $entry  = $query->fetchAll();
 314
 315         return isset($entry[0]) ? $entry[0] : null;
 316     }
 317
 318     public function retrieveOneByURL($url, $user_id) {
 319         $entry  = NULL;
 320         $sql    = "SELECT * FROM entries WHERE url=? AND user_id=?";
 321         $params = array($url, $user_id);
 322         $query  = $this->executeQuery($sql, $params);
 323         $entry  = $query->fetchAll();
 324
 325         return isset($entry[0]) ? $entry[0] : null;
 326     }
 327
 328     public function reassignTags($old_entry_id, $new_entry_id) {
 329         $sql    = "UPDATE tags_entries SET entry_id=? WHERE entry_id=?";
 330         $params = array($new_entry_id, $old_entry_id);
 331         $query  = $this->executeQuery($sql, $params);
 332     }
 333
 334     public function getEntriesByView($view, $user_id, $limit = '', $tag_id = 0) {
 335         switch ($view) {
 336             case 'archive':
 337                 $sql    = "SELECT * FROM entries WHERE user_id=? AND is_read=? ";
 338                 $params = array($user_id, 1);
 339                 break;
 340             case 'fav' :
 341                 $sql    = "SELECT * FROM entries WHERE user_id=? AND is_fav=? ";
 342                 $params = array($user_id, 1);
 343                 break;
 344             case 'tag' :
 345                 $sql    = "SELECT entries.* FROM entries
 346                 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
 347                 WHERE entries.user_id=? AND tags_entries.tag_id = ? ";
 348                 $params = array($user_id, $tag_id);
 349                 break;
 350             default:
 351                 $sql    = "SELECT * FROM entries WHERE user_id=? AND is_read=? ";
 352                 $params = array($user_id, 0);
 353                 break;
 354         }
 355
 356                 $sql .= $this->getEntriesOrder().' ' . $limit;
 357
 358                 $query = $this->executeQuery($sql, $params);
 359                 $entries = $query->fetchAll();
 360
 361                 return $entries;
 362         }
 363
 364     public function getEntriesByViewCount($view, $user_id, $tag_id = 0) {
 365         switch ($view) {
 366             case 'archive':
 367                     $sql    = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? ";
 368                 $params = array($user_id, 1);
 369                 break;
 370             case 'fav' :
 371                     $sql    = "SELECT count(*) FROM entries WHERE user_id=? AND is_fav=? ";
 372                 $params = array($user_id, 1);
 373                 break;
 374             case 'tag' :
 375                 $sql    = "SELECT count(*) FROM entries
 376                     LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
 377                     WHERE entries.user_id=? AND tags_entries.tag_id = ? ";
 378                 $params = array($user_id, $tag_id);
 379                 break;
 380             default:
 381                 $sql    = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? ";
 382                 $params = array($user_id, 0);
 383                 break;
 384         }
 385
 386         $query = $this->executeQuery($sql, $params);
 387         list($count) = $query->fetch();
 388
 389         return $count;
 390     }
 391
 392     public function updateContent($id, $content, $user_id) {
 393         $sql_action = 'UPDATE entries SET content = ? WHERE id=? AND user_id=?';
 394         $params_action = array($content, $id, $user_id);
 395         $query = $this->executeQuery($sql_action, $params_action);
 396         return $query;
 397     }
 398
 399     /**
 400      *
 401      * @param string $url
 402      * @param string $title
 403      * @param string $content
 404      * @param integer $user_id
 405      * @return integer $id of inserted record
 406      */
 407     public function add($url, $title, $content, $user_id, $isFavorite=0, $isRead=0) {
 408         $sql_action = 'INSERT INTO entries ( url, title, content, user_id, is_fav, is_read ) VALUES (?, ?, ?, ?, ?, ?)';
 409         $params_action = array($url, $title, $content, $user_id, $isFavorite, $isRead);
 410         if ( !$this->executeQuery($sql_action, $params_action) ) {
 411           $id = null;
 412         }
 413         else {
 414           $id = intval($this->getLastId( (STORAGE == 'postgres') ? 'entries_id_seq' : '') );
 415         }
 416         return $id;
 417     }
 418
 419     public function deleteById($id, $user_id) {
 420         $sql_action     = "DELETE FROM entries WHERE id=? AND user_id=?";
 421         $params_action  = array($id, $user_id);
 422         $query          = $this->executeQuery($sql_action, $params_action);
 423         return $query;
 424     }
 425
 426     public function favoriteById($id, $user_id) {
 427         $sql_action     = "UPDATE entries SET is_fav=NOT is_fav WHERE id=? AND user_id=?";
 428         $params_action  = array($id, $user_id);
 429         $query          = $this->executeQuery($sql_action, $params_action);
 430     }
 431
 432     public function archiveById($id, $user_id) {
 433         $sql_action     = "UPDATE entries SET is_read=NOT is_read WHERE id=? AND user_id=?";
 434         $params_action  = array($id, $user_id);
 435         $query          = $this->executeQuery($sql_action, $params_action);
 436     }
 437
 438     public function archiveAll($user_id) {
 439         $sql_action     = "UPDATE entries SET is_read=? WHERE user_id=? AND is_read=?";
 440         $params_action  = array($user_id, 1, 0);
 441         $query          = $this->executeQuery($sql_action, $params_action);
 442     }
 443
 444     public function getLastId($column = '') {
 445         return $this->getHandle()->lastInsertId($column);
 446     }
 447
 448     public function search($term, $user_id, $limit = '') {
 449         $search = '%'.$term.'%';
 450         $sql_action = "SELECT * FROM entries WHERE user_id=? AND (content LIKE ? OR title LIKE ? OR url LIKE ?) "; //searches in content, title and URL
 451         $sql_action .= $this->getEntriesOrder().' ' . $limit;
 452         $params_action = array($user_id, $search, $search, $search);
 453         $query = $this->executeQuery($sql_action, $params_action);
 454         return $query->fetchAll();
 455         }
 456
 457     public function retrieveAllTags($user_id, $term = null) {
 458         $sql = "SELECT DISTINCT tags.*, count(entries.id) AS entriescount FROM tags
 459           LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
 460           LEFT JOIN entries ON tags_entries.entry_id=entries.id
 461           WHERE entries.user_id=?
 462             ". (($term) ? "AND lower(tags.value) LIKE ?" : '') ."
 463           GROUP BY tags.id, tags.value
 464           ORDER BY tags.value";
 465         $query = $this->executeQuery($sql, (($term)? array($user_id, strtolower('%'.$term.'%')) : array($user_id) ));
 466         $tags = $query->fetchAll();
 467
 468         return $tags;
 469     }
 470
 471     public function retrieveTag($id, $user_id) {
 472         $tag  = NULL;
 473         $sql    = "SELECT DISTINCT tags.* FROM tags
 474           LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
 475           LEFT JOIN entries ON tags_entries.entry_id=entries.id
 476           WHERE tags.id=? AND entries.user_id=?";
 477         $params = array(intval($id), $user_id);
 478         $query  = $this->executeQuery($sql, $params);
 479         $tag  = $query->fetchAll();
 480
 481         return isset($tag[0]) ? $tag[0] : null;
 482     }
 483
 484     public function retrieveEntriesByTag($tag_id, $user_id) {
 485         $sql =
 486             "SELECT entries.* FROM entries
 487             LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
 488             WHERE tags_entries.tag_id = ? AND entries.user_id=?";
 489         $query = $this->executeQuery($sql, array($tag_id, $user_id));
 490         $entries = $query->fetchAll();
 491
 492         return $entries;
 493     }
 494
 495     public function retrieveTagsByEntry($entry_id) {
 496         $sql =
 497             "SELECT tags.* FROM tags
 498             LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
 499             WHERE tags_entries.entry_id = ?";
 500         $query = $this->executeQuery($sql, array($entry_id));
 501         $tags = $query->fetchAll();
 502
 503         return $tags;
 504     }
 505
 506     public function removeTagForEntry($entry_id, $tag_id) {
 507         $sql_action     = "DELETE FROM tags_entries WHERE tag_id=? AND entry_id=?";
 508         $params_action  = array($tag_id, $entry_id);
 509         $query          = $this->executeQuery($sql_action, $params_action);
 510         return $query;
 511     }
 512
 513     public function retrieveTagByValue($value) {
 514         $tag  = NULL;
 515         $sql    = "SELECT * FROM tags WHERE value=?";
 516         $params = array($value);
 517         $query  = $this->executeQuery($sql, $params);
 518         $tag  = $query->fetchAll();
 519
 520         return isset($tag[0]) ? $tag[0] : null;
 521     }
 522
 523     public function createTag($value) {
 524         $sql_action = 'INSERT INTO tags ( value ) VALUES (?)';
 525         $params_action = array($value);
 526         $query = $this->executeQuery($sql_action, $params_action);
 527         return $query;
 528     }
 529
 530     public function setTagToEntry($tag_id, $entry_id) {
 531         $sql_action = 'INSERT INTO tags_entries ( tag_id, entry_id ) VALUES (?, ?)';
 532         $params_action = array($tag_id, $entry_id);
 533         $query = $this->executeQuery($sql_action, $params_action);
 534         return $query;
 535     }
 536
 537         private function getEntriesOrder() {
 538             if (isset($_SESSION['sort']) and array_key_exists($_SESSION['sort'], $this->order)) {
 539                 return $this->order[$_SESSION['sort']];
 540             }
 541             else {
 542                 return $this->order['default'];
 543             }
 544         }
 545
 546 }