[vhffs-dev] [1490] make it clear

[subversion@xxxxxxxxxxxxx]

Revision: 1490
Author:   gradator
Date:     2009-07-31 17:22:33 +0200 (Fri, 31 Jul 2009)

Log Message:
-----------
make it clear

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

Removed Paths:
-------------
    trunk/vhffs-compat/4.1.sql
    trunk/vhffs-compat/4.2.sql


Deleted: trunk/vhffs-compat/4.1.sql
===================================================================
--- trunk/vhffs-compat/4.1.sql	2009-07-31 15:22:03 UTC (rev 1489)
+++ trunk/vhffs-compat/4.1.sql	2009-07-31 15:22:33 UTC (rev 1490)
@@ -1,66 +0,0 @@
-ALTER TABLE vhffs_pgsql ADD COLUMN dbencoding VARCHAR(20) NOT NULL DEFAULT 'LATIN1';
-
--- 16 bytes is not enough for vhffs_pgsql.dbuser
-BEGIN;
-ALTER TABLE vhffs_pgsql ADD COLUMN dbuser_new varchar(200);
-UPDATE vhffs_pgsql SET dbuser_new = dbuser;
-ALTER TABLE vhffs_pgsql DROP COLUMN dbuser; 
-ALTER TABLE vhffs_pgsql RENAME COLUMN dbuser_new TO dbuser;
-ALTER TABLE vhffs_pgsql ALTER COLUMN dbuser SET NOT NULL;
-COMMIT;
-
--- add allowpop and allowimap field to vhffs_boxes
-ALTER TABLE vhffs_boxes ADD COLUMN allowpop boolean;
-ALTER TABLE vhffs_boxes ALTER COLUMN allowpop SET DEFAULT true;
-UPDATE vhffs_boxes SET allowpop=true;
-ALTER TABLE vhffs_boxes ALTER COLUMN allowpop SET NOT NULL;
-
-ALTER TABLE vhffs_boxes ADD COLUMN allowimap boolean;
-ALTER TABLE vhffs_boxes ALTER COLUMN allowimap SET DEFAULT true;
-UPDATE vhffs_boxes SET allowimap=true;
-ALTER TABLE vhffs_boxes ALTER COLUMN allowimap SET NOT NULL;
-
--- add vhffs_cron table
-CREATE TABLE vhffs_cron
-(
-        cron_id SERIAL,
-        cronpath varchar NOT NULL,
-        interval int4 NOT NULL,
-        reportmail varchar NOT NULL,
-        lastrundate int8,
-        lastrunreturncode int4,
-        nextrundate int8,
-        running int4,
-        object_id int4 NOT NULL,
-        CONSTRAINT vhffs_cron_pkey PRIMARY KEY( cron_id )
-) WITH OIDS;
-ALTER TABLE vhffs_cron ADD CONSTRAINT vhffs_cron_unique_cronpath UNIQUE (cronpath);
-CREATE INDEX idx_vhffs_cron_nextrun ON vhffs_cron(nextrundate);
-ALTER TABLE vhffs_cron ADD CONSTRAINT fk_vhffs_vhffs_cron_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
-
--- 128 bytes is not enough for vhffs_dns_rr.data, TXT fields might be bigger
-BEGIN;
-ALTER TABLE vhffs_dns_rr ADD COLUMN data_new varchar(512);
-UPDATE vhffs_dns_rr SET data_new = data;
-ALTER TABLE vhffs_dns_rr DROP COLUMN data; 
-ALTER TABLE vhffs_dns_rr RENAME COLUMN data_new TO data;
-ALTER TABLE vhffs_dns_rr ALTER COLUMN data SET NOT NULL;
-COMMIT;
-
-
--- 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);
-
-ALTER TABLE vhffs_object_tag ADD CONSTRAINT fk_vhffs_object_tag_vhffs_users FOREIGN KEY ( updater_id ) REFERENCES vhffs_users( uid ) ON DELETE SET NULL;
-
--- and not necessary but useful
-CREATE INDEX idx_vhffs_tag_category_visibility ON vhffs_tag_category(visibility);
-
-
--- delete duplicate values in vhffs_ml_subscribers (inserted members where not lowered)
-DELETE FROM vhffs_ml_subscribers WHERE EXISTS ( SELECT 'x' FROM vhffs_ml_subscribers m WHERE LOWER(m.member) = LOWER(vhffs_ml_subscribers.member) AND m.ml_id = vhffs_ml_subscribers.ml_id AND m.sub_id < vhffs_ml_subscribers.sub_id );
-UPDATE vhffs_ml_subscribers set member=LOWER(member);
-
--- Server encoding is always used to avoid compatibility issues
-ALTER TABLE vhffs_pgsql DROP COLUMN dbencoding;

Deleted: trunk/vhffs-compat/4.2.sql
===================================================================
--- trunk/vhffs-compat/4.2.sql	2009-07-31 15:22:03 UTC (rev 1489)
+++ trunk/vhffs-compat/4.2.sql	2009-07-31 15:22:33 UTC (rev 1490)
@@ -1,7 +0,0 @@
--- update_id field is cleared when the user is unsubscribing
-ALTER TABLE vhffs_object_tag ALTER COLUMN updater_id DROP NOT NULL;
-
--- add the user who modified the object in the object's history
-ALTER TABLE vhffs_history ADD COLUMN source_uid INTEGER DEFAULT NULL;
-ALTER TABLE vhffs_history ADD CONSTRAINT fk_vhffs_history_vhffs_users FOREIGN KEY (source_uid) REFERENCES vhffs_users(uid);
-

