X-Git-Url: https://git.immae.eu/?a=blobdiff_plain;f=inc%2Fpoche%2FDatabase.class.php;h=c998fe14db5fcd40dd79a9ab9c22501633c39139;hb=6065553c137ec63ec3ffddda0350f1d3d805b852;hp=48ec5abf88055fe447655670bd7820906925ca3f;hpb=3ae345b3d7136057bcb88783a76ffbdc740b4416;p=github%2Fwallabag%2Fwallabag.git diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php old mode 100644 new mode 100755 index 48ec5abf..c998fe14 --- a/inc/poche/Database.class.php +++ b/inc/poche/Database.class.php @@ -1,17 +1,24 @@ + * @category wallabag + * @author Nicolas Lœuillet * @copyright 2013 * @license http://www.wtfpl.net/ see COPYING file */ class Database { var $handle; - - function __construct() + private $order = array( + 'ia' => 'ORDER BY entries.id', + 'id' => 'ORDER BY entries.id DESC', + 'ta' => 'ORDER BY lower(entries.title)', + 'td' => 'ORDER BY lower(entries.title) DESC', + 'default' => 'ORDER BY entries.id' + ); + + function __construct() { switch (STORAGE) { case 'sqlite': @@ -165,9 +172,14 @@ class Database { } } - public function login($username, $password) { - $sql = "SELECT * FROM users WHERE username=? AND password=?"; - $query = $this->executeQuery($sql, array($username, $password)); + public function login($username, $password, $isauthenticated=false) { + if ($isauthenticated) { + $sql = "SELECT * FROM users WHERE username=?"; + $query = $this->executeQuery($sql, array($username)); + } else { + $sql = "SELECT * FROM users WHERE username=? AND password=?"; + $query = $this->executeQuery($sql, array($username, $password)); + } $login = $query->fetchAll(); $user = array(); @@ -236,48 +248,78 @@ class Database { return isset($entry[0]) ? $entry[0] : null; } - public function getEntriesByView($view, $user_id, $limit = '') { - switch ($_SESSION['sort']) - { - case 'ia': - $order = 'ORDER BY id'; - break; - case 'id': - $order = 'ORDER BY id DESC'; + public function retrieveOneByURL($url, $user_id) { + $entry = NULL; + $sql = "SELECT * FROM entries WHERE url=? AND user_id=?"; + $params = array($url, $user_id); + $query = $this->executeQuery($sql, $params); + $entry = $query->fetchAll(); + + return isset($entry[0]) ? $entry[0] : null; + } + + public function reassignTags($old_entry_id, $new_entry_id) { + $sql = "UPDATE tags_entries SET entry_id=? WHERE entry_id=?"; + $params = array($new_entry_id, $old_entry_id); + $query = $this->executeQuery($sql, $params); + } + + public function getEntriesByView($view, $user_id, $limit = '', $tag_id = 0) { + switch ($view) { + case 'archive': + $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? "; + $params = array($user_id, 1); break; - case 'ta': - $order = 'ORDER BY lower(title)'; + case 'fav' : + $sql = "SELECT * FROM entries WHERE user_id=? AND is_fav=? "; + $params = array($user_id, 1); break; - case 'td': - $order = 'ORDER BY lower(title) DESC'; + case 'tag' : + $sql = "SELECT entries.* FROM entries + LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id + WHERE entries.user_id=? AND tags_entries.tag_id = ? "; + $params = array($user_id, $tag_id); break; default: - $order = 'ORDER BY id'; + $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? "; + $params = array($user_id, 0); break; } - switch ($view) - { + $sql .= $this->getEntriesOrder().' ' . $limit; + + $query = $this->executeQuery($sql, $params); + $entries = $query->fetchAll(); + + return $entries; + } + + public function getEntriesByViewCount($view, $user_id, $tag_id = 0) { + switch ($view) { case 'archive': - $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? " . $order; + $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? "; $params = array($user_id, 1); break; case 'fav' : - $sql = "SELECT * FROM entries WHERE user_id=? AND is_fav=? " . $order; + $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_fav=? "; $params = array($user_id, 1); break; + case 'tag' : + $sql = "SELECT count(*) FROM entries + LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id + WHERE entries.user_id=? AND tags_entries.tag_id = ? "; + $params = array($user_id, $tag_id); + break; default: - $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? " . $order; + $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? "; $params = array($user_id, 0); break; } - $sql .= ' ' . $limit; - $query = $this->executeQuery($sql, $params); - $entries = $query->fetchAll(); + list($count) = $query->fetch(); - return $entries; + return $count; } public function updateContent($id, $content, $user_id) { @@ -313,34 +355,46 @@ class Database { $query = $this->executeQuery($sql_action, $params_action); } + public function archiveAll($user_id) { + $sql_action = "UPDATE entries SET is_read=? WHERE user_id=? AND is_read=?"; + $params_action = array($user_id, 1, 0); + $query = $this->executeQuery($sql_action, $params_action); + } + public function getLastId($column = '') { return $this->getHandle()->lastInsertId($column); } - public function retrieveAllTags() { - $sql = "SELECT * FROM tags"; - $query = $this->executeQuery($sql, array()); + public function retrieveAllTags($user_id) { + $sql = "SELECT DISTINCT tags.* FROM tags + LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id + LEFT JOIN entries ON tags_entries.entry_id=entries.id + WHERE entries.user_id=?"; + $query = $this->executeQuery($sql, array($user_id)); $tags = $query->fetchAll(); return $tags; } - public function retrieveTag($id) { + public function retrieveTag($id, $user_id) { $tag = NULL; - $sql = "SELECT * FROM tags WHERE id=?"; - $params = array(intval($id)); + $sql = "SELECT DISTINCT tags.* FROM tags + LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id + LEFT JOIN entries ON tags_entries.entry_id=entries.id + WHERE tags.id=? AND entries.user_id=?"; + $params = array(intval($id), $user_id); $query = $this->executeQuery($sql, $params); $tag = $query->fetchAll(); return isset($tag[0]) ? $tag[0] : null; } - public function retrieveEntriesByTag($tag_id) { + public function retrieveEntriesByTag($tag_id, $user_id) { $sql = "SELECT entries.* FROM entries LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id - WHERE tags_entries.tag_id = ?"; - $query = $this->executeQuery($sql, array($tag_id)); + WHERE tags_entries.tag_id = ? AND entries.user_id=?"; + $query = $this->executeQuery($sql, array($tag_id, $user_id)); $entries = $query->fetchAll(); return $entries; @@ -387,4 +441,14 @@ class Database { $query = $this->executeQuery($sql_action, $params_action); return $query; } + + private function getEntriesOrder() { + if (isset($_SESSION['sort']) and array_key_exists($_SESSION['sort'], $this->order)) { + return $this->order[$_SESSION['sort']]; + } + else { + return $this->order['default']; + } + } + }