[vhffs-dev] [2141] added uniqueness on service.object_id

[subversion@xxxxxxxxxxxxx]

Revision: 2141
Author:   gradator
Date:     2012-03-11 18:28:18 +0100 (Sun, 11 Mar 2012)
Log Message:
-----------
added uniqueness on service.object_id

Modified Paths:
--------------
    trunk/vhffs-backend/src/pgsql/initdb.sql.in
    trunk/vhffs-compat/from-4.4-to-4.5.sql

Modified: trunk/vhffs-backend/src/pgsql/initdb.sql.in
===================================================================
--- trunk/vhffs-backend/src/pgsql/initdb.sql.in	2012-03-11 17:10:11 UTC (rev 2140)
+++ trunk/vhffs-backend/src/pgsql/initdb.sql.in	2012-03-11 17:28:18 UTC (rev 2141)
@@ -518,6 +518,21 @@
 ALTER TABLE vhffs_cron ADD CONSTRAINT vhffs_cron_unique_cronpath UNIQUE (cronpath);
 ALTER TABLE vhffs_tag_category ADD CONSTRAINT vhffs_tag_category_unique_label UNIQUE(label);
 ALTER TABLE vhffs_tag ADD CONSTRAINT vhffs_tag_unique_label_category UNIQUE(label , category_id);
+-- add uniqueness on service.object_id
+ALTER TABLE vhffs_cvs ADD CONSTRAINT vhffs_cvs_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_dns ADD CONSTRAINT vhffs_dns_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_groups ADD CONSTRAINT vhffs_groups_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_users ADD CONSTRAINT vhffs_users_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_httpd ADD CONSTRAINT vhffs_httpd_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_ml ADD CONSTRAINT vhffs_ml_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_mxdomain ADD CONSTRAINT vhffs_mxdomain_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_mysql ADD CONSTRAINT vhffs_mysql_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_pgsql ADD CONSTRAINT vhffs_pgsql_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_repository ADD CONSTRAINT vhffs_repository_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_svn ADD CONSTRAINT vhffs_svn_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_git ADD CONSTRAINT vhffs_git_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_mercurial ADD CONSTRAINT vhffs_mercurial_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_bazaar ADD CONSTRAINT vhffs_bazaar_unique_object_id UNIQUE(object_id);
 
 -- This index drastically improves performances on get_used_letters
 CREATE INDEX idx_vhffs_httpd_servername_firstletter ON vhffs_httpd(substr(servername, 1, 1));
@@ -555,21 +570,7 @@
 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);
-CREATE INDEX idx_vhffs_git_object_id ON vhffs_git(object_id);
-CREATE INDEX idx_vhffs_mercurial_object_id ON vhffs_mercurial(object_id);
-CREATE INDEX idx_vhffs_bazaar_object_id ON vhffs_bazaar(object_id);
 -- add index on dates, it is used to search old object, ...
 CREATE INDEX idx_vhffs_object_date_creation ON vhffs_object(date_creation);
 CREATE INDEX idx_vhffs_history_date ON vhffs_history(date);
@@ -651,6 +652,8 @@
 ALTER TABLE vhffs_tag_request ADD CONSTRAINT fk_vhffs_tag_request_vhffs_object FOREIGN KEY ( tagged_id ) REFERENCES vhffs_object( object_id ) ON DELETE SET NULL;
 
 
+-- VIEWS
+
 CREATE VIEW vhffs_passwd AS
 SELECT uid, gid, username, shell, '*'::character varying AS passwd, homedir
 FROM vhffs_users;

Modified: trunk/vhffs-compat/from-4.4-to-4.5.sql
===================================================================
--- trunk/vhffs-compat/from-4.4-to-4.5.sql	2012-03-11 17:10:11 UTC (rev 2140)
+++ trunk/vhffs-compat/from-4.4-to-4.5.sql	2012-03-11 17:28:18 UTC (rev 2141)
@@ -11,3 +11,33 @@
 SELECT uid, gid, username, shell, passwd, '0'::int4 as newtok , '0'::int4 as expired , homedir
 FROM vhffs_users;
 COMMIT;
+
+-- Former indexes on service.object_id are actually more comfortable with uniqueness
+DROP INDEX idx_vhffs_cvs_object_id;
+DROP INDEX idx_vhffs_dns_object_id;
+DROP INDEX idx_vhffs_groups_object_id;
+DROP INDEX idx_vhffs_users_object_id;
+DROP INDEX idx_vhffs_httpd_object_id;
+DROP INDEX idx_vhffs_ml_object_id;
+DROP INDEX idx_vhffs_mxdomain_object_id;
+DROP INDEX idx_vhffs_mysql_object_id;
+DROP INDEX idx_vhffs_pgsql_object_id;
+DROP INDEX idx_vhffs_repository_object_id;
+DROP INDEX idx_vhffs_svn_object_id;
+DROP INDEX idx_vhffs_git_object_id;
+DROP INDEX idx_vhffs_mercurial_object_id;
+DROP INDEX idx_vhffs_bazaar_object_id;
+ALTER TABLE vhffs_cvs ADD CONSTRAINT vhffs_cvs_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_dns ADD CONSTRAINT vhffs_dns_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_groups ADD CONSTRAINT vhffs_groups_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_users ADD CONSTRAINT vhffs_users_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_httpd ADD CONSTRAINT vhffs_httpd_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_ml ADD CONSTRAINT vhffs_ml_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_mxdomain ADD CONSTRAINT vhffs_mxdomain_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_mysql ADD CONSTRAINT vhffs_mysql_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_pgsql ADD CONSTRAINT vhffs_pgsql_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_repository ADD CONSTRAINT vhffs_repository_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_svn ADD CONSTRAINT vhffs_svn_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_git ADD CONSTRAINT vhffs_git_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_mercurial ADD CONSTRAINT vhffs_mercurial_unique_object_id UNIQUE(object_id);
+ALTER TABLE vhffs_bazaar ADD CONSTRAINT vhffs_bazaar_unique_object_id UNIQUE(object_id);