[vhffs-dev] [1317] added necessary constraints on tags

[subversion@xxxxxxxxxxxxx]

Revision: 1317
Author:   gradator
Date:     2008-12-13 15:11:05 +0100 (Sat, 13 Dec 2008)

Log Message:
-----------
added necessary constraints on tags

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


Modified: trunk/vhffs-backend/src/pgsql/initdb.sql.in
===================================================================
--- trunk/vhffs-backend/src/pgsql/initdb.sql.in	2008-12-13 12:24:39 UTC (rev 1316)
+++ trunk/vhffs-backend/src/pgsql/initdb.sql.in	2008-12-13 14:11:05 UTC (rev 1317)
@@ -528,6 +528,8 @@
 ALTER TABLE vhffs_ml ADD CONSTRAINT vhffs_ml_unique_address UNIQUE (local_part, domain);
 ALTER TABLE vhffs_ml_subscribers ADD CONSTRAINT vhffs_ml_subscribers_member_list UNIQUE (ml_id, member);
 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);
 
 -- This index drastically improves performances on get_used_letters
 CREATE INDEX idx_vhffs_httpd_servername_firstletter ON vhffs_httpd(substr(servername, 1, 1));
@@ -581,6 +583,8 @@
 CREATE INDEX idx_vhffs_users_lastloginpanel ON vhffs_users(lastloginpanel);
 -- nextrundate is used to known which cron we should run
 CREATE INDEX idx_vhffs_cron_nextrun ON vhffs_cron(nextrundate);
+-- vhffs_tag_category.visibility may be used in where clauses
+CREATE INDEX idx_vhffs_tag_category_visibility ON vhffs_tag_category(visibility);
 
 /****** Non primary key constraints.
       Defining foreign keys here allow to create tables in any order.
@@ -642,6 +646,7 @@
 ALTER TABLE vhffs_tag_translation ADD CONSTRAINT fk_vhffs_tag_translation_vhffs_users FOREIGN KEY( updater_id ) REFERENCES vhffs_users( uid ) ON DELETE SET NULL;
 ALTER TABLE vhffs_object_tag ADD CONSTRAINT fk_vhffs_object_tag_vhffs_object FOREIGN KEY ( object_id ) REFERENCES vhffs_object( object_id ) ON DELETE CASCADE;
 ALTER TABLE vhffs_object_tag ADD CONSTRAINT fk_vhffs_object_tag_vhffs_tag FOREIGN KEY ( tag_id ) REFERENCES vhffs_tag( tag_id ) ON DELETE CASCADE;
+ALTER TABLE vhffs_object_tag ADD CONSTRAINT fk_vhffs_object_tag_vhffs_users FOREIGN KEY ( updater_id ) REFERENCES vhffs_users( uid ) ON DELETE SET NULL;
 ALTER TABLE vhffs_tag_request ADD CONSTRAINT fk_vhffs_tag_request_vhffs_user FOREIGN KEY ( requester_id ) REFERENCES vhffs_users( uid ) ON DELETE SET NULL;
 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;
 

Modified: trunk/vhffs-compat/4.1.sql
===================================================================
--- trunk/vhffs-compat/4.1.sql	2008-12-13 12:24:39 UTC (rev 1316)
+++ trunk/vhffs-compat/4.1.sql	2008-12-13 14:11:05 UTC (rev 1317)
@@ -46,3 +46,14 @@
 ALTER TABLE vhffs_dns_rr RENAME COLUMN data_new TO data;
 ALTER TABLE vhffs_dns_rr ALTER COLUMN data SET NOT NULL;
 COMMIT;
+
+
+-- add necessary constraints on tags
+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);
+
+ALTER TABLE vhffs_object_tag ADD CONSTRAINT fk_vhffs_object_tag_vhffs_users FOREIGN KEY ( updater_id ) REFERENCES vhffs_users( uid ) ON DELETE SET NULL;
+
+
+-- and not necessary but useful
+CREATE INDEX idx_vhffs_tag_category_visibility ON vhffs_tag_category(visibility);