[vhffs-dev] [1171] Fix enhancement #236 (http://bugs.vhffs.org/view.php?id=236), this should be enough to hold all information we need for tags.

[subversion@xxxxxxxxxxxxx]

Revision: 1171
Author:   beuss
Date:     2008-03-03 12:37:47 +0000 (Mon, 03 Mar 2008)

Log Message:
-----------
Fix enhancement #236 (http://bugs.vhffs.org/view.php?id=236), this should be enough to hold all information we need for tags.

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


Modified: trunk/vhffs-backend/src/pgsql/initdb.sql.in
===================================================================
--- trunk/vhffs-backend/src/pgsql/initdb.sql.in	2008-03-01 21:53:20 UTC (rev 1170)
+++ trunk/vhffs-backend/src/pgsql/initdb.sql.in	2008-03-03 12:37:47 UTC (rev 1171)
@@ -30,35 +30,63 @@
 # POSSIBILITY OF SUCH DAMAGE.
 */	
 
+-- This platform's objects.
+-- Almost every entity in VHFFS has his corresponding record
+-- in vhffs_object. This table contains fields commons to all
+-- services. This is an implementation of inheritance... Maybe
+-- we could have used PostgreSQL built-in facilities...
+
 CREATE TABLE vhffs_object
 (
+-- ID of the object (referenced by child tables)
 	object_id serial,
+-- Owner of this object
 	owner_uid int4 NOT NULL,
+-- Group owning this object
 	owner_gid int4 NOT NULL,
+-- When was this object created?
 	date_creation int8 NOT NULL,
+-- State of the object (waiting for validation/creation/deletion and so on).
 	state int4 NOT NULL,
+-- Description of the object
 	description TEXT,
+-- Why this object has been refused, if applicable.
 	refuse_reason TEXT,
+-- Type of service. Allows us to provided customized
+-- info for each service through a factory (we could have
+-- used a view aggregating all services with common fields...)
 	type int4 NOT NULL DEFAULT 0,
 	CONSTRAINT vhffs_object_pkey PRIMARY KEY (object_id)
 ) WITH OIDS;
 
+-- Access control lists
 
 CREATE TABLE vhffs_acl
 (
+-- This object...
 	granted_oid int4,
+-- ...has this permission...
 	perm int4 NOT NULL,
+-- ...on this object
 	target_oid int4 ,
 	CONSTRAINT vhffs_acl_pkey PRIMARY KEY (granted_oid, target_oid)
 ) WITH OIDS;
 
