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 retrieveAll($user_id) {
 234         $sql        = "SELECT * FROM entries WHERE user_id=? ORDER BY id";
 235         $query      = $this->executeQuery($sql, array($user_id));
 236         $entries    = $query->fetchAll();
 237
 238         return $entries;
 239     }
 240
 241     public function retrieveOneById($id, $user_id) {
 242         $entry  = NULL;
 243         $sql    = "SELECT * FROM entries WHERE id=? AND user_id=?";
 244         $params = array(intval($id), $user_id);
 245         $query  = $this->executeQuery($sql, $params);
 246         $entry  = $query->fetchAll();
 247
 248         return isset($entry[0]) ? $entry[0] : null;
 249     }
 250
 251     public function retrieveOneByURL($url, $user_id) {
 252         $entry  = NULL;
 253         $sql    = "SELECT * FROM entries WHERE url=? AND user_id=?";
 254         $params = array($url, $user_id);
 255         $query  = $this->executeQuery($sql, $params);
 256         $entry  = $query->fetchAll();
 257
 258         return isset($entry[0]) ? $entry[0] : null;
 259     }
 260
 261     public function reassignTags($old_entry_id, $new_entry_id) {
 262         $sql    = "UPDATE tags_entries SET entry_id=? WHERE entry_id=?";
 263         $params = array($new_entry_id, $old_entry_id);
 264         $query  = $this->executeQuery($sql, $params);
 265     }
 266
 267     public function getEntriesByView($view, $user_id, $limit = '', $tag_id = 0) {
 268         switch ($view) {
 269             case 'archive':
 270                 $sql    = "SELECT * FROM entries WHERE user_id=? AND is_read=? ";
 271                 $params = array($user_id, 1);
 272                 break;
 273             case 'fav' :
 274                 $sql    = "SELECT * FROM entries WHERE user_id=? AND is_fav=? ";
 275                 $params = array($user_id, 1);
 276                 break;
 277             case 'tag' :
 278                 $sql    = "SELECT entries.* FROM entries
 279                 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
 280                 WHERE entries.user_id=? AND tags_entries.tag_id = ? ";
 281                 $params = array($user_id, $tag_id);
 282                 break;
 283             default:
 284                 $sql    = "SELECT * FROM entries WHERE user_id=? AND is_read=? ";
 285                 $params = array($user_id, 0);
 286                 break;
 287         }
 288
 289                 $sql .= $this->getEntriesOrder().' ' . $limit;
 290
 291                 $query = $this->executeQuery($sql, $params);
 292                 $entries = $query->fetchAll();
 293
 294                 return $entries;
 295         }
 296
 297     public function getEntriesByViewCount($view, $user_id, $tag_id = 0) {
 298         switch ($view) {
 299             case 'archive':
 300                     $sql    = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? ";
 301                 $params = array($user_id, 1);
 302                 break;
 303             case 'fav' :
 304                     $sql    = "SELECT count(*) FROM entries WHERE user_id=? AND is_fav=? ";
 305                 $params = array($user_id, 1);
 306                 break;
 307             case 'tag' :
 308                 $sql    = "SELECT count(*) 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 count(*) FROM entries WHERE user_id=? AND is_read=? ";
 315                 $params = array($user_id, 0);
 316                 break;
 317         }
 318
 319         $query = $this->executeQuery($sql, $params);
 320         list($count) = $query->fetch();
 321
 322         return $count;
 323     }
 324
 325     public function updateContent($id, $content, $user_id) {
 326         $sql_action = 'UPDATE entries SET content = ? WHERE id=? AND user_id=?';
 327         $params_action = array($content, $id, $user_id);
 328         $query = $this->executeQuery($sql_action, $params_action);
 329         return $query;
 330     }
 331
 332     public function add($url, $title, $content, $user_id) {
 333         $sql_action = 'INSERT INTO entries ( url, title, content, user_id ) VALUES (?, ?, ?, ?)';
 334         $params_action = array($url, $title, $content, $user_id);
 335         $query = $this->executeQuery($sql_action, $params_action);
 336         return $query;
 337     }
 338
 339     public function deleteById($id, $user_id) {
 340         $sql_action     = "DELETE FROM entries WHERE id=? AND user_id=?";
 341         $params_action  = array($id, $user_id);
 342         $query          = $this->executeQuery($sql_action, $params_action);
 343         return $query;
 344     }
 345
 346     public function favoriteById($id, $user_id) {
 347         $sql_action     = "UPDATE entries SET is_fav=NOT is_fav WHERE id=? AND user_id=?";
 348         $params_action  = array($id, $user_id);
 349         $query          = $this->executeQuery($sql_action, $params_action);
 350     }
 351
 352     public function archiveById($id, $user_id) {
 353         $sql_action     = "UPDATE entries SET is_read=NOT is_read WHERE id=? AND user_id=?";
 354         $params_action  = array($id, $user_id);
 355         $query          = $this->executeQuery($sql_action, $params_action);
 356     }
 357
 358     public function archiveAll($user_id) {
 359         $sql_action     = "UPDATE entries SET is_read=? WHERE user_id=? AND is_read=?";
 360         $params_action  = array($user_id, 1, 0);
 361         $query          = $this->executeQuery($sql_action, $params_action);
 362     }
 363
 364     public function getLastId($column = '') {
 365         return $this->getHandle()->lastInsertId($column);
 366     }
 367
 368     public function retrieveAllTags($user_id) {
 369         $sql = "SELECT DISTINCT tags.* FROM tags
 370           LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
 371           LEFT JOIN entries ON tags_entries.entry_id=entries.id
 372           WHERE entries.user_id=?";
 373         $query = $this->executeQuery($sql, array($user_id));
 374         $tags = $query->fetchAll();
 375
 376         return $tags;
 377     }
 378
 379     public function retrieveTag($id, $user_id) {
 380         $tag  = NULL;
 381         $sql    = "SELECT DISTINCT tags.* FROM tags
 382           LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
 383           LEFT JOIN entries ON tags_entries.entry_id=entries.id
 384           WHERE tags.id=? AND entries.user_id=?";
 385         $params = array(intval($id), $user_id);
 386         $query  = $this->executeQuery($sql, $params);
 387         $tag  = $query->fetchAll();
 388
 389         return isset($tag[0]) ? $tag[0] : null;
 390     }
 391
 392     public function retrieveEntriesByTag($tag_id, $user_id) {
 393         $sql =
 394             "SELECT entries.* FROM entries
 395             LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
 396             WHERE tags_entries.tag_id = ? AND entries.user_id=?";
 397         $query = $this->executeQuery($sql, array($tag_id, $user_id));
 398         $entries = $query->fetchAll();
 399
 400         return $entries;
 401     }
 402
 403     public function retrieveTagsByEntry($entry_id) {
 404         $sql =
 405             "SELECT tags.* FROM tags
 406             LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
 407             WHERE tags_entries.entry_id = ?";
 408         $query = $this->executeQuery($sql, array($entry_id));
 409         $tags = $query->fetchAll();
 410
 411         return $tags;
 412     }
 413
 414     public function removeTagForEntry($entry_id, $tag_id) {
 415         $sql_action     = "DELETE FROM tags_entries WHERE tag_id=? AND entry_id=?";
 416         $params_action  = array($tag_id, $entry_id);
 417         $query          = $this->executeQuery($sql_action, $params_action);
 418         return $query;
 419     }
 420
 421     public function retrieveTagByValue($value) {
 422         $tag  = NULL;
 423         $sql    = "SELECT * FROM tags WHERE value=?";
 424         $params = array($value);
 425         $query  = $this->executeQuery($sql, $params);
 426         $tag  = $query->fetchAll();
 427
 428         return isset($tag[0]) ? $tag[0] : null;
 429     }
 430
 431     public function createTag($value) {
 432         $sql_action = 'INSERT INTO tags ( value ) VALUES (?)';
 433         $params_action = array($value);
 434         $query = $this->executeQuery($sql_action, $params_action);
 435         return $query;
 436     }
 437
 438     public function setTagToEntry($tag_id, $entry_id) {
 439         $sql_action = 'INSERT INTO tags_entries ( tag_id, entry_id ) VALUES (?, ?)';
 440         $params_action = array($tag_id, $entry_id);
 441         $query = $this->executeQuery($sql_action, $params_action);
 442         return $query;
 443     }
 444
 445         private function getEntriesOrder() {
 446             if (isset($_SESSION['sort']) and array_key_exists($_SESSION['sort'], $this->order)) {
 447                 return $this->order[$_SESSION['sort']];
 448             }
 449             else {
 450                 return $this->order['default'];
 451             }
 452         }
 453
 454 }