diff options
Diffstat (limited to 'network/dbmail/create_tables.mysql')
| -rw-r--r-- | network/dbmail/create_tables.mysql | 467 |
1 files changed, 0 insertions, 467 deletions
diff --git a/network/dbmail/create_tables.mysql b/network/dbmail/create_tables.mysql deleted file mode 100644 index de035154eb..0000000000 --- a/network/dbmail/create_tables.mysql +++ /dev/null @@ -1,467 +0,0 @@ --- dbmail mysql schema --- --- Copyright (c) 2006 Aaron Stone, aaron@serendipity.cx --- Copyright (c) 2004-2014, NFG Net Facilities Group BV, support@nfg.nl --- --- This program is free software; you can redistribute it and/or --- modify it under the terms of the GNU General Public License --- as published by the Free Software Foundation; either --- version 2 of the License, or (at your option) any later --- version. --- --- This program is distributed in the hope that it will be useful, --- but WITHOUT ANY WARRANTY; without even the implied warranty of --- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the --- GNU General Public License for more details. --- --- You should have received a copy of the GNU General Public License --- along with this program; if not, write to the Free Software --- Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. --- - - --- Make sure our database is set for utf8 -ALTER DATABASE CHARACTER SET utf8; - -SET FOREIGN_KEY_CHECKS=0; --- --- Table structure for table `dbmail_acl` --- - -DROP TABLE IF EXISTS `dbmail_authlog`; -CREATE TABLE `dbmail_authlog` ( - `id` bigint(20) UNSIGNED NOT NULL auto_increment, - `userid` varchar(100) default NULL, - `service` varchar(32) default NULL, - `login_time` datetime default NULL, - `logout_time` datetime default NULL, - `src_ip` varchar(16) default NULL, - `src_port` int(11) default NULL, - `dst_ip` varchar(16) default NULL, - `dst_port` int(11) default NULL, - `status` varchar(32) default 'active', - `bytes_rx` bigint(20) NOT NULL default '0', - `bytes_tx` bigint(20) NOT NULL default '0', - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - - --- --- Table structure for table `dbmail_acl` --- - -DROP TABLE IF EXISTS `dbmail_acl`; -CREATE TABLE `dbmail_acl` ( - `user_id` bigint(20) UNSIGNED NOT NULL default '0', - `mailbox_id` bigint(20) UNSIGNED NOT NULL default '0', - `lookup_flag` tinyint(1) NOT NULL default '0', - `read_flag` tinyint(1) NOT NULL default '0', - `seen_flag` tinyint(1) NOT NULL default '0', - `write_flag` tinyint(1) NOT NULL default '0', - `insert_flag` tinyint(1) NOT NULL default '0', - `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`), - KEY `mailbox_id_index` (`mailbox_id`), - CONSTRAINT `dbmail_acl_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `dbmail_acl_ibfk_2` FOREIGN KEY (`mailbox_id`) REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `dbmail_aliases` --- - -DROP TABLE IF EXISTS `dbmail_aliases`; -CREATE TABLE `dbmail_aliases` ( - `alias_idnr` bigint(20) UNSIGNED NOT NULL auto_increment, - `alias` varchar(255) NOT NULL default '', - `deliver_to` varchar(255) NOT NULL default '', - `client_idnr` bigint(20) UNSIGNED NOT NULL default '0', - PRIMARY KEY (`alias_idnr`), - KEY `alias_index` (`alias`), - KEY `client_idnr_index` (`client_idnr`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `dbmail_envelope` --- - -DROP TABLE IF EXISTS `dbmail_envelope`; -CREATE TABLE `dbmail_envelope` ( - `id` bigint(20) UNSIGNED NOT NULL auto_increment, - `physmessage_id` bigint(20) UNSIGNED NOT NULL default '0', - `envelope` text NOT NULL, - PRIMARY KEY (`id`), - UNIQUE KEY `physmessage_id_1` (`physmessage_id`), - UNIQUE KEY `physmessage_id_2` (`physmessage_id`,`id`), - CONSTRAINT `dbmail_envelope_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `dbmail_filters` --- - -DROP TABLE IF EXISTS `dbmail_filters`; -CREATE TABLE `dbmail_filters` ( - `id` bigint(20) UNSIGNED NOT NULL auto_increment, - `user_id` bigint(20) UNSIGNED NOT NULL, - `headername` varchar(255) NOT NULL, - `headervalue` varchar(255) NOT NULL, - `mailbox` varchar(255) NOT NULL, - PRIMARY KEY (`id`), - KEY `user_id` (`user_id`), - CONSTRAINT `dbmail_filters_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `dbmail_header` --- - -DROP TABLE IF EXISTS `dbmail_header`; -CREATE TABLE `dbmail_header` ( - `physmessage_id` bigint(20) UNSIGNED NOT NULL, - `headername_id` bigint(20) UNSIGNED NOT NULL, - `headervalue_id` bigint(20) UNSIGNED 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 -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `dbmail_headername` --- - -DROP TABLE IF EXISTS `dbmail_headername`; -CREATE TABLE `dbmail_headername` ( - `id` bigint(20) UNSIGNED NOT NULL auto_increment, - `headername` varchar(255) NOT NULL default '', - PRIMARY KEY (`id`), - UNIQUE KEY `headername` (`headername`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `dbmail_headervalue` --- - -DROP TABLE IF EXISTS `dbmail_headervalue`; -CREATE TABLE `dbmail_headervalue` ( - `id` bigint(20) UNSIGNED NOT NULL auto_increment, - `hash` varchar(255) NOT NULL, - `headervalue` text NOT NULL, - `sortfield` varchar(255) default NULL, - `datefield` datetime default NULL, - PRIMARY KEY (`id`), - KEY `hash` (`hash`), - KEY `headervalue` (`headervalue`(255)), - KEY `sortfield` (`sortfield`), - KEY `datefield` (`datefield`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - - --- --- Table structure for table `dbmail_keywords` --- - -DROP TABLE IF EXISTS `dbmail_keywords`; -CREATE TABLE `dbmail_keywords` ( - `message_idnr` bigint(20) UNSIGNED NOT NULL default '0', - `keyword` varchar(255) NOT NULL, - PRIMARY KEY (`message_idnr`,`keyword`), - CONSTRAINT `dbmail_keywords_ibfk_1` FOREIGN KEY (`message_idnr`) REFERENCES `dbmail_messages` (`message_idnr`) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `dbmail_mailboxes` --- - -DROP TABLE IF EXISTS `dbmail_mailboxes`; -CREATE TABLE `dbmail_mailboxes` ( - `mailbox_idnr` bigint(20) UNSIGNED NOT NULL auto_increment, - `owner_idnr` bigint(20) UNSIGNED NOT NULL default '0', - `name` varchar(255) NOT NULL default '', - `seen_flag` tinyint(1) NOT NULL default '0', - `answered_flag` tinyint(1) NOT NULL default '0', - `deleted_flag` tinyint(1) NOT NULL default '0', - `flagged_flag` tinyint(1) NOT NULL default '0', - `recent_flag` tinyint(1) NOT NULL default '0', - `draft_flag` tinyint(1) NOT NULL default '0', - `no_inferiors` tinyint(1) NOT NULL default '0', - `no_select` tinyint(1) NOT NULL default '0', - `permission` tinyint(1) default '2', - `seq` bigint(20) NOT NULL default '0', - PRIMARY KEY (`mailbox_idnr`), - UNIQUE KEY `owner_idnr_name_index` (`owner_idnr`,`name`), - KEY `name_index` (`name`), - KEY `owner_idnr_index` (`owner_idnr`), - KEY `seq_index` (`seq`), - CONSTRAINT `dbmail_mailboxes_ibfk_1` FOREIGN KEY (`owner_idnr`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `dbmail_messages` --- - -DROP TABLE IF EXISTS `dbmail_messages`; -CREATE TABLE `dbmail_messages` ( - `message_idnr` bigint(20) UNSIGNED NOT NULL auto_increment, - `mailbox_idnr` bigint(20) UNSIGNED NOT NULL default '0', - `physmessage_id` bigint(20) UNSIGNED NOT NULL default '0', - `seen_flag` tinyint(1) NOT NULL default '0', - `answered_flag` tinyint(1) NOT NULL default '0', - `deleted_flag` tinyint(1) NOT NULL default '0', - `flagged_flag` tinyint(1) NOT NULL default '0', - `recent_flag` tinyint(1) NOT NULL default '0', - `draft_flag` tinyint(1) NOT NULL default '0', - `unique_id` varchar(70) NOT NULL default '', - `status` tinyint(3) unsigned NOT NULL default '0', - PRIMARY KEY (`message_idnr`), - KEY `physmessage_id_index` (`physmessage_id`), - KEY `mailbox_idnr_index` (`mailbox_idnr`), - KEY `seen_flag_index` (`seen_flag`), - KEY `unique_id_index` (`unique_id`), - KEY `status_index` (`status`), - KEY `mailbox_status` (`mailbox_idnr`,`status`), - CONSTRAINT `dbmail_messages_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `dbmail_messages_ibfk_2` FOREIGN KEY (`mailbox_idnr`) REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `dbmail_mimeparts` --- - -DROP TABLE IF EXISTS `dbmail_mimeparts`; -CREATE TABLE `dbmail_mimeparts` ( - `id` bigint(20) UNSIGNED NOT NULL auto_increment, - `hash` char(128) NOT NULL, - `data` longblob NOT NULL, - `size` bigint(20) NOT NULL default '0', - PRIMARY KEY (`id`), - KEY `hash` (`hash`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `dbmail_partlists` --- - -DROP TABLE IF EXISTS `dbmail_partlists`; -CREATE TABLE `dbmail_partlists` ( - `physmessage_id` bigint(20) UNSIGNED NOT NULL default '0', - `is_header` tinyint(1) NOT NULL default '0', - `part_key` smallint(6) NOT NULL default '0', - `part_depth` smallint(6) NOT NULL default '0', - `part_order` smallint(6) NOT NULL default '0', - `part_id` bigint(20) UNSIGNED NOT NULL default '0', - KEY `physmessage_id` (`physmessage_id`), - KEY `part_id` (`part_id`), - UNIQUE KEY `message_parts` (`physmessage_id`,`part_key`,`part_depth`,`part_order`), - CONSTRAINT `dbmail_partlists_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `dbmail_partlists_ibfk_2` FOREIGN KEY (`part_id`) REFERENCES `dbmail_mimeparts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `dbmail_pbsp` --- - -DROP TABLE IF EXISTS `dbmail_pbsp`; -CREATE TABLE `dbmail_pbsp` ( - `idnr` bigint(20) UNSIGNED NOT NULL auto_increment, - `since` datetime NOT NULL default '0000-00-00 00:00:00', - `ipnumber` varchar(40) NOT NULL, - PRIMARY KEY (`idnr`), - UNIQUE KEY `ipnumber_index` (`ipnumber`), - KEY `since_index` (`since`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `dbmail_physmessage` --- - -DROP TABLE IF EXISTS `dbmail_physmessage`; -CREATE TABLE `dbmail_physmessage` ( - `id` bigint(20) UNSIGNED NOT NULL auto_increment, - `messagesize` bigint(20) UNSIGNED NOT NULL default '0', - `rfcsize` bigint(20) UNSIGNED NOT NULL default '0', - `internal_date` datetime NOT NULL default '0000-00-00 00:00:00', - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `dbmail_referencesfield` --- - -DROP TABLE IF EXISTS `dbmail_referencesfield`; -CREATE TABLE `dbmail_referencesfield` ( - `id` bigint(20) UNSIGNED NOT NULL auto_increment, - `physmessage_id` bigint(20) UNSIGNED NOT NULL default '0', - `referencesfield` varchar(255) NOT NULL default '', - PRIMARY KEY (`id`), - UNIQUE KEY `physmessage_id` (`physmessage_id`,`referencesfield`), - CONSTRAINT `dbmail_referencesfield_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `dbmail_replycache` --- - -DROP TABLE IF EXISTS `dbmail_replycache`; -CREATE TABLE `dbmail_replycache` ( - `to_addr` varchar(255) NOT NULL default '', - `from_addr` varchar(255) NOT NULL default '', - `handle` varchar(255) NOT NULL default '', - `lastseen` datetime NOT NULL default '0000-00-00 00:00:00', - UNIQUE KEY `replycache_1` (`to_addr`,`from_addr`,`handle`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `dbmail_sievescripts` --- - -DROP TABLE IF EXISTS `dbmail_sievescripts`; -CREATE TABLE `dbmail_sievescripts` ( - `owner_idnr` bigint(20) UNSIGNED NOT NULL default '0', - `name` varchar(255) NOT NULL, - `script` text, - `active` tinyint(1) NOT NULL default '0', - UNIQUE KEY `owner_idnr_2` (`owner_idnr`,`name`), - KEY `name` (`name`), - KEY `owner_idnr` (`owner_idnr`), - CONSTRAINT `dbmail_sievescripts_ibfk_1` FOREIGN KEY (`owner_idnr`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `dbmail_subscription` --- - -DROP TABLE IF EXISTS `dbmail_subscription`; -CREATE TABLE `dbmail_subscription` ( - `user_id` bigint(20) UNSIGNED NOT NULL default '0', - `mailbox_id` bigint(20) UNSIGNED NOT NULL default '0', - PRIMARY KEY (`user_id`,`mailbox_id`), - KEY `user_id_index` (`user_id`), - KEY `mailbox_id_index` (`mailbox_id`), - CONSTRAINT `dbmail_subscription_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `dbmail_subscription_ibfk_2` FOREIGN KEY (`mailbox_id`) REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `dbmail_usermap` --- - -DROP TABLE IF EXISTS `dbmail_usermap`; -CREATE TABLE `dbmail_usermap` ( - `login` varchar(255) NOT NULL, - `sock_allow` varchar(255) NOT NULL, - `sock_deny` varchar(255) NOT NULL, - `userid` varchar(255) NOT NULL, - UNIQUE KEY `usermap_idx_1` (`login`,`sock_allow`,`userid`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `dbmail_users` --- - -DROP TABLE IF EXISTS `dbmail_users`; -CREATE TABLE `dbmail_users` ( - `user_idnr` bigint(20) UNSIGNED NOT NULL auto_increment, - `userid` varchar(255) NOT NULL default '', - `passwd` varchar(255) NOT NULL default '', - `client_idnr` bigint(20) UNSIGNED NOT NULL default '0', - `maxmail_size` bigint(20) NOT NULL default '0', - `curmail_size` bigint(20) NOT NULL default '0', - `maxsieve_size` bigint(20) NOT NULL default '0', - `cursieve_size` bigint(20) NOT NULL default '0', - `encryption_type` varchar(255) NOT NULL default '', - `last_login` datetime NOT NULL default '1979-11-03 22:05:58', - PRIMARY KEY (`user_idnr`), - UNIQUE KEY `userid_index` (`userid`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -DROP TABLE IF EXISTS dbmail_auto_notifications; -CREATE TABLE dbmail_auto_notifications ( - user_idnr bigint(20) UNSIGNED NOT NULL, - notify_address varchar(100) NOT NULL default '', - INDEX user_idnr_index (user_idnr), - FOREIGN KEY user_idnr_fk (user_idnr) - REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE InnoDB DEFAULT CHARSET=utf8; - -DROP TABLE IF EXISTS dbmail_auto_replies; -CREATE TABLE dbmail_auto_replies ( - user_idnr bigint(20) UNSIGNED DEFAULT '0' NOT NULL, - start_date DATETIME NOT NULL, - stop_date DATETIME NOT NULL, - reply_body MEDIUMTEXT, - INDEX user_idnr_index (user_idnr), - FOREIGN KEY user_idnr_fk2 (user_idnr) - REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE InnoDB DEFAULT CHARSET=utf8; - --- --- views for IMAP sort --- - -DROP VIEW IF EXISTS dbmail_fromfield; -CREATE VIEW dbmail_fromfield AS - SELECT physmessage_id,sortfield AS fromfield - FROM dbmail_messages m - JOIN dbmail_header h USING (physmessage_id) - JOIN dbmail_headername n ON h.headername_id = n.id - JOIN dbmail_headervalue v ON h.headervalue_id = v.id -WHERE n.headername='from'; - -DROP VIEW IF EXISTS dbmail_ccfield; -CREATE VIEW dbmail_ccfield AS - SELECT physmessage_id,sortfield AS ccfield - FROM dbmail_messages m - JOIN dbmail_header h USING (physmessage_id) - JOIN dbmail_headername n ON h.headername_id = n.id - JOIN dbmail_headervalue v ON h.headervalue_id = v.id -WHERE n.headername='cc'; - -DROP VIEW IF EXISTS dbmail_tofield; -CREATE VIEW dbmail_tofield AS - SELECT physmessage_id,sortfield AS tofield - FROM dbmail_messages m - JOIN dbmail_header h USING (physmessage_id) - JOIN dbmail_headername n ON h.headername_id = n.id - JOIN dbmail_headervalue v ON h.headervalue_id = v.id -WHERE n.headername='to'; - -DROP VIEW IF EXISTS dbmail_subjectfield; -CREATE VIEW dbmail_subjectfield AS - SELECT physmessage_id,headervalue AS subjectfield - FROM dbmail_messages m - JOIN dbmail_header h USING (physmessage_id) - JOIN dbmail_headername n ON h.headername_id = n.id - JOIN dbmail_headervalue v ON h.headervalue_id = v.id -WHERE n.headername='subject'; - -DROP VIEW IF EXISTS dbmail_datefield; -CREATE VIEW dbmail_datefield AS - SELECT physmessage_id,datefield,sortfield - FROM dbmail_messages m - JOIN dbmail_header h USING (physmessage_id) - JOIN dbmail_headername n ON h.headername_id = n.id - JOIN dbmail_headervalue v ON h.headervalue_id = v.id -WHERE n.headername='date'; - - - --- Create the required built-in users for the delivery chain, anyone acls, and #public mailboxes -INSERT INTO dbmail_users (userid, passwd, encryption_type) VALUES - ('__@!internal_delivery_user!@__', '', 'md5'), - ('anyone', '', 'md5'), - ('__public__', '', 'md5'); - |