Fix database cache expunge issues (#1489149) - added 'expires' column

7 files changed, 112 insertions, 78 deletions

diff --git a/program/lib/Roundcube/rcube.php b/program/lib/Roundcube/rcube.php
index ebfa4f8aa..8d17827be 100644
--- a/program/lib/Roundcube/rcube.php
+++ b/program/lib/Roundcube/rcube.php
@@ -478,15 +478,9 @@ class rcube
      */
     public function gc()
     {
-        foreach ($this->caches as $cache) {
-            if (is_object($cache)) {
-                $cache->expunge();
-            }
-        }
-
-        if (is_object($this->storage)) {
-            $this->storage->expunge_cache();
-        }
+        rcube_cache::gc();
+        rcube_cache_shared::gc();
+        $this->get_storage()->cache_gc();
 
         $this->gc_temp();
     }
diff --git a/program/lib/Roundcube/rcube_cache.php b/program/lib/Roundcube/rcube_cache.php
index 08c9fc8a9..a708cb292 100644
--- a/program/lib/Roundcube/rcube_cache.php
+++ b/program/lib/Roundcube/rcube_cache.php
@@ -38,6 +38,7 @@ class rcube_cache
     private $type;
     private $userid;
     private $prefix;
+    private $table;
     private $ttl;
     private $packed;
     private $index;
@@ -71,8 +72,9 @@ class rcube_cache
             $this->db   = function_exists('apc_exists'); // APC 3.1.4 required
         }
         else {
-            $this->type = 'db';
-            $this->db   = $rcube->get_dbh();
+            $this->type  = 'db';
+            $this->db    = $rcube->get_dbh();
+            $this->table = $this->db->table_name('cache');
         }
 
         // convert ttl string to seconds
@@ -194,18 +196,29 @@ class rcube_cache
     {
         if ($this->type == 'db' && $this->db && $this->ttl) {
             $this->db->query(
-                "DELETE FROM ".$this->db->table_name('cache').
+                "DELETE FROM ".$this->table.
                 " WHERE user_id = ?".
                 " AND cache_key LIKE ?".
-                " AND " . $this->db->unixtimestamp('created')." < ?",
+                " AND expires < " . $this->db->now(),
                 $this->userid,
-                $this->prefix.'.%',
-                time() - $this->ttl);
+                $this->prefix.'.%');
         }
     }
 
 
     /**
+     * Remove expired records of all caches
+     */
+    static function gc()
+    {
+        $rcube = rcube::get_instance();
+        $db    = $rcube->get_dbh();
+
+        $db->query("DELETE FROM " . $db->table_name('cache') . " WHERE expires < " . $db->now());
+    }
+
+
+    /**
      * Writes the cache back to the DB.
      */
     function close()
@@ -271,7 +284,7 @@ class rcube_cache
         else {
             $sql_result = $this->db->limitquery(
                 "SELECT data, cache_key".
-                " FROM ".$this->db->table_name('cache').
+                " FROM " . $this->table.
                 " WHERE user_id = ?".
                 " AND cache_key = ?".
                 // for better performance we allow more records for one key
@@ -326,7 +339,7 @@ class rcube_cache
         // Remove NULL rows (here we don't need to check if the record exist)
         if ($data == 'N;') {
             $this->db->query(
-                "DELETE FROM ".$this->db->table_name('cache').
+                "DELETE FROM " . $this->table.
                 " WHERE user_id = ?".
                 " AND cache_key = ?",
                 $this->userid, $key);
@@ -337,8 +350,10 @@ class rcube_cache
         // update existing cache record
         if ($key_exists) {
             $result = $this->db->query(
-                "UPDATE ".$this->db->table_name('cache').
-                " SET created = ". $this->db->now().", data = ?".
+                "UPDATE " . $this->table.
+                " SET created = " . $this->db->now().
+                    ", expires = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL').
+                    ", data = ?".
                 " WHERE user_id = ?".
                 " AND cache_key = ?",
                 $data, $this->userid, $key);
@@ -348,9 +363,9 @@ class rcube_cache
             // for better performance we allow more records for one key
             // so, no need to check if record exist (see rcube_cache::read_record())
             $result = $this->db->query(
-                "INSERT INTO ".$this->db->table_name('cache').
-                " (created, user_id, cache_key, data)".
-                " VALUES (".$this->db->now().", ?, ?, ?)",
+                "INSERT INTO " . $this->table.
+                " (created, expires, user_id, cache_key, data)".
+                " VALUES (" . $this->db->now() . ", " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL') . ", ?, ?, ?)",
                 $this->userid, $key, $data);
         }
 
@@ -411,7 +426,7 @@ class rcube_cache
         }
 
         $this->db->query(
-            "DELETE FROM ".$this->db->table_name('cache').
+            "DELETE FROM " . $this->table.
             " WHERE user_id = ?" . $where,
             $this->userid);
     }
diff --git a/program/lib/Roundcube/rcube_cache_shared.php b/program/lib/Roundcube/rcube_cache_shared.php
index 2c4af2046..8f2574046 100644
--- a/program/lib/Roundcube/rcube_cache_shared.php
+++ b/program/lib/Roundcube/rcube_cache_shared.php
@@ -195,14 +195,25 @@ class rcube_cache_shared
             $this->db->query(
                 "DELETE FROM " . $this->table
                 . " WHERE cache_key LIKE ?"
-                . " AND " . $this->db->unixtimestamp('created') . " < ?",
-                $this->prefix . '.%',
-                time() - $this->ttl);
+                . " AND expires < " . $this->db->now(),
+                $this->prefix . '.%');
         }
     }
 
 
     /**
+     * Remove expired records of all caches
+     */
+    static function gc()
+    {
+        $rcube = rcube::get_instance();
+        $db    = $rcube->get_dbh();
+
+        $db->query("DELETE FROM " . $db->table_name('cache_shared') . " WHERE expires < " . $db->now());
+    }
+
+
+    /**
      * Writes the cache back to the DB.
      */
     function close()
