[vhffs-dev] [svn] commit: r241 - /trunk/vhffs-backend/src/pgsql/initdb.s

[vhffs-dev] [svn] commit: r241 - /trunk/vhffs-backend/src/pgsql/initdb.sql
[ Thread Index | Date Index | More vhffs.org/vhffs-dev Archives ]
To: vhffs-dev@xxxxxxxxx
Subject: [vhffs-dev] [svn] commit: r241 - /trunk/vhffs-backend/src/pgsql/initdb.sql
From: vhffs-dev@xxxxxxxxx
Date: Wed, 25 Oct 2006 06:59:12 -0000
Author: beuss
Date: Wed Oct 25 08:59:11 2006
New Revision: 241

Log:
SQL refactoring
all primary keys now have a name, all foreign key constraints too
non primary keys constraints are now declared at the end of the file, so that tables can be created in any order.


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

Modified: trunk/vhffs-backend/src/pgsql/initdb.sql
==============================================================================
--- trunk/vhffs-backend/src/pgsql/initdb.sql (original)
+++ trunk/vhffs-backend/src/pgsql/initdb.sql Wed Oct 25 08:59:11 2006
@@ -28,134 +28,11 @@
 # LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN 
 # ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 
 # POSSIBILITY OF SUCH DAMAGE.
