summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--CHANGELOG1
-rw-r--r--SQL/mssql.initial.sql47
-rw-r--r--SQL/mssql/2013061000.sql44
-rw-r--r--SQL/mysql.initial.sql24
-rw-r--r--SQL/mysql/2013061000.sql24
-rw-r--r--SQL/postgres.initial.sql30
-rw-r--r--SQL/postgres/2013061000.sql24
-rw-r--r--SQL/sqlite.initial.sql20
-rw-r--r--SQL/sqlite/2013061000.sql48
-rw-r--r--program/lib/Roundcube/rcube.php12
-rw-r--r--program/lib/Roundcube/rcube_cache.php43
-rw-r--r--program/lib/Roundcube/rcube_cache_shared.php25
-rw-r--r--program/lib/Roundcube/rcube_db.php2
-rw-r--r--program/lib/Roundcube/rcube_imap.php32
-rw-r--r--program/lib/Roundcube/rcube_imap_cache.php74
-rw-r--r--program/lib/Roundcube/rcube_storage.php2
16 files changed, 319 insertions, 133 deletions
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,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