Mercurial > dive4elements > river

changeset 2823:13b3fcaa1b34

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add MINFO schema for PostgreSQL flys-backend/trunk@4240 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Fri, 13 Apr 2012 16:15:23 +0000
parents 212c33c9a5e2
children 85b2b5e7377f
files flys-backend/ChangeLog flys-backend/doc/schema/oracle-minfo.sql flys-backend/doc/schema/postgresql-minfo.sql
diffstat 3 files changed, 179 insertions(+), 1 deletions(-) [+]
line wrap: on
line diff
--- a/flys-backend/ChangeLog	Fri Apr 13 13:27:09 2012 +0000
+++ b/flys-backend/ChangeLog	Fri Apr 13 16:15:23 2012 +0000
@@ -1,3 +1,10 @@
+2012-02-14	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
+
+	* doc/schema/oracle-minfo.sql: Fixed column unit_id in table depths
+	  to match type of column id in table units.
+
+	* doc/schema/postgresql-minfo.sql: oracle-minfo.sql for a better DBMS.
+
 2012-04-13  Ingo Weinzierl <ingo@intevation.de>
 
 	* src/main/java/de/intevation/flys/model/MorphologicalWidth.java: Added
--- a/flys-backend/doc/schema/oracle-minfo.sql	Fri Apr 13 13:27:09 2012 +0000
+++ b/flys-backend/doc/schema/oracle-minfo.sql	Fri Apr 13 16:15:23 2012 +0000
@@ -110,7 +110,7 @@
     id      NUMBER(38,0) NOT NULL,
     lower   NUMBER(38,2) NOT NULL,
     upper   NUMBER(38,2) NOT NULL,
-    unit_id NUMBER(38,2) NOT NULL,
+    unit_id NUMBER(38,0) NOT NULL,
     PRIMARY KEY(id),
     CONSTRAINT fk_depths_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
 );
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/flys-backend/doc/schema/postgresql-minfo.sql	Fri Apr 13 16:15:23 2012 +0000
@@ -0,0 +1,171 @@
+BEGIN;
+
+CREATE SEQUENCE LOCATION_SYSTEM_SEQ;
+
+CREATE TABLE location_system (
+    id          int NOT NULL,
+    name        VARCHAR(32)  NOT NULL,
+    description VARCHAR(255),
+    PRIMARY KEY(id)
+);
+
+
+CREATE SEQUENCE ELEVATION_MODEL_SEQ;
+
+CREATE TABLE elevation_model (
+    id          int NOT NULL,
+    name        VARCHAR(32)  NOT NULL,
+    unit_id     int NOT NULL,
+    PRIMARY KEY(id),
+    CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id)
+);
+
+CREATE SEQUENCE BED_HEIGHT_TYPE_SEQ;
+
+CREATE TABLE bed_height_type (
+    id          int NOT NULL,
+    name        VARCHAR(16)  NOT NULL,
+    description VARCHAR(255),
+    PRIMARY KEY(id)
+);
+
+
+
+CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;
+
+CREATE TABLE bed_height_single (
+    id                      int NOT NULL,
+    river_id                int NOT NULL,
+    year                    int NOT NULL,
+    sounding_width          int NOT NULL,
+    type_id                 int NOT NULL,
+    location_system_id      int NOT NULL,
+    cur_elevation_model_id  int NOT NULL,
+    old_elevation_model_id  int,
+    range_id                int NOT NULL,
+    evaluation_by           VARCHAR(255),
+    description             VARCHAR(255),
+    PRIMARY KEY(id),
+    CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
+    CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
+    CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id),
+    CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
+    CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
+    CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id)
+);
+
+
+CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ;
+
+CREATE TABLE bed_height_epoch (
+    id                      int NOT NULL,
+    river_id                int NOT NULL,
+    time_interval_id        int NOT NULL,
+    -- sounding_with           int NOT NULL,
+    -- type_id                 int NOT NULL,
+    cur_elevation_model_id  int NOT NULL,
+    old_elevation_model_id  int,
+    range_id                int NOT NULL,
+    evaluation_by           VARCHAR(255),
+    description             VARCHAR(255),
+    PRIMARY KEY(id),
+    CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id),
+    CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
+    CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
+    CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id)
+);
+
+
+CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ;
+
+CREATE TABLE bed_height_single_values (
+    id                      int NOT NULL,
+    bed_height_single_id    int NOT NULL,
+    station                 NUMERIC NOT NULL,
+    height                  NUMERIC,
+    uncertainty             NUMERIC,
+    data_gap                NUMERIC NOT NULL,
+    sounding_width          NUMERIC NOT NULL,
+    width                   NUMERIC NOT NULL,
+    PRIMARY KEY(id),
+    CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id)
+);
+
+
+CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ;
+
+CREATE TABLE bed_height_epoch_values (
+    id                      int NOT NULL,
+    bed_height_epoch_id     int NOT NULL,
+    station                 NUMERIC NOT NULL,
+    height                  NUMERIC,
+    PRIMARY KEY(id),
+    CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id)
+);
+
+
+CREATE SEQUENCE DEPTHS_ID_SEQ;
+
+CREATE TABLE depths (
+    id      int NOT NULL,
+    lower   NUMERIC NOT NULL,
+    upper   NUMERIC NOT NULL,
+    unit_id int NOT NULL,
+    PRIMARY KEY(id),
+    CONSTRAINT fk_depths_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
+);
+
+
+CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ;
+
+CREATE TABLE sediment_density (
+    id          int NOT NULL,
+    river_id    int NOT NULL,
+    depth_id    int NOT NULL,
+    unit_id     int NOT NULL,
+    description VARCHAR(256),
+    PRIMARY KEY(id),
+    CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
+    CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id),
+    CONSTRAINT fk_sd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
+);
+
+
+CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ;
+
+CREATE TABLE sediment_density_values (
+    id                  int NOT NULL,
+    sediment_density_id int NOT NULL,
+    station             NUMERIC NOT NULL,
+    density             NUMERIC NOT NULL,
+    description         VARCHAR(256),
+    PRIMARY KEY(id),
+    CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id)
+);
+
+
+CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ;
+
+CREATE TABLE morphologic_width (
+    id          int NOT NULL,
+    river_id    int NOT NULL,
+    unit_id     int NOT NULL,
+    PRIMARY KEY(id),
+    CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id),
+    CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id)
+);
+
+
+CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ;
+
+CREATE TABLE morphologic_width_values (
+    id                      int NOT NULL,
+    morphologic_width_id    int NOT NULL,
+    station                 NUMERIC NOT NULL,
+    width                   NUMERIC NOT NULL,
+    description             VARCHAR(256),
+    PRIMARY KEY(id),
+    CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id)
+);
+
+COMMIT;
http://dive4elements.wald.intevation.org