Fix postgres header cache indexes. Add some additional indexes on dbmail_header for speed. RFC 4314 IMAP Acl support.

Signed-off-by: Paul J Stevens <paul@nfg.nl>

6 files changed, 35 insertions, 6 deletions

diff --git a/sql/mysql/2_3_5-2_3_6.mysql b/sql/mysql/2_3_5-2_3_6.mysql
index 5d7d31cd..32aa79a5 100644
--- a/sql/mysql/2_3_5-2_3_6.mysql
+++ b/sql/mysql/2_3_5-2_3_6.mysql
@@ -28,6 +28,9 @@ DROP TABLE if EXISTS dbmail_headervalue;
 -- Make sure all bigint columns are size 20 to be consistent and varchars are proper size
 ALTER TABLE dbmail_acl MODIFY COLUMN user_id BIGINT(20) DEFAULT 0 NOT NULL;
 ALTER TABLE dbmail_acl MODIFY COLUMN mailbox_id BIGINT(20) DEFAULT 0 NOT NULL;
+ALTER TABLE dbmail_acl ADD COLUMN deleted_flag TINYINT(1) NOT NULL DEFAULT '0';
+ALTER TABLE dbmail_acl ADD COLUMN expunge_flag TINYINT(1) NOT NULL DEFAULT '0';
+UPDATE dbmail_acl SET deleted_flag=delete_flag, expunge_flag=delete_flag;
 ALTER TABLE dbmail_aliases MODIFY COLUMN alias_idnr BIGINT(20) AUTO_INCREMENT NOT NULL;
 ALTER TABLE dbmail_aliases MODIFY COLUMN client_idnr BIGINT(20) DEFAULT 0 NOT NULL;
 ALTER TABLE dbmail_auto_notifications MODIFY COLUMN user_idnr BIGINT(20) DEFAULT 0 NOT NULL;
