# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1317805438 0
# Node ID 775e316f255b96d032b4e98a5cc5bca4f86c18ef
# Parent  8477965674cee1266232a81cc126ecaaeaf7c0fa
units table: Added update statements for existing databases

flys-backend/trunk@2890 c6561f87-3c4e-4783-a992-168aeb5c3f6f

diff -r 8477965674ce -r 775e316f255b flys-backend/ChangeLog
--- 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