@@ -328,7 +339,9 @@ class rcube_cache_shared
         if ($key_exists) {
             $result = $this->db->query(
                 "UPDATE " . $this->table .
-                " SET created = " . $this->db->now() . ", data = ?" .
+                " SET created = " . $this->db->now() .
+                    ", expires = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL') .
+                    ", data = ?".
                 " WHERE cache_key = ?",
                 $data, $key);
         }
@@ -338,8 +351,8 @@ class rcube_cache_shared
             // so, no need to check if record exist (see rcube_cache::read_record())
             $result = $this->db->query(
                 "INSERT INTO ".$this->table.
-                " (created, cache_key, data)".
-                " VALUES (".$this->db->now().", ?, ?)",
+                " (created, expires, cache_key, data)".
+                " VALUES (".$this->db->now().", " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL') . ", ?, ?)",
                 $key, $data);
         }
 
diff --git a/program/lib/Roundcube/rcube_db.php b/program/lib/Roundcube/rcube_db.php
index fe5ed39c5..852070073 100644
--- a/program/lib/Roundcube/rcube_db.php
+++ b/program/lib/Roundcube/rcube_db.php
@@ -692,7 +692,7 @@ class rcube_db
         if ($interval) {
             $add = ' ' . ($interval > 0 ? '+' : '-') . ' INTERVAL ';
             $add .= $interval > 0 ? intval($interval) : intval($interval) * -1;
-            $add .= ' SECONDS';
+            $add .= ' SECOND';
         }
 
         return "now()" . $add;
diff --git a/program/lib/Roundcube/rcube_imap.php b/program/lib/Roundcube/rcube_imap.php
index 9cd8ef7e4..257efbbbc 100644
--- a/program/lib/Roundcube/rcube_imap.php
+++ b/program/lib/Roundcube/rcube_imap.php
@@ -3691,7 +3691,7 @@ class rcube_imap extends rcube_storage
     {
         if ($this->caching && !$this->cache) {
             $rcube = rcube::get_instance();
-            $ttl = $rcube->config->get('imap_cache_ttl', '10d');
+            $ttl   = $rcube->config->get('imap_cache_ttl', '10d');
             $this->cache = $rcube->get_cache('IMAP', $this->caching, $ttl);
         }
 
@@ -3739,24 +3739,6 @@ class rcube_imap extends rcube_storage
         }
     }
 
-    /**
-     * Delete outdated cache entries
-     */
-    public function expunge_cache()
-    {
-        if ($this->mcache) {
-            $ttl = rcube::get_instance()->config->get('messages_cache_ttl', '10d');
-            $this->mcache->expunge($ttl);
-        }
-
-/*
-        // this cache is expunged by rcube class
-        if ($this->cache) {
-            $this->cache->expunge();
-        }
-*/
-    }
-
 
     /* --------------------------------
      *   message caching methods
@@ -3790,8 +3772,9 @@ class rcube_imap extends rcube_storage
         if ($this->messages_caching && !$this->mcache) {
             $rcube = rcube::get_instance();
             if (($dbh = $rcube->get_dbh()) && ($userid = $rcube->get_user_id())) {
+                $ttl = $rcube->config->get('messages_cache_ttl', '10d');
                 $this->mcache = new rcube_imap_cache(
-                    $dbh, $this, $userid, $this->options['skip_deleted']);
+                    $dbh, $this, $userid, $this->options['skip_deleted'], $ttl);
             }
         }
 
@@ -3813,6 +3796,15 @@ class rcube_imap extends rcube_storage
     }
 
 
+    /**
+     * Delete outdated cache entries
+     */
+    function cache_gc()
+    {
+        rcube_imap_cache::gc();
+    }
+
+
     /* --------------------------------
      *         protected methods
      * --------------------------------*/
