[vhffs-dev] [2146] compat SQL script now create the new mail tables before migrating the data

[subversion@xxxxxxxxxxxxx]

Revision: 2146
Author:   gradator
Date:     2012-04-12 00:10:17 +0200 (Thu, 12 Apr 2012)
Log Message:
-----------
compat SQL script now create the new mail tables before migrating the data

Modified Paths:
--------------
    trunk/vhffs-compat/from-4.4-to-4.5.sql

Modified: trunk/vhffs-compat/from-4.4-to-4.5.sql
===================================================================
--- trunk/vhffs-compat/from-4.4-to-4.5.sql	2012-04-11 22:01:48 UTC (rev 2145)
+++ trunk/vhffs-compat/from-4.4-to-4.5.sql	2012-04-11 22:10:17 UTC (rev 2146)
@@ -44,6 +44,133 @@
 
 -- Migration to the new VHFFS mail database
 
+-- Mail domains
+CREATE TABLE vhffs_mx (
+	mx_id serial,
+-- Domain name
+	domain varchar NOT NULL,
+	object_id int4 NOT NULL,
+	CONSTRAINT vhffs_mx_pkey PRIMARY KEY (mx_id),
+	CONSTRAINT vhffs_mx_unique_domain UNIQUE (domain),
+	CONSTRAINT vhffs_mx_unique_object_id UNIQUE (object_id)
+) WITH (OIDS);
+
+-- Catchall boxes
+CREATE TABLE vhffs_mx_catchall (
+	catchall_id serial,
+-- Mail domain
+	mx_id int4 NOT NULL,
+-- Box
+	box_id int4 NOT NULL,
+	CONSTRAINT vhffs_mx_catchall_pkey PRIMARY KEY (catchall_id),
+	CONSTRAINT vhffs_mx_catchall_unique_domain_box UNIQUE (mx_id, box_id)
+) WITH (OIDS);
+
+-- Mail localparts
+CREATE TABLE vhffs_mx_localpart (
+	localpart_id serial,
+-- Mail domain
+	mx_id int4 NOT NULL,
+-- Local part of the address (part before @)
+	localpart varchar NOT NULL,
+-- Password (of the box and/or a future redirect administration)
+	password varchar,
+-- Is antispam activated ?
+	nospam boolean NOT NULL DEFAULT FALSE,
+-- Is antivirus activated ?
+	novirus boolean NOT NULL DEFAULT FALSE,
+	CONSTRAINT vhffs_mx_localpart_pkey PRIMARY KEY (localpart_id),
+	CONSTRAINT vhffs_mx_localpart_unique_domain_localpart UNIQUE (mx_id, localpart)
+) WITH (OIDS);
+
+-- Mail redirects
+CREATE TABLE vhffs_mx_redirect (
+	redirect_id serial,
+-- Local part
+	localpart_id int4 NOT NULL,
+-- Mail address to which mails are forwarded
+	redirect varchar NOT NULL,
+	CONSTRAINT vhffs_mx_redirect_pkey PRIMARY KEY (redirect_id),
+	CONSTRAINT vhffs_mx_redirect_unique_localpart_redirect UNIQUE (localpart_id, redirect)
+) WITH (OIDS);
+
+-- Mail boxes
+CREATE TABLE vhffs_mx_box (
+	box_id serial,
+-- Local part
+	localpart_id int4 NOT NULL,
+-- Allow pop login ?
+	allowpop boolean NOT NULL DEFAULT TRUE,
+-- Allow imap login ?
+	allowimap boolean NOT NULL DEFAULT TRUE,
+-- State of the box (we don't have object for this entity...)
+	state int4 NOT NULL,
+	CONSTRAINT vhffs_mx_box_pkey PRIMARY KEY (box_id),
+	CONSTRAINT vhffs_mx_box_unique_domain_localpart UNIQUE (localpart_id)
+) WITH (OIDS);
+-- state is used in vhffs_mx_box in where clauses
+CREATE INDEX idx_vhffs_mx_box_state ON vhffs_mx_box(state);
+
+-- Mailing lists
+CREATE TABLE vhffs_mx_ml (
+	ml_id serial,
+-- Local part
+	localpart_id int4 NOT NULL,
+-- Object
+	object_id int4 NOT NULL,
+-- Prefix prepended to all subjects
+	prefix varchar,
+-- How are subscriptions managed
+	sub_ctrl int4 NOT NULL,
+-- Posting policy
+	post_ctrl int4 NOT NULL,
+-- Add Reply-To header?
+	reply_to boolean NOT NULL DEFAULT FALSE,
+-- Do we keep open archives for this list?
+	open_archive boolean NOT NULL DEFAULT FALSE,
+-- Signature appended to all messages
+	signature text,
+	CONSTRAINT vhffs_mx_ml_pkey PRIMARY KEY (ml_id),
+	CONSTRAINT vhffs_mx_ml_unique_domain_localpart UNIQUE (localpart_id),
+	CONSTRAINT vhffs_mx_ml_unique_object_id UNIQUE (object_id)
+) WITH (OIDS);
+-- vhffs_mx_ml.open_archive may be used in where clause to select on public ml
+CREATE INDEX idx_vhffs_mx_ml_open_archive ON vhffs_mx_ml(open_archive);
+
+-- Subscribers of a mailing list
+CREATE TABLE vhffs_mx_ml_subscribers (
+	sub_id serial,
+-- Mailing list to which this address has subscribed
+	ml_id int4 NOT NULL,
+-- Email address of the subscriber
+	member varchar NOT NULL,
+-- Access level of this member
+	perm int4 NOT NULL,
+-- Hash for activation
+	hash varchar,
+-- Language of the subscriber
+	language varchar(16),
+	CONSTRAINT vhffs_mx_ml_subscribers_pkey PRIMARY KEY (sub_id),
+	CONSTRAINT vhffs_mx_ml_subscribers_member_list UNIQUE (ml_id, member)
+) WITH (OIDS);
+
+
+ALTER TABLE vhffs_mx ADD CONSTRAINT fk_vhffs_mx_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
+ALTER TABLE vhffs_mx_ml ADD CONSTRAINT fk_vhffs_mx_ml_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
+
+ALTER TABLE vhffs_mx_catchall ADD CONSTRAINT fk_vhffs_mx_catchall_vhffs_mx FOREIGN KEY (mx_id) REFERENCES vhffs_mx(mx_id) ON DELETE CASCADE;
+ALTER TABLE vhffs_mx_catchall ADD CONSTRAINT fk_vhffs_mx_catchall_vhffs_mx_box FOREIGN KEY (box_id) REFERENCES vhffs_mx_box(box_id) ON DELETE CASCADE;
+
+ALTER TABLE vhffs_mx_localpart ADD CONSTRAINT fk_vhffs_mx_localpart_vhffs_mx FOREIGN KEY (mx_id) REFERENCES vhffs_mx(mx_id) ON DELETE CASCADE;
+
+ALTER TABLE vhffs_mx_redirect ADD CONSTRAINT fk_vhffs_mx_redirect_vhffs_localpart FOREIGN KEY (localpart_id) REFERENCES vhffs_mx_localpart(localpart_id) ON DELETE CASCADE;
+ALTER TABLE vhffs_mx_box ADD CONSTRAINT fk_vhffs_mx_box_vhffs_localpart FOREIGN KEY (localpart_id) REFERENCES vhffs_mx_localpart(localpart_id) ON DELETE CASCADE;
+ALTER TABLE vhffs_mx_ml ADD CONSTRAINT fk_vhffs_mx_ml_vhffs_localpart FOREIGN KEY (localpart_id) REFERENCES vhffs_mx_localpart(localpart_id) ON DELETE CASCADE;
+
+ALTER TABLE vhffs_mx_ml_subscribers ADD CONSTRAINT fk_vhffs_mx_ml_subscribers_vhffs_mx_ml FOREIGN KEY (ml_id) REFERENCES vhffs_mx_ml(ml_id) ON DELETE CASCADE;
+
+-- Migrate data from previous mail tables
+
 -- fill vhffs_mx from former vhffs_mxdomain
 INSERT INTO vhffs_mx (domain, object_id) SELECT domain, object_id FROM vhffs_mxdomain;