| [vhffs-dev] [447] Added a script to help migrating from vhffs-4.0(pre1)? to vhffs-4. 1 and stay synced with svn db schema. |
[ Thread Index |
Date Index
| More vhffs.org/vhffs-dev Archives
]
- To: vhffs-dev@xxxxxxxxx
- Subject: [vhffs-dev] [447] Added a script to help migrating from vhffs-4.0(pre1)? to vhffs-4. 1 and stay synced with svn db schema.
- From: subversion@xxxxxxxxx
- Date: Thu, 15 Feb 2007 18:25:36 +0100
Revision: 447
Author: beuss
Date: 2007-02-15 17:25:35 +0000 (Thu, 15 Feb 2007)
Log Message:
-----------
Added a script to help migrating from vhffs-4.0(pre1)? to vhffs-4.1 and stay synced with svn db schema.
Modified Paths:
--------------
branches/vhffs_4.1/vhffs-backend/src/pgsql/initdb.sql
Added Paths:
-----------
branches/vhffs_4.1/vhffs-compat/4.0.sql
branches/vhffs_4.1/vhffs-compat/4.0pre1.sql
branches/vhffs_4.1/vhffs-compat/updatedb.pl
Removed Paths:
-------------
branches/vhffs_4.1/vhffs-backend/src/pgsql/alter_from4.0_tocurrent.sql
branches/vhffs_4.1/vhffs-backend/src/pgsql/alter_from4.0pre1_tocurrent.sql
branches/vhffs_4.1/vhffs-compat/fix_desc.pl
Deleted: branches/vhffs_4.1/vhffs-backend/src/pgsql/alter_from4.0_tocurrent.sql
===================================================================
--- branches/vhffs_4.1/vhffs-backend/src/pgsql/alter_from4.0_tocurrent.sql 2007-02-11 20:30:53 UTC (rev 446)
+++ branches/vhffs_4.1/vhffs-backend/src/pgsql/alter_from4.0_tocurrent.sql 2007-02-15 17:25:35 UTC (rev 447)
@@ -1,41 +0,0 @@
--- OK, we try to minimize risks by setting sequences' current values to
--- the greatest id. Ideally, vhffs should be stopped during upgrade
-
-SELECT setval('vhffs_object_object_id_seq', (SELECT COALESCE(MAX(object_id), 1) FROM vhffs_object));
-SELECT setval('vhffs_acl_acl_id_seq', (SELECT COALESCE(MAX(acl_id), 1) FROM vhffs_acl));
--- **** Change 10000 to your former min_gid
--- TODO : handle it in the installer
-SELECT setval('vhffs_groups_gid_seq', (SELECT COALESCE(MAX(gid), 10000) FROM vhffs_groups));
--- TODO : same as above but for uid
-SELECT setval('vhffs_users_uid_seq', (SELECT COALESCE(MAX(uid), 10000) FROM vhffs_users));
-SELECT setval('vhffs_confirmation_cid_seq', (SELECT COALESCE(MAX(cid), 1) FROM vhffs_confirmation));
-SELECT setval('vhffs_cvs_cvs_id_seq', (SELECT COALESCE(MAX(cvs_id), 1) FROM vhffs_cvs));
-SELECT setval('vhffs_dns_global_dns_id_seq', (SELECT COALESCE(MAX(dns_id), 1) FROM vhffs_dns_global));
-SELECT setval('vhffs_dns_rr_id_seq', (SELECT COALESCE(MAX(id), 1) FROM vhffs_dns_rr));
-SELECT setval('vhffs_dns_soa_id_seq', (SELECT COALESCE(MAX(id), 1) FROM vhffs_dns_soa));
-SELECT setval('vhffs_httpd_httpd_id_seq', (SELECT COALESCE(MAX(httpd_id), 1) FROM vhffs_httpd));
-SELECT setval('vhffs_mailings_id_mailing_seq', (SELECT COALESCE(MAX(id_mailing), 1) FROM vhffs_mailings));
-SELECT setval('vhffs_mxdomain_mxdomain_id_seq', (SELECT COALESCE(MAX(mxdomain_id), 1) FROM vhffs_mxdomain));
-SELECT setval('vhffs_ml_ml_id_seq', (SELECT COALESCE(MAX(ml_id), 1) FROM vhffs_ml));
-SELECT setval('vhffs_mysql_mysql_id_seq', (SELECT COALESCE(MAX(mysql_id), 1) FROM vhffs_mysql));
-SELECT setval('vhffs_history_history_id_seq', (SELECT COALESCE(MAX(history_id), 1) FROM vhffs_history));
-SELECT setval('vhffs_pgsql_pgsql_id_seq', (SELECT COALESCE(MAX(pgsql_id), 1) FROM vhffs_pgsql));
-SELECT setval('vhffs_repository_repository_id_seq', (SELECT COALESCE(MAX(repository_id), 1) FROM vhffs_repository));
-SELECT setval('vhffs_svn_svn_id_seq', (SELECT COALESCE(MAX(svn_id), 1) FROM vhffs_svn));
-
--- username must be unique
-ALTER TABLE vhffs_users ADD CONSTRAINT vhffs_users_unique_username UNIQUE (username);
-ALTER TABLE vhffs_groups ADD CONSTRAINT vhffs_groups_unique_groupname UNIQUE (groupname);
-ALTER TABLE vhffs_cvs ADD CONSTRAINT vhffs_cvs_unique_cvsroot UNIQUE (cvsroot);
-ALTER TABLE vhffs_httpd ADD CONSTRAINT vhffs_httpd_unique_servername UNIQUE (servername);
-
-
-ALTER TABLE vhffs_object ALTER owner_uid DROP NOT NULL;
-ALTER TABLE vhffs_users ALTER gid DROP NOT NULL;
-
--- This index drastically improves performances on get_used_letters
-CREATE INDEX idx_vhffs_httpd_servername_firstletter ON vhffs_httpd(substr(servername, 1, 1));
--- state is massively used in WHERE clause, this index improves overall performances
-CREATE INDEX idx_vhffs_object_state ON vhffs_object(state);
-
-DROP TABLE vhffs_largefile CASCADE;
Deleted: branches/vhffs_4.1/vhffs-backend/src/pgsql/alter_from4.0pre1_tocurrent.sql
===================================================================
--- branches/vhffs_4.1/vhffs-backend/src/pgsql/alter_from4.0pre1_tocurrent.sql 2007-02-11 20:30:53 UTC (rev 446)
+++ branches/vhffs_4.1/vhffs-backend/src/pgsql/alter_from4.0pre1_tocurrent.sql 2007-02-15 17:25:35 UTC (rev 447)
@@ -1,111 +0,0 @@
-/*
-# Copyright (c) vhffs project and its contributors
-# All rights reserved.
-#
-# Redistribution and use in source and binary forms, with or without
-# modification, are permitted provided that the following conditions
-# are met:
-#
-# 1. Redistributions of source code must retain the above copyright
-# notice, this list of conditions and the following disclaimer.
-#2. Redistributions in binary form must reproduce the above copyright
-# notice, this list of conditions and the following disclaimer in
-# the documentation and/or other materials provided with the
-# distribution.
-#3. Neither the name of vhffs nor the names of its contributors
-# may be used to endorse or promote products derived from this
-# software without specific prior written permission.
-#
-#THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
-#"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
-#LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
-#FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
-#COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
-#INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
-#BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
-#LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
-#CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
-# 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.
-*/
-
--- These are the sequences needed for post VHFFS-4.0pre1
--- To know if we need to update this, we should try a SELECT COUNT(*) FROM pg_class WHERE relname = 'seq_id_groups'
-
--- Drop former (unused) sequences...
-DROP SEQUENCE seq_id_groups;
-DROP SEQUENCE seq_id_users;
-DROP SEQUENCE seq_id_mx;
-DROP SEQUENCE seq_id_acl;
-DROP SEQUENCE seq_id_cvs;
-DROP SEQUENCE seq_id_httpd;
-DROP SEQUENCE seq_id_ml;
-DROP SEQUENCE seq_id_sub;
-DROP SEQUENCE seq_id_mailings;
-DROP SEQUENCE seq_virtualuid;
-DROP SEQUENCE seq_virtualgid;
-DROP SEQUENCE seq_id_pgsql;
-DROP SEQUENCE seq_id_svn;
-DROP SEQUENCE seq_id_svn_users;
-DROP SEQUENCE seq_id_user_group;
-DROP SEQUENCE seq_id_mysql;
-DROP SEQUENCE seq_id_dns;
-DROP SEQUENCE seq_id_history;
-DROP SEQUENCE seq_id_objects;
-
--- Create the same sequences as SERIAL would
-CREATE SEQUENCE vhffs_object_object_id_seq;
-ALTER TABLE vhffs_object ALTER COLUMN object_id SET DEFAULT nextval('vhffs_object_object_id_seq');
-CREATE SEQUENCE vhffs_acl_acl_id_seq;
-ALTER TABLE vhffs_acl ALTER COLUMN acl_id SET DEFAULT nextval('vhffs_acl_acl_id_seq');
--- vhffs_group_gid_seq already exists
--- vhffs_users_uid_seq already exists
--- vhffs_confirmation_cid_seq already exists
-CREATE SEQUENCE vhffs_cvs_cvs_id_seq;
-ALTER TABLE vhffs_cvs ALTER COLUMN cvs_id SET DEFAULT nextval('vhffs_cvs_cvs_id_seq');
-CREATE SEQUENCE vhffs_dns_global_dns_id_seq;
-ALTER TABLE vhffs_dns_global ALTER COLUMN dns_id SET DEFAULT nextval('vhffs_dns_global_dns_id_seq');
--- vhffs_dns_rr_id_seq already exists
--- vhffs_dns_soa_id_seq already exists
-CREATE SEQUENCE vhffs_httpd_httpd_id_seq;
-ALTER TABLE vhffs_httpd ALTER COLUMN httpd_id SET DEFAULT nextval('vhffs_httpd_httpd_id_seq');
--- vhffs_mailings_id_mailing_seq already exists
-CREATE SEQUENCE vhffs_mxdomain_mxdomain_id_seq;
-ALTER TABLE vhffs_mxdomain ALTER COLUMN mxdomain_id SET DEFAULT nextval('vhffs_mxdomain_mxdomain_id_seq');
-CREATE SEQUENCE vhffs_ml_ml_id_seq;
-ALTER TABLE vhffs_ml ALTER COLUMN ml_id SET DEFAULT nextval('vhffs_ml_ml_id_seq');
-CREATE SEQUENCE vhffs_ml_subscribers_sub_id_seq;
-ALTER TABLE vhffs_ml_subscribers ALTER COLUMN sub_id SET DEFAULT nextval('vhffs_ml_subscribers_sub_id_seq');
-CREATE SEQUENCE vhffs_mysql_mysql_id_seq;
-ALTER TABLE vhffs_mysql ALTER COLUMN mysql_id SET DEFAULT nextval('vhffs_mysql_mysql_id_seq');
-CREATE SEQUENCE vhffs_history_history_id_seq;
-ALTER TABLE vhffs_history ALTER COLUMN history_id SET DEFAULT nextval('vhffs_history_history_id_seq');
-CREATE SEQUENCE vhffs_pgsql_pgsql_id_seq;
-ALTER TABLE vhffs_pgsql ALTER COLUMN pgsql_id SET DEFAULT nextval('vhffs_pgsql_pgsql_id_seq');
--- vhffs_svn_svn_id_seq already exists
-
-CREATE TABLE vhffs_repository
-(
- repository_id serial,
- name varchar NOT NULL,
- owner_uid int4 NOT NULL,
- owner_gid int4 NOT NULL,
- quota int4 NOT NULL,
- quota_used int4 NOT NULL DEFAULT 0,
- object_id int4 NOT NULL,
- CONSTRAINT vhffs_repository_pkey PRIMARY KEY (repository_id)
-) WITH OIDS;
-
-ALTER TABLE vhffs_repository ADD CONSTRAINT fk_vhffs_vhffs_repository_vhffs_users FOREIGN KEY (owner_uid) REFERENCES vhffs_users(uid);
-ALTER TABLE vhffs_repository ADD CONSTRAINT fk_vhffs_vhffs_repository_vhffs_groups FOREIGN KEY (owner_gid) REFERENCES vhffs_groups(gid);
-ALTER TABLE vhffs_repository ADD CONSTRAINT fk_vhffs_vhffs_repository_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
-ALTER TABLE vhffs_history DROP CONSTRAINT "$1";
-ALTER TABLE vhffs_history ADD CONSTRAINT "fk_vhffs_history_vhffs_object" FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
-
-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;
-
-\i alter_from4.0_tocurrent.sql
Modified: branches/vhffs_4.1/vhffs-backend/src/pgsql/initdb.sql
===================================================================
--- branches/vhffs_4.1/vhffs-backend/src/pgsql/initdb.sql 2007-02-11 20:30:53 UTC (rev 446)
+++ branches/vhffs_4.1/vhffs-backend/src/pgsql/initdb.sql 2007-02-15 17:25:35 UTC (rev 447)
@@ -205,7 +205,7 @@
CREATE TABLE vhffs_mxdomain
(
mxdomain_id serial,
- domain varchar UNIQUE,
+ domain varchar,
unix_user varchar NOT NULL,
boxes_path varchar NOT NULL,
max_popbox int4 NOT NULL,
@@ -220,8 +220,8 @@
(
ml_id serial,
local_part VARCHAR NOT NULL,
- domain VARCHAR REFERENCES vhffs_mxdomain( domain ),
- prefix VARCHAR ,
+ domain VARCHAR,
+ prefix VARCHAR,
owner_uid int4,
owner_gid int4,
object_id int4,
@@ -230,14 +230,15 @@
CREATE TABLE vhffs_ml_prefs
(
- ml_id int4 REFERENCES vhffs_ml( ml_id ) PRIMARY KEY,
+ 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
+ url VARCHAR,
+ CONSTRAINT vhffs_ml_prefs_pkey PRIMARY KEY(ml_id)
) WITH OIDS;
CREATE TABLE vhffs_ml_subscribers
@@ -347,6 +348,7 @@
ALTER TABLE vhffs_groups ADD CONSTRAINT vhffs_groups_unique_groupname UNIQUE (groupname);
ALTER TABLE vhffs_cvs ADD CONSTRAINT vhffs_cvs_unique_cvsroot UNIQUE (cvsroot);
ALTER TABLE vhffs_httpd ADD CONSTRAINT vhffs_httpd_unique_servername UNIQUE (servername);
+ALTER TABLE vhffs_mxdomain ADD CONSTRAINT vhffs_mxdomain_unique_domainname UNIQUE (domain);
-- This index drastically improves performances on get_used_letters
CREATE INDEX idx_vhffs_httpd_servername_firstletter ON vhffs_httpd(substr(servername, 1, 1));
@@ -392,7 +394,10 @@
ALTER TABLE vhffs_ml ADD CONSTRAINT fk_vhffs_ml_vhffs_users FOREIGN KEY (owner_uid) REFERENCES vhffs_users(uid);
ALTER TABLE vhffs_ml ADD CONSTRAINT fk_vhffs_ml_vhffs_groups FOREIGN KEY (owner_gid) REFERENCES vhffs_groups(gid);
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);
Copied: branches/vhffs_4.1/vhffs-compat/4.0.sql (from rev 442, branches/vhffs_4.1/vhffs-backend/src/pgsql/alter_from4.0_tocurrent.sql)
===================================================================
--- branches/vhffs_4.1/vhffs-backend/src/pgsql/alter_from4.0_tocurrent.sql 2007-02-04 14:08:07 UTC (rev 442)
+++ branches/vhffs_4.1/vhffs-compat/4.0.sql 2007-02-15 17:25:35 UTC (rev 447)
@@ -0,0 +1,29 @@
+-- OK, we try to minimize risks by setting sequences' current values to
+-- the greatest id. Ideally, vhffs should be stopped during upgrade
+
+SELECT setval('vhffs_object_object_id_seq', (SELECT COALESCE(MAX(object_id), 1) FROM vhffs_object));
+SELECT setval('vhffs_acl_acl_id_seq', (SELECT COALESCE(MAX(acl_id), 1) FROM vhffs_acl));
+-- **** Change 10000 to your former min_gid
+-- TODO : handle it in the installer
+SELECT setval('vhffs_groups_gid_seq', (SELECT COALESCE(MAX(gid), 10000) FROM vhffs_groups));
+-- TODO : same as above but for uid
+SELECT setval('vhffs_users_uid_seq', (SELECT COALESCE(MAX(uid), 10000) FROM vhffs_users));
+SELECT setval('vhffs_confirmation_cid_seq', (SELECT COALESCE(MAX(cid), 1) FROM vhffs_confirmation));
+SELECT setval('vhffs_cvs_cvs_id_seq', (SELECT COALESCE(MAX(cvs_id), 1) FROM vhffs_cvs));
+SELECT setval('vhffs_dns_global_dns_id_seq', (SELECT COALESCE(MAX(dns_id), 1) FROM vhffs_dns_global));
+SELECT setval('vhffs_dns_rr_id_seq', (SELECT COALESCE(MAX(id), 1) FROM vhffs_dns_rr));
+SELECT setval('vhffs_dns_soa_id_seq', (SELECT COALESCE(MAX(id), 1) FROM vhffs_dns_soa));
+SELECT setval('vhffs_httpd_httpd_id_seq', (SELECT COALESCE(MAX(httpd_id), 1) FROM vhffs_httpd));
+SELECT setval('vhffs_mailings_id_mailing_seq', (SELECT COALESCE(MAX(id_mailing), 1) FROM vhffs_mailings));
+SELECT setval('vhffs_mxdomain_mxdomain_id_seq', (SELECT COALESCE(MAX(mxdomain_id), 1) FROM vhffs_mxdomain));
+SELECT setval('vhffs_ml_ml_id_seq', (SELECT COALESCE(MAX(ml_id), 1) FROM vhffs_ml));
+SELECT setval('vhffs_mysql_mysql_id_seq', (SELECT COALESCE(MAX(mysql_id), 1) FROM vhffs_mysql));
+SELECT setval('vhffs_history_history_id_seq', (SELECT COALESCE(MAX(history_id), 1) FROM vhffs_history));
+SELECT setval('vhffs_pgsql_pgsql_id_seq', (SELECT COALESCE(MAX(pgsql_id), 1) FROM vhffs_pgsql));
+SELECT setval('vhffs_repository_repository_id_seq', (SELECT COALESCE(MAX(repository_id), 1) FROM vhffs_repository));
+SELECT setval('vhffs_svn_svn_id_seq', (SELECT COALESCE(MAX(svn_id), 1) FROM vhffs_svn));
+
+ALTER TABLE vhffs_object ALTER owner_uid DROP NOT NULL;
+ALTER TABLE vhffs_users ALTER gid DROP NOT NULL;
+
+DROP TABLE vhffs_largefile CASCADE;
Copied: branches/vhffs_4.1/vhffs-compat/4.0pre1.sql (from rev 437, branches/vhffs_4.1/vhffs-backend/src/pgsql/alter_from4.0pre1_tocurrent.sql)
===================================================================
--- branches/vhffs_4.1/vhffs-backend/src/pgsql/alter_from4.0pre1_tocurrent.sql 2007-02-04 11:24:07 UTC (rev 437)
+++ branches/vhffs_4.1/vhffs-compat/4.0pre1.sql 2007-02-15 17:25:35 UTC (rev 447)
@@ -0,0 +1,105 @@
+/*
+# Copyright (c) vhffs project and its contributors
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions
+# are met:
+#
+# 1. Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+#2. Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in
+# the documentation and/or other materials provided with the
+# distribution.
+#3. Neither the name of vhffs nor the names of its contributors
+# may be used to endorse or promote products derived from this
+# software without specific prior written permission.
+#
+#THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
+#"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
+#LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
+#FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
+#COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
+#INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
+#BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+#LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
+#CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
+# 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.
+*/
+
+-- DO NOT RUN THIS SCRIPT MANUALLY ! Use updatedb.pl instead --
+
+-- These are the sequences needed for post VHFFS-4.0pre1
+-- To know if we need to update this, we should try a SELECT COUNT(*) FROM pg_class WHERE relname = 'seq_id_groups'
+
+-- Drop former (unused) sequences...
+DROP SEQUENCE seq_id_groups;
+DROP SEQUENCE seq_id_users;
+DROP SEQUENCE seq_id_mx;
+DROP SEQUENCE seq_id_acl;
+DROP SEQUENCE seq_id_cvs;
+DROP SEQUENCE seq_id_httpd;
+DROP SEQUENCE seq_id_ml;
+DROP SEQUENCE seq_id_sub;
+DROP SEQUENCE seq_id_mailings;
+DROP SEQUENCE seq_virtualuid;
+DROP SEQUENCE seq_virtualgid;
+DROP SEQUENCE seq_id_pgsql;
+DROP SEQUENCE seq_id_svn;
+DROP SEQUENCE seq_id_svn_users;
+DROP SEQUENCE seq_id_user_group;
+DROP SEQUENCE seq_id_mysql;
+DROP SEQUENCE seq_id_dns;
+DROP SEQUENCE seq_id_history;
+DROP SEQUENCE seq_id_objects;
+
+-- Create the same sequences as SERIAL would
+CREATE SEQUENCE vhffs_object_object_id_seq;
+ALTER TABLE vhffs_object ALTER COLUMN object_id SET DEFAULT nextval('vhffs_object_object_id_seq');
+CREATE SEQUENCE vhffs_acl_acl_id_seq;
+ALTER TABLE vhffs_acl ALTER COLUMN acl_id SET DEFAULT nextval('vhffs_acl_acl_id_seq');
+-- vhffs_group_gid_seq already exists
+-- vhffs_users_uid_seq already exists
+-- vhffs_confirmation_cid_seq already exists
+CREATE SEQUENCE vhffs_cvs_cvs_id_seq;
+ALTER TABLE vhffs_cvs ALTER COLUMN cvs_id SET DEFAULT nextval('vhffs_cvs_cvs_id_seq');
+CREATE SEQUENCE vhffs_dns_global_dns_id_seq;
+ALTER TABLE vhffs_dns_global ALTER COLUMN dns_id SET DEFAULT nextval('vhffs_dns_global_dns_id_seq');
+-- vhffs_dns_rr_id_seq already exists
+-- vhffs_dns_soa_id_seq already exists
+CREATE SEQUENCE vhffs_httpd_httpd_id_seq;
+ALTER TABLE vhffs_httpd ALTER COLUMN httpd_id SET DEFAULT nextval('vhffs_httpd_httpd_id_seq');
+-- vhffs_mailings_id_mailing_seq already exists
+CREATE SEQUENCE vhffs_mxdomain_mxdomain_id_seq;
+ALTER TABLE vhffs_mxdomain ALTER COLUMN mxdomain_id SET DEFAULT nextval('vhffs_mxdomain_mxdomain_id_seq');
+CREATE SEQUENCE vhffs_ml_ml_id_seq;
+ALTER TABLE vhffs_ml ALTER COLUMN ml_id SET DEFAULT nextval('vhffs_ml_ml_id_seq');
+CREATE SEQUENCE vhffs_ml_subscribers_sub_id_seq;
+ALTER TABLE vhffs_ml_subscribers ALTER COLUMN sub_id SET DEFAULT nextval('vhffs_ml_subscribers_sub_id_seq');
+CREATE SEQUENCE vhffs_mysql_mysql_id_seq;
+ALTER TABLE vhffs_mysql ALTER COLUMN mysql_id SET DEFAULT nextval('vhffs_mysql_mysql_id_seq');
+CREATE SEQUENCE vhffs_history_history_id_seq;
+ALTER TABLE vhffs_history ALTER COLUMN history_id SET DEFAULT nextval('vhffs_history_history_id_seq');
+CREATE SEQUENCE vhffs_pgsql_pgsql_id_seq;
+ALTER TABLE vhffs_pgsql ALTER COLUMN pgsql_id SET DEFAULT nextval('vhffs_pgsql_pgsql_id_seq');
+-- vhffs_svn_svn_id_seq already exists
+
+CREATE TABLE vhffs_repository
+(
+ repository_id serial,
+ name varchar NOT NULL,
+ owner_uid int4 NOT NULL,
+ owner_gid int4 NOT NULL,
+ quota int4 NOT NULL,
+ quota_used int4 NOT NULL DEFAULT 0,
+ object_id int4 NOT NULL,
+ CONSTRAINT vhffs_repository_pkey PRIMARY KEY (repository_id)
+) WITH OIDS;
+
+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;
Deleted: branches/vhffs_4.1/vhffs-compat/fix_desc.pl
===================================================================
--- branches/vhffs_4.1/vhffs-compat/fix_desc.pl 2007-02-11 20:30:53 UTC (rev 446)
+++ branches/vhffs_4.1/vhffs-compat/fix_desc.pl 2007-02-15 17:25:35 UTC (rev 447)
@@ -1,16 +0,0 @@
-#!/usr/bin/perl
-
-use lib "/usr/share/vhffs/api";
-use Vhffs::Main;
-
-my $vhffs = init Vhffs::Main;
-my $dbh = $vhffs->get_db_object();
-
-my $sql = 'SELECT object_id, description FROM vhffs_object';
-my $rows = $dbh->selectall_arrayref($sql);
-
-my $sth = $dbh->prepare('UPDATE vhffs_object SET description = ? WHERE object_id = ?');
-foreach(@{$rows}) {
- $_->[1] =~ s/\\//g;
- $sth->execute($_->[1], $_->[0]) or warn "Unable to update object #$_->[0] (description $_->[1])";
-}
Added: branches/vhffs_4.1/vhffs-compat/updatedb.pl
===================================================================
--- branches/vhffs_4.1/vhffs-compat/updatedb.pl 2007-02-11 20:30:53 UTC (rev 446)
+++ branches/vhffs_4.1/vhffs-compat/updatedb.pl 2007-02-15 17:25:35 UTC (rev 447)
@@ -0,0 +1,238 @@
+#!/usr/bin/perl
+# Copyright (c) vhffs project and its contributors
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions
+# are met:
+#
+# 1. Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# 2. Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in
+# the documentation and/or other materials provided with the
+# distribution.
+# 3. Neither the name of vhffs nor the names of its contributors
+# may be used to endorse or promote products derived from this
+# software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
+# "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
+# LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
+# FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
+# COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
+# INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
+# BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
+# CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
+# 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.
+#
+
+
+use lib "/usr/share/vhffs/api";
+use Term::ReadPassword;
+use File::Basename;
+use File::Temp;
+use DBI;
+use Cwd;
+use Vhffs::Main;
+use IO::File;
+
+use strict;
+
+# get vhffs sources base dir
+my $BASE_DIR = dirname(dirname(getcwd()."/$0"));
+my $SQL_DIR = $BASE_DIR.'/vhffs-backend/src/pgsql/';
+
+
+sub confirm($) {
+ my $text = shift;
+ my $answer;
+ do {
+ print "$text (Y/n) ? ";
+ chomp($answer = <STDIN>);
+ $answer = lc($answer);
+ } while($answer ne 'y' and $answer ne 'n' and $answer ne '');
+
+ return($answer ne 'n');
+}
+
+sub upgrade_40_pre1() {
+ my $script = $BASE_DIR.'/vhffs-compat/4.0pre1.sql';
+ system("psql -f $script");
+}
+
+sub upgrade_40() {
+ my $script = $BASE_DIR.'/vhffs-compat/4.0.sql';
+ system("psql -f $script");
+}
+
+sub is_40_pre1($) {
+ # seq_id_groups only exists in pre1, it isn't created in 4.0
+ my $dbh = shift;
+ my ($res) = $dbh->selectrow_array('SELECT count(*) FROM pg_catalog.pg_class WHERE relname=\'seq_id_groups\'');
+ return $res;
+}
+
+sub is_40($) {
+ my $dbh = shift;
+ my ($res) = $dbh->selectrow_array('select count(*) from pg_catalog.pg_constraint where conname = \'vhffs_users_unique_username\'');
+ return ($res == 0);
+
+}
+
+print "**** VHFFS UPDATEDB ****\nThis script will upgrade your database to the VHFFS 4.1's schema.\nEnsure that VHFFS is *stopped* !\nUse this script to your own risks!!!\n";
+if(!confirm('Do you still want to continue')) {
+ exit(-1);
+}
+
+my ($dbname, $dbuser, $dbpass);
+
+print 'Enter VHFFS DB name : ';
+chomp($dbname = <STDIN>);
+print 'Enter VHFFS DB username : ';
+chomp($dbuser = <STDIN>);
+$dbpass = read_password('Enter VHFFS DB password : ');
+
+my $dbh = DBI->connect("DBI:Pg:dbname=$dbname;host=localhost;port=5432",$dbuser, $dbpass);
+if(!$dbh) {
+ die "Cant connect to VHFFS DB\n";
+}
+
+# psql and pg_dump will not ask for a password using this
+my $pgpass = new File::Temp(DIR => '/tmp');
+print $pgpass "*:*:$dbname:$dbuser:$dbpass";
+$ENV{'PGDATABASE'} = $dbname;
+$ENV{'PGUSER'} = $dbuser;
+$ENV{'PGPASSFILE'} = $pgpass->filename;
+
+if(confirm('Do you want to perform a backup right now')) {
+ print 'Enter a filename for the backup file : ';
+ my $backupfile = <STDIN>;
+ chomp($backupfile);
+ my $bfh = new IO::File($backupfile, 'w');
+ die("Unable to open $backupfile : $!\n") unless($bfh);
+ my $pg_dump = new IO::File("pg_dump |");
+ while(<$pg_dump>) {
+ print $bfh $_;
+ }
+ $bfh->close();
+}
+
+if(!is_40($dbh) && !confirm('It seems that you are not running vhffs 4.0, do you still want to upgrade')) {
+ exit(-2);
+} else {
+ if(is_40_pre1($dbh)) {
+ print "Detected VHFFS 4.0-pre1, performing transition upgrade\n";
+ upgrade_40_pre1();
+ }
+}
+
+upgrade_40();
+
+my $INIT_DB = new IO::File($SQL_DIR.'initdb.sql', 'r');
+die("unable to open ${SQL_DIR}.initdb.sql : $!, DB not upgraded\n") unless($INIT_DB);
+
+print "Let's drop foreign keys...\n";
+
+# Fetches and removes all foreign key constraints
+
+my $fk = $dbh->selectall_arrayref('SELECT c.conname AS constraint, cl.relname AS table FROM pg_constraint c INNER JOIN pg_class cl ON c.conrelid = cl.oid WHERE c.contype = \'f\' AND cl.relname LIKE \'vhffs_%\'', { Slice => {} });
+
+foreach(@$fk) {
+ print "\tDropping $_->{table}($_->{constraint})\n";
+ $dbh->do("ALTER TABLE $_->{table} DROP CONSTRAINT $_->{constraint} CASCADE") or warn "Unable to drop constraint $_->{constraint}\n";
+}
+
+print " done.\nLet's drop unique key constraints\n";
+
+my $uc = $dbh->selectall_arrayref('SELECT c.conname AS constraint, cl.relname AS table FROM pg_catalog.pg_constraint c INNER JOIN pg_class cl ON c.conrelid = cl.oid WHERE contype = \'u\' AND cl.relname LIKE \'vhffs_%\'', { Slice => {} });
+
+foreach(@$uc) {
+ print "\tDropping $_->{table}($_->{constraint})\n";
+ $dbh->do("ALTER TABLE $_->{table} DROP CONSTRAINT $_->{constraint} CASCADE") or warn "Unable to drop constraint $_->{constraint}\n";
+}
+
+print " done.\nLet's drop indexes\n";
+
+my $idx = $dbh->selectall_arrayref('SELECT c.relname AS index FROM pg_catalog.pg_class c INNER JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid INNER JOIN pg_catalog.pg_class c2 ON c2.oid = i.indrelid WHERE i.indisprimary = \'f\' AND i.indisunique = \'f\' AND c2.relname LIKE \'vhffs_%\'', { Slice => {} });
+
+foreach(@$idx) {
+ print "\tDropping index $_->{index}\n";
+ $dbh->do("DROP INDEX $_->{index}") or warn "Unable to drop index $_->{index}\n";
+}
+
+print " done.\n";
+
+
+# Create new foreign key constraints based on initdb.sql content
+
+print "Creating new foreign key, indexes and unique keys...\n";
+
+while(<$INIT_DB>) {
+ if(/(ALTER TABLE\s+(.*?)\s+ADD CONSTRAINT\s+(.*?)\s+FOREIGN KEY.*);\s*$/i) {
+ print "\tCreating $2($3)\n";
+ $dbh->do($1) or warn("*** Unable to create constraint $3 ***\n");
+ } elsif(/(ALTER TABLE\s+(.*?)\s+ADD CONSTRAINT\s+(.*?)\s+UNIQUE.*);\s*$/i) {
+ print "\tCreating $2($3)\n";
+ $dbh->do($1) or warn("*** Unable to create constraint $3 ***\n");
+ } elsif(/(CREATE INDEX\s+(.*?)\s+ON\s(.*));\s*$/i) {
+ print "\tCreating $2 ON $3\n";
+ $dbh->do($1) or warn("*** Unable to create index $3 ***\n");
+ }
+}
+
+$INIT_DB->close();
+
+print "Fixing \\s in objects' description...";
+
+my $rows = $dbh->selectall_arrayref('SELECT object_id, description FROM vhffs_object');
+
+my $sth = $dbh->prepare('UPDATE vhffs_object SET description = ? WHERE object_id = ?');
+foreach(@{$rows}) {
+ $_->[1] =~ s/\\([^\\])/$1/g;
+ $sth->execute($_->[1], $_->[0]) or warn "Unable to update object #$_->[0] (description : $_->[1])\n";
+}
+
+print " done.\n";
+
+
+print "Fixing \\s in objects' history...";
+
+$rows = $dbh->selectall_arrayref('SELECT history_id, message FROM vhffs_history');
+
+$sth = $dbh->prepare('UPDATE vhffs_history SET message = ? WHERE history_id = ?');
+foreach(@$rows) {
+ $_->[1] =~ s/\\([^\\])/$1/g;
+ $sth->execute($_->[1], $_->[0]) or warn "Unable to update history #$_->[0] (message : $_->[1])\n";
+}
+
+print " done.\n";
+
+print "Fixing \\s in users' informations...";
+
+$rows = $dbh->selectall_arrayref('SELECT uid, shell FROM vhffs_users');
+
+$sth = $dbh->prepare('UPDATE vhffs_users SET shell = ? WHERE uid = ?');
+foreach(@$rows) {
+ $_->[1] =~ s/\\([^\\])/$1/g;
+ $sth->execute($_->[1], $_->[0]) or warn "Unable to update user #$_->[0] (shell : $_->[1])\n";
+}
+
+print " done.\n";
+
+$rows = $dbh->selectall_arrayref('SELECT uid, firstname, lastname, address, city, country FROM vhffs_user_info', { Slice => {} });
+
+$sth = $dbh->prepare('UPDATE vhffs_user_info SET firstname = ?, lastname = ?, address = ?, city = ?, country = ? WHERE uid = ?');
+foreach(@$rows) {
+ foreach my $prop (qw(firstname lastname address, city, country) ) {
+ $_->{$prop} =~ s/\\([^\\])/$1/g;
+ }
+ $sth->execute($_->{firstname}, $_->{lastname}, $_->{address}, $_->{city}, $_->{country}, $_->{uid}) or warn "Unable to update infos for user #$_{uid}";
+}
+
+print " done.";
+
+