update_tables_0.4.x-0.5.pgsql
changeset 160 639cf4003965
parent 159 78b6b06188d3
child 161 b62aa4aabcd0
equal deleted inserted replaced
159:78b6b06188d3 160:639cf4003965
     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();