# HG changeset patch
# User Pascal Volk <neverseen@users.sourceforge.net>
# Date 1219715628 0
# Node ID e4d25f50164deec75b71abb4d854850e4b1d1423
# Parent  995d538a6eb50113192949d11e5c552bf0303150
* 'create_tables.pgsql'
* 'update_tables_0.4.x-0.5.pgsql'
    - Changed length of address column in table from varchar(256) to varchar(64)
      because only the local part will be stored.

* 'UPGRADE'
    - Updated, privileges on view postfix_alias have to be set once again.

diff -r 995d538a6eb5 -r e4d25f50164d UPGRADE
--- a/UPGRADE	Mon Aug 25 20:45:54 2008 +0000
+++ b/UPGRADE	Tue Aug 26 01:53:48 2008 +0000
@@ -11,6 +11,8 @@
     
     * start psql and connect to the appropriate database (ex. psql mailsys)
     * update the database: \i update_tables_0.4.x-0.5.pgsql
+    * GRANT SELECT ON postfix_alias TO postfix;
+                                       ^^^^^^^^ <- your Postfix db user
 
     * execute upgrade.sh
 
diff -r 995d538a6eb5 -r e4d25f50164d create_tables.pgsql
--- a/create_tables.pgsql	Mon Aug 25 20:45:54 2008 +0000
+++ b/create_tables.pgsql	Tue Aug 26 01:53:48 2008 +0000
@@ -83,7 +83,7 @@
 
 CREATE TABLE alias (
     gid         bigint NOT NULL,
-    address     varchar(256) NOT NULL,
+    address     varchar(64) NOT NULL,-- only localpart w/o '@'
     destination varchar(320) NOT NULL,
     CONSTRAINT  pkey_alias PRIMARY KEY (gid, address, destination),
     CONSTRAINT  fkey_alias_gid_domain_data FOREIGN KEY (gid)
diff -r 995d538a6eb5 -r e4d25f50164d update_tables_0.4.x-0.5.pgsql
--- 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,