X-Git-Url: https://git.immae.eu/?a=blobdiff_plain;f=inc%2Fpoche%2FDatabase.class.php;h=036c9d1b264d23f8ed82bb219bbe519ab714e19a;hb=d70dd7ac698a1f9e90c97fa7eb7b113bf84304fc;hp=c998fe14db5fcd40dd79a9ab9c22501633c39139;hpb=e7345a2c4feaa64c8582844425f388e57f1f933d;p=github%2Fwallabag%2Fwallabag.git diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php index c998fe14..036c9d1b 100755 --- a/inc/poche/Database.class.php +++ b/inc/poche/Database.class.php @@ -18,7 +18,7 @@ class Database { 'default' => 'ORDER BY entries.id' ); - function __construct() + function __construct() { switch (STORAGE) { case 'sqlite': @@ -27,11 +27,11 @@ class Database { break; case 'mysql': $db_path = 'mysql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB; - $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD); + $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD); break; case 'postgres': $db_path = 'pgsql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB; - $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD); + $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD); break; } @@ -51,7 +51,7 @@ class Database { } $hasAdmin = count($query->fetchAll()); - if ($hasAdmin == 0) + if ($hasAdmin == 0) return false; return true; @@ -77,7 +77,7 @@ class Database { } else { $sql = ' - CREATE TABLE tags ( + CREATE TABLE IF NOT EXISTS tags ( id bigserial primary key, value varchar(255) NOT NULL ); @@ -110,7 +110,7 @@ class Database { } else { $sql = ' - CREATE TABLE tags_entries ( + CREATE TABLE IF NOT EXISTS tags_entries ( id bigserial primary key, entry_id integer NOT NULL, tag_id integer NOT NULL @@ -140,7 +140,7 @@ class Database { $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; $params = array($id_user, 'language', LANG); $query = $this->executeQuery($sql, $params); - + $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; $params = array($id_user, 'theme', DEFAULT_THEME); $query = $this->executeQuery($sql, $params); @@ -153,7 +153,7 @@ class Database { $query = $this->executeQuery($sql, array($id)); $result = $query->fetchAll(); $user_config = array(); - + foreach ($result as $key => $value) { $user_config[$value['name']] = $value['value']; } @@ -201,10 +201,10 @@ class Database { $params_update = array($password, $userId); $query = $this->executeQuery($sql_update, $params_update); } - + public function updateUserConfig($userId, $key, $value) { $config = $this->getConfigUser($userId); - + if (! isset($config[$key])) { $sql = "INSERT INTO users_config (value, user_id, name) VALUES (?, ?, ?)"; } @@ -230,6 +230,36 @@ class Database { } } + public function updateContentAndTitle($id, $title, $body, $user_id) { + $sql_action = 'UPDATE entries SET content = ?, title = ? WHERE id=? AND user_id=?'; + $params_action = array($body, $title, $id, $user_id); + $query = $this->executeQuery($sql_action, $params_action); + + return $query; + } + + public function retrieveUnfetchedEntries($user_id, $limit) { + + $sql_limit = "LIMIT 0,".$limit; + if (STORAGE == 'postgres') { + $sql_limit = "LIMIT ".$limit." OFFSET 0"; + } + + $sql = "SELECT * FROM entries WHERE (content = '' OR content IS NULL) AND title LIKE 'Untitled - Import%' AND user_id=? ORDER BY id " . $sql_limit; + $query = $this->executeQuery($sql, array($user_id)); + $entries = $query->fetchAll(); + + return $entries; + } + + public function retrieveUnfetchedEntriesCount($user_id) { + $sql = "SELECT count(*) FROM entries WHERE (content = '' OR content IS NULL) AND title LIKE 'Untitled - Import%' AND user_id=?"; + $query = $this->executeQuery($sql, array($user_id)); + list($count) = $query->fetch(); + + return $count; + } + public function retrieveAll($user_id) { $sql = "SELECT * FROM entries WHERE user_id=? ORDER BY id"; $query = $this->executeQuery($sql, array($user_id)); @@ -294,24 +324,24 @@ class Database { return $entries; } - public function getEntriesByViewCount($view, $user_id, $tag_id = 0) { - switch ($view) { + public function getEntriesByViewCount($view, $user_id, $tag_id = 0) { + switch ($view) { case 'archive': - $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? "; + $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? "; $params = array($user_id, 1); break; case 'fav' : - $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_fav=? "; + $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; + 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 count(*) FROM entries WHERE user_id=? AND is_read=? "; + $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? "; $params = array($user_id, 0); break; } @@ -319,7 +349,7 @@ class Database { $query = $this->executeQuery($sql, $params); list($count) = $query->fetch(); - return $count; + return $count; } public function updateContent($id, $content, $user_id) { @@ -329,11 +359,24 @@ class Database { return $query; } - public function add($url, $title, $content, $user_id) { - $sql_action = 'INSERT INTO entries ( url, title, content, user_id ) VALUES (?, ?, ?, ?)'; - $params_action = array($url, $title, $content, $user_id); - $query = $this->executeQuery($sql_action, $params_action); - return $query; + /** + * + * @param string $url + * @param string $title + * @param string $content + * @param integer $user_id + * @return integer $id of inserted record + */ + public function add($url, $title, $content, $user_id, $isFavorite=0, $isRead=0) { + $sql_action = 'INSERT INTO entries ( url, title, content, user_id, is_fav, is_read ) VALUES (?, ?, ?, ?, ?, ?)'; + $params_action = array($url, $title, $content, $user_id, $isFavorite, $isRead); + if ( !$this->executeQuery($sql_action, $params_action) ) { + $id = null; + } + else { + $id = intval($this->getLastId( (STORAGE == 'postgres') ? 'entries_id_seq' : '') ); + } + return $id; } public function deleteById($id, $user_id) { @@ -365,12 +408,24 @@ class Database { return $this->getHandle()->lastInsertId($column); } - public function retrieveAllTags($user_id) { - $sql = "SELECT DISTINCT tags.* FROM tags + public function search($term, $user_id, $limit = '') { + $search = '%'.$term.'%'; + $sql_action = "SELECT * FROM entries WHERE user_id=? AND (content LIKE ? OR title LIKE ? OR url LIKE ?) "; //searches in content, title and URL + $sql_action .= $this->getEntriesOrder().' ' . $limit; + $params_action = array($user_id, $search, $search, $search); + $query = $this->executeQuery($sql_action, $params_action); + return $query->fetchAll(); + } + + public function retrieveAllTags($user_id, $term = null) { + $sql = "SELECT DISTINCT tags.*, count(entries.id) AS entriescount 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)); + WHERE entries.user_id=? + ". (($term) ? "AND lower(tags.value) LIKE ?" : '') ." + GROUP BY tags.id, tags.value + ORDER BY tags.value"; + $query = $this->executeQuery($sql, (($term)? array($user_id, strtolower('%'.$term.'%')) : array($user_id) )); $tags = $query->fetchAll(); return $tags; @@ -390,7 +445,7 @@ class Database { } public function retrieveEntriesByTag($tag_id, $user_id) { - $sql = + $sql = "SELECT entries.* FROM entries LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id WHERE tags_entries.tag_id = ? AND entries.user_id=?"; @@ -401,7 +456,7 @@ class Database { } public function retrieveTagsByEntry($entry_id) { - $sql = + $sql = "SELECT tags.* FROM tags LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id WHERE tags_entries.entry_id = ?";