-*/
-
-
-
-CREATE SEQUENCE seq_id_groups
-	INCREMENT 1
-	MINVALUE 1000
-	MAXVALUE 2147483647
-	START 1000
-	CACHE 1;
-
-CREATE SEQUENCE seq_id_users
-	INCREMENT 1
-	MINVALUE 1000
-	MAXVALUE 2147483647
-	START 1000
-	CACHE 1;
-
-CREATE SEQUENCE seq_id_mx
-	INCREMENT 1
-	MINVALUE 1000
-	MAXVALUE 2147483647
-	START 1000
-	CACHE 1;
-
-CREATE SEQUENCE seq_id_acl
-	INCREMENT 1
-	MINVALUE 1000
-	MAXVALUE 2147483647
-	START 1000
-	CACHE 1;
-
-
-CREATE SEQUENCE seq_id_cvs
-	INCREMENT 1
-	MINVALUE 1000
-	MAXVALUE 2147483647
-	START 1000
-	CACHE 1;
-
-CREATE SEQUENCE seq_id_httpd
-	INCREMENT 1
-	MINVALUE 1000
-	MAXVALUE 2147483647
-	START 1000
-	CACHE 1;
-
-
-CREATE SEQUENCE seq_id_ml
-	INCREMENT 1
-	MINVALUE 1000
-	MAXVALUE 2147483647
-	START 1000
-	CACHE 1;
-
-
-CREATE SEQUENCE seq_id_sub
-	INCREMENT 1
-	MINVALUE 1000
-	MAXVALUE 2147483647
-	START 1000
-	CACHE 1;
-
-CREATE SEQUENCE seq_id_mailings
-	INCREMENT 1
-	MINVALUE 1000
-	MAXVALUE 2147483647
-	START 1000
-	CACHE 1;
-
-CREATE SEQUENCE seq_virtualuid
-	INCREMENT 1
-	MINVALUE 10000
-	MAXVALUE 2147483647
-	START 10000
-	CACHE 1;
-
-CREATE SEQUENCE seq_virtualgid
-	INCREMENT 1
-	MINVALUE 10000
-	MAXVALUE 2147483647
-	START 10000
-	CACHE 1;
-
-
-CREATE SEQUENCE seq_id_pgsql
-	INCREMENT 1
-	MINVALUE 1000
-	MAXVALUE 2147483647
-	START 1000
-	CACHE 1;
-
-CREATE SEQUENCE seq_id_svn
-	INCREMENT 1
-	MINVALUE 1000
-	MAXVALUE 2147483647
-	START 1000
-	CACHE 1;
-
-
-CREATE SEQUENCE seq_id_svn_users
-	INCREMENT 1
-	MINVALUE 1000
-	MAXVALUE 2147483647
-	START 1000
-	CACHE 1;
-
-
-
-
-CREATE SEQUENCE seq_id_user_group
-	INCREMENT 1
-	MINVALUE 1000
-	MAXVALUE 2147483647
-	START 1000
-	CACHE 1;
-
-CREATE SEQUENCE seq_id_mysql
-	INCREMENT 1
-	MINVALUE 1000
-	MAXVALUE 2147483647
-	START 1000
-	CACHE 1;
-
+*/	
 
 CREATE TABLE vhffs_object
 (
-	object_id int4 NOT NULL DEFAULT nextval('seq_id_objects'::text),
+	object_id serial,
 	owner_uid int4 NOT NULL,
 	date_creation timestamp,
 	state varchar NOT NULL,
@@ -166,41 +43,36 @@
 
 CREATE TABLE vhffs_acl
 (
-	acl_id int4 NOT NULL DEFAULT nextval('seq_id_objects'::text),
-	oid_src int4 REFERENCES vhffs_object(object_id),
+	acl_id serial,
+	oid_src int4,
 	perm int4 NOT NULL,
-	oid_dst int4 REFERENCES vhffs_object(object_id),
+	oid_dst int4 ,
 	CONSTRAINT vhffs_acl_pkey PRIMARY KEY (acl_id)
 ) WITH OIDS;
 
 CREATE TABLE vhffs_groups
 (
-	gid serial NOT NULL,
-	groupname varchar(50) NOT NULL DEFAULT ' '::character varying,
-	passwd character varying(20),
+	gid serial,
+	groupname varchar(50) NOT NULL DEFAULT ' ',
+	passwd varchar(20),
 	quota int4 NOT NULL,
 	quota_used int4 NOT NULL DEFAULT 0,
-	object_id int4 NOT NULL REFERENCES vhffs_object( object_id ),
-    CONSTRAINT vhffs_groups_pkey2 FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON UPDATE CASCADE ON DELETE CASCADE,
+	object_id int4 NOT NULL,
 	CONSTRAINT vhffs_groups_pkey PRIMARY KEY (gid)
 ) WITH OIDS;
 
-
-
 CREATE TABLE vhffs_users
 (
-	uid serial NOT NULL,
+	uid serial,
 	gid int4 NOT NULL,
-	username varchar(50) NOT NULL DEFAULT ' '::character varying,
-	shell varchar(20) NOT NULL DEFAULT ' '::character varying,
-	passwd varchar(40) NOT NULL DEFAULT ' '::character varying,
-	homedir varchar(40) NOT NULL DEFAULT ' '::character varying,
+	username varchar(50) NOT NULL DEFAULT ' ',
+	shell varchar(20) NOT NULL DEFAULT ' ',
+	passwd varchar(40) NOT NULL DEFAULT ' ',
+	homedir varchar(40) NOT NULL DEFAULT ' ',
 	admin int4 NOT NULL,
 	object_id int4 NOT NULL,
-    CONSTRAINT vhffs_users_pkey2 FOREIGN KEY (object_id) REFERENCES vhffs_object (object_id) ON UPDATE CASCADE ON DELETE CASCADE,
 	CONSTRAINT vhffs_users_pkey PRIMARY KEY (uid)
 ) WITH OIDS;
-
 
 CREATE TABLE vhffs_boxes
 (
@@ -212,14 +84,15 @@
 	password varchar NOT NULL,
 	nospam boolean,
 	novirus boolean,
-	PRIMARY KEY (domain,local_part)
+	CONSTRAINT vhffs_boxes_pkey PRIMARY KEY (domain,local_part)
 ) WITH OIDS;
 
 
 CREATE TABLE vhffs_confirmation
 (
-	cid SERIAL PRIMARY KEY,
-	code varchar(10)
+	cid SERIAL,
+	code varchar(10),
+	CONSTRAINT vhffs_confirmation_pkey PRIMARY KEY (cid)
 ) WITH OIDS;
 
 
@@ -227,46 +100,39 @@
 
 CREATE TABLE vhffs_cvs
 (
-	cvs_id int4 NOT NULL DEFAULT nextval('seq_id_cvs'::text),
+	cvs_id serial,
 	cvsroot varchar NOT NULL,
-	owner_uid int4 REFERENCES vhffs_users( uid ), 
-	owner_gid int4 REFERENCES vhffs_groups( gid ),
+	owner_uid int4, 
+	owner_gid int4,
 	public boolean NOT NULL,
-	object_id int4 REFERENCES vhffs_object( object_id ),
+	object_id int4,
 	CONSTRAINT vhffs_cvs_pkey PRIMARY KEY (cvs_id)
 ) WITH OIDS;
-CREATE SEQUENCE seq_id_dns
-	INCREMENT 1
-	MINVALUE 1000
-	MAXVALUE 2147483647
-	START 1000
-	CACHE 1;
-
 
 CREATE TABLE vhffs_dns_global
 (
-	dns_id int4 DEFAULT nextval('seq_id_dns'::text) NOT NULL,
+	dns_id serial,
 	domain varchar(255) NOT NULL,
-	owner_gid int4 REFERENCES vhffs_groups( gid ),
-	owner_uid int4 REFERENCES vhffs_users( uid ),
-	object_id int4 REFERENCES vhffs_object( object_id ),
+	owner_gid int4,
+	owner_uid int4,
+	object_id int4,
 	CONSTRAINT vhffs_dns_pkey4 PRIMARY KEY ( dns_id )
 ) WITH OIDS;
 
-
 CREATE TABLE vhffs_dns_rr (
-	id serial NOT NULL PRIMARY KEY,
-	zone INTEGER NOT NULL REFERENCES vhffs_dns_global( dns_id),
+	id serial,
+	zone INTEGER NOT NULL,
 	name varchar(64) NOT NULL,
-	type VARCHAR(5) NOT NULL CHECK (type='A' OR type='AAAA' OR type='CNAME' OR type='HINFO' OR type='MX' OR type='NS' OR type='PTR' OR type='RP' OR type='SRV' OR type='TXT'),
+	type VARCHAR(5) NOT NULL,
 	data varchar(128) NOT NULL,
 	aux INTEGER NOT NULL DEFAULT 0,
-	ttl INTEGER NOT NULL DEFAULT 86400
-) WITH OIDS;
+	ttl INTEGER NOT NULL DEFAULT 86400,
+	CONSTRAINT vhffs_dns_rr_pkey PRIMARY KEY (id)
+) WITH OIDS;	
 
 CREATE TABLE vhffs_dns_soa
 (
-	id SERIAL NOT NULL PRIMARY KEY,
+	id serial,
 	origin varchar(255) NOT NULL,
 	ns varchar(255) NOT NULL,
 	mbox varchar(255) NOT NULL,
@@ -276,7 +142,8 @@
 	expire INTEGER NOT NULL DEFAULT 604800,
 	minimum INTEGER NOT NULL DEFAULT 86400,
 	ttl INTEGER NOT NULL DEFAULT 86400,
-	active INTEGER
+	active INTEGER,
+	CONSTRAINT vhffs_dns_soa_pkey PRIMARY KEY (id)
 ) WITH OIDS;
 
 CREATE TABLE vhffs_forward
