1-- This script fixes the 2.3.5 database schema for use with 2.3.6 2-- Running this script will drop legacy caching tables, 3-- so backup your database before continuing! 4 5SET FOREIGN_KEY_CHECKS=0; 6 7-- Make sure our database is set for utf8 8ALTER DATABASE CHARACTER SET utf8; 9 10-- Delete old cached data for the references 11BEGIN; 12DELETE FROM dbmail_referencesfield; 13COMMIT; 14 15-- Drop old legacy cache tables 16DROP TABLE IF EXISTS dbmail_ccfield; 17DROP TABLE IF EXISTS dbmail_datefield; 18DROP TABLE IF EXISTS dbmail_fromfield; 19DROP TABLE IF EXISTS dbmail_replytofield; 20DROP TABLE IF EXISTS dbmail_subjectfield; 21DROP TABLE IF EXISTS dbmail_tofield; 22 23-- Drop auto tables 24DROP TABLE IF EXISTS dbmail_auto_replies; 25DROP TABLE IF EXISTS dbmail_auto_notifications; 26 27-- Drop new cache tables because of move from legacy tables 28DROP TABLE IF EXISTS dbmail_header; 29DROP TABLE IF EXISTS dbmail_headername; 30DROP TABLE if EXISTS dbmail_headervalue; 31 32-- Make sure all bigint columns are size 20 to be consistent and varchars are proper size 33ALTER TABLE dbmail_acl MODIFY COLUMN user_id BIGINT(20) DEFAULT 0 NOT NULL; 34ALTER TABLE dbmail_acl MODIFY COLUMN mailbox_id BIGINT(20) DEFAULT 0 NOT NULL; 35ALTER TABLE dbmail_acl ADD COLUMN deleted_flag TINYINT(1) NOT NULL DEFAULT '0'; 36ALTER TABLE dbmail_acl ADD COLUMN expunge_flag TINYINT(1) NOT NULL DEFAULT '0'; 37UPDATE dbmail_acl SET deleted_flag=delete_flag, expunge_flag=delete_flag; 38ALTER TABLE dbmail_aliases MODIFY COLUMN alias_idnr BIGINT(20) AUTO_INCREMENT NOT NULL; 39ALTER TABLE dbmail_aliases MODIFY COLUMN client_idnr BIGINT(20) DEFAULT 0 NOT NULL; 40ALTER TABLE dbmail_envelope MODIFY COLUMN physmessage_id BIGINT(20) DEFAULT 0 NOT NULL; 41ALTER TABLE dbmail_envelope MODIFY COLUMN id BIGINT(20) AUTO_INCREMENT NOT NULL; 42ALTER TABLE dbmail_keywords MODIFY COLUMN message_idnr BIGINT(20) DEFAULT 0 NOT NULL; 43ALTER TABLE dbmail_mailboxes MODIFY COLUMN mailbox_idnr BIGINT(20) AUTO_INCREMENT NOT NULL; 44ALTER TABLE dbmail_mailboxes MODIFY COLUMN owner_idnr BIGINT(20) DEFAULT 0 NOT NULL; 45ALTER TABLE dbmail_mailboxes MODIFY COLUMN seq BIGINT(20) DEFAULT 0 NOT NULL; 46ALTER TABLE dbmail_messages MODIFY COLUMN message_idnr BIGINT(20) AUTO_INCREMENT NOT NULL; 47ALTER TABLE dbmail_messages MODIFY COLUMN mailbox_idnr BIGINT(20) DEFAULT 0 NOT NULL; 48ALTER TABLE dbmail_messages MODIFY COLUMN physmessage_id BIGINT(20) DEFAULT 0 NOT NULL; 49ALTER TABLE dbmail_mimeparts MODIFY COLUMN id BIGINT(20) AUTO_INCREMENT NOT NULL; 50ALTER TABLE dbmail_mimeparts MODIFY COLUMN size BIGINT(20) DEFAULT 0 NOT NULL; 51ALTER TABLE dbmail_partlists MODIFY COLUMN physmessage_id BIGINT(20) DEFAULT 0 NOT NULL; 52ALTER TABLE dbmail_partlists MODIFY COLUMN part_id BIGINT(20) DEFAULT 0 NOT NULL; 53ALTER TABLE dbmail_partlists ADD UNIQUE INDEX `message_parts` (`physmessage_id`,`part_key`,`part_depth`,`part_order`); 54ALTER TABLE dbmail_pbsp MODIFY COLUMN idnr BIGINT(20) AUTO_INCREMENT NOT NULL; 55ALTER TABLE dbmail_physmessage MODIFY COLUMN id BIGINT(20) AUTO_INCREMENT NOT NULL; 56ALTER TABLE dbmail_physmessage MODIFY COLUMN messagesize BIGINT(20) DEFAULT 0 NOT NULL; 57ALTER TABLE dbmail_physmessage MODIFY COLUMN rfcsize BIGINT(20) DEFAULT 0 NOT NULL; 58ALTER TABLE dbmail_referencesfield MODIFY COLUMN physmessage_id BIGINT(20) DEFAULT 0 NOT NULL; 59ALTER TABLE dbmail_referencesfield MODIFY COLUMN id BIGINT(20) AUTO_INCREMENT NOT NULL; 60ALTER TABLE dbmail_sievescripts MODIFY COLUMN owner_idnr BIGINT(20) DEFAULT 0 NOT NULL; 61ALTER TABLE dbmail_subscription MODIFY COLUMN user_id BIGINT(20) DEFAULT 0 NOT NULL; 62ALTER TABLE dbmail_subscription MODIFY COLUMN mailbox_id BIGINT(20) DEFAULT 0 NOT NULL; 63ALTER TABLE dbmail_users MODIFY COLUMN user_idnr BIGINT(20) AUTO_INCREMENT NOT NULL; 64ALTER TABLE dbmail_users MODIFY COLUMN client_idnr BIGINT(20) DEFAULT 0 NOT NULL; 65ALTER TABLE dbmail_users MODIFY COLUMN maxmail_size BIGINT(20) DEFAULT 0 NOT NULL; 66ALTER TABLE dbmail_users MODIFY COLUMN curmail_size BIGINT(20) DEFAULT 0 NOT NULL; 67ALTER TABLE dbmail_users MODIFY COLUMN maxsieve_size BIGINT(20) DEFAULT 0 NOT NULL; 68ALTER TABLE dbmail_users MODIFY COLUMN cursieve_size BIGINT(20) DEFAULT 0 NOT NULL; 69 70ALTER TABLE dbmail_aliases MODIFY COLUMN `alias` varchar(255) NOT NULL default ''; 71ALTER TABLE dbmail_aliases MODIFY COLUMN `deliver_to` varchar(255) NOT NULL default ''; 72ALTER TABLE dbmail_keywords MODIFY COLUMN `keyword` varchar(255) NOT NULL; 73ALTER TABLE dbmail_replycache MODIFY COLUMN `to_addr` varchar(255) NOT NULL default ''; 74ALTER TABLE dbmail_replycache MODIFY COLUMN `from_addr` varchar(255) NOT NULL default ''; 75ALTER TABLE dbmail_replycache MODIFY COLUMN `handle` varchar(255) NOT NULL default ''; 76ALTER TABLE dbmail_sievescripts MODIFY COLUMN `name` varchar(255) NOT NULL; 77ALTER TABLE dbmail_usermap MODIFY COLUMN `login` varchar(255) NOT NULL; 78ALTER TABLE dbmail_usermap MODIFY COLUMN `sock_allow` varchar(255) NOT NULL; 79ALTER TABLE dbmail_usermap MODIFY COLUMN `sock_deny` varchar(255) NOT NULL; 80ALTER TABLE dbmail_usermap MODIFY COLUMN `userid` varchar(255) NOT NULL; 81ALTER TABLE dbmail_users MODIFY COLUMN `userid` varchar(255) NOT NULL default ''; 82ALTER TABLE dbmail_users MODIFY COLUMN `passwd` varchar(255) NOT NULL default ''; 83ALTER TABLE dbmail_users MODIFY COLUMN `encryption_type` varchar(255) NOT NULL default ''; 84 85 86-- Create new single instance header storage tables 87CREATE TABLE `dbmail_headername` ( 88 `id` bigint(20) NOT NULL auto_increment, 89 `headername` varchar(255) NOT NULL default '', 90 PRIMARY KEY (`id`), 91 UNIQUE KEY `headername` (`headername`) 92) ENGINE=InnoDB DEFAULT CHARSET=utf8; 93 94CREATE TABLE `dbmail_headervalue` ( 95 `id` bigint(20) NOT NULL auto_increment, 96 `hash` varchar(255) NOT NULL, 97 `headervalue` text NOT NULL, 98 `sortfield` varchar(255) default NULL, 99 `datefield` datetime default NULL, 100 PRIMARY KEY (`id`), 101 KEY `hash` (`hash`), 102 KEY `headervalue` (`headervalue`(255)), 103 KEY `sortfield` (`sortfield`), 104 KEY `datefield` (`datefield`) 105) ENGINE=InnoDB DEFAULT CHARSET=utf8; 106 107CREATE TABLE `dbmail_header` ( 108 `physmessage_id` bigint(20) NOT NULL, 109 `headername_id` bigint(20) NOT NULL, 110 `headervalue_id` bigint(20) NOT NULL, 111 PRIMARY KEY (`physmessage_id`,`headername_id`,`headervalue_id`), 112 KEY `physmessage_id` (`physmessage_id`), 113 KEY `headername_id` (`headername_id`), 114 KEY `headervalue_id` (`headervalue_id`), 115 KEY `physmessage_id_headername_id` (`physmessage_id`,`headername_id`), 116 KEY `physmessage_id_headervalue_id` (`physmessage_id`,`headervalue_id`), 117 KEY `headername_id_headervalue_id` (`headername_id`,`headervalue_id`), 118 CONSTRAINT `dbmail_header_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, 119 CONSTRAINT `dbmail_header_ibfk_2` FOREIGN KEY (`headername_id`) REFERENCES `dbmail_headername` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, 120 CONSTRAINT `dbmail_header_ibfk_3` FOREIGN KEY (`headervalue_id`) REFERENCES `dbmail_headervalue` (`id`) ON DELETE CASCADE ON UPDATE CASCADE 121) ENGINE=InnoDB DEFAULT CHARSET=utf8; 122 123-- views for IMAP sort 124 125DROP VIEW IF EXISTS dbmail_fromfield; 126CREATE VIEW dbmail_fromfield AS 127 SELECT physmessage_id,sortfield AS fromfield 128 FROM dbmail_messages m 129 JOIN dbmail_header h USING (physmessage_id) 130 JOIN dbmail_headername n ON h.headername_id = n.id 131 JOIN dbmail_headervalue v ON h.headervalue_id = v.id 132WHERE n.headername='from'; 133 134DROP VIEW IF EXISTS dbmail_ccfield; 135CREATE VIEW dbmail_ccfield AS 136 SELECT physmessage_id,sortfield AS ccfield 137 FROM dbmail_messages m 138 JOIN dbmail_header h USING (physmessage_id) 139 JOIN dbmail_headername n ON h.headername_id = n.id 140 JOIN dbmail_headervalue v ON h.headervalue_id = v.id 141WHERE n.headername='cc'; 142 143DROP VIEW IF EXISTS dbmail_tofield; 144CREATE VIEW dbmail_tofield AS 145 SELECT physmessage_id,sortfield AS tofield 146 FROM dbmail_messages m 147 JOIN dbmail_header h USING (physmessage_id) 148 JOIN dbmail_headername n ON h.headername_id = n.id 149 JOIN dbmail_headervalue v ON h.headervalue_id = v.id 150WHERE n.headername='to'; 151 152DROP VIEW IF EXISTS dbmail_subjectfield; 153CREATE VIEW dbmail_subjectfield AS 154 SELECT physmessage_id,headervalue AS subjectfield 155 FROM dbmail_messages m 156 JOIN dbmail_header h USING (physmessage_id) 157 JOIN dbmail_headername n ON h.headername_id = n.id 158 JOIN dbmail_headervalue v ON h.headervalue_id = v.id 159WHERE n.headername='subject'; 160 161DROP VIEW IF EXISTS dbmail_datefield; 162CREATE VIEW dbmail_datefield AS 163 SELECT physmessage_id,datefield 164 FROM dbmail_messages m 165 JOIN dbmail_header h USING (physmessage_id) 166 JOIN dbmail_headername n ON h.headername_id = n.id 167 JOIN dbmail_headervalue v ON h.headervalue_id = v.id 168WHERE n.headername='date'; 169 170 171 172-- Add new delivery sorting filters table 173DROP TABLE IF EXISTS dbmail_filters; 174CREATE TABLE dbmail_filters ( 175 user_id BIGINT(20) NOT NULL, 176 id BIGINT(20) NOT NULL AUTO_INCREMENT, 177 headername VARCHAR(255) NOT NULL, 178 headervalue VARCHAR(255) NOT NULL, 179 mailbox VARCHAR(255) NOT NULL, 180 PRIMARY KEY (id), 181 FOREIGN KEY (user_id) REFERENCES dbmail_users(user_idnr) ON DELETE CASCADE ON UPDATE CASCADE 182) ENGINE=InnoDB DEFAULT CHARSET=utf8; 183 184 185-- Add new authlog table 186DROP TABLE IF EXISTS dbmail_authlog; 187CREATE TABLE `dbmail_authlog` ( 188 `id` bigint(20) NOT NULL auto_increment, 189 `userid` varchar(100) default NULL, 190 `service` varchar(32) default NULL, 191 `login_time` datetime default NULL, 192 `logout_time` datetime default NULL, 193 `src_ip` varchar(16) default NULL, 194 `src_port` int(11) default NULL, 195 `dst_ip` varchar(16) default NULL, 196 `dst_port` int(11) default NULL, 197 `status` varchar(32) default 'active', 198 `bytes_rx` bigint(20) NOT NULL default '0', 199 `bytes_tx` bigint(20) NOT NULL default '0', 200 PRIMARY KEY (`id`) 201) ENGINE=InnoDB DEFAULT CHARSET=utf8; 202 203SET FOREIGN_KEY_CHECKS=1; 204