# HG changeset patch # User Sascha L. Teichmann # Date 1334333723 0 # Node ID 13b3fcaa1b3448f094b6d4a7389da38de574bd7a # Parent 212c33c9a5e293f19224395dc191491a7f9917ca Add MINFO schema for PostgreSQL flys-backend/trunk@4240 c6561f87-3c4e-4783-a992-168aeb5c3f6f diff -r 212c33c9a5e2 -r 13b3fcaa1b34 flys-backend/ChangeLog --- 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 + + * 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 * src/main/java/de/intevation/flys/model/MorphologicalWidth.java: Added diff -r 212c33c9a5e2 -r 13b3fcaa1b34 flys-backend/doc/schema/oracle-minfo.sql --- 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) ); diff -r 212c33c9a5e2 -r 13b3fcaa1b34 flys-backend/doc/schema/postgresql-minfo.sql --- /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;