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