From 5846b0f1b32f7f9b99a3c20acfeeaee9781eebea Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Nicolas=20L=C5=93uillet?= Date: Tue, 3 Dec 2013 10:39:45 +0100 Subject: [change] getConfigUser is now a public function --- inc/poche/Database.class.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'inc/poche/Database.class.php') diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php index bf67de2a..c233eda1 100644 --- a/inc/poche/Database.class.php +++ b/inc/poche/Database.class.php @@ -74,7 +74,7 @@ class Database { 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(); -- cgit v1.2.3 From 5bea1a4d310e468d6ea1b71de36bdb4a53b8c57a Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Nicolas=20L=C5=93uillet?= Date: Fri, 6 Dec 2013 13:02:15 +0100 Subject: [add] function to get tags by entry --- inc/poche/Database.class.php | 11 +++++++++++ 1 file changed, 11 insertions(+) (limited to 'inc/poche/Database.class.php') diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php index c233eda1..86907e52 100644 --- a/inc/poche/Database.class.php +++ b/inc/poche/Database.class.php @@ -249,4 +249,15 @@ class Database { public function getLastId($column = '') { return $this->getHandle()->lastInsertId($column); } + + 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; + } } -- cgit v1.2.3 From 2e2ebe5ec767dcbee90394d12b03298592c87805 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Nicolas=20L=C5=93uillet?= Date: Fri, 6 Dec 2013 13:15:06 +0100 Subject: [add] create tags page --- inc/poche/Database.class.php | 14 +++++++++++--- 1 file changed, 11 insertions(+), 3 deletions(-) (limited to 'inc/poche/Database.class.php') diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php index 86907e52..8e9ee0b7 100644 --- a/inc/poche/Database.class.php +++ b/inc/poche/Database.class.php @@ -250,13 +250,21 @@ class Database { return $this->getHandle()->lastInsertId($column); } + public function retrieveAllTags() { + $sql = "SELECT * FROM tags"; + $query = $this->executeQuery($sql, array()); + $tags = $query->fetchAll(); + + return $tags; + } + public function retrieveTagsByEntry($entry_id) { - $sql = + $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(); + $query = $this->executeQuery($sql, array($entry_id)); + $tags = $query->fetchAll(); return $tags; } -- cgit v1.2.3 From 4886ed6d3637df0b3e16e672d58d4ef8f17dc432 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Nicolas=20L=C5=93uillet?= Date: Fri, 6 Dec 2013 14:22:29 +0100 Subject: [add] page which lists entries for a tag --- inc/poche/Database.class.php | 21 +++++++++++++++++++++ 1 file changed, 21 insertions(+) (limited to 'inc/poche/Database.class.php') diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php index 8e9ee0b7..a89bce41 100644 --- a/inc/poche/Database.class.php +++ b/inc/poche/Database.class.php @@ -258,6 +258,27 @@ class Database { 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 -- cgit v1.2.3 From c432fa1674fbe2b190cf0d0bab2e90302a61e98f Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Nicolas=20L=C5=93uillet?= Date: Fri, 6 Dec 2013 15:07:51 +0100 Subject: [add] assign and remove a tag to an entry --- inc/poche/Database.class.php | 31 +++++++++++++++++++++++++++++++ 1 file changed, 31 insertions(+) (limited to 'inc/poche/Database.class.php') diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php index a89bce41..d95b9b81 100644 --- a/inc/poche/Database.class.php +++ b/inc/poche/Database.class.php @@ -289,4 +289,35 @@ class Database { 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; + } } -- cgit v1.2.3 From 5cfafc6110985236d9f212533faee79e460bf20d Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Nicolas=20L=C5=93uillet?= Date: Mon, 23 Dec 2013 10:35:09 +0100 Subject: [add] check tags tables --- inc/poche/Database.class.php | 71 ++++++++++++++++++++++++++++++++++++++++++-- 1 file changed, 69 insertions(+), 2 deletions(-) (limited to 'inc/poche/Database.class.php') diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php index d95b9b81..d7e9fc11 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 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) { -- cgit v1.2.3 From da5fc42f615eeb45a702604970f94967507fb432 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Nicolas=20L=C5=93uillet?= Date: Mon, 23 Dec 2013 11:23:12 +0100 Subject: [fix] bug with queries when postgresql is used --- inc/poche/Database.class.php | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'inc/poche/Database.class.php') diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php index d7e9fc11..afe02a41 100644 --- a/inc/poche/Database.class.php +++ b/inc/poche/Database.class.php @@ -194,10 +194,10 @@ class Database { $config = $this->getConfigUser($userId); if (!isset ($user_config[$key])) { - $sql = "INSERT INTO users_config (`value`, `user_id`, `name`) VALUES (?, ?, ?)"; + $sql = "INSERT INTO users_config (value, user_id, name) VALUES (?, ?, ?)"; } else { - $sql = "UPDATE users_config SET `value`=? WHERE `user_id`=? AND `name`=?"; + $sql = "UPDATE users_config SET value=? WHERE user_id=? AND name=?"; } $params = array($value, $userId, $key); -- cgit v1.2.3