+
−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 pkye_users;
+
−ALTER TABLE users ADD CONSTRAINT  pkey_users PRIMARY KEY (local_part, gid);
+
−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 DROP CONSTRAINT pkey_alias;
+
−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);
+
−
+
−DROP VIEW postfix_alias;
+
−DROP VIEW vmm_domain_info;
+
−DROP VIEW vmm_alias_count;
+
−
+
−ALTER TABLE alias ALTER address TYPE varchar(64);
+
−ALTER TABLE alias ADD CONSTRAINT pkey_alias 
+
−    PRIMARY KEY (gid, address, destination);
+
−
+
−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);
+
−
+
−CREATE OR REPLACE VIEW vmm_domain_info AS
+
−    SELECT gid, domainname, transport, domaindir,
+
−           count(uid) AS accounts,
+
−           (SELECT count(DISTINCT address)
+
−              FROM alias
+
−             WHERE alias.gid = domain_data.gid) AS aliases,
+
−           (SELECT count(gid)
+
−              FROM relocated
+
−             WHERE relocated.gid = domain_data.gid) AS relocated,
+
−           (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)
+
−     WHERE domain_name.is_primary
+
−  GROUP BY gid, domainname, transport, domaindir;
+
−
+
−
+
−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;
+
−
+
−DROP TRIGGER IF EXISTS primary_count ON domain_name;
+
−CREATE TRIGGER primary_count_ins BEFORE INSERT ON domain_name
+
−    FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();
+
−
+
−CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name
+
−    FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();