1 SET client_encoding = 'UTF8';

     2 SET client_min_messages = warning;

     3 

     4 ALTER SEQUENCE domains_gid RENAME TO domain_gid;

     5 

     6 

     7 CREATE TABLE domain_data (

     8     gid         bigint NOT NULL DEFAULT nextval('domain_gid'),

     9     tid         bigint NOT NULL DEFAULT 1,

    10     domaindir   varchar(40) NOT NULL,

    11     CONSTRAINT  pkey_domain_data PRIMARY KEY (gid),

    12     CONSTRAINT  fkey_domain_data_tid_transport FOREIGN KEY (tid)

    13         REFERENCES transport (tid)

    14 );

    15 

    16 CREATE TABLE domain_name (

    17     domainname  varchar(255) NOT NULL,

    18     gid         bigint NOT NULL,

    19     is_primary  boolean NOT NULL,

    20     CONSTRAINT  pkey_domain_name PRIMARY KEY (domainname),

    21     CONSTRAINT  fkey_domain_name_gid_domain_data FOREIGN KEY (gid)

    22         REFERENCES domain_data (gid)

    23 );

    24 

    25 INSERT INTO domain_data (gid, tid, domaindir) 

    26     SELECT gid, tid, domaindir

    27       FROM domains;

    28 

    29 INSERT INTO domain_name (domainname, gid, is_primary) 

    30     SELECT domainname, gid, TRUE

    31       FROM domains;

    32 

    33 

    34 ALTER TABLE users DROP CONSTRAINT pkye_users;

    35 ALTER TABLE users ADD CONSTRAINT  pkey_users PRIMARY KEY (local_part, gid);

    36 ALTER TABLE users DROP CONSTRAINT fkey_users_gid_domains;

    37 ALTER TABLE users ADD CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid)

    38     REFERENCES domain_data (gid);

    39 

    40 ALTER TABLE alias DROP CONSTRAINT fkey_alias_gid_domains;

    41 ALTER TABLE alias DROP CONSTRAINT pkey_alias;

    42 ALTER TABLE alias ADD CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid)

    43     REFERENCES domain_data (gid);

    44 

    45 ALTER TABLE relocated DROP CONSTRAINT fkey_relocated_gid_domains;

    46 ALTER TABLE relocated ADD CONSTRAINT fkey_relocated_gid_domain_data

    47     FOREIGN KEY (gid) REFERENCES domain_data (gid);

    48 

    49 

    50 CREATE OR REPLACE VIEW dovecot_password AS

    51     SELECT local_part || '@' || domain_name.domainname AS "user",

    52            passwd AS "password", smtp, pop3, imap, managesieve

    53       FROM users

    54            LEFT JOIN domain_name USING (gid);

    55 

    56 CREATE OR REPLACE VIEW dovecot_user AS

    57     SELECT local_part || '@' || domain_name.domainname AS userid,

    58            uid, gid, domain_data.domaindir || '/' || uid AS home,

    59            '~/' || maillocation.maillocation AS mail

    60       FROM users

    61            LEFT JOIN domain_data USING (gid)

    62            LEFT JOIN domain_name USING (gid)

    63            LEFT JOIN maillocation USING (mid);

    64 

    65 CREATE OR REPLACE VIEW postfix_gid AS

    66     SELECT gid, domainname

    67       FROM domain_name;

    68 

    69 CREATE OR REPLACE VIEW postfix_uid AS

    70     SELECT local_part || '@' || domain_name.domainname AS address, uid

    71       FROM users

    72            LEFT JOIN domain_name USING (gid);

    73 

    74 CREATE OR REPLACE VIEW postfix_maildir AS

    75     SELECT local_part || '@' || domain_name.domainname AS address,

    76            domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/'

    77            AS maildir

    78       FROM users

    79            LEFT JOIN domain_data USING (gid)

    80            LEFT JOIN domain_name USING (gid)

    81            LEFT JOIN maillocation USING (mid);

    82 

    83 CREATE OR REPLACE VIEW postfix_relocated AS

    84     SELECT address || '@' || domain_name.domainname AS address, destination

    85       FROM relocated

    86            LEFT JOIN domain_name USING (gid);

    87 

    88 DROP VIEW postfix_alias;

    89 DROP VIEW vmm_domain_info;

    90 DROP VIEW vmm_alias_count;

    91 

    92 ALTER TABLE alias ALTER address TYPE varchar(64);

    93 ALTER TABLE alias ADD CONSTRAINT pkey_alias 

    94     PRIMARY KEY (gid, address, destination);

    95 

    96 CREATE OR REPLACE VIEW postfix_alias AS

    97     SELECT address || '@' || domain_name.domainname AS address, destination, gid

    98       FROM alias

    99            LEFT JOIN domain_name USING (gid);

   100 

   101 CREATE OR REPLACE VIEW postfix_transport AS

   102     SELECT local_part || '@' || domain_name.domainname AS address,

   103            transport.transport

   104       FROM users

   105            LEFT JOIN transport USING (tid)

   106            LEFT JOIN domain_name USING (gid);

   107 

   108 CREATE OR REPLACE VIEW vmm_domain_info AS

   109     SELECT gid, domainname, transport, domaindir,

   110            count(uid) AS accounts,

   111            (SELECT count(DISTINCT address)

   112               FROM alias

   113              WHERE alias.gid = domain_data.gid) AS aliases,

   114            (SELECT count(gid)

   115               FROM relocated

   116              WHERE relocated.gid = domain_data.gid) AS relocated,

   117            (SELECT count(gid)

   118               FROM domain_name

   119              WHERE domain_name.gid = domain_data.gid

   120                AND NOT domain_name.is_primary) AS aliasdomains

   121       FROM domain_data

   122            LEFT JOIN domain_name USING (gid)

   123            LEFT JOIN transport USING (tid)

   124            LEFT JOIN users USING (gid)

   125      WHERE domain_name.is_primary

   126   GROUP BY gid, domainname, transport, domaindir;

   127 

   128 

   129 DROP TABLE domains;

   130 

   131 

   132 CREATE LANGUAGE plpgsql;

   133 

   134 CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$

   135 DECLARE

   136     primary_count bigint;

   137 BEGIN

   138     SELECT INTO primary_count count(gid) + NEW.is_primary::integer

   139       FROM domain_name

   140      WHERE domain_name.gid = NEW.gid

   141        AND is_primary;

   142 

   143     IF (primary_count > 1) THEN

   144         RAISE EXCEPTION 'There can only be one domain marked as primary.';

   145     END IF;

   146 

   147     RETURN NEW;

   148 END;

   149 $$ LANGUAGE plpgsql STABLE;

   150 

   151 DROP TRIGGER IF EXISTS primary_count ON domain_name;

   152 CREATE TRIGGER primary_count_ins BEFORE INSERT ON domain_name

   153     FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();

   154 

   155 CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name

   156     FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();