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: Added quotalimit's bytes and messages to view vmm_domain_info. v0.6.x
authorPascal Volk <neverseen@users.sourceforge.net>
Tue, 08 Feb 2011 18:04:07 +0000
branchv0.6.x
changeset 388 dd95ed5bc9d2
parent 387 05dc4e1f8dff
child 389 5f7e9f778b29
pgsql: Added quotalimit's bytes and messages to view vmm_domain_info.
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	Tue Feb 08 13:43:35 2011 +0000
+++ b/pgsql/create_tables-dovecot-1.2.x.pgsql	Tue Feb 08 18:04:07 2011 +0000
@@ -203,13 +203,15 @@
            (SELECT count(gid)
               FROM domain_name
              WHERE domain_name.gid = domain_data.gid
-               AND NOT domain_name.is_primary) AS aliasdomains
+               AND NOT domain_name.is_primary) AS aliasdomains,
+           bytes, messages
       FROM domain_data
            LEFT JOIN domain_name USING (gid)
+           LEFT JOIN quotalimit USING (qid)
            LEFT JOIN transport USING (tid)
            LEFT JOIN users USING (gid)
      WHERE domain_name.is_primary
-  GROUP BY gid, domainname, transport, domaindir;
+  GROUP BY gid, domainname, transport, domaindir, bytes, messages;
 
 
 CREATE LANGUAGE plpgsql;
--- a/pgsql/create_tables.pgsql	Tue Feb 08 13:43:35 2011 +0000
+++ b/pgsql/create_tables.pgsql	Tue Feb 08 18:04:07 2011 +0000
@@ -203,13 +203,15 @@
            (SELECT count(gid)
               FROM domain_name
              WHERE domain_name.gid = domain_data.gid
-               AND NOT domain_name.is_primary) AS aliasdomains
+               AND NOT domain_name.is_primary) AS aliasdomains,
+           bytes, messages
       FROM domain_data
            LEFT JOIN domain_name USING (gid)
+           LEFT JOIN quotalimit USING (qid)
            LEFT JOIN transport USING (tid)
            LEFT JOIN users USING (gid)
      WHERE domain_name.is_primary
-  GROUP BY gid, domainname, transport, domaindir;
+  GROUP BY gid, domainname, transport, domaindir, bytes, messages;
 
 
 CREATE LANGUAGE plpgsql;
--- a/pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql	Tue Feb 08 13:43:35 2011 +0000
+++ b/pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql	Tue Feb 08 18:04:07 2011 +0000
@@ -135,3 +135,25 @@
            LEFT JOIN domain_data USING (gid)
            LEFT JOIN domain_name USING (gid)
            LEFT JOIN maillocation USING (mid);
+
+CREATE OR REPLACE VIEW vmm_domain_info AS
+    SELECT gid, domainname, transport, domaindir,
+           count(uid) AS accounts,
+           (SELECT count(DISTINCT address)
+              FROM alias
+             WHERE alias.gid = domain_data.gid) AS aliases,
+           (SELECT count(gid)
+              FROM relocated
+             WHERE relocated.gid = domain_data.gid) AS relocated,
+           (SELECT count(gid)
+              FROM domain_name
+             WHERE domain_name.gid = domain_data.gid
+               AND NOT domain_name.is_primary) AS aliasdomains,
+           bytes, messages
+      FROM domain_data
+           LEFT JOIN domain_name USING (gid)
+           LEFT JOIN quotalimit USING (qid)
+           LEFT JOIN transport USING (tid)
+           LEFT JOIN users USING (gid)
+     WHERE domain_name.is_primary
+  GROUP BY gid, domainname, transport, domaindir, bytes, messages;
--- a/pgsql/update_tables_0.5.x-0.6.pgsql	Tue Feb 08 13:43:35 2011 +0000
+++ b/pgsql/update_tables_0.5.x-0.6.pgsql	Tue Feb 08 18:04:07 2011 +0000
@@ -113,3 +113,25 @@
            LEFT JOIN domain_data USING (gid)
            LEFT JOIN domain_name USING (gid)
            LEFT JOIN maillocation USING (mid);
+
+CREATE OR REPLACE VIEW vmm_domain_info AS
+    SELECT gid, domainname, transport, domaindir,
+           count(uid) AS accounts,
+           (SELECT count(DISTINCT address)
+              FROM alias
+             WHERE alias.gid = domain_data.gid) AS aliases,
+           (SELECT count(gid)
+              FROM relocated
+             WHERE relocated.gid = domain_data.gid) AS relocated,
+           (SELECT count(gid)
+              FROM domain_name
+             WHERE domain_name.gid = domain_data.gid
+               AND NOT domain_name.is_primary) AS aliasdomains,
+           bytes, messages
+      FROM domain_data
+           LEFT JOIN domain_name USING (gid)
+           LEFT JOIN quotalimit USING (qid)
+           LEFT JOIN transport USING (tid)
+           LEFT JOIN users USING (gid)
+     WHERE domain_name.is_primary
+  GROUP BY gid, domainname, transport, domaindir, bytes, messages;
vmm

Repositories index