diff --git a/program/lib/Roundcube/rcube_imap_cache.php b/program/lib/Roundcube/rcube_imap_cache.php
index 403137f2d..e2fd2a98b 100644
--- a/program/lib/Roundcube/rcube_imap_cache.php
+++ b/program/lib/Roundcube/rcube_imap_cache.php
@@ -49,6 +49,13 @@ class rcube_imap_cache
     private $userid;
 
     /**
+     * Expiration time in seconds
+     *
+     * @var int
+     */
+    private $ttl;
+
+    /**
      * Internal (in-memory) cache
      *
      * @var array
@@ -83,13 +90,25 @@ class rcube_imap_cache
 
     /**
      * Object constructor.
+     *
+     * @param rcube_db   $db           DB handler
+     * @param rcube_imap $imap         IMAP handler
+     * @param int        $userid       User identifier
+     * @param bool       $skip_deleted skip_deleted flag
+     * @param string     $ttl          Expiration time of memcache/apc items
+     *
      */
-    function __construct($db, $imap, $userid, $skip_deleted)
+    function __construct($db, $imap, $userid, $skip_deleted, $ttl=0)
     {
+        // convert ttl string to seconds
+        $ttl = get_offset_sec($ttl);
+        if ($ttl > 2592000) $ttl = 2592000;
+
         $this->db           = $db;
         $this->imap         = $imap;
         $this->userid       = $userid;
         $this->skip_deleted = $skip_deleted;
+        $this->ttl          = $ttl;
     }
 
 