@@ -293,27 +160,25 @@
 	gid int4 NOT NULL,
 	owner_uid int4,
 	uid_mod int4,
-	CONSTRAINT vhffs_groups_info_pkey PRIMARY KEY (gid),
-	CONSTRAINT vhffs_groups_info_fkey1 FOREIGN KEY (gid) REFERENCES vhffs_groups (gid) ON UPDATE CASCADE ON DELETE CASCADE,
-	CONSTRAINT vhffs_groups_info_pkey2 FOREIGN KEY (uid_mod) REFERENCES vhffs_users (uid) ON UPDATE CASCADE ON DELETE CASCADE
-) WITH OIDS;
-
-
+	CONSTRAINT vhffs_groups_info_pkey PRIMARY KEY (gid)
+) WITH OIDS;
 
 CREATE TABLE vhffs_httpd
 (
-	httpd_id int4 NOT NULL DEFAULT nextval('seq_id_httpd'::text) PRIMARY KEY,
-	servername varchar(200) NOT NULL DEFAULT ' '::character varying,
+	httpd_id serial,
+	servername varchar(200) NOT NULL DEFAULT ' ',
 	crawl int4 NOT NULL default '1',
 	owner_http int4 NOT NULL,
 	owner_rev int4 NOT NULL,
-	owner_uid int4 REFERENCES vhffs_users( uid ),
-	owner_gid int4 REFERENCES vhffs_groups( gid ),
+	owner_uid int4,
+	owner_gid int4,
 	trafic int4 NOT NULL,
 	alert_state int4 NOT NULL default '0',
 	alert_limit int4 NOT NULL default '80',
-	object_id int4 NOT NULL
-) WITH OIDS;
+	object_id int4 NOT NULL,
+	CONSTRAINT vhffs_httpd_pkey PRIMARY KEY (httpd_id)
+) WITH OIDS;
+
 CREATE TABLE vhffs_largefile
 (
 	file_id SERIAL,
@@ -331,9 +196,9 @@
 	ncheck			int4 DEFAULT '0',
 	virtual_uid		int4,
 	virtual_gid		int4,
-	owner_uid int4 REFERENCES vhffs_users( uid ), 
-	owner_gid int4 REFERENCES vhffs_groups( gid ),
-	object_id int4 REFERENCES vhffs_object( object_id ),
+	owner_uid 		int4, 
+	owner_gid		int4,
+	object_id		int4,
 	CONSTRAINT vhffs_largefile_pkey PRIMARY KEY( filename , owner_gid)
 ) WITH OIDS;
 
