Mercurial > dive4elements > river
changeset 2834:daba3f895eb3
Improved the db schema to store sediment yield values.
flys-backend/trunk@4266 c6561f87-3c4e-4783-a992-168aeb5c3f6f
| author | Ingo Weinzierl <ingo.weinzierl@intevation.de> |
|---|---|
| date | Wed, 18 Apr 2012 12:12:22 +0000 |
| parents | 5b54a648f702 |
| children | 8649e4ed01ec |
| files | flys-backend/ChangeLog flys-backend/doc/schema/oracle-drop-minfo.sql flys-backend/doc/schema/oracle-minfo.sql |
| diffstat | 3 files changed, 56 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- a/flys-backend/ChangeLog Wed Apr 18 08:54:55 2012 +0000 +++ b/flys-backend/ChangeLog Wed Apr 18 12:12:22 2012 +0000 @@ -1,3 +1,9 @@ +2012-04-18 Ingo Weinzierl <ingo@intevation.de> + + * doc/schema/oracle-minfo.sql, + doc/schema/oracle-drop-minfo.sql: Added relations for storing sediment + yield values. + 2012-04-18 Ingo Weinzierl <ingo@intevation.de> * doc/schema/oracle-minfo.sql,
--- a/flys-backend/doc/schema/oracle-drop-minfo.sql Wed Apr 18 08:54:55 2012 +0000 +++ b/flys-backend/doc/schema/oracle-drop-minfo.sql Wed Apr 18 12:12:22 2012 +0000 @@ -25,6 +25,11 @@ ALTER TABLE discharge_zone DROP CONSTRAINT fk_dz_river_id; ALTER TABLE flow_velocity_measurements DROP CONSTRAINT fk_fvm_rivers_id; ALTER TABLE flow_velocity_measure_values DROP CONSTRAINT fk_fvmv_measurements_id; +ALTER TABLE grain_fraction DROP CONSTRAINT fk_gf_unit_id; +ALTER TABLE sediment_yield DROP CONSTRAINT fk_sy_grain_fraction_id; +ALTER TABLE sediment_yield DROP CONSTRAINT fk_sy_unit_id; +ALTER TABLE sediment_yield DROP CONSTRAINT fk_sy_time_interval_id; +ALTER TABLE sediment_yield_values DROP CONSTRAINT fk_syv_sediment_yield_id; DROP TABLE bed_height_type; DROP TABLE location_system; @@ -43,6 +48,9 @@ DROP TABLE flow_velocity_model_values; DROP TABLE flow_velocity_measurements; DROP TABLE flow_velocity_measure_values; +DROP TABLE grain_fraction; +DROP TABLE sediment_yield; +DROP TABLE sediment_yield_values; DROP SEQUENCE BED_HEIGHT_TYPE_SEQ; DROP SEQUENCE LOCATION_SYSTEM_SEQ; @@ -61,4 +69,7 @@ DROP SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ; DROP SEQUENCE FV_MEASURE_ID_SEQ; DROP SEQUENCE FV_MEASURE_VALUES_ID_SEQ; +DROP SEQUENCE GRAIN_FRACTION_ID_SEQ; +DROP SEQUENCE SEDIMENT_YIELD_ID_SEQ; +DROP SEQUENCE SEDIMENT_YIELD_VALUES_ID_SEQ;
--- a/flys-backend/doc/schema/oracle-minfo.sql Wed Apr 18 08:54:55 2012 +0000 +++ b/flys-backend/doc/schema/oracle-minfo.sql Wed Apr 18 12:12:22 2012 +0000 @@ -236,3 +236,42 @@ PRIMARY KEY (id), CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) ); + + +CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ; + +CREATE TABLE grain_fraction ( + id NUMBER(38,0) NOT NULL, + name VARCHAR(64) NOT NULL, + lower NUMBER(38,3), + upper NUMBER(38,3), + unit_id NUMBER (38,0), + PRIMARY KEY (id), + CONSTRAINT fk_gf_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) +); + + +CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ; + +CREATE TABLE sediment_yield ( + id NUMBER(38,0) NOT NULL, + grain_fraction_id NUMBER(38,0), + unit_id NUMBER(38,0) NOT NULL, + time_interval_id NUMBER(38,0) NOT NULL, + PRIMARY KEY (id), + CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id), + CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id), + CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) +); + + +CREATE SEQUENCE SEDIMENT_YIELD_VALUES_ID_SEQ; + +CREATE TABLE sediment_yield_values ( + id NUMBER(38,0) NOT NULL, + sediment_yield_id NUMBER(38,0) NOT NULL, + station NUMBER(38,3) NOT NULL, + value NUMBER(38,3) NOT NULL, + PRIMARY KEY (id), + CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id) +);