--- a/UPGRADE	Thu Aug 07 22:27:29 2008 +0000
+++ b/UPGRADE	Thu Aug 14 00:33:31 2008 +0000
@@ -1,8 +1,21 @@
 If you still have installed vmm 0.3.x you have to proceed this step first:
-    * upgrade your vmm installation to version 0.4
+
+    * upgrade your vmm installation to version 0.4-r41
+
+
+If you have installed vmm 0.4/0.4-r41 you have to proceed this steps:
 
-If you have installed vmm 0.4 you have to proceed this step:
-   * execute upgrade.sh
+    * stop Postfix and Dovecot
+    * backup/dump your database.
+    * backup/dump your database!
+    
+    * start psql and connect to the appropriate database (ex. psql mailsys)
+    * update the database: \i update_tables_0.4.x-0.5.pgsql
+
+    * execute upgrade.sh
+
+    * start Dovecot and Postfix
+
 
 else
      * read INSTALL

--- a/create_tables.pgsql	Thu Aug 07 22:27:29 2008 +0000
+++ b/create_tables.pgsql	Thu Aug 14 00:33:31 2008 +0000
@@ -1,26 +1,14 @@
 -- $Id$ 
 
+SET client_encoding = 'UTF8';
+SET client_min_messages = warning;
+
+
 CREATE SEQUENCE transport_id;
-CREATE TABLE transport (
-    tid         bigint NOT NULL DEFAULT nextval('transport_id'),
-    transport   varchar(270) NOT NULL, -- smtps:[255-char.host.name:50025]
-    CONSTRAINT pkey_transport PRIMARY KEY (tid),
-    CONSTRAINT ukey_transport UNIQUE (transport)
-);
--- Insert default transport
-INSERT INTO transport(transport) VALUES ('dovecot:');
 
 CREATE SEQUENCE maillocation_id;
-CREATE TABLE maillocation(
-    mid     bigint NOT NULL DEFAULT nextval('maillocation_id'),
-    maillocation varchar(20) NOT NULL,
-    CONSTRAINT pkey_maillocation PRIMARY KEY (mid),
-    CONSTRAINT ukey_maillocation UNIQUE (maillocation)
-);
--- Insert default Maildir-folder name
-INSERT INTO maillocation(maillocation) VALUES ('Maildir');
 
-CREATE SEQUENCE domains_gid
+CREATE SEQUENCE domain_gid
     START WITH 70000
     INCREMENT BY 1
     MINVALUE 70000
@@ -34,17 +22,43 @@
     MAXVALUE 4294967294
     NO CYCLE;
 
-CREATE TABLE domains (
-    gid         bigint NOT NULL DEFAULT nextval('domains_gid'),
+
+CREATE TABLE transport (
+    tid         bigint NOT NULL DEFAULT nextval('transport_id'),
+    transport   varchar(270) NOT NULL, -- smtps:[255-char.host.name:50025]
+    CONSTRAINT  pkey_transport PRIMARY KEY (tid),
+    CONSTRAINT  ukey_transport UNIQUE (transport)
+);
+-- Insert default transport
+INSERT INTO transport(transport) VALUES ('dovecot:');
+
+CREATE TABLE maillocation(
+    mid     bigint NOT NULL DEFAULT nextval('maillocation_id'),
+    maillocation varchar(20) NOT NULL,
+    CONSTRAINT  pkey_maillocation PRIMARY KEY (mid),
+    CONSTRAINT  ukey_maillocation UNIQUE (maillocation)
+);
+-- Insert default Maildir-folder name
+INSERT INTO maillocation(maillocation) VALUES ('Maildir');
+
+CREATE TABLE domain_data (
+    gid         bigint NOT NULL DEFAULT nextval('domain_gid'),
     tid         bigint NOT NULL DEFAULT 1, -- defualt transport
-    domainname  varchar(255) NOT NULL,
     domaindir   varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294
-    CONSTRAINT pkey_domains PRIMARY KEY (gid),
-    CONSTRAINT ukey_domains UNIQUE (domainname),
-    CONSTRAINT fkey_domains_tid_transport FOREIGN KEY (tid)
+    CONSTRAINT  pkey_domain_data PRIMARY KEY (gid),
+    CONSTRAINT  fkey_domain_data_tid_transport FOREIGN KEY (tid)
         REFERENCES transport (tid)
 );
 
+CREATE TABLE domain_name (
+    domainname  varchar(255) NOT NULL,
+    gid         bigint NOT NULL,
+    is_primary  boolean NOT NULL,
+    CONSTRAINT  pkey_domain_name PRIMARY KEY (domainname),
+    CONSTRAINT  fkey_domain_name_gid_domain_data FOREIGN KEY (gid)
+        REFERENCES domain_data (gid)
+);
+
 CREATE TABLE users (
     local_part  varchar(64) NOT NULL,-- only localpart w/o '@'
     passwd      varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers
@@ -57,13 +71,13 @@
     pop3        boolean NOT NULL DEFAULT TRUE,
     imap        boolean NOT NULL DEFAULT TRUE,
     managesieve boolean NOT NULL DEFAULT TRUE,
-    CONSTRAINT pkye_users PRIMARY KEY (local_part, gid),
-    CONSTRAINT ukey_users_uid UNIQUE (uid),
-    CONSTRAINT fkey_users_gid_domains FOREIGN KEY (gid)
-        REFERENCES domains (gid),
-    CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid)
+    CONSTRAINT  pkye_users PRIMARY KEY (local_part, gid),
+    CONSTRAINT  ukey_users_uid UNIQUE (uid),
+    CONSTRAINT  fkey_users_gid_domain_data FOREIGN KEY (gid)
+        REFERENCES domain_data (gid),
+    CONSTRAINT  fkey_users_mid_maillocation FOREIGN KEY (mid)
         REFERENCES maillocation (mid),
-    CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid)
+    CONSTRAINT  fkey_users_tid_transport FOREIGN KEY (tid)
         REFERENCES transport (tid)
 );
 
