[vhffs-dev] [455] PgSQL 7.4 doesn't support DEFAULT clause in ALTER TABLE.

[subversion@xxxxxxxxx]

Revision: 455
Author:   beuss
Date:     2007-02-16 12:13:49 +0000 (Fri, 16 Feb 2007)

Log Message:
-----------
PgSQL 7.4 doesn't support DEFAULT clause in ALTER TABLE. Com'on guys ! Was it a stupidity contest with the MySQL dev team ?!

Modified Paths:
--------------
    branches/vhffs_4.1/vhffs-compat/4.0.sql


Modified: branches/vhffs_4.1/vhffs-compat/4.0.sql
===================================================================
--- branches/vhffs_4.1/vhffs-compat/4.0.sql	2007-02-16 11:11:43 UTC (rev 454)
+++ branches/vhffs_4.1/vhffs-compat/4.0.sql	2007-02-16 12:13:49 UTC (rev 455)
@@ -30,19 +30,25 @@
 
 -- add a column named "type" on object table in order to know the purpose of the object (web area, mysql, ...)
 -- existing types: unassigned[0], user[10], group[11], httpd[20], repository[21], mysql[30], pgsql[31], cvs[40], svn[41], dns[50], mail[60], ml[61]
-ALTER TABLE vhffs_object ADD COLUMN type int4 DEFAULT 0;
-UPDATE vhffs_object SET type='10' WHERE object_id IN (SELECT object_id FROM vhffs_users);
-UPDATE vhffs_object SET type='11' WHERE object_id IN (SELECT object_id FROM vhffs_groups);
-UPDATE vhffs_object SET type='20' WHERE object_id IN (SELECT object_id FROM vhffs_httpd);
-UPDATE vhffs_object SET type='21' WHERE object_id IN (SELECT object_id FROM vhffs_repository);
-UPDATE vhffs_object SET type='30' WHERE object_id IN (SELECT object_id FROM vhffs_mysql);
-UPDATE vhffs_object SET type='31' WHERE object_id IN (SELECT object_id FROM vhffs_pgsql);
-UPDATE vhffs_object SET type='40' WHERE object_id IN (SELECT object_id FROM vhffs_cvs);
-UPDATE vhffs_object SET type='41' WHERE object_id IN (SELECT object_id FROM vhffs_svn);
-UPDATE vhffs_object SET type='50' WHERE object_id IN (SELECT object_id FROM vhffs_dns_global);
-UPDATE vhffs_object SET type='60' WHERE object_id IN (SELECT object_id FROM vhffs_mxdomain);
-UPDATE vhffs_object SET type='61' WHERE object_id IN (SELECT object_id FROM vhffs_ml);
+-- Pg 7.4 doesn't support default value in add column !
+ALTER TABLE vhffs_object ADD COLUMN type int4;
+ALTER TABLE vhffs_object ALTER COLUMN type SET DEFAULT 0;
+-- This *shouldn't be necessary since every object has a type but some old code
+-- didn't perform correct cleanup
+UPDATE vhffs_object SET type=0;
 
+UPDATE vhffs_object SET type=10 WHERE object_id IN (SELECT object_id FROM vhffs_users);
+UPDATE vhffs_object SET type=11 WHERE object_id IN (SELECT object_id FROM vhffs_groups);
+UPDATE vhffs_object SET type=20 WHERE object_id IN (SELECT object_id FROM vhffs_httpd);
+UPDATE vhffs_object SET type=21 WHERE object_id IN (SELECT object_id FROM vhffs_repository);
+UPDATE vhffs_object SET type=30 WHERE object_id IN (SELECT object_id FROM vhffs_mysql);
+UPDATE vhffs_object SET type=31 WHERE object_id IN (SELECT object_id FROM vhffs_pgsql);
+UPDATE vhffs_object SET type=40 WHERE object_id IN (SELECT object_id FROM vhffs_cvs);
+UPDATE vhffs_object SET type=41 WHERE object_id IN (SELECT object_id FROM vhffs_svn);
+UPDATE vhffs_object SET type=50 WHERE object_id IN (SELECT object_id FROM vhffs_dns_global);
+UPDATE vhffs_object SET type=60 WHERE object_id IN (SELECT object_id FROM vhffs_mxdomain);
+UPDATE vhffs_object SET type=61 WHERE object_id IN (SELECT object_id FROM vhffs_ml);
+
 -- merge vhffs_user_info to vhffs_users
 ALTER TABLE vhffs_users ADD COLUMN date_creation timestamp;
 UPDATE vhffs_users SET date_creation=vhffs_user_info.date_creation WHERE vhffs_users.uid=vhffs_user_info.uid;