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