@@ -71,66 +85,69 @@
     gid         bigint NOT NULL,
     address     varchar(256) NOT NULL,
     destination varchar(320) NOT NULL,
-    CONSTRAINT pkey_alias PRIMARY KEY (gid, address, destination),
-    CONSTRAINT fkey_alias_gid_domains FOREIGN KEY (gid)
-        REFERENCES domains (gid)
+    CONSTRAINT  pkey_alias PRIMARY KEY (gid, address, destination),
+    CONSTRAINT  fkey_alias_gid_domain_data FOREIGN KEY (gid)
+        REFERENCES domain_data (gid)
 );
 
 CREATE TABLE relocated (
     gid         bigint NOT NULL,
     address     varchar(64) NOT NULL,
     destination varchar(320) NOT NULL,
-    CONSTRAINT pkey_relocated PRIMARY KEY (gid, address),
-    CONSTRAINT fkey_relocated_gid_domains FOREIGN KEY (gid)
-        REFERENCES domains (gid)
+    CONSTRAINT  pkey_relocated PRIMARY KEY (gid, address),
+    CONSTRAINT  fkey_relocated_gid_domain_data FOREIGN KEY (gid)
+        REFERENCES domain_data (gid)
 );
 
 CREATE OR REPLACE VIEW dovecot_password AS
-    SELECT local_part || '@' || domains.domainname AS "user",
+    SELECT local_part || '@' || domain_name.domainname AS "user",
            passwd AS "password", smtp, pop3, imap, managesieve
       FROM users
-           LEFT JOIN domains USING (gid);
+           LEFT JOIN domain_name USING (gid);
 
 CREATE OR REPLACE VIEW dovecot_user AS
-    SELECT local_part || '@' || domains.domainname AS userid,
-           uid, gid, domains.domaindir || '/' || uid AS home,
+    SELECT local_part || '@' || domain_name.domainname AS userid,
+           uid, gid, domain_data.domaindir || '/' || uid AS home,
            '~/' || maillocation.maillocation AS mail
       FROM users
-           LEFT JOIN domains USING (gid)
+           LEFT JOIN domain_data USING (gid)
+           LEFT JOIN domain_name USING (gid)
            LEFT JOIN maillocation USING (mid);
 
 CREATE OR REPLACE VIEW postfix_gid AS
     SELECT gid, domainname
-      FROM domains;
+      FROM domain_name;
 
 CREATE OR REPLACE VIEW postfix_uid AS
-    SELECT local_part || '@' || domains.domainname AS address, uid
+    SELECT local_part || '@' || domain_name.domainname AS address, uid
       FROM users
-           LEFT JOIN domains USING (gid);
+           LEFT JOIN domain_name USING (gid);
 
 CREATE OR REPLACE VIEW postfix_maildir AS
