[vhffs-dev] [1563] Add tables for tags from 4.1

[subversion@xxxxxxxxxxxxx]

Revision: 1563
Author:   misric
Date:     2010-03-17 19:54:40 +0100 (Wed, 17 Mar 2010)
Log Message:
-----------
Add tables for tags from 4.1

Modified Paths:
--------------
    trunk/vhffs-compat/from-4.1-to-4.2.sql

Modified: trunk/vhffs-compat/from-4.1-to-4.2.sql
===================================================================
--- trunk/vhffs-compat/from-4.1-to-4.2.sql	2010-03-15 09:07:09 UTC (rev 1562)
+++ trunk/vhffs-compat/from-4.1-to-4.2.sql	2010-03-17 18:54:40 UTC (rev 1563)
@@ -47,7 +47,72 @@
 ALTER TABLE vhffs_dns_rr ALTER COLUMN data SET NOT NULL;
 COMMIT;
 
+-- add Tags
 
+REATE TABLE vhffs_tag (
+    tag_id SERIAL,
+-- Label for the tag in platform's default language
+    label VARCHAR(30) NOT NULL,
+    description TEXT NOT NULL,
+    updated int8 NOT NULL,
+-- This tag's creator id, null if user has been deleted
+    updater_id int4,
+    category_id int4 NOT NULL,
+    CONSTRAINT vhffs_tag_pkey PRIMARY KEY( tag_id )
+) WITH OIDS;
+
+-- Table containing all tag categories for this platform
+-- See vhffs_tag for description...
+CREATE TABLE vhffs_tag_category (
+    tag_category_id SERIAL,
+    label VARCHAR(30) NOT NULL,
+    description TEXT NOT NULL,
+-- Access level of the category
+    visibility int4 NOT NULL DEFAULT 0,
+    updated int8 NOT NULL,
+    updater_id int4,
+    CONSTRAINT vhffs_tag_category_pkey PRIMARY KEY( tag_category_id )
+) WITH OIDS;
+
+-- Tag categories' translations
+CREATE TABLE vhffs_tag_category_translation (
+    tag_category_id int4 NOT NULL,
+    lang VARCHAR(16) NOT NULL,
+    label VARCHAR(30) NOT NULL,
+    description TEXT NOT NULL,
+    updated int8 NOT NULL,
+    updater_id int4,
+    CONSTRAINT vhffs_tag_category_translation_pkey PRIMARY KEY( tag_category_id, lang )
+) WITH OIDS;
+
+-- Tags requested by users
+CREATE TABLE vhffs_tag_request (
+    tag_request_id SERIAL,
+-- Label of the category. We could have a label
+-- and an id and fill in the correct field depending
+-- of the existence of the category
+    category_label VARCHAR(30) NOT NULL,
+    tag_label VARCHAR(30) NOT NULL,
+    created int8 NOT NULL,
+-- User who requested the tag
+    requester_id int4,
+-- For which object
+    tagged_id int4,
+    CONSTRAINT vhffs_tag_request_pkey PRIMARY KEY( tag_request_id )
+);
+
+-- Description & label translation for a tag
+CREATE TABLE vhffs_tag_translation (
+    tag_id int4 NOT NULL,
+    lang VARCHAR(16) NOT NULL,
+    label VARCHAR(30) NOT NULL,
+    description TEXT NOT NULL,
+    updated int8 NOT NULL,
+    updater_id int4,
+    CONSTRAINT vhffs_tag_translation_pkey PRIMARY KEY( tag_id, lang )
+) WITH OIDS;
+
+
 -- 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);