[vhffs-dev] [457] Pg-8.1 compatibility

[subversion@xxxxxxxxx]

Revision: 457
Author:   beuss
Date:     2007-02-16 15:16:38 +0000 (Fri, 16 Feb 2007)

Log Message:
-----------
Pg-8.1 compatibility

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 13:50:36 UTC (rev 456)
+++ branches/vhffs_4.1/vhffs-compat/4.0.sql	2007-02-16 15:16:38 UTC (rev 457)
@@ -54,61 +54,68 @@
 
 -- 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;
 ALTER TABLE vhffs_users ADD COLUMN firstname varchar(250);
-UPDATE vhffs_users SET firstname=vhffs_user_info.firstname WHERE vhffs_users.uid=vhffs_user_info.uid;
 ALTER TABLE vhffs_users ADD COLUMN lastname varchar(250);
-UPDATE vhffs_users SET lastname=vhffs_user_info.lastname WHERE vhffs_users.uid=vhffs_user_info.uid;
 ALTER TABLE vhffs_users ADD COLUMN address text;
-UPDATE vhffs_users SET address=vhffs_user_info.address WHERE vhffs_users.uid=vhffs_user_info.uid;
 ALTER TABLE vhffs_users ADD COLUMN zipcode varchar(16);
-UPDATE vhffs_users SET zipcode=vhffs_user_info.zipcode WHERE vhffs_users.uid=vhffs_user_info.uid;
 ALTER TABLE vhffs_users ADD COLUMN city varchar(250);
-UPDATE vhffs_users SET city=vhffs_user_info.city WHERE vhffs_users.uid=vhffs_user_info.uid;
 ALTER TABLE vhffs_users ADD COLUMN country varchar(250);
-UPDATE vhffs_users SET country=vhffs_user_info.country WHERE vhffs_users.uid=vhffs_user_info.uid;
 ALTER TABLE vhffs_users ADD COLUMN mail varchar(200);
-UPDATE vhffs_users SET mail=vhffs_user_info.mail WHERE vhffs_users.uid=vhffs_user_info.uid;
 ALTER TABLE vhffs_users ADD COLUMN gpg_key varchar(250);
-UPDATE vhffs_users SET gpg_key=vhffs_user_info.gpg_key WHERE vhffs_users.uid=vhffs_user_info.uid;
 
+UPDATE vhffs_users SET date_creation = ui.date_creation, firstname = ui.firstname, lastname = ui.lastname,
+       address = ui.address, zipcode = ui.zipcode, city = ui.city, country = ui.country, mail = ui.mail,
+       gpg_key = ui.gpg_key
+    FROM vhffs_user_info ui
+    WHERE ui.uid = vhffs_users.uid;
+
+-- DROP TABLE vhffs_users_info;
+
 -- merge vhffs_notes to vhffs_users
 ALTER TABLE vhffs_users ADD COLUMN note int4;
-UPDATE vhffs_users SET note=vhffs_notes.note WHERE vhffs_users.object_id=vhffs_notes.object_id;
+UPDATE vhffs_users SET note = n.note FROM vhffs_notes n WHERE n.object_id = vhffs_users.object_id;
 
+-- DROP TABLE vhffs_notes;
+
 -- merge vhffs_panel_user_prefs to vhffs_users
 ALTER TABLE vhffs_users ADD COLUMN language varchar(16);
-UPDATE vhffs_users SET language=vhffs_panel_user_prefs.language WHERE vhffs_users.uid=vhffs_panel_user_prefs.uid;
 ALTER TABLE vhffs_users ADD COLUMN theme varchar(250);
-UPDATE vhffs_users SET theme=vhffs_panel_user_prefs.theme WHERE vhffs_users.uid=vhffs_panel_user_prefs.uid;
+UPDATE vhffs_users SET language = up.language, theme up.theme
+    FROM vhffs_panel_user_prefs up
+    WHERE up.uid = vhffs_users.uid;
 
+-- DROP TABLE vhffs_panel_user_prefs;
+
 -- merge vhffs_groups_info to vhffs_groups
 ALTER TABLE vhffs_groups ADD COLUMN owner_uid int4;
