From 0499dcf219f636ad9f81cf8ba79f2b2584f77693 Mon Sep 17 00:00:00 2001 From: Sergei Fedosoff Date: Fri, 25 May 2018 22:14:25 +0700 Subject: network/dbmail: Added (IMAP and POP3 Server). Signed-off-by: Willy Sudiarto Raharjo --- network/dbmail/create_tables.mysql | 467 +++++++++++++++++++++++++++++++++++++ 1 file changed, 467 insertions(+) create mode 100644 network/dbmail/create_tables.mysql (limited to 'network/dbmail/create_tables.mysql') diff --git a/network/dbmail/create_tables.mysql b/network/dbmail/create_tables.mysql new file mode 100644 index 0000000000..de035154eb --- /dev/null +++ b/network/dbmail/create_tables.mysql @@ -0,0 +1,467 @@ +-- 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'); + -- cgit v1.2.3