update_tables_0.4.x-0.5.pgsql
changeset 67 e4d25f50164d
parent 42 9d10877e1c10
child 71 4c94ba297698
equal deleted inserted replaced
66:995d538a6eb5 67:e4d25f50164d
    36 ALTER TABLE users DROP CONSTRAINT fkey_users_gid_domains;
    36 ALTER TABLE users DROP CONSTRAINT fkey_users_gid_domains;
    37 ALTER TABLE users ADD CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid)
    37 ALTER TABLE users ADD CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid)
    38     REFERENCES domain_data (gid);
    38     REFERENCES domain_data (gid);
    39 
    39 
    40 ALTER TABLE alias DROP CONSTRAINT fkey_alias_gid_domains;
    40 ALTER TABLE alias DROP CONSTRAINT fkey_alias_gid_domains;
       
    41 ALTER TABLE alias DROP CONSTRAINT pkey_alias;
    41 ALTER TABLE alias ADD CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid)
    42 ALTER TABLE alias ADD CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid)
    42     REFERENCES domain_data (gid);
    43     REFERENCES domain_data (gid);
    43 
    44 
    44 ALTER TABLE relocated DROP CONSTRAINT fkey_relocated_gid_domains;
    45 ALTER TABLE relocated DROP CONSTRAINT fkey_relocated_gid_domains;
    45 ALTER TABLE relocated ADD CONSTRAINT fkey_relocated_gid_domain_data
    46 ALTER TABLE relocated ADD CONSTRAINT fkey_relocated_gid_domain_data
    82 CREATE OR REPLACE VIEW postfix_relocated AS
    83 CREATE OR REPLACE VIEW postfix_relocated AS
    83     SELECT address || '@' || domain_name.domainname AS address, destination
    84     SELECT address || '@' || domain_name.domainname AS address, destination
    84       FROM relocated
    85       FROM relocated
    85            LEFT JOIN domain_name USING (gid);
    86            LEFT JOIN domain_name USING (gid);
    86 
    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 
    87 CREATE OR REPLACE VIEW postfix_alias AS
    96 CREATE OR REPLACE VIEW postfix_alias AS
    88     SELECT address || '@' || domain_name.domainname AS address, destination, gid
    97     SELECT address || '@' || domain_name.domainname AS address, destination, gid
    89       FROM alias
    98       FROM alias
    90            LEFT JOIN domain_name USING (gid);
    99            LEFT JOIN domain_name USING (gid);
    91 
   100 
    94            transport.transport
   103            transport.transport
    95       FROM users
   104       FROM users
    96            LEFT JOIN transport USING (tid)
   105            LEFT JOIN transport USING (tid)
    97            LEFT JOIN domain_name USING (gid);
   106            LEFT JOIN domain_name USING (gid);
    98 
   107 
    99 DROP VIEW vmm_domain_info;
   108 CREATE OR REPLACE VIEW vmm_alias_count AS
       
   109     SELECT count(DISTINCT address) AS aliases, gid
       
   110       FROM alias 
       
   111   GROUP BY gid;
       
   112 
   100 CREATE OR REPLACE VIEW vmm_domain_info AS
   113 CREATE OR REPLACE VIEW vmm_domain_info AS
   101     SELECT gid, domainname, transport, domaindir,
   114     SELECT gid, domainname, transport, domaindir,
   102            count(uid) AS accounts,
   115            count(uid) AS accounts,
   103            aliases,
   116            aliases,
   104            (SELECT count(gid)
   117            (SELECT count(gid)