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

8 files changed, 206 insertions, 55 deletions

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);