# HG changeset patch
# User Andre Heinecke <aheinecke@intevation.de>
# Date 1360928305 -3600
# Node ID 929ec3ed7dc25c9b9030b179f1ed9bcc615d8702
# Parent  ab5d8f73a982222e90239157ff0009eece6b43ac
SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind

diff -r ab5d8f73a982 -r 929ec3ed7dc2 flys-backend/doc/schema/postgresql-drop-spatial.sql
--- a/flys-backend/doc/schema/postgresql-drop-spatial.sql	Fri Feb 15 12:10:44 2013 +0100
+++ b/flys-backend/doc/schema/postgresql-drop-spatial.sql	Fri Feb 15 12:38:25 2013 +0100
@@ -27,9 +27,6 @@
 DROP TABLE catchment;
 DROP SEQUENCE CATCHMENT_ID_SEQ;
 
---DROP TABLE hws;
---DROP SEQUENCE HWS_ID_SEQ;
-
 DROP TABLE hws_points;
 DROP SEQUENCE HWS_POINTS_ID_SEQ;
 
@@ -50,5 +47,8 @@
 
 DROP TABLE fed_states;
 DROP TABLE hws_kinds;
+DROP TABLE sobek_kinds;
+DROP TABLE sectie_kinds;
+DROP TABLE boundary_kinds;
 
 COMMIT;
diff -r ab5d8f73a982 -r 929ec3ed7dc2 flys-backend/doc/schema/postgresql-spatial.sql
--- a/flys-backend/doc/schema/postgresql-spatial.sql	Fri Feb 15 12:10:44 2013 +0100
+++ b/flys-backend/doc/schema/postgresql-spatial.sql	Fri Feb 15 12:38:25 2013 +0100
@@ -242,14 +242,40 @@
 ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text);
 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ');
 
+CREATE TABLE sectie_kinds (
+    id int PRIMARY KEY NOT NULL,
+    name VARCHAR(64) NOT NULL
+);
+INSERT INTO sectie_kinds (id, name) VALUES (0, 'Unbekannt');
+INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch');
+INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank');
+INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich');
+
+CREATE TABLE sobek_kinds (
+    id int PRIMARY KEY NOT NULL,
+    name VARCHAR(64) NOT NULL
+);
+INSERT INTO sobek_kinds (id, name) VALUES (0, 'Unbekannt');
+INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend');
+INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd');
+
+CREATE TABLE boundary_kinds (
+    id int PRIMARY KEY NOT NULL,
+    name VARCHAR(64) NOT NULL
+);
+INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt');
+INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG');
+INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land');
+INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige');
+
 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
 CREATE TABLE hydr_boundaries (
     id         int PRIMARY KEY NOT NULL,
     river_id   int REFERENCES rivers(id) ON DELETE CASCADE,
     name       VARCHAR(255),
-    kind       int, -- 1 BfG / 2 Land / 3 Sonstige
-    sectie     int, -- 1 stromführend / 2 stromspeichernd
-    sobek      int, -- 1 Flussschlauch / 2 Uferbank / 3 Überflutungsbereich
+    kind       int REFERENCES boundary_kinds(id),
+    sectie     int REFERENCES sectie_kinds(id),
+    sobek      int REFERENCES sobek_kinds(id),
     path       VARCHAR(256)
 );
 SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'LINESTRING',3);
@@ -261,9 +287,9 @@
     id         int PRIMARY KEY NOT NULL,
     river_id   int REFERENCES rivers(id) ON DELETE CASCADE,
     name       VARCHAR(255),
-    kind       int, -- 1 BfG / 2 Land / 3 Sonstige
-    sectie     int, -- 1 Flussschlauch / 2 Uferbank / 3 Überflutungsbereich
-    sobek      int, -- 1 stromführend / 2 stromspeichernd
+    kind       int REFERENCES boundary_kinds(id),
+    sectie     int REFERENCES sectie_kinds(id),
+    sobek      int REFERENCES sobek_kinds(id),
     path       VARCHAR(256)
 );
 SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'POLYGON',3);