]>
Commit | Line | Data |
---|---|---|
14890de3 | 1 | <?php |
2 | /** | |
c95b78a8 | 3 | * wallabag, self hostable application allowing you to not miss any content anymore |
14890de3 | 4 | * |
c95b78a8 NL |
5 | * @category wallabag |
6 | * @author Nicolas Lœuillet <nicolas@loeuillet.org> | |
14890de3 | 7 | * @copyright 2013 |
8 | * @license http://www.wtfpl.net/ see COPYING file | |
9 | */ | |
10 | ||
bc1ee852 | 11 | class Database { |
14890de3 | 12 | var $handle; |
032e0ca1 MR |
13 | private $order = array( |
14 | 'ia' => 'ORDER BY entries.id', | |
15 | 'id' => 'ORDER BY entries.id DESC', | |
16 | 'ta' => 'ORDER BY lower(entries.title)', | |
17 | 'td' => 'ORDER BY lower(entries.title) DESC', | |
18 | 'default' => 'ORDER BY entries.id' | |
19 | ); | |
20 | ||
182faf26 | 21 | function __construct() |
68857cea NL |
22 | { |
23 | switch (STORAGE) { | |
24 | case 'sqlite': | |
25 | $db_path = 'sqlite:' . STORAGE_SQLITE; | |
26 | $this->handle = new PDO($db_path); | |
27 | break; | |
28 | case 'mysql': | |
29 | $db_path = 'mysql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB; | |
182faf26 | 30 | $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD); |
68857cea NL |
31 | break; |
32 | case 'postgres': | |
33 | $db_path = 'pgsql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB; | |
182faf26 | 34 | $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD); |
68857cea | 35 | break; |
8af31ae0 | 36 | default: |
37 | die(STORAGE . ' is not a recognised database system !'); | |
68857cea NL |
38 | } |
39 | ||
14890de3 | 40 | $this->handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); |
580d60b9 | 41 | Tools::logm('storage type ' . STORAGE); |
14890de3 | 42 | } |
43 | ||
44 | private function getHandle() { | |
45 | return $this->handle; | |
46 | } | |
47 | ||
aa8c9f2a | 48 | public function isInstalled() { |
b916bcfc NL |
49 | $sql = "SELECT username FROM users"; |
50 | $query = $this->executeQuery($sql, array()); | |
5cfafc61 NL |
51 | if ($query == false) { |
52 | die(STORAGE . ' database looks empty. You have to create it (you can find database structure in install folder).'); | |
53 | } | |
b916bcfc | 54 | $hasAdmin = count($query->fetchAll()); |
aa8c9f2a | 55 | |
182faf26 | 56 | if ($hasAdmin == 0) |
5cfafc61 NL |
57 | return false; |
58 | ||
59 | return true; | |
60 | } | |
61 | ||
62 | public function checkTags() { | |
63 | ||
64 | if (STORAGE == 'sqlite') { | |
65 | $sql = ' | |
66 | CREATE TABLE IF NOT EXISTS tags ( | |
67 | id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, | |
68 | value TEXT | |
69 | )'; | |
70 | } | |
71 | elseif(STORAGE == 'mysql') { | |
72 | $sql = ' | |
73 | CREATE TABLE IF NOT EXISTS `tags` ( | |
74 | `id` int(11) NOT NULL AUTO_INCREMENT, | |
75 | `value` varchar(255) NOT NULL, | |
76 | PRIMARY KEY (`id`) | |
77 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; | |
78 | '; | |
79 | } | |
80 | else { | |
81 | $sql = ' | |
5ce39784 | 82 | CREATE TABLE IF NOT EXISTS tags ( |
5cfafc61 NL |
83 | id bigserial primary key, |
84 | value varchar(255) NOT NULL | |
85 | ); | |
86 | '; | |
87 | } | |
aa8c9f2a | 88 | |
5cfafc61 NL |
89 | $query = $this->executeQuery($sql, array()); |
90 | ||
91 | if (STORAGE == 'sqlite') { | |
92 | $sql = ' | |
a562e390 | 93 | CREATE TABLE IF NOT EXISTS tags_entries ( |
5cfafc61 NL |
94 | id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, |
95 | entry_id INTEGER, | |
96 | tag_id INTEGER, | |
97 | FOREIGN KEY(entry_id) REFERENCES entries(id) ON DELETE CASCADE, | |
98 | FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE | |
99 | )'; | |
100 | } | |
101 | elseif(STORAGE == 'mysql') { | |
102 | $sql = ' | |
103 | CREATE TABLE IF NOT EXISTS `tags_entries` ( | |
104 | `id` int(11) NOT NULL AUTO_INCREMENT, | |
105 | `entry_id` int(11) NOT NULL, | |
106 | `tag_id` int(11) NOT NULL, | |
107 | FOREIGN KEY(entry_id) REFERENCES entries(id) ON DELETE CASCADE, | |
108 | FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE, | |
109 | PRIMARY KEY (`id`) | |
110 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; | |
111 | '; | |
112 | } | |
113 | else { | |
114 | $sql = ' | |
5ce39784 | 115 | CREATE TABLE IF NOT EXISTS tags_entries ( |
5cfafc61 NL |
116 | id bigserial primary key, |
117 | entry_id integer NOT NULL, | |
118 | tag_id integer NOT NULL | |
119 | ) | |
120 | '; | |
121 | } | |
122 | ||
123 | $query = $this->executeQuery($sql, array()); | |
aa8c9f2a NL |
124 | } |
125 | ||
126 | public function install($login, $password) { | |
b916bcfc NL |
127 | $sql = 'INSERT INTO users ( username, password, name, email) VALUES (?, ?, ?, ?)'; |
128 | $params = array($login, $password, $login, ' '); | |
129 | $query = $this->executeQuery($sql, $params); | |
130 | ||
131 | $sequence = ''; | |
132 | if (STORAGE == 'postgres') { | |
133 | $sequence = 'users_id_seq'; | |
134 | } | |
135 | ||
136 | $id_user = intval($this->getLastId($sequence)); | |
137 | ||
138 | $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; | |
00dbaf90 | 139 | $params = array($id_user, 'pager', PAGINATION); |
b916bcfc NL |
140 | $query = $this->executeQuery($sql, $params); |
141 | ||
142 | $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; | |
00dbaf90 NL |
143 | $params = array($id_user, 'language', LANG); |
144 | $query = $this->executeQuery($sql, $params); | |
182faf26 | 145 | |
00dbaf90 NL |
146 | $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; |
147 | $params = array($id_user, 'theme', DEFAULT_THEME); | |
7ce7ec4c | 148 | $query = $this->executeQuery($sql, $params); |
aa8c9f2a | 149 | |
7ce7ec4c NL |
150 | return TRUE; |
151 | } | |
aa8c9f2a | 152 | |
5846b0f1 | 153 | public function getConfigUser($id) { |
7ce7ec4c NL |
154 | $sql = "SELECT * FROM users_config WHERE user_id = ?"; |
155 | $query = $this->executeQuery($sql, array($id)); | |
156 | $result = $query->fetchAll(); | |
157 | $user_config = array(); | |
182faf26 | 158 | |
7ce7ec4c NL |
159 | foreach ($result as $key => $value) { |
160 | $user_config[$value['name']] = $value['value']; | |
aa8c9f2a NL |
161 | } |
162 | ||
7ce7ec4c | 163 | return $user_config; |
aa8c9f2a NL |
164 | } |
165 | ||
027b4e15 DS |
166 | public function userExists($username) { |
167 | $sql = "SELECT * FROM users WHERE username=?"; | |
168 | $query = $this->executeQuery($sql, array($username)); | |
169 | $login = $query->fetchAll(); | |
170 | if (isset($login[0])) { | |
171 | return true; | |
172 | } else { | |
173 | return false; | |
174 | } | |
175 | } | |
176 | ||
6af66b11 MR |
177 | public function login($username, $password, $isauthenticated=false) { |
178 | if ($isauthenticated) { | |
179 | $sql = "SELECT * FROM users WHERE username=?"; | |
180 | $query = $this->executeQuery($sql, array($username)); | |
181 | } else { | |
182 | $sql = "SELECT * FROM users WHERE username=? AND password=?"; | |
183 | $query = $this->executeQuery($sql, array($username, $password)); | |
184 | } | |
8d3275be | 185 | $login = $query->fetchAll(); |
aa8c9f2a | 186 | |
7ce7ec4c NL |
187 | $user = array(); |
188 | if (isset($login[0])) { | |
189 | $user['id'] = $login[0]['id']; | |
190 | $user['username'] = $login[0]['username']; | |
191 | $user['password'] = $login[0]['password']; | |
192 | $user['name'] = $login[0]['name']; | |
193 | $user['email'] = $login[0]['email']; | |
194 | $user['config'] = $this->getConfigUser($login[0]['id']); | |
195 | } | |
aa8c9f2a | 196 | |
7ce7ec4c | 197 | return $user; |
aa8c9f2a NL |
198 | } |
199 | ||
00dbaf90 | 200 | public function updatePassword($userId, $password) |
6499b26a | 201 | { |
8d3275be | 202 | $sql_update = "UPDATE users SET password=? WHERE id=?"; |
1e98ee1d NL |
203 | $params_update = array($password, $userId); |
204 | $query = $this->executeQuery($sql_update, $params_update); | |
00dbaf90 | 205 | } |
182faf26 | 206 | |
00dbaf90 | 207 | public function updateUserConfig($userId, $key, $value) { |
58ace494 | 208 | $config = $this->getConfigUser($userId); |
182faf26 | 209 | |
d1d3498b | 210 | if (! isset($config[$key])) { |
da5fc42f | 211 | $sql = "INSERT INTO users_config (value, user_id, name) VALUES (?, ?, ?)"; |
58ace494 NL |
212 | } |
213 | else { | |
da5fc42f | 214 | $sql = "UPDATE users_config SET value=? WHERE user_id=? AND name=?"; |
58ace494 NL |
215 | } |
216 | ||
217 | $params = array($value, $userId, $key); | |
218 | $query = $this->executeQuery($sql, $params); | |
6499b26a NL |
219 | } |
220 | ||
14890de3 | 221 | private function executeQuery($sql, $params) { |
222 | try | |
223 | { | |
224 | $query = $this->getHandle()->prepare($sql); | |
225 | $query->execute($params); | |
226 | return $query; | |
227 | } | |
228 | catch (Exception $e) | |
229 | { | |
eb1af592 | 230 | Tools::logm('execute query error : '.$e->getMessage()); |
bc1ee852 | 231 | return FALSE; |
14890de3 | 232 | } |
233 | } | |
4d99bae8 | 234 | |
235 | public function listUsers($username=null) { | |
236 | $sql = 'SELECT count(*) FROM users'.( $username ? ' WHERE username=?' : ''); | |
237 | $query = $this->executeQuery($sql, ( $username ? array($username) : array())); | |
238 | list($count) = $query->fetch(); | |
239 | return $count; | |
240 | } | |
241 | ||
242 | public function getUserPassword($userID) { | |
243 | $sql = "SELECT * FROM users WHERE id=?"; | |
244 | $query = $this->executeQuery($sql, array($userID)); | |
245 | $password = $query->fetchAll(); | |
246 | return isset($password[0]['password']) ? $password[0]['password'] : null; | |
247 | } | |
248 | ||
249 | public function deleteUserConfig($userID) { | |
250 | $sql_action = 'DELETE from users_config WHERE user_id=?'; | |
251 | $params_action = array($userID); | |
252 | $query = $this->executeQuery($sql_action, $params_action); | |
253 | return $query; | |
254 | } | |
255 | ||
256 | public function deleteTagsEntriesAndEntries($userID) { | |
257 | $entries = $this->retrieveAll($userID); | |
258 | foreach($entries as $entryid) { | |
259 | $tags = $this->retrieveTagsByEntry($entryid); | |
260 | foreach($tags as $tag) { | |
261 | $this->removeTagForEntry($entryid,$tags); | |
262 | } | |
263 | $this->deleteById($entryid,$userID); | |
264 | } | |
265 | } | |
266 | ||
267 | public function deleteUser($userID) { | |
268 | $sql_action = 'DELETE from users WHERE id=?'; | |
269 | $params_action = array($userID); | |
270 | $query = $this->executeQuery($sql_action, $params_action); | |
271 | } | |
14890de3 | 272 | |
53e3158d NL |
273 | public function updateContentAndTitle($id, $title, $body, $user_id) { |
274 | $sql_action = 'UPDATE entries SET content = ?, title = ? WHERE id=? AND user_id=?'; | |
275 | $params_action = array($body, $title, $id, $user_id); | |
276 | $query = $this->executeQuery($sql_action, $params_action); | |
53e3158d NL |
277 | return $query; |
278 | } | |
279 | ||
280 | public function retrieveUnfetchedEntries($user_id, $limit) { | |
281 | ||
282 | $sql_limit = "LIMIT 0,".$limit; | |
283 | if (STORAGE == 'postgres') { | |
284 | $sql_limit = "LIMIT ".$limit." OFFSET 0"; | |
285 | } | |
286 | ||
0f859c6f | 287 | $sql = "SELECT * FROM entries WHERE (content = '' OR content IS NULL) AND title LIKE 'Untitled - Import%' AND user_id=? ORDER BY id " . $sql_limit; |
53e3158d NL |
288 | $query = $this->executeQuery($sql, array($user_id)); |
289 | $entries = $query->fetchAll(); | |
290 | ||
291 | return $entries; | |
292 | } | |
293 | ||
182faf26 | 294 | public function retrieveUnfetchedEntriesCount($user_id) { |
0f859c6f | 295 | $sql = "SELECT count(*) FROM entries WHERE (content = '' OR content IS NULL) AND title LIKE 'Untitled - Import%' AND user_id=?"; |
182faf26 MR |
296 | $query = $this->executeQuery($sql, array($user_id)); |
297 | list($count) = $query->fetch(); | |
298 | ||
299 | return $count; | |
300 | } | |
301 | ||
8d3275be | 302 | public function retrieveAll($user_id) { |
182faf26 | 303 | $sql = "SELECT * FROM entries WHERE user_id=? ORDER BY id"; |
8d3275be | 304 | $query = $this->executeQuery($sql, array($user_id)); |
44e77bfa | 305 | $entries = $query->fetchAll(); |
306 | ||
307 | return $entries; | |
308 | } | |
309 | ||
8d3275be | 310 | public function retrieveOneById($id, $user_id) { |
14890de3 | 311 | $entry = NULL; |
8d3275be NL |
312 | $sql = "SELECT * FROM entries WHERE id=? AND user_id=?"; |
313 | $params = array(intval($id), $user_id); | |
14890de3 | 314 | $query = $this->executeQuery($sql, $params); |
315 | $entry = $query->fetchAll(); | |
316 | ||
0c2f4537 | 317 | return isset($entry[0]) ? $entry[0] : null; |
14890de3 | 318 | } |
319 | ||
488fc63b MR |
320 | public function retrieveOneByURL($url, $user_id) { |
321 | $entry = NULL; | |
182faf26 | 322 | $sql = "SELECT * FROM entries WHERE url=? AND user_id=?"; |
488fc63b MR |
323 | $params = array($url, $user_id); |
324 | $query = $this->executeQuery($sql, $params); | |
325 | $entry = $query->fetchAll(); | |
326 | ||
327 | return isset($entry[0]) ? $entry[0] : null; | |
328 | } | |
329 | ||
330 | public function reassignTags($old_entry_id, $new_entry_id) { | |
331 | $sql = "UPDATE tags_entries SET entry_id=? WHERE entry_id=?"; | |
332 | $params = array($new_entry_id, $old_entry_id); | |
333 | $query = $this->executeQuery($sql, $params); | |
334 | } | |
335 | ||
032e0ca1 MR |
336 | public function getEntriesByView($view, $user_id, $limit = '', $tag_id = 0) { |
337 | switch ($view) { | |
338 | case 'archive': | |
182faf26 | 339 | $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? "; |
032e0ca1 | 340 | $params = array($user_id, 1); |
14890de3 | 341 | break; |
032e0ca1 | 342 | case 'fav' : |
182faf26 | 343 | $sql = "SELECT * FROM entries WHERE user_id=? AND is_fav=? "; |
032e0ca1 | 344 | $params = array($user_id, 1); |
14890de3 | 345 | break; |
032e0ca1 MR |
346 | case 'tag' : |
347 | $sql = "SELECT entries.* FROM entries | |
348 | LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id | |
182faf26 | 349 | WHERE entries.user_id=? AND tags_entries.tag_id = ? "; |
032e0ca1 | 350 | $params = array($user_id, $tag_id); |
14890de3 | 351 | break; |
352 | default: | |
182faf26 | 353 | $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? "; |
032e0ca1 | 354 | $params = array($user_id, 0); |
14890de3 | 355 | break; |
356 | } | |
357 | ||
032e0ca1 MR |
358 | $sql .= $this->getEntriesOrder().' ' . $limit; |
359 | ||
360 | $query = $this->executeQuery($sql, $params); | |
361 | $entries = $query->fetchAll(); | |
362 | ||
363 | return $entries; | |
364 | } | |
365 | ||
53e3158d NL |
366 | public function getEntriesByViewCount($view, $user_id, $tag_id = 0) { |
367 | switch ($view) { | |
14890de3 | 368 | case 'archive': |
182faf26 | 369 | $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? "; |
bc1ee852 | 370 | $params = array($user_id, 1); |
14890de3 | 371 | break; |
372 | case 'fav' : | |
182faf26 | 373 | $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_fav=? "; |
bc1ee852 | 374 | $params = array($user_id, 1); |
14890de3 | 375 | break; |
53e3158d NL |
376 | case 'tag' : |
377 | $sql = "SELECT count(*) FROM entries | |
378 | LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id | |
182faf26 | 379 | WHERE entries.user_id=? AND tags_entries.tag_id = ? "; |
53e3158d NL |
380 | $params = array($user_id, $tag_id); |
381 | break; | |
14890de3 | 382 | default: |
182faf26 | 383 | $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? "; |
8d3275be | 384 | $params = array($user_id, 0); |
14890de3 | 385 | break; |
386 | } | |
387 | ||
8d3275be | 388 | $query = $this->executeQuery($sql, $params); |
032e0ca1 | 389 | list($count) = $query->fetch(); |
14890de3 | 390 | |
53e3158d | 391 | return $count; |
14890de3 | 392 | } |
393 | ||
b58e261d NL |
394 | public function updateContent($id, $content, $user_id) { |
395 | $sql_action = 'UPDATE entries SET content = ? WHERE id=? AND user_id=?'; | |
396 | $params_action = array($content, $id, $user_id); | |
397 | $query = $this->executeQuery($sql_action, $params_action); | |
398 | return $query; | |
399 | } | |
400 | ||
182faf26 MR |
401 | /** |
402 | * | |
403 | * @param string $url | |
404 | * @param string $title | |
405 | * @param string $content | |
406 | * @param integer $user_id | |
407 | * @return integer $id of inserted record | |
408 | */ | |
409 | public function add($url, $title, $content, $user_id, $isFavorite=0, $isRead=0) { | |
410 | $sql_action = 'INSERT INTO entries ( url, title, content, user_id, is_fav, is_read ) VALUES (?, ?, ?, ?, ?, ?)'; | |
411 | $params_action = array($url, $title, $content, $user_id, $isFavorite, $isRead); | |
412 | if ( !$this->executeQuery($sql_action, $params_action) ) { | |
413 | $id = null; | |
414 | } | |
415 | else { | |
1bcbe8be | 416 | $id = intval($this->getLastId( (STORAGE == 'postgres') ? 'entries_id_seq' : '') ); |
182faf26 MR |
417 | } |
418 | return $id; | |
14890de3 | 419 | } |
420 | ||
8d3275be | 421 | public function deleteById($id, $user_id) { |
8d3275be NL |
422 | $sql_action = "DELETE FROM entries WHERE id=? AND user_id=?"; |
423 | $params_action = array($id, $user_id); | |
14890de3 | 424 | $query = $this->executeQuery($sql_action, $params_action); |
29c6fd46 | 425 | return $query; |
14890de3 | 426 | } |
427 | ||
8d3275be | 428 | public function favoriteById($id, $user_id) { |
bc1ee852 | 429 | $sql_action = "UPDATE entries SET is_fav=NOT is_fav WHERE id=? AND user_id=?"; |
8d3275be | 430 | $params_action = array($id, $user_id); |
14890de3 | 431 | $query = $this->executeQuery($sql_action, $params_action); |
432 | } | |
433 | ||
8d3275be | 434 | public function archiveById($id, $user_id) { |
bc1ee852 | 435 | $sql_action = "UPDATE entries SET is_read=NOT is_read WHERE id=? AND user_id=?"; |
8d3275be | 436 | $params_action = array($id, $user_id); |
14890de3 | 437 | $query = $this->executeQuery($sql_action, $params_action); |
438 | } | |
439 | ||
f14807de NL |
440 | public function archiveAll($user_id) { |
441 | $sql_action = "UPDATE entries SET is_read=? WHERE user_id=? AND is_read=?"; | |
442 | $params_action = array($user_id, 1, 0); | |
443 | $query = $this->executeQuery($sql_action, $params_action); | |
444 | } | |
445 | ||
b916bcfc NL |
446 | public function getLastId($column = '') { |
447 | return $this->getHandle()->lastInsertId($column); | |
14890de3 | 448 | } |
5ce39784 | 449 | |
a4585f7e MR |
450 | public function search($term, $user_id, $limit = '') { |
451 | $search = '%'.$term.'%'; | |
452 | $sql_action = "SELECT * FROM entries WHERE user_id=? AND (content LIKE ? OR title LIKE ? OR url LIKE ?) "; //searches in content, title and URL | |
453 | $sql_action .= $this->getEntriesOrder().' ' . $limit; | |
454 | $params_action = array($user_id, $search, $search, $search); | |
455 | $query = $this->executeQuery($sql_action, $params_action); | |
456 | return $query->fetchAll(); | |
457 | } | |
5bea1a4d | 458 | |
fb26cc93 MR |
459 | public function retrieveAllTags($user_id, $term = null) { |
460 | $sql = "SELECT DISTINCT tags.*, count(entries.id) AS entriescount FROM tags | |
b89d5a2b NL |
461 | LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id |
462 | LEFT JOIN entries ON tags_entries.entry_id=entries.id | |
182faf26 | 463 | WHERE entries.user_id=? |
fb26cc93 MR |
464 | ". (($term) ? "AND lower(tags.value) LIKE ?" : '') ." |
465 | GROUP BY tags.id, tags.value | |
466 | ORDER BY tags.value"; | |
467 | $query = $this->executeQuery($sql, (($term)? array($user_id, strtolower('%'.$term.'%')) : array($user_id) )); | |
2e2ebe5e NL |
468 | $tags = $query->fetchAll(); |
469 | ||
470 | return $tags; | |
471 | } | |
472 | ||
b89d5a2b | 473 | public function retrieveTag($id, $user_id) { |
4886ed6d | 474 | $tag = NULL; |
e83cf5a7 | 475 | $sql = "SELECT DISTINCT tags.* FROM tags |
b89d5a2b NL |
476 | LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id |
477 | LEFT JOIN entries ON tags_entries.entry_id=entries.id | |
182faf26 | 478 | WHERE tags.id=? AND entries.user_id=?"; |
b89d5a2b | 479 | $params = array(intval($id), $user_id); |
4886ed6d NL |
480 | $query = $this->executeQuery($sql, $params); |
481 | $tag = $query->fetchAll(); | |
482 | ||
483 | return isset($tag[0]) ? $tag[0] : null; | |
484 | } | |
485 | ||
b89d5a2b | 486 | public function retrieveEntriesByTag($tag_id, $user_id) { |
182faf26 | 487 | $sql = |
9de34d4e | 488 | "SELECT entries.* FROM entries |
4886ed6d | 489 | LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id |
182faf26 | 490 | WHERE tags_entries.tag_id = ? AND entries.user_id=?"; |
b89d5a2b | 491 | $query = $this->executeQuery($sql, array($tag_id, $user_id)); |
4886ed6d NL |
492 | $entries = $query->fetchAll(); |
493 | ||
494 | return $entries; | |
495 | } | |
496 | ||
5bea1a4d | 497 | public function retrieveTagsByEntry($entry_id) { |
182faf26 | 498 | $sql = |
9de34d4e | 499 | "SELECT tags.* FROM tags |
5bea1a4d NL |
500 | LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id |
501 | WHERE tags_entries.entry_id = ?"; | |
2e2ebe5e NL |
502 | $query = $this->executeQuery($sql, array($entry_id)); |
503 | $tags = $query->fetchAll(); | |
5bea1a4d NL |
504 | |
505 | return $tags; | |
506 | } | |
c432fa16 NL |
507 | |
508 | public function removeTagForEntry($entry_id, $tag_id) { | |
509 | $sql_action = "DELETE FROM tags_entries WHERE tag_id=? AND entry_id=?"; | |
510 | $params_action = array($tag_id, $entry_id); | |
511 | $query = $this->executeQuery($sql_action, $params_action); | |
512 | return $query; | |
513 | } | |
9c743ab9 | 514 | |
24696800 | 515 | public function cleanUnusedTag($tag_id) { |
516 | $sql_action = "SELECT tags.* FROM tags JOIN tags_entries ON tags_entries.tag_id=tags.id WHERE tags.id=?"; | |
517 | $query = $this->executeQuery($sql_action,array($tag_id)); | |
9c743ab9 | 518 | $tagstokeep = $query->fetchAll(); |
24696800 | 519 | $sql_action = "SELECT tags.* FROM tags LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id WHERE tags.id=?"; |
520 | $query = $this->executeQuery($sql_action,array($tag_id)); | |
9c743ab9 | 521 | $alltags = $query->fetchAll(); |
24696800 | 522 | |
9c743ab9 | 523 | foreach ($alltags as $tag) { |
524 | if ($tag && !in_array($tag,$tagstokeep)) { | |
9c743ab9 | 525 | $sql_action = "DELETE FROM tags WHERE id=?"; |
526 | $params_action = array($tag[0]); | |
24696800 | 527 | $this->executeQuery($sql_action, $params_action); |
528 | return true; | |
9c743ab9 | 529 | } |
530 | } | |
24696800 | 531 | |
9c743ab9 | 532 | } |
c432fa16 NL |
533 | |
534 | public function retrieveTagByValue($value) { | |
535 | $tag = NULL; | |
536 | $sql = "SELECT * FROM tags WHERE value=?"; | |
537 | $params = array($value); | |
538 | $query = $this->executeQuery($sql, $params); | |
539 | $tag = $query->fetchAll(); | |
540 | ||
541 | return isset($tag[0]) ? $tag[0] : null; | |
542 | } | |
543 | ||
544 | public function createTag($value) { | |
545 | $sql_action = 'INSERT INTO tags ( value ) VALUES (?)'; | |
546 | $params_action = array($value); | |
547 | $query = $this->executeQuery($sql_action, $params_action); | |
548 | return $query; | |
549 | } | |
550 | ||
551 | public function setTagToEntry($tag_id, $entry_id) { | |
552 | $sql_action = 'INSERT INTO tags_entries ( tag_id, entry_id ) VALUES (?, ?)'; | |
553 | $params_action = array($tag_id, $entry_id); | |
554 | $query = $this->executeQuery($sql_action, $params_action); | |
555 | return $query; | |
556 | } | |
032e0ca1 MR |
557 | |
558 | private function getEntriesOrder() { | |
559 | if (isset($_SESSION['sort']) and array_key_exists($_SESSION['sort'], $this->order)) { | |
560 | return $this->order[$_SESSION['sort']]; | |
561 | } | |
562 | else { | |
563 | return $this->order['default']; | |
564 | } | |
565 | } | |
566 | ||
2cd263e0 | 567 | } |