@@ -350,7 +215,7 @@
 
 CREATE TABLE vhffs_mxdomain
 (
-	mxdomain_id int4 NOT NULL DEFAULT nextval('seq_id_mx'::text),
+	mxdomain_id serial,
 	domain varchar UNIQUE,
 	unix_user varchar NOT NULL,
 	boxes_path varchar NOT NULL,
@@ -362,19 +227,17 @@
 	CONSTRAINT vhffs_mxdomain_pkey PRIMARY KEY (mxdomain_id)
 ) WITH OIDS;
 
-
-
 CREATE TABLE vhffs_ml
 (
-	ml_id int4 NOT NULL DEFAULT nextval('seq_id_ml'::text) PRIMARY KEY,
+	ml_id serial,
 	local_part VARCHAR NOT NULL,
 	domain VARCHAR REFERENCES vhffs_mxdomain( domain ),
 	prefix VARCHAR ,
-	owner_uid int4 REFERENCES vhffs_users( uid ), 
-	owner_gid int4 REFERENCES vhffs_groups( gid ),
-	object_id int4 REFERENCES vhffs_object( object_id)
-) WITH OIDS;
-
+	owner_uid int4, 
+	owner_gid int4,
+	object_id int4,
+	CONSTRAINT vhffs_ml_pkey PRIMARY KEY (ml_id)
+) WITH OIDS;
 
 CREATE TABLE vhffs_ml_prefs
 (
@@ -390,108 +253,89 @@
 
 CREATE TABLE vhffs_ml_subscribers
 (
-	sub_id int4 NOT NULL DEFAULT nextval('seq_id_sub'::text) PRIMARY KEY,
+	sub_id serial,
 	member VARCHAR NOT NULL,
 	perm int4 NOT NULL,
 	active int4 NOT NULL,
 	hash varchar NOT NULL,
-	ml_id int4 NOT NULL REFERENCES vhffs_ml( ml_id)
+	ml_id int4 NOT NULL,
+	CONSTRAINT vhffs_ml_subscribers_pkey PRIMARY KEY (sub_id)
 ) WITH OIDS;
 
 CREATE TABLE vhffs_ml_lang
 (
-	member VARCHAR NOT NULL PRIMARY KEY,
-	lang   VARCHAR NOT NULL
-) WITH OIDS;
-
-
-
-
+/* TODO use an int4 and link it to vhffs_ml_subscribers(sub_id) */
+	member VARCHAR NOT NULL,
+	lang   VARCHAR NOT NULL,
+	CONSTRAINT vhffs_ml_lang_pkey PRIMARY KEY (member)
+) WITH OIDS;
 
 CREATE TABLE vhffs_mysql
 (
-	mysql_id int4 NOT NULL DEFAULT nextval('seq_id_mysql'::text) PRIMARY KEY,
+	mysql_id serial,
 	dbname varchar(200) NOT NULL,
 	dbuser varchar(200) NOT NULL,
-	owner_uid int4 REFERENCES vhffs_users( uid ), 
-	owner_gid int4 REFERENCES vhffs_groups( gid ),
+	owner_uid int4, 
+	owner_gid int4,
 	dbpass VARCHAR(32) NOT NULL,
-	object_id int4 REFERENCES vhffs_object( object_id)
-) WITH OIDS;
+	object_id int4,
+	CONSTRAINT vhffs_mysql_pkey PRIMARY KEY (mysql_id)
+) WITH OIDS;
+
 CREATE TABLE vhffs_notes
 (
-	object_id int4 NOT NULL REFERENCES vhffs_object(object_id),
+	object_id int4 NOT NULL,
 	note int4 DEFAULT '0',
 	CONSTRAINT vhffs_notes_pkey PRIMARY KEY(object_id)
 ) WITH OIDS;
