# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1310722583 0
# Node ID c12b5fbd33e8fe0a2c1c873c3a195d4784ba6b2b
# Parent  2b57edd947359a8306befff865dde7d8b66d3b71
schema: Added structures for HYKs "Hydraulische Kenngroessen"

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

diff -r 2b57edd94735 -r c12b5fbd33e8 flys-backend/ChangeLog
--- a/flys-backend/ChangeLog	Wed Jul 13 12:38:11 2011 +0000
+++ b/flys-backend/ChangeLog	Fri Jul 15 09:36:23 2011 +0000
@@ -1,3 +1,57 @@
+2011-07-15	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
+
+	* doc/schema/postgresql.sql: Added structures for HYKs "Hydraulische Kenngroessen"
+	  To update existing databases:
+
+	  BEGIN;
+	    CREATE SEQUENCE HYKS_ID_SEQ;
+	    
+	    CREATE TABLE hyks (
+	        id          int PRIMARY KEY NOT NULL,
+	        river_id    int             NOT NULL,
+	        description VARCHAR(256)    NOT NULL
+	    );
+	    
+	    CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
+	    
+	    CREATE TABLE hyk_entries (
+	        id     int PRIMARY KEY NOT NULL,
+	        hyk_id int             NOT NULL REFERENCES hyks(id),
+	        km     NUMERIC         NOT NULL,
+	        UNIQUE (hyk_id, km)
+	    );
+	    
+	    CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
+	    
+	    CREATE TABLE hyk_formations (
+	        id            int PRIMARY KEY NOT NULL,
+	        formation_num int             NOT NULL DEFAULT 0,
+	        hyk_entry_id  int             NOT NULL REFERENCES hyk_entries(id),
+	        top           NUMERIC         NOT NULL,
+	        bottom        NUMERIC         NOT NULL,
+	        UNIQUE (hyk_entry_id, formation_num)
+	    );
+	    
+	    CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;
+	    
+	    CREATE TABLE hyk_flow_zone_types (
+	        id          int PRIMARY KEY NOT NULL,
+	        name        VARCHAR(50)     NOT NULL UNIQUE,
+	        description VARCHAR(256)
+	    );
+	    
+	    CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ;
+	    
+	    CREATE TABLE hyk_flow_zones (
+	        id           int PRIMARY KEY NOT NULL,
+	        formation_id int             NOT NULL REFERENCES hyk_formations(id),
+	        type_id      int             NOT NULL REFERENCES hyk_flow_zone_types(id),
+	        a            NUMERIC         NOT NULL,
+	        b            NUMERIC         NOT NULL,
+	        CHECK (a <= b)
+	    );
+	  COMMIT;
+
 2011-07-13	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
 
 	* pom.xml: Bumped Hibernate up to 3.6.5.
diff -r 2b57edd94735 -r c12b5fbd33e8 flys-backend/doc/schema/postgresql.sql
--- a/flys-backend/doc/schema/postgresql.sql	Wed Jul 13 12:38:11 2011 +0000
+++ b/flys-backend/doc/schema/postgresql.sql	Fri Jul 15 09:36:23 2011 +0000
@@ -291,4 +291,53 @@
     UNIQUE (cross_section_line_id, col_pos)
 );
 
+-- Hydraulische Kenngroessen
+
+CREATE SEQUENCE HYKS_ID_SEQ;
+
+CREATE TABLE hyks (
+    id          int PRIMARY KEY NOT NULL,
+    river_id    int             NOT NULL,
+    description VARCHAR(256)    NOT NULL
+);
+
+CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
+
+CREATE TABLE hyk_entries (
+    id     int PRIMARY KEY NOT NULL,
+    hyk_id int             NOT NULL REFERENCES hyks(id),
+    km     NUMERIC         NOT NULL,
+    UNIQUE (hyk_id, km)
+);
+
+CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
+
+CREATE TABLE hyk_formations (
+    id            int PRIMARY KEY NOT NULL,
+    formation_num int             NOT NULL DEFAULT 0,
+    hyk_entry_id  int             NOT NULL REFERENCES hyk_entries(id),
+    top           NUMERIC         NOT NULL,
+    bottom        NUMERIC         NOT NULL,
+    UNIQUE (hyk_entry_id, formation_num)
+);
+
+CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;
+
+CREATE TABLE hyk_flow_zone_types (
+    id          int PRIMARY KEY NOT NULL,
+    name        VARCHAR(50)     NOT NULL UNIQUE,
+    description VARCHAR(256)
+);
+
+CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ;
+
+CREATE TABLE hyk_flow_zones (
+    id           int PRIMARY KEY NOT NULL,
+    formation_id int             NOT NULL REFERENCES hyk_formations(id),
+    type_id      int             NOT NULL REFERENCES hyk_flow_zone_types(id),
+    a            NUMERIC         NOT NULL,
+    b            NUMERIC         NOT NULL,
+    CHECK (a <= b)
+);
+
 COMMIT;