X-Git-Url: https://git.immae.eu/?a=blobdiff_plain;ds=inline;f=inc%2Fpoche%2FDatabase.class.php;h=04731821fb3311a6cd0e6180bfe5d8bc4ec9ebdb;hb=a562e3905ac67983b85853e5ace813b2de0e3807;hp=84916b83efe3cebd2fc6d9f42d03ed327068b71e;hpb=b58e261db9f3ea8b9c4c51ef9f0e393942915e21;p=github%2Fwallabag%2Fwallabag.git diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php index 84916b83..04731821 100644 --- a/inc/poche/Database.class.php +++ b/inc/poche/Database.class.php @@ -39,12 +39,79 @@ class Database { public function isInstalled() { $sql = "SELECT username FROM users"; $query = $this->executeQuery($sql, array()); + if ($query == false) { + die(STORAGE . ' database looks empty. You have to create it (you can find database structure in install folder).'); + } $hasAdmin = count($query->fetchAll()); if ($hasAdmin == 0) - return FALSE; + return false; - return TRUE; + return true; + } + + public function checkTags() { + + if (STORAGE == 'sqlite') { + $sql = ' + CREATE TABLE IF NOT EXISTS tags ( + id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, + value TEXT + )'; + } + elseif(STORAGE == 'mysql') { + $sql = ' + CREATE TABLE IF NOT EXISTS `tags` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `value` varchar(255) NOT NULL, + PRIMARY KEY (`id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8; + '; + } + else { + $sql = ' + CREATE TABLE tags ( + id bigserial primary key, + value varchar(255) NOT NULL + ); + '; + } + + $query = $this->executeQuery($sql, array()); + + if (STORAGE == 'sqlite') { + $sql = ' + CREATE TABLE IF NOT EXISTS tags_entries ( + id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, + entry_id INTEGER, + tag_id INTEGER, + FOREIGN KEY(entry_id) REFERENCES entries(id) ON DELETE CASCADE, + FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE + )'; + } + elseif(STORAGE == 'mysql') { + $sql = ' + CREATE TABLE IF NOT EXISTS `tags_entries` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `entry_id` int(11) NOT NULL, + `tag_id` int(11) NOT NULL, + FOREIGN KEY(entry_id) REFERENCES entries(id) ON DELETE CASCADE, + FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE, + PRIMARY KEY (`id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8; + '; + } + else { + $sql = ' + CREATE TABLE tags_entries ( + id bigserial primary key, + entry_id integer NOT NULL, + tag_id integer NOT NULL + ) + '; + } + + $query = $this->executeQuery($sql, array()); } public function install($login, $password) { @@ -60,17 +127,21 @@ class Database { $id_user = intval($this->getLastId($sequence)); $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; - $params = array($id_user, 'pager', '10'); + $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', 'en_EN.UTF8'); + $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(); @@ -83,6 +154,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)); @@ -101,12 +183,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 @@ -137,7 +233,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 = '') { @@ -220,4 +316,75 @@ class Database { 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 entries.* 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 tags.* 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; + } }