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