pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql
branchv0.6.x
changeset 500 5ccc9c6e5193
parent 437 9823548b2717
child 502 e1b32377032f
equal deleted inserted replaced
499:23d04d1f29e8 500:5ccc9c6e5193
    34 TRUE	FALSE	FALSE	FALSE
    34 TRUE	FALSE	FALSE	FALSE
    35 FALSE	FALSE	FALSE	FALSE
    35 FALSE	FALSE	FALSE	FALSE
    36 \.
    36 \.
    37 
    37 
    38 -- ---
    38 -- ---
    39 -- Make room for different mailbox formats and longer password hashes.
    39 -- Drop the obsolete VIEWs, we've functions now.
    40 -- ---
    40 -- ---
    41 DROP VIEW dovecot_user;
    41 DROP VIEW dovecot_user;
    42 DROP VIEW dovecot_password;
    42 DROP VIEW dovecot_password;
       
    43 DROP VIEW postfix_alias
    43 DROP VIEW postfix_maildir;
    44 DROP VIEW postfix_maildir;
       
    45 DROP VIEW postfix_relocated;
       
    46 DROP VIEW postfix_transport;
       
    47 DROP VIEW postfix_uid;
       
    48 -- the vmm_domain_info view will be restored later
    44 DROP VIEW vmm_domain_info;
    49 DROP VIEW vmm_domain_info;
    45 
    50 
    46 CREATE SEQUENCE mailboxformat_id;
    51 CREATE SEQUENCE mailboxformat_id;
    47 CREATE SEQUENCE quotalimit_id;
    52 CREATE SEQUENCE quotalimit_id;
    48 
    53 
   158 ALTER TABLE users DROP COLUMN sieve;
   163 ALTER TABLE users DROP COLUMN sieve;
   159 ALTER TABLE users ADD CONSTRAINT fkey_users_ssid_service_set
   164 ALTER TABLE users ADD CONSTRAINT fkey_users_ssid_service_set
   160     FOREIGN KEY (ssid) REFERENCES service_set (ssid);
   165     FOREIGN KEY (ssid) REFERENCES service_set (ssid);
   161 
   166 
   162 -- ---
   167 -- ---
   163 -- Restore views
   168 -- Restore view
   164 -- ---
   169 -- ---
   165 CREATE VIEW dovecot_user AS
       
   166     SELECT local_part || '@' || domain_name.domainname AS userid,
       
   167            uid, gid, domain_data.domaindir || '/' || uid AS home,
       
   168            mailboxformat.format || ':~/' || maillocation.directory AS mail
       
   169       FROM users
       
   170            LEFT JOIN domain_data USING (gid)
       
   171            LEFT JOIN domain_name USING (gid)
       
   172            LEFT JOIN maillocation USING (mid)
       
   173            LEFT JOIN mailboxformat USING (fid);
       
   174 
       
   175 CREATE OR REPLACE VIEW dovecot_password AS
       
   176     SELECT local_part || '@' || domainname AS "user",
       
   177            passwd AS "password", smtp, pop3, imap, sieve
       
   178       FROM users
       
   179            LEFT JOIN domain_name USING (gid)
       
   180            LEFT JOIN service_set USING (ssid);
       
   181 
       
   182 CREATE VIEW postfix_maildir AS
       
   183     SELECT local_part || '@' || domain_name.domainname AS address,
       
   184            domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/'
       
   185            AS maildir
       
   186       FROM users
       
   187            LEFT JOIN domain_data USING (gid)
       
   188            LEFT JOIN domain_name USING (gid)
       
   189            LEFT JOIN maillocation USING (mid);
       
   190 
       
   191 CREATE VIEW vmm_domain_info AS
   170 CREATE VIEW vmm_domain_info AS
   192     SELECT gid, count(uid) AS accounts,
   171     SELECT gid, count(uid) AS accounts,
   193            (SELECT count(DISTINCT address)
   172            (SELECT count(DISTINCT address)
   194               FROM alias
   173               FROM alias
   195              WHERE alias.gid = domain_data.gid) AS aliases,
   174              WHERE alias.gid = domain_data.gid) AS aliases,