changeset 2805:a55b7b35cc3a

First things for MINFO specific database schema. flys-backend/trunk@4210 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Wed, 11 Apr 2012 09:23:10 +0000
parents 68d1661a57cf
children 33f40b23edd8
files flys-backend/ChangeLog flys-backend/doc/schema/oracle-drop-minfo.sql flys-backend/doc/schema/oracle-minfo.sql
diffstat 3 files changed, 135 insertions(+), 0 deletions(-) [+]
line wrap: on
line diff
--- a/flys-backend/ChangeLog	Thu Mar 29 07:24:15 2012 +0000
+++ b/flys-backend/ChangeLog	Wed Apr 11 09:23:10 2012 +0000
@@ -1,3 +1,9 @@
+2012-04-11  Ingo Weinzierl <ingo@intevation.de>
+
+	* doc/schema/oracle-minfo.sql,
+	  doc/schema/oracle-drop-minfo.sql: MINFO specific DB schema and sql statements
+	  to drop MINFO specific stuff.
+
 2012-03-29  Ingo Weinzierl <ingo@intevation.de>
 
 	* src/main/java/de/intevation/flys/importer/Config.java: Added a config
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/flys-backend/doc/schema/oracle-drop-minfo.sql	Wed Apr 11 09:23:10 2012 +0000
@@ -0,0 +1,30 @@
+SET AUTOCOMMIT ON;
+
+ALTER TABLE elevation_model DROP CONSTRAINT fk_unit;
+ALTER TABLE bed_height_single DROP CONSTRAINT fk_type;
+ALTER TABLE bed_height_single DROP CONSTRAINT fk_cur_elevation_model;
+ALTER TABLE bed_height_single DROP CONSTRAINT fk_old_elevation_model;
+ALTER TABLE bed_height_single DROP CONSTRAINT fk_range;
+ALTER TABLE bed_height_single_values DROP CONSTRAINT fk_bed_single_values_parent;
+ALTER TABLE bed_height_epoch_values DROP CONSTRAINT fk_bed_epoch_values_parent;
+ALTER TABLE bed_height_epoch DROP CONSTRAINT fk_type;
+ALTER TABLE bed_height_epoch DROP CONSTRAINT fk_cur_elevation_model;
+ALTER TABLE bed_height_epoch DROP CONSTRAINT fk_old_elevation_model;
+ALTER TABLE bed_height_epoch DROP CONSTRAINT fk_range;
+
+DROP TABLE bed_height_type;
+DROP TABLE location_system;
+DROP TABLE elevation_model;
+DROP TABLE bed_height_single;
+DROP TABLE bed_height_single_values;
+DROP TABLE bed_height_epoch_values;
+DROP TABLE bed_height_epoch;
+
+DROP SEQUENCE BED_HEIGHT_TYPE_SEQ;
+DROP SEQUENCE LOCATION_SYSTEM_SEQ;
+DROP SEQUENCE ELEVATION_MODEL_SEQ;
+DROP SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;
+DROP SEQUENCE BED_SINGLE_VALUES_ID_SEQ;
+DROP SEQUENCE BED_EPOCH_VALUES_ID_SEQ;
+DROP SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ;
+
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/flys-backend/doc/schema/oracle-minfo.sql	Wed Apr 11 09:23:10 2012 +0000
@@ -0,0 +1,99 @@
+SET AUTOCOMMIT ON;
+
+CREATE SEQUENCE LOCATION_SYSTEM_SEQ;
+
+CREATE TABLE location_system (
+    id          NUMBER(38,0) NOT NULL,
+    name        VARCHAR(32)  NOT NULL,
+    description VARCHAR(255),
+    PRIMARY KEY(id)
+);
+
+
+CREATE SEQUENCE ELEVATION_MODEL_SEQ;
+
+CREATE TABLE elevation_model (
+    id          NUMBER(38,0) NOT NULL,
+    name        VARCHAR(32)  NOT NULL,
+    unit_id     NUMBER(38,0) 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          NUMBER(38,0) 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                      NUMBER(38,0) NOT NULL,
+    year                    NUMBER(38,0) NOT NULL,
+    sounding_with           NUMBER(38,0) NOT NULL,
+    type_id                 NUMBER(38,0) NOT NULL,
+    cur_elevation_model_id  NUMBER(38,0) NOT NULL,
+    old_elevation_model_id  NUMBER(38,0),
+    range_id                NUMBER(38,0) NOT NULL,
+    evaluation_by           VARCHAR(255),
+    description             VARCHAR(255),
+    PRIMARY KEY(id),
+    CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(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                      NUMBER(38,0) NOT NULL,
+    time_interval_id        NUMBER(38,0) NOT NULL,
+    -- sounding_with           NUMBER(38,0) NOT NULL,
+    -- type_id                 NUMBER(38,0) NOT NULL,
+    cur_elevation_model_id  NUMBER(38,0) NOT NULL,
+    old_elevation_model_id  NUMBER(38,0),
+    range_id                NUMBER(38,0) 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                      NUMBER(38,0) NOT NULL,
+    bed_height_single_id    NUMBER(38,0) NOT NULL,
+    station                 NUMBER(38,2) NOT NULL,
+    height                  NUMBER(38,2),
+    uncertainty             NUMBER(38,2),
+    data_gap                NUMBER(38,2) NOT NULL,
+    width                   NUMBER(38,2) 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                      NUMBER(38,0) NOT NULL,
+    bed_height_epoch_id     NUMBER(38,0) NOT NULL,
+    station                 NUMBER(38,2) NOT NULL,
+    height                  NUMBER(38,2),
+    PRIMARY KEY(id),
+    CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id)
+);
+

http://dive4elements.wald.intevation.org