@@ -111,8 +114,12 @@ CREATE TABLE `dbmail_header` (
   `headername_id` bigint(20) NOT NULL,
   `headervalue_id` bigint(20) NOT NULL,
   PRIMARY KEY  (`physmessage_id`,`headername_id`,`headervalue_id`),
+  KEY `physmessage_id` (`physmessage_id`),
   KEY `headername_id` (`headername_id`),
   KEY `headervalue_id` (`headervalue_id`),
+  KEY `physmessage_id_headername_id` (`physmessage_id`,`headername_id`),
+  KEY `physmessage_id_headervalue_id` (`physmessage_id`,`headervalue_id`),
+  KEY `headername_id_headervalue_id` (`headername_id`,`headervalue_id`),
   CONSTRAINT `dbmail_header_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `dbmail_header_ibfk_2` FOREIGN KEY (`headername_id`) REFERENCES `dbmail_headername` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `dbmail_header_ibfk_3` FOREIGN KEY (`headervalue_id`) REFERENCES `dbmail_headervalue` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
diff --git a/sql/mysql/create_tables.mysql b/sql/mysql/create_tables.mysql
index 302dd844..b293cadf 100644
--- a/sql/mysql/create_tables.mysql
+++ b/sql/mysql/create_tables.mysql
@@ -40,6 +40,8 @@ CREATE TABLE `dbmail_acl` (
   `post_flag` tinyint(1) NOT NULL default '0',
   `create_flag` tinyint(1) NOT NULL default '0',
   `delete_flag` tinyint(1) NOT NULL default '0',
+  `deleted_flag` tinyint(1) NOT NULL default '0',
+  `expunge_flag` tinyint(1) NOT NULL default '0',
   `administer_flag` tinyint(1) NOT NULL default '0',
   PRIMARY KEY  (`user_id`,`mailbox_id`),
   KEY `user_id_index` (`user_id`),
@@ -103,8 +105,12 @@ CREATE TABLE `dbmail_header` (
   `headername_id` bigint(20) NOT NULL,
   `headervalue_id` bigint(20) NOT NULL,
   PRIMARY KEY  (`physmessage_id`,`headername_id`,`headervalue_id`),
+  KEY `physmessage_id` (`physmessage_id`),
   KEY `headername_id` (`headername_id`),
   KEY `headervalue_id` (`headervalue_id`),
+  KEY `physmessage_id_headername_id` (`physmessage_id`,`headername_id`),
+  KEY `physmessage_id_headervalue_id` (`physmessage_id`,`headervalue_id`),
+  KEY `headername_id_headervalue_id` (`headername_id`,`headervalue_id`),
   CONSTRAINT `dbmail_header_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `dbmail_header_ibfk_2` FOREIGN KEY (`headername_id`) REFERENCES `dbmail_headername` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `dbmail_header_ibfk_3` FOREIGN KEY (`headervalue_id`) REFERENCES `dbmail_headervalue` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
diff --git a/sql/postgresql/2_3_5-2_3_6.pgsql b/sql/postgresql/2_3_5-2_3_6.pgsql
index cd6ce292..95378ada 100644
--- a/sql/postgresql/2_3_5-2_3_6.pgsql
+++ b/sql/postgresql/2_3_5-2_3_6.pgsql
@@ -11,6 +11,10 @@ drop table if exists dbmail_tofield;
 delete from dbmail_referencesfield;
 
 
+ALTER TABLE dbmail_acl ADD COLUMN deleted_flag INT2 DEFAULT '0' NOT NULL;
+ALTER TABLE dbmail_acl ADD COLUMN expunge_flag INT2 DEFAULT '0' NOT NULL;
+UPDATE dbmail_acl SET deleted_flag=delete_flag, expunge_flag=delete_flag;
+
 DROP TABLE if exists dbmail_headervalue CASCADE;
 DROP TABLE if exists dbmail_headername CASCADE;
 DROP TABLE if exists dbmail_header;
@@ -37,9 +41,9 @@ CREATE TABLE dbmail_headervalue (
         PRIMARY KEY (id)
 );
 CREATE INDEX dbmail_headervalue_1 ON dbmail_headervalue USING btree (hash);
-CREATE INDEX dbmail_headervalue_2 ON dbmail_headervalue USING btree (emailname);
-CREATE INDEX dbmail_headervalue_3 ON dbmail_headervalue USING btree (emailaddr);
-CREATE INDEX dbmail_headervalue_4 ON dbmail_headervalue USING btree (sortfield);
+CREATE INDEX dbmail_headervalue_2 ON dbmail_headervalue USING btree (substring(emailname,0,255));
+CREATE INDEX dbmail_headervalue_3 ON dbmail_headervalue USING btree (substring(emailaddr,0,255));
+CREATE INDEX dbmail_headervalue_4 ON dbmail_headervalue USING btree (substring(sortfield,0,255));
 CREATE INDEX dbmail_headervalue_5 ON dbmail_headervalue USING btree (datefield);
 
 CREATE TABLE dbmail_header (
diff --git a/sql/postgresql/create_tables.pgsql b/sql/postgresql/create_tables.pgsql
index ce8702ab..b98deed7 100644
--- a/sql/postgresql/create_tables.pgsql
+++ b/sql/postgresql/create_tables.pgsql
@@ -100,6 +100,8 @@ CREATE TABLE dbmail_acl (
     post_flag INT2 DEFAULT '0' NOT NULL,
     create_flag INT2 DEFAULT '0' NOT NULL,
     delete_flag INT2 DEFAULT '0' NOT NULL,
+    deleted_flag INT2 DEFAULT '0' NOT NULL,
+    expunge_flag INT2 DEFAULT '0' NOT NULL,
     administer_flag INT2 DEFAULT '0' NOT NULL,
     PRIMARY KEY (user_id, mailbox_id)
 );
@@ -202,9 +204,9 @@ CREATE TABLE dbmail_headervalue (
 );
 
 CREATE INDEX dbmail_headervalue_1 ON dbmail_headervalue USING btree (hash);
-CREATE INDEX dbmail_headervalue_2 ON dbmail_headervalue USING btree (emailname);
-CREATE INDEX dbmail_headervalue_3 ON dbmail_headervalue USING btree (emailaddr);
-CREATE INDEX dbmail_headervalue_4 ON dbmail_headervalue USING btree (sortfield);
+CREATE INDEX dbmail_headervalue_2 ON dbmail_headervalue USING btree (substring(emailname,0,255));
+CREATE INDEX dbmail_headervalue_3 ON dbmail_headervalue USING btree (substring(emailaddr,0,255));
+CREATE INDEX dbmail_headervalue_4 ON dbmail_headervalue USING btree (substring(sortfield,0,255));
 CREATE INDEX dbmail_headervalue_5 ON dbmail_headervalue USING btree (datefield);
 
 CREATE SEQUENCE dbmail_headername_id_seq;
diff --git a/sql/sqlite/2_3_5-2_3_6.sqlite b/sql/sqlite/2_3_5-2_3_6.sqlite
index 801f0f61..261fc90c 100644
--- a/sql/sqlite/2_3_5-2_3_6.sqlite
+++ b/sql/sqlite/2_3_5-2_3_6.sqlite
@@ -2,6 +2,14 @@
 
 BEGIN TRANSACTION;
 
+ALTER TABLE dbmail_acl ADD COLUMN deleted_flag TINYINT(1) NOT NULL DEFAULT '0';
+ALTER TABLE dbmail_acl ADD COLUMN expunge_flag TINYINT(1) NOT NULL DEFAULT '0';
+UPDATE dbmail_acl SET deleted_flag=delete_flag, expunge_flag=delete_flag;
+
+COMMIT;
+
+BEGIN TRANSACTION;
+
 DROP TABLE IF EXISTS dbmail_header;
 DROP TABLE IF EXISTS dbmail_headername;
 DROP TABLE IF EXISTS dbmail_headervalue;
diff --git a/sql/sqlite/create_tables.sqlite b/sql/sqlite/create_tables.sqlite
index c5790d52..72bab06d 100644
--- a/sql/sqlite/create_tables.sqlite
+++ b/sql/sqlite/create_tables.sqlite
@@ -168,6 +168,8 @@ CREATE TABLE dbmail_acl (
 	post_flag BOOLEAN default '0' not null,
 	create_flag BOOLEAN default '0' not null,	
 	delete_flag BOOLEAN default '0' not null,	
+	deleted_flag BOOLEAN default '0' not null,	
+	expunge_flag BOOLEAN default '0' not null,	
 	administer_flag BOOLEAN default '0' not null
 );
 CREATE INDEX dbmail_acl_1 ON dbmail_acl(user_id);