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.php96
1 files changed, 57 insertions, 39 deletions
diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php
index 2257f281..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 }
@@ -252,8 +252,16 @@ class Database {
252 return $entries; 252 return $entries;
253 } 253 }
254 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
255 public function retrieveAll($user_id) { 263 public function retrieveAll($user_id) {
256 $sql = "SELECT * FROM entries WHERE content <> '' AND user_id=? ORDER BY id"; 264 $sql = "SELECT * FROM entries WHERE user_id=? ORDER BY id";
257 $query = $this->executeQuery($sql, array($user_id)); 265 $query = $this->executeQuery($sql, array($user_id));
258 $entries = $query->fetchAll(); 266 $entries = $query->fetchAll();
259 267
@@ -272,7 +280,7 @@ class Database {
272 280
273 public function retrieveOneByURL($url, $user_id) { 281 public function retrieveOneByURL($url, $user_id) {
274 $entry = NULL; 282 $entry = NULL;
275 $sql = "SELECT * FROM entries WHERE content <> '' AND url=? AND user_id=?"; 283 $sql = "SELECT * FROM entries WHERE url=? AND user_id=?";
276 $params = array($url, $user_id); 284 $params = array($url, $user_id);
277 $query = $this->executeQuery($sql, $params); 285 $query = $this->executeQuery($sql, $params);
278 $entry = $query->fetchAll(); 286 $entry = $query->fetchAll();
@@ -289,22 +297,21 @@ class Database {
289 public function getEntriesByView($view, $user_id, $limit = '', $tag_id = 0) { 297 public function getEntriesByView($view, $user_id, $limit = '', $tag_id = 0) {
290 switch ($view) { 298 switch ($view) {
291 case 'archive': 299 case 'archive':
292 $sql = "SELECT * FROM entries WHERE content <> '' AND user_id=? AND is_read=? "; 300 $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? ";
293 $params = array($user_id, 1); 301 $params = array($user_id, 1);
294 break; 302 break;
295 case 'fav' : 303 case 'fav' :
296 $sql = "SELECT * FROM entries WHERE content <> '' AND user_id=? AND is_fav=? "; 304 $sql = "SELECT * FROM entries WHERE user_id=? AND is_fav=? ";
297 $params = array($user_id, 1); 305 $params = array($user_id, 1);
298 break; 306 break;
299 case 'tag' : 307 case 'tag' :
300 $sql = "SELECT entries.* FROM entries 308 $sql = "SELECT entries.* FROM entries
301 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id 309 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
302 WHERE entries.content <> '' AND 310 WHERE entries.user_id=? AND tags_entries.tag_id = ? ";
303 entries.user_id=? AND tags_entries.tag_id = ? ";
304 $params = array($user_id, $tag_id); 311 $params = array($user_id, $tag_id);
305 break; 312 break;
306 default: 313 default:
307 $sql = "SELECT * FROM entries WHERE content <> '' AND user_id=? AND is_read=? "; 314 $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? ";
308 $params = array($user_id, 0); 315 $params = array($user_id, 0);
309 break; 316 break;
310 } 317 }
@@ -320,22 +327,21 @@ class Database {
320 public function getEntriesByViewCount($view, $user_id, $tag_id = 0) { 327 public function getEntriesByViewCount($view, $user_id, $tag_id = 0) {
321 switch ($view) { 328 switch ($view) {
322 case 'archive': 329 case 'archive':
323 $sql = "SELECT count(*) FROM entries WHERE content <> '' AND user_id=? AND is_read=? "; 330 $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? ";
324 $params = array($user_id, 1); 331 $params = array($user_id, 1);
325 break; 332 break;
326 case 'fav' : 333 case 'fav' :
327 $sql = "SELECT count(*) FROM entries WHERE content <> '' AND user_id=? AND is_fav=? "; 334 $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_fav=? ";
328 $params = array($user_id, 1); 335 $params = array($user_id, 1);
329 break; 336 break;
330 case 'tag' : 337 case 'tag' :
331 $sql = "SELECT count(*) FROM entries 338 $sql = "SELECT count(*) FROM entries
332 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id 339 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
333 WHERE entries.content <> '' AND 340 WHERE entries.user_id=? AND tags_entries.tag_id = ? ";
334 entries.user_id=? AND tags_entries.tag_id = ? ";
335 $params = array($user_id, $tag_id); 341 $params = array($user_id, $tag_id);
336 break; 342 break;
337 default: 343 default:
338 $sql = "SELECT count(*) FROM entries WHERE content <> '' AND user_id=? AND is_read=? "; 344 $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? ";
339 $params = array($user_id, 0); 345 $params = array($user_id, 0);
340 break; 346 break;
341 } 347 }
@@ -353,11 +359,24 @@ class Database {
353 return $query; 359 return $query;
354 } 360 }
355 361
356 public function add($url, $title, $content, $user_id) { 362 /**
357 $sql_action = 'INSERT INTO entries ( url, title, content, user_id ) VALUES (?, ?, ?, ?)'; 363 *
358 $params_action = array($url, $title, $content, $user_id); 364 * @param string $url
359 $query = $this->executeQuery($sql_action, $params_action); 365 * @param string $title
360 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;
361 } 380 }
362 381
363 public function deleteById($id, $user_id) { 382 public function deleteById($id, $user_id) {
@@ -389,20 +408,20 @@ class Database {
389 return $this->getHandle()->lastInsertId($column); 408 return $this->getHandle()->lastInsertId($column);
390 } 409 }
391 410
392 public function search($term,$id,$limit = ''){ 411 public function search($term, $user_id, $limit = '') {
393 $search = '%'.$term.'%'; 412 $search = '%'.$term.'%';
394 $sql_action = ("SELECT * FROM entries WHERE user_id=? AND (content LIKE ? OR title LIKE ? OR url LIKE ?) "); //searches in content, title and URL 413 $sql_action = "SELECT * FROM entries WHERE user_id=? AND (content LIKE ? OR title LIKE ? OR url LIKE ?) "; //searches in content, title and URL
395 $sql_action .= $this->getEntriesOrder().' ' . $limit; 414 $sql_action .= $this->getEntriesOrder().' ' . $limit;
396 $params_action = array($id,$search,$search,$search); 415 $params_action = array($user_id, $search, $search, $search);
397 $query = $this->executeQuery($sql_action, $params_action); 416 $query = $this->executeQuery($sql_action, $params_action);
398 return $query->fetchAll(); 417 return $query->fetchAll();
399 } 418 }
400 419
401 public function retrieveAllTags($user_id, $term = null) { 420 public function retrieveAllTags($user_id, $term = null) {
402 $sql = "SELECT DISTINCT tags.*, count(entries.id) AS entriescount FROM tags 421 $sql = "SELECT DISTINCT tags.*, count(entries.id) AS entriescount FROM tags
403 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id 422 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
404 LEFT JOIN entries ON tags_entries.entry_id=entries.id 423 LEFT JOIN entries ON tags_entries.entry_id=entries.id
405 WHERE entries.content <> '' AND entries.user_id=? 424 WHERE entries.user_id=?
406 ". (($term) ? "AND lower(tags.value) LIKE ?" : '') ." 425 ". (($term) ? "AND lower(tags.value) LIKE ?" : '') ."
407 GROUP BY tags.id, tags.value 426 GROUP BY tags.id, tags.value
408 ORDER BY tags.value"; 427 ORDER BY tags.value";
@@ -417,7 +436,7 @@ class Database {
417 $sql = "SELECT DISTINCT tags.* FROM tags 436 $sql = "SELECT DISTINCT tags.* FROM tags
418 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id 437 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
419 LEFT JOIN entries ON tags_entries.entry_id=entries.id 438 LEFT JOIN entries ON tags_entries.entry_id=entries.id
420 WHERE entries.content <> '' AND tags.id=? AND entries.user_id=?"; 439 WHERE tags.id=? AND entries.user_id=?";
421 $params = array(intval($id), $user_id); 440 $params = array(intval($id), $user_id);
422 $query = $this->executeQuery($sql, $params); 441 $query = $this->executeQuery($sql, $params);
423 $tag = $query->fetchAll(); 442 $tag = $query->fetchAll();
@@ -426,11 +445,10 @@ class Database {
426 } 445 }
427 446
428 public function retrieveEntriesByTag($tag_id, $user_id) { 447 public function retrieveEntriesByTag($tag_id, $user_id) {
429 $sql = 448 $sql =
430 "SELECT entries.* FROM entries 449 "SELECT entries.* FROM entries
431 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id 450 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
432 WHERE entries.content <> '' AND 451 WHERE tags_entries.tag_id = ? AND entries.user_id=?";
433 tags_entries.tag_id = ? AND entries.user_id=?";
434 $query = $this->executeQuery($sql, array($tag_id, $user_id)); 452 $query = $this->executeQuery($sql, array($tag_id, $user_id));
435 $entries = $query->fetchAll(); 453 $entries = $query->fetchAll();
436 454
@@ -438,7 +456,7 @@ class Database {
438 } 456 }
439 457
440 public function retrieveTagsByEntry($entry_id) { 458 public function retrieveTagsByEntry($entry_id) {
441 $sql = 459 $sql =
442 "SELECT tags.* FROM tags 460 "SELECT tags.* FROM tags
443 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id 461 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
444 WHERE tags_entries.entry_id = ?"; 462 WHERE tags_entries.entry_id = ?";