From 60b6d7c3894f61eb9c8bc40efe5528e91386bf94 Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Sun, 9 Jun 2013 11:07:46 +0200 Subject: Fix database cache expunge issues (#1489149) - added 'expires' column --- program/lib/Roundcube/rcube.php | 12 ++--- program/lib/Roundcube/rcube_cache.php | 43 ++++++++++------ program/lib/Roundcube/rcube_cache_shared.php | 25 +++++++--- program/lib/Roundcube/rcube_db.php | 2 +- program/lib/Roundcube/rcube_imap.php | 32 +++++------- program/lib/Roundcube/rcube_imap_cache.php | 74 ++++++++++++++++++---------- program/lib/Roundcube/rcube_storage.php | 2 +- 7 files changed, 112 insertions(+), 78 deletions(-) (limited to 'program/lib/Roundcube') 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,17 +196,28 @@ 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. */ @@ -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,13 +195,24 @@ 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. */ @@ -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 @@ -48,6 +48,13 @@ class rcube_imap_cache */ private $userid; + /** + * Expiration time in seconds + * + * @var int + */ + private $ttl; + /** * Internal (in-memory) cache * @@ -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 -- cgit v1.2.3