Copied: trunk/vhffs-compat/from-4.1-to-4.2.sql (from rev 1489, trunk/vhffs-compat/4.1.sql)
===================================================================
--- trunk/vhffs-compat/from-4.1-to-4.2.sql	                        (rev 0)
+++ trunk/vhffs-compat/from-4.1-to-4.2.sql	2009-07-31 15:22:33 UTC (rev 1490)
@@ -0,0 +1,66 @@
+ALTER TABLE vhffs_pgsql ADD COLUMN dbencoding VARCHAR(20) NOT NULL DEFAULT 'LATIN1';
+
+-- 16 bytes is not enough for vhffs_pgsql.dbuser
+BEGIN;
+ALTER TABLE vhffs_pgsql ADD COLUMN dbuser_new varchar(200);
+UPDATE vhffs_pgsql SET dbuser_new = dbuser;
+ALTER TABLE vhffs_pgsql DROP COLUMN dbuser; 
+ALTER TABLE vhffs_pgsql RENAME COLUMN dbuser_new TO dbuser;
+ALTER TABLE vhffs_pgsql ALTER COLUMN dbuser SET NOT NULL;
+COMMIT;
+
+-- add allowpop and allowimap field to vhffs_boxes
+ALTER TABLE vhffs_boxes ADD COLUMN allowpop boolean;
+ALTER TABLE vhffs_boxes ALTER COLUMN allowpop SET DEFAULT true;
+UPDATE vhffs_boxes SET allowpop=true;
+ALTER TABLE vhffs_boxes ALTER COLUMN allowpop SET NOT NULL;
+
+ALTER TABLE vhffs_boxes ADD COLUMN allowimap boolean;
+ALTER TABLE vhffs_boxes ALTER COLUMN allowimap SET DEFAULT true;
+UPDATE vhffs_boxes SET allowimap=true;
+ALTER TABLE vhffs_boxes ALTER COLUMN allowimap SET NOT NULL;
+
+-- add vhffs_cron table
+CREATE TABLE vhffs_cron
+(
+        cron_id SERIAL,
+        cronpath varchar NOT NULL,
+        interval int4 NOT NULL,
+        reportmail varchar NOT NULL,
+        lastrundate int8,
+        lastrunreturncode int4,
+        nextrundate int8,
+        running int4,
+        object_id int4 NOT NULL,
+        CONSTRAINT vhffs_cron_pkey PRIMARY KEY( cron_id )
+) WITH OIDS;
+ALTER TABLE vhffs_cron ADD CONSTRAINT vhffs_cron_unique_cronpath UNIQUE (cronpath);
+CREATE INDEX idx_vhffs_cron_nextrun ON vhffs_cron(nextrundate);
+ALTER TABLE vhffs_cron ADD CONSTRAINT fk_vhffs_vhffs_cron_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
+
+-- 128 bytes is not enough for vhffs_dns_rr.data, TXT fields might be bigger
+BEGIN;
+ALTER TABLE vhffs_dns_rr ADD COLUMN data_new varchar(512);
+UPDATE vhffs_dns_rr SET data_new = data;
+ALTER TABLE vhffs_dns_rr DROP COLUMN data; 
+ALTER TABLE vhffs_dns_rr RENAME COLUMN data_new TO data;
+ALTER TABLE vhffs_dns_rr ALTER COLUMN data SET NOT NULL;
+COMMIT;
+
+
+-- 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);
+
+ALTER TABLE vhffs_object_tag ADD CONSTRAINT fk_vhffs_object_tag_vhffs_users FOREIGN KEY ( updater_id ) REFERENCES vhffs_users( uid ) ON DELETE SET NULL;
+
+-- and not necessary but useful
+CREATE INDEX idx_vhffs_tag_category_visibility ON vhffs_tag_category(visibility);
+
+
+-- delete duplicate values in vhffs_ml_subscribers (inserted members where not lowered)
+DELETE FROM vhffs_ml_subscribers WHERE EXISTS ( SELECT 'x' FROM vhffs_ml_subscribers m WHERE LOWER(m.member) = LOWER(vhffs_ml_subscribers.member) AND m.ml_id = vhffs_ml_subscribers.ml_id AND m.sub_id < vhffs_ml_subscribers.sub_id );
+UPDATE vhffs_ml_subscribers set member=LOWER(member);
+
+-- Server encoding is always used to avoid compatibility issues
+ALTER TABLE vhffs_pgsql DROP COLUMN dbencoding;

Copied: trunk/vhffs-compat/from-4.2-to-4.3.sql (from rev 1489, trunk/vhffs-compat/4.2.sql)
===================================================================
--- trunk/vhffs-compat/from-4.2-to-4.3.sql	                        (rev 0)
+++ trunk/vhffs-compat/from-4.2-to-4.3.sql	2009-07-31 15:22:33 UTC (rev 1490)
@@ -0,0 +1,7 @@
+-- update_id field is cleared when the user is unsubscribing
+ALTER TABLE vhffs_object_tag ALTER COLUMN updater_id DROP NOT NULL;
+
+-- add the user who modified the object in the object's history
+ALTER TABLE vhffs_history ADD COLUMN source_uid INTEGER DEFAULT NULL;
+ALTER TABLE vhffs_history ADD CONSTRAINT fk_vhffs_history_vhffs_users FOREIGN KEY (source_uid) REFERENCES vhffs_users(uid);
+