| [vhffs-dev] [456] harvesting some spice to fill some silots |
[ Thread Index |
Date Index
| More vhffs.org/vhffs-dev Archives
]
Revision: 456
Author: gradator
Date: 2007-02-16 13:50:36 +0000 (Fri, 16 Feb 2007)
Log Message:
-----------
harvesting some spice to fill some silots
Modified Paths:
--------------
branches/vhffs_4.1/vhffs-backend/src/pgsql/initdb.sql
branches/vhffs_4.1/vhffs-compat/4.0.sql
Modified: branches/vhffs_4.1/vhffs-backend/src/pgsql/initdb.sql
===================================================================
--- branches/vhffs_4.1/vhffs-backend/src/pgsql/initdb.sql 2007-02-16 12:13:49 UTC (rev 455)
+++ branches/vhffs_4.1/vhffs-backend/src/pgsql/initdb.sql 2007-02-16 13:50:36 UTC (rev 456)
@@ -37,7 +37,7 @@
date_creation timestamp,
state varchar NOT NULL,
description TEXT,
- type int4 DEFAULT 0,
+ type int4 DEFAULT 0,
CONSTRAINT vhffs_object_pkey PRIMARY KEY (object_id)
) WITH OIDS;
@@ -59,6 +59,8 @@
quota int4 NOT NULL,
quota_used int4 NOT NULL DEFAULT 0,
object_id int4 NOT NULL,
+ owner_uid int4,
+ uid_mod int4,
CONSTRAINT vhffs_groups_pkey PRIMARY KEY (gid)
) WITH OIDS;
@@ -72,6 +74,18 @@
homedir varchar(40) NOT NULL DEFAULT ' ',
admin int4 NOT NULL,
object_id int4 NOT NULL,
+ date_creation timestamp,
+ firstname varchar(250),
+ lastname varchar(250),
+ address text,
+ zipcode varchar(16),
+ city varchar(250),
+ country varchar(250),
+ mail varchar(200),
+ gpg_key varchar(250),
+ note int4 DEFAULT '0',
+ language varchar(16),
+ theme varchar(250),
CONSTRAINT vhffs_users_pkey PRIMARY KEY (uid)
) WITH OIDS;
@@ -156,14 +170,6 @@
CONSTRAINT vhffs_forward_pkey PRIMARY KEY (domain , local_part)
) WITH OIDS;
-CREATE TABLE vhffs_groups_info
-(
- gid int4 NOT NULL,
- owner_uid int4,
- uid_mod int4,
- CONSTRAINT vhffs_groups_info_pkey PRIMARY KEY (gid)
-) WITH OIDS;
-
CREATE TABLE vhffs_httpd
(
httpd_id serial,
@@ -220,28 +226,22 @@
CREATE TABLE vhffs_ml
(
ml_id serial,
- local_part VARCHAR NOT NULL,
- domain VARCHAR,
- prefix VARCHAR,
+ local_part varchar NOT NULL,
+ domain varchar,
+ prefix varchar,
owner_uid int4,
owner_gid int4,
object_id int4,
+ admin varchar(250) NOT NULL,
+ open_post boolean,
+ open_archive boolean,
+ open_sub boolean,
+ reply_to boolean,
+ moderated boolean,
+ url varchar(512),
CONSTRAINT vhffs_ml_pkey PRIMARY KEY (ml_id)
) WITH OIDS;
-CREATE TABLE vhffs_ml_prefs
-(
- ml_id int4,
- admin VARCHAR NOT NULL,
- open_post int4 NOT NULL,
- open_archive int4 NOT NULL,
- open_sub int4 NOT NULL,
- reply_to int4,
- moderated int4 NOT NULL,
- url VARCHAR,
- CONSTRAINT vhffs_ml_prefs_pkey PRIMARY KEY(ml_id)
-) WITH OIDS;
-
CREATE TABLE vhffs_ml_subscribers
(
sub_id serial,
@@ -273,13 +273,6 @@
CONSTRAINT vhffs_mysql_pkey PRIMARY KEY (mysql_id)
) WITH OIDS;
-CREATE TABLE vhffs_notes
-(
- object_id int4 NOT NULL,
- note int4 DEFAULT '0',
- CONSTRAINT vhffs_notes_pkey PRIMARY KEY(object_id)
-) WITH OIDS;
-
CREATE TABLE vhffs_history
(
history_id serial,
@@ -289,14 +282,6 @@
CONSTRAINT vhffs_history_pkey PRIMARY KEY (history_id)
) WITH OIDS;
-CREATE TABLE vhffs_panel_user_prefs
-(
- uid int4,
- language varchar,
- theme varchar,
- CONSTRAINT vhffs_panel_user_prefs_pkey PRIMARY KEY (uid)
-) WITH OIDS;
-
CREATE TABLE vhffs_pgsql
(
pgsql_id serial,
@@ -328,22 +313,7 @@
CONSTRAINT vhffs_user_group_pkey PRIMARY KEY (uid,gid)
)WITH OIDS;
-CREATE TABLE vhffs_user_info
-(
- uid int4 NOT NULL,
- date_creation timestamp,
- firstname varchar(250),
- lastname varchar(250),
- address text,
- zipcode int4,
- city varchar(250),
- country varchar(250),
- mail varchar(200),
- gpg_key varchar(250),
- CONSTRAINT vhffs_user_info_pkey PRIMARY KEY (uid)
-) WITH OIDS;
-
/****** Indexes and unique constraints *******/
ALTER TABLE vhffs_users ADD CONSTRAINT vhffs_users_unique_username UNIQUE (username);
ALTER TABLE vhffs_groups ADD CONSTRAINT vhffs_groups_unique_groupname UNIQUE (groupname);
@@ -364,6 +334,7 @@
ALTER TABLE vhffs_acl 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_groups ADD CONSTRAINT fk_vhffs_group_vhffs_users FOREIGN KEY (owner_uid) REFERENCES vhffs_users (uid);
ALTER TABLE vhffs_users ADD CONSTRAINT fk_vhffs_users_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object (object_id) ON DELETE CASCADE;
@@ -378,9 +349,6 @@
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;
ALTER TABLE vhffs_dns_rr 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 fk_vhffs_groups_info_vhffs_groups FOREIGN KEY (gid) REFERENCES vhffs_groups (gid) ON DELETE CASCADE;
-ALTER TABLE vhffs_groups_info ADD CONSTRAINT fk_vhffs_groups_info_vhffs_users FOREIGN KEY (uid_mod) REFERENCES vhffs_users (uid);
-
ALTER TABLE vhffs_httpd ADD CONSTRAINT fk_vhffs_httpd_vhffs_users FOREIGN KEY (owner_uid) REFERENCES vhffs_users(uid);
ALTER TABLE vhffs_httpd ADD CONSTRAINT fk_vhffs_httpd_vhffs_groups FOREIGN KEY (owner_gid) REFERENCES vhffs_groups(gid);
@@ -397,20 +365,14 @@
ALTER TABLE vhffs_ml ADD CONSTRAINT fk_vhffs_ml_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
ALTER TABLE vhffs_ml ADD CONSTRAINT fk_vhffs_ml_vhffs_mxdomain FOREIGN KEY (domain) REFERENCES vhffs_mxdomain(domain) ON DELETE CASCADE;
-ALTER TABLE vhffs_ml_prefs ADD CONSTRAINT fk_vhffs_ml_prefs_vhffs_ml FOREIGN KEY (ml_id) REFERENCES vhffs_ml(ml_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);
ALTER TABLE vhffs_mysql ADD CONSTRAINT fk_vhffs_mysql_vhffs_groups FOREIGN KEY (owner_gid) REFERENCES vhffs_groups(gid);
ALTER TABLE vhffs_mysql 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 fk_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);
ALTER TABLE vhffs_pgsql ADD CONSTRAINT fk_vhffs_pgsql_vhffs_groups FOREIGN KEY (owner_gid) REFERENCES vhffs_groups(gid);
ALTER TABLE vhffs_pgsql ADD CONSTRAINT fk_vhffs_pgsql_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
@@ -422,9 +384,7 @@
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_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, '*'::character varying AS passwd, homedir
FROM vhffs_users;
@@ -434,6 +394,6 @@
FROM vhffs_users;
CREATE VIEW vhffs_forum AS
-SELECT users.username, users.passwd, user_info.firstname, user_info.lastname, user_info.mail, user_info.date_creation, object.state
-FROM vhffs_users users, vhffs_user_info user_info, vhffs_object object
-WHERE user_info.uid=users.uid AND object.object_id=users.object_id;
+SELECT users.username, users.passwd, users.firstname, users.lastname, users.mail, users.date_creation, object.state
+FROM vhffs_users users, vhffs_object object
+WHERE object.object_id=users.object_id;
Modified: branches/vhffs_4.1/vhffs-compat/4.0.sql
===================================================================
--- branches/vhffs_4.1/vhffs-compat/4.0.sql 2007-02-16 12:13:49 UTC (rev 455)
+++ branches/vhffs_4.1/vhffs-compat/4.0.sql 2007-02-16 13:50:36 UTC (rev 456)
@@ -49,6 +49,9 @@
UPDATE vhffs_object SET type=60 WHERE object_id IN (SELECT object_id FROM vhffs_mxdomain);
UPDATE vhffs_object SET type=61 WHERE object_id IN (SELECT object_id FROM vhffs_ml);
+-- cleanup unused objects
+DELETE FROM vhffs_object WHERE type='0';
+
-- merge vhffs_user_info to vhffs_users
ALTER TABLE vhffs_users ADD COLUMN date_creation timestamp;
UPDATE vhffs_users SET date_creation=vhffs_user_info.date_creation WHERE vhffs_users.uid=vhffs_user_info.uid;
@@ -105,3 +108,10 @@
UPDATE vhffs_ml SET moderated='t' WHERE vhffs_ml.ml_id=vhffs_ml_prefs.ml_id AND vhffs_ml_prefs.moderated='1';
ALTER TABLE vhffs_ml ADD COLUMN url varchar(512);
UPDATE vhffs_ml SET url=vhffs_ml_prefs.url WHERE vhffs_ml.ml_id=vhffs_ml_prefs.ml_id;
+
+-- update vhffs_forum view
+DROP VIEW vhffs_forum;
+CREATE VIEW vhffs_forum AS
+SELECT users.username, users.passwd, users.firstname, users.lastname, users.mail, users.date_creation, object.state
+FROM vhffs_users users, vhffs_object object
+WHERE object.object_id=users.object_id;