Mercurial > dive4elements > river

changeset 9008:ce99d3db9477

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
author mschaefer
date Wed, 18 Apr 2018 07:34:28 +0200
parents fe561931026c
children 6fd0f20b58aa
files backend/doc/schema/oracle-sinfo-uinfo.sql backend/doc/schema/postgresql-alter-bed_height.sql backend/doc/schema/postgresql-alter-wsts.sql backend/doc/schema/postgresql-sinfo-uinfo.sql
diffstat 4 files changed, 93 insertions(+), 39 deletions(-) [+]
line wrap: on
line diff
--- 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;
--- 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;
--- 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;
--- 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
http://dive4elements.wald.intevation.org