From bc1ee8524e0769ad37e3c4c02cfe96d2f60e52f6 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Nicolas=20L=C5=93uillet?= Date: Wed, 7 Aug 2013 14:24:07 +0200 Subject: postgres --- inc/poche/Database.class.php | 199 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 199 insertions(+) create mode 100644 inc/poche/Database.class.php (limited to 'inc/poche/Database.class.php') diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php new file mode 100644 index 00000000..a226b31e --- /dev/null +++ b/inc/poche/Database.class.php @@ -0,0 +1,199 @@ + + * @copyright 2013 + * @license http://www.wtfpl.net/ see COPYING file + */ + +class Database { + + #postgresql + public static $db_path = 'pgsql:host=localhost;dbname=poche'; + public static $user = 'postgres'; + public static $password = 'postgres'; + #sqlite + // public static $db_path = 'sqlite:./db/poche.sqlite'; + // public static $user = ''; + // public static $password = ''; + #mysql + // public static $db_path = 'mysql:host=localhost;dbname=poche'; + // public static $user = 'root'; + // public static $password = 'root'; + + var $handle; + + function __construct() { + $this->handle = new PDO(self::$db_path, self::$user, self::$password); + $this->handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); + } + + private function getHandle() { + return $this->handle; + } + + public function isInstalled() { + $sql = "SELECT username FROM users WHERE id=?"; + $query = $this->executeQuery($sql, array('1')); + $hasAdmin = $query->fetchAll(); + + if (count($hasAdmin) == 0) + return FALSE; + + return TRUE; + } + + public function install($login, $password) { + $sql = 'INSERT INTO users ( username, password ) VALUES (?, ?)'; + $params = array($login, $password); + $query = $this->executeQuery($sql, $params); + + return TRUE; + } + + private function getConfigUser($id) { + $sql = "SELECT * FROM users_config WHERE user_id = ?"; + $query = $this->executeQuery($sql, array($id)); + $result = $query->fetchAll(); + $user_config = array(); + + foreach ($result as $key => $value) { + $user_config[$value['name']] = $value['value']; + } + + return $user_config; + } + + public function login($username, $password) { + $sql = "SELECT * FROM users WHERE username=? AND password=?"; + $query = $this->executeQuery($sql, array($username, $password)); + $login = $query->fetchAll(); + + $user = array(); + if (isset($login[0])) { + $user['id'] = $login[0]['id']; + $user['username'] = $login[0]['username']; + $user['password'] = $login[0]['password']; + $user['name'] = $login[0]['name']; + $user['email'] = $login[0]['email']; + $user['config'] = $this->getConfigUser($login[0]['id']); + } + + return $user; + } + + public function updatePassword($id, $password) + { + $sql_update = "UPDATE users SET password=? WHERE id=?"; + $params_update = array($password, $id); + $query = $this->executeQuery($sql_update, $params_update); + } + + private function executeQuery($sql, $params) { + try + { + $query = $this->getHandle()->prepare($sql); + $query->execute($params); + return $query; + } + catch (Exception $e) + { + Tools::logm('execute query error : '.$e->getMessage()); + return FALSE; + } + } + + public function retrieveAll($user_id) { + $sql = "SELECT * FROM entries WHERE user_id=? ORDER BY id"; + $query = $this->executeQuery($sql, array($user_id)); + $entries = $query->fetchAll(); + + return $entries; + } + + public function retrieveOneById($id, $user_id) { + $entry = NULL; + $sql = "SELECT * FROM entries WHERE id=? AND user_id=?"; + $params = array(intval($id), $user_id); + $query = $this->executeQuery($sql, $params); + $entry = $query->fetchAll(); + + return $entry[0]; + } + + public function getEntriesByView($view, $user_id, $limit = '') { + switch ($_SESSION['sort']) + { + case 'ia': + $order = 'ORDER BY id'; + break; + case 'id': + $order = 'ORDER BY id DESC'; + break; + case 'ta': + $order = 'ORDER BY lower(title)'; + break; + case 'td': + $order = 'ORDER BY lower(title) DESC'; + break; + default: + $order = 'ORDER BY id'; + break; + } + + switch ($view) + { + case 'archive': + $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? " . $order; + $params = array($user_id, 1); + break; + case 'fav' : + $sql = "SELECT * FROM entries WHERE user_id=? AND is_fav=? " . $order; + $params = array($user_id, 1); + break; + default: + $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? " . $order; + $params = array($user_id, 0); + break; + } + + $sql .= ' ' . $limit; + + $query = $this->executeQuery($sql, $params); + $entries = $query->fetchAll(); + + return $entries; + } + + public function add($url, $title, $content, $user_id) { + $sql_action = 'INSERT INTO entries ( url, title, content, user_id ) VALUES (?, ?, ?, ?)'; + $params_action = array($url, $title, $content, $user_id); + $query = $this->executeQuery($sql_action, $params_action); + return $query; + } + + public function deleteById($id, $user_id) { + $sql_action = "DELETE FROM entries WHERE id=? AND user_id=?"; + $params_action = array($id, $user_id); + $query = $this->executeQuery($sql_action, $params_action); + return $query; + } + + public function favoriteById($id, $user_id) { + $sql_action = "UPDATE entries SET is_fav=NOT is_fav WHERE id=? AND user_id=?"; + $params_action = array($id, $user_id); + $query = $this->executeQuery($sql_action, $params_action); + } + + public function archiveById($id, $user_id) { + $sql_action = "UPDATE entries SET is_read=NOT is_read WHERE id=? AND user_id=?"; + $params_action = array($id, $user_id); + $query = $this->executeQuery($sql_action, $params_action); + } + + public function getLastId() { + return $this->getHandle()->lastInsertId(); + } +} -- cgit v1.2.3 From 68857cea8c08aab54c632d63f4526d0bb16f80d4 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Nicolas=20L=C5=93uillet?= Date: Wed, 7 Aug 2013 14:38:58 +0200 Subject: setup of storage --- inc/poche/Database.class.php | 33 +++++++++++++++++---------------- 1 file changed, 17 insertions(+), 16 deletions(-) (limited to 'inc/poche/Database.class.php') diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php index a226b31e..034b1003 100644 --- a/inc/poche/Database.class.php +++ b/inc/poche/Database.class.php @@ -9,24 +9,25 @@ */ class Database { - - #postgresql - public static $db_path = 'pgsql:host=localhost;dbname=poche'; - public static $user = 'postgres'; - public static $password = 'postgres'; - #sqlite - // public static $db_path = 'sqlite:./db/poche.sqlite'; - // public static $user = ''; - // public static $password = ''; - #mysql - // public static $db_path = 'mysql:host=localhost;dbname=poche'; - // public static $user = 'root'; - // public static $password = 'root'; - var $handle; - function __construct() { - $this->handle = new PDO(self::$db_path, self::$user, self::$password); + function __construct() + { + switch (STORAGE) { + case 'sqlite': + $db_path = 'sqlite:' . STORAGE_SQLITE; + $this->handle = new PDO($db_path); + break; + case 'mysql': + $db_path = 'mysql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB; + $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD); + break; + case 'postgres': + $db_path = 'pgsql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB; + $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD); + break; + } + $this->handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } -- cgit v1.2.3 From 580d60b9416b3445300f37fc0ecc160254ed0676 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Nicolas=20L=C5=93uillet?= Date: Wed, 7 Aug 2013 15:46:17 +0200 Subject: file to update from 0.x to 1.x \o/ --- inc/poche/Database.class.php | 1 + 1 file changed, 1 insertion(+) (limited to 'inc/poche/Database.class.php') diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php index 034b1003..8da7a994 100644 --- a/inc/poche/Database.class.php +++ b/inc/poche/Database.class.php @@ -29,6 +29,7 @@ class Database { } $this->handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); + Tools::logm('storage type ' . STORAGE); } private function getHandle() { -- cgit v1.2.3 From b916bcfccc5a8b1c1852c0bf39bb6f9837296a4e Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Nicolas=20L=C5=93uillet?= Date: Wed, 7 Aug 2013 19:14:28 +0200 Subject: fixes for 1.0-beta --- inc/poche/Database.class.php | 31 +++++++++++++++++++++++-------- 1 file changed, 23 insertions(+), 8 deletions(-) (limited to 'inc/poche/Database.class.php') diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php index 8da7a994..cd5a9a31 100644 --- a/inc/poche/Database.class.php +++ b/inc/poche/Database.class.php @@ -37,19 +37,34 @@ class Database { } public function isInstalled() { - $sql = "SELECT username FROM users WHERE id=?"; - $query = $this->executeQuery($sql, array('1')); - $hasAdmin = $query->fetchAll(); + $sql = "SELECT username FROM users"; + $query = $this->executeQuery($sql, array()); + $hasAdmin = count($query->fetchAll()); - if (count($hasAdmin) == 0) + if ($hasAdmin == 0) return FALSE; return TRUE; } public function install($login, $password) { - $sql = 'INSERT INTO users ( username, password ) VALUES (?, ?)'; - $params = array($login, $password); + $sql = 'INSERT INTO users ( username, password, name, email) VALUES (?, ?, ?, ?)'; + $params = array($login, $password, $login, ' '); + $query = $this->executeQuery($sql, $params); + + $sequence = ''; + if (STORAGE == 'postgres') { + $sequence = 'users_id_seq'; + } + + $id_user = intval($this->getLastId($sequence)); + + $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; + $params = array($id_user, 'pager', '10'); + $query = $this->executeQuery($sql, $params); + + $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; + $params = array($id_user, 'language', 'en_EN.UTF8'); $query = $this->executeQuery($sql, $params); return TRUE; @@ -195,7 +210,7 @@ class Database { $query = $this->executeQuery($sql_action, $params_action); } - public function getLastId() { - return $this->getHandle()->lastInsertId(); + public function getLastId($column = '') { + return $this->getHandle()->lastInsertId($column); } } -- cgit v1.2.3