Mercurial Mercurial > vmm / changeset
summary | shortlog | changelog | graph | tags | bookmarks | branches | files | changeset | raw | gz | bz2 | help
Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
pgsql: Removed most of the VIEWs. v0.6.x
authorPascal Volk <user@localhost.localdomain.org>
Sun, 08 Apr 2012 16:20:43 +0000
branchv0.6.x
changeset 500 5ccc9c6e5193
parent 499 23d04d1f29e8
child 501 f2387d60624b
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.
pgsql/create_tables-dovecot-1.2.x.pgsql file | annotate | diff | comparison | revisions
pgsql/create_tables.pgsql file | annotate | diff | comparison | revisions
pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql file | annotate | diff | comparison | revisions
pgsql/update_tables_0.5.x-0.6.pgsql file | annotate | diff | comparison | revisions 
--- 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)
--- 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)
--- 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)
--- 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)
vmm

Repositories index