| [vhffs-dev] [734] As usual, I edited the wrong file, let's commit the right one :) |
[ Thread Index |
Date Index
| More vhffs.org/vhffs-dev Archives
]
Revision: 734
Author: gradator
Date: 2007-07-29 15:54:39 +0000 (Sun, 29 Jul 2007)
Log Message:
-----------
As usual, I edited the wrong file, let's commit the right one :)
Modified Paths:
--------------
trunk/vhffs-compat/4.0.sql.in
Modified: trunk/vhffs-compat/4.0.sql.in
===================================================================
--- trunk/vhffs-compat/4.0.sql.in 2007-07-29 15:52:48 UTC (rev 733)
+++ trunk/vhffs-compat/4.0.sql.in 2007-07-29 15:54:39 UTC (rev 734)
@@ -4,9 +4,8 @@
SELECT setval('vhffs_object_object_id_seq', (SELECT COALESCE(MAX(object_id), 1) FROM vhffs_object));
SELECT setval('vhffs_groups_gid_seq', (SELECT COALESCE(MAX(gid), @MINGID@) FROM vhffs_groups));
SELECT setval('vhffs_users_uid_seq', (SELECT COALESCE(MAX(uid), @MINUID@) FROM vhffs_users));
-SELECT setval('vhffs_confirmation_cid_seq', (SELECT COALESCE(MAX(cid), 1) FROM vhffs_confirmation));
SELECT setval('vhffs_cvs_cvs_id_seq', (SELECT COALESCE(MAX(cvs_id), 1) FROM vhffs_cvs));
-SELECT setval('vhffs_dns_dns_id_seq', (SELECT COALESCE(MAX(dns_id), 1) FROM vhffs_dns));
+SELECT setval('vhffs_dns_global_id_seq', (SELECT COALESCE(MAX(dns_id), 1) FROM vhffs_dns_global));
SELECT setval('vhffs_dns_rr_id_seq', (SELECT COALESCE(MAX(id), 1) FROM vhffs_dns_rr));
SELECT setval('vhffs_httpd_httpd_id_seq', (SELECT COALESCE(MAX(httpd_id), 1) FROM vhffs_httpd));
SELECT setval('vhffs_mailings_id_mailing_seq', (SELECT COALESCE(MAX(id_mailing), 1) FROM vhffs_mailings));
@@ -26,17 +25,14 @@
-- vhffs_users
ALTER TABLE vhffs_users ALTER gid DROP NOT NULL;
-- TODO merge date_creation to vhffs_object (check if this field is set in vhffs_users AND not in vhffs_object)
DROP VIEW vhffs_forum;
-ALTER TABLE vhffs_users DROP COLUMN date_creation;
+-- date_creation will be dropped when vhffs_user_info will be dropped
-- vhffs_object
ALTER TABLE vhffs_object ADD COLUMN owner_gid int4;
ALTER TABLE vhffs_object ALTER owner_uid SET NOT NULL;
ALTER TABLE vhffs_object ALTER date_creation SET NOT NULL;
--- TODO: merge vhffs_$service:owner_uid|owner_gid -> vhffs_object
-
-- 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]
-- Pg 7.4 doesn't support default value in add column !
@@ -58,9 +54,6 @@
UPDATE vhffs_object SET type=60, owner_gid = m.owner_gid FROM vhffs_mxdomain m WHERE m.object_id = vhffs_object.object_id;
UPDATE vhffs_object SET type=61, owner_gid = m.owner_gid FROM vhffs_ml m WHERE m.object_id = vhffs_object.object_id;
-ALTER TABLE vhffs_users DROP COLUMN owner_gid;
-ALTER TABLE vhffs_groups DROP COLUMN owner_gid;
-
ALTER TABLE vhffs_httpd DROP COLUMN owner_gid;
ALTER TABLE vhffs_httpd DROP COLUMN owner_uid;
@@ -90,12 +83,14 @@
-- cleanup unused objects
--- DELETE FROM vhffs_object WHERE type=0;
+DELETE FROM vhffs_acl WHERE oid_src IN ( SELECT object_id FROM vhffs_object WHERE type=0 );
+DELETE FROM vhffs_acl WHERE oid_dst IN ( SELECT object_id FROM vhffs_object WHERE type=0 );
+DELETE FROM vhffs_history WHERE object_id IN ( SELECT object_id FROM vhffs_object WHERE type=0 );
+DELETE FROM vhffs_object WHERE type=0;
ALTER TABLE vhffs_object ALTER owner_gid SET NOT NULL;
-- merge vhffs_user_info to vhffs_users
-ALTER TABLE vhffs_users ADD COLUMN date_creation timestamp;
ALTER TABLE vhffs_users ADD COLUMN firstname varchar(250);
ALTER TABLE vhffs_users ADD COLUMN lastname varchar(250);
ALTER TABLE vhffs_users ADD COLUMN address text;
@@ -105,20 +100,20 @@
ALTER TABLE vhffs_users ADD COLUMN mail varchar(200);
ALTER TABLE vhffs_users ADD COLUMN gpg_key varchar(250);
-UPDATE vhffs_users SET date_creation = ui.date_creation, firstname = ui.firstname, lastname = ui.lastname,
+UPDATE vhffs_users SET 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_user_info;
+DROP TABLE vhffs_user_info;
-- merge vhffs_notes to vhffs_users
ALTER TABLE vhffs_users ADD COLUMN note int4;
UPDATE vhffs_users SET note = 0 WHERE object_id NOT IN (SELECT object_id FROM vhffs_notes);
UPDATE vhffs_users SET note = n.note FROM vhffs_notes n WHERE n.object_id = vhffs_users.object_id;
--- DROP TABLE vhffs_notes;
+DROP TABLE vhffs_notes;
-- merge vhffs_panel_user_prefs to vhffs_users
ALTER TABLE vhffs_users ADD COLUMN language varchar(16);
@@ -129,21 +124,11 @@
FROM vhffs_panel_user_prefs up
WHERE up.uid = vhffs_users.uid;
--- DROP TABLE vhffs_panel_user_prefs;
+DROP TABLE vhffs_panel_user_prefs;
-- vhffs_groups
-ALTER TABLE vhffs_groups DROP COLUMN owner_uid;
-ALTER TABLE vhffs_groups DROP COLUMN uid_mod;
+DROP TABLE vhffs_groups_info;
--- merge vhffs_groups_info to vhffs_groups
-ALTER TABLE vhffs_groups ADD COLUMN owner_uid int4;
-ALTER TABLE vhffs_groups ADD COLUMN uid_mod int4;
-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);
ALTER TABLE vhffs_ml ADD COLUMN open_post boolean;
@@ -161,16 +146,11 @@
FROM vhffs_ml_prefs mlp
WHERE mlp.ml_id = vhffs_ml.ml_id;
--- DROP TABLE vhffs_ml_prefs;
+DROP TABLE vhffs_ml_prefs;
-ALTER TABLE vhffs_ml DROP url;
ALTER TABLE vhffs_ml ADD COLUMN signature varchar(250);
-ALTER TABLE vhffs_ml ALTER owner_uid SET NOT NULL;
-ALTER TABLE vhffs_ml ALTER owner_gid SET NOT NULL;
ALTER TABLE vhffs_ml ALTER object_id SET NOT NULL;
-ALTER TABLE vhffs_ml ALTER sub_ctrl SET NOT NULL;
-ALTER TABLE vhffs_ml ALTER post_ctrl SET NOT NULL;
ALTER TABLE vhffs_ml ALTER domain SET NOT NULL;
@@ -178,7 +158,7 @@
ALTER TABLE vhffs_ml_subscribers ADD COLUMN language varchar(16);
UPDATE vhffs_ml_subscribers SET language = mll.lang FROM vhffs_ml_lang mll WHERE mll.member = vhffs_ml_subscribers.member;
--- DROP TABLE vhffs_ml_lang;
+DROP TABLE vhffs_ml_lang;
ALTER TABLE vhffs_ml_subscribers DROP active;
ALTER TABLE vhffs_ml_subscribers ALTER COLUMN hash DROP NOT NULL;
@@ -188,12 +168,10 @@
-- sub_ctrl, 0 = open, 1 = open but moderated (approval required), 2 = closed
-- post_ctrl, 0 = open, 1 = moderated for all, 2 = moderated for non members and open for members, 3 = members only,
-- 4 = members only and moderated, 5 = admins only
-ALTER TABLE vhffs_ml ADD COLUMN sub_ctrl int4;
-ALTER TABLE vhffs_ml ADD COLUMN post_ctrl int4;
-UPDATE vhffs_ml SET sub_ctrl = 0;
+ALTER TABLE vhffs_ml ADD COLUMN sub_ctrl int4 NOT NULL DEFAULT 0;
+ALTER TABLE vhffs_ml ADD COLUMN post_ctrl int4 NOT NULL DEFAULT 3;
UPDATE vhffs_ml SET sub_ctrl = 0 where open_sub = 't';
UPDATE vhffs_ml SET sub_ctrl = 2 where open_sub = 'f';
-UPDATE vhffs_ml SET post_ctrl = 0;
UPDATE vhffs_ml SET post_ctrl = 0 where open_post = 't' and moderated = 'f';
UPDATE vhffs_ml SET post_ctrl = 1 where open_post = 't' and moderated = 't';
UPDATE vhffs_ml SET post_ctrl = 3 where open_post = 'f' and moderated = 'f';
@@ -238,18 +216,16 @@
ALTER TABLE vhffs_dns ALTER COLUMN expire SET NOT NULL;
ALTER TABLE vhffs_dns ALTER COLUMN minimum SET NOT NULL;
ALTER TABLE vhffs_dns ALTER COLUMN ttl SET NOT NULL;
-
-ALTER TABLE vhffs_dns ALTER COLUMN owner_gid SET NOT NULL;
-ALTER TABLE vhffs_dns ALTER COLUMN owner_uid SET NOT NULL;
ALTER TABLE vhffs_dns ALTER COLUMN object_id SET NOT NULL;
--- DROP TABLE vhffs_dns_soa;
+DROP TABLE vhffs_dns_soa;
-- Rename vhffs_acl columns, remove unused acl_id
ALTER TABLE vhffs_acl DROP CONSTRAINT vhffs_acl_pkey;
-ALTER TABLE vhffs_acl DROP COLUMN acl_id;
ALTER TABLE vhffs_acl RENAME COLUMN oid_src TO granted_oid;
ALTER TABLE vhffs_acl RENAME COLUMN oid_dst TO target_oid;
+DELETE FROM vhffs_acl WHERE acl_id IN (SELECT c1.acl_id FROM vhffs_acl c1, vhffs_acl c2 WHERE c1.acl_id < c2.acl_id AND c1.granted_oid = c2.granted_oid AND c1.target_oid = c2.target_oid);
+ALTER TABLE vhffs_acl DROP COLUMN acl_id;
ALTER TABLE vhffs_acl ADD CONSTRAINT vhffs_acl_pkey PRIMARY KEY (granted_oid, target_oid);
-- change the type of vhffs_object.state from varchar to int4 ..... (no comments allowed please)
@@ -261,6 +237,24 @@
ALTER TABLE vhffs_object ALTER COLUMN state SET NOT NULL;
COMMIT;
+-- change the type of vhffs_groups.object_id from varchar to int4 ..... (no comments allowed please)
+BEGIN;
+ALTER TABLE vhffs_groups ADD COLUMN objectid_new int4;
+UPDATE vhffs_groups SET objectid_new = object_id::text::integer;
+ALTER TABLE vhffs_groups DROP COLUMN object_id;
+ALTER TABLE vhffs_groups RENAME COLUMN objectid_new TO object_id;
+ALTER TABLE vhffs_object ALTER COLUMN object_id SET NOT NULL;
+COMMIT;
+
+-- change the type of vhffs_users.object_id from varchar to int4 ..... (no comments allowed please)
+BEGIN;
+ALTER TABLE vhffs_users ADD COLUMN objectid_new int4;
+UPDATE vhffs_users SET objectid_new = object_id::text::integer;
+ALTER TABLE vhffs_users DROP COLUMN object_id;
+ALTER TABLE vhffs_users RENAME COLUMN objectid_new TO object_id;
+ALTER TABLE vhffs_object ALTER COLUMN object_id SET NOT NULL;
+COMMIT;
+
-- create vhffs_dns_soa view
CREATE VIEW vhffs_dns_soa AS
SELECT dns.dns_id AS id, dns.domain || '.' AS origin , dns.ns, dns.mbox, dns.serial, dns.refresh, dns.retry, dns.expire, dns.minimum, dns.ttl
@@ -268,7 +262,7 @@
-- update vhffs_forum view, it has been dropped by drop column state in vhffs_object
CREATE VIEW vhffs_forum AS
-SELECT users.username, users.passwd, users.firstname, users.lastname, users.mail, users.date_creation, object.state
+SELECT users.username, users.passwd, users.firstname, users.lastname, users.mail, object.date_creation, object.state
FROM vhffs_users users, vhffs_object object
WHERE object.object_id=users.object_id;
@@ -285,13 +279,9 @@
ALTER TABLE vhffs_httpd ALTER servername DROP DEFAULT;
-- some alter on vhffs_cvs table
-ALTER TABLE vhffs_cvs ALTER owner_uid SET NOT NULL;
-ALTER TABLE vhffs_cvs ALTER owner_gid SET NOT NULL;
ALTER TABLE vhffs_cvs ALTER object_id SET NOT NULL;
-- some alter on vhffs_mysql
-ALTER TABLE vhffs_mysql ALTER owner_uid SET NOT NULL;
-ALTER TABLE vhffs_mysql ALTER owner_gid SET NOT NULL;
ALTER TABLE vhffs_mysql ALTER object_id SET NOT NULL;
-- some alter on vhffs_history
@@ -299,13 +289,9 @@
ALTER TABLE vhffs_history ALTER date SET NOT NULL;
-- some alter on vhffs_pgsql
-ALTER TABLE vhffs_pgsql ALTER owner_uid SET NOT NULL;
-ALTER TABLE vhffs_pgsql ALTER owner_gid SET NOT NULL;
ALTER TABLE vhffs_pgsql ALTER object_id SET NOT NULL;
-- some alter on vhffs_svn
-ALTER TABLE vhffs_svn ALTER owner_uid SET NOT NULL;
-ALTER TABLE vhffs_svn ALTER owner_gid SET NOT NULL;
ALTER TABLE vhffs_svn ALTER object_id SET NOT NULL;
-- some alter on vhffs_user_group