-    SELECT local_part || '@' || domains.domainname AS address,
-           domains.domaindir||'/'||uid||'/'||maillocation.maillocation||'/' AS maildir
+    SELECT local_part || '@' || domain_name.domainname AS address,
+           domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/'
+           AS maildir
       FROM users
-           LEFT JOIN domains USING (gid)
+           LEFT JOIN domain_data USING (gid)
+           LEFT JOIN domain_name USING (gid)
            LEFT JOIN maillocation USING (mid);
 
 CREATE OR REPLACE VIEW postfix_relocated AS
-    SELECT address || '@' || domains.domainname AS address, destination
+    SELECT address || '@' || domain_name.domainname AS address, destination
       FROM relocated
-           LEFT JOIN domains USING (gid);
+           LEFT JOIN domain_name USING (gid);
 
 CREATE OR REPLACE VIEW postfix_alias AS
-    SELECT address || '@' || domains.domainname AS address, destination, gid
+    SELECT address || '@' || domain_name.domainname AS address, destination, gid
       FROM alias
-           LEFT JOIN domains USING (gid);
+           LEFT JOIN domain_name USING (gid);
 
 CREATE OR REPLACE VIEW postfix_transport AS
-    SELECT local_part || '@' || domains.domainname AS address,
+    SELECT local_part || '@' || domain_name.domainname AS address,
            transport.transport
       FROM users
            LEFT JOIN transport USING (tid)
-           LEFT JOIN domains USING (gid);
+           LEFT JOIN domain_name USING (gid);
 
 CREATE OR REPLACE VIEW vmm_alias_count AS
     SELECT count(DISTINCT address) AS aliases, gid
@@ -138,10 +155,42 @@
   GROUP BY gid;
 
 CREATE OR REPLACE VIEW vmm_domain_info AS
-    SELECT gid, domainname, transport, domaindir, count(uid) AS accounts,
-           aliases
-      FROM domains
+    SELECT gid, domainname, transport, domaindir,
+           count(uid) AS accounts,
+           aliases,
+           (SELECT count(gid)
+              FROM domain_name
+             WHERE domain_name.gid = domain_data.gid
+               AND NOT domain_name.is_primary) AS aliasdomains
+      FROM domain_data
+           LEFT JOIN domain_name USING (gid)
            LEFT JOIN transport USING (tid)
            LEFT JOIN users USING (gid)
            LEFT JOIN vmm_alias_count USING (gid)
+     WHERE domain_name.is_primary
   GROUP BY gid, domainname, transport, domaindir, aliases;
