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