+-- Platform's groups
+
 CREATE TABLE vhffs_groups
 (
+-- UNIX GID
 	gid serial,
+-- Name of the group (UNIX)
 	groupname varchar(50) NOT NULL,
+-- Human readable name (panel)
 	realname varchar(100),
+-- Group password (not used)
 	passwd varchar(20),
+-- This group quota (except for download repo), in MB
 	quota int4 NOT NULL,
+-- Used quota (MB)
 	quota_used int4 NOT NULL DEFAULT 0,
 	object_id int4 NOT NULL,
 	CONSTRAINT vhffs_groups_pkey PRIMARY KEY (gid)
@@ -66,16 +94,26 @@
 
 SELECT setval('vhffs_groups_gid_seq', @MINGID@);
 
+-- Platform's users
+
 CREATE TABLE vhffs_users
 (
+-- UNIX UID
 	uid serial,
+-- Main GID
 	gid int4 NOT NULL,
+-- Name of the user
 	username varchar(50) NOT NULL,
+-- Shell for the user
 	shell varchar(20) NOT NULL,
+-- Crypt'ed password
 	passwd varchar(40) NOT NULL,
+-- Home path
 	homedir varchar(40) NOT NULL,
+-- Access rights (user, moderator, administrator)
 	admin int4 NOT NULL,
 	object_id int4 NOT NULL,
+-- Mis info
 	firstname varchar(250),
 	lastname varchar(250),
 	address text,
@@ -87,38 +125,57 @@
 	note int4 DEFAULT 0,
 	language varchar(16),
 	theme varchar(250),
+-- Last date user logged in
 	lastloginpanel int8,
 	CONSTRAINT vhffs_users_pkey PRIMARY KEY (uid)
 ) WITH OIDS;
 
 SELECT setval('vhffs_users_uid_seq', @MINUID@) ;
 
+-- Mailboxes hosted on this platform
+
 CREATE TABLE vhffs_boxes
 (
+-- Maildomain to which the box is attached
 	domain varchar,
+-- Left part of the address
 	local_part varchar,
+-- Domain hash to compute box path (should'n we store it in mxdomain?)
 	domain_hash varchar NOT NULL,
+-- Box name appended to domain_hash
 	mbox_name varchar NOT NULL,
+-- Box password
 	password varchar NOT NULL,
+-- Is antispam activated
 	nospam boolean,
+-- Is antivirus activated?
 	novirus boolean,
+-- State of the box (we don't have object for this entity...)
 	state int4 NOT NULL,
 	CONSTRAINT vhffs_boxes_pkey PRIMARY KEY (domain,local_part)
 ) WITH OIDS;
 
+-- CVS repos on this platform
+
 CREATE TABLE vhffs_cvs
 (
 	cvs_id serial,
+-- repository path
 	cvsroot varchar NOT NULL,
+-- is it public or not?
 	public boolean NOT NULL,
 	object_id int4 NOT NULL,
 	CONSTRAINT vhffs_cvs_pkey PRIMARY KEY (cvs_id)
 ) WITH OIDS;
 
+-- DNS managed by this platform
+
 CREATE TABLE vhffs_dns
 (
 	dns_id serial,
+-- Domain name
 	domain varchar(255) NOT NULL,
+-- See myDNS docs
 	object_id int4 NOT NULL,
 	ns varchar(255) NOT NULL,
 	mbox varchar(255) NOT NULL,
@@ -131,9 +188,14 @@
 	CONSTRAINT vhffs_dns_pkey4 PRIMARY KEY (dns_id)
 ) WITH OIDS;
 
+-- Resource records for DNS on this platform
+
 CREATE TABLE vhffs_dns_rr (
 	id serial,
+-- Zone to which this record is linked
 	zone INTEGER NOT NULL,
+-- See myDNS manual for this fields' meaning,
+-- it can vary followin the type attribute.
 	name varchar(64) NOT NULL,
 	type VARCHAR(5) NOT NULL,
 	data varchar(128) NOT NULL,
@@ -142,130 +204,256 @@
 	CONSTRAINT vhffs_dns_rr_pkey PRIMARY KEY (id)
 ) WITH OIDS;	
 
+-- Mail forwards on this platform
+
 CREATE TABLE vhffs_forward
 (
+-- Mail domain to which this forward is linked
 	domain varchar NOT NULL,
+-- Left part of the mail address
 	local_part varchar NOT NULL,
+-- Mail address to which mails are forwarded
 	remote_name varchar NOT NULL,
+-- Is this used?!
 	password varchar NOT NULL,
 	CONSTRAINT vhffs_forward_pkey PRIMARY KEY (domain , local_part)
 ) WITH OIDS;
 
+-- Webareas for this platform
+
 CREATE TABLE vhffs_httpd
 (
 	httpd_id serial,
+-- Servername (virtual host), will be hashed to get the directory
 	servername varchar(200) NOT NULL,
 	object_id int4 NOT NULL,
 	CONSTRAINT vhffs_httpd_pkey PRIMARY KEY (httpd_id)
 ) WITH OIDS;
 
+-- Download repositories
+
 CREATE TABLE vhffs_repository
 (
 	repository_id serial,
+-- Name of the repository
 	name varchar NOT NULL,
+-- Group quota for this repos (MB)
 	quota int4 NOT NULL,
+-- Used quota (MB)
 	quota_used int4 NOT NULL DEFAULT 0,
 	object_id int4 NOT NULL,
 	CONSTRAINT vhffs_repository_pkey PRIMARY KEY (repository_id)
 ) WITH OIDS;
 
+-- Mass mailing made to the platform users
+
 CREATE TABLE vhffs_mailings
 (
 	id_mailing serial,
+-- Subject of the mail
 	subject VARCHAR NOT NULL,
+-- Message sent
 	message TEXT NOT NULL,
+-- Date for the mailing
 	date int8,
+-- State of the mailing (waiting to be sent, sent, ...)
 	state int4 NOT NULL,
 	CONSTRAINT vhffs_mailings_pkey PRIMARY KEY (id_mailing)
 ) WITH OIDS;
 
+-- Mail domains hosted on this platform
 
 CREATE TABLE vhffs_mxdomain
 (
 	mxdomain_id serial,
+-- Domain name
 	domain varchar,
+-- Where are the box stored? (Can't we compute it rather than store it?)
 	boxes_path varchar NOT NULL,
+-- Catchall address for the mail domain
 	catchall varchar NOT NULL,
 	object_id int4 NOT NULL,
 	CONSTRAINT vhffs_mxdomain_pkey PRIMARY KEY (mxdomain_id)
 ) WITH OIDS;
 
+-- Mailing lists of this platform
+
 CREATE TABLE vhffs_ml
 (
 	ml_id serial,
+-- Left part of the mailing list address
 	local_part varchar(256) NOT NULL,
+-- Right part of the ml address, refers to an existing mail domain
 	domain varchar(256) NOT NULL,
+-- Prefix prepended to all subjects
 	prefix varchar(32),
 	object_id int4 NOT NULL,
+-- How are subscriptions managed
 	sub_ctrl int4 NOT NULL,
+-- Posting policy
 	post_ctrl int4 NOT NULL,
+-- Add Reply-To header?
 	reply_to boolean,
+-- Do we keep open archives for this list?
 	open_archive boolean,
+-- Signature appended to all messages
 	signature varchar(250),
 	CONSTRAINT vhffs_ml_pkey PRIMARY KEY (ml_id)
 ) WITH OIDS;
 
+-- Subscribers of a mailing list
+
 CREATE TABLE vhffs_ml_subscribers
 (
 	sub_id serial,
+-- Email address of the subscriber
 	member varchar(256) NOT NULL,
+-- Access level of this member
 	perm int4 NOT NULL,
+-- Hash for activation
 	hash varchar,
+-- Mailing list to which this address has subscribed
 	ml_id int4 NOT NULL,
+-- Language of the subscriber
 	language varchar(16),
 	CONSTRAINT vhffs_ml_subscribers_pkey PRIMARY KEY (sub_id)
 ) WITH OIDS;
 
+-- MySQL databases
+
 CREATE TABLE vhffs_mysql
 (
 	mysql_id serial,
+-- Name of the database
 	dbname varchar(200) NOT NULL,
+-- Username for the DB
 	dbuser varchar(200) NOT NULL,
+-- DB password, cleared on creation
 	dbpass VARCHAR(32) NOT NULL,
 	object_id int4 NOT NULL,
 	CONSTRAINT vhffs_mysql_pkey PRIMARY KEY (mysql_id)
 ) WITH OIDS;
 
+-- Objects' history
+
 CREATE TABLE vhffs_history
 (
 	history_id serial,
 	object_id int4 NOT NULL,
+-- Timestamp of the entry
 	date int8 NOT NULL,
+-- History message
 	message varchar(200),
-	CONSTRAINT vhffs_history_pkey PRIMARY KEY (history_id)
+	CONSTRAINT vhffs_history_pkey PRIMARY KEY ( history_id )
 ) WITH OIDS;
 
+-- Link objects -> tags
+CREATE TABLE vhffs_object_tag (
+    object_id int4 NOT NULL,
+    tag_id int4 NOT NULL,
+    created int8 NOT NULL,
+    creator_id int4 NOT NULL,
+    CONSTRAINT vhffs_object_tag_pkey PRIMARY KEY ( object_id, tag_id )
+) WITH OIDS;
+
+-- PostgreSQL databases on the platform
+
 CREATE TABLE vhffs_pgsql
 (
 	pgsql_id serial,
+-- Name of the database
 	dbname varchar(200) NOT NULL,
+-- Username to access this database
 	dbuser varchar(200) NOT NULL,
+-- Password for the database, cleared after creation
 	dbpass varchar(32) NOT NULL,
+-- Character encoding for the database
     dbencoding varchar(20) NOT NULL DEFAULT 'LATIN1',
 	object_id int4 NOT NULL,
 	CONSTRAINT vhffs_pgsql_pkey PRIMARY KEY (pgsql_id)
 ) WITH OIDS;
 
+-- SVN repositories
+
 CREATE TABLE vhffs_svn
 (
 	svn_id SERIAL,
+-- Name (path) of the repo
 	reponame varchar NOT NULL,
+-- Is the repo public or not
 	public int4 NOT NULL,
+-- Mailing list to which send commit mails
 	ml_name varchar,
+-- Object representing this repository
 	object_id int4 NOT NULL,
 	CONSTRAINT vhffs_svn_pkey PRIMARY KEY( svn_id )
 ) WITH OIDS;
 
+-- Git repositories
+
 CREATE TABLE vhffs_git
 (
 	git_id SERIAL,
+-- Name of the repo
 	reponame varchar NOT NULL,
+-- Is the repo public or not
 	public int4 NOT NULL,
+-- Mailing list to which send commit mails
 	ml_name varchar,
+-- Object representing this Git repository
 	object_id int4 NOT NULL,
 	CONSTRAINT vhffs_git_pkey PRIMARY KEY( git_id )
 ) WITH OIDS;
 
+-- Table containing all registered tags for this platform
+
+CREATE TABLE vhffs_tag (
+    tag_id SERIAL,
+-- Label for the tag in platform's default language
+    label VARCHAR(30) NOT NULL,
+    description TEXT NOT NULL,
+    created int8 NOT NULL,
+-- This tag's creator id, null if user has been deleted
+    creator_id int4,
+    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,
+    created int8 NOT NULL,
+    creator_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,
+    created int8 NOT NULL,
+    creator_id int4,
+    CONSTRAINT vhffs_tag_category_translation_pkey PRIMARY KEY( tag_category_id, lang )
+) WITH OIDS;
+
+-- 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,
+    created int8 NOT NULL,
+    creator_id int4,
+    CONSTRAINT vhffs_tag_translation_pkey PRIMARY KEY( tag_id, lang )
+) WITH OIDS;
+
 CREATE TABLE vhffs_user_group
 (
 	uid int4 NOT NULL,
@@ -392,7 +580,16 @@
 ALTER TABLE vhffs_user_group ADD CONSTRAINT fk_vhffs_user_group_vhffs_users FOREIGN KEY (uid) REFERENCES vhffs_users (uid) ON DELETE CASCADE;
 ALTER TABLE vhffs_user_group ADD CONSTRAINT fk_vhffs_user_group_vhffs_groups FOREIGN KEY (gid) REFERENCES vhffs_groups (gid) ON DELETE CASCADE;
 
+ALTER TABLE vhffs_tag ADD CONSTRAINT fk_vhffs_tag_vhffs_users FOREIGN KEY (creator_id) REFERENCES vhffs_users(uid) ON DELETE SET NULL;
+ALTER TABLE vhffs_tag_category ADD CONSTRAINT fk_vhffs_tag_category_vhffs_users FOREIGN KEY (creator_id) REFERENCES vhffs_users(uid) ON DELETE SET NULL;
+ALTER TABLE vhffs_tag_category_translation ADD CONSTRAINT fk_vhffs_tag_category_translation_vhffs_tag_category FOREIGN KEY ( tag_category_id ) REFERENCES vhffs_tag_category(tag_category_id) ON DELETE CASCADE;
+ALTER TABLE vhffs_tag_category_translation ADD CONSTRAINT fk_vhffs_tag_category_translation_vhffs_users FOREIGN KEY ( creator_id ) REFERENCES vhffs_users(uid) ON DELETE SET NULL;
+ALTER TABLE vhffs_tag_translation ADD CONSTRAINT fk_vhffs_tag_translation_vhffs_tag FOREIGN KEY( tag_id ) REFERENCES vhffs_tag( tag_id ) ON DELETE CASCADE;
+ALTER TABLE vhffs_tag_translation ADD CONSTRAINT fk_vhffs_tag_translation_vhffs_users FOREIGN KEY( creator_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;
 
+
 CREATE VIEW vhffs_passwd AS
 SELECT uid, gid, username, shell, '*'::character varying AS passwd, homedir
 FROM vhffs_users;