network/dbmail: Added (IMAP and POP3 Server).

Signed-off-by: Willy Sudiarto Raharjo <willysr@slackbuilds.org>

1 files changed, 467 insertions, 0 deletions

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');
+