-CREATE SEQUENCE seq_id_history
-	INCREMENT 1
-	MINVALUE 1000
-	MAXVALUE 2147483647
-	START 1000
-	CACHE 1;
 
 CREATE TABLE vhffs_history
 (
-	history_id int4 NOT NULL DEFAULT nextval('seq_id_history'::text),
-	object_id int4 REFERENCES vhffs_object(object_id),
+	history_id serial,
+	object_id int4,
 	date timestamp,
 	message varchar(200),
 	CONSTRAINT vhffs_history_pkey PRIMARY KEY (history_id)
 ) WITH OIDS;
-CREATE SEQUENCE seq_id_objects
-	INCREMENT 1
-	MINVALUE 1000
-	MAXVALUE 2147483647
-	START 1000
-	CACHE 1;
-
 
 CREATE TABLE vhffs_panel_user_prefs
 (
-	uid int4 REFERENCES vhffs_users( uid ) PRIMARY KEY,
+	uid int4,
 	language varchar,
-	theme varchar
-) WITH OIDS;
-
-
-
+	theme varchar,
+	CONSTRAINT vhffs_panel_user_prefs_pkey PRIMARY KEY (uid)
+) WITH OIDS;
 
 CREATE TABLE vhffs_pgsql
 (
-	pgsql_id int4 NOT NULL DEFAULT nextval('seq_id_pgsql'::text) PRIMARY KEY,
-	dbname varchar(200) NOT NULL DEFAULT ' '::character varying,
+	pgsql_id serial,
+	dbname varchar(200) NOT NULL,
 	dbuser VARCHAR(16) NOT NULL,
 	dbpass VARCHAR(32) NOT NULL,
-	owner_uid int4 REFERENCES vhffs_users( uid ), 
-	owner_gid int4 REFERENCES vhffs_groups( gid ),
-	object_id int4 REFERENCES vhffs_object( object_id)
+	owner_uid int4, 
+	owner_gid int4,
+	object_id int4,
+	CONSTRAINT vhffs_pgsql_pkey PRIMARY KEY (pgsql_id)
 ) WITH OIDS;
 
 CREATE TABLE vhffs_svn
 (
 	svn_id SERIAL,
 	reponame varchar NOT NULL,
-	owner_uid int4 REFERENCES vhffs_users( uid ), 
-	owner_gid int4 REFERENCES vhffs_groups( gid ),
+	owner_uid int4, 
+	owner_gid int4,
 	public int4 NOT NULL,
-	object_id int4 REFERENCES vhffs_object( object_id ),
-	CONSTRAINT vhffs_svn_pkey2 PRIMARY KEY( reponame , owner_gid)
-) WITH OIDS;
-
-
-
+	object_id int4,
+	CONSTRAINT vhffs_svn_pkey PRIMARY KEY( reponame , owner_gid)
+) WITH OIDS;
 
 CREATE TABLE vhffs_user_group
 (
 	uid int4 ,
 	gid int4 ,
 	state int4 ,
-	CONSTRAINT vhffs_user_group_fkey1 FOREIGN KEY (uid) REFERENCES vhffs_users (uid) ON UPDATE CASCADE ON DELETE CASCADE,
-	CONSTRAINT vhffs_user_group_fkey2 FOREIGN KEY (gid) REFERENCES vhffs_groups (gid) ON UPDATE CASCADE ON DELETE CASCADE,
 	CONSTRAINT vhffs_user_group_pkey PRIMARY KEY (uid,gid)
 )WITH OIDS;
