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