From 50abd557184326f173357b50e7593dc3f452be91 Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Sat, 25 May 2013 15:37:46 +0200 Subject: Added shared (cross-user) cache --- SQL/mssql.initial.sql | 19 +++++++++++++- SQL/mssql/2013052500.sql | 17 ++++++++++++ SQL/mysql.initial.sql | 13 ++++++++- SQL/mysql/2013052500.sql | 7 +++++ SQL/postgres.initial.sql | 16 +++++++++++- SQL/postgres/2013052500.sql | 8 ++++++ SQL/sqlite.initial.sql | 64 +++++++++++++++++++-------------------------- SQL/sqlite/2013052500.sql | 8 ++++++ 8 files changed, 112 insertions(+), 40 deletions(-) create mode 100644 SQL/mssql/2013052500.sql create mode 100644 SQL/mysql/2013052500.sql create mode 100644 SQL/postgres/2013052500.sql create mode 100644 SQL/sqlite/2013052500.sql (limited to 'SQL') diff --git a/SQL/mssql.initial.sql b/SQL/mssql.initial.sql index d43676ff9..23e7c7d7f 100644 --- a/SQL/mssql.initial.sql +++ b/SQL/mssql.initial.sql @@ -6,6 +6,13 @@ CREATE TABLE [dbo].[cache] ( ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO +CREATE TABLE [dbo].[cache_shared] ( + [cache_key] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL , + [created] [datetime] NOT NULL , + [data] [text] COLLATE Latin1_General_CI_AI NOT NULL +) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] +GO + CREATE TABLE [dbo].[cache_index] ( [user_id] [int] NOT NULL , [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , @@ -213,6 +220,16 @@ GO CREATE INDEX [IX_cache_created] ON [dbo].[cache]([created]) ON [PRIMARY] GO +ALTER TABLE [dbo].[cache_shared] ADD + CONSTRAINT [DF_cache_shared_created] DEFAULT (getdate()) FOR [created] +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] +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] @@ -371,6 +388,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', '2013042700') +INSERT INTO [dbo].[system] ([name], [value]) VALUES ('roundcube-version', '2013052500') GO \ No newline at end of file diff --git a/SQL/mssql/2013052500.sql b/SQL/mssql/2013052500.sql new file mode 100644 index 000000000..6a7e31d46 --- /dev/null +++ b/SQL/mssql/2013052500.sql @@ -0,0 +1,17 @@ +CREATE TABLE [dbo].[cache_shared] ( + [cache_key] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL , + [created] [datetime] NOT NULL , + [data] [text] COLLATE Latin1_General_CI_AI NOT NULL +) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] +GO + +ALTER TABLE [dbo].[cache_shared] ADD + CONSTRAINT [DF_cache_shared_created] DEFAULT (getdate()) FOR [created] +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] +GO + diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql index 5a86dd8f8..75268ca03 100644 --- a/SQL/mysql.initial.sql +++ b/SQL/mysql.initial.sql @@ -45,6 +45,17 @@ CREATE TABLE `cache` ( ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; +-- Table structure for table `cache_shared` + +CREATE TABLE `cache_shared` ( + `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', + `data` longtext NOT NULL, + INDEX `created_index` (`created`), + INDEX `cache_key_index` (`cache_key`) +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + + -- Table structure for table `cache_index` CREATE TABLE `cache_index` ( @@ -196,4 +207,4 @@ CREATE TABLE `system` ( /*!40014 SET FOREIGN_KEY_CHECKS=1 */; -INSERT INTO system (name, value) VALUES ('roundcube-version', '2013042700'); +INSERT INTO system (name, value) VALUES ('roundcube-version', '2013052500'); diff --git a/SQL/mysql/2013052500.sql b/SQL/mysql/2013052500.sql new file mode 100644 index 000000000..b5f72b8ab --- /dev/null +++ b/SQL/mysql/2013052500.sql @@ -0,0 +1,7 @@ +CREATE TABLE `cache_shared` ( + `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', + `data` longtext NOT NULL, + INDEX `created_index` (`created`), + INDEX `cache_key_index` (`cache_key`) +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql index f3b368f7d..c54f05f0b 100644 --- a/SQL/postgres.initial.sql +++ b/SQL/postgres.initial.sql @@ -173,6 +173,20 @@ CREATE TABLE "cache" ( CREATE INDEX cache_user_id_idx ON "cache" (user_id, cache_key); CREATE INDEX cache_created_idx ON "cache" (created); +-- +-- Table "cache_shared" +-- Name: cache_shared; Type: TABLE; Schema: public; Owner: postgres +-- + +CREATE TABLE "cache_shared" ( + cache_key varchar(255) NOT NULL, + created timestamp with time zone DEFAULT now() NOT 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); + -- -- Table "cache_index" -- Name: cache_index; Type: TABLE; Schema: public; Owner: postgres @@ -274,4 +288,4 @@ CREATE TABLE "system" ( value text ); -INSERT INTO system (name, value) VALUES ('roundcube-version', '2013042700'); +INSERT INTO system (name, value) VALUES ('roundcube-version', '2013052500'); diff --git a/SQL/postgres/2013052500.sql b/SQL/postgres/2013052500.sql new file mode 100644 index 000000000..471e57176 --- /dev/null +++ b/SQL/postgres/2013052500.sql @@ -0,0 +1,8 @@ +CREATE TABLE "cache_shared" ( + cache_key varchar(255) NOT NULL, + created timestamp with time zone DEFAULT now() NOT 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); diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql index d9bafb7e0..1b5b62b31 100644 --- a/SQL/sqlite.initial.sql +++ b/SQL/sqlite.initial.sql @@ -1,22 +1,5 @@ -- Roundcube Webmail initial database structure --- --- Table structure for table cache --- - -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', - 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); - - --- -------------------------------------------------------- - -- -- Table structure for table contacts and related -- @@ -57,9 +40,6 @@ CREATE TABLE contactgroupmembers ( CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id); - --- -------------------------------------------------------- - -- -- Table structure for table identities -- @@ -82,9 +62,6 @@ CREATE TABLE identities ( CREATE INDEX ix_identities_user_id ON identities(user_id, del); CREATE INDEX ix_identities_email ON identities(email, del); - --- -------------------------------------------------------- - -- -- Table structure for table users -- @@ -101,8 +78,6 @@ CREATE TABLE users ( CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host); --- -------------------------------------------------------- - -- -- Table structure for table session -- @@ -117,8 +92,6 @@ CREATE TABLE session ( CREATE INDEX ix_session_changed ON session (changed); --- -------------------------------------------------------- - -- -- Table structure for table dictionary -- @@ -131,8 +104,6 @@ CREATE TABLE dictionary ( CREATE UNIQUE INDEX ix_dictionary_user_language ON dictionary (user_id, "language"); --- -------------------------------------------------------- - -- -- Table structure for table searches -- @@ -147,7 +118,32 @@ CREATE TABLE searches ( CREATE UNIQUE INDEX ix_searches_user_type_name ON searches (user_id, type, name); --- -------------------------------------------------------- +-- +-- Table structure for table cache +-- + +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', + 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); + +-- +-- Table structure for table cache_shared +-- + +CREATE TABLE cache_shared ( + cache_key varchar(255) NOT NULL, + created datetime NOT NULL default '0000-00-00 00:00:00', + 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); -- -- Table structure for table cache_index @@ -164,8 +160,6 @@ CREATE TABLE cache_index ( CREATE INDEX ix_cache_index_changed ON cache_index (changed); --- -------------------------------------------------------- - -- -- Table structure for table cache_thread -- @@ -180,8 +174,6 @@ CREATE TABLE cache_thread ( CREATE INDEX ix_cache_thread_changed ON cache_thread (changed); --- -------------------------------------------------------- - -- -- Table structure for table cache_messages -- @@ -198,8 +190,6 @@ CREATE TABLE cache_messages ( CREATE INDEX ix_cache_messages_changed ON cache_messages (changed); --- -------------------------------------------------------- - -- -- Table structure for table system -- @@ -209,4 +199,4 @@ CREATE TABLE system ( value text NOT NULL ); -INSERT INTO system (name, value) VALUES ('roundcube-version', '2013042700'); +INSERT INTO system (name, value) VALUES ('roundcube-version', '2013052500'); diff --git a/SQL/sqlite/2013052500.sql b/SQL/sqlite/2013052500.sql new file mode 100644 index 000000000..19ae1b110 --- /dev/null +++ b/SQL/sqlite/2013052500.sql @@ -0,0 +1,8 @@ +CREATE TABLE cache_shared ( + cache_key varchar(255) NOT NULL, + created datetime NOT NULL default '0000-00-00 00:00:00', + 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); -- cgit v1.2.3