changeset 2349:775e316f255b

units table: Added update statements for existing databases flys-backend/trunk@2890 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 05 Oct 2011 09:03:58 +0000
parents 8477965674ce
children 3eb520b7a70e
files flys-backend/ChangeLog
diffstat 1 files changed, 20 insertions(+), 0 deletions(-) [+]
line wrap: on
line diff
--- a/flys-backend/ChangeLog	Wed Oct 05 08:33:25 2011 +0000
+++ b/flys-backend/ChangeLog	Wed Oct 05 09:03:58 2011 +0000
@@ -1,3 +1,23 @@
+2011-10-05	Sascha L. Teichmann	<sascha.teichmann@intevation.de>:
+
+	* ChangeLog: Added database update statements.
+
+	To update existing databases:
+
+	    BEGIN;
+	      CREATE SEQUENCE UNITS_ID_SEQ;
+	      CREATE TABLE units (
+	        id   int PRIMARY KEY NOT NULL,
+	        name VARCHAR(32)     NOT NULL UNIQUE
+	      );
+	    ALTER TABLE rivers ADD COLUMN wst_unit_id int REFERENCES units(id);
+	    INSERT INTO units (id, name) VALUES (nextval('UNITS_ID_SEQ'), 'NN + m');
+	    INSERT INTO units (id, name) VALUES (nextval('UNITS_ID_SEQ'), 'NHN + m');
+	    UPDATE rivers SET wst_unit_id = (SELECT id FROM units WHERE name = 'NHN + m') WHERE name = 'Elbe';
+	    UPDATE rivers SET wst_unit_id = (SELECT id FROM units WHERE name = 'NN + m') WHERE name <> 'Elbe';
+	    ALTER TABLE rivers ALTER COLUMN wst_unit_id SET NOT NULL;
+	    COMMIT;
+
 2011-10-05  Ingo Weinzierl <ingo@intevation.de>
 
 	* contrib/dump-schema.sh: New. A small shell script that dumps the

http://dive4elements.wald.intevation.org