From 80152b333ca5d856dcf09f5ca10a9ffd80ba117f Mon Sep 17 00:00:00 2001 From: alecpl Date: Wed, 7 Sep 2011 11:07:03 +0000 Subject: - Rewritten messages caching (merged devel-mcache branch): Indexes are stored in a separate table, so there's no need to store all messages in a folder Added threads data caching Flags are stored separately, so flag change doesn't cause DELETE+INSERT, just UPDATE - Partial QRESYNC support - Improved FETCH response handling - Improvements in response tokenization method --- SQL/mssql.initial.sql | 144 +++++++++++++++++++++++++++++------------------ SQL/mssql.upgrade.sql | 96 +++++++++++++++++++++++++++++++ SQL/mysql.initial.sql | 76 ++++++++++++++++--------- SQL/mysql.update.sql | 42 ++++++++++++++ SQL/postgres.initial.sql | 76 +++++++++++++++---------- SQL/postgres.update.sql | 43 ++++++++++++++ SQL/sqlite.initial.sql | 87 ++++++++++++++++++---------- SQL/sqlite.update.sql | 41 +++++++++++++- 8 files changed, 463 insertions(+), 142 deletions(-) (limited to 'SQL') diff --git a/SQL/mssql.initial.sql b/SQL/mssql.initial.sql index 8e103eb55..eb5f20bcc 100644 --- a/SQL/mssql.initial.sql +++ b/SQL/mssql.initial.sql @@ -7,6 +7,37 @@ CREATE TABLE [dbo].[cache] ( ) 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 , + [changed] [datetime] NOT NULL , + [data] [text] COLLATE Latin1_General_CI_AI NOT NULL +) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] +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 , + [data] [text] COLLATE Latin1_General_CI_AI NOT NULL +) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] +GO + +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 , + [data] [text] COLLATE Latin1_General_CI_AI NOT NULL + [seen] [char](1) NOT NULL , + [deleted] [char](1) NOT NULL , + [answered] [char](1) NOT NULL , + [forwarded] [char](1) NOT NULL , + [flagged] [char](1) NOT NULL , + [mdnsent] [char](1) NOT NULL , +) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] +GO + CREATE TABLE [dbo].[contacts] ( [contact_id] [int] IDENTITY (1, 1) NOT NULL , [user_id] [int] NOT NULL , @@ -53,25 +84,6 @@ CREATE TABLE [dbo].[identities] ( ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO -CREATE TABLE [dbo].[messages] ( - [message_id] [int] IDENTITY (1, 1) NOT NULL , - [user_id] [int] NOT NULL , - [del] [tinyint] NOT NULL , - [cache_key] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , - [created] [datetime] NOT NULL , - [idx] [int] NOT NULL , - [uid] [int] NOT NULL , - [subject] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL , - [from] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL , - [to] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL , - [cc] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL , - [date] [datetime] NOT NULL , - [size] [int] NOT NULL , - [headers] [text] COLLATE Latin1_General_CI_AI NOT NULL , - [structure] [text] COLLATE Latin1_General_CI_AI NULL -) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] -GO - CREATE TABLE [dbo].[session] ( [sess_id] [varchar] (32) COLLATE Latin1_General_CI_AI NOT NULL , [created] [datetime] NOT NULL , @@ -116,6 +128,27 @@ ALTER TABLE [dbo].[cache] WITH NOCHECK ADD ) ON [PRIMARY] GO +ALTER TABLE [dbo].[cache_index] WITH NOCHECK ADD + PRIMARY KEY CLUSTERED + ( + [user_id],[mailbox] + ) ON [PRIMARY] +GO + +ALTER TABLE [dbo].[cache_thread] WITH NOCHECK ADD + PRIMARY KEY CLUSTERED + ( + [user_id],[mailbox] + ) ON [PRIMARY] +GO + +ALTER TABLE [dbo].[cache_messages] WITH NOCHECK ADD + PRIMARY KEY CLUSTERED + ( + [user_id],[mailbox],[uid] + ) ON [PRIMARY] +GO + ALTER TABLE [dbo].[contacts] WITH NOCHECK ADD CONSTRAINT [PK_contacts_contact_id] PRIMARY KEY CLUSTERED ( @@ -144,13 +177,6 @@ ALTER TABLE [dbo].[identities] WITH NOCHECK ADD ) ON [PRIMARY] GO -ALTER TABLE [dbo].[messages] WITH NOCHECK ADD - PRIMARY KEY CLUSTERED - ( - [message_id] - ) ON [PRIMARY] -GO - ALTER TABLE [dbo].[session] WITH NOCHECK ADD CONSTRAINT [PK_session_sess_id] PRIMARY KEY CLUSTERED ( @@ -187,6 +213,33 @@ GO CREATE INDEX [IX_cache_created] ON [dbo].[cache]([created]) ON [PRIMARY] GO +ALTER TABLE [dbo].[cache_index] ADD + CONSTRAINT [DF_cache_index_changed] DEFAULT (getdate()) FOR [changed] +GO + +CREATE INDEX [IX_cache_index_user_id] ON [dbo].[cache_index]([user_id]) ON [PRIMARY] +GO + +ALTER TABLE [dbo].[cache_thread] ADD + CONSTRAINT [DF_cache_thread_changed] DEFAULT (getdate()) FOR [changed] +GO + +CREATE INDEX [IX_cache_thread_user_id] ON [dbo].[cache_thread]([user_id]) ON [PRIMARY] +GO + +ALTER TABLE [dbo].[cache_messages] ADD + CONSTRAINT [DF_cache_messages_changed] DEFAULT (getdate()) FOR [changed] + CONSTRAINT [DF_cache_messages_seen] DEFAULT (0) FOR [seen], + CONSTRAINT [DF_cache_messages_deleted] DEFAULT (0) FOR [deleted], + CONSTRAINT [DF_cache_messages_answered] DEFAULT (0) FOR [answered], + CONSTRAINT [DF_cache_messages_forwarded] DEFAULT (0) FOR [forwarded], + CONSTRAINT [DF_cache_messages_flagged] DEFAULT (0) FOR [flagged], + CONSTRAINT [DF_cache_messages_mdnsent] DEFAULT (0) FOR [mdnsent], +GO + +CREATE INDEX [IX_cache_messages_user_id] ON [dbo].[cache_messages]([user_id]) ON [PRIMARY] +GO + ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [DF_contacts_user_id] DEFAULT (0) FOR [user_id], CONSTRAINT [DF_contacts_changed] DEFAULT (getdate()) FOR [changed], @@ -238,33 +291,6 @@ GO CREATE INDEX [IX_identities_user_id] ON [dbo].[identities]([user_id]) ON [PRIMARY] GO -ALTER TABLE [dbo].[messages] ADD - CONSTRAINT [DF_messages_user_id] DEFAULT (0) FOR [user_id], - CONSTRAINT [DF_messages_del] DEFAULT (0) FOR [del], - CONSTRAINT [DF_messages_cache_key] DEFAULT ('') FOR [cache_key], - CONSTRAINT [DF_messages_created] DEFAULT (getdate()) FOR [created], - CONSTRAINT [DF_messages_idx] DEFAULT (0) FOR [idx], - CONSTRAINT [DF_messages_uid] DEFAULT (0) FOR [uid], - CONSTRAINT [DF_messages_subject] DEFAULT ('') FOR [subject], - CONSTRAINT [DF_messages_from] DEFAULT ('') FOR [from], - CONSTRAINT [DF_messages_to] DEFAULT ('') FOR [to], - CONSTRAINT [DF_messages_cc] DEFAULT ('') FOR [cc], - CONSTRAINT [DF_messages_date] DEFAULT (getdate()) FOR [date], - CONSTRAINT [DF_messages_size] DEFAULT (0) FOR [size] -GO - -CREATE INDEX [IX_messages_user_id] ON [dbo].[messages]([user_id]) ON [PRIMARY] -GO - -CREATE INDEX [IX_messages_cache_key] ON [dbo].[messages]([cache_key]) ON [PRIMARY] -GO - -CREATE INDEX [IX_messages_uid] ON [dbo].[messages]([uid]) ON [PRIMARY] -GO - -CREATE INDEX [IX_messages_created] ON [dbo].[messages]([created]) ON [PRIMARY] -GO - ALTER TABLE [dbo].[session] ADD CONSTRAINT [DF_session_sess_id] DEFAULT ('') FOR [sess_id], CONSTRAINT [DF_session_created] DEFAULT (getdate()) FOR [created], @@ -318,7 +344,17 @@ ALTER TABLE [dbo].[cache] ADD CONSTRAINT [FK_cache_user_id] ON DELETE CASCADE ON UPDATE CASCADE GO -ALTER TABLE [dbo].[messages] ADD CONSTRAINT [FK_messages_user_id] +ALTER TABLE [dbo].[cache_index] ADD CONSTRAINT [FK_cache_index_user_id] + FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id]) + ON DELETE CASCADE ON UPDATE CASCADE +GO + +ALTER TABLE [dbo].[cache_thread] ADD CONSTRAINT [FK_cache_thread_user_id] + FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id]) + ON DELETE CASCADE ON UPDATE CASCADE +GO + +ALTER TABLE [dbo].[cache_messages] ADD CONSTRAINT [FK_cache_messages_user_id] FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id]) ON DELETE CASCADE ON UPDATE CASCADE GO diff --git a/SQL/mssql.upgrade.sql b/SQL/mssql.upgrade.sql index 258b1d78a..29440f950 100644 --- a/SQL/mssql.upgrade.sql +++ b/SQL/mssql.upgrade.sql @@ -151,3 +151,99 @@ ALTER TABLE [dbo].[searches] ADD CONSTRAINT [FK_searches_user_id] ON DELETE CASCADE ON UPDATE CASCADE GO +DROP TABLE [dbo].[messages] +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 , + [data] [text] COLLATE Latin1_General_CI_AI NOT NULL +) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] +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 , + [data] [text] COLLATE Latin1_General_CI_AI NOT NULL +) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] +GO + +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 , + [data] [text] COLLATE Latin1_General_CI_AI NOT NULL + [seen] [char](1) NOT NULL , + [deleted] [char](1) NOT NULL , + [answered] [char](1) NOT NULL , + [forwarded] [char](1) NOT NULL , + [flagged] [char](1) NOT NULL , + [mdnsent] [char](1) NOT NULL , +) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] +GO + +ALTER TABLE [dbo].[cache_index] WITH NOCHECK ADD + PRIMARY KEY CLUSTERED + ( + [user_id],[mailbox] + ) ON [PRIMARY] +GO + +ALTER TABLE [dbo].[cache_thread] WITH NOCHECK ADD + PRIMARY KEY CLUSTERED + ( + [user_id],[mailbox] + ) ON [PRIMARY] +GO + +ALTER TABLE [dbo].[cache_messages] WITH NOCHECK ADD + PRIMARY KEY CLUSTERED + ( + [user_id],[mailbox],[uid] + ) ON [PRIMARY] +GO + +ALTER TABLE [dbo].[cache_index] ADD + CONSTRAINT [DF_cache_index_changed] DEFAULT (getdate()) FOR [changed] +GO + +CREATE INDEX [IX_cache_index_user_id] ON [dbo].[cache_index]([user_id]) ON [PRIMARY] +GO + +ALTER TABLE [dbo].[cache_thread] ADD + CONSTRAINT [DF_cache_thread_changed] DEFAULT (getdate()) FOR [changed] +GO + +CREATE INDEX [IX_cache_thread_user_id] ON [dbo].[cache_thread]([user_id]) ON [PRIMARY] +GO + +ALTER TABLE [dbo].[cache_messages] ADD + CONSTRAINT [DF_cache_messages_changed] DEFAULT (getdate()) FOR [changed] + CONSTRAINT [DF_cache_messages_seen] DEFAULT (0) FOR [seen], + CONSTRAINT [DF_cache_messages_deleted] DEFAULT (0) FOR [deleted], + CONSTRAINT [DF_cache_messages_answered] DEFAULT (0) FOR [answered], + CONSTRAINT [DF_cache_messages_forwarded] DEFAULT (0) FOR [forwarded], + CONSTRAINT [DF_cache_messages_flagged] DEFAULT (0) FOR [flagged], + CONSTRAINT [DF_cache_messages_mdnsent] DEFAULT (0) FOR [mdnsent], +GO + +CREATE INDEX [IX_cache_messages_user_id] ON [dbo].[cache_messages]([user_id]) ON [PRIMARY] +GO + +ALTER TABLE [dbo].[cache_index] ADD CONSTRAINT [FK_cache_index_user_id] + FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id]) + ON DELETE CASCADE ON UPDATE CASCADE +GO + +ALTER TABLE [dbo].[cache_thread] ADD CONSTRAINT [FK_cache_thread_user_id] + FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id]) + ON DELETE CASCADE ON UPDATE CASCADE +GO + +ALTER TABLE [dbo].[cache_messages] ADD CONSTRAINT [FK_cache_messages_user_id] + FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id]) + ON DELETE CASCADE ON UPDATE CASCADE +GO + \ No newline at end of file diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql index a50e28c35..32b3991e0 100644 --- a/SQL/mysql.initial.sql +++ b/SQL/mysql.initial.sql @@ -33,33 +33,6 @@ CREATE TABLE `users` ( ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; --- Table structure for table `messages` - -CREATE TABLE `messages` ( - `message_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, - `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', - `del` tinyint(1) NOT NULL DEFAULT '0', - `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', - `idx` int(11) UNSIGNED NOT NULL DEFAULT '0', - `uid` int(11) UNSIGNED NOT NULL DEFAULT '0', - `subject` varchar(255) NOT NULL, - `from` varchar(255) NOT NULL, - `to` varchar(255) NOT NULL, - `cc` varchar(255) NOT NULL, - `date` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', - `size` int(11) UNSIGNED NOT NULL DEFAULT '0', - `headers` text NOT NULL, - `structure` text, - PRIMARY KEY(`message_id`), - CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, - INDEX `created_index` (`created`), - INDEX `index_index` (`user_id`, `cache_key`, `idx`), - UNIQUE `uniqueness` (`user_id`, `cache_key`, `uid`) -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; - - -- Table structure for table `cache` CREATE TABLE `cache` ( @@ -76,6 +49,55 @@ CREATE TABLE `cache` ( ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; +-- Table structure for table `cache_index` + +CREATE TABLE `cache_index` ( + `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', + `mailbox` varchar(255) BINARY NOT NULL, + `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', + `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`), + PRIMARY KEY (`user_id`, `mailbox`) +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + + +-- Table structure for table `cache_thread` + +CREATE TABLE `cache_thread` ( + `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', + `mailbox` varchar(255) BINARY NOT NULL, + `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', + `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`), + PRIMARY KEY (`user_id`, `mailbox`) +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + + +-- Table structure for table `cache_messages` + +CREATE TABLE `cache_messages` ( + `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', + `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', + `data` longtext NOT NULL, + `seen` tinyint(1) NOT NULL DEFAULT '0', + `deleted` tinyint(1) NOT NULL DEFAULT '0', + `answered` tinyint(1) NOT NULL DEFAULT '0', + `forwarded` tinyint(1) NOT NULL DEFAULT '0', + `flagged` tinyint(1) NOT NULL DEFAULT '0', + `mdnsent` tinyint(1) 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`), + PRIMARY KEY (`user_id`, `mailbox`, `uid`) +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + + -- Table structure for table `contacts` CREATE TABLE `contacts` ( diff --git a/SQL/mysql.update.sql b/SQL/mysql.update.sql index 7f8ce6154..fee18d640 100644 --- a/SQL/mysql.update.sql +++ b/SQL/mysql.update.sql @@ -170,3 +170,45 @@ CREATE TABLE `searches` ( REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE `uniqueness` (`user_id`, `type`, `name`) ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + +DROP TABLE `messages`; + +CREATE TABLE `cache_index` ( + `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', + `mailbox` varchar(255) BINARY NOT NULL, + `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', + `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`), + PRIMARY KEY (`user_id`, `mailbox`) +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + +CREATE TABLE `cache_thread` ( + `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', + `mailbox` varchar(255) BINARY NOT NULL, + `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', + `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`), + PRIMARY KEY (`user_id`, `mailbox`) +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + +CREATE TABLE `cache_messages` ( + `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', + `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', + `data` longtext NOT NULL, + `seen` tinyint(1) NOT NULL DEFAULT '0', + `deleted` tinyint(1) NOT NULL DEFAULT '0', + `answered` tinyint(1) NOT NULL DEFAULT '0', + `forwarded` tinyint(1) NOT NULL DEFAULT '0', + `flagged` tinyint(1) NOT NULL DEFAULT '0', + `mdnsent` tinyint(1) 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`), + PRIMARY KEY (`user_id`, `mailbox`, `uid`) +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql index 01221c4e3..a1864c6c9 100644 --- a/SQL/postgres.initial.sql +++ b/SQL/postgres.initial.sql @@ -67,7 +67,7 @@ CREATE SEQUENCE identity_ids CREATE TABLE identities ( identity_id integer DEFAULT nextval('identity_ids'::text) PRIMARY KEY, 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, changed timestamp with time zone DEFAULT now() NOT NULL, del smallint DEFAULT 0 NOT NULL, standard smallint DEFAULT 0 NOT NULL, @@ -178,7 +178,7 @@ CREATE SEQUENCE cache_ids CREATE TABLE "cache" ( cache_id integer DEFAULT nextval('cache_ids'::text) PRIMARY KEY, 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, data text NOT NULL @@ -188,43 +188,59 @@ CREATE INDEX cache_user_id_idx ON "cache" (user_id, cache_key); CREATE INDEX cache_created_idx ON "cache" (created); -- --- Sequence "message_ids" --- Name: message_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- Table "cache_index" +-- Name: cache_index; Type: TABLE; Schema: public; Owner: postgres -- -CREATE SEQUENCE message_ids - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +CREATE TABLE cache_index ( + user_id integer NOT NULL + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + mailbox varchar(255) NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + data text NOT NULL, + PRIMARY KEY (user_id, mailbox) +); + +CREATE INDEX cache_index_changed_idx ON cache_index (changed); -- --- Table "messages" --- Name: messages; Type: TABLE; Schema: public; Owner: postgres +-- Table "cache_thread" +-- Name: cache_thread; Type: TABLE; Schema: public; Owner: postgres -- -CREATE TABLE messages ( - message_id integer DEFAULT nextval('message_ids'::text) PRIMARY KEY, +CREATE TABLE cache_thread ( user_id integer NOT NULL - REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, - del smallint DEFAULT 0 NOT NULL, - cache_key varchar(128) DEFAULT '' NOT NULL, - created timestamp with time zone DEFAULT now() NOT NULL, - idx integer DEFAULT 0 NOT NULL, - uid integer DEFAULT 0 NOT NULL, - subject varchar(128) DEFAULT '' NOT NULL, - "from" varchar(128) DEFAULT '' NOT NULL, - "to" varchar(128) DEFAULT '' NOT NULL, - cc varchar(128) DEFAULT '' NOT NULL, - date timestamp with time zone NOT NULL, - size integer DEFAULT 0 NOT NULL, - headers text NOT NULL, - structure text, - CONSTRAINT messages_user_id_key UNIQUE (user_id, cache_key, uid) + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + mailbox varchar(255) NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + data text NOT NULL, + PRIMARY KEY (user_id, mailbox) +); + +CREATE INDEX cache_thread_changed_idx ON cache_thread (changed); + +-- +-- Table "cache_messages" +-- Name: cache_messages; Type: TABLE; Schema: public; Owner: postgres +-- + +CREATE TABLE cache_messages ( + user_id integer NOT NULL + 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, + data text NOT NULL, + seen smallint NOT NULL DEFAULT 0, + deleted smallint NOT NULL DEFAULT 0, + answered smallint NOT NULL DEFAULT 0, + forwarded smallint NOT NULL DEFAULT 0, + flagged smallint NOT NULL DEFAULT 0, + mdnsent smallint NOT NULL DEFAULT 0, + PRIMARY KEY (user_id, mailbox, uid) ); -CREATE INDEX messages_index_idx ON messages (user_id, cache_key, idx); -CREATE INDEX messages_created_idx ON messages (created); +CREATE INDEX cache_messages_changed_idx ON cache_messages (changed); -- -- Table "dictionary" diff --git a/SQL/postgres.update.sql b/SQL/postgres.update.sql index e316ff540..4bd0400c9 100644 --- a/SQL/postgres.update.sql +++ b/SQL/postgres.update.sql @@ -126,3 +126,46 @@ CREATE TABLE searches ( data text NOT NULL, CONSTRAINT searches_user_id_key UNIQUE (user_id, "type", name) ); + +DROP SEQUENCE messages_ids; +DROP TABLE messages; + +CREATE TABLE cache_index ( + user_id integer NOT NULL + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + mailbox varchar(255) NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + data text NOT NULL, + PRIMARY KEY (user_id, mailbox) +); + +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, + mailbox varchar(255) NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + data text NOT NULL, + PRIMARY KEY (user_id, mailbox) +); + +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, + mailbox varchar(255) NOT NULL, + uid integer NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + data text NOT NULL, + seen smallint NOT NULL DEFAULT 0, + deleted smallint NOT NULL DEFAULT 0, + answered smallint NOT NULL DEFAULT 0, + forwarded smallint NOT NULL DEFAULT 0, + flagged smallint NOT NULL DEFAULT 0, + mdnsent smallint NOT NULL DEFAULT 0, + PRIMARY KEY (user_id, mailbox, uid) +); + +CREATE INDEX cache_messages_changed_idx ON cache_messages (changed); diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql index 46ee5301b..7ec82015d 100644 --- a/SQL/sqlite.initial.sql +++ b/SQL/sqlite.initial.sql @@ -1,7 +1,7 @@ -- Roundcube Webmail initial database structure -- --- Table structure for table `cache` +-- Table structure for table cache -- CREATE TABLE cache ( @@ -9,7 +9,7 @@ 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 longtext NOT NULL + data text NOT NULL ); CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key); @@ -121,34 +121,6 @@ CREATE INDEX ix_session_changed ON session (changed); -- -------------------------------------------------------- --- --- Table structure for table messages --- - -CREATE TABLE messages ( - message_id integer NOT NULL PRIMARY KEY, - user_id integer NOT NULL default '0', - del tinyint NOT NULL default '0', - cache_key varchar(128) NOT NULL default '', - created datetime NOT NULL default '0000-00-00 00:00:00', - idx integer NOT NULL default '0', - uid integer NOT NULL default '0', - subject varchar(255) NOT NULL default '', - "from" varchar(255) NOT NULL default '', - "to" varchar(255) NOT NULL default '', - "cc" varchar(255) NOT NULL default '', - "date" datetime NOT NULL default '0000-00-00 00:00:00', - size integer NOT NULL default '0', - headers text NOT NULL, - structure text -); - -CREATE UNIQUE INDEX ix_messages_user_cache_uid ON messages (user_id,cache_key,uid); -CREATE INDEX ix_messages_index ON messages (user_id,cache_key,idx); -CREATE INDEX ix_messages_created ON messages (created); - --- -------------------------------------------------------- - -- -- Table structure for table dictionary -- @@ -176,3 +148,58 @@ CREATE TABLE searches ( ); CREATE UNIQUE INDEX ix_searches_user_type_name (user_id, type, name); + +-- -------------------------------------------------------- + +-- +-- Table structure for table cache_index +-- + +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', + data text NOT NULL, + PRIMARY KEY (user_id, mailbox) +); + +CREATE INDEX ix_cache_index_changed ON cache_index (changed); + +-- -------------------------------------------------------- + +-- +-- Table structure for table cache_thread +-- + +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', + data text NOT NULL, + PRIMARY KEY (user_id, mailbox) +); + +CREATE INDEX ix_cache_thread_changed ON cache_thread (changed); + +-- -------------------------------------------------------- + +-- +-- Table structure for table cache_messages +-- + +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', + data text NOT NULL, + seen smallint NOT NULL DEFAULT '0', + deleted smallint NOT NULL DEFAULT '0', + answered smallint NOT NULL DEFAULT '0', + forwarded smallint NOT NULL DEFAULT '0', + flagged smallint NOT NULL DEFAULT '0', + mdnsent smallint NOT NULL DEFAULT '0', + PRIMARY KEY (user_id, mailbox, uid) +); + +CREATE INDEX ix_cache_messages_changed ON cache_messages (changed); diff --git a/SQL/sqlite.update.sql b/SQL/sqlite.update.sql index 41ab0200d..378072c03 100644 --- a/SQL/sqlite.update.sql +++ b/SQL/sqlite.update.sql @@ -223,11 +223,11 @@ INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, CREATE INDEX ix_contacts_user_id ON contacts(user_id, email); DROP TABLE contacts_tmp; + DELETE FROM messages; DELETE FROM cache; CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id); - -- Updates from version 0.6-stable CREATE TABLE dictionary ( @@ -247,3 +247,42 @@ CREATE TABLE searches ( ); CREATE UNIQUE INDEX ix_searches_user_type_name (user_id, type, name); + +DROP TABLE messages; + +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', + data text NOT NULL, + PRIMARY KEY (user_id, mailbox) +); + +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', + data text NOT NULL, + PRIMARY KEY (user_id, mailbox) +); + +CREATE INDEX ix_cache_thread_changed ON cache_thread (changed); + +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', + data text NOT NULL, + seen smallint NOT NULL DEFAULT '0', + deleted smallint NOT NULL DEFAULT '0', + answered smallint NOT NULL DEFAULT '0', + forwarded smallint NOT NULL DEFAULT '0', + flagged smallint NOT NULL DEFAULT '0', + mdnsent smallint NOT NULL DEFAULT '0', + PRIMARY KEY (user_id, mailbox, uid) +); + +CREATE INDEX ix_cache_messages_changed ON cache_messages (changed); -- cgit v1.2.3