+
+
+CREATE LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$
+DECLARE
+    primary_count bigint;
+BEGIN
+    SELECT INTO primary_count count(gid) + NEW.is_primary::integer
+      FROM domain_name
+     WHERE domain_name.gid = NEW.gid
+       AND is_primary;
+
+    IF (primary_count > 1) THEN
+        RAISE EXCEPTION 'There can only be one domain marked as primary.';
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql STABLE;
+
+
+CREATE TRIGGER primary_count BEFORE INSERT OR UPDATE ON domain_name
+    FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/update_tables_0.4.x-0.5.pgsql	Thu Aug 14 00:33:31 2008 +0000
@@ -0,0 +1,140 @@
+-- $Id$ 
+
+SET client_encoding = 'UTF8';
+SET client_min_messages = warning;
+
+ALTER SEQUENCE domains_gid RENAME TO domain_gid;
+
+
+CREATE TABLE domain_data (
+    gid         bigint NOT NULL DEFAULT nextval('domain_gid'),
+    tid         bigint NOT NULL DEFAULT 1,
+    domaindir   varchar(40) NOT NULL,
+    CONSTRAINT  pkey_domain_data PRIMARY KEY (gid),
+    CONSTRAINT  fkey_domain_data_tid_transport FOREIGN KEY (tid)
+        REFERENCES transport (tid)
+);
+
+CREATE TABLE domain_name (
+    domainname  varchar(255) NOT NULL,
+    gid         bigint NOT NULL,
+    is_primary  boolean NOT NULL,
+    CONSTRAINT  pkey_domain_name PRIMARY KEY (domainname),
+    CONSTRAINT  fkey_domain_name_gid_domain_data FOREIGN KEY (gid)
+        REFERENCES domain_data (gid)
+);
+
+INSERT INTO domain_data (gid, tid, domaindir) 
+    SELECT gid, tid, domaindir
+      FROM domains;
+
+INSERT INTO domain_name (domainname, gid, is_primary) 
+    SELECT domainname, gid, TRUE
+      FROM domains;
+
+
+ALTER TABLE users DROP CONSTRAINT fkey_users_gid_domains;
+ALTER TABLE users ADD CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid)
+    REFERENCES domain_data (gid);
+
+ALTER TABLE alias DROP CONSTRAINT fkey_alias_gid_domains;
+ALTER TABLE alias ADD CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid)
+    REFERENCES domain_data (gid);
+
+ALTER TABLE relocated DROP CONSTRAINT fkey_relocated_gid_domains;
+ALTER TABLE relocated ADD CONSTRAINT fkey_relocated_gid_domain_data
+    FOREIGN KEY (gid) REFERENCES domain_data (gid);
+
+
+CREATE OR REPLACE VIEW dovecot_password AS
+    SELECT local_part || '@' || domain_name.domainname AS "user",
+           passwd AS "password", smtp, pop3, imap, managesieve
+      FROM users
+           LEFT JOIN domain_name USING (gid);
+
+CREATE OR REPLACE VIEW dovecot_user AS
+    SELECT local_part || '@' || domain_name.domainname AS userid,
+           uid, gid, domain_data.domaindir || '/' || uid AS home,
+           '~/' || maillocation.maillocation AS mail
+      FROM users
+           LEFT JOIN domain_data USING (gid)
+           LEFT JOIN domain_name USING (gid)
+           LEFT JOIN maillocation USING (mid);
+
+CREATE OR REPLACE VIEW postfix_gid AS
+    SELECT gid, domainname
+      FROM domain_name;
+
+CREATE OR REPLACE VIEW postfix_uid AS
+    SELECT local_part || '@' || domain_name.domainname AS address, uid
+      FROM users
+           LEFT JOIN domain_name USING (gid);
+
+CREATE OR REPLACE VIEW postfix_maildir AS
+    SELECT local_part || '@' || domain_name.domainname AS address,
+           domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/'
+           AS maildir
+      FROM users
+           LEFT JOIN domain_data USING (gid)
+           LEFT JOIN domain_name USING (gid)
+           LEFT JOIN maillocation USING (mid);
+
+CREATE OR REPLACE VIEW postfix_relocated AS
+    SELECT address || '@' || domain_name.domainname AS address, destination
+      FROM relocated
+           LEFT JOIN domain_name USING (gid);
+
+CREATE OR REPLACE VIEW postfix_alias AS
+    SELECT address || '@' || domain_name.domainname AS address, destination, gid
+      FROM alias
+           LEFT JOIN domain_name USING (gid);
+
+CREATE OR REPLACE VIEW postfix_transport AS
+    SELECT local_part || '@' || domain_name.domainname AS address,
+           transport.transport
+      FROM users
+           LEFT JOIN transport USING (tid)
+           LEFT JOIN domain_name USING (gid);
+
+DROP VIEW vmm_domain_info;
+CREATE OR REPLACE VIEW vmm_domain_info AS
+    SELECT gid, domainname, transport, domaindir,
+           count(uid) AS accounts,
+           aliases,
+           (SELECT count(gid)
+              FROM domain_name
+             WHERE domain_name.gid = domain_data.gid
+               AND NOT domain_name.is_primary) AS aliasdomains
+      FROM domain_data
+           LEFT JOIN domain_name USING (gid)
+           LEFT JOIN transport USING (tid)
+           LEFT JOIN users USING (gid)
+           LEFT JOIN vmm_alias_count USING (gid)
+     WHERE domain_name.is_primary
+  GROUP BY gid, domainname, transport, domaindir, aliases;
+
+
+DROP TABLE domains;
+
+
+CREATE LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$
+DECLARE
+    primary_count bigint;
+BEGIN
+    SELECT INTO primary_count count(gid) + NEW.is_primary::integer
+      FROM domain_name
+     WHERE domain_name.gid = NEW.gid
+       AND is_primary;
+
+    IF (primary_count > 1) THEN
+        RAISE EXCEPTION 'There can only be one domain marked as primary.';
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql STABLE;
+
+CREATE TRIGGER primary_count BEFORE INSERT OR UPDATE ON domain_name
+    FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();