# HG changeset patch # User Pascal Volk # Date 1333902043 0 # Node ID 5ccc9c6e51936533876de9df762637d76ac63d10 # Parent 23d04d1f29e86868a834c3a9cb15ecc83478173b pgsql: Removed most of the VIEWs. There are only two views left for now: - postfix_gid - vmm_domain_info All other views have a faster replacement: plpgsql functions. diff -r 23d04d1f29e8 -r 5ccc9c6e5193 pgsql/create_tables-dovecot-1.2.x.pgsql --- a/pgsql/create_tables-dovecot-1.2.x.pgsql Sun Apr 08 13:18:02 2012 +0200 +++ b/pgsql/create_tables-dovecot-1.2.x.pgsql Sun Apr 08 16:20:43 2012 +0000 @@ -173,58 +173,10 @@ REFERENCES domain_data (gid) ); -CREATE OR REPLACE VIEW dovecot_password AS - SELECT local_part || '@' || domain_name.domainname AS "user", - passwd AS "password", smtp, pop3, imap, sieve - FROM users - LEFT JOIN domain_name USING (gid) - LEFT JOIN service_set USING (ssid); - -CREATE OR REPLACE VIEW dovecot_user AS - SELECT local_part || '@' || domain_name.domainname AS userid, - uid, gid, domain_data.domaindir || '/' || uid AS home, - mailboxformat.format || ':~/' || maillocation.directory AS mail - FROM users - LEFT JOIN domain_data USING (gid) - LEFT JOIN domain_name USING (gid) - LEFT JOIN maillocation USING (mid) - LEFT JOIN mailboxformat USING (fid); - CREATE OR REPLACE VIEW postfix_gid AS SELECT gid, domainname FROM domain_name; -CREATE OR REPLACE VIEW postfix_uid AS - SELECT local_part || '@' || domain_name.domainname AS address, uid - FROM users - LEFT JOIN domain_name USING (gid); - -CREATE OR REPLACE VIEW postfix_maildir AS - SELECT local_part || '@' || domain_name.domainname AS address, - domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/' - AS maildir - FROM users - LEFT JOIN domain_data USING (gid) - LEFT JOIN domain_name USING (gid) - LEFT JOIN maillocation USING (mid); - -CREATE OR REPLACE VIEW postfix_relocated AS - SELECT address || '@' || domain_name.domainname AS address, destination - FROM relocated - LEFT JOIN domain_name USING (gid); - -CREATE OR REPLACE VIEW postfix_alias AS - SELECT address || '@' || domain_name.domainname AS address, destination, gid - FROM alias - LEFT JOIN domain_name USING (gid); - -CREATE OR REPLACE VIEW postfix_transport AS - SELECT local_part || '@' || domain_name.domainname AS address, - transport.transport - FROM users - LEFT JOIN transport USING (tid) - LEFT JOIN domain_name USING (gid); - CREATE OR REPLACE VIEW vmm_domain_info AS SELECT gid, count(uid) AS accounts, (SELECT count(DISTINCT address) diff -r 23d04d1f29e8 -r 5ccc9c6e5193 pgsql/create_tables.pgsql --- a/pgsql/create_tables.pgsql Sun Apr 08 13:18:02 2012 +0200 +++ b/pgsql/create_tables.pgsql Sun Apr 08 16:20:43 2012 +0000 @@ -173,58 +173,10 @@ REFERENCES domain_data (gid) ); -CREATE OR REPLACE VIEW dovecot_password AS - SELECT local_part || '@' || domain_name.domainname AS "user", - passwd AS "password", smtp, pop3, imap, managesieve - FROM users - LEFT JOIN domain_name USING (gid) - LEFT JOIN service_set USING (ssid); - -CREATE OR REPLACE VIEW dovecot_user AS - SELECT local_part || '@' || domain_name.domainname AS userid, - uid, gid, domain_data.domaindir || '/' || uid AS home, - mailboxformat.format || ':~/' || maillocation.directory AS mail - FROM users - LEFT JOIN domain_data USING (gid) - LEFT JOIN domain_name USING (gid) - LEFT JOIN maillocation USING (mid) - LEFT JOIN mailboxformat USING (fid); - CREATE OR REPLACE VIEW postfix_gid AS SELECT gid, domainname FROM domain_name; -CREATE OR REPLACE VIEW postfix_uid AS - SELECT local_part || '@' || domain_name.domainname AS address, uid - FROM users - LEFT JOIN domain_name USING (gid); - -CREATE OR REPLACE VIEW postfix_maildir AS - SELECT local_part || '@' || domain_name.domainname AS address, - domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/' - AS maildir - FROM users - LEFT JOIN domain_data USING (gid) - LEFT JOIN domain_name USING (gid) - LEFT JOIN maillocation USING (mid); - -CREATE OR REPLACE VIEW postfix_relocated AS - SELECT address || '@' || domain_name.domainname AS address, destination - FROM relocated - LEFT JOIN domain_name USING (gid); - -CREATE OR REPLACE VIEW postfix_alias AS - SELECT address || '@' || domain_name.domainname AS address, destination, gid - FROM alias - LEFT JOIN domain_name USING (gid); - -CREATE OR REPLACE VIEW postfix_transport AS - SELECT local_part || '@' || domain_name.domainname AS address, - transport.transport - FROM users - LEFT JOIN transport USING (tid) - LEFT JOIN domain_name USING (gid); - CREATE OR REPLACE VIEW vmm_domain_info AS SELECT gid, count(uid) AS accounts, (SELECT count(DISTINCT address) diff -r 23d04d1f29e8 -r 5ccc9c6e5193 pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql --- a/pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql Sun Apr 08 13:18:02 2012 +0200 +++ b/pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql Sun Apr 08 16:20:43 2012 +0000 @@ -36,11 +36,16 @@ \. -- --- --- Make room for different mailbox formats and longer password hashes. +-- Drop the obsolete VIEWs, we've functions now. -- --- DROP VIEW dovecot_user; DROP VIEW dovecot_password; +DROP VIEW postfix_alias DROP VIEW postfix_maildir; +DROP VIEW postfix_relocated; +DROP VIEW postfix_transport; +DROP VIEW postfix_uid; +-- the vmm_domain_info view will be restored later DROP VIEW vmm_domain_info; CREATE SEQUENCE mailboxformat_id; @@ -160,34 +165,8 @@ FOREIGN KEY (ssid) REFERENCES service_set (ssid); -- --- --- Restore views +-- Restore view -- --- -CREATE VIEW dovecot_user AS - SELECT local_part || '@' || domain_name.domainname AS userid, - uid, gid, domain_data.domaindir || '/' || uid AS home, - mailboxformat.format || ':~/' || maillocation.directory AS mail - FROM users - LEFT JOIN domain_data USING (gid) - LEFT JOIN domain_name USING (gid) - LEFT JOIN maillocation USING (mid) - LEFT JOIN mailboxformat USING (fid); - -CREATE OR REPLACE VIEW dovecot_password AS - SELECT local_part || '@' || domainname AS "user", - passwd AS "password", smtp, pop3, imap, sieve - FROM users - LEFT JOIN domain_name USING (gid) - LEFT JOIN service_set USING (ssid); - -CREATE VIEW postfix_maildir AS - SELECT local_part || '@' || domain_name.domainname AS address, - domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/' - AS maildir - FROM users - LEFT JOIN domain_data USING (gid) - LEFT JOIN domain_name USING (gid) - LEFT JOIN maillocation USING (mid); - CREATE VIEW vmm_domain_info AS SELECT gid, count(uid) AS accounts, (SELECT count(DISTINCT address) diff -r 23d04d1f29e8 -r 5ccc9c6e5193 pgsql/update_tables_0.5.x-0.6.pgsql --- a/pgsql/update_tables_0.5.x-0.6.pgsql Sun Apr 08 13:18:02 2012 +0200 +++ b/pgsql/update_tables_0.5.x-0.6.pgsql Sun Apr 08 16:20:43 2012 +0000 @@ -36,11 +36,16 @@ \. -- --- --- Make room for different mailbox formats and longer password hashes. +-- Drop the obsolete VIEWs, we've functions now. -- --- DROP VIEW dovecot_user; DROP VIEW dovecot_password; +DROP VIEW postfix_alias; DROP VIEW postfix_maildir; +DROP VIEW postfix_relocated; +DROP VIEW postfix_transport; +DROP VIEW postfix_uid; +-- the vmm_domain_info view will be restored later DROP VIEW vmm_domain_info; CREATE SEQUENCE mailboxformat_id; @@ -138,34 +143,8 @@ FOREIGN KEY (ssid) REFERENCES service_set (ssid); -- --- --- Restore views +-- Restore view -- --- -CREATE VIEW dovecot_user AS - SELECT local_part || '@' || domain_name.domainname AS userid, - uid, gid, domain_data.domaindir || '/' || uid AS home, - mailboxformat.format || ':~/' || maillocation.directory AS mail - FROM users - LEFT JOIN domain_data USING (gid) - LEFT JOIN domain_name USING (gid) - LEFT JOIN maillocation USING (mid) - LEFT JOIN mailboxformat USING (fid); - -CREATE OR REPLACE VIEW dovecot_password AS - SELECT local_part || '@' || domainname AS "user", - passwd AS "password", smtp, pop3, imap, managesieve - FROM users - LEFT JOIN domain_name USING (gid) - LEFT JOIN service_set USING (ssid); - -CREATE VIEW postfix_maildir AS - SELECT local_part || '@' || domain_name.domainname AS address, - domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/' - AS maildir - FROM users - LEFT JOIN domain_data USING (gid) - LEFT JOIN domain_name USING (gid) - LEFT JOIN maillocation USING (mid); - CREATE VIEW vmm_domain_info AS SELECT gid, count(uid) AS accounts, (SELECT count(DISTINCT address)