aboutsummaryrefslogtreecommitdiffhomepage
path: root/inc/poche/Database.class.php
diff options
context:
space:
mode:
Diffstat (limited to 'inc/poche/Database.class.php')
-rw-r--r--inc/poche/Database.class.php161
1 files changed, 155 insertions, 6 deletions
diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php
index 5c40b026..d8b63859 100644
--- a/inc/poche/Database.class.php
+++ b/inc/poche/Database.class.php
@@ -39,12 +39,79 @@ class Database {
39 public function isInstalled() { 39 public function isInstalled() {
40 $sql = "SELECT username FROM users"; 40 $sql = "SELECT username FROM users";
41 $query = $this->executeQuery($sql, array()); 41 $query = $this->executeQuery($sql, array());
42 if ($query == false) {
43 die(STORAGE . ' database looks empty. You have to create it (you can find database structure in install folder).');
44 }
42 $hasAdmin = count($query->fetchAll()); 45 $hasAdmin = count($query->fetchAll());
43 46
44 if ($hasAdmin == 0) 47 if ($hasAdmin == 0)
45 return FALSE; 48 return false;
46 49
47 return TRUE; 50 return true;
51 }
52
53 public function checkTags() {
54
55 if (STORAGE == 'sqlite') {
56 $sql = '
57 CREATE TABLE IF NOT EXISTS tags (
58 id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
59 value TEXT
60 )';
61 }
62 elseif(STORAGE == 'mysql') {
63 $sql = '
64 CREATE TABLE IF NOT EXISTS `tags` (
65 `id` int(11) NOT NULL AUTO_INCREMENT,
66 `value` varchar(255) NOT NULL,
67 PRIMARY KEY (`id`)
68 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
69 ';
70 }
71 else {
72 $sql = '
73 CREATE TABLE tags (
74 id bigserial primary key,
75 value varchar(255) NOT NULL
76 );
77 ';
78 }
79
80 $query = $this->executeQuery($sql, array());
81
82 if (STORAGE == 'sqlite') {
83 $sql = '
84 CREATE TABLE tags_entries (
85 id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
86 entry_id INTEGER,
87 tag_id INTEGER,
88 FOREIGN KEY(entry_id) REFERENCES entries(id) ON DELETE CASCADE,
89 FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE
90 )';
91 }
92 elseif(STORAGE == 'mysql') {
93 $sql = '
94 CREATE TABLE IF NOT EXISTS `tags_entries` (
95 `id` int(11) NOT NULL AUTO_INCREMENT,
96 `entry_id` int(11) NOT NULL,
97 `tag_id` int(11) NOT NULL,
98 FOREIGN KEY(entry_id) REFERENCES entries(id) ON DELETE CASCADE,
99 FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE,
100 PRIMARY KEY (`id`)
101 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
102 ';
103 }
104 else {
105 $sql = '
106 CREATE TABLE tags_entries (
107 id bigserial primary key,
108 entry_id integer NOT NULL,
109 tag_id integer NOT NULL
110 )
111 ';
112 }
113
114 $query = $this->executeQuery($sql, array());
48 } 115 }
49 116
50 public function install($login, $password) { 117 public function install($login, $password) {
@@ -74,7 +141,7 @@ class Database {
74 return TRUE; 141 return TRUE;
75 } 142 }
76 143
77 private function getConfigUser($id) { 144 public function getConfigUser($id) {
78 $sql = "SELECT * FROM users_config WHERE user_id = ?"; 145 $sql = "SELECT * FROM users_config WHERE user_id = ?";
79 $query = $this->executeQuery($sql, array($id)); 146 $query = $this->executeQuery($sql, array($id));
80 $result = $query->fetchAll(); 147 $result = $query->fetchAll();
@@ -87,6 +154,17 @@ class Database {
87 return $user_config; 154 return $user_config;
88 } 155 }
89 156
157 public function userExists($username) {
158 $sql = "SELECT * FROM users WHERE username=?";
159 $query = $this->executeQuery($sql, array($username));
160 $login = $query->fetchAll();
161 if (isset($login[0])) {
162 return true;
163 } else {
164 return false;
165 }
166 }
167
90 public function login($username, $password) { 168 public function login($username, $password) {
91 $sql = "SELECT * FROM users WHERE username=? AND password=?"; 169 $sql = "SELECT * FROM users WHERE username=? AND password=?";
92 $query = $this->executeQuery($sql, array($username, $password)); 170 $query = $this->executeQuery($sql, array($username, $password));
@@ -116,10 +194,10 @@ class Database {
116 $config = $this->getConfigUser($userId); 194 $config = $this->getConfigUser($userId);
117 195
118 if (!isset ($user_config[$key])) { 196 if (!isset ($user_config[$key])) {
119 $sql = "INSERT INTO users_config (`value`, `user_id`, `name`) VALUES (?, ?, ?)"; 197 $sql = "INSERT INTO users_config (value, user_id, name) VALUES (?, ?, ?)";
120 } 198 }
121 else { 199 else {
122 $sql = "UPDATE users_config SET `value`=? WHERE `user_id`=? AND `name`=?"; 200 $sql = "UPDATE users_config SET value=? WHERE user_id=? AND name=?";
123 } 201 }
124 202
125 $params = array($value, $userId, $key); 203 $params = array($value, $userId, $key);
@@ -155,7 +233,7 @@ class Database {
155 $query = $this->executeQuery($sql, $params); 233 $query = $this->executeQuery($sql, $params);
156 $entry = $query->fetchAll(); 234 $entry = $query->fetchAll();
157 235
158 return $entry[0]; 236 return isset($entry[0]) ? $entry[0] : null;
159 } 237 }
160 238
161 public function getEntriesByView($view, $user_id, $limit = '') { 239 public function getEntriesByView($view, $user_id, $limit = '') {
@@ -238,4 +316,75 @@ class Database {
238 public function getLastId($column = '') { 316 public function getLastId($column = '') {
239 return $this->getHandle()->lastInsertId($column); 317 return $this->getHandle()->lastInsertId($column);
240 } 318 }
319
320 public function retrieveAllTags() {
321 $sql = "SELECT * FROM tags";
322 $query = $this->executeQuery($sql, array());
323 $tags = $query->fetchAll();
324
325 return $tags;
326 }
327
328 public function retrieveTag($id) {
329 $tag = NULL;
330 $sql = "SELECT * FROM tags WHERE id=?";
331 $params = array(intval($id));
332 $query = $this->executeQuery($sql, $params);
333 $tag = $query->fetchAll();
334
335 return isset($tag[0]) ? $tag[0] : null;
336 }
337
338 public function retrieveEntriesByTag($tag_id) {
339 $sql =
340 "SELECT entries.* FROM entries
341 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
342 WHERE tags_entries.tag_id = ?";
343 $query = $this->executeQuery($sql, array($tag_id));
344 $entries = $query->fetchAll();
345
346 return $entries;
347 }
348
349 public function retrieveTagsByEntry($entry_id) {
350 $sql =
351 "SELECT tags.* FROM tags
352 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
353 WHERE tags_entries.entry_id = ?";
354 $query = $this->executeQuery($sql, array($entry_id));
355 $tags = $query->fetchAll();
356
357 return $tags;
358 }
359
360 public function removeTagForEntry($entry_id, $tag_id) {
361 $sql_action = "DELETE FROM tags_entries WHERE tag_id=? AND entry_id=?";
362 $params_action = array($tag_id, $entry_id);
363 $query = $this->executeQuery($sql_action, $params_action);
364 return $query;
365 }
366
367 public function retrieveTagByValue($value) {
368 $tag = NULL;
369 $sql = "SELECT * FROM tags WHERE value=?";
370 $params = array($value);
371 $query = $this->executeQuery($sql, $params);
372 $tag = $query->fetchAll();
373
374 return isset($tag[0]) ? $tag[0] : null;
375 }
376
377 public function createTag($value) {
378 $sql_action = 'INSERT INTO tags ( value ) VALUES (?)';
379 $params_action = array($value);
380 $query = $this->executeQuery($sql_action, $params_action);
381 return $query;
382 }
383
384 public function setTagToEntry($tag_id, $entry_id) {
385 $sql_action = 'INSERT INTO tags_entries ( tag_id, entry_id ) VALUES (?, ?)';
386 $params_action = array($tag_id, $entry_id);
387 $query = $this->executeQuery($sql_action, $params_action);
388 return $query;
389 }
241} 390}