ERROR 1061 (42000) at line 262: Duplicate key name 'physmessage_id_2' because i cleaned up 'dbmail_envelope' and added the key to prevent get new duplicates until upgrade is done
i would drop the whole table and re-create it ________________________________________ attached a modified and overall cleaned (uppercase, quotes..) version (my additional commands to compress the tables can be removed, but if you are not cpu-bound this is faster with less storage) + current table definitions after using the sql-script can someone verify if "schema_after_transition.sql" does create an empty dbmail-database with all field-ytpes, constraints and view - i am getting slightly blind looking at this :-( below my transition-script and outputs ________________________________________ [root@dbmail-test:~]$ cat /data/dbmail3-migration/convert.sh #!/bin/bash systemctl stop dovecot.service systemctl stop dbmail-imapd.service systemctl stop dbmail-pop3d.service systemctl stop dbmail-timsieved.service systemctl stop dbmail-lmtpd.service logger "dbmail-migration started" sync mysql -f -u dbmail -p******** dbmail < sql-convert-2_2-3_0.sql dbmail-util -by dbmail-util -My -m 750000 sync logger "dbmail-migration finished" systemctl start dovecot.service systemctl start dbmail-imapd.service systemctl start dbmail-pop3d.service systemctl start dbmail-timsieved.service systemctl start dbmail-lmtpd.service ________________________________________ [root@dbmail-test:/data/dbmail3-migration]$ ./convert.sh Repairing DBMAIL for rfcsize field... Ok. Found [0] missing rfcsize values. Repairing DBMAIL for cached envelopes... Ok. Found [59104] missing envelope values. Am 14.02.2012 14:00, schrieb ITronic Harald Leithner: > Now drops the index of the sievescripts table regardless of the name. > Truncate the envelope table to prevent double key values.
-- mysql -f -u dbmail -p*** dbmail < sql-convert-2_2-3_0.sql
--
-- This script fixes the 2.1 database schema for use with 3.0
-- Running this script will drop legacy caching tables
-- so backup your database before continue
--
-- do not forget 'dbmail-util' after conversion
-- dbmail-util -by (caching-tables)
-- dbmail-util -My (new mimepart storage)
SET FOREIGN_KEY_CHECKS=0;
DROP PROCEDURE IF EXISTS dodropkeys;
DELIMITER //
CREATE PROCEDURE dodropkeys()
BEGIN
mainloop: LOOP
SELECT TABLE_NAME, CONSTRAINT_NAME INTO @droptable, @dropkey FROM information_schema.KEY_COLUMN_USAGE
WHERE `REFERENCED_TABLE_SCHEMA` LIKE DATABASE()
AND `REFERENCED_TABLE_NAME` IS NOT NULL ORDER BY CONSTRAINT_NAME LIMIT 1;
IF @dropkey IS NULL THEN
LEAVE mainloop;
END IF;
SET @sqltext := CONCAT('ALTER TABLE ', @droptable,' DROP FOREIGN KEY ', @dropkey);
PREPARE dropkeys FROM @sqltext;
EXECUTE dropkeys;
DEALLOCATE PREPARE dropkeys;
SET @dropkey = NULL;
END LOOP mainloop;
-- Workaround for different Upgrade pathes
SELECT index_name INTO @dropkey FROM information_schema.STATISTICS
WHERE table_schema like DATABASE()
AND table_name = "dbmail_sievescripts"
GROUP BY index_name
HAVING count(1) = 2;
IF @dropkey IS NOT NULL THEN
SET @sqltext := CONCAT('ALTER TABLE dbmail_sievescripts DROP INDEX ', @dropkey);
PREPARE dropkeys FROM @sqltext;
EXECUTE dropkeys;
DEALLOCATE PREPARE dropkeys;
SET @dropkey = NULL;
END IF;
END//
DELIMITER ;
CALL dodropkeys();
DROP PROCEDURE IF EXISTS dodropkeys;
-- Make sure our database is set for utf8
ALTER DATABASE CHARACTER SET utf8;
-- Delete old cached data for the references
BEGIN;
DELETE FROM `dbmail_referencesfield`;
COMMIT;
-- Drop old legacy cache tables
DROP TABLE IF EXISTS `dbmail_ccfield`;
DROP TABLE IF EXISTS `dbmail_datefield`;
DROP TABLE IF EXISTS `dbmail_fromfield`;
DROP TABLE IF EXISTS `dbmail_replytofield`;
DROP TABLE IF EXISTS `dbmail_subjectfield`;
DROP TABLE IF EXISTS `dbmail_tofield`;
-- Empty cache tables
-- re-create envelope table
DROP TABLE IF EXISTS `dbmail_envelope`;
CREATE TABLE `dbmail_envelope` (
`physmessage_id` BIGINT(20) unsigned NOT NULL DEFAULT '0',
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`envelope` TEXT NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `physmessage_id` (`physmessage_id`,`id`),
UNIQUE KEY `physmessage_id_2` (`physmessage_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;
-- create new storage tables
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) DEFAULT '0' NOT NULL,
PRIMARY KEY (`id`),
KEY `hash` (`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- message meta data
DROP TABLE IF EXISTS `dbmail_keywords`;
CREATE TABLE `dbmail_keywords` (
`message_idnr` BIGINT(20) UNSIGNED DEFAULT '0' NOT NULL,
`keyword` VARCHAR(255) NOT NULL,
PRIMARY KEY (`message_idnr`,`keyword`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Add new delivery sorting filters table
DROP TABLE IF EXISTS `dbmail_filters`;
CREATE TABLE `dbmail_filters` (
`user_id` BIGINT(20) UNSIGNED NOT NULL,
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`headername` VARCHAR(255) NOT NULL,
`headervalue` VARCHAR(255) NOT NULL,
`mailbox` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Add new authlog table
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;
-- Create new single instance header storage tables
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;
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;
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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Change columns
ALTER TABLE `dbmail_acl`
MODIFY COLUMN `user_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
MODIFY COLUMN `mailbox_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
ADD COLUMN `deleted_flag` TINYINT(1) NOT NULL DEFAULT '0',
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) UNSIGNED AUTO_INCREMENT NOT NULL,
MODIFY COLUMN `client_idnr` BIGINT(20) UNSIGNED DEFAULT '0' NOT NULL,
MODIFY COLUMN `alias` VARCHAR(255) NOT NULL DEFAULT '',
MODIFY COLUMN `deliver_to` VARCHAR(255) NOT NULL DEFAULT '';
ALTER TABLE `dbmail_auto_notifications`
MODIFY COLUMN `user_idnr` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `dbmail_auto_replies`
MODIFY COLUMN `user_idnr` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `dbmail_filters`
MODIFY COLUMN `user_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `dbmail_keywords`
MODIFY COLUMN `message_idnr` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `dbmail_mailboxes`
MODIFY COLUMN `owner_idnr` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
MODIFY COLUMN `mailbox_idnr` BIGINT(20) UNSIGNED AUTO_INCREMENT NOT NULL,
MODIFY COLUMN `name` VARCHAR(255) NOT NULL DEFAULT '',
ADD seq bigint DEFAULT '0' NOT NULL,
ADD KEY seq (seq);
ALTER TABLE `dbmail_messages`
MODIFY COLUMN `message_idnr` BIGINT(20) UNSIGNED AUTO_INCREMENT NOT NULL,
MODIFY COLUMN `physmessage_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
MODIFY COLUMN `mailbox_idnr` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `dbmail_pbsp`
MODIFY COLUMN `idnr` BIGINT(20) UNSIGNED AUTO_INCREMENT NOT NULL;
ALTER TABLE `dbmail_physmessage`
MODIFY COLUMN `id` BIGINT(20) UNSIGNED AUTO_INCREMENT NOT NULL,
MODIFY COLUMN `messagesize` BIGINT(20) UNSIGNED DEFAULT '0' NOT NULL,
MODIFY COLUMN `rfcsize` BIGINT(20) UNSIGNED DEFAULT '0' NOT NULL;
ALTER TABLE `dbmail_referencesfield`
MODIFY COLUMN `id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
MODIFY COLUMN `physmessage_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `dbmail_sievescripts`
MODIFY COLUMN `owner_idnr` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
MODIFY COLUMN `name` VARCHAR(255) NOT NULL,
ADD UNIQUE INDEX `dbmail_sievescripts_1` (`owner_idnr`,`name`);
ALTER TABLE `dbmail_subscription`
MODIFY COLUMN `user_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
MODIFY COLUMN `mailbox_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `dbmail_users`
MODIFY COLUMN `user_idnr` BIGINT(20) UNSIGNED AUTO_INCREMENT NOT NULL,
MODIFY COLUMN `client_idnr` BIGINT(20) UNSIGNED DEFAULT '0' NOT NULL,
MODIFY COLUMN `maxmail_size` BIGINT(20) DEFAULT '0' NOT NULL,
MODIFY COLUMN `curmail_size` BIGINT(20) DEFAULT '0' NOT NULL,
MODIFY COLUMN `maxsieve_size` BIGINT(20) DEFAULT '0' NOT NULL,
MODIFY COLUMN `cursieve_size` BIGINT(20) DEFAULT '0' NOT NULL,
MODIFY COLUMN `userid` VARCHAR(255) NOT NULL DEFAULT '',
MODIFY COLUMN `passwd` VARCHAR(255) NOT NULL DEFAULT '',
MODIFY COLUMN `encryption_type` VARCHAR(255) NOT NULL DEFAULT '';
ALTER TABLE `dbmail_replycache`
MODIFY COLUMN `to_addr` VARCHAR(255) NOT NULL DEFAULT '',
MODIFY COLUMN `from_addr` VARCHAR(255) NOT NULL DEFAULT '',
MODIFY COLUMN `handle` VARCHAR(255) NOT NULL DEFAULT '';
ALTER TABLE `dbmail_usermap`
MODIFY COLUMN `login` VARCHAR(255) NOT NULL,
MODIFY COLUMN `sock_allow` VARCHAR(255) NOT NULL,
MODIFY COLUMN `sock_deny` VARCHAR(255) NOT NULL,
MODIFY COLUMN `userid` VARCHAR(255) NOT NULL;
-- Create Foreign Keys and change columns
ALTER TABLE `dbmail_acl`
ADD CONSTRAINT `dbmail_acl_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `dbmail_acl_ibfk_2` FOREIGN KEY (`mailbox_id`) REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `dbmail_auto_notifications`
ADD CONSTRAINT `dbmail_auto_notifications_ibfk_1` FOREIGN KEY (`user_idnr`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `dbmail_auto_replies`
ADD CONSTRAINT `dbmail_auto_replies_ibfk_1` FOREIGN KEY (`user_idnr`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `dbmail_filters`
ADD CONSTRAINT `dbmail_filters_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `dbmail_header`
ADD CONSTRAINT `dbmail_header_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `dbmail_header_ibfk_2` FOREIGN KEY (`headername_id`) REFERENCES `dbmail_headername` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `dbmail_header_ibfk_3` FOREIGN KEY (`headervalue_id`) REFERENCES `dbmail_headervalue` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `dbmail_keywords`
ADD CONSTRAINT `dbmail_keywords_ibfk_1` FOREIGN KEY (`message_idnr`) REFERENCES `dbmail_messages` (`message_idnr`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `dbmail_mailboxes`
ADD CONSTRAINT `dbmail_mailboxes_ibfk_1` FOREIGN KEY (`owner_idnr`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `dbmail_messages`
ADD CONSTRAINT `dbmail_messages_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `dbmail_messages_ibfk_2` FOREIGN KEY (`mailbox_idnr`) REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `dbmail_partlists`
ADD CONSTRAINT `dbmail_partlists_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `dbmail_partlists_ibfk_2` FOREIGN KEY (`part_id`) REFERENCES `dbmail_mimeparts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `dbmail_referencesfield`
ADD CONSTRAINT `dbmail_referencesfield_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `dbmail_sievescripts`
ADD CONSTRAINT `dbmail_sievescripts_ibfk_1` FOREIGN KEY (`owner_idnr`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `dbmail_subscription`
ADD CONSTRAINT `dbmail_subscription_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `dbmail_subscription_ibfk_2` FOREIGN KEY (`mailbox_id`) REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON UPDATE CASCADE;
-- 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`
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';
-- compress tables (MySQL > 5.5.8)
ALTER TABLE `dbmail_mimeparts` ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
ALTER TABLE `dbmail_partlists` ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
ALTER TABLE `dbmail_keywords` ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
ALTER TABLE `dbmail_filters` ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
ALTER TABLE `dbmail_authlog` ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
ALTER TABLE `dbmail_headername` ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
ALTER TABLE `dbmail_headervalue` ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
ALTER TABLE `dbmail_header` ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
ALTER TABLE `dbmail_envelope` ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
SET FOREIGN_KEY_CHECKS=1;
DROP TABLE IF EXISTS `dbmail_acl`; CREATE TABLE IF NOT EXISTS `dbmail_acl` ( `user_id` bigint(20) unsigned NOT NULL DEFAULT '0', `mailbox_id` bigint(20) unsigned NOT NULL DEFAULT '0', `lookup_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `read_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `seen_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `write_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `insert_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `post_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `create_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `delete_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `administer_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `deleted_flag` tinyint(1) NOT NULL DEFAULT '0', `expunge_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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; DROP TABLE IF EXISTS `dbmail_aliases`; CREATE TABLE IF NOT EXISTS `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`), UNIQUE KEY `alias` (`alias`,`deliver_to`), KEY `alias_index` (`alias`), KEY `client_idnr_index` (`client_idnr`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; DROP TABLE IF EXISTS `dbmail_authlog`; CREATE TABLE IF NOT EXISTS `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 ROW_FORMAT=COMPRESSED; DROP TABLE IF EXISTS `dbmail_auto_notifications`; CREATE TABLE IF NOT EXISTS `dbmail_auto_notifications` ( `user_idnr` bigint(20) unsigned NOT NULL DEFAULT '0', `notify_address` varchar(100) NOT NULL, KEY `user_idnr_index` (`user_idnr`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; DROP TABLE IF EXISTS `dbmail_auto_replies`; CREATE TABLE IF NOT EXISTS `dbmail_auto_replies` ( `user_idnr` bigint(20) unsigned NOT NULL DEFAULT '0', `start_date` datetime NOT NULL, `stop_date` datetime NOT NULL, `reply_body` mediumtext, KEY `user_idnr_index` (`user_idnr`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; DROP VIEW IF EXISTS `dbmail_ccfield`; CREATE TABLE IF NOT EXISTS `dbmail_ccfield` ( `physmessage_id` bigint(20) unsigned ,`ccfield` varchar(255) ); DROP VIEW IF EXISTS `dbmail_datefield`; CREATE TABLE IF NOT EXISTS `dbmail_datefield` ( `physmessage_id` bigint(20) unsigned ,`datefield` datetime ); DROP TABLE IF EXISTS `dbmail_envelope`; CREATE TABLE IF NOT EXISTS `dbmail_envelope` ( `physmessage_id` bigint(20) unsigned NOT NULL DEFAULT '0', `id` bigint(20) NOT NULL AUTO_INCREMENT, `envelope` text NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `physmessage_id` (`physmessage_id`,`id`), UNIQUE KEY `physmessage_id_2` (`physmessage_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; DROP TABLE IF EXISTS `dbmail_filters`; CREATE TABLE IF NOT EXISTS `dbmail_filters` ( `user_id` bigint(20) unsigned NOT NULL DEFAULT '0', `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `headername` varchar(255) NOT NULL, `headervalue` varchar(255) NOT NULL, `mailbox` varchar(255) NOT NULL, PRIMARY KEY (`id`), KEY `dbmail_filters_ibfk_1` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; DROP VIEW IF EXISTS `dbmail_fromfield`; CREATE TABLE IF NOT EXISTS `dbmail_fromfield` ( `physmessage_id` bigint(20) unsigned ,`fromfield` varchar(255) ); DROP TABLE IF EXISTS `dbmail_header`; CREATE TABLE IF NOT EXISTS `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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; DROP TABLE IF EXISTS `dbmail_headername`; CREATE TABLE IF NOT EXISTS `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 ROW_FORMAT=COMPRESSED; DROP TABLE IF EXISTS `dbmail_headervalue`; CREATE TABLE IF NOT EXISTS `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 ROW_FORMAT=COMPRESSED; DROP TABLE IF EXISTS `dbmail_keywords`; CREATE TABLE IF NOT EXISTS `dbmail_keywords` ( `message_idnr` bigint(20) unsigned NOT NULL DEFAULT '0', `keyword` varchar(255) NOT NULL, PRIMARY KEY (`message_idnr`,`keyword`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; DROP TABLE IF EXISTS `dbmail_mailboxes`; CREATE TABLE IF NOT EXISTS `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(3) unsigned NOT NULL DEFAULT '0', `answered_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `deleted_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `flagged_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `recent_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `draft_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `no_inferiors` tinyint(3) unsigned NOT NULL DEFAULT '0', `no_select` tinyint(3) unsigned NOT NULL DEFAULT '0', `permission` tinyint(3) unsigned 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` (`seq`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; DROP TABLE IF EXISTS `dbmail_messageblks`; CREATE TABLE IF NOT EXISTS `dbmail_messageblks` ( `messageblk_idnr` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `physmessage_id` bigint(20) unsigned NOT NULL DEFAULT '0', `messageblk` longblob NOT NULL, `blocksize` bigint(20) unsigned NOT NULL DEFAULT '0', `is_header` tinyint(3) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`messageblk_idnr`), KEY `physmessage_id_index` (`physmessage_id`), KEY `physmessage_id_is_header_index` (`physmessage_id`,`is_header`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; DROP TABLE IF EXISTS `dbmail_messages`; CREATE TABLE IF NOT EXISTS `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(3) unsigned NOT NULL DEFAULT '0', `answered_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `deleted_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `flagged_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `recent_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', `draft_flag` tinyint(3) unsigned 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`), KEY `deleted_flag_index` (`deleted_flag`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; DROP TABLE IF EXISTS `dbmail_mimeparts`; CREATE TABLE IF NOT EXISTS `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 ROW_FORMAT=COMPRESSED; DROP TABLE IF EXISTS `dbmail_partlists`; CREATE TABLE IF NOT EXISTS `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', UNIQUE KEY `message_parts` (`physmessage_id`,`part_key`,`part_depth`,`part_order`), KEY `physmessage_id` (`physmessage_id`), KEY `part_id` (`part_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; DROP TABLE IF EXISTS `dbmail_pbsp`; CREATE TABLE IF NOT EXISTS `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 ROW_FORMAT=COMPRESSED; DROP TABLE IF EXISTS `dbmail_physmessage`; CREATE TABLE IF NOT EXISTS `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 ROW_FORMAT=COMPRESSED; DROP TABLE IF EXISTS `dbmail_referencesfield`; CREATE TABLE IF NOT EXISTS `dbmail_referencesfield` ( `physmessage_id` bigint(20) unsigned NOT NULL DEFAULT '0', `id` bigint(20) unsigned NOT NULL DEFAULT '0', `referencesfield` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`), UNIQUE KEY `physmessage_id` (`physmessage_id`,`referencesfield`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; DROP TABLE IF EXISTS `dbmail_replycache`; CREATE TABLE IF NOT EXISTS `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 ROW_FORMAT=COMPRESSED; DROP TABLE IF EXISTS `dbmail_sievescripts`; CREATE TABLE IF NOT EXISTS `dbmail_sievescripts` ( `owner_idnr` bigint(20) unsigned NOT NULL DEFAULT '0', `name` varchar(255) NOT NULL, `script` text, `active` tinyint(3) unsigned NOT NULL DEFAULT '0', UNIQUE KEY `dbmail_sievescripts_1` (`owner_idnr`,`name`), KEY `name` (`name`), KEY `owner_idnr` (`owner_idnr`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; DROP VIEW IF EXISTS `dbmail_subjectfield`; CREATE TABLE IF NOT EXISTS `dbmail_subjectfield` ( `physmessage_id` bigint(20) unsigned ,`subjectfield` text ); DROP TABLE IF EXISTS `dbmail_subscription`; CREATE TABLE IF NOT EXISTS `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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED; DROP VIEW IF EXISTS `dbmail_tofield`; CREATE TABLE IF NOT EXISTS `dbmail_tofield` ( `physmessage_id` bigint(20) unsigned ,`tofield` varchar(255) ); DROP TABLE IF EXISTS `dbmail_usermap`; CREATE TABLE IF NOT EXISTS `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 ROW_FORMAT=COMPRESSED; DROP TABLE IF EXISTS `dbmail_users`; CREATE TABLE IF NOT EXISTS `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', `user_realname` varchar(128) NOT NULL DEFAULT '', PRIMARY KEY (`user_idnr`), UNIQUE KEY `userid_index` (`userid`), KEY `client_idnr` (`client_idnr`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED ; DROP TABLE IF EXISTS `dbmail_ccfield`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `dbmail_ccfield` AS select `m`.`physmessage_id` AS `physmessage_id`,`v`.`sortfield` AS `ccfield` from (((`dbmail_messages` `m` join `dbmail_header` `h` on((`m`.`physmessage_id` = `h`.`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 TABLE IF EXISTS `dbmail_datefield`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `dbmail_datefield` AS select `m`.`physmessage_id` AS `physmessage_id`,`v`.`datefield` AS `datefield` from (((`dbmail_messages` `m` join `dbmail_header` `h` on((`m`.`physmessage_id` = `h`.`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'); DROP TABLE IF EXISTS `dbmail_fromfield`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `dbmail_fromfield` AS select `m`.`physmessage_id` AS `physmessage_id`,`v`.`sortfield` AS `fromfield` from (((`dbmail_messages` `m` join `dbmail_header` `h` on((`m`.`physmessage_id` = `h`.`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 TABLE IF EXISTS `dbmail_subjectfield`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `dbmail_subjectfield` AS select `m`.`physmessage_id` AS `physmessage_id`,`v`.`headervalue` AS `subjectfield` from (((`dbmail_messages` `m` join `dbmail_header` `h` on((`m`.`physmessage_id` = `h`.`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 TABLE IF EXISTS `dbmail_tofield`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `dbmail_tofield` AS select `m`.`physmessage_id` AS `physmessage_id`,`v`.`sortfield` AS `tofield` from (((`dbmail_messages` `m` join `dbmail_header` `h` on((`m`.`physmessage_id` = `h`.`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'); ALTER TABLE `dbmail_acl` ADD CONSTRAINT `dbmail_acl_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `dbmail_acl_ibfk_2` FOREIGN KEY (`mailbox_id`) REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_auto_notifications` ADD CONSTRAINT `dbmail_auto_notifications_ibfk_1` FOREIGN KEY (`user_idnr`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_auto_replies` ADD CONSTRAINT `dbmail_auto_replies_ibfk_1` FOREIGN KEY (`user_idnr`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_envelope` ADD CONSTRAINT `dbmail_envelope_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_filters` ADD CONSTRAINT `dbmail_filters_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_header` ADD CONSTRAINT `dbmail_header_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `dbmail_header_ibfk_2` FOREIGN KEY (`headername_id`) REFERENCES `dbmail_headername` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `dbmail_header_ibfk_3` FOREIGN KEY (`headervalue_id`) REFERENCES `dbmail_headervalue` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_keywords` ADD CONSTRAINT `dbmail_keywords_ibfk_1` FOREIGN KEY (`message_idnr`) REFERENCES `dbmail_messages` (`message_idnr`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_mailboxes` ADD CONSTRAINT `dbmail_mailboxes_ibfk_1` FOREIGN KEY (`owner_idnr`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_messages` ADD CONSTRAINT `dbmail_messages_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `dbmail_messages_ibfk_2` FOREIGN KEY (`mailbox_idnr`) REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_partlists` ADD CONSTRAINT `dbmail_partlists_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `dbmail_partlists_ibfk_2` FOREIGN KEY (`part_id`) REFERENCES `dbmail_mimeparts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_referencesfield` ADD CONSTRAINT `dbmail_referencesfield_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_sievescripts` ADD CONSTRAINT `dbmail_sievescripts_ibfk_1` FOREIGN KEY (`owner_idnr`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `dbmail_subscription` ADD CONSTRAINT `dbmail_subscription_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `dbmail_subscription_ibfk_2` FOREIGN KEY (`mailbox_id`) REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON UPDATE CASCADE;
signature.asc
Description: OpenPGP digital signature
_______________________________________________ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
