update_tables_0.4.x-0.5.pgsql
changeset 80 5dedc673524e
parent 71 4c94ba297698
child 81 89b71a9abfcf
--- a/update_tables_0.4.x-0.5.pgsql	Mon Sep 08 23:47:34 2008 +0000
+++ b/update_tables_0.4.x-0.5.pgsql	Tue Sep 09 04:29:13 2008 +0000
@@ -107,15 +107,15 @@
            LEFT JOIN transport USING (tid)
            LEFT JOIN domain_name USING (gid);
 
-CREATE OR REPLACE VIEW vmm_alias_count AS
-    SELECT count(DISTINCT address) AS aliases, gid
-      FROM alias 
-  GROUP BY gid;
-
 CREATE OR REPLACE VIEW vmm_domain_info AS
     SELECT gid, domainname, transport, domaindir,
            count(uid) AS accounts,
-           aliases,
+           (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
@@ -124,9 +124,8 @@
            LEFT JOIN domain_name USING (gid)
            LEFT JOIN transport USING (tid)
            LEFT JOIN users USING (gid)
-           LEFT JOIN vmm_alias_count USING (gid)
      WHERE domain_name.is_primary
-  GROUP BY gid, domainname, transport, domaindir, aliases;
+  GROUP BY gid, domainname, transport, domaindir;
 
 
 DROP TABLE domains;
@@ -151,5 +150,8 @@
 END;
 $$ LANGUAGE plpgsql STABLE;
 
-CREATE TRIGGER primary_count BEFORE INSERT OR UPDATE ON domain_name
+CREATE TRIGGER primary_count_ins BEFORE INSERT ON domain_name
     FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();
+
+CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name
+    FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();