--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/backend/doc/schema/postgresql-sinfo-uinfo.sql	Tue Apr 03 08:30:23 2018 +0200
@@ -0,0 +1,402 @@
+BEGIN;
+
+
+CREATE TABLE bed_mobility (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    river_id  integer NOT NULL CONSTRAINT cBedMobilityRivers REFERENCES rivers(id) ON DELETE CASCADE,
+    kmrange_info  VARCHAR(32),
+    filename  VARCHAR(256) NOT NULL,
+    "comment"  VARCHAR(256)
+);
+COMMENT ON TABLE bed_mobility IS 'Longitudinal section of the bed mobility of a river' ;
+COMMENT ON COLUMN bed_mobility.kmrange_info IS 'File header line info "Strecke"' ;
+COMMENT ON COLUMN bed_mobility.filename IS 'Name without type extension of the imported file' ;
+COMMENT ON COLUMN bed_mobility."comment" IS 'File header line info "weitere Bemerkungen"' ;
+
+CREATE SEQUENCE BED_MOBILITY_ID_SEQ ;
+
+
+CREATE TABLE bed_mobility_values (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    bed_mobility_id  NUMERIC(9,0) NOT NULL CONSTRAINT cBedMobilityValuesBedMobility REFERENCES bed_mobility(id) ON DELETE CASCADE,
+    station  NUMERIC(7,3) NOT NULL,
+    moving  NUMERIC(1,0) DEFAULT 0 NOT NULL CHECK(moving IN (0,1))
+);
+COMMENT ON TABLE bed_mobility_values IS 'Bed mobility of a km' ;
+COMMENT ON COLUMN bed_mobility_values.station IS 'River km' ;
+COMMENT ON COLUMN bed_mobility_values.moving IS 'Whether the river bed at the station is moving' ;
+
+CREATE SEQUENCE BED_MOBILITY_VALUES_ID_SEQ ;
+
+
+CREATE TABLE infrastructure (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    river_id  integer NOT NULL CONSTRAINT cInfrastructureRivers REFERENCES rivers(id) ON DELETE CASCADE,
+    annotation_type_id  integer NOT NULL CONSTRAINT cInfrastructureAnnotationType REFERENCES annotation_types(id),
+    year  NUMERIC(4,0) CHECK((year >= 1700) AND (year <= 2199)),
+    dataprovider  VARCHAR(256),
+    evaluation_by  VARCHAR(256),
+    kmrange_info  VARCHAR(32),
+    filename  VARCHAR(256) NOT NULL,
+    "comment"  VARCHAR(256)
+);
+COMMENT ON TABLE infrastructure IS 'Longitudinal section of infrastructures of a river and a type' ;
+COMMENT ON COLUMN infrastructure.year IS 'File header line info "Stand"' ;
+COMMENT ON COLUMN infrastructure.dataprovider IS 'File header line info "Datenherkunft"' ;
+COMMENT ON COLUMN infrastructure.evaluation_by IS 'File header line info "Auswerter"' ;
+COMMENT ON COLUMN infrastructure.kmrange_info IS 'File header line info "Strecke"' ;
+COMMENT ON COLUMN infrastructure.filename IS 'Name without type extension of the imported file' ;
+COMMENT ON COLUMN infrastructure."comment" IS 'File header line info "weitere Bemerkungen"' ;
+
+CREATE SEQUENCE INFRASTRUCTURE_ID_SEQ ;
+
+
+CREATE TABLE infrastructure_values (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    infrastructure_id  NUMERIC(9,0) CONSTRAINT cInfrastructureValuesInfrastructure REFERENCES infrastructure(id) ON DELETE CASCADE,
+    station  NUMERIC(7,3) NOT NULL,
+    attribute_id  integer CONSTRAINT cInfrastructureValuesAttributes REFERENCES attributes(id),
+    height  NUMERIC(6,2)
+);
+COMMENT ON TABLE infrastructure_values IS 'Infrastructure at a river station' ;
+COMMENT ON COLUMN infrastructure_values.station IS 'River km' ;
+COMMENT ON COLUMN infrastructure_values.height IS 'Geodetic height of the top of the infrastructure in m' ;
+
+CREATE SEQUENCE INFRASTRUCTURE_VALUES_ID_SEQ ;
+
+
+CREATE TABLE channel (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    river_id  integer NOT NULL CONSTRAINT cChannelRivers REFERENCES rivers(id) ON DELETE CASCADE,
+    kmrange_info  VARCHAR(32),
+    filename  VARCHAR(256) NOT NULL,
+    "comment"  VARCHAR(256),
+    year_from  NUMERIC(4,0),
+    year_to  NUMERIC(4,0)
+);
+COMMENT ON TABLE channel IS 'Longitudinal section of the navigable channel of a river' ;
+COMMENT ON COLUMN channel.kmrange_info IS 'File header line info "Strecke"' ;
+COMMENT ON COLUMN channel.filename IS 'Name without type extension of the imported file' ;
+COMMENT ON COLUMN channel."comment" IS 'File header line info "weitere Bemerkungen"' ;
+COMMENT ON COLUMN channel.year_from IS 'Start year of the period for which the channel values are valid, NULL when valid for the whole past';
+COMMENT ON COLUMN channel.year_to IS 'End year of the period for which the channel values are valid, NULL when unlimited';
+
+
+CREATE SEQUENCE CHANNEL_ID_SEQ ;
+
+
+CREATE TABLE channel_values (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    channel_id  NUMERIC(9,0) CONSTRAINT cChannelValuesChannel REFERENCES channel(id) ON DELETE CASCADE,
+    station  NUMERIC(7,3) NOT NULL,
+    width  NUMERIC(6,2),
+    depth  NUMERIC(6,2)
+);
+COMMENT ON TABLE channel_values IS 'Nominal size of the navigable channel at a river station' ;
+COMMENT ON COLUMN channel_values.station IS 'River km' ;
+COMMENT ON COLUMN channel_values.width IS 'Nominal width of the channel in m' ;
+COMMENT ON COLUMN channel_values.depth IS 'Nominal depth of the channel in m' ;
+
+CREATE SEQUENCE CHANNEL_VALUES_ID_SEQ ;
+
+
+CREATE TABLE collision_type (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    name  VARCHAR(64) NOT NULL UNIQUE
+);
+COMMENT ON TABLE collision_type IS 'Type of a ship collision' ;
+COMMENT ON COLUMN collision_type.name IS 'Name of the collision type' ;
+
+CREATE SEQUENCE COLLISION_TYPE_ID_SEQ ;
+
+
+CREATE TABLE collision (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    river_id  integer NOT NULL CONSTRAINT cCollisionRivers REFERENCES rivers(id) ON DELETE CASCADE,
+    year  NUMERIC(4,0) NOT NULL,
+    kmrange_info  VARCHAR(32),
+    filename  VARCHAR(256) NOT NULL,
+    "comment"  VARCHAR(256)
+);
+COMMENT ON TABLE collision IS 'Longitudinal section of ship collisions with the river bed' ;
+COMMENT ON COLUMN collision.year IS 'File header line info "Jahr"' ;
+COMMENT ON COLUMN collision.kmrange_info IS 'File header line info "Strecke"' ;
+COMMENT ON COLUMN collision.filename IS 'Name without type extension of the imported file' ;
+COMMENT ON COLUMN collision."comment" IS 'File header line info "weitere Bemerkungen"' ;
+
+CREATE SEQUENCE COLLISION_ID_SEQ ;
+
+
+CREATE TABLE collision_values (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    collision_id  NUMERIC(9,0) NOT NULL CONSTRAINT cCollisionValueCollision REFERENCES collision(id) ON DELETE CASCADE,
+    station  NUMERIC(7,3) NOT NULL,
+    event_date  DATE NOT NULL,
+    gauge_w  NUMERIC(6,2),
+    gauge_name  VARCHAR(64),
+    collision_type_id  NUMERIC(9,0) NOT NULL CONSTRAINT cCollisionValuesCollisionType REFERENCES collision_type(id)
+);
+COMMENT ON TABLE collision_values IS 'Collision event' ;
+COMMENT ON COLUMN collision_values.station IS 'River km' ;
+COMMENT ON COLUMN collision_values.event_date IS 'Date of the collision' ;
+COMMENT ON COLUMN collision_values.gauge_w IS 'Waterlevel during the collision' ;
+COMMENT ON COLUMN collision_values.gauge_name IS 'Name of the gauge' ;
+
+CREATE SEQUENCE COLLISION_VALUES_ID_SEQ ;
+
+
+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,
+    kmrange_info  VARCHAR(32),
+    filename  VARCHAR(256) NOT NULL,
+    sounding_info  VARCHAR(64),
+    evaluation_by  VARCHAR(256),
+    "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.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"' ;
+COMMENT ON COLUMN tkh.evaluation_by IS 'File header line info "Auswerter"' ;
+COMMENT ON COLUMN tkh."comment" IS 'File header line info "weitere Bemerkungen"' ;
+
+CREATE SEQUENCE TKH_ID_SEQ ;
+
+
+CREATE TABLE tkh_column (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    tkh_id  NUMERIC(9,0) NOT NULL CONSTRAINT cTkhColumnTkh REFERENCES tkh(id) ON DELETE CASCADE,
+    name  VARCHAR(64) NOT NULL
+);
+COMMENT ON TABLE tkh_column IS 'Longitudinal section of computed Transportkörperhöhe for a waterlevel series' ;
+COMMENT ON COLUMN tkh_column.name IS 'Name of the tkh computation column' ;
+
+CREATE SEQUENCE TKH_COLUMN_ID_SEQ ;
+
+
+CREATE TABLE tkh_values (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    tkh_column_id  NUMERIC(9,0) NOT NULL CONSTRAINT cTkhValuesTkhColumn REFERENCES tkh_column(id) ON DELETE CASCADE,
+    station  NUMERIC(7,3) NOT NULL,
+    tkheight  NUMERIC(7,3)
+);
+COMMENT ON TABLE tkh_values IS 'Transportkörperhöhe of a river station and referenced file column' ;
+COMMENT ON COLUMN tkh_values.station IS 'River km' ;
+COMMENT ON COLUMN tkh_values.tkheight IS 'Transportkörperhöhe of a river station computed for a waterlevel in  m' ;
+
+CREATE SEQUENCE TKH_VALUES_ID_SEQ ;
+
+
+CREATE TABLE flow_depth (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    river_id  integer NOT NULL CONSTRAINT cFlowDepthRivers REFERENCES rivers(id) ON DELETE CASCADE,
+    year  NUMERIC(4,0) NOT NULL,
+    kmrange_info  VARCHAR(32),
+    filename  VARCHAR(256) NOT NULL,
+    sounding_info  VARCHAR(64),
+    evaluation_by  VARCHAR(255),
+    "comment"  VARCHAR(256)
+);
+
+COMMENT ON TABLE flow_depth IS 'Longitudinal section of computed flow depth of a river' ;
+COMMENT ON COLUMN flow_depth.year IS 'File header line info "Bezugsjahr"' ;
+COMMENT ON COLUMN flow_depth.kmrange_info IS 'File header line info "Strecke"' ;
+COMMENT ON COLUMN flow_depth.filename IS 'Name without type extension of the imported file' ;
+COMMENT ON COLUMN flow_depth.sounding_info IS 'File header line info "Peilung"' ;
+COMMENT ON COLUMN flow_depth.evaluation_by IS 'File header line info "Auswerter"' ;
+COMMENT ON COLUMN flow_depth."comment" IS 'File header line info "weitere Bemerkungen"' ;
+
+CREATE SEQUENCE FLOW_DEPTH_ID_SEQ ;
+
+
+CREATE TABLE flow_depth_column (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    flow_depth_id  NUMERIC(9,0) NOT NULL CONSTRAINT cFlowDepthColumnFlowDepth REFERENCES flow_depth(id) ON DELETE CASCADE,
+    name  VARCHAR(64) NOT NULL
+);
+
+COMMENT ON TABLE flow_depth_column IS 'Longitudinal section of computed flow depth for a waterlevel series' ;
+COMMENT ON COLUMN flow_depth_column.name IS 'Name of the flow depth computation column' ;
+
+CREATE SEQUENCE FLOW_DEPTH_COLUMN_ID_SEQ ;
+
+
+CREATE TABLE flow_depth_values (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    flow_depth_column_id  NUMERIC(9,0) NOT NULL CONSTRAINT cFlowDepthValuesFlowDepthColumn REFERENCES flow_depth_column(id) ON DELETE CASCADE,
+    station  NUMERIC(7,3) NOT NULL,
+    depth  NUMERIC(7,3)
+);
+
+COMMENT ON TABLE flow_depth_values IS 'Flow depth of a river station and referenced file column' ;
+COMMENT ON COLUMN flow_depth_values.station IS 'River km' ;
+COMMENT ON COLUMN flow_depth_values.depth IS 'Flow depth of a river station computed for a waterlevel in  m' ;
+
+CREATE SEQUENCE FLOW_DEPTH_VALUES_ID_SEQ ;
+
+
+CREATE TABLE depth_evolution (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    river_id  integer NOT NULL CONSTRAINT cDepthEvolutionRivers REFERENCES rivers(id) ON DELETE CASCADE,
+    reference_year  NUMERIC(4,0) NOT NULL,
+    start_year  NUMERIC(4,0) NOT NULL,
+    curr_sounding  VARCHAR(64) NOT NULL,
+    old_sounding  VARCHAR(64) NOT NULL,
+    kmrange_info  VARCHAR(32),
+    curr_glw  VARCHAR(64) NOT NULL,
+    old_glw  VARCHAR(64) NOT NULL,
+    filename  VARCHAR(256) NOT NULL,
+    "comment"  VARCHAR(256)
+);
+COMMENT ON TABLE depth_evolution IS 'Longitudinal section of the evolution of the flow depth of a river' ;
+COMMENT ON COLUMN depth_evolution.reference_year IS 'File header line info "Bezugsjahr" (GlW)' ;
+COMMENT ON COLUMN depth_evolution.start_year IS 'File header line info "Ausgangsjahr" (GlW)' ;
+COMMENT ON COLUMN depth_evolution.curr_sounding IS 'File header line info "Aktuelle Peilung / Epoche"' ;
+COMMENT ON COLUMN depth_evolution.old_sounding IS 'File header line info "Historische Peilung / Epoche"' ;
+COMMENT ON COLUMN depth_evolution.kmrange_info IS 'File header line info "Strecke"' ;
+COMMENT ON COLUMN depth_evolution.curr_glw IS 'File header line info "Aktuelle Wasserspiegellage"' ;
+COMMENT ON COLUMN depth_evolution.old_glw IS 'File header line info "Historische Wasserspiegellage"' ;
+COMMENT ON COLUMN depth_evolution.filename IS 'Name without type extension of the imported file' ;
+COMMENT ON COLUMN depth_evolution."comment" IS 'File header line info "weitere Bemerkungen"' ;
+
+CREATE SEQUENCE DEPTH_EVOLUTION_ID_SEQ ;
+
+
+CREATE TABLE depth_evolution_values (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    depth_evolution_id  NUMERIC(9,0) NOT NULL CONSTRAINT cDepthEvolutionValuesDepthEvolution REFERENCES depth_evolution(id) ON DELETE CASCADE,
+    station  NUMERIC(7,3) NOT NULL,
+    total_change  NUMERIC(8,4),
+    change_per_year  NUMERIC(8,4)
+);
+COMMENT ON TABLE depth_evolution_values IS 'Evolution of the flow depth of a river station in a referenced period of time' ;
+COMMENT ON COLUMN depth_evolution_values.station IS 'River km' ;
+COMMENT ON COLUMN depth_evolution_values.total_change IS 'Flow depth change of the whole period of time in m' ;
+COMMENT ON COLUMN depth_evolution_values.change_per_year IS 'Average yearly flow depth change in the referenced period of time in m' ;
+
+CREATE SEQUENCE DEPTH_EVOLUTION_VALUES_ID_SEQ ;
+
+
+CREATE TABLE daily_discharge (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    gauge_id  integer NOT NULL CONSTRAINT cDailyDischargeGauge REFERENCES gauges(id) ON DELETE CASCADE,
+    filename  VARCHAR(256) NOT NULL
+);
+
+COMMENT ON TABLE daily_discharge IS 'Daily discharge value series' ;
+COMMENT ON COLUMN daily_discharge.filename IS 'Name without type extension of the imported file' ;
+
+CREATE SEQUENCE DAILY_DISCHARGE_ID_SEQ ;
+
+
+CREATE TABLE daily_discharge_values (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    daily_discharge_id  NUMERIC(9,0) NOT NULL CONSTRAINT cDailyDischargeValueDailyDischarge REFERENCES daily_discharge(id) ON DELETE CASCADE,
+    day  DATE NOT NULL,
+    discharge  NUMERIC(8,3) NOT NULL
+);
+
+COMMENT ON TABLE daily_discharge_values IS 'Mean daily discharge of a date and a gauge' ;
+COMMENT ON COLUMN daily_discharge_values.day IS 'Date of the discharge value' ;
+COMMENT ON COLUMN daily_discharge_values.discharge IS 'Mean daily discharge in m^3/s' ;
+
+CREATE SEQUENCE DAILY_DISCHARGE_VALUES_ID_SEQ ;
+
+
+CREATE TABLE salix (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    river_id  integer NOT NULL CONSTRAINT cSalixRivers REFERENCES rivers(id) ON DELETE CASCADE,
+    evaluation_by  VARCHAR(255),
+    kmrange_info  VARCHAR(32),
+    filename  VARCHAR(256) NOT NULL,
+    "comment"  VARCHAR(256)
+);
+
+COMMENT ON TABLE salix IS 'Longitudinal section of the salix line parameters of a river' ;
+COMMENT ON COLUMN salix.evaluation_by IS 'File header line info "Auswerter"' ;
+COMMENT ON COLUMN salix.kmrange_info IS 'File header line info "Strecke"' ;
+COMMENT ON COLUMN salix.filename IS 'Name without type extension of the imported file' ;
+COMMENT ON COLUMN salix."comment" IS 'File header line info "weitere Bemerkungen"' ;
+
+CREATE SEQUENCE SALIX_ID_SEQ ;
+
+
+CREATE TABLE salix_values (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    salix_id  integer NOT NULL CONSTRAINT cSalixValuesSalix REFERENCES salix(id) ON DELETE CASCADE,
+    station  NUMERIC(7,3) NOT NULL,
+    factor  NUMERIC(6,2) NOT NULL,
+    mnw_mw_diff  NUMERIC(6,2)
+);
+
+COMMENT ON COLUMN salix_values.station IS 'River km' ;
+COMMENT ON COLUMN salix_values.factor IS 'Salix "factor" of the station in m' ;
+COMMENT ON COLUMN salix_values.mnw_mw_diff IS 'Difference between MNW and MW in m (less than zero)' ;
+
+CREATE SEQUENCE SALIX_VALUES_ID_SEQ ;
+
+
+CREATE TABLE salix_rank (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    min_value  NUMERIC(6,2),
+    max_value  NUMERIC(6,2),
+    name  VARCHAR(16) NOT NULL
+);
+INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (1, NULL, -0.3, 'invalid');
+INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (2, -0.3, 0.3, 'very good');
+INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (3, 0.3, 0.5, 'good');
+INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (4, 0.5, 1.0, 'moderate');
+INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (5, 1.0, 1.5, 'bad');
+INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (6, 1.5, NULL, 'very bad');
+
+
+CREATE TABLE vegetation_type (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    name  VARCHAR(256) NOT NULL
+);
+INSERT INTO vegetation_type (id, name) VALUES (1, 'zonal forest');
+INSERT INTO vegetation_type (id, name) VALUES (2, 'dry hartwood forest floodplain');
+INSERT INTO vegetation_type (id, name) VALUES (3, 'wet hartwood forest floodplain');
+INSERT INTO vegetation_type (id, name) VALUES (4, 'salix alba forest');
+INSERT INTO vegetation_type (id, name) VALUES (5, 'salix shrubs');
+INSERT INTO vegetation_type (id, name) VALUES (6, 'reed bed');
+INSERT INTO vegetation_type (id, name) VALUES (7, 'bank pioneers');
+INSERT INTO vegetation_type (id, name) VALUES (8, 'no vegetation');
+INSERT INTO vegetation_type (id, name) VALUES (9, 'water');
+
+
+CREATE TABLE vegetation (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    river_id  integer NOT NULL CONSTRAINT cVegetationRivers REFERENCES rivers(id) ON DELETE CASCADE,
+    name  VARCHAR(256),
+    filename  VARCHAR(256) NOT NULL,
+    "comment"  VARCHAR(256)
+);
+
+COMMENT ON TABLE vegetation IS 'Classification of the vegetation of a river' ;
+COMMENT ON COLUMN vegetation.name IS 'File header line info "Einteilung"' ;
+COMMENT ON COLUMN vegetation.filename IS 'Name without type extension of the imported file' ;
+COMMENT ON COLUMN vegetation."comment" IS 'File header line info "weitere Bemerkungen"' ;
+
+CREATE SEQUENCE VEGETATION_ID_SEQ ;
+
+
+CREATE TABLE vegetation_zone (
+    id  NUMERIC(9,0) PRIMARY KEY,
+    vegetation_id  NUMERIC(9,0) NOT NULL CONSTRAINT cVegetationZoneVegetation REFERENCES vegetation(id) ON DELETE CASCADE,
+    vegetation_type_id  NUMERIC(9,0) NOT NULL CONSTRAINT cVegetationZoneVegetationType REFERENCES vegetation_type(id) ON DELETE CASCADE,
+    min_overflow_days  NUMERIC(3,0) NOT NULL,
+    max_overflow_days  NUMERIC(3,0) NOT NULL
+);
+
+COMMENT ON TABLE vegetation_zone IS 'Vegetation zone of a river station' ;
+COMMENT ON COLUMN vegetation_zone.min_overflow_days IS 'Minimum number (inclusive) of overflow days in a year for the zone type' ;
+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;