update_tables_0.4.x-0.5.pgsql
changeset 67 e4d25f50164d
parent 42 9d10877e1c10
child 71 4c94ba297698
--- a/update_tables_0.4.x-0.5.pgsql	Mon Aug 25 20:45:54 2008 +0000
+++ b/update_tables_0.4.x-0.5.pgsql	Tue Aug 26 01:53:48 2008 +0000
@@ -38,6 +38,7 @@
     REFERENCES domain_data (gid);
 
 ALTER TABLE alias DROP CONSTRAINT fkey_alias_gid_domains;
+ALTER TABLE alias DROP CONSTRAINT pkey_alias;
 ALTER TABLE alias ADD CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid)
     REFERENCES domain_data (gid);
 
@@ -84,6 +85,14 @@
       FROM relocated
            LEFT JOIN domain_name USING (gid);
 
+DROP VIEW postfix_alias;
+DROP VIEW vmm_domain_info;
+DROP VIEW vmm_alias_count;
+
+ALTER TABLE alias ALTER address TYPE varchar(64);
+ALTER TABLE alias ADD CONSTRAINT pkey_alias 
+    PRIMARY KEY (gid, address, destination);
+
 CREATE OR REPLACE VIEW postfix_alias AS
     SELECT address || '@' || domain_name.domainname AS address, destination, gid
       FROM alias
@@ -96,7 +105,11 @@
            LEFT JOIN transport USING (tid)
            LEFT JOIN domain_name USING (gid);
 
-DROP VIEW vmm_domain_info;
+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,