aboutsummaryrefslogtreecommitdiffhomepage
path: root/inc/poche/Database.class.php
diff options
context:
space:
mode:
authorNicolas LÅ“uillet <nicolas@loeuillet.org>2014-04-03 14:42:03 +0200
committerNicolas LÅ“uillet <nicolas@loeuillet.org>2014-04-03 14:42:03 +0200
commit0d67b00d5d3b7ce1b76b639dcc65c415a5f13439 (patch)
tree7c4d113bb1c3d90ef53e08138a2850576395780f /inc/poche/Database.class.php
parent99679d06884120c57f43b44e55e03595f1f87bed (diff)
parent7d2f1aa2793595aa6cdc58a58260184234cfa809 (diff)
downloadwallabag-0d67b00d5d3b7ce1b76b639dcc65c415a5f13439.tar.gz
wallabag-0d67b00d5d3b7ce1b76b639dcc65c415a5f13439.tar.zst
wallabag-0d67b00d5d3b7ce1b76b639dcc65c415a5f13439.zip
Merge pull request #595 from wallabag/dev1.6.11.6.0
wallabag 1.6.0
Diffstat (limited to 'inc/poche/Database.class.php')
-rwxr-xr-xinc/poche/Database.class.php117
1 files changed, 86 insertions, 31 deletions
diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php
index c998fe14..6244df88 100755
--- a/inc/poche/Database.class.php
+++ b/inc/poche/Database.class.php
@@ -18,7 +18,7 @@ class Database {
18 'default' => 'ORDER BY entries.id' 18 'default' => 'ORDER BY entries.id'
19 ); 19 );
20 20
21 function __construct() 21 function __construct()
22 { 22 {
23 switch (STORAGE) { 23 switch (STORAGE) {
24 case 'sqlite': 24 case 'sqlite':
@@ -27,11 +27,11 @@ class Database {
27 break; 27 break;
28 case 'mysql': 28 case 'mysql':
29 $db_path = 'mysql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB; 29 $db_path = 'mysql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB;
30 $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD); 30 $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD);
31 break; 31 break;
32 case 'postgres': 32 case 'postgres':
33 $db_path = 'pgsql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB; 33 $db_path = 'pgsql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB;
34 $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD); 34 $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD);
35 break; 35 break;
36 } 36 }
37 37
@@ -51,7 +51,7 @@ class Database {
51 } 51 }
52 $hasAdmin = count($query->fetchAll()); 52 $hasAdmin = count($query->fetchAll());
53 53
54 if ($hasAdmin == 0) 54 if ($hasAdmin == 0)
55 return false; 55 return false;
56 56
57 return true; 57 return true;
@@ -140,7 +140,7 @@ class Database {
140 $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; 140 $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)';
141 $params = array($id_user, 'language', LANG); 141 $params = array($id_user, 'language', LANG);
142 $query = $this->executeQuery($sql, $params); 142 $query = $this->executeQuery($sql, $params);
143 143
144 $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; 144 $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)';
145 $params = array($id_user, 'theme', DEFAULT_THEME); 145 $params = array($id_user, 'theme', DEFAULT_THEME);
146 $query = $this->executeQuery($sql, $params); 146 $query = $this->executeQuery($sql, $params);
@@ -153,7 +153,7 @@ class Database {
153 $query = $this->executeQuery($sql, array($id)); 153 $query = $this->executeQuery($sql, array($id));
154 $result = $query->fetchAll(); 154 $result = $query->fetchAll();
155 $user_config = array(); 155 $user_config = array();
156 156
157 foreach ($result as $key => $value) { 157 foreach ($result as $key => $value) {
158 $user_config[$value['name']] = $value['value']; 158 $user_config[$value['name']] = $value['value'];
159 } 159 }
@@ -201,10 +201,10 @@ class Database {
201 $params_update = array($password, $userId); 201 $params_update = array($password, $userId);
202 $query = $this->executeQuery($sql_update, $params_update); 202 $query = $this->executeQuery($sql_update, $params_update);
203 } 203 }
204 204
205 public function updateUserConfig($userId, $key, $value) { 205 public function updateUserConfig($userId, $key, $value) {
206 $config = $this->getConfigUser($userId); 206 $config = $this->getConfigUser($userId);
207 207
208 if (! isset($config[$key])) { 208 if (! isset($config[$key])) {
209 $sql = "INSERT INTO users_config (value, user_id, name) VALUES (?, ?, ?)"; 209 $sql = "INSERT INTO users_config (value, user_id, name) VALUES (?, ?, ?)";
210 } 210 }
@@ -230,6 +230,36 @@ class Database {
230 } 230 }
231 } 231 }
232 232
233 public function updateContentAndTitle($id, $title, $body, $user_id) {
234 $sql_action = 'UPDATE entries SET content = ?, title = ? WHERE id=? AND user_id=?';
235 $params_action = array($body, $title, $id, $user_id);
236 $query = $this->executeQuery($sql_action, $params_action);
237
238 return $query;
239 }
240
241 public function retrieveUnfetchedEntries($user_id, $limit) {
242
243 $sql_limit = "LIMIT 0,".$limit;
244 if (STORAGE == 'postgres') {
245 $sql_limit = "LIMIT ".$limit." OFFSET 0";
246 }
247
248 $sql = "SELECT * FROM entries WHERE (content = '' OR content IS NULL) AND user_id=? ORDER BY id " . $sql_limit;
249 $query = $this->executeQuery($sql, array($user_id));
250 $entries = $query->fetchAll();
251
252 return $entries;
253 }
254
255 public function retrieveUnfetchedEntriesCount($user_id) {
256 $sql = "SELECT count(*) FROM entries WHERE (content = '' OR content IS NULL) AND user_id=?";
257 $query = $this->executeQuery($sql, array($user_id));
258 list($count) = $query->fetch();
259
260 return $count;
261 }
262
233 public function retrieveAll($user_id) { 263 public function retrieveAll($user_id) {
234 $sql = "SELECT * FROM entries WHERE user_id=? ORDER BY id"; 264 $sql = "SELECT * FROM entries WHERE user_id=? ORDER BY id";
235 $query = $this->executeQuery($sql, array($user_id)); 265 $query = $this->executeQuery($sql, array($user_id));
@@ -294,24 +324,24 @@ class Database {
294 return $entries; 324 return $entries;
295 } 325 }
296 326
297 public function getEntriesByViewCount($view, $user_id, $tag_id = 0) { 327 public function getEntriesByViewCount($view, $user_id, $tag_id = 0) {
298 switch ($view) { 328 switch ($view) {
299 case 'archive': 329 case 'archive':
300 $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? "; 330 $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? ";
301 $params = array($user_id, 1); 331 $params = array($user_id, 1);
302 break; 332 break;
303 case 'fav' : 333 case 'fav' :
304 $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_fav=? "; 334 $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_fav=? ";
305 $params = array($user_id, 1); 335 $params = array($user_id, 1);
306 break; 336 break;
307 case 'tag' : 337 case 'tag' :
308 $sql = "SELECT count(*) FROM entries 338 $sql = "SELECT count(*) FROM entries
309 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id 339 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
310 WHERE entries.user_id=? AND tags_entries.tag_id = ? "; 340 WHERE entries.user_id=? AND tags_entries.tag_id = ? ";
311 $params = array($user_id, $tag_id); 341 $params = array($user_id, $tag_id);
312 break; 342 break;
313 default: 343 default:
314 $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? "; 344 $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? ";
315 $params = array($user_id, 0); 345 $params = array($user_id, 0);
316 break; 346 break;
317 } 347 }
@@ -319,7 +349,7 @@ class Database {
319 $query = $this->executeQuery($sql, $params); 349 $query = $this->executeQuery($sql, $params);
320 list($count) = $query->fetch(); 350 list($count) = $query->fetch();
321 351
322 return $count; 352 return $count;
323 } 353 }
324 354
325 public function updateContent($id, $content, $user_id) { 355 public function updateContent($id, $content, $user_id) {
@@ -329,11 +359,24 @@ class Database {
329 return $query; 359 return $query;
330 } 360 }
331 361
332 public function add($url, $title, $content, $user_id) { 362 /**
333 $sql_action = 'INSERT INTO entries ( url, title, content, user_id ) VALUES (?, ?, ?, ?)'; 363 *
334 $params_action = array($url, $title, $content, $user_id); 364 * @param string $url
335 $query = $this->executeQuery($sql_action, $params_action); 365 * @param string $title
336 return $query; 366 * @param string $content
367 * @param integer $user_id
368 * @return integer $id of inserted record
369 */
370 public function add($url, $title, $content, $user_id, $isFavorite=0, $isRead=0) {
371 $sql_action = 'INSERT INTO entries ( url, title, content, user_id, is_fav, is_read ) VALUES (?, ?, ?, ?, ?, ?)';
372 $params_action = array($url, $title, $content, $user_id, $isFavorite, $isRead);
373 if ( !$this->executeQuery($sql_action, $params_action) ) {
374 $id = null;
375 }
376 else {
377 $id = intval($this->getLastId( (STORAGE == 'postgres') ? 'users_id_seq' : '' ));
378 }
379 return $id;
337 } 380 }
338 381
339 public function deleteById($id, $user_id) { 382 public function deleteById($id, $user_id) {
@@ -364,13 +407,25 @@ class Database {
364 public function getLastId($column = '') { 407 public function getLastId($column = '') {
365 return $this->getHandle()->lastInsertId($column); 408 return $this->getHandle()->lastInsertId($column);
366 } 409 }
410
411 public function search($term, $user_id, $limit = '') {
412 $search = '%'.$term.'%';
413 $sql_action = "SELECT * FROM entries WHERE user_id=? AND (content LIKE ? OR title LIKE ? OR url LIKE ?) "; //searches in content, title and URL
414 $sql_action .= $this->getEntriesOrder().' ' . $limit;
415 $params_action = array($user_id, $search, $search, $search);
416 $query = $this->executeQuery($sql_action, $params_action);
417 return $query->fetchAll();
418 }
367 419
368 public function retrieveAllTags($user_id) { 420 public function retrieveAllTags($user_id, $term = null) {
369 $sql = "SELECT DISTINCT tags.* FROM tags 421 $sql = "SELECT DISTINCT tags.*, count(entries.id) AS entriescount FROM tags
370 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id 422 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
371 LEFT JOIN entries ON tags_entries.entry_id=entries.id 423 LEFT JOIN entries ON tags_entries.entry_id=entries.id
372 WHERE entries.user_id=?"; 424 WHERE entries.user_id=?
373 $query = $this->executeQuery($sql, array($user_id)); 425 ". (($term) ? "AND lower(tags.value) LIKE ?" : '') ."
426 GROUP BY tags.id, tags.value
427 ORDER BY tags.value";
428 $query = $this->executeQuery($sql, (($term)? array($user_id, strtolower('%'.$term.'%')) : array($user_id) ));
374 $tags = $query->fetchAll(); 429 $tags = $query->fetchAll();
375 430
376 return $tags; 431 return $tags;
@@ -390,7 +445,7 @@ class Database {
390 } 445 }
391 446
392 public function retrieveEntriesByTag($tag_id, $user_id) { 447 public function retrieveEntriesByTag($tag_id, $user_id) {
393 $sql = 448 $sql =
394 "SELECT entries.* FROM entries 449 "SELECT entries.* FROM entries
395 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id 450 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
396 WHERE tags_entries.tag_id = ? AND entries.user_id=?"; 451 WHERE tags_entries.tag_id = ? AND entries.user_id=?";
@@ -401,7 +456,7 @@ class Database {
401 } 456 }
402 457
403 public function retrieveTagsByEntry($entry_id) { 458 public function retrieveTagsByEntry($entry_id) {
404 $sql = 459 $sql =
405 "SELECT tags.* FROM tags 460 "SELECT tags.* FROM tags
406 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id 461 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
407 WHERE tags_entries.entry_id = ?"; 462 WHERE tags_entries.entry_id = ?";