# HG changeset patch # User Ingo Weinzierl # Date 1334136190 0 # Node ID a55b7b35cc3a3441aed38f68e8b4a4586dedeab1 # Parent 68d1661a57cfe5506e38d5c3d5538354faaed5f0 First things for MINFO specific database schema. flys-backend/trunk@4210 c6561f87-3c4e-4783-a992-168aeb5c3f6f diff -r 68d1661a57cf -r a55b7b35cc3a flys-backend/ChangeLog --- 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 + + * 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 * src/main/java/de/intevation/flys/importer/Config.java: Added a config diff -r 68d1661a57cf -r a55b7b35cc3a flys-backend/doc/schema/oracle-drop-minfo.sql --- /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; + diff -r 68d1661a57cf -r a55b7b35cc3a flys-backend/doc/schema/oracle-minfo.sql --- /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) +); +