[vhffs-dev] [742] Add all necessary indexed, synchronise compat script result with init script result

[subversion@xxxxxxxxx]

Revision: 742
Author:   gradator
Date:     2007-08-01 01:33:47 +0000 (Wed, 01 Aug 2007)

Log Message:
-----------
Add all necessary indexed, synchronise compat script result with init script result

Modified Paths:
--------------
    trunk/vhffs-backend/src/pgsql/initdb.sql.in
    trunk/vhffs-compat/4.0.sql.in
    trunk/vhffs-compat/4.0pre1.sql


Modified: trunk/vhffs-backend/src/pgsql/initdb.sql.in
===================================================================
--- trunk/vhffs-backend/src/pgsql/initdb.sql.in	2007-07-31 23:49:25 UTC (rev 741)
+++ trunk/vhffs-backend/src/pgsql/initdb.sql.in	2007-08-01 01:33:47 UTC (rev 742)
@@ -67,7 +67,7 @@
 CREATE TABLE vhffs_users
 (
 	uid serial,
-	gid int4,
+	gid int4 NOT NULL,
 	username varchar(50) NOT NULL DEFAULT ' ',
 	shell varchar(20) NOT NULL DEFAULT ' ',
 	passwd varchar(40) NOT NULL DEFAULT ' ',
@@ -282,11 +282,46 @@
 
 -- This index drastically improves performances on get_used_letters
 CREATE INDEX idx_vhffs_httpd_servername_firstletter ON vhffs_httpd(substr(servername, 1, 1));
--- domain is massively used when manipulating mailing lists (yes we should use IDs)
-CREATE INDEX idx_vhffs_ml_domain ON vhffs_ml(domain);
 -- state is massively used in WHERE clause, this index improves overall performances
 CREATE INDEX idx_vhffs_object_state ON vhffs_object(state);
+-- state may be used in vhffs_mailing in where clause
+CREATE INDEX idx_vhffs_mailings_state ON vhffs_mailings(state);
+-- state is used in vhffs_user_group in where clauses
+CREATE INDEX idx_vhffs_user_group_state ON vhffs_user_group(state);
+-- vhffs_cvs.public may be used in where clause to display public cvs
+CREATE INDEX idx_vhffs_cvs_public ON vhffs_cvs(public);
+-- vhffs_svn.public may be used in where clause to display public svn
+CREATE INDEX idx_vhffs_svn_public ON vhffs_svn(public);
+-- vhffs_ml.open_archive may be used in where clause to select on public ml
+CREATE INDEX idx_vhffs_ml_open_archive ON vhffs_ml(open_archive);
+-- vhffs_object.owner_uid and owner_gid is used a lot in where clauses
+CREATE INDEX idx_vhffs_object_owner_uid ON vhffs_object(owner_uid);
+CREATE INDEX idx_vhffs_object_owner_gid ON vhffs_object(owner_gid);
+-- vhffs_object.type may be used in where clauses
+CREATE INDEX idx_vhffs_object_type ON vhffs_object(type);
+-- vhffs_users.gid may be used to find if a group is a workgroup group or a group associated to an user
+CREATE INDEX idx_vhffs_users_gid ON vhffs_users(gid);
+-- vhffs_users.admin may be used to display admin users
+CREATE INDEX idx_vhffs_users_admin ON vhffs_users(admin);
+-- vhffs_dns_rr needed indexes
+CREATE INDEX idx_vhffs_dns_rr_zone ON vhffs_dns_rr(zone);
+CREATE INDEX idx_vhffs_dns_rr_type ON vhffs_dns_rr(type);
+CREATE INDEX idx_vhffs_dns_rr_name ON vhffs_dns_rr(name);
+-- add indexes on service.object_id
+CREATE INDEX idx_vhffs_cvs_object_id ON vhffs_cvs(object_id);
+CREATE INDEX idx_vhffs_dns_object_id ON vhffs_dns(object_id);
+CREATE INDEX idx_vhffs_groups_object_id ON vhffs_groups(object_id);
+CREATE INDEX idx_vhffs_users_object_id ON vhffs_users(object_id);
+CREATE INDEX idx_vhffs_history_object_id ON vhffs_history(object_id);
+CREATE INDEX idx_vhffs_httpd_object_id ON vhffs_httpd(object_id);
+CREATE INDEX idx_vhffs_ml_object_id ON vhffs_ml(object_id);
+CREATE INDEX idx_vhffs_mxdomain_object_id ON vhffs_mxdomain(object_id);
+CREATE INDEX idx_vhffs_mysql_object_id ON vhffs_mysql(object_id);
+CREATE INDEX idx_vhffs_pgsql_object_id ON vhffs_pgsql(object_id);
+CREATE INDEX idx_vhffs_repository_object_id ON vhffs_repository(object_id);
+CREATE INDEX idx_vhffs_svn_object_id ON vhffs_svn(object_id);
 
+
 /****** Non primary key constraints.
       Defining foreign keys here allow to create tables in any order.
 *******/
@@ -309,6 +344,8 @@
 
 ALTER TABLE vhffs_history ADD CONSTRAINT fk_vhffs_history_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
 
+ALTER TABLE vhffs_httpd ADD CONSTRAINT fk_vhffs_httpd_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
+
 ALTER TABLE vhffs_ml ADD CONSTRAINT fk_vhffs_ml_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
 ALTER TABLE vhffs_ml ADD CONSTRAINT fk_vhffs_ml_vhffs_mxdomain FOREIGN KEY (domain) REFERENCES vhffs_mxdomain(domain) ON DELETE CASCADE;
 

Modified: trunk/vhffs-compat/4.0.sql.in
===================================================================
--- trunk/vhffs-compat/4.0.sql.in	2007-07-31 23:49:25 UTC (rev 741)
+++ trunk/vhffs-compat/4.0.sql.in	2007-08-01 01:33:47 UTC (rev 742)
@@ -5,7 +5,7 @@
 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_cvs_cvs_id_seq', (SELECT COALESCE(MAX(cvs_id), 1) FROM vhffs_cvs));
-SELECT setval('vhffs_dns_global_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_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));
@@ -23,8 +23,13 @@
 -- rename vhffs_dns_global to vhffs_dns
 ALTER TABLE vhffs_dns_global RENAME TO vhffs_dns;
 
+-- vhffs_groups
+ALTER TABLE vhffs_groups ALTER object_id DROP NOT NULL;
+
 -- vhffs_users
 ALTER TABLE vhffs_users ALTER gid DROP NOT NULL;
+ALTER TABLE vhffs_users ALTER object_id DROP NOT NULL;
+
 DROP VIEW vhffs_forum;
 -- date_creation will be dropped when vhffs_user_info will be dropped
 
@@ -88,6 +93,7 @@
 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 type SET NOT NULL;
 ALTER TABLE vhffs_object ALTER owner_gid SET NOT NULL;
 
 -- merge vhffs_user_info to vhffs_users
@@ -293,6 +299,7 @@
 
 -- some alter on vhffs_svn
 ALTER TABLE vhffs_svn ALTER object_id SET NOT NULL;
+ALTER TABLE vhffs_svn ADD CONSTRAINT vhffs_svn_pkey PRIMARY KEY (svn_id);
 
 -- some alter on vhffs_user_group
 ALTER TABLE vhffs_user_group ALTER uid SET NOT NULL;
@@ -304,3 +311,14 @@
 
 -- change naming scheme of vhffs_cvs (complete path instead single name)
 UPDATE vhffs_cvs SET cvsroot=(SELECT (SELECT g.groupname FROM vhffs_groups g WHERE g.gid=(SELECT o.owner_gid FROM vhffs_object o WHERE o.object_id=c.object_id) )||'/'||c.cvsroot FROM vhffs_cvs c WHERE c.cvs_id=vhffs_cvs.cvs_id);
+
+-- vhffs_groups
+ALTER TABLE vhffs_groups ALTER object_id SET NOT NULL;
+
+-- vhffs_users
+ALTER TABLE vhffs_users ALTER gid SET NOT NULL;
+ALTER TABLE vhffs_users ALTER object_id SET NOT NULL;
+ALTER TABLE vhffs_users ALTER COLUMN note SET DEFAULT 0;
+
+-- vhffs_ml
+ALTER TABLE vhffs_ml ALTER admin SET NOT NULL;

Modified: trunk/vhffs-compat/4.0pre1.sql
===================================================================
--- trunk/vhffs-compat/4.0pre1.sql	2007-07-31 23:49:25 UTC (rev 741)
+++ trunk/vhffs-compat/4.0pre1.sql	2007-08-01 01:33:47 UTC (rev 742)
@@ -66,8 +66,8 @@
 -- vhffs_confirmation_cid_seq already exists
 CREATE SEQUENCE vhffs_cvs_cvs_id_seq;
 ALTER TABLE vhffs_cvs ALTER COLUMN cvs_id SET DEFAULT nextval('vhffs_cvs_cvs_id_seq');
-CREATE SEQUENCE vhffs_dns_global_id_seq;
-ALTER TABLE vhffs_dns_global ALTER COLUMN dns_id SET DEFAULT nextval('vhffs_dns_global_id_seq');
+CREATE SEQUENCE vhffs_dns_dns_id_seq;
+ALTER TABLE vhffs_dns_global ALTER COLUMN dns_id SET DEFAULT nextval('vhffs_dns_dns_id_seq');
 -- vhffs_dns_rr_id_seq already exists
 -- vhffs_dns_soa_id_seq already exists
 CREATE SEQUENCE vhffs_httpd_httpd_id_seq;