Merge pull request #761 from wallabag/dev

[github/wallabag/wallabag.git] / inc / poche / Database.class.php

diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php
index d1044a6e8539c1a6fba81d253653ccb2900f7d86..11cccb7238d9d6a6e16695386152a25fc6789fa8 100755 (executable)
--- a/inc/poche/Database.class.php
+++ b/inc/poche/Database.class.php
@@ -33,6 +33,8 @@ class Database {
                 $db_path = 'pgsql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB;
                 $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD);
                 break;
+            default:
+                die(STORAGE . ' is not a recognised database system !');
         }
 
         $this->handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
@@ -229,12 +231,49 @@ class Database {
             return FALSE;
         }
     }
+    
+    public function listUsers($username=null) {
+        $sql = 'SELECT count(*) FROM users'.( $username ? ' WHERE username=?' : '');
+        $query = $this->executeQuery($sql, ( $username ? array($username) : array()));
+        list($count) = $query->fetch();
+        return $count;
+    }
+    
+    public function getUserPassword($userID) {
+        $sql = "SELECT * FROM users WHERE id=?";
+        $query = $this->executeQuery($sql, array($userID));
+        $password = $query->fetchAll();
+        return isset($password[0]['password']) ? $password[0]['password'] : null;
+    }
+    
+    public function deleteUserConfig($userID) {
+        $sql_action = 'DELETE from users_config WHERE user_id=?';
+        $params_action = array($userID);
+        $query = $this->executeQuery($sql_action, $params_action);
+        return $query;
+    }
+    
+    public function deleteTagsEntriesAndEntries($userID) {
+        $entries = $this->retrieveAll($userID);
+        foreach($entries as $entryid) {
+            $tags = $this->retrieveTagsByEntry($entryid);
+            foreach($tags as $tag) {
+                $this->removeTagForEntry($entryid,$tags);
+            }
+            $this->deleteById($entryid,$userID);
+        }
+    }
+    
+    public function deleteUser($userID) {
+        $sql_action = 'DELETE from users WHERE id=?';
+        $params_action = array($userID);
+        $query = $this->executeQuery($sql_action, $params_action);
+    }
 
     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;
     }
 
@@ -245,7 +284,7 @@ class Database {
             $sql_limit = "LIMIT ".$limit." OFFSET 0";
         }
 
-        $sql        = "SELECT * FROM entries WHERE (content = '' OR content IS NULL) AND user_id=? ORDER BY id " . $sql_limit;
+        $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();
 
@@ -253,7 +292,7 @@ class Database {
     }
 
     public function retrieveUnfetchedEntriesCount($user_id) {
-      $sql        = "SELECT count(*) FROM entries WHERE (content = '' OR content IS NULL) AND 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();
 
@@ -370,6 +409,7 @@ class Database {
     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;
         }
@@ -448,7 +488,7 @@ class Database {
         $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=?";
+            WHERE tags_entries.tag_id = ? AND entries.user_id=? ORDER by entries.id DESC";
         $query = $this->executeQuery($sql, array($tag_id, $user_id));
         $entries = $query->fetchAll();
 
@@ -472,6 +512,25 @@ class Database {
         $query          = $this->executeQuery($sql_action, $params_action);
         return $query;
     }
+    
+    public function cleanUnusedTag($tag_id) {
+        $sql_action = "SELECT tags.* FROM tags JOIN tags_entries ON tags_entries.tag_id=tags.id WHERE tags.id=?";
+        $query = $this->executeQuery($sql_action,array($tag_id));
+        $tagstokeep = $query->fetchAll();
+        $sql_action = "SELECT tags.* FROM tags LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id WHERE tags.id=?";
+        $query = $this->executeQuery($sql_action,array($tag_id));
+        $alltags = $query->fetchAll();
+        
+        foreach ($alltags as $tag) {
+            if ($tag && !in_array($tag,$tagstokeep)) {
+                $sql_action = "DELETE FROM tags WHERE id=?";
+                $params_action = array($tag[0]);
+                $this->executeQuery($sql_action, $params_action);
+                return true;
+            }
+        }
+        
+    }
 
     public function retrieveTagByValue($value) {
         $tag  = NULL;