inc/poche/Database.class.php

   1 <?php
   2 /**
   3  * poche, a read it later open source system
   4  *
   5  * @category   poche
   6  * @author     Nicolas Lœuillet <support@inthepoche.com>
   7  * @copyright  2013
   8  * @license    http://www.wtfpl.net/ see COPYING file
   9  */
  10
  11 class Database {
  12     var $handle;
  13
  14     function __construct()
  15     {
  16         switch (STORAGE) {
  17             case 'sqlite':
  18                 $db_path = 'sqlite:' . STORAGE_SQLITE;
  19                 $this->handle = new PDO($db_path);
  20                 break;
  21             case 'mysql':
  22                 $db_path = 'mysql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB;
  23                 $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD);
  24                 break;
  25             case 'postgres':
  26                 $db_path = 'pgsql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB;
  27                 $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD);
  28                 break;
  29         }
  30
  31         $this->handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  32     }
  33
  34     private function getHandle() {
  35         return $this->handle;
  36     }
  37
  38     public function isInstalled() {
  39         $sql = "SELECT username FROM users WHERE id=?";
  40         $query = $this->executeQuery($sql, array('1'));
  41         $hasAdmin = $query->fetchAll();
  42
  43         if (count($hasAdmin) == 0)
  44             return FALSE;
  45
  46         return TRUE;
  47     }
  48
  49     public function install($login, $password) {
  50         $sql = 'INSERT INTO users ( username, password ) VALUES (?, ?)';
  51         $params = array($login, $password);
  52         $query = $this->executeQuery($sql, $params);
  53
  54         return TRUE;
  55     }
  56
  57     private function getConfigUser($id) {
  58         $sql = "SELECT * FROM users_config WHERE user_id = ?";
  59         $query = $this->executeQuery($sql, array($id));
  60         $result = $query->fetchAll();
  61         $user_config = array();
  62
  63         foreach ($result as $key => $value) {
  64             $user_config[$value['name']] = $value['value'];
  65         }
  66
  67         return $user_config;
  68     }
  69
  70     public function login($username, $password) {
  71         $sql = "SELECT * FROM users WHERE username=? AND password=?";
  72         $query = $this->executeQuery($sql, array($username, $password));
  73         $login = $query->fetchAll();
  74
  75         $user = array();
  76         if (isset($login[0])) {
  77             $user['id'] = $login[0]['id'];
  78             $user['username'] = $login[0]['username'];
  79             $user['password'] = $login[0]['password'];
  80             $user['name'] = $login[0]['name'];
  81             $user['email'] = $login[0]['email'];
  82             $user['config'] = $this->getConfigUser($login[0]['id']);
  83         }
  84
  85         return $user;
  86     }
  87
  88     public function updatePassword($id, $password)
  89     {
  90         $sql_update = "UPDATE users SET password=? WHERE id=?";
  91         $params_update = array($password, $id);
  92         $query = $this->executeQuery($sql_update, $params_update);
  93     }
  94
  95     private function executeQuery($sql, $params) {
  96         try
  97         {
  98             $query = $this->getHandle()->prepare($sql);
  99             $query->execute($params);
 100             return $query;
 101         }
 102         catch (Exception $e)
 103         {
 104             Tools::logm('execute query error : '.$e->getMessage());
 105             return FALSE;
 106         }
 107     }
 108
 109     public function retrieveAll($user_id) {
 110         $sql        = "SELECT * FROM entries WHERE user_id=? ORDER BY id";
 111         $query      = $this->executeQuery($sql, array($user_id));
 112         $entries    = $query->fetchAll();
 113
 114         return $entries;
 115     }
 116
 117     public function retrieveOneById($id, $user_id) {
 118         $entry  = NULL;
 119         $sql    = "SELECT * FROM entries WHERE id=? AND user_id=?";
 120         $params = array(intval($id), $user_id);
 121         $query  = $this->executeQuery($sql, $params);
 122         $entry  = $query->fetchAll();
 123
 124         return $entry[0];
 125     }
 126
 127     public function getEntriesByView($view, $user_id, $limit = '') {
 128         switch ($_SESSION['sort'])
 129         {
 130             case 'ia':
 131                 $order = 'ORDER BY id';
 132                 break;
 133             case 'id':
 134                 $order = 'ORDER BY id DESC';
 135                 break;
 136             case 'ta':
 137                 $order = 'ORDER BY lower(title)';
 138                 break;
 139             case 'td':
 140                 $order = 'ORDER BY lower(title) DESC';
 141                 break;
 142             default:
 143                 $order = 'ORDER BY id';
 144                 break;
 145         }
 146
 147         switch ($view)
 148         {
 149             case 'archive':
 150                 $sql    = "SELECT * FROM entries WHERE user_id=? AND is_read=? " . $order;
 151                 $params = array($user_id, 1);
 152                 break;
 153             case 'fav' :
 154                 $sql    = "SELECT * FROM entries WHERE user_id=? AND is_fav=? " . $order;
 155                 $params = array($user_id, 1);
 156                 break;
 157             default:
 158                 $sql    = "SELECT * FROM entries WHERE user_id=? AND is_read=? " . $order;
 159                 $params = array($user_id, 0);
 160                 break;
 161         }
 162
 163         $sql .= ' ' . $limit;
 164
 165         $query = $this->executeQuery($sql, $params);
 166         $entries = $query->fetchAll();
 167
 168         return $entries;
 169     }
 170
 171     public function add($url, $title, $content, $user_id) {
 172         $sql_action = 'INSERT INTO entries ( url, title, content, user_id ) VALUES (?, ?, ?, ?)';
 173         $params_action = array($url, $title, $content, $user_id);
 174         $query = $this->executeQuery($sql_action, $params_action);
 175         return $query;
 176     }
 177
 178     public function deleteById($id, $user_id) {
 179         $sql_action     = "DELETE FROM entries WHERE id=? AND user_id=?";
 180         $params_action  = array($id, $user_id);
 181         $query          = $this->executeQuery($sql_action, $params_action);
 182         return $query;
 183     }
 184
 185     public function favoriteById($id, $user_id) {
 186         $sql_action     = "UPDATE entries SET is_fav=NOT is_fav WHERE id=? AND user_id=?";
 187         $params_action  = array($id, $user_id);
 188         $query          = $this->executeQuery($sql_action, $params_action);
 189     }
 190
 191     public function archiveById($id, $user_id) {
 192         $sql_action     = "UPDATE entries SET is_read=NOT is_read WHERE id=? AND user_id=?";
 193         $params_action  = array($id, $user_id);
 194         $query          = $this->executeQuery($sql_action, $params_action);
 195     }
 196
 197     public function getLastId() {
 198         return $this->getHandle()->lastInsertId();
 199     }
 200 }