X-Git-Url: https://git.immae.eu/?a=blobdiff_plain;f=inc%2Fpoche%2FDatabase.class.php;h=d95b9b8101c08903bd4dfa80875697aca957b9db;hb=a0aa150418b628b32b18c70436d6be495129ee38;hp=a226b31e999cfb5d10f3bf4cdca1aac6f5cb7294;hpb=bc1ee8524e0769ad37e3c4c02cfe96d2f60e52f6;p=github%2Fwallabag%2Fwallabag.git diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php index a226b31e..d95b9b81 100644 --- a/inc/poche/Database.class.php +++ b/inc/poche/Database.class.php @@ -9,25 +9,27 @@ */ 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); + Tools::logm('storage type ' . STORAGE); } private function getHandle() { @@ -35,25 +37,44 @@ 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', PAGINATION); + $query = $this->executeQuery($sql, $params); + + $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; + $params = array($id_user, 'language', LANG); + $query = $this->executeQuery($sql, $params); + + $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; + $params = array($id_user, 'theme', DEFAULT_THEME); $query = $this->executeQuery($sql, $params); return TRUE; } - private function getConfigUser($id) { + public function getConfigUser($id) { $sql = "SELECT * FROM users_config WHERE user_id = ?"; $query = $this->executeQuery($sql, array($id)); $result = $query->fetchAll(); @@ -66,6 +87,17 @@ class Database { return $user_config; } + public function userExists($username) { + $sql = "SELECT * FROM users WHERE username=?"; + $query = $this->executeQuery($sql, array($username)); + $login = $query->fetchAll(); + if (isset($login[0])) { + return true; + } else { + return false; + } + } + public function login($username, $password) { $sql = "SELECT * FROM users WHERE username=? AND password=?"; $query = $this->executeQuery($sql, array($username, $password)); @@ -84,12 +116,26 @@ class Database { return $user; } - public function updatePassword($id, $password) + public function updatePassword($userId, $password) { $sql_update = "UPDATE users SET password=? WHERE id=?"; - $params_update = array($password, $id); + $params_update = array($password, $userId); $query = $this->executeQuery($sql_update, $params_update); } + + public function updateUserConfig($userId, $key, $value) { + $config = $this->getConfigUser($userId); + + if (!isset ($user_config[$key])) { + $sql = "INSERT INTO users_config (`value`, `user_id`, `name`) VALUES (?, ?, ?)"; + } + else { + $sql = "UPDATE users_config SET `value`=? WHERE `user_id`=? AND `name`=?"; + } + + $params = array($value, $userId, $key); + $query = $this->executeQuery($sql, $params); + } private function executeQuery($sql, $params) { try @@ -120,7 +166,7 @@ class Database { $query = $this->executeQuery($sql, $params); $entry = $query->fetchAll(); - return $entry[0]; + return isset($entry[0]) ? $entry[0] : null; } public function getEntriesByView($view, $user_id, $limit = '') { @@ -167,6 +213,13 @@ class Database { return $entries; } + public function updateContent($id, $content, $user_id) { + $sql_action = 'UPDATE entries SET content = ? WHERE id=? AND user_id=?'; + $params_action = array($content, $id, $user_id); + $query = $this->executeQuery($sql_action, $params_action); + return $query; + } + 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); @@ -193,7 +246,78 @@ 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); + } + + public function retrieveAllTags() { + $sql = "SELECT * FROM tags"; + $query = $this->executeQuery($sql, array()); + $tags = $query->fetchAll(); + + return $tags; + } + + public function retrieveTag($id) { + $tag = NULL; + $sql = "SELECT * FROM tags WHERE id=?"; + $params = array(intval($id)); + $query = $this->executeQuery($sql, $params); + $tag = $query->fetchAll(); + + return isset($tag[0]) ? $tag[0] : null; + } + + public function retrieveEntriesByTag($tag_id) { + $sql = + "SELECT * FROM entries + LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id + WHERE tags_entries.tag_id = ?"; + $query = $this->executeQuery($sql, array($tag_id)); + $entries = $query->fetchAll(); + + return $entries; + } + + public function retrieveTagsByEntry($entry_id) { + $sql = + "SELECT * FROM tags + LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id + WHERE tags_entries.entry_id = ?"; + $query = $this->executeQuery($sql, array($entry_id)); + $tags = $query->fetchAll(); + + return $tags; + } + + public function removeTagForEntry($entry_id, $tag_id) { + $sql_action = "DELETE FROM tags_entries WHERE tag_id=? AND entry_id=?"; + $params_action = array($tag_id, $entry_id); + $query = $this->executeQuery($sql_action, $params_action); + return $query; + } + + public function retrieveTagByValue($value) { + $tag = NULL; + $sql = "SELECT * FROM tags WHERE value=?"; + $params = array($value); + $query = $this->executeQuery($sql, $params); + $tag = $query->fetchAll(); + + return isset($tag[0]) ? $tag[0] : null; + } + + public function createTag($value) { + $sql_action = 'INSERT INTO tags ( value ) VALUES (?)'; + $params_action = array($value); + $query = $this->executeQuery($sql_action, $params_action); + return $query; + } + + public function setTagToEntry($tag_id, $entry_id) { + $sql_action = 'INSERT INTO tags_entries ( tag_id, entry_id ) VALUES (?, ?)'; + $params_action = array($tag_id, $entry_id); + $query = $this->executeQuery($sql_action, $params_action); + return $query; } }