-UPDATE vhffs_groups SET owner_uid=vhffs_groups_info.owner_uid WHERE vhffs_groups.gid=vhffs_groups_info.gid;
 ALTER TABLE vhffs_groups ADD COLUMN uid_mod int4;
-UPDATE vhffs_groups SET uid_mod=vhffs_groups_info.uid_mod WHERE vhffs_groups.gid=vhffs_groups_info.gid;
+UPDATE vhffs_groups SET owner_uid = gi.owner_uid, uid_mod = gi.uid_mod
+    FROM vhffs_groups_info gi
+    WHERE gi.gid = vhffs_groups.gid;
 
+-- DROP TABLE vhffs_groups_info
+
 -- merge vhffs_ml_prefs to vhffs_ml
 ALTER TABLE vhffs_ml ADD COLUMN admin varchar(250);
-UPDATE vhffs_ml SET admin=vhffs_ml_prefs.admin WHERE vhffs_ml.ml_id=vhffs_ml_prefs.ml_id;
 ALTER TABLE vhffs_ml ADD COLUMN open_post boolean;
-UPDATE vhffs_ml SET open_post='f' WHERE vhffs_ml.ml_id=vhffs_ml_prefs.ml_id AND vhffs_ml_prefs.open_post='0';
-UPDATE vhffs_ml SET open_post='t' WHERE vhffs_ml.ml_id=vhffs_ml_prefs.ml_id AND vhffs_ml_prefs.open_post='1';
 ALTER TABLE vhffs_ml ADD COLUMN open_archive boolean;
-UPDATE vhffs_ml SET open_archive='f' WHERE vhffs_ml.ml_id=vhffs_ml_prefs.ml_id AND vhffs_ml_prefs.open_archive='0';
-UPDATE vhffs_ml SET open_archive='t' WHERE vhffs_ml.ml_id=vhffs_ml_prefs.ml_id AND vhffs_ml_prefs.open_archive='1';
 ALTER TABLE vhffs_ml ADD COLUMN open_sub boolean;
-UPDATE vhffs_ml SET open_sub='f' WHERE vhffs_ml.ml_id=vhffs_ml_prefs.ml_id AND vhffs_ml_prefs.open_sub='0';
-UPDATE vhffs_ml SET open_sub='t' WHERE vhffs_ml.ml_id=vhffs_ml_prefs.ml_id AND vhffs_ml_prefs.open_sub='1';
 ALTER TABLE vhffs_ml ADD COLUMN reply_to boolean;
-UPDATE vhffs_ml SET reply_to='f' WHERE vhffs_ml.ml_id=vhffs_ml_prefs.ml_id AND vhffs_ml_prefs.reply_to='0';
-UPDATE vhffs_ml SET reply_to='t' WHERE vhffs_ml.ml_id=vhffs_ml_prefs.ml_id AND vhffs_ml_prefs.reply_to='1';
 ALTER TABLE vhffs_ml ADD COLUMN moderated boolean;
-UPDATE vhffs_ml SET moderated='f' WHERE vhffs_ml.ml_id=vhffs_ml_prefs.ml_id AND vhffs_ml_prefs.moderated='0';
-UPDATE vhffs_ml SET moderated='t' WHERE vhffs_ml.ml_id=vhffs_ml_prefs.ml_id AND vhffs_ml_prefs.moderated='1';
 ALTER TABLE vhffs_ml ADD COLUMN url varchar(512);
-UPDATE vhffs_ml SET url=vhffs_ml_prefs.url WHERE vhffs_ml.ml_id=vhffs_ml_prefs.ml_id;
 
+UPDATE vhffs_ml SET admin = mlp.admin,
+    open_post = (mlp.open_post != 0),
+    open_archive = (mlp.open_archive != 0),
+    open_sub = (mlp.open_sub != 0),
+    reply_to = (mlp.reply_to != 0),
+    moderated = (mlp.moderated != 0),
+    url = mlp.url
+    FROM vhffs_ml_prefs mlp
+    WHERE mlp.ml_id = vhffs_ml.ml_id;
+
+-- DROP TABLE vhffs_ml_prefs
+
 -- update vhffs_forum view
 DROP VIEW vhffs_forum;
 CREATE VIEW vhffs_forum AS