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 --- CHANGELOG | 1 + SQL/mssql.initial.sql | 47 ++++++++++-------- SQL/mssql/2013061000.sql | 44 +++++++++++++++++ SQL/mysql.initial.sql | 24 ++++----- SQL/mysql/2013061000.sql | 24 +++++++++ SQL/postgres.initial.sql | 30 +++++------ SQL/postgres/2013061000.sql | 24 +++++++++ SQL/sqlite.initial.sql | 20 ++++---- SQL/sqlite/2013061000.sql | 48 ++++++++++++++++++ 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 +- 16 files changed, 319 insertions(+), 133 deletions(-) create mode 100644 SQL/mssql/2013061000.sql create mode 100644 SQL/mysql/2013061000.sql create mode 100644 SQL/postgres/2013061000.sql create mode 100644 SQL/sqlite/2013061000.sql diff --git a/CHANGELOG b/CHANGELOG index a601dd2a0..179bb0b97 100644 --- a/CHANGELOG +++ b/CHANGELOG @@ -1,6 +1,7 @@ CHANGELOG Roundcube Webmail =========================== +- Fix database cache expunge issues (#1489149) - Fix date format issues on MS SQL Server (#1488918) - Fix so non-inline images aren't skipped on forward (#1489150) - Add imap_cache_ttl option to configure TTL of imap_cache diff --git a/SQL/mssql.initial.sql b/SQL/mssql.initial.sql index 23e7c7d7f..1027867e3 100644 --- a/SQL/mssql.initial.sql +++ b/SQL/mssql.initial.sql @@ -2,6 +2,7 @@ CREATE TABLE [dbo].[cache] ( [user_id] [int] NOT NULL , [cache_key] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , [created] [datetime] NOT NULL , + [expires] [datetime] NULL , [data] [text] COLLATE Latin1_General_CI_AI NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO @@ -9,6 +10,7 @@ GO CREATE TABLE [dbo].[cache_shared] ( [cache_key] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL , [created] [datetime] NOT NULL , + [expires] [datetime] NULL , [data] [text] COLLATE Latin1_General_CI_AI NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO @@ -16,7 +18,7 @@ GO CREATE TABLE [dbo].[cache_index] ( [user_id] [int] NOT NULL , [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , - [changed] [datetime] NOT NULL , + [expires] [datetime] NULL , [valid] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL , [data] [text] COLLATE Latin1_General_CI_AI NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] @@ -25,7 +27,7 @@ GO CREATE TABLE [dbo].[cache_thread] ( [user_id] [int] NOT NULL , [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , - [changed] [datetime] NOT NULL , + [expires] [datetime] NULL , [data] [text] COLLATE Latin1_General_CI_AI NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO @@ -34,7 +36,7 @@ CREATE TABLE [dbo].[cache_messages] ( [user_id] [int] NOT NULL , [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , [uid] [int] NOT NULL , - [changed] [datetime] NOT NULL , + [expires] [datetime] NULL , [data] [text] COLLATE Latin1_General_CI_AI NOT NULL , [flags] [int] NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] @@ -211,46 +213,49 @@ ALTER TABLE [dbo].[cache] ADD CONSTRAINT [DF_cache_created] DEFAULT (getdate()) FOR [created] GO -CREATE INDEX [IX_cache_user_id] ON [dbo].[cache]([user_id]) ON [PRIMARY] +ALTER TABLE [dbo].[cache_shared] ADD + CONSTRAINT [DF_cache_shared_created] DEFAULT (getdate()) FOR [created] GO -CREATE INDEX [IX_cache_cache_key] ON [dbo].[cache]([cache_key]) ON [PRIMARY] +ALTER TABLE [dbo].[cache_index] ADD + CONSTRAINT [DF_cache_index_valid] DEFAULT ('0') FOR [valid] GO -CREATE INDEX [IX_cache_created] ON [dbo].[cache]([created]) ON [PRIMARY] +ALTER TABLE [dbo].[cache_messages] ADD + CONSTRAINT [DF_cache_messages_flags] DEFAULT (0) FOR [flags] GO -ALTER TABLE [dbo].[cache_shared] ADD - CONSTRAINT [DF_cache_shared_created] DEFAULT (getdate()) FOR [created] +CREATE INDEX [IX_cache_user_id] ON [dbo].[cache]([user_id]) ON [PRIMARY] +GO + +CREATE INDEX [IX_cache_cache_key] ON [dbo].[cache]([cache_key]) ON [PRIMARY] GO CREATE INDEX [IX_cache_shared_cache_key] ON [dbo].[cache_shared]([cache_key]) ON [PRIMARY] GO -CREATE INDEX [IX_cache_shared_created] ON [dbo].[cache_shared]([created]) ON [PRIMARY] +CREATE INDEX [IX_cache_index_user_id] ON [dbo].[cache_index]([user_id]) ON [PRIMARY] GO -ALTER TABLE [dbo].[cache_index] ADD - CONSTRAINT [DF_cache_index_changed] DEFAULT (getdate()) FOR [changed], - CONSTRAINT [DF_cache_index_valid] DEFAULT ('0') FOR [valid] +CREATE INDEX [IX_cache_thread_user_id] ON [dbo].[cache_thread]([user_id]) ON [PRIMARY] GO -CREATE INDEX [IX_cache_index_user_id] ON [dbo].[cache_index]([user_id]) ON [PRIMARY] +CREATE INDEX [IX_cache_messages_user_id] ON [dbo].[cache_messages]([user_id]) ON [PRIMARY] GO -ALTER TABLE [dbo].[cache_thread] ADD - CONSTRAINT [DF_cache_thread_changed] DEFAULT (getdate()) FOR [changed] +CREATE INDEX [IX_cache_expires] ON [dbo].[cache]([expires]) ON [PRIMARY] GO -CREATE INDEX [IX_cache_thread_user_id] ON [dbo].[cache_thread]([user_id]) ON [PRIMARY] +CREATE INDEX [IX_cache_shared_expires] ON [dbo].[cache_shared]([expires]) ON [PRIMARY] GO -ALTER TABLE [dbo].[cache_messages] ADD - CONSTRAINT [DF_cache_messages_changed] DEFAULT (getdate()) FOR [changed], - CONSTRAINT [DF_cache_messages_flags] DEFAULT (0) FOR [flags] +CREATE INDEX [IX_cache_index_expires] ON [dbo].[cache_index]([expires]) ON [PRIMARY] GO -CREATE INDEX [IX_cache_messages_user_id] ON [dbo].[cache_messages]([user_id]) ON [PRIMARY] +CREATE INDEX [IX_cache_thread_expires] ON [dbo].[cache_thread]([expires]) ON [PRIMARY] +GO + +CREATE INDEX [IX_cache_messages_expires] ON [dbo].[cache_messages]([expires]) ON [PRIMARY] GO ALTER TABLE [dbo].[contacts] ADD @@ -388,6 +393,6 @@ CREATE TRIGGER [contact_delete_member] ON [dbo].[contacts] WHERE [contact_id] IN (SELECT [contact_id] FROM deleted) GO -INSERT INTO [dbo].[system] ([name], [value]) VALUES ('roundcube-version', '2013052500') +INSERT INTO [dbo].[system] ([name], [value]) VALUES ('roundcube-version', '2013061000') GO \ No newline at end of file diff --git a/SQL/mssql/2013061000.sql b/SQL/mssql/2013061000.sql new file mode 100644 index 000000000..55b5ec3a5 --- /dev/null +++ b/SQL/mssql/2013061000.sql @@ -0,0 +1,44 @@ +ALTER TABLE [dbo].[cache] ADD COLUMN [expires] [datetime] NULL +GO +ALTER TABLE [dbo].[cache_shared] ADD COLUMN [expires] [datetime] NULL +GO +ALTER TABLE [dbo].[cache_index] ADD COLUMN [expires] [datetime] NULL +GO +ALTER TABLE [dbo].[cache_thread] ADD COLUMN [expires] [datetime] NULL +GO +ALTER TABLE [dbo].[cache_messages] ADD COLUMN [expires] [datetime] NULL +GO + +UPDATE [dbo].[cache] SET [expires] = DATEADD(second, 604800, [created]) +GO +UPDATE [dbo].[cache_shared] SET [expires] = DATEADD(second, 604800, [created]) +GO +UPDATE [dbo].[cache_index] SET [expires] = DATEADD(second, 604800, [changed]) +GO +UPDATE [dbo].[cache_thread] SET [expires] = DATEADD(second, 604800, [changed]) +GO +UPDATE [dbo].[cache_messages] SET [expires] = DATEADD(second, 604800, [changed]) +GO + +DROP INDEX [IX_cache_created] +GO +DROP INDEX [IX_cache_shared_created] +GO +ALTER TABLE [dbo].[cache_index] DROP COLUMN [changed] +GO +ALTER TABLE [dbo].[cache_thread] DROP COLUMN [changed] +GO +ALTER TABLE [dbo].[cache_messages] DROP COLUMN [changed] +GO + +CREATE INDEX [IX_cache_expires] ON [dbo].[cache]([expires]) ON [PRIMARY] +GO +CREATE INDEX [IX_cache_shared_expires] ON [dbo].[cache_shared]([expires]) ON [PRIMARY] +GO +CREATE INDEX [IX_cache_index_expires] ON [dbo].[cache_index]([expires]) ON [PRIMARY] +GO +CREATE INDEX [IX_cache_thread_expires] ON [dbo].[cache_thread]([expires]) ON [PRIMARY] +GO +CREATE INDEX [IX_cache_messages_expires] ON [dbo].[cache_messages]([expires]) ON [PRIMARY] +GO + \ No newline at end of file diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql index 75268ca03..4e4833a62 100644 --- a/SQL/mysql.initial.sql +++ b/SQL/mysql.initial.sql @@ -35,12 +35,13 @@ CREATE TABLE `users` ( CREATE TABLE `cache` ( `user_id` int(10) UNSIGNED NOT NULL, - `cache_key` varchar(128) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL , + `cache_key` varchar(128) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL, `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', + `expires` datetime DEFAULT NULL, `data` longtext NOT NULL, CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, - INDEX `created_index` (`created`), + INDEX `expires_index` (`expires`), INDEX `user_cache_index` (`user_id`,`cache_key`) ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; @@ -48,10 +49,11 @@ CREATE TABLE `cache` ( -- Table structure for table `cache_shared` CREATE TABLE `cache_shared` ( - `cache_key` varchar(255) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL , + `cache_key` varchar(255) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL, `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', + `expires` datetime DEFAULT NULL, `data` longtext NOT NULL, - INDEX `created_index` (`created`), + INDEX `expires_index` (`expires`), INDEX `cache_key_index` (`cache_key`) ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; @@ -61,12 +63,12 @@ CREATE TABLE `cache_shared` ( CREATE TABLE `cache_index` ( `user_id` int(10) UNSIGNED NOT NULL, `mailbox` varchar(255) BINARY NOT NULL, - `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', + `expires` datetime DEFAULT NULL, `valid` tinyint(1) NOT NULL DEFAULT '0', `data` longtext NOT NULL, CONSTRAINT `user_id_fk_cache_index` FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, - INDEX `changed_index` (`changed`), + INDEX `expires_index` (`expires`), PRIMARY KEY (`user_id`, `mailbox`) ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; @@ -76,11 +78,11 @@ CREATE TABLE `cache_index` ( CREATE TABLE `cache_thread` ( `user_id` int(10) UNSIGNED NOT NULL, `mailbox` varchar(255) BINARY NOT NULL, - `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', + `expires` datetime DEFAULT NULL, `data` longtext NOT NULL, CONSTRAINT `user_id_fk_cache_thread` FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, - INDEX `changed_index` (`changed`), + INDEX `expires_index` (`expires`), PRIMARY KEY (`user_id`, `mailbox`) ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; @@ -91,12 +93,12 @@ CREATE TABLE `cache_messages` ( `user_id` int(10) UNSIGNED NOT NULL, `mailbox` varchar(255) BINARY NOT NULL, `uid` int(11) UNSIGNED NOT NULL DEFAULT '0', - `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', + `expires` datetime DEFAULT NULL, `data` longtext NOT NULL, `flags` int(11) NOT NULL DEFAULT '0', CONSTRAINT `user_id_fk_cache_messages` FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, - INDEX `changed_index` (`changed`), + INDEX `expires_index` (`expires`), PRIMARY KEY (`user_id`, `mailbox`, `uid`) ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; @@ -207,4 +209,4 @@ CREATE TABLE `system` ( /*!40014 SET FOREIGN_KEY_CHECKS=1 */; -INSERT INTO system (name, value) VALUES ('roundcube-version', '2013052500'); +INSERT INTO system (name, value) VALUES ('roundcube-version', '2013061000'); diff --git a/SQL/mysql/2013061000.sql b/SQL/mysql/2013061000.sql new file mode 100644 index 000000000..54041b340 --- /dev/null +++ b/SQL/mysql/2013061000.sql @@ -0,0 +1,24 @@ +ALTER TABLE `cache` ADD `expires` datetime DEFAULT NULL; +ALTER TABLE `cache_shared` ADD `expires` datetime DEFAULT NULL; +ALTER TABLE `cache_index` ADD `expires` datetime DEFAULT NULL; +ALTER TABLE `cache_thread` ADD `expires` datetime DEFAULT NULL; +ALTER TABLE `cache_messages` ADD `expires` datetime DEFAULT NULL; + +-- initialize expires column with created/changed date + 7days +UPDATE `cache` SET `expires` = `created` + interval 604800 second; +UPDATE `cache_shared` SET `expires` = `created` + interval 604800 second; +UPDATE `cache_index` SET `expires` = `changed` + interval 604800 second; +UPDATE `cache_thread` SET `expires` = `changed` + interval 604800 second; +UPDATE `cache_messages` SET `expires` = `changed` + interval 604800 second; + +ALTER TABLE `cache` DROP INDEX `created_index`; +ALTER TABLE `cache_shared` DROP INDEX `created_index`; +ALTER TABLE `cache_index` DROP `changed`; +ALTER TABLE `cache_thread` DROP `changed`; +ALTER TABLE `cache_messages` DROP `changed`; + +ALTER TABLE `cache` ADD INDEX `expires_index` (`expires`); +ALTER TABLE `cache_shared` ADD INDEX `expires_index` (`expires`); +ALTER TABLE `cache_index` ADD INDEX `expires_index` (`expires`); +ALTER TABLE `cache_thread` ADD INDEX `expires_index` (`expires`); +ALTER TABLE `cache_messages` ADD INDEX `expires_index` (`expires`); diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql index c54f05f0b..f18cb6ad0 100644 --- a/SQL/postgres.initial.sql +++ b/SQL/postgres.initial.sql @@ -164,14 +164,15 @@ CREATE INDEX contactgroupmembers_contact_id_idx ON contactgroupmembers (contact_ CREATE TABLE "cache" ( user_id integer NOT NULL - REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, cache_key varchar(128) DEFAULT '' NOT NULL, created timestamp with time zone DEFAULT now() NOT NULL, + expires timestamp with time zone DEFAULT NULL, data text NOT NULL ); CREATE INDEX cache_user_id_idx ON "cache" (user_id, cache_key); -CREATE INDEX cache_created_idx ON "cache" (created); +CREATE INDEX cache_expires_idx ON "cache" (expires); -- -- Table "cache_shared" @@ -181,11 +182,12 @@ CREATE INDEX cache_created_idx ON "cache" (created); CREATE TABLE "cache_shared" ( cache_key varchar(255) NOT NULL, created timestamp with time zone DEFAULT now() NOT NULL, + expires timestamp with time zone DEFAULT NULL, data text NOT NULL ); CREATE INDEX cache_shared_cache_key_idx ON "cache_shared" (cache_key); -CREATE INDEX cache_shared_created_idx ON "cache_shared" (created); +CREATE INDEX cache_shared_expires_idx ON "cache_shared" (expires); -- -- Table "cache_index" @@ -194,15 +196,15 @@ CREATE INDEX cache_shared_created_idx ON "cache_shared" (created); CREATE TABLE cache_index ( user_id integer NOT NULL - REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, mailbox varchar(255) NOT NULL, - changed timestamp with time zone DEFAULT now() NOT NULL, + expires timestamp with time zone DEFAULT NULL, valid smallint NOT NULL DEFAULT 0, data text NOT NULL, PRIMARY KEY (user_id, mailbox) ); -CREATE INDEX cache_index_changed_idx ON cache_index (changed); +CREATE INDEX cache_index_expires_idx ON cache_index (expires); -- -- Table "cache_thread" @@ -211,14 +213,14 @@ CREATE INDEX cache_index_changed_idx ON cache_index (changed); CREATE TABLE cache_thread ( user_id integer NOT NULL - REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, mailbox varchar(255) NOT NULL, - changed timestamp with time zone DEFAULT now() NOT NULL, + expires timestamp with time zone DEFAULT NULL, data text NOT NULL, PRIMARY KEY (user_id, mailbox) ); -CREATE INDEX cache_thread_changed_idx ON cache_thread (changed); +CREATE INDEX cache_thread_expires_idx ON cache_thread (expires); -- -- Table "cache_messages" @@ -227,16 +229,16 @@ CREATE INDEX cache_thread_changed_idx ON cache_thread (changed); CREATE TABLE cache_messages ( user_id integer NOT NULL - REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, mailbox varchar(255) NOT NULL, uid integer NOT NULL, - changed timestamp with time zone DEFAULT now() NOT NULL, + expires timestamp with time zone DEFAULT NULL, data text NOT NULL, flags integer NOT NULL DEFAULT 0, PRIMARY KEY (user_id, mailbox, uid) ); -CREATE INDEX cache_messages_changed_idx ON cache_messages (changed); +CREATE INDEX cache_messages_expires_idx ON cache_messages (expires); -- -- Table "dictionary" @@ -245,7 +247,7 @@ CREATE INDEX cache_messages_changed_idx ON cache_messages (changed); CREATE TABLE dictionary ( user_id integer DEFAULT NULL - REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, "language" varchar(5) NOT NULL, data text NOT NULL, CONSTRAINT dictionary_user_id_language_key UNIQUE (user_id, "language") @@ -288,4 +290,4 @@ CREATE TABLE "system" ( value text ); -INSERT INTO system (name, value) VALUES ('roundcube-version', '2013052500'); +INSERT INTO system (name, value) VALUES ('roundcube-version', '2013061000'); diff --git a/SQL/postgres/2013061000.sql b/SQL/postgres/2013061000.sql new file mode 100644 index 000000000..9db0ebcd7 --- /dev/null +++ b/SQL/postgres/2013061000.sql @@ -0,0 +1,24 @@ +ALTER TABLE "cache" ADD expires timestamp with time zone DEFAULT NULL; +ALTER TABLE "cache_shared" ADD expires timestamp with time zone DEFAULT NULL; +ALTER TABLE "cache_index" ADD expires timestamp with time zone DEFAULT NULL; +ALTER TABLE "cache_thread" ADD expires timestamp with time zone DEFAULT NULL; +ALTER TABLE "cache_messages" ADD expires timestamp with time zone DEFAULT NULL; + +-- initialize expires column with created/changed date + 7days +UPDATE "cache" SET expires = created + interval '604800 seconds'; +UPDATE "cache_shared" SET expires = created + interval '604800 seconds'; +UPDATE "cache_index" SET expires = changed + interval '604800 seconds'; +UPDATE "cache_thread" SET expires = changed + interval '604800 seconds'; +UPDATE "cache_messages" SET expires = changed + interval '604800 seconds'; + +DROP INDEX cache_created_idx; +DROP INDEX cache_shared_created_idx; +ALTER TABLE "cache_index" DROP "changed"; +ALTER TABLE "cache_thread" DROP "changed"; +ALTER TABLE "cache_messages" DROP "changed"; + +CREATE INDEX cache_expires_idx ON "cache" (expires); +CREATE INDEX cache_shared_expires_idx ON "cache_shared" (expires); +CREATE INDEX cache_index_expires_idx ON "cache_index" (expires); +CREATE INDEX cache_thread_expires_idx ON "cache_thread" (expires); +CREATE INDEX cache_messages_expires_idx ON "cache_messages" (expires); diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql index 1b5b62b31..28a43680d 100644 --- a/SQL/sqlite.initial.sql +++ b/SQL/sqlite.initial.sql @@ -126,11 +126,12 @@ CREATE TABLE cache ( user_id integer NOT NULL default 0, cache_key varchar(128) NOT NULL default '', created datetime NOT NULL default '0000-00-00 00:00:00', + expires datetime DEFAULT NOT, data text NOT NULL ); CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key); -CREATE INDEX ix_cache_created ON cache(created); +CREATE INDEX ix_cache_expires ON cache(expires); -- -- Table structure for table cache_shared @@ -139,11 +140,12 @@ CREATE INDEX ix_cache_created ON cache(created); CREATE TABLE cache_shared ( cache_key varchar(255) NOT NULL, created datetime NOT NULL default '0000-00-00 00:00:00', + expires datetime DEFAULT NULL, data text NOT NULL ); CREATE INDEX ix_cache_shared_cache_key ON cache_shared(cache_key); -CREATE INDEX ix_cache_shared_created ON cache_shared(created); +CREATE INDEX ix_cache_shared_expires ON cache_shared(expires); -- -- Table structure for table cache_index @@ -152,13 +154,13 @@ CREATE INDEX ix_cache_shared_created ON cache_shared(created); CREATE TABLE cache_index ( user_id integer NOT NULL, mailbox varchar(255) NOT NULL, - changed datetime NOT NULL default '0000-00-00 00:00:00', + expires datetime DEFAULT NULL, valid smallint NOT NULL DEFAULT '0', data text NOT NULL, PRIMARY KEY (user_id, mailbox) ); -CREATE INDEX ix_cache_index_changed ON cache_index (changed); +CREATE INDEX ix_cache_index_expires ON cache_index (expires); -- -- Table structure for table cache_thread @@ -167,12 +169,12 @@ CREATE INDEX ix_cache_index_changed ON cache_index (changed); CREATE TABLE cache_thread ( user_id integer NOT NULL, mailbox varchar(255) NOT NULL, - changed datetime NOT NULL default '0000-00-00 00:00:00', + expires datetime DEFAULT NULL, data text NOT NULL, PRIMARY KEY (user_id, mailbox) ); -CREATE INDEX ix_cache_thread_changed ON cache_thread (changed); +CREATE INDEX ix_cache_thread_expires ON cache_thread (expires); -- -- Table structure for table cache_messages @@ -182,13 +184,13 @@ CREATE TABLE cache_messages ( user_id integer NOT NULL, mailbox varchar(255) NOT NULL, uid integer NOT NULL, - changed datetime NOT NULL default '0000-00-00 00:00:00', + expires datetime DEFAULT NULL, data text NOT NULL, flags integer NOT NULL DEFAULT '0', PRIMARY KEY (user_id, mailbox, uid) ); -CREATE INDEX ix_cache_messages_changed ON cache_messages (changed); +CREATE INDEX ix_cache_messages_expires ON cache_messages (expires); -- -- Table structure for table system @@ -199,4 +201,4 @@ CREATE TABLE system ( value text NOT NULL ); -INSERT INTO system (name, value) VALUES ('roundcube-version', '2013052500'); +INSERT INTO system (name, value) VALUES ('roundcube-version', '2013061000'); diff --git a/SQL/sqlite/2013061000.sql b/SQL/sqlite/2013061000.sql new file mode 100644 index 000000000..3c6b43eac --- /dev/null +++ b/SQL/sqlite/2013061000.sql @@ -0,0 +1,48 @@ +DROP TABLE cache_index; +DROP TABLE cache_thread; +DROP TABLE cache_messages; + +ALTER TABLE cache ADD expires datetime DEFAULT NULL; +DROP INDEX ix_cache_created; + +ALTER TABLE cache_shared ADD expires datetime DEFAULT NULL; +DROP INDEX ix_cache_shared_created; + +UPDATE cache SET expires = datetime(created, '+604800 seconds'); +UPDATE cache_shared SET expires = datetime(created, '+604800 seconds'); + +CREATE INDEX ix_cache_expires ON cache(expires); +CREATE INDEX ix_cache_shared_expires ON cache_shared(expires); + +CREATE TABLE cache_index ( + user_id integer NOT NULL, + mailbox varchar(255) NOT NULL, + expires datetime DEFAULT NULL, + valid smallint NOT NULL DEFAULT '0', + data text NOT NULL, + PRIMARY KEY (user_id, mailbox) +); + +CREATE INDEX ix_cache_index_expires ON cache_index (expires); + +CREATE TABLE cache_thread ( + user_id integer NOT NULL, + mailbox varchar(255) NOT NULL, + expires datetime DEFAULT NULL, + data text NOT NULL, + PRIMARY KEY (user_id, mailbox) +); + +CREATE INDEX ix_cache_thread_expires ON cache_thread (expires); + +CREATE TABLE cache_messages ( + user_id integer NOT NULL, + mailbox varchar(255) NOT NULL, + uid integer NOT NULL, + expires datetime DEFAULT NULL, + data text NOT NULL, + flags integer NOT NULL DEFAULT '0', + PRIMARY KEY (user_id, mailbox, uid) +); + +CREATE INDEX ix_cache_messages_expires ON cache_messages (expires); 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