diff options
author | alecpl <alec@alec.pl> | 2011-09-07 15:07:03 +0400 |
---|---|---|
committer | alecpl <alec@alec.pl> | 2011-09-07 15:07:03 +0400 |
commit | 80152b333ca5d856dcf09f5ca10a9ffd80ba117f (patch) | |
tree | 084aa3c8aabb3d2b1783dbb01170840ccefc0c62 /SQL | |
parent | b104e39f3425faf77cae67101c734fcfc3b0c1e9 (diff) |
- 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
Diffstat (limited to 'SQL')
-rw-r--r-- | SQL/mssql.initial.sql | 144 | ||||
-rw-r--r-- | SQL/mssql.upgrade.sql | 96 | ||||
-rw-r--r-- | SQL/mysql.initial.sql | 76 | ||||
-rw-r--r-- | SQL/mysql.update.sql | 42 | ||||
-rw-r--r-- | SQL/postgres.initial.sql | 76 | ||||
-rw-r--r-- | SQL/postgres.update.sql | 43 | ||||
-rw-r--r-- | SQL/sqlite.initial.sql | 87 | ||||
-rw-r--r-- | SQL/sqlite.update.sql | 41 |
8 files changed, 463 insertions, 142 deletions
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); |