aboutsummaryrefslogtreecommitdiffhomepage
path: root/inc/poche/Database.class.php
diff options
context:
space:
mode:
authorNicolas LÅ“uillet <nicolas@loeuillet.org>2014-07-11 17:06:51 +0200
committerNicolas LÅ“uillet <nicolas@loeuillet.org>2014-07-11 17:06:51 +0200
commitb3cda72e93fff3a4c3476e9e7e78ef2b2a3f02b9 (patch)
treefb23b73225da5bc3a4466a51915586f60dde802c /inc/poche/Database.class.php
parent3602405ec0dbc576fce09ff9e865ba2404622080 (diff)
downloadwallabag-b3cda72e93fff3a4c3476e9e7e78ef2b2a3f02b9.tar.gz
wallabag-b3cda72e93fff3a4c3476e9e7e78ef2b2a3f02b9.tar.zst
wallabag-b3cda72e93fff3a4c3476e9e7e78ef2b2a3f02b9.zip
PicoFarad framework for routing
Diffstat (limited to 'inc/poche/Database.class.php')
-rwxr-xr-xinc/poche/Database.class.php138
1 files changed, 89 insertions, 49 deletions
diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php
index 9c1c0286..8d74f2ff 100755
--- a/inc/poche/Database.class.php
+++ b/inc/poche/Database.class.php
@@ -9,6 +9,7 @@
9 */ 9 */
10 10
11class Database { 11class Database {
12
12 var $handle; 13 var $handle;
13 private $order = array( 14 private $order = array(
14 'ia' => 'ORDER BY entries.id', 15 'ia' => 'ORDER BY entries.id',
@@ -42,11 +43,13 @@ class Database {
42 Tools::logm('storage type ' . STORAGE); 43 Tools::logm('storage type ' . STORAGE);
43 } 44 }
44 45
45 private function getHandle() { 46 private function getHandle()
47 {
46 return $this->handle; 48 return $this->handle;
47 } 49 }
48 50
49 private function _checkTags() { 51 private function _checkTags()
52 {
50 53
51 if (STORAGE == 'sqlite') { 54 if (STORAGE == 'sqlite') {
52 $sql = ' 55 $sql = '
@@ -110,7 +113,8 @@ class Database {
110 $query = $this->executeQuery($sql, array()); 113 $query = $this->executeQuery($sql, array());
111 } 114 }
112 115
113 public function install($login, $password) { 116 public function install($login, $password)
117 {
114 $sql = 'INSERT INTO users ( username, password, name, email) VALUES (?, ?, ?, ?)'; 118 $sql = 'INSERT INTO users ( username, password, name, email) VALUES (?, ?, ?, ?)';
115 $params = array($login, $password, $login, ' '); 119 $params = array($login, $password, $login, ' ');
116 $query = $this->executeQuery($sql, $params); 120 $query = $this->executeQuery($sql, $params);
@@ -137,7 +141,8 @@ class Database {
137 return TRUE; 141 return TRUE;
138 } 142 }
139 143
140 public function getConfigUser($id) { 144 public function getConfigUser($id)
145 {
141 $sql = "SELECT * FROM users_config WHERE user_id = ?"; 146 $sql = "SELECT * FROM users_config WHERE user_id = ?";
142 $query = $this->executeQuery($sql, array($id)); 147 $query = $this->executeQuery($sql, array($id));
143 $result = $query->fetchAll(); 148 $result = $query->fetchAll();
@@ -150,7 +155,8 @@ class Database {
150 return $user_config; 155 return $user_config;
151 } 156 }
152 157
153 public function userExists($username) { 158 public function userExists($username)
159 {
154 $sql = "SELECT * FROM users WHERE username=?"; 160 $sql = "SELECT * FROM users WHERE username=?";
155 $query = $this->executeQuery($sql, array($username)); 161 $query = $this->executeQuery($sql, array($username));
156 $login = $query->fetchAll(); 162 $login = $query->fetchAll();
@@ -161,7 +167,8 @@ class Database {
161 } 167 }
162 } 168 }
163 169
164 public function login($username, $password, $isauthenticated=false) { 170 public function login($username, $password, $isauthenticated = FALSE)
171 {
165 if ($isauthenticated) { 172 if ($isauthenticated) {
166 $sql = "SELECT * FROM users WHERE username=?"; 173 $sql = "SELECT * FROM users WHERE username=?";
167 $query = $this->executeQuery($sql, array($username)); 174 $query = $this->executeQuery($sql, array($username));
@@ -191,7 +198,8 @@ class Database {
191 $query = $this->executeQuery($sql_update, $params_update); 198 $query = $this->executeQuery($sql_update, $params_update);
192 } 199 }
193 200
194 public function updateUserConfig($userId, $key, $value) { 201 public function updateUserConfig($userId, $key, $value)
202 {
195 $config = $this->getConfigUser($userId); 203 $config = $this->getConfigUser($userId);
196 204
197 if (! isset($config[$key])) { 205 if (! isset($config[$key])) {
@@ -205,7 +213,8 @@ class Database {
205 $query = $this->executeQuery($sql, $params); 213 $query = $this->executeQuery($sql, $params);
206 } 214 }
207 215
208 private function executeQuery($sql, $params) { 216 private function executeQuery($sql, $params)
217 {
209 try 218 try
210 { 219 {
211 $query = $this->getHandle()->prepare($sql); 220 $query = $this->getHandle()->prepare($sql);
@@ -219,28 +228,32 @@ class Database {
219 } 228 }
220 } 229 }
221 230
222 public function listUsers($username=null) { 231 public function listUsers($username = NULL)
232 {
223 $sql = 'SELECT count(*) FROM users'.( $username ? ' WHERE username=?' : ''); 233 $sql = 'SELECT count(*) FROM users'.( $username ? ' WHERE username=?' : '');
224 $query = $this->executeQuery($sql, ( $username ? array($username) : array())); 234 $query = $this->executeQuery($sql, ( $username ? array($username) : array()));
225 list($count) = $query->fetch(); 235 list($count) = $query->fetch();
226 return $count; 236 return $count;
227 } 237 }
228 238
229 public function getUserPassword($userID) { 239 public function getUserPassword($userID)
240 {
230 $sql = "SELECT * FROM users WHERE id=?"; 241 $sql = "SELECT * FROM users WHERE id=?";
231 $query = $this->executeQuery($sql, array($userID)); 242 $query = $this->executeQuery($sql, array($userID));
232 $password = $query->fetchAll(); 243 $password = $query->fetchAll();
233 return isset($password[0]['password']) ? $password[0]['password'] : null; 244 return isset($password[0]['password']) ? $password[0]['password'] : null;
234 } 245 }
235 246
236 public function deleteUserConfig($userID) { 247 public function deleteUserConfig($userID)
248 {
237 $sql_action = 'DELETE from users_config WHERE user_id=?'; 249 $sql_action = 'DELETE from users_config WHERE user_id=?';
238 $params_action = array($userID); 250 $params_action = array($userID);
239 $query = $this->executeQuery($sql_action, $params_action); 251 $query = $this->executeQuery($sql_action, $params_action);
240 return $query; 252 return $query;
241 } 253 }
242 254
243 public function deleteTagsEntriesAndEntries($userID) { 255 public function deleteTagsEntriesAndEntries($userID)
256 {
244 $entries = $this->retrieveAll($userID); 257 $entries = $this->retrieveAll($userID);
245 foreach($entries as $entryid) { 258 foreach($entries as $entryid) {
246 $tags = $this->retrieveTagsByEntry($entryid); 259 $tags = $this->retrieveTagsByEntry($entryid);
@@ -251,20 +264,23 @@ class Database {
251 } 264 }
252 } 265 }
253 266
254 public function deleteUser($userID) { 267 public function deleteUser($userID)
268 {
255 $sql_action = 'DELETE from users WHERE id=?'; 269 $sql_action = 'DELETE from users WHERE id=?';
256 $params_action = array($userID); 270 $params_action = array($userID);
257 $query = $this->executeQuery($sql_action, $params_action); 271 $query = $this->executeQuery($sql_action, $params_action);
258 } 272 }
259 273
260 public function updateContentAndTitle($id, $title, $body, $user_id) { 274 public function updateContentAndTitle($id, $title, $body, $user_id)
275 {
261 $sql_action = 'UPDATE entries SET content = ?, title = ? WHERE id=? AND user_id=?'; 276 $sql_action = 'UPDATE entries SET content = ?, title = ? WHERE id=? AND user_id=?';
262 $params_action = array($body, $title, $id, $user_id); 277 $params_action = array($body, $title, $id, $user_id);
263 $query = $this->executeQuery($sql_action, $params_action); 278 $query = $this->executeQuery($sql_action, $params_action);
264 return $query; 279 return $query;
265 } 280 }
266 281
267 public function retrieveUnfetchedEntries($user_id, $limit) { 282 public function retrieveUnfetchedEntries($user_id, $limit)
283 {
268 284
269 $sql_limit = "LIMIT 0,".$limit; 285 $sql_limit = "LIMIT 0,".$limit;
270 if (STORAGE == 'postgres') { 286 if (STORAGE == 'postgres') {
@@ -278,7 +294,8 @@ class Database {
278 return $entries; 294 return $entries;
279 } 295 }
280 296
281 public function retrieveUnfetchedEntriesCount($user_id) { 297 public function retrieveUnfetchedEntriesCount($user_id)
298 {
282 $sql = "SELECT count(*) FROM entries WHERE (content = '' OR content IS NULL) AND title LIKE 'Untitled - Import%' AND user_id=?"; 299 $sql = "SELECT count(*) FROM entries WHERE (content = '' OR content IS NULL) AND title LIKE 'Untitled - Import%' AND user_id=?";
283 $query = $this->executeQuery($sql, array($user_id)); 300 $query = $this->executeQuery($sql, array($user_id));
284 list($count) = $query->fetch(); 301 list($count) = $query->fetch();
@@ -286,7 +303,8 @@ class Database {
286 return $count; 303 return $count;
287 } 304 }
288 305
289 public function retrieveAll($user_id) { 306 public function retrieveAll($user_id)
307 {
290 $sql = "SELECT * FROM entries WHERE user_id=? ORDER BY id"; 308 $sql = "SELECT * FROM entries WHERE user_id=? ORDER BY id";
291 $query = $this->executeQuery($sql, array($user_id)); 309 $query = $this->executeQuery($sql, array($user_id));
292 $entries = $query->fetchAll(); 310 $entries = $query->fetchAll();
@@ -294,7 +312,8 @@ class Database {
294 return $entries; 312 return $entries;
295 } 313 }
296 314
297 public function retrieveOneById($id, $user_id) { 315 public function retrieveOneById($id, $user_id)
316 {
298 $entry = NULL; 317 $entry = NULL;
299 $sql = "SELECT * FROM entries WHERE id=? AND user_id=?"; 318 $sql = "SELECT * FROM entries WHERE id=? AND user_id=?";
300 $params = array(intval($id), $user_id); 319 $params = array(intval($id), $user_id);
@@ -304,7 +323,8 @@ class Database {
304 return isset($entry[0]) ? $entry[0] : null; 323 return isset($entry[0]) ? $entry[0] : null;
305 } 324 }
306 325
307 public function retrieveOneByURL($url, $user_id) { 326 public function retrieveOneByURL($url, $user_id)
327 {
308 $entry = NULL; 328 $entry = NULL;
309 $sql = "SELECT * FROM entries WHERE url=? AND user_id=?"; 329 $sql = "SELECT * FROM entries WHERE url=? AND user_id=?";
310 $params = array($url, $user_id); 330 $params = array($url, $user_id);
@@ -314,13 +334,15 @@ class Database {
314 return isset($entry[0]) ? $entry[0] : null; 334 return isset($entry[0]) ? $entry[0] : null;
315 } 335 }
316 336
317 public function reassignTags($old_entry_id, $new_entry_id) { 337 public function reassignTags($old_entry_id, $new_entry_id)
338 {
318 $sql = "UPDATE tags_entries SET entry_id=? WHERE entry_id=?"; 339 $sql = "UPDATE tags_entries SET entry_id=? WHERE entry_id=?";
319 $params = array($new_entry_id, $old_entry_id); 340 $params = array($new_entry_id, $old_entry_id);
320 $query = $this->executeQuery($sql, $params); 341 $query = $this->executeQuery($sql, $params);
321 } 342 }
322 343
323 public function getEntriesByView($view, $user_id, $limit = '', $tag_id = 0) { 344 public function getEntriesByView($view, $user_id, $limit = '', $tag_id = 0)
345 {
324 switch ($view) { 346 switch ($view) {
325 case 'archive': 347 case 'archive':
326 $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? "; 348 $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? ";
@@ -348,9 +370,10 @@ class Database {
348 $entries = $query->fetchAll(); 370 $entries = $query->fetchAll();
349 371
350 return $entries; 372 return $entries;
351 } 373 }
352 374
353 public function getEntriesByViewCount($view, $user_id, $tag_id = 0) { 375 public function getEntriesByViewCount($view, $user_id, $tag_id = 0)
376 {
354 switch ($view) { 377 switch ($view) {
355 case 'archive': 378 case 'archive':
356 $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? "; 379 $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? ";
@@ -378,7 +401,8 @@ class Database {
378 return $count; 401 return $count;
379 } 402 }
380 403
381 public function updateContent($id, $content, $user_id) { 404 public function updateContent($id, $content, $user_id)
405 {
382 $sql_action = 'UPDATE entries SET content = ? WHERE id=? AND user_id=?'; 406 $sql_action = 'UPDATE entries SET content = ? WHERE id=? AND user_id=?';
383 $params_action = array($content, $id, $user_id); 407 $params_action = array($content, $id, $user_id);
384 $query = $this->executeQuery($sql_action, $params_action); 408 $query = $this->executeQuery($sql_action, $params_action);
@@ -393,7 +417,8 @@ class Database {
393 * @param integer $user_id 417 * @param integer $user_id
394 * @return integer $id of inserted record 418 * @return integer $id of inserted record
395 */ 419 */
396 public function add($url, $title, $content, $user_id, $isFavorite=0, $isRead=0) { 420 public function add($url, $title, $content, $user_id, $isFavorite=0, $isRead=0)
421 {
397 $sql_action = 'INSERT INTO entries ( url, title, content, user_id, is_fav, is_read ) VALUES (?, ?, ?, ?, ?, ?)'; 422 $sql_action = 'INSERT INTO entries ( url, title, content, user_id, is_fav, is_read ) VALUES (?, ?, ?, ?, ?, ?)';
398 $params_action = array($url, $title, $content, $user_id, $isFavorite, $isRead); 423 $params_action = array($url, $title, $content, $user_id, $isFavorite, $isRead);
399 424
@@ -406,36 +431,42 @@ class Database {
406 return $id; 431 return $id;
407 } 432 }
408 433
409 public function deleteById($id, $user_id) { 434 public function deleteById($id, $user_id)
435 {
410 $sql_action = "DELETE FROM entries WHERE id=? AND user_id=?"; 436 $sql_action = "DELETE FROM entries WHERE id=? AND user_id=?";
411 $params_action = array($id, $user_id); 437 $params_action = array($id, $user_id);
412 $query = $this->executeQuery($sql_action, $params_action); 438 $query = $this->executeQuery($sql_action, $params_action);
413 return $query; 439 return $query;
414 } 440 }
415 441
416 public function favoriteById($id, $user_id) { 442 public function favoriteById($id, $user_id)
443 {
417 $sql_action = "UPDATE entries SET is_fav=NOT is_fav WHERE id=? AND user_id=?"; 444 $sql_action = "UPDATE entries SET is_fav=NOT is_fav WHERE id=? AND user_id=?";
418 $params_action = array($id, $user_id); 445 $params_action = array($id, $user_id);
419 $query = $this->executeQuery($sql_action, $params_action); 446 $query = $this->executeQuery($sql_action, $params_action);
420 } 447 }
421 448
422 public function archiveById($id, $user_id) { 449 public function archiveById($id, $user_id)
450 {
423 $sql_action = "UPDATE entries SET is_read=NOT is_read WHERE id=? AND user_id=?"; 451 $sql_action = "UPDATE entries SET is_read=NOT is_read WHERE id=? AND user_id=?";
424 $params_action = array($id, $user_id); 452 $params_action = array($id, $user_id);
425 $query = $this->executeQuery($sql_action, $params_action); 453 $query = $this->executeQuery($sql_action, $params_action);
426 } 454 }
427 455
428 public function archiveAll($user_id) { 456 public function archiveAll($user_id)
457 {
429 $sql_action = "UPDATE entries SET is_read=? WHERE user_id=? AND is_read=?"; 458 $sql_action = "UPDATE entries SET is_read=? WHERE user_id=? AND is_read=?";
430 $params_action = array($user_id, 1, 0); 459 $params_action = array($user_id, 1, 0);
431 $query = $this->executeQuery($sql_action, $params_action); 460 $query = $this->executeQuery($sql_action, $params_action);
432 } 461 }
433 462
434 public function getLastId($column = '') { 463 public function getLastId($column = '')
464 {
435 return $this->getHandle()->lastInsertId($column); 465 return $this->getHandle()->lastInsertId($column);
436 } 466 }
437 467
438 public function search($term, $user_id, $limit = '') { 468 public function search($term, $user_id, $limit = '')
469 {
439 $search = '%'.$term.'%'; 470 $search = '%'.$term.'%';
440 $sql_action = "SELECT * FROM entries WHERE user_id=? AND (content LIKE ? OR title LIKE ? OR url LIKE ?) "; //searches in content, title and URL 471 $sql_action = "SELECT * FROM entries WHERE user_id=? AND (content LIKE ? OR title LIKE ? OR url LIKE ?) "; //searches in content, title and URL
441 $sql_action .= $this->getEntriesOrder().' ' . $limit; 472 $sql_action .= $this->getEntriesOrder().' ' . $limit;
@@ -444,7 +475,8 @@ class Database {
444 return $query->fetchAll(); 475 return $query->fetchAll();
445 } 476 }
446 477
447 public function retrieveAllTags($user_id, $term = null) { 478 public function retrieveAllTags($user_id, $term = NULL)
479 {
448 $sql = "SELECT DISTINCT tags.*, count(entries.id) AS entriescount FROM tags 480 $sql = "SELECT DISTINCT tags.*, count(entries.id) AS entriescount FROM tags
449 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id 481 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
450 LEFT JOIN entries ON tags_entries.entry_id=entries.id 482 LEFT JOIN entries ON tags_entries.entry_id=entries.id
@@ -458,7 +490,8 @@ class Database {
458 return $tags; 490 return $tags;
459 } 491 }
460 492
461 public function retrieveTag($id, $user_id) { 493 public function retrieveTag($id, $user_id)
494 {
462 $tag = NULL; 495 $tag = NULL;
463 $sql = "SELECT DISTINCT tags.* FROM tags 496 $sql = "SELECT DISTINCT tags.* FROM tags
464 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id 497 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
@@ -468,10 +501,11 @@ class Database {
468 $query = $this->executeQuery($sql, $params); 501 $query = $this->executeQuery($sql, $params);
469 $tag = $query->fetchAll(); 502 $tag = $query->fetchAll();
470 503
471 return isset($tag[0]) ? $tag[0] : null; 504 return isset($tag[0]) ? $tag[0] : NULL;
472 } 505 }
473 506
474 public function retrieveEntriesByTag($tag_id, $user_id) { 507 public function retrieveEntriesByTag($tag_id, $user_id)
508 {
475 $sql = 509 $sql =
476 "SELECT entries.* FROM entries 510 "SELECT entries.* FROM entries
477 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id 511 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
@@ -482,7 +516,8 @@ class Database {
482 return $entries; 516 return $entries;
483 } 517 }
484 518
485 public function retrieveTagsByEntry($entry_id) { 519 public function retrieveTagsByEntry($entry_id)
520 {
486 $sql = 521 $sql =
487 "SELECT tags.* FROM tags 522 "SELECT tags.* FROM tags
488 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id 523 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
@@ -493,14 +528,16 @@ class Database {
493 return $tags; 528 return $tags;
494 } 529 }
495 530
496 public function removeTagForEntry($entry_id, $tag_id) { 531 public function removeTagForEntry($entry_id, $tag_id)
532 {
497 $sql_action = "DELETE FROM tags_entries WHERE tag_id=? AND entry_id=?"; 533 $sql_action = "DELETE FROM tags_entries WHERE tag_id=? AND entry_id=?";
498 $params_action = array($tag_id, $entry_id); 534 $params_action = array($tag_id, $entry_id);
499 $query = $this->executeQuery($sql_action, $params_action); 535 $query = $this->executeQuery($sql_action, $params_action);
500 return $query; 536 return $query;
501 } 537 }
502 538
503 public function cleanUnusedTag($tag_id) { 539 public function cleanUnusedTag($tag_id)
540 {
504 $sql_action = "SELECT tags.* FROM tags JOIN tags_entries ON tags_entries.tag_id=tags.id WHERE tags.id=?"; 541 $sql_action = "SELECT tags.* FROM tags JOIN tags_entries ON tags_entries.tag_id=tags.id WHERE tags.id=?";
505 $query = $this->executeQuery($sql_action,array($tag_id)); 542 $query = $this->executeQuery($sql_action,array($tag_id));
506 $tagstokeep = $query->fetchAll(); 543 $tagstokeep = $query->fetchAll();
@@ -519,7 +556,8 @@ class Database {
519 556
520 } 557 }
521 558
522 public function retrieveTagByValue($value) { 559 public function retrieveTagByValue($value)
560 {
523 $tag = NULL; 561 $tag = NULL;
524 $sql = "SELECT * FROM tags WHERE value=?"; 562 $sql = "SELECT * FROM tags WHERE value=?";
525 $params = array($value); 563 $params = array($value);
@@ -529,27 +567,29 @@ class Database {
529 return isset($tag[0]) ? $tag[0] : null; 567 return isset($tag[0]) ? $tag[0] : null;
530 } 568 }
531 569
532 public function createTag($value) { 570 public function createTag($value)
571 {
533 $sql_action = 'INSERT INTO tags ( value ) VALUES (?)'; 572 $sql_action = 'INSERT INTO tags ( value ) VALUES (?)';
534 $params_action = array($value); 573 $params_action = array($value);
535 $query = $this->executeQuery($sql_action, $params_action); 574 $query = $this->executeQuery($sql_action, $params_action);
536 return $query; 575 return $query;
537 } 576 }
538 577
539 public function setTagToEntry($tag_id, $entry_id) { 578 public function setTagToEntry($tag_id, $entry_id)
579 {
540 $sql_action = 'INSERT INTO tags_entries ( tag_id, entry_id ) VALUES (?, ?)'; 580 $sql_action = 'INSERT INTO tags_entries ( tag_id, entry_id ) VALUES (?, ?)';
541 $params_action = array($tag_id, $entry_id); 581 $params_action = array($tag_id, $entry_id);
542 $query = $this->executeQuery($sql_action, $params_action); 582 $query = $this->executeQuery($sql_action, $params_action);
543 return $query; 583 return $query;
544 } 584 }
545 585
546 private function getEntriesOrder() { 586 private function getEntriesOrder()
547 if (isset($_SESSION['sort']) and array_key_exists($_SESSION['sort'], $this->order)) { 587 {
548 return $this->order[$_SESSION['sort']]; 588 if (isset($_SESSION['sort']) and array_key_exists($_SESSION['sort'], $this->order)) {
549 } 589 return $this->order[$_SESSION['sort']];
550 else {
551 return $this->order['default'];
552 }
553 } 590 }
554 591 else {
592 return $this->order['default'];
593 }
594 }
555} 595}