[vhffs-dev] [488] Removed no more used sequences updates, regrouped all vhffs_dns updates, simplified vhffs_object(state_new) migration, removed create indexes (they are directly obtained from initdb. sql by updatedb.pl).

[subversion@xxxxxxxxx]

Revision: 488
Author:   beuss
Date:     2007-02-20 09:10:27 +0000 (Tue, 20 Feb 2007)

Log Message:
-----------
Removed no more used sequences updates, regrouped all vhffs_dns updates, simplified vhffs_object(state_new) migration, removed create indexes (they are directly obtained from initdb.sql by updatedb.pl).

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-19 11:11:36 UTC (rev 487)
+++ branches/vhffs_4.1/vhffs-compat/4.0.sql	2007-02-20 09:10:27 UTC (rev 488)
@@ -10,9 +10,8 @@
 SELECT setval('vhffs_users_uid_seq', (SELECT COALESCE(MAX(uid), 10000) 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_global_dns_id_seq', (SELECT COALESCE(MAX(dns_id), 1) FROM vhffs_dns_global));
+SELECT setval('vhffs_dns_dns_id_seq', (SELECT COALESCE(MAX(dns_id), 1) FROM vhffs_dns));
 SELECT setval('vhffs_dns_rr_id_seq', (SELECT COALESCE(MAX(id), 1) FROM vhffs_dns_rr));
-SELECT setval('vhffs_dns_soa_id_seq', (SELECT COALESCE(MAX(id), 1) FROM vhffs_dns_soa));
 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));
 SELECT setval('vhffs_mxdomain_mxdomain_id_seq', (SELECT COALESCE(MAX(mxdomain_id), 1) FROM vhffs_mxdomain));
@@ -130,62 +129,47 @@
 
 -- merge vhffs_dns_soa to vhffs_dns(ex vhffs_dns_global)
 ALTER TABLE vhffs_dns ADD COLUMN ns varchar(255);
-UPDATE vhffs_dns SET ns = soa.ns FROM vhffs_dns_soa soa WHERE soa.id = vhffs_dns.dns_id;
-ALTER TABLE vhffs_dns ALTER COLUMN ns SET NOT NULL;
 
 ALTER TABLE vhffs_dns ADD COLUMN mbox varchar(255);
-UPDATE vhffs_dns SET mbox = soa.mbox FROM vhffs_dns_soa soa WHERE soa.id = vhffs_dns.dns_id;
-ALTER TABLE vhffs_dns ALTER COLUMN mbox SET NOT NULL;
 
 ALTER TABLE vhffs_dns ADD COLUMN serial INTEGER;
 ALTER TABLE vhffs_dns ALTER COLUMN serial SET DEFAULT 1;
-UPDATE vhffs_dns SET serial = soa.serial FROM vhffs_dns_soa soa WHERE soa.id = vhffs_dns.dns_id;
-ALTER TABLE vhffs_dns ALTER COLUMN serial SET NOT NULL;
 
 ALTER TABLE vhffs_dns ADD COLUMN refresh INTEGER;
 ALTER TABLE vhffs_dns ALTER COLUMN refresh SET DEFAULT 28800;
-UPDATE vhffs_dns SET refresh = soa.refresh FROM vhffs_dns_soa soa WHERE soa.id = vhffs_dns.dns_id;
-ALTER TABLE vhffs_dns ALTER COLUMN refresh SET NOT NULL;
 
 ALTER TABLE vhffs_dns ADD COLUMN retry INTEGER;
 ALTER TABLE vhffs_dns ALTER COLUMN retry SET DEFAULT 7200;
-UPDATE vhffs_dns SET retry = soa.retry FROM vhffs_dns_soa soa WHERE soa.id = vhffs_dns.dns_id;
-ALTER TABLE vhffs_dns ALTER COLUMN retry SET NOT NULL;
 
 ALTER TABLE vhffs_dns ADD COLUMN expire INTEGER;
 ALTER TABLE vhffs_dns ALTER COLUMN expire SET DEFAULT 604800;
