1 -- $Id$ 

     2 

     3 SET client_encoding = 'UTF8';

     4 SET client_min_messages = warning;

     5 

     6 ALTER SEQUENCE domains_gid RENAME TO domain_gid;

     7 

     8 

     9 CREATE TABLE domain_data (

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

    11     tid         bigint NOT NULL DEFAULT 1,

    12     domaindir   varchar(40) NOT NULL,

    13     CONSTRAINT  pkey_domain_data PRIMARY KEY (gid),

    14     CONSTRAINT  fkey_domain_data_tid_transport FOREIGN KEY (tid)

    15         REFERENCES transport (tid)

    16 );

    17 

    18 CREATE TABLE domain_name (

    19     domainname  varchar(255) NOT NULL,

    20     gid         bigint NOT NULL,

    21     is_primary  boolean NOT NULL,

    22     CONSTRAINT  pkey_domain_name PRIMARY KEY (domainname),

    23     CONSTRAINT  fkey_domain_name_gid_domain_data FOREIGN KEY (gid)

    24         REFERENCES domain_data (gid)

    25 );

    26 

    27 INSERT INTO domain_data (gid, tid, domaindir) 

    28     SELECT gid, tid, domaindir

    29       FROM domains;

    30 

    31 INSERT INTO domain_name (domainname, gid, is_primary) 

    32     SELECT domainname, gid, TRUE

    33       FROM domains;

    34 

    35 

    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 ADD CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid)

    42     REFERENCES domain_data (gid);

    43 

    44 ALTER TABLE relocated DROP CONSTRAINT fkey_relocated_gid_domains;

    45 ALTER TABLE relocated ADD CONSTRAINT fkey_relocated_gid_domain_data

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

    47 

    48 

    49 CREATE OR REPLACE VIEW dovecot_password AS

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

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

    52       FROM users

    53            LEFT JOIN domain_name USING (gid);

    54 

    55 CREATE OR REPLACE VIEW dovecot_user AS

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

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

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

    59       FROM users

    60            LEFT JOIN domain_data USING (gid)

    61            LEFT JOIN domain_name USING (gid)

    62            LEFT JOIN maillocation USING (mid);

    63 

    64 CREATE OR REPLACE VIEW postfix_gid AS

    65     SELECT gid, domainname

    66       FROM domain_name;

    67 

    68 CREATE OR REPLACE VIEW postfix_uid AS

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

    70       FROM users

    71            LEFT JOIN domain_name USING (gid);

    72 

    73 CREATE OR REPLACE VIEW postfix_maildir AS

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

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

    76            AS maildir

    77       FROM users

    78            LEFT JOIN domain_data USING (gid)

    79            LEFT JOIN domain_name USING (gid)

    80            LEFT JOIN maillocation USING (mid);

    81 

    82 CREATE OR REPLACE VIEW postfix_relocated AS

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

    84       FROM relocated

    85            LEFT JOIN domain_name USING (gid);

    86 

    87 CREATE OR REPLACE VIEW postfix_alias AS

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

    89       FROM alias

    90            LEFT JOIN domain_name USING (gid);

    91 

    92 CREATE OR REPLACE VIEW postfix_transport AS

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

    94            transport.transport

    95       FROM users

    96            LEFT JOIN transport USING (tid)

    97            LEFT JOIN domain_name USING (gid);

    98 

    99 DROP VIEW vmm_domain_info;

   100 CREATE OR REPLACE VIEW vmm_domain_info AS

   101     SELECT gid, domainname, transport, domaindir,

   102            count(uid) AS accounts,

   103            aliases,

   104            (SELECT count(gid)

   105               FROM domain_name

   106              WHERE domain_name.gid = domain_data.gid

   107                AND NOT domain_name.is_primary) AS aliasdomains

   108       FROM domain_data

   109            LEFT JOIN domain_name USING (gid)

   110            LEFT JOIN transport USING (tid)

   111            LEFT JOIN users USING (gid)

   112            LEFT JOIN vmm_alias_count USING (gid)

   113      WHERE domain_name.is_primary

   114   GROUP BY gid, domainname, transport, domaindir, aliases;

   115 

   116 

   117 DROP TABLE domains;

   118 

   119 

   120 CREATE LANGUAGE plpgsql;

   121 

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

   123 DECLARE

   124     primary_count bigint;

   125 BEGIN

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

   127       FROM domain_name

   128      WHERE domain_name.gid = NEW.gid

   129        AND is_primary;

   130 

   131     IF (primary_count > 1) THEN

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

   133     END IF;

   134 

   135     RETURN NEW;

   136 END;

   137 $$ LANGUAGE plpgsql STABLE;

   138 

   139 CREATE TRIGGER primary_count BEFORE INSERT OR UPDATE ON domain_name

   140     FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();