]>
git.immae.eu Git - github/wallabag/wallabag.git/blob - inc/poche/Database.class.php
3 * wallabag, self hostable application allowing you to not miss any content anymore
6 * @author Nicolas LÅ“uillet <nicolas@loeuillet.org>
8 * @license http://opensource.org/licenses/MIT see COPYING file
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'
22 function __construct()
26 $db_path = 'sqlite:' . STORAGE_SQLITE
;
27 $this->handle
= new PDO($db_path);
30 $db_path = 'mysql:host=' . STORAGE_SERVER
. ';dbname=' . STORAGE_DB
;
31 $this->handle
= new PDO($db_path, STORAGE_USER
, STORAGE_PASSWORD
);
34 $db_path = 'pgsql:host=' . STORAGE_SERVER
. ';dbname=' . STORAGE_DB
;
35 $this->handle
= new PDO($db_path, STORAGE_USER
, STORAGE_PASSWORD
);
38 die(STORAGE
. ' is not a recognised database system !');
41 $this->handle
->setAttribute(PDO
::ATTR_ERRMODE
, PDO
::ERRMODE_EXCEPTION
);
43 Tools
::logm('storage type ' . STORAGE
);
46 private function getHandle()
51 private function _checkTags()
54 if (STORAGE
== 'sqlite') {
56 CREATE TABLE IF NOT EXISTS tags (
57 id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
61 elseif(STORAGE
== 'mysql') {
63 CREATE TABLE IF NOT EXISTS `tags` (
64 `id` int(11) NOT NULL AUTO_INCREMENT,
65 `value` varchar(255) NOT NULL,
67 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
72 CREATE TABLE IF NOT EXISTS tags (
73 id bigserial primary key,
74 value varchar(255) NOT NULL
79 $query = $this->executeQuery($sql, array());
81 if (STORAGE
== 'sqlite') {
83 CREATE TABLE IF NOT EXISTS tags_entries (
84 id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
87 FOREIGN KEY(entry_id) REFERENCES entries(id) ON DELETE CASCADE,
88 FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE
91 elseif(STORAGE
== 'mysql') {
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,
100 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
105 CREATE TABLE IF NOT EXISTS tags_entries (
106 id bigserial primary key,
107 entry_id integer NOT NULL,
108 tag_id integer NOT NULL
113 $query = $this->executeQuery($sql, array());
116 public function install($login, $password)
118 $sql = 'INSERT INTO users ( username, password, name, email) VALUES (?, ?, ?, ?)';
119 $params = array($login, $password, $login, ' ');
120 $query = $this->executeQuery($sql, $params);
123 if (STORAGE
== 'postgres') {
124 $sequence = 'users_id_seq';
127 $id_user = intval($this->getLastId($sequence));
129 $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)';
130 $params = array($id_user, 'pager', PAGINATION
);
131 $query = $this->executeQuery($sql, $params);
133 $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)';
134 $params = array($id_user, 'language', LANG
);
135 $query = $this->executeQuery($sql, $params);
137 $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)';
138 $params = array($id_user, 'theme', DEFAULT_THEME
);
139 $query = $this->executeQuery($sql, $params);
144 public function getConfigUser($id)
146 $sql = "SELECT * FROM users_config WHERE user_id = ?";
147 $query = $this->executeQuery($sql, array($id));
148 $result = $query->fetchAll();
149 $user_config = array();
151 foreach ($result as $key => $value) {
152 $user_config[$value['name']] = $value['value'];
158 public function userExists($username)
160 $sql = "SELECT * FROM users WHERE username=?";
161 $query = $this->executeQuery($sql, array($username));
162 $login = $query->fetchAll();
163 if (isset($login[0])) {
170 public function login($username, $password, $isauthenticated = FALSE)
172 if ($isauthenticated) {
173 $sql = "SELECT * FROM users WHERE username=?";
174 $query = $this->executeQuery($sql, array($username));
176 $sql = "SELECT * FROM users WHERE username=? AND password=?";
177 $query = $this->executeQuery($sql, array($username, $password));
179 $login = $query->fetchAll();
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']);
194 public function updatePassword($userId, $password)
196 $sql_update = "UPDATE users SET password=? WHERE id=?";
197 $params_update = array($password, $userId);
198 $query = $this->executeQuery($sql_update, $params_update);
201 public function updateUserConfig($userId, $key, $value)
203 $config = $this->getConfigUser($userId);
205 if (! isset($config[$key])) {
206 $sql = "INSERT INTO users_config (value, user_id, name) VALUES (?, ?, ?)";
209 $sql = "UPDATE users_config SET value=? WHERE user_id=? AND name=?";
212 $params = array($value, $userId, $key);
213 $query = $this->executeQuery($sql, $params);
216 private function executeQuery($sql, $params)
220 $query = $this->getHandle()->prepare($sql);
221 $query->execute($params);
226 Tools
::logm('execute query error : '.$e->getMessage());
231 public function listUsers($username = NULL)
233 $sql = 'SELECT count(*) FROM users'.( $username ? ' WHERE username=?' : '');
234 $query = $this->executeQuery($sql, ( $username ? array($username) : array()));
235 list($count) = $query->fetch();
239 public function getUserPassword($userID)
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;
247 public function deleteUserConfig($userID)
249 $sql_action = 'DELETE from users_config WHERE user_id=?';
250 $params_action = array($userID);
251 $query = $this->executeQuery($sql_action, $params_action);
255 public function deleteTagsEntriesAndEntries($userID)
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);
263 $this->deleteById($entryid,$userID);
267 public function deleteUser($userID)
269 $sql_action = 'DELETE from users WHERE id=?';
270 $params_action = array($userID);
271 $query = $this->executeQuery($sql_action, $params_action);
274 public function updateContentAndTitle($id, $title, $body, $user_id)
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);
282 public function retrieveUnfetchedEntries($user_id, $limit)
285 $sql_limit = "LIMIT 0,".$limit;
286 if (STORAGE
== 'postgres') {
287 $sql_limit = "LIMIT ".$limit." OFFSET 0";
290 $sql = "SELECT * FROM entries WHERE (content = '' OR content IS NULL) AND title LIKE 'Untitled - Import%' AND user_id=? ORDER BY id " . $sql_limit;
291 $query = $this->executeQuery($sql, array($user_id));
292 $entries = $query->fetchAll();
297 public function retrieveUnfetchedEntriesCount($user_id)
299 $sql = "SELECT count(*) FROM entries WHERE (content = '' OR content IS NULL) AND title LIKE 'Untitled - Import%' AND user_id=?";
300 $query = $this->executeQuery($sql, array($user_id));
301 list($count) = $query->fetch();
306 public function retrieveAll($user_id)
308 $sql = "SELECT * FROM entries WHERE user_id=? ORDER BY id";
309 $query = $this->executeQuery($sql, array($user_id));
310 $entries = $query->fetchAll();
315 public function retrieveOneById($id, $user_id)
318 $sql = "SELECT * FROM entries WHERE id=? AND user_id=?";
319 $params = array(intval($id), $user_id);
320 $query = $this->executeQuery($sql, $params);
321 $entry = $query->fetchAll();
323 return isset($entry[0]) ? $entry[0] : null;
326 public function retrieveOneByURL($url, $user_id)
329 $sql = "SELECT * FROM entries WHERE url=? AND user_id=?";
330 $params = array($url, $user_id);
331 $query = $this->executeQuery($sql, $params);
332 $entry = $query->fetchAll();
334 return isset($entry[0]) ? $entry[0] : null;
337 public function reassignTags($old_entry_id, $new_entry_id)
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);
344 public function getEntriesByView($view, $user_id, $limit = '', $tag_id = 0)
348 $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? ";
349 $params = array($user_id, 1);
352 $sql = "SELECT * FROM entries WHERE user_id=? AND is_fav=? ";
353 $params = array($user_id, 1);
356 $sql = "SELECT entries.* FROM entries
357 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
358 WHERE entries.user_id=? AND tags_entries.tag_id = ? ";
359 $params = array($user_id, $tag_id);
362 $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? ";
363 $params = array($user_id, 0);
367 $sql .= $this->getEntriesOrder().' ' . $limit;
369 $query = $this->executeQuery($sql, $params);
370 $entries = $query->fetchAll();
375 public function getEntriesByViewCount($view, $user_id, $tag_id = 0)
379 $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? ";
380 $params = array($user_id, 1);
383 $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_fav=? ";
384 $params = array($user_id, 1);
387 $sql = "SELECT count(*) FROM entries
388 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
389 WHERE entries.user_id=? AND tags_entries.tag_id = ? ";
390 $params = array($user_id, $tag_id);
393 $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? ";
394 $params = array($user_id, 0);
398 $query = $this->executeQuery($sql, $params);
399 list($count) = $query->fetch();
404 public function updateContent($id, $content, $user_id)
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);
415 * @param string $title
416 * @param string $content
417 * @param integer $user_id
418 * @return integer $id of inserted record
420 public function add($url, $title, $content, $user_id, $isFavorite=0, $isRead=0)
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);
425 if ( !$this->executeQuery($sql_action, $params_action) ) {
429 $id = intval($this->getLastId( (STORAGE
== 'postgres') ? 'entries_id_seq' : '') );
434 public function deleteById($id, $user_id)
436 $sql_action = "DELETE FROM entries WHERE id=? AND user_id=?";
437 $params_action = array($id, $user_id);
438 $query = $this->executeQuery($sql_action, $params_action);
442 public function favoriteById($id, $user_id)
444 $sql_action = "UPDATE entries SET is_fav=NOT is_fav WHERE id=? AND user_id=?";
445 $params_action = array($id, $user_id);
446 $query = $this->executeQuery($sql_action, $params_action);
449 public function archiveById($id, $user_id)
451 $sql_action = "UPDATE entries SET is_read=NOT is_read WHERE id=? AND user_id=?";
452 $params_action = array($id, $user_id);
453 $query = $this->executeQuery($sql_action, $params_action);
456 public function archiveAll($user_id)
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);
463 public function getLastId($column = '')
465 return $this->getHandle()->lastInsertId($column);
468 public function search($term, $user_id, $limit = '')
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();
478 public function retrieveAllTags($user_id, $term = NULL)
480 $sql = "SELECT DISTINCT tags.*, count(entries.id) AS entriescount FROM tags
481 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
482 LEFT JOIN entries ON tags_entries.entry_id=entries.id
483 WHERE entries.user_id=?
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) ));
488 $tags = $query->fetchAll();
493 public function retrieveTag($id, $user_id)
496 $sql = "SELECT DISTINCT tags.* FROM tags
497 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
498 LEFT JOIN entries ON tags_entries.entry_id=entries.id
499 WHERE tags.id=? AND entries.user_id=?";
500 $params = array(intval($id), $user_id);
501 $query = $this->executeQuery($sql, $params);
502 $tag = $query->fetchAll();
504 return isset($tag[0]) ? $tag[0] : NULL;
507 public function retrieveEntriesByTag($tag_id, $user_id)
510 "SELECT entries.* FROM entries
511 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
512 WHERE tags_entries.tag_id = ? AND entries.user_id=? ORDER by entries.id DESC";
513 $query = $this->executeQuery($sql, array($tag_id, $user_id));
514 $entries = $query->fetchAll();
519 public function retrieveTagsByEntry($entry_id)
522 "SELECT tags.* FROM tags
523 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
524 WHERE tags_entries.entry_id = ?";
525 $query = $this->executeQuery($sql, array($entry_id));
526 $tags = $query->fetchAll();
531 public function removeTagForEntry($entry_id, $tag_id)
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);
539 public function cleanUnusedTag($tag_id)
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));
543 $tagstokeep = $query->fetchAll();
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));
546 $alltags = $query->fetchAll();
548 foreach ($alltags as $tag) {
549 if ($tag && !in_array($tag,$tagstokeep)) {
550 $sql_action = "DELETE FROM tags WHERE id=?";
551 $params_action = array($tag[0]);
552 $this->executeQuery($sql_action, $params_action);
559 public function retrieveTagByValue($value)
562 $sql = "SELECT * FROM tags WHERE value=?";
563 $params = array($value);
564 $query = $this->executeQuery($sql, $params);
565 $tag = $query->fetchAll();
567 return isset($tag[0]) ? $tag[0] : null;
570 public function createTag($value)
572 $sql_action = 'INSERT INTO tags ( value ) VALUES (?)';
573 $params_action = array($value);
574 $query = $this->executeQuery($sql_action, $params_action);
578 public function setTagToEntry($tag_id, $entry_id)
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);
586 private function getEntriesOrder()
588 if (isset($_SESSION['sort']) and array_key_exists($_SESSION['sort'], $this->order
)) {
589 return $this->order
[$_SESSION['sort']];
592 return $this->order
['default'];