summaryrefslogtreecommitdiff
path: root/SQL
diff options
context:
space:
mode:
Diffstat (limited to 'SQL')
-rw-r--r--SQL/mssql.initial.sql144
-rw-r--r--SQL/mssql.upgrade.sql96
-rw-r--r--SQL/mysql.initial.sql76
-rw-r--r--SQL/mysql.update.sql42
-rw-r--r--SQL/postgres.initial.sql76
-rw-r--r--SQL/postgres.update.sql43
-rw-r--r--SQL/sqlite.initial.sql87
-rw-r--r--SQL/sqlite.update.sql41
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);
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-26 18:26:06 +0000