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 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 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 retrieveAll($user_id) {
 256         $sql        = "SELECT * FROM entries WHERE content <> '' AND user_id=? ORDER BY id";
 257         $query      = $this->executeQuery($sql, array($user_id));
 258         $entries    = $query->fetchAll();
 259
 260         return $entries;
 261     }
 262
 263     public function retrieveOneById($id, $user_id) {
 264         $entry  = NULL;
 265         $sql    = "SELECT * FROM entries WHERE id=? AND user_id=?";
 266         $params = array(intval($id), $user_id);
 267         $query  = $this->executeQuery($sql, $params);
 268         $entry  = $query->fetchAll();
 269
 270         return isset($entry[0]) ? $entry[0] : null;
 271     }
 272
 273     public function retrieveOneByURL($url, $user_id) {
 274         $entry  = NULL;
 275         $sql    = "SELECT * FROM entries WHERE content <> '' AND url=? AND user_id=?";
 276         $params = array($url, $user_id);
 277         $query  = $this->executeQuery($sql, $params);
 278         $entry  = $query->fetchAll();
 279
 280         return isset($entry[0]) ? $entry[0] : null;
 281     }
 282
 283     public function reassignTags($old_entry_id, $new_entry_id) {
 284         $sql    = "UPDATE tags_entries SET entry_id=? WHERE entry_id=?";
 285         $params = array($new_entry_id, $old_entry_id);
 286         $query  = $this->executeQuery($sql, $params);
 287     }
 288
 289     public function getEntriesByView($view, $user_id, $limit = '', $tag_id = 0) {
 290         switch ($view) {
 291             case 'archive':
 292                 $sql    = "SELECT * FROM entries WHERE content <> '' AND user_id=? AND is_read=? ";
 293                 $params = array($user_id, 1);
 294                 break;
 295             case 'fav' :
 296                 $sql    = "SELECT * FROM entries WHERE content <> '' AND user_id=? AND is_fav=? ";
 297                 $params = array($user_id, 1);
 298                 break;
 299             case 'tag' :
 300                 $sql    = "SELECT entries.* FROM entries
 301                 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
 302                 WHERE entries.content <> '' AND
 303                 entries.user_id=? AND tags_entries.tag_id = ? ";
 304                 $params = array($user_id, $tag_id);
 305                 break;
 306             default:
 307                 $sql    = "SELECT * FROM entries WHERE content <> '' AND user_id=? AND is_read=? ";
 308                 $params = array($user_id, 0);
 309                 break;
 310         }
 311
 312                 $sql .= $this->getEntriesOrder().' ' . $limit;
 313
 314                 $query = $this->executeQuery($sql, $params);
 315                 $entries = $query->fetchAll();
 316
 317                 return $entries;
 318         }
 319
 320     public function getEntriesByViewCount($view, $user_id, $tag_id = 0) {
 321         switch ($view) {
 322             case 'archive':
 323                     $sql    = "SELECT count(*) FROM entries WHERE content <> '' AND user_id=? AND is_read=? ";
 324                 $params = array($user_id, 1);
 325                 break;
 326             case 'fav' :
 327                     $sql    = "SELECT count(*) FROM entries WHERE content <> '' AND user_id=? AND is_fav=? ";
 328                 $params = array($user_id, 1);
 329                 break;
 330             case 'tag' :
 331                 $sql    = "SELECT count(*) FROM entries
 332                     LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
 333                     WHERE entries.content <> '' AND
 334                     entries.user_id=? AND tags_entries.tag_id = ? ";
 335                 $params = array($user_id, $tag_id);
 336                 break;
 337             default:
 338                 $sql    = "SELECT count(*) FROM entries WHERE content <> '' AND user_id=? AND is_read=? ";
 339                 $params = array($user_id, 0);
 340                 break;
 341         }
 342
 343         $query = $this->executeQuery($sql, $params);
 344         list($count) = $query->fetch();
 345
 346         return $count;
 347     }
 348
 349     public function updateContent($id, $content, $user_id) {
 350         $sql_action = 'UPDATE entries SET content = ? WHERE id=? AND user_id=?';
 351         $params_action = array($content, $id, $user_id);
 352         $query = $this->executeQuery($sql_action, $params_action);
 353         return $query;
 354     }
 355
 356     public function add($url, $title, $content, $user_id) {
 357         $sql_action = 'INSERT INTO entries ( url, title, content, user_id ) VALUES (?, ?, ?, ?)';
 358         $params_action = array($url, $title, $content, $user_id);
 359         $query = $this->executeQuery($sql_action, $params_action);
 360         return $query;
 361     }
 362
 363     public function deleteById($id, $user_id) {
 364         $sql_action     = "DELETE FROM entries WHERE id=? AND user_id=?";
 365         $params_action  = array($id, $user_id);
 366         $query          = $this->executeQuery($sql_action, $params_action);
 367         return $query;
 368     }
 369
 370     public function favoriteById($id, $user_id) {
 371         $sql_action     = "UPDATE entries SET is_fav=NOT is_fav WHERE id=? AND user_id=?";
 372         $params_action  = array($id, $user_id);
 373         $query          = $this->executeQuery($sql_action, $params_action);
 374     }
 375
 376     public function archiveById($id, $user_id) {
 377         $sql_action     = "UPDATE entries SET is_read=NOT is_read WHERE id=? AND user_id=?";
 378         $params_action  = array($id, $user_id);
 379         $query          = $this->executeQuery($sql_action, $params_action);
 380     }
 381
 382     public function archiveAll($user_id) {
 383         $sql_action     = "UPDATE entries SET is_read=? WHERE user_id=? AND is_read=?";
 384         $params_action  = array($user_id, 1, 0);
 385         $query          = $this->executeQuery($sql_action, $params_action);
 386     }
 387
 388     public function getLastId($column = '') {
 389         return $this->getHandle()->lastInsertId($column);
 390     }
 391
 392         public function search($term){
 393                 $search = '%'.$term.'%';
 394                 $query = $this->getHandle()->prepare("SELECT * FROM entries WHERE content LIKE ? OR title LIKE ? OR url LIKE ?"); //searches in content, title and URL
 395                 $query->execute(array($search,$search,$search));
 396                 $entries = $query->fetchAll();
 397                 return $entries;
 398         }
 399
 400     public function retrieveAllTags($user_id, $term = null) {
 401         $sql = "SELECT DISTINCT tags.*, count(entries.id) AS entriescount FROM tags
 402           LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
 403           LEFT JOIN entries ON tags_entries.entry_id=entries.id
 404           WHERE entries.content <> '' AND entries.user_id=?
 405             ". (($term) ? "AND lower(tags.value) LIKE ?" : '') ."
 406           GROUP BY tags.id, tags.value
 407           ORDER BY tags.value";
 408         $query = $this->executeQuery($sql, (($term)? array($user_id, strtolower('%'.$term.'%')) : array($user_id) ));
 409         $tags = $query->fetchAll();
 410
 411         return $tags;
 412     }
 413
 414     public function retrieveTag($id, $user_id) {
 415         $tag  = NULL;
 416         $sql    = "SELECT DISTINCT tags.* FROM tags
 417           LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
 418           LEFT JOIN entries ON tags_entries.entry_id=entries.id
 419           WHERE entries.content <> '' AND tags.id=? AND entries.user_id=?";
 420         $params = array(intval($id), $user_id);
 421         $query  = $this->executeQuery($sql, $params);
 422         $tag  = $query->fetchAll();
 423
 424         return isset($tag[0]) ? $tag[0] : null;
 425     }
 426
 427     public function retrieveEntriesByTag($tag_id, $user_id) {
 428         $sql =
 429             "SELECT entries.* FROM entries
 430             LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
 431             WHERE entries.content <> '' AND
 432             tags_entries.tag_id = ? AND entries.user_id=?";
 433         $query = $this->executeQuery($sql, array($tag_id, $user_id));
 434         $entries = $query->fetchAll();
 435
 436         return $entries;
 437     }
 438
 439     public function retrieveTagsByEntry($entry_id) {
 440         $sql =
 441             "SELECT tags.* FROM tags
 442             LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
 443             WHERE tags_entries.entry_id = ?";
 444         $query = $this->executeQuery($sql, array($entry_id));
 445         $tags = $query->fetchAll();
 446
 447         return $tags;
 448     }
 449
 450     public function removeTagForEntry($entry_id, $tag_id) {
 451         $sql_action     = "DELETE FROM tags_entries WHERE tag_id=? AND entry_id=?";
 452         $params_action  = array($tag_id, $entry_id);
 453         $query          = $this->executeQuery($sql_action, $params_action);
 454         return $query;
 455     }
 456
 457     public function retrieveTagByValue($value) {
 458         $tag  = NULL;
 459         $sql    = "SELECT * FROM tags WHERE value=?";
 460         $params = array($value);
 461         $query  = $this->executeQuery($sql, $params);
 462         $tag  = $query->fetchAll();
 463
 464         return isset($tag[0]) ? $tag[0] : null;
 465     }
 466
 467     public function createTag($value) {
 468         $sql_action = 'INSERT INTO tags ( value ) VALUES (?)';
 469         $params_action = array($value);
 470         $query = $this->executeQuery($sql_action, $params_action);
 471         return $query;
 472     }
 473
 474     public function setTagToEntry($tag_id, $entry_id) {
 475         $sql_action = 'INSERT INTO tags_entries ( tag_id, entry_id ) VALUES (?, ?)';
 476         $params_action = array($tag_id, $entry_id);
 477         $query = $this->executeQuery($sql_action, $params_action);
 478         return $query;
 479     }
 480
 481         private function getEntriesOrder() {
 482             if (isset($_SESSION['sort']) and array_key_exists($_SESSION['sort'], $this->order)) {
 483                 return $this->order[$_SESSION['sort']];
 484             }
 485             else {
 486                 return $this->order['default'];
 487             }
 488         }
 489
 490 }