-UPDATE vhffs_dns SET expire = soa.expire FROM vhffs_dns_soa soa WHERE soa.id = vhffs_dns.dns_id;
-ALTER TABLE vhffs_dns ALTER COLUMN expire SET NOT NULL;
 
 ALTER TABLE vhffs_dns ADD COLUMN minimum INTEGER;
 ALTER TABLE vhffs_dns ALTER COLUMN minimum SET DEFAULT 86400;
-UPDATE vhffs_dns SET minimum = soa.minimum FROM vhffs_dns_soa soa WHERE soa.id = vhffs_dns.dns_id;
-ALTER TABLE vhffs_dns ALTER COLUMN minimum SET NOT NULL;
 
 ALTER TABLE vhffs_dns ADD COLUMN ttl INTEGER;
 ALTER TABLE vhffs_dns ALTER COLUMN ttl SET DEFAULT 86400;
-UPDATE vhffs_dns SET ttl = soa.ttl FROM vhffs_dns_soa soa WHERE soa.id = vhffs_dns.dns_id;
+
+UPDATE vhffs_dns SET ns = soa.ns, mbox = soa.mbox, serial = soa.serial, refresh = soa.refresh, retry = soa.retry,
+    expire = soa.expire, minimum = soa.minimum, ttl = soa.ttl
+    FROM vhffs_dns_soa soa
+    WHERE soa.id = vhffs_dns.dns_id;
+    
+ALTER TABLE vhffs_dns ALTER COLUMN ns SET NOT NULL;
+ALTER TABLE vhffs_dns ALTER COLUMN mbox SET NOT NULL;
+ALTER TABLE vhffs_dns ALTER COLUMN serial SET NOT NULL;
+ALTER TABLE vhffs_dns ALTER COLUMN refresh SET NOT NULL;
+ALTER TABLE vhffs_dns ALTER COLUMN retry SET NOT NULL;
+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;
 
 -- DROP TABLE vhffs_dns_soa;
 
 -- change the type of vhffs_object.state from varchar to int4 ..... (no comments allowed please)
 BEGIN;
-DROP VIEW vhffs_forum;
 ALTER TABLE vhffs_object ADD COLUMN state_new int4;
-UPDATE vhffs_object SET state_new = '0';
-UPDATE vhffs_object SET state_new = '1' WHERE state='1';
-UPDATE vhffs_object SET state_new = '2' WHERE state='2';
-UPDATE vhffs_object SET state_new = '3' WHERE state='3';
-UPDATE vhffs_object SET state_new = '4' WHERE state='4';
-UPDATE vhffs_object SET state_new = '5' WHERE state='5';
-UPDATE vhffs_object SET state_new = '6' WHERE state='6';
-UPDATE vhffs_object SET state_new = '7' WHERE state='7';
-UPDATE vhffs_object SET state_new = '8' WHERE state='8';
-UPDATE vhffs_object SET state_new = '9' WHERE state='9';
-UPDATE vhffs_object SET state_new = '10' WHERE state='10';
-UPDATE vhffs_object SET state_new = '11' WHERE state='11';
-UPDATE vhffs_object SET state_new = '12' WHERE state='12';
+UPDATE vhffs_object SET state_new = state::text::integer;
 ALTER TABLE vhffs_object DROP COLUMN state;
 ALTER TABLE vhffs_object RENAME COLUMN state_new TO state;
 ALTER TABLE vhffs_object ALTER COLUMN state SET NOT NULL;
@@ -197,12 +181,9 @@
 FROM vhffs_dns dns, vhffs_object o WHERE o.object_id=dns.object_id;
 
 -- update vhffs_forum view
+DROP VIEW vhffs_forum;
 CREATE VIEW vhffs_forum AS
 SELECT users.username, users.passwd, users.firstname, users.lastname, users.mail, users.date_creation, object.state
 FROM vhffs_users users, vhffs_object object
 WHERE object.object_id=users.object_id;
 
--- create somes index that improve overall performances
-CREATE INDEX idx_vhffs_httpd_servername_firstletter ON vhffs_httpd(substr(servername, 1, 1));
-CREATE INDEX idx_vhffs_object_state ON vhffs_object(state);
-CREATE INDEX idx_vhffs_dns_domain ON vhffs_dns(domain);