-
 
 CREATE TABLE vhffs_user_info
 (
@@ -505,18 +349,100 @@
 	country varchar(250),
 	mail varchar(200),
 	gpg_key varchar(250),
-	CONSTRAINT vhffs_user_info_pkey PRIMARY KEY (uid),
-	CONSTRAINT vhffs_user_info_fkey FOREIGN KEY (uid) REFERENCES vhffs_users (uid) ON UPDATE CASCADE ON DELETE CASCADE
-) WITH OIDS;
-
-
+	CONSTRAINT vhffs_user_info_pkey PRIMARY KEY (uid)
+) WITH OIDS;
+
+/****** Non primary key constraints.
+      Defining foreign keys here allow to create tables in any order.
+*******/
+
+ALTER TABLE vhffs_acl 
+	ADD CONSTRAINT fk_vhffs_acl_vhffs_object_dst FOREIGN KEY (oid_dst) REFERENCES vhffs_object(object_id) ON DELETE CASCADE,
+	ADD CONSTRAINT fk_vhffs_acl_vhffs_object_src FOREIGN KEY (oid_src) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
+
+ALTER TABLE vhffs_groups
+	ADD CONSTRAINT fk_vhffs_group_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
+
+ALTER TABLE vhffs_users
+	ADD CONSTRAINT fk_vhffs_users_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object (object_id) ON DELETE CASCADE;
+
+ALTER TABLE vhffs_cvs
+	ADD CONSTRAINT fk_vhffs_cvs_vhffs_users FOREIGN KEY (owner_uid) REFERENCES vhffs_users(uid) ON DELETE CASCADE,
+	ADD CONSTRAINT fk_vhffs_cvs_vhffs_groups FOREIGN KEY (owner_gid) REFERENCES vhffs_groups(gid) ON DELETE CASCADE,
+	ADD CONSTRAINT fk_vhffs_cvs_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
+
+ALTER TABLE vhffs_dns_global
+	ADD CONSTRAINT fk_vhffs_dns_vhffs_users FOREIGN KEY (owner_uid) REFERENCES vhffs_users(uid) ON DELETE CASCADE,
+	ADD CONSTRAINT fk_vhffs_dns_vhffs_groups FOREIGN KEY (owner_gid) REFERENCES vhffs_groups(gid) ON DELETE CASCADE,
+	ADD CONSTRAINT fk_vhffs_dns_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
+
+ALTER TABLE vhffs_dns_rr
+	ADD CONSTRAINT fk_vhffs_dns_rr_vhffs_dns FOREIGN KEY (zone) REFERENCES vhffs_dns_global(dns_id) ON DELETE CASCADE,
+	ADD CONSTRAINT fk_vhffs_dns_rr_chk_type CHECK (type='A' OR type='AAAA' OR type='CNAME' OR type='HINFO' OR type='MX' OR type='NS' OR type='PTR' OR type='RP' OR type='SRV' OR type='TXT');
+
+ALTER TABLE vhffs_groups_info
+	ADD CONSTRAINT vhffs_groups_info_vhffs_groups FOREIGN KEY (gid) REFERENCES vhffs_groups (gid) ON DELETE CASCADE,
+	ADD CONSTRAINT vhffs_groups_info_vhffs_users FOREIGN KEY (uid_mod) REFERENCES vhffs_users (uid) ON DELETE CASCADE;
+
+ALTER TABLE vhffs_httpd
+	ADD CONSTRAINT fk_vhffs_httpd_vhffs_users FOREIGN KEY (owner_uid) REFERENCES vhffs_users(uid) ON DELETE CASCADE,
+	ADD CONSTRAINT fk_vhffs_httpd_vhffs_groups FOREIGN KEY (owner_gid) REFERENCES vhffs_groups(gid) ON DELETE CASCADE;
+
+ALTER TABLE vhffs_largefile
+	ADD CONSTRAINT fk_vhffs_largefile_vhffs_users FOREIGN KEY (owner_uid) REFERENCES vhffs_users(uid) ON DELETE CASCADE,
+	ADD CONSTRAINT fk_vhffs_largefile_vhffs_groups FOREIGN KEY (owner_gid) REFERENCES vhffs_groups(gid) ON DELETE CASCADE,
+	ADD CONSTRAINT fk_vhffs_largefile_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
+
+ALTER TABLE vhffs_mxdomain
+	ADD CONSTRAINT fk_vhffs_mxdomain_vhffs_users FOREIGN KEY (owner_uid) REFERENCES vhffs_users(uid) ON DELETE CASCADE,
+	ADD CONSTRAINT fk_vhffs_mxdomain_vhffs_groups FOREIGN KEY (owner_gid) REFERENCES vhffs_groups(gid) ON DELETE CASCADE,
+	ADD CONSTRAINT fk_vhffs_mxdomain_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
+
+ALTER TABLE vhffs_ml
+	ADD CONSTRAINT fk_vhffs_ml_vhffs_users FOREIGN KEY (owner_uid) REFERENCES vhffs_users(uid) ON DELETE CASCADE,
+	ADD CONSTRAINT fk_vhffs_ml_vhffs_groups FOREIGN KEY (owner_gid) REFERENCES vhffs_groups(gid) ON DELETE CASCADE,
+	ADD CONSTRAINT fk_vhffs_ml_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
+
+ALTER TABLE vhffs_ml_subscribers
+	ADD CONSTRAINT fk_vhffs_ml_subscribers_vhffs_ml FOREIGN KEY (ml_id) REFERENCES vhffs_ml(ml_id);
+
+ALTER TABLE vhffs_mysql
+	ADD CONSTRAINT fk_vhffs_mysql_vhffs_users FOREIGN KEY (owner_uid) REFERENCES vhffs_users(uid) ON DELETE CASCADE,
+	ADD CONSTRAINT fk_vhffs_mysql_vhffs_groups FOREIGN KEY (owner_gid) REFERENCES vhffs_groups(gid) ON DELETE CASCADE,
+	ADD CONSTRAINT fk_vhffs_mysql_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
+
+ALTER TABLE vhffs_notes
+	ADD CONSTRAINT fk_vhffs_notes FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
+
+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_user_info
+	ADD CONSTRAINT vhffs_user_info_vhffs_users FOREIGN KEY (uid) REFERENCES vhffs_users (uid) ON UPDATE CASCADE ON DELETE CASCADE;
+
+ALTER TABLE vhffs_pgsql
+	ADD CONSTRAINT fk_vhffs_pgsql_vhffs_users FOREIGN KEY (owner_uid) REFERENCES vhffs_users(uid) ON DELETE CASCADE,
+	ADD CONSTRAINT fk_vhffs_pgsql_vhffs_groups FOREIGN KEY (owner_gid) REFERENCES vhffs_groups(gid) ON DELETE CASCADE,
+	ADD CONSTRAINT fk_vhffs_pgsql_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
+
+ALTER TABLE vhffs_svn
+	ADD CONSTRAINT fk_vhffs_svn_vhffs_users FOREIGN KEY (owner_uid) REFERENCES vhffs_users(uid) ON DELETE CASCADE,
+	ADD CONSTRAINT fk_vhffs_svn_vhffs_groups FOREIGN KEY (owner_gid) REFERENCES vhffs_groups(gid) ON DELETE CASCADE,
+	ADD CONSTRAINT fk_vhffs_svn_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
+
+ALTER TABLE vhffs_user_group
+	ADD CONSTRAINT vhffs_user_group_vhffs_users FOREIGN KEY (uid) REFERENCES vhffs_users (uid) ON UPDATE CASCADE ON DELETE CASCADE,
+	ADD CONSTRAINT vhffs_user_group_vhffs_groups FOREIGN KEY (gid) REFERENCES vhffs_groups (gid) ON UPDATE CASCADE ON DELETE CASCADE;
+
+ALTER TABLE vhffs_panel_user_prefs
+	ADD CONSTRAINT fk_vhffs_panel_user_prefs_vhffs_users FOREIGN KEY (uid) REFERENCES vhffs_users(uid) ON DELETE CASCADE;
 
 
 CREATE VIEW vhffs_passwd AS
-SELECT uid, gid, username, shell, '*' AS passwd, homedir
+SELECT uid, gid, username, shell, '*'::character varying AS passwd, homedir
 FROM vhffs_users;
 
 
 CREATE VIEW vhffs_shadow AS
-SELECT uid, gid, username, shell, passwd, '0' as newtok , '0' as expired , homedir
+SELECT uid, gid, username, shell, passwd, '0'::int4 as newtok , '0'::int4 as expired , homedir
 FROM vhffs_users;
Messages sorted by: [ date | thread ]
Prev by Date: Re: [vhffs-dev] compte svn pour beuss
Next by Date: [vhffs-dev] [svn] commit: r242 - /trunk/Makefile
Previous by thread: Re: [vhffs-dev] compte svn pour beuss
Next by thread: [vhffs-dev] [svn] commit: r242 - /trunk/Makefile
Mail converted by MHonArc 2.6.19+
http://listengine.tuxfamily.org/