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