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 --- 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 +++++++++++++++++++++++++++++++++++++++++++++ 8 files changed, 206 insertions(+), 55 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 (limited to 'SQL') 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); -- cgit v1.2.3