Mercurial > dive4elements > river
changeset 8017:bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Tue, 08 Jul 2014 19:44:54 +0200 |
parents | b0d4b1a3c345 |
children | ad614eff7a9b 139b057a39dd |
files | backend/doc/schema/oracle-minfo.sql backend/doc/schema/postgresql-minfo.sql |
diffstat | 2 files changed, 72 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- a/backend/doc/schema/oracle-minfo.sql Tue Jul 08 18:37:27 2014 +0200 +++ b/backend/doc/schema/oracle-minfo.sql Tue Jul 08 19:44:54 2014 +0200 @@ -308,6 +308,42 @@ ); +CREATE SEQUENCE SEDIMENT_LOAD_ID_SEQ; + +CREATE TABLE sediment_loads ( + id int NOT NULL, + grain_fraction_id int NOT NULL, + time_interval_id int NOT NULL, + sq_time_interval_id int, + description VARCHAR(256), + kind int, + PRIMARY KEY (id), + CONSTRAINT fk_sl_kind_id FOREIGN KEY (kind) + REFERENCES sediment_yield_kinds(id), + CONSTRAINT fk_sl_grain_fraction_id FOREIGN KEY (grain_fraction_id) + REFERENCES grain_fraction(id), + CONSTRAINT fk_sl_time_interval_id FOREIGN KEY (time_interval_id) + REFERENCES time_intervals(id), + CONSTRAINT fk_sl_sq_time_interval_id FOREIGN KEY (sq_time_interval_id) + REFERENCES time_intervals(id) +); + + +CREATE SEQUENCE SEDIMENT_LOAD_VALUES_ID_SEQ; + +CREATE TABLE sediment_load_values ( + id int NOT NULL, + sediment_load_id int NOT NULL, + measurement_station_id int NOT NULL, + value DOUBLE PRECISION NOT NULL, + PRIMARY KEY (id), + CONSTRAINT fk_slv_sediment_load_id FOREIGN KEY (sediment_load_id) + REFERENCES sediment_loads(id) ON DELETE CASCADE, + CONSTRAINT fk_slv_m_station_id FOREIGN KEY (measurement_station_id) + REFERENCES measurement_station(id) ON DELETE CASCADE +); + + CREATE SEQUENCE SQ_RELATION_ID_SEQ; CREATE TABLE sq_relation (
--- a/backend/doc/schema/postgresql-minfo.sql Tue Jul 08 18:37:27 2014 +0200 +++ b/backend/doc/schema/postgresql-minfo.sql Tue Jul 08 19:44:54 2014 +0200 @@ -309,6 +309,42 @@ ); +CREATE SEQUENCE SEDIMENT_LOAD_ID_SEQ; + +CREATE TABLE sediment_loads ( + id int NOT NULL, + grain_fraction_id int NOT NULL, + time_interval_id int NOT NULL, + sq_time_interval_id int, + description VARCHAR(256), + kind int, + PRIMARY KEY (id), + CONSTRAINT fk_sl_kind_id FOREIGN KEY (kind) + REFERENCES sediment_yield_kinds(id), + CONSTRAINT fk_sl_grain_fraction_id FOREIGN KEY (grain_fraction_id) + REFERENCES grain_fraction(id), + CONSTRAINT fk_sl_time_interval_id FOREIGN KEY (time_interval_id) + REFERENCES time_intervals(id), + CONSTRAINT fk_sl_sq_time_interval_id FOREIGN KEY (sq_time_interval_id) + REFERENCES time_intervals(id) +); + + +CREATE SEQUENCE SEDIMENT_LOAD_VALUES_ID_SEQ; + +CREATE TABLE sediment_load_values ( + id int NOT NULL, + sediment_load_id int NOT NULL, + measurement_station_id int NOT NULL, + value DOUBLE PRECISION NOT NULL, + PRIMARY KEY (id), + CONSTRAINT fk_slv_sediment_load_id FOREIGN KEY (sediment_load_id) + REFERENCES sediment_loads(id) ON DELETE CASCADE, + CONSTRAINT fk_slv_m_station_id FOREIGN KEY (measurement_station_id) + REFERENCES measurement_station(id) ON DELETE CASCADE +); + + CREATE SEQUENCE SQ_RELATION_ID_SEQ; CREATE TABLE sq_relation (