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