# HG changeset patch # User mschaefer # Date 1524029668 -7200 # Node ID ce99d3db94773be0df11b36765cfde144951cb36 # Parent fe561931026c685e1f021ca7e1a13360dca36101 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script diff -r fe561931026c -r ce99d3db9477 backend/doc/schema/oracle-sinfo-uinfo.sql --- a/backend/doc/schema/oracle-sinfo-uinfo.sql Tue Apr 17 13:20:53 2018 +0200 +++ b/backend/doc/schema/oracle-sinfo-uinfo.sql Wed Apr 18 07:34:28 2018 +0200 @@ -1,6 +1,32 @@ -BEGIN; +SET AUTOCOMMIT ON; +--Add new column wsts.sinfo_selection + +ALTER TABLE wsts ADD COLUMN sinfo_selection CHAR(1) CHECK((sinfo_selection IS NULL) OR (sinfo_selection IN ('W','Q'))); +COMMENT ON COLUMN wsts.sinfo_selection IS 'Whether and how the WST series is selected within S-INFO calculation types ("W"=without-Q-group, "Q"=with-Q-group, NULL=not-selectable)' ; + + +--Add new columns bed_height.sounding_width_info and bed_height.comment + +ALTER TABLE bed_height ADD COLUMN sounding_width_info VARCHAR2(256); +COMMENT ON COLUMN bed_height.sounding_width_info IS 'File header line info "ausgewertete Peilbreite"' ; + +ALTER TABLE bed_height ADD COLUMN "comment" VARCHAR2(256); +COMMENT ON COLUMN bed_height."comment" IS 'File header line info "weitere Bemerkungen"' ; + + +--Add new columns bed_height_values.min_height (Talweg) and bed_height_values.max_height (Kammlage) (same type as existing height column) + +ALTER TABLE bed_height_values ADD COLUMN min_height DOUBLE PRECISION; +COMMENT ON COLUMN bed_height_values.min_height IS 'Minimum bed height (Talweg) in m' ; + +ALTER TABLE bed_height_values ADD COLUMN max_height DOUBLE PRECISION; +COMMENT ON COLUMN bed_height_values.max_height IS 'Maximum bed height (Kammlage) in m' ; + + +--Bed mobility + CREATE TABLE bed_mobility ( id NUMBER(9,0) PRIMARY KEY, river_id NUMBER(38,0) NOT NULL CONSTRAINT cBedMobilityRivers REFERENCES rivers(id) ON DELETE CASCADE, @@ -29,6 +55,8 @@ CREATE SEQUENCE BED_MOBILITY_VALUES_ID_SEQ ; +--Infrastructure + CREATE TABLE infrastructure ( id NUMBER(9,0) PRIMARY KEY, river_id NUMBER(38,0) NOT NULL CONSTRAINT cInfrastructureRivers REFERENCES rivers(id) ON DELETE CASCADE, @@ -65,6 +93,8 @@ CREATE SEQUENCE INFRASTRUCTURE_VALUES_ID_SEQ ; +--Channel + CREATE TABLE channel ( id NUMBER(9,0) PRIMARY KEY, river_id NUMBER(38,0) NOT NULL CONSTRAINT cChannelRivers REFERENCES rivers(id) ON DELETE CASCADE, @@ -100,6 +130,8 @@ CREATE SEQUENCE CHANNEL_VALUES_ID_SEQ ; +--Collision + CREATE TABLE collision_type ( id NUMBER(9,0) PRIMARY KEY, name VARCHAR2(64) NOT NULL UNIQUE @@ -145,10 +177,12 @@ CREATE SEQUENCE COLLISION_VALUES_ID_SEQ ; +--Tkh + CREATE TABLE tkh ( id NUMBER(9,0) PRIMARY KEY, river_id NUMBER(38,0) NOT NULL CONSTRAINT cTkhRivers REFERENCES rivers(id) ON DELETE CASCADE, - year NUMBER(4,0) NOT NULL, + day DATE, kmrange_info VARCHAR2(32), filename VARCHAR2(256) NOT NULL, sounding_info VARCHAR2(64), @@ -156,7 +190,7 @@ "comment" VARCHAR2(256) ); COMMENT ON TABLE tkh IS 'Longitudinal section of computed Transportkörperhöhe of a river' ; -COMMENT ON COLUMN tkh.year IS 'File header line info "Bezugsjahr"' ; +COMMENT ON COLUMN tkh.day IS 'File header line info "Datum"' ; COMMENT ON COLUMN tkh.kmrange_info IS 'File header line info "Strecke"' ; COMMENT ON COLUMN tkh.filename IS 'Name without type extension of the imported file' ; COMMENT ON COLUMN tkh.sounding_info IS 'File header line info "Peilung"' ; @@ -190,6 +224,8 @@ CREATE SEQUENCE TKH_VALUES_ID_SEQ ; +--Flow depth + CREATE TABLE flow_depth ( id NUMBER(9,0) PRIMARY KEY, river_id NUMBER(38,0) NOT NULL CONSTRAINT cFlowDepthRivers REFERENCES rivers(id) ON DELETE CASCADE, @@ -238,6 +274,8 @@ CREATE SEQUENCE FLOW_DEPTH_VALUES_ID_SEQ ; +--Depth evolution + CREATE TABLE depth_evolution ( id NUMBER(9,0) PRIMARY KEY, river_id NUMBER(38,0) NOT NULL CONSTRAINT cDepthEvolutionRivers REFERENCES rivers(id) ON DELETE CASCADE, @@ -280,6 +318,8 @@ CREATE SEQUENCE DEPTH_EVOLUTION_VALUES_ID_SEQ ; +--Daily discharge + CREATE TABLE daily_discharge ( id NUMBER(9,0) PRIMARY KEY, gauge_id NUMBER(38,0) NOT NULL CONSTRAINT cDailyDischargeGauge REFERENCES gauges(id) ON DELETE CASCADE, @@ -306,6 +346,8 @@ CREATE SEQUENCE DAILY_DISCHARGE_VALUES_ID_SEQ ; +--Salix line + CREATE TABLE salix ( id NUMBER(9,0) PRIMARY KEY, river_id NUMBER(38,0) NOT NULL CONSTRAINT cSalixRivers REFERENCES rivers(id) ON DELETE CASCADE, @@ -353,6 +395,8 @@ INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (6, 1.5, NULL, 'very bad'); +--Vegetation zone + CREATE TABLE vegetation_type ( id NUMBER(9,0) PRIMARY KEY, name VARCHAR2(256) NOT NULL @@ -397,6 +441,3 @@ COMMENT ON COLUMN vegetation_zone.max_overflow_days IS 'Maximum number (exclusive) of overflow days in a year for the zone type' ; CREATE SEQUENCE VEGETATION_ZONE_ID_SEQ ; - - -COMMIT; diff -r fe561931026c -r ce99d3db9477 backend/doc/schema/postgresql-alter-bed_height.sql --- a/backend/doc/schema/postgresql-alter-bed_height.sql Tue Apr 17 13:20:53 2018 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,22 +0,0 @@ -BEGIN; - - ---Add further bed height file header info columns - -ALTER TABLE bed_height ADD COLUMN sounding_width_info VARCHAR(256); -COMMENT ON COLUMN bed_height.sounding_width_info IS 'File header line info "ausgewertete Peilbreite"' ; - -ALTER TABLE bed_height ADD COLUMN "comment" VARCHAR(256); -COMMENT ON COLUMN bed_height."comment" IS 'File header line info "weitere Bemerkungen"' ; - - ---Add bed_height_values columns for min height (Talweg) and max height (Kammlage) (same type as existing value column) - -ALTER TABLE bed_height_values ADD COLUMN min_height NUMERIC; -COMMENT ON COLUMN bed_height_values.min_height IS 'Minimum bed height (Talweg) in m' ; - -ALTER TABLE bed_height_values ADD COLUMN max_height NUMERIC; -COMMENT ON COLUMN bed_height_values.max_height IS 'Maximum bed height (Kammlage) in m' ; - - -COMMIT; diff -r fe561931026c -r ce99d3db9477 backend/doc/schema/postgresql-alter-wsts.sql --- a/backend/doc/schema/postgresql-alter-wsts.sql Tue Apr 17 13:20:53 2018 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,9 +0,0 @@ -BEGIN; - - ---Add a column to specify S-INFO selection -ALTER TABLE wsts ADD COLUMN sinfo_selection VARCHAR(1) CHECK((sinfo_selection IS NULL) OR (sinfo_selection IN ('W','Q'))); -COMMENT ON COLUMN wsts.sinfo_selection IS 'Whether and how the WST series is selected within S-INFO calculation types ("W"=without-Q-group, "Q"=with-Q-group, NULL=not-selectable)' ; - - -COMMIT; diff -r fe561931026c -r ce99d3db9477 backend/doc/schema/postgresql-sinfo-uinfo.sql --- a/backend/doc/schema/postgresql-sinfo-uinfo.sql Tue Apr 17 13:20:53 2018 +0200 +++ b/backend/doc/schema/postgresql-sinfo-uinfo.sql Wed Apr 18 07:34:28 2018 +0200 @@ -1,6 +1,32 @@ BEGIN; +--Add new column wsts.sinfo_selection + +ALTER TABLE wsts ADD COLUMN sinfo_selection VARCHAR(1) CHECK((sinfo_selection IS NULL) OR (sinfo_selection IN ('W','Q'))); +COMMENT ON COLUMN wsts.sinfo_selection IS 'Whether and how the WST series is selected within S-INFO calculation types ("W"=without-Q-group, "Q"=with-Q-group, NULL=not-selectable)' ; + + +--Add new columns bed_height.sounding_width_info and bed_height.comment + +ALTER TABLE bed_height ADD COLUMN sounding_width_info VARCHAR(256); +COMMENT ON COLUMN bed_height.sounding_width_info IS 'File header line info "ausgewertete Peilbreite"' ; + +ALTER TABLE bed_height ADD COLUMN "comment" VARCHAR(256); +COMMENT ON COLUMN bed_height."comment" IS 'File header line info "weitere Bemerkungen"' ; + + +--Add new columns bed_height_values.min_height (Talweg) and bed_height_values.max_height (Kammlage) (same type as existing height column) + +ALTER TABLE bed_height_values ADD COLUMN min_height NUMERIC; +COMMENT ON COLUMN bed_height_values.min_height IS 'Minimum bed height (Talweg) in m' ; + +ALTER TABLE bed_height_values ADD COLUMN max_height NUMERIC; +COMMENT ON COLUMN bed_height_values.max_height IS 'Maximum bed height (Kammlage) in m' ; + + +--Bed mobility + CREATE TABLE bed_mobility ( id NUMERIC(9,0) PRIMARY KEY, river_id integer NOT NULL CONSTRAINT cBedMobilityRivers REFERENCES rivers(id) ON DELETE CASCADE, @@ -29,6 +55,8 @@ CREATE SEQUENCE BED_MOBILITY_VALUES_ID_SEQ ; +--Infrastructure + CREATE TABLE infrastructure ( id NUMERIC(9,0) PRIMARY KEY, river_id integer NOT NULL CONSTRAINT cInfrastructureRivers REFERENCES rivers(id) ON DELETE CASCADE, @@ -65,6 +93,8 @@ CREATE SEQUENCE INFRASTRUCTURE_VALUES_ID_SEQ ; +--Channel + CREATE TABLE channel ( id NUMERIC(9,0) PRIMARY KEY, river_id integer NOT NULL CONSTRAINT cChannelRivers REFERENCES rivers(id) ON DELETE CASCADE, @@ -100,6 +130,8 @@ CREATE SEQUENCE CHANNEL_VALUES_ID_SEQ ; +--Collision + CREATE TABLE collision_type ( id NUMERIC(9,0) PRIMARY KEY, name VARCHAR(64) NOT NULL UNIQUE @@ -145,10 +177,12 @@ CREATE SEQUENCE COLLISION_VALUES_ID_SEQ ; +--Tkh + CREATE TABLE tkh ( id NUMERIC(9,0) PRIMARY KEY, river_id integer NOT NULL CONSTRAINT cTkhRivers REFERENCES rivers(id) ON DELETE CASCADE, - year NUMERIC(4,0) NOT NULL, + day DATE, kmrange_info VARCHAR(32), filename VARCHAR(256) NOT NULL, sounding_info VARCHAR(64), @@ -156,7 +190,7 @@ "comment" VARCHAR(256) ); COMMENT ON TABLE tkh IS 'Longitudinal section of computed Transportkörperhöhe of a river' ; -COMMENT ON COLUMN tkh.year IS 'File header line info "Bezugsjahr"' ; +COMMENT ON COLUMN tkh.day IS 'File header line info "Datum"' ; COMMENT ON COLUMN tkh.kmrange_info IS 'File header line info "Strecke"' ; COMMENT ON COLUMN tkh.filename IS 'Name without type extension of the imported file' ; COMMENT ON COLUMN tkh.sounding_info IS 'File header line info "Peilung"' ; @@ -190,6 +224,8 @@ CREATE SEQUENCE TKH_VALUES_ID_SEQ ; +--Flow depth + CREATE TABLE flow_depth ( id NUMERIC(9,0) PRIMARY KEY, river_id integer NOT NULL CONSTRAINT cFlowDepthRivers REFERENCES rivers(id) ON DELETE CASCADE, @@ -238,6 +274,8 @@ CREATE SEQUENCE FLOW_DEPTH_VALUES_ID_SEQ ; +--Depth evolution + CREATE TABLE depth_evolution ( id NUMERIC(9,0) PRIMARY KEY, river_id integer NOT NULL CONSTRAINT cDepthEvolutionRivers REFERENCES rivers(id) ON DELETE CASCADE, @@ -280,6 +318,8 @@ CREATE SEQUENCE DEPTH_EVOLUTION_VALUES_ID_SEQ ; +--Daily discharge + CREATE TABLE daily_discharge ( id NUMERIC(9,0) PRIMARY KEY, gauge_id integer NOT NULL CONSTRAINT cDailyDischargeGauge REFERENCES gauges(id) ON DELETE CASCADE, @@ -306,6 +346,8 @@ CREATE SEQUENCE DAILY_DISCHARGE_VALUES_ID_SEQ ; +--Salix line + CREATE TABLE salix ( id NUMERIC(9,0) PRIMARY KEY, river_id integer NOT NULL CONSTRAINT cSalixRivers REFERENCES rivers(id) ON DELETE CASCADE, @@ -353,6 +395,8 @@ INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (6, 1.5, NULL, 'very bad'); +--Vegetation zone + CREATE TABLE vegetation_type ( id NUMERIC(9,0) PRIMARY KEY, name VARCHAR(256) NOT NULL