@@ -426,7 +445,7 @@ class rcube_imap_cache
         if (!$force) {
             $res = $this->db->query(
                 "UPDATE ".$this->db->table_name('cache_messages')
-                ." SET flags = ?, data = ?, changed = ".$this->db->now()
+                ." SET flags = ?, data = ?, expires = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL')
                 ." WHERE user_id = ?"
                     ." AND mailbox = ?"
                     ." AND uid = ?",
@@ -442,8 +461,8 @@ class rcube_imap_cache
         // insert new record
         $res = $this->db->query(
             "INSERT INTO ".$this->db->table_name('cache_messages')
-            ." (user_id, mailbox, uid, flags, changed, data)"
-            ." VALUES (?, ?, ?, ?, ".$this->db->now().", ?)",
+            ." (user_id, mailbox, uid, flags, expires, data)"
+            ." VALUES (?, ?, ?, ?, ". ($this->ttl ? $this->db->now($this->ttl) : 'NULL') . ", ?)",
             $this->userid, $mailbox, (int) $message->uid, $flags, $msg);
 
         // race-condition, insert failed so try update (#1489146)
@@ -451,7 +470,8 @@ class rcube_imap_cache
         if ($force && !$res && !$this->db->is_error($res)) {
             $this->db->query(
                 "UPDATE ".$this->db->table_name('cache_messages')
-                ." SET flags = ?, data = ?, changed = ".$this->db->now()
+                ." SET expires = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL')
+                    .", flags = ?, data = ?"
                 ." WHERE user_id = ?"
                     ." AND mailbox = ?"
                     ." AND uid = ?",
@@ -499,7 +519,7 @@ class rcube_imap_cache
 
         $this->db->query(
             "UPDATE ".$this->db->table_name('cache_messages')
-            ." SET changed = ".$this->db->now()
+            ." SET expires = ". ($this->ttl ? $this->db->now($this->ttl) : 'NULL')
             .", flags = flags ".($enabled ? "+ $idx" : "- $idx")
             ." WHERE user_id = ?"
                 ." AND mailbox = ?"
@@ -622,23 +642,21 @@ class rcube_imap_cache
 
 
     /**
-     * Delete cache entries older than TTL
-     *
-     * @param string $ttl  Lifetime of message cache entries
+     * Delete expired cache entries
      */
-    function expunge($ttl)
+    static function gc()
     {
-        // get expiration timestamp
-        $ts = get_offset_time($ttl, -1);
+        $rcube = rcube::get_instance();
+        $db    = $rcube->get_dbh();
 
-        $this->db->query("DELETE FROM ".$this->db->table_name('cache_messages')
-              ." WHERE changed < " . $this->db->fromunixtime($ts));
+        $db->query("DELETE FROM ".$db->table_name('cache_messages')
+              ." WHERE expired < " . $db->now());
 
-        $this->db->query("DELETE FROM ".$this->db->table_name('cache_index')
-              ." WHERE changed < " . $this->db->fromunixtime($ts));
+        $db->query("DELETE FROM ".$db->table_name('cache_index')
+              ." WHERE expired < " . $db->now());
 
-        $this->db->query("DELETE FROM ".$this->db->table_name('cache_thread')
-              ." WHERE changed < " . $this->db->fromunixtime($ts));
+        $db->query("DELETE FROM ".$db->table_name('cache_thread')
+              ." WHERE expired < " . $db->now());
     }
 
 
@@ -732,7 +750,7 @@ class rcube_imap_cache
         if ($exists) {
             $res = $this->db->query(
                 "UPDATE ".$this->db->table_name('cache_index')
-                ." SET data = ?, valid = 1, changed = ".$this->db->now()
+                ." SET data = ?, valid = 1, expires = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL')
                 ." WHERE user_id = ?"
                     ." AND mailbox = ?",
                 $data, $this->userid, $mailbox);
@@ -746,8 +764,8 @@ class rcube_imap_cache
 
         $res = $this->db->query(
             "INSERT INTO ".$this->db->table_name('cache_index')
-            ." (user_id, mailbox, data, valid, changed)"
-            ." VALUES (?, ?, ?, 1, ".$this->db->now().")",
+            ." (user_id, mailbox, valid, expires, data)"
+            ." VALUES (?, ?, 1, ". ($this->ttl ? $this->db->now($this->ttl) : 'NULL') .", ?)",
             $this->userid, $mailbox, $data);
 
         // race-condition, insert failed so try update (#1489146)
@@ -755,7 +773,7 @@ class rcube_imap_cache
         if (!$exists && !$res && !$this->db->is_error($res)) {
             $res = $this->db->query(
                 "UPDATE ".$this->db->table_name('cache_index')
-                ." SET data = ?, valid = 1, changed = ".$this->db->now()
+                ." SET data = ?, valid = 1, expires = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL')
                 ." WHERE user_id = ?"
                     ." AND mailbox = ?",
                 $data, $this->userid, $mailbox);
@@ -778,10 +796,12 @@ class rcube_imap_cache
         );
         $data = implode('@', $data);
 
+        $expires = ($this->ttl ? $this->db->now($this->ttl) : 'NULL');
+
         if ($exists) {
             $res = $this->db->query(
                 "UPDATE ".$this->db->table_name('cache_thread')
-                ." SET data = ?, changed = ".$this->db->now()
+                ." SET data = ?, expires = $expires"
                 ." WHERE user_id = ?"
                     ." AND mailbox = ?",
                 $data, $this->userid, $mailbox);
@@ -795,8 +815,8 @@ class rcube_imap_cache
 
         $res = $this->db->query(
             "INSERT INTO ".$this->db->table_name('cache_thread')
-            ." (user_id, mailbox, data, changed)"
-            ." VALUES (?, ?, ?, ".$this->db->now().")",
+            ." (user_id, mailbox, expires, data)"
+            ." VALUES (?, ?, $expires, ?)",
             $this->userid, $mailbox, $data);
 
         // race-condition, insert failed so try update (#1489146)
@@ -804,7 +824,7 @@ class rcube_imap_cache
         if (!$exists && !$res && !$this->db->is_error($res)) {
             $this->db->query(
                 "UPDATE ".$this->db->table_name('cache_thread')
-                ." SET data = ?, changed = ".$this->db->now()
+                ." SET expires = $expires, data = ?"
                 ." WHERE user_id = ?"
                     ." AND mailbox = ?",
                 $data, $this->userid, $mailbox);
@@ -1058,7 +1078,7 @@ class rcube_imap_cache
 
                     $this->db->query(
                         "UPDATE ".$this->db->table_name('cache_messages')
-                        ." SET flags = ?, changed = ".$this->db->now()
+                        ." SET flags = ?, expires = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL')
                         ." WHERE user_id = ?"
                             ." AND mailbox = ?"
                             ." AND uid = ?"
diff --git a/program/lib/Roundcube/rcube_storage.php b/program/lib/Roundcube/rcube_storage.php
index 700d12ffb..b17291bdf 100644
--- a/program/lib/Roundcube/rcube_storage.php
+++ b/program/lib/Roundcube/rcube_storage.php
@@ -986,6 +986,6 @@ abstract class rcube_storage
     /**
      * Delete outdated cache entries
      */
-    abstract function expunge_cache();
+    abstract function cache_gc();
 
 }  // end class rcube_storage