new fields in database, reading time / date and domain name are stored

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

diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php
index ba2d1d9438565b194914d216a120dd0fae7c2617..a222ed39b76017fc8b758b2d486b429f5e645f92 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);
@@ -396,6 +398,21 @@ class Database {
         return $query;
     }
 
+
+    private function _getDomain($url)
+    {
+        return parse_url($url, PHP_URL_HOST);
+    }
+
+    private function _getReadingTime($text) {
+        $word = str_word_count(strip_tags($text));
+        $minutes = floor($word / 200);
+        $seconds = floor($word % 200 / (200 / 60));
+        $time = array('minutes' => $minutes, 'seconds' => $seconds);
+
+        return $minutes;
+    }
+
     /**
      *
      * @param string $url
@@ -405,8 +422,12 @@ class Database {
      * @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);
+        $readingTime = $this->_getReadingTime($content);
+        $domainName  = $this->_getDomain($url);
+        $date        = date('Y-m-d H:i:s');
+
+        $sql_action = 'INSERT INTO entries ( url, title, content, user_id, is_fav, is_read, date, reading_time, domain_name ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)';
+        $params_action = array($url, $title, $content, $user_id, $isFavorite, $isRead, $date, $readingTime, $domainName);
         if ( !$this->executeQuery($sql_action, $params_action) ) {
           $id = null;
         }
@@ -485,7 +506,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();
 
@@ -509,6 +530,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;