dive4elements/river: backend/doc/schema/postgresql-sinfo-uinfo.sql annotate

annotate backend/doc/schema/postgresql-sinfo-uinfo.sql @ 9695:9e0ee81a9b7e

configs

author	dnt_bjoernsen <d.tironi@bjoernsen.de>
date	Wed, 20 Jan 2021 11:35:59 +0100
parents	9b8ba3b83a15
children

rev	line source
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	1 BEGIN;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	2
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	3
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	4 --Add new column wsts.sinfo_selection
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	5
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	6 ALTER TABLE wsts ADD COLUMN sinfo_selection VARCHAR(1) CHECK((sinfo_selection IS NULL) OR (sinfo_selection IN ('W','Q')));
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	7 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)' ;
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	8
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	9
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	10 --Add new columns bed_height.sounding_width_info and bed_height.comment
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	11
9032 1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	12 ALTER TABLE bed_height
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	13 ADD sounding_width_info VARCHAR(256),
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	14 ADD notes VARCHAR(256);
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	15 COMMENT ON COLUMN bed_height.sounding_width_info IS 'File header line info "ausgewertete Peilbreite"' ;
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	16 COMMENT ON COLUMN bed_height.notes IS 'File header line info "weitere Bemerkungen"' ;
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	17
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	18
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	19 --Add new columns bed_height_values.min_height (Talweg) and bed_height_values.max_height (Kammlage) (same type as existing height column)
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	20
9032 1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	21 ALTER TABLE bed_height_values
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	22 ADD min_height NUMERIC,
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	23 ADD max_height NUMERIC,
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	24 ADD height01 NUMERIC,
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	25 ADD height02 NUMERIC,
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	26 ADD height03 NUMERIC,
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	27 ADD height04 NUMERIC,
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	28 ADD height05 NUMERIC,
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	29 ADD height06 NUMERIC,
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	30 ADD height07 NUMERIC,
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	31 ADD height08 NUMERIC,
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	32 ADD height09 NUMERIC,
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	33 ADD height10 NUMERIC;
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	34 COMMENT ON COLUMN bed_height_values.min_height IS 'Minimum bed height (Talweg) in m' ;
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	35 COMMENT ON COLUMN bed_height_values.max_height IS 'Maximum bed height (Kammlage) in m' ;
9032 1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	36 COMMENT ON COLUMN bed_height_values.height01 IS 'Bed height of section 1 in m' ;
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	37 COMMENT ON COLUMN bed_height_values.height02 IS 'Bed height of section 2 in m' ;
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	38 COMMENT ON COLUMN bed_height_values.height03 IS 'Bed height of section 3 in m' ;
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	39 COMMENT ON COLUMN bed_height_values.height04 IS 'Bed height of section 4 in m' ;
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	40 COMMENT ON COLUMN bed_height_values.height05 IS 'Bed height of section 5 in m' ;
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	41 COMMENT ON COLUMN bed_height_values.height06 IS 'Bed height of section 6 in m' ;
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	42 COMMENT ON COLUMN bed_height_values.height07 IS 'Bed height of section 7 in m' ;
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	43 COMMENT ON COLUMN bed_height_values.height08 IS 'Bed height of section 8 in m' ;
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	44 COMMENT ON COLUMN bed_height_values.height09 IS 'Bed height of section 9 in m' ;
1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	45 COMMENT ON COLUMN bed_height_values.height10 IS 'Bed height of section 10 in m' ;
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	46
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	47
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	48 --Bed mobility
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	49
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	50 CREATE TABLE bed_mobility (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	51 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	52 river_id integer NOT NULL CONSTRAINT cBedMobilityRivers REFERENCES rivers(id) ON DELETE CASCADE,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	53 kmrange_info VARCHAR(32),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	54 filename VARCHAR(256) NOT NULL,
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	55 notes VARCHAR(256)
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	56 );
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	57 COMMENT ON TABLE bed_mobility IS 'Longitudinal section of the bed mobility of a river' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	58 COMMENT ON COLUMN bed_mobility.kmrange_info IS 'File header line info "Strecke"' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	59 COMMENT ON COLUMN bed_mobility.filename IS 'Name without type extension of the imported file' ;
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	60 COMMENT ON COLUMN bed_mobility.notes IS 'File header line info "weitere Bemerkungen"' ;
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	61
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	62 CREATE SEQUENCE BED_MOBILITY_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	63
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	64
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	65 CREATE TABLE bed_mobility_values (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	66 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	67 bed_mobility_id NUMERIC(9,0) NOT NULL CONSTRAINT cBedMobilityValuesBedMobility REFERENCES bed_mobility(id) ON DELETE CASCADE,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	68 station NUMERIC(7,3) NOT NULL,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	69 moving NUMERIC(1,0) DEFAULT 0 NOT NULL CHECK(moving IN (0,1))
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	70 );
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	71 COMMENT ON TABLE bed_mobility_values IS 'Bed mobility of a km' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	72 COMMENT ON COLUMN bed_mobility_values.station IS 'River km' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	73 COMMENT ON COLUMN bed_mobility_values.moving IS 'Whether the river bed at the station is moving' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	74
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	75 CREATE SEQUENCE BED_MOBILITY_VALUES_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	76
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	77
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	78 --Infrastructure
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	79
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	80 CREATE TABLE infrastructure (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	81 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	82 river_id integer NOT NULL CONSTRAINT cInfrastructureRivers REFERENCES rivers(id) ON DELETE CASCADE,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	83 annotation_type_id integer NOT NULL CONSTRAINT cInfrastructureAnnotationType REFERENCES annotation_types(id),
9614 d889ffe2fb05 Nachtrag Pos. 20: rename type/part to group/type, group added in Infrastructure class mschaefer parents: 9508 diff changeset	84 group_id integer NOT NULL CONSTRAINT cInfrastructureGroupAnnoType REFERENCES annotation_types(id),
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	85 year NUMERIC(4,0) CHECK((year >= 1700) AND (year <= 2199)),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	86 dataprovider VARCHAR(256),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	87 evaluation_by VARCHAR(256),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	88 kmrange_info VARCHAR(32),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	89 filename VARCHAR(256) NOT NULL,
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	90 notes VARCHAR(256)
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	91 );
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	92 COMMENT ON TABLE infrastructure IS 'Longitudinal section of infrastructures of a river and a type' ;
9614 d889ffe2fb05 Nachtrag Pos. 20: rename type/part to group/type, group added in Infrastructure class mschaefer parents: 9508 diff changeset	93 COMMENT ON COLUMN infrastructure.group_id IS 'Reference to the infrastructure type group';
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	94 COMMENT ON COLUMN infrastructure.year IS 'File header line info "Stand"' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	95 COMMENT ON COLUMN infrastructure.dataprovider IS 'File header line info "Datenherkunft"' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	96 COMMENT ON COLUMN infrastructure.evaluation_by IS 'File header line info "Auswerter"' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	97 COMMENT ON COLUMN infrastructure.kmrange_info IS 'File header line info "Strecke"' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	98 COMMENT ON COLUMN infrastructure.filename IS 'Name without type extension of the imported file' ;
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	99 COMMENT ON COLUMN infrastructure.notes IS 'File header line info "weitere Bemerkungen"' ;
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	100
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	101 CREATE SEQUENCE INFRASTRUCTURE_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	102
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	103
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	104 CREATE TABLE infrastructure_values (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	105 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	106 infrastructure_id NUMERIC(9,0) CONSTRAINT cInfrastructureValuesInfrastructure REFERENCES infrastructure(id) ON DELETE CASCADE,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	107 station NUMERIC(7,3) NOT NULL,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	108 attribute_id integer CONSTRAINT cInfrastructureValuesAttributes REFERENCES attributes(id),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	109 height NUMERIC(6,2)
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	110 );
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	111 COMMENT ON TABLE infrastructure_values IS 'Infrastructure at a river station' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	112 COMMENT ON COLUMN infrastructure_values.station IS 'River km' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	113 COMMENT ON COLUMN infrastructure_values.height IS 'Geodetic height of the top of the infrastructure in m' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	114
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	115 CREATE SEQUENCE INFRASTRUCTURE_VALUES_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	116
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	117
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	118 --Channel
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	119
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	120 CREATE TABLE channel (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	121 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	122 river_id integer NOT NULL CONSTRAINT cChannelRivers REFERENCES rivers(id) ON DELETE CASCADE,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	123 kmrange_info VARCHAR(32),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	124 filename VARCHAR(256) NOT NULL,
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	125 notes VARCHAR(256),
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	126 year_from NUMERIC(4,0),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	127 year_to NUMERIC(4,0)
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	128 );
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	129 COMMENT ON TABLE channel IS 'Longitudinal section of the navigable channel of a river' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	130 COMMENT ON COLUMN channel.kmrange_info IS 'File header line info "Strecke"' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	131 COMMENT ON COLUMN channel.filename IS 'Name without type extension of the imported file' ;
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	132 COMMENT ON COLUMN channel.notes IS 'File header line info "weitere Bemerkungen"' ;
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	133 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';
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	134 COMMENT ON COLUMN channel.year_to IS 'End year of the period for which the channel values are valid, NULL when unlimited';
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	135
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	136
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	137 CREATE SEQUENCE CHANNEL_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	138
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	139
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	140 CREATE TABLE channel_values (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	141 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	142 channel_id NUMERIC(9,0) CONSTRAINT cChannelValuesChannel REFERENCES channel(id) ON DELETE CASCADE,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	143 station NUMERIC(7,3) NOT NULL,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	144 width NUMERIC(6,2),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	145 depth NUMERIC(6,2)
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	146 );
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	147 COMMENT ON TABLE channel_values IS 'Nominal size of the navigable channel at a river station' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	148 COMMENT ON COLUMN channel_values.station IS 'River km' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	149 COMMENT ON COLUMN channel_values.width IS 'Nominal width of the channel in m' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	150 COMMENT ON COLUMN channel_values.depth IS 'Nominal depth of the channel in m' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	151
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	152 CREATE SEQUENCE CHANNEL_VALUES_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	153
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	154
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	155 --Collision
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	156
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	157 CREATE TABLE collision_type (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	158 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	159 name VARCHAR(64) NOT NULL UNIQUE
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	160 );
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	161 COMMENT ON TABLE collision_type IS 'Type of a ship collision' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	162 COMMENT ON COLUMN collision_type.name IS 'Name of the collision type' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	163
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	164 CREATE SEQUENCE COLLISION_TYPE_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	165
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	166
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	167 CREATE TABLE collision (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	168 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	169 river_id integer NOT NULL CONSTRAINT cCollisionRivers REFERENCES rivers(id) ON DELETE CASCADE,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	170 year NUMERIC(4,0) NOT NULL,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	171 kmrange_info VARCHAR(32),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	172 filename VARCHAR(256) NOT NULL,
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	173 notes VARCHAR(256)
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	174 );
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	175 COMMENT ON TABLE collision IS 'Longitudinal section of ship collisions with the river bed' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	176 COMMENT ON COLUMN collision.year IS 'File header line info "Jahr"' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	177 COMMENT ON COLUMN collision.kmrange_info IS 'File header line info "Strecke"' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	178 COMMENT ON COLUMN collision.filename IS 'Name without type extension of the imported file' ;
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	179 COMMENT ON COLUMN collision.notes IS 'File header line info "weitere Bemerkungen"' ;
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	180
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	181 CREATE SEQUENCE COLLISION_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	182
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	183
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	184 CREATE TABLE collision_values (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	185 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	186 collision_id NUMERIC(9,0) NOT NULL CONSTRAINT cCollisionValueCollision REFERENCES collision(id) ON DELETE CASCADE,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	187 station NUMERIC(7,3) NOT NULL,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	188 event_date DATE NOT NULL,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	189 gauge_w NUMERIC(6,2),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	190 gauge_name VARCHAR(64),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	191 collision_type_id NUMERIC(9,0) NOT NULL CONSTRAINT cCollisionValuesCollisionType REFERENCES collision_type(id)
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	192 );
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	193 COMMENT ON TABLE collision_values IS 'Collision event' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	194 COMMENT ON COLUMN collision_values.station IS 'River km' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	195 COMMENT ON COLUMN collision_values.event_date IS 'Date of the collision' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	196 COMMENT ON COLUMN collision_values.gauge_w IS 'Waterlevel during the collision' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	197 COMMENT ON COLUMN collision_values.gauge_name IS 'Name of the gauge' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	198
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	199 CREATE SEQUENCE COLLISION_VALUES_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	200
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	201
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	202 --Tkh
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	203
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	204 CREATE TABLE tkh (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	205 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	206 river_id integer NOT NULL CONSTRAINT cTkhRivers REFERENCES rivers(id) ON DELETE CASCADE,
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	207 day DATE,
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	208 kmrange_info VARCHAR(32),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	209 filename VARCHAR(256) NOT NULL,
9032 1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	210 groupname VARCHAR(32) NOT NULL CHECK(groupname IN ('messung','berechnung')),
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	211 sounding_info VARCHAR(64),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	212 evaluation_by VARCHAR(256),
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	213 notes VARCHAR(256)
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	214 );
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: 8968 diff changeset	215 COMMENT ON TABLE tkh IS 'Longitudinal section of computed Transportkörperhöhe of a river' ;
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	216 COMMENT ON COLUMN tkh.day IS 'File header line info "Datum"' ;
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	217 COMMENT ON COLUMN tkh.kmrange_info IS 'File header line info "Strecke"' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	218 COMMENT ON COLUMN tkh.filename IS 'Name without type extension of the imported file' ;
9032 1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	219 COMMENT ON COLUMN tkh.groupname IS 'Group the file belongs to' ;
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	220 COMMENT ON COLUMN tkh.sounding_info IS 'File header line info "Peilung"' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	221 COMMENT ON COLUMN tkh.evaluation_by IS 'File header line info "Auswerter"' ;
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	222 COMMENT ON COLUMN tkh.notes IS 'File header line info "weitere Bemerkungen"' ;
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	223
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	224 CREATE SEQUENCE TKH_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	225
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	226
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	227 CREATE TABLE tkh_column (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	228 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	229 tkh_id NUMERIC(9,0) NOT NULL CONSTRAINT cTkhColumnTkh REFERENCES tkh(id) ON DELETE CASCADE,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	230 name VARCHAR(64) NOT NULL
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	231 );
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: 8968 diff changeset	232 COMMENT ON TABLE tkh_column IS 'Longitudinal section of computed Transportkörperhöhe for a waterlevel series' ;
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	233 COMMENT ON COLUMN tkh_column.name IS 'Name of the tkh computation column' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	234
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	235 CREATE SEQUENCE TKH_COLUMN_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	236
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	237
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	238 CREATE TABLE tkh_values (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	239 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	240 tkh_column_id NUMERIC(9,0) NOT NULL CONSTRAINT cTkhValuesTkhColumn REFERENCES tkh_column(id) ON DELETE CASCADE,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	241 station NUMERIC(7,3) NOT NULL,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	242 tkheight NUMERIC(7,3)
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	243 );
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: 8968 diff changeset	244 COMMENT ON TABLE tkh_values IS 'Transportkörperhöhe of a river station and referenced file column' ;
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	245 COMMENT ON COLUMN tkh_values.station IS 'River km' ;
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: 8968 diff changeset	246 COMMENT ON COLUMN tkh_values.tkheight IS 'Transportkörperhöhe of a river station computed for a waterlevel in m' ;
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	247
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	248 CREATE SEQUENCE TKH_VALUES_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	249
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	250
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	251 --Flow depth
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	252
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	253 CREATE TABLE flow_depth (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	254 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	255 river_id integer NOT NULL CONSTRAINT cFlowDepthRivers REFERENCES rivers(id) ON DELETE CASCADE,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	256 year NUMERIC(4,0) NOT NULL,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	257 kmrange_info VARCHAR(32),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	258 filename VARCHAR(256) NOT NULL,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	259 sounding_info VARCHAR(64),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	260 evaluation_by VARCHAR(255),
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	261 notes VARCHAR(256)
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	262 );
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	263
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	264 COMMENT ON TABLE flow_depth IS 'Longitudinal section of computed flow depth of a river' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	265 COMMENT ON COLUMN flow_depth.year IS 'File header line info "Bezugsjahr"' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	266 COMMENT ON COLUMN flow_depth.kmrange_info IS 'File header line info "Strecke"' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	267 COMMENT ON COLUMN flow_depth.filename IS 'Name without type extension of the imported file' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	268 COMMENT ON COLUMN flow_depth.sounding_info IS 'File header line info "Peilung"' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	269 COMMENT ON COLUMN flow_depth.evaluation_by IS 'File header line info "Auswerter"' ;
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	270 COMMENT ON COLUMN flow_depth.notes IS 'File header line info "weitere Bemerkungen"' ;
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	271
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	272 CREATE SEQUENCE FLOW_DEPTH_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	273
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	274
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	275 CREATE TABLE flow_depth_column (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	276 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	277 flow_depth_id NUMERIC(9,0) NOT NULL CONSTRAINT cFlowDepthColumnFlowDepth REFERENCES flow_depth(id) ON DELETE CASCADE,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	278 name VARCHAR(64) NOT NULL
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	279 );
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	280
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	281 COMMENT ON TABLE flow_depth_column IS 'Longitudinal section of computed flow depth for a waterlevel series' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	282 COMMENT ON COLUMN flow_depth_column.name IS 'Name of the flow depth computation column' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	283
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	284 CREATE SEQUENCE FLOW_DEPTH_COLUMN_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	285
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	286
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	287 CREATE TABLE flow_depth_values (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	288 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	289 flow_depth_column_id NUMERIC(9,0) NOT NULL CONSTRAINT cFlowDepthValuesFlowDepthColumn REFERENCES flow_depth_column(id) ON DELETE CASCADE,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	290 station NUMERIC(7,3) NOT NULL,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	291 depth NUMERIC(7,3)
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	292 );
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	293
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	294 COMMENT ON TABLE flow_depth_values IS 'Flow depth of a river station and referenced file column' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	295 COMMENT ON COLUMN flow_depth_values.station IS 'River km' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	296 COMMENT ON COLUMN flow_depth_values.depth IS 'Flow depth of a river station computed for a waterlevel in m' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	297
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	298 CREATE SEQUENCE FLOW_DEPTH_VALUES_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	299
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	300
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	301 --Depth evolution
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	302
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	303 CREATE TABLE depth_evolution (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	304 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	305 river_id integer NOT NULL CONSTRAINT cDepthEvolutionRivers REFERENCES rivers(id) ON DELETE CASCADE,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	306 reference_year NUMERIC(4,0) NOT NULL,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	307 start_year NUMERIC(4,0) NOT NULL,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	308 curr_sounding VARCHAR(64) NOT NULL,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	309 old_sounding VARCHAR(64) NOT NULL,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	310 kmrange_info VARCHAR(32),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	311 curr_glw VARCHAR(64) NOT NULL,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	312 old_glw VARCHAR(64) NOT NULL,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	313 filename VARCHAR(256) NOT NULL,
9032 1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	314 groupname VARCHAR(32) NOT NULL CHECK(groupname IN ('aktuell','etappe')),
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	315 notes VARCHAR(256)
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	316 );
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	317 COMMENT ON TABLE depth_evolution IS 'Longitudinal section of the evolution of the flow depth of a river' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	318 COMMENT ON COLUMN depth_evolution.reference_year IS 'File header line info "Bezugsjahr" (GlW)' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	319 COMMENT ON COLUMN depth_evolution.start_year IS 'File header line info "Ausgangsjahr" (GlW)' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	320 COMMENT ON COLUMN depth_evolution.curr_sounding IS 'File header line info "Aktuelle Peilung / Epoche"' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	321 COMMENT ON COLUMN depth_evolution.old_sounding IS 'File header line info "Historische Peilung / Epoche"' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	322 COMMENT ON COLUMN depth_evolution.kmrange_info IS 'File header line info "Strecke"' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	323 COMMENT ON COLUMN depth_evolution.curr_glw IS 'File header line info "Aktuelle Wasserspiegellage"' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	324 COMMENT ON COLUMN depth_evolution.old_glw IS 'File header line info "Historische Wasserspiegellage"' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	325 COMMENT ON COLUMN depth_evolution.filename IS 'Name without type extension of the imported file' ;
9032 1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	326 COMMENT ON COLUMN depth_evolution.groupname IS 'Group the file belongs to' ;
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	327 COMMENT ON COLUMN depth_evolution.notes IS 'File header line info "weitere Bemerkungen"' ;
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	328
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	329 CREATE SEQUENCE DEPTH_EVOLUTION_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	330
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	331
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	332 CREATE TABLE depth_evolution_values (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	333 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	334 depth_evolution_id NUMERIC(9,0) NOT NULL CONSTRAINT cDepthEvolutionValuesDepthEvolution REFERENCES depth_evolution(id) ON DELETE CASCADE,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	335 station NUMERIC(7,3) NOT NULL,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	336 total_change NUMERIC(8,4),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	337 change_per_year NUMERIC(8,4)
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	338 );
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	339 COMMENT ON TABLE depth_evolution_values IS 'Evolution of the flow depth of a river station in a referenced period of time' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	340 COMMENT ON COLUMN depth_evolution_values.station IS 'River km' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	341 COMMENT ON COLUMN depth_evolution_values.total_change IS 'Flow depth change of the whole period of time in m' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	342 COMMENT ON COLUMN depth_evolution_values.change_per_year IS 'Average yearly flow depth change in the referenced period of time in m' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	343
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	344 CREATE SEQUENCE DEPTH_EVOLUTION_VALUES_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	345
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	346
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	347 --Daily discharge
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	348
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	349 CREATE TABLE daily_discharge (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	350 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	351 gauge_id integer NOT NULL CONSTRAINT cDailyDischargeGauge REFERENCES gauges(id) ON DELETE CASCADE,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	352 filename VARCHAR(256) NOT NULL
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	353 );
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	354
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	355 COMMENT ON TABLE daily_discharge IS 'Daily discharge value series' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	356 COMMENT ON COLUMN daily_discharge.filename IS 'Name without type extension of the imported file' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	357
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	358 CREATE SEQUENCE DAILY_DISCHARGE_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	359
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	360
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	361 CREATE TABLE daily_discharge_values (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	362 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	363 daily_discharge_id NUMERIC(9,0) NOT NULL CONSTRAINT cDailyDischargeValueDailyDischarge REFERENCES daily_discharge(id) ON DELETE CASCADE,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	364 day DATE NOT NULL,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	365 discharge NUMERIC(8,3) NOT NULL
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	366 );
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	367
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	368 COMMENT ON TABLE daily_discharge_values IS 'Mean daily discharge of a date and a gauge' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	369 COMMENT ON COLUMN daily_discharge_values.day IS 'Date of the discharge value' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	370 COMMENT ON COLUMN daily_discharge_values.discharge IS 'Mean daily discharge in m^3/s' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	371
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	372 CREATE SEQUENCE DAILY_DISCHARGE_VALUES_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	373
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	374
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	375 --Salix line
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	376
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	377 CREATE TABLE salix (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	378 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	379 river_id integer NOT NULL CONSTRAINT cSalixRivers REFERENCES rivers(id) ON DELETE CASCADE,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	380 evaluation_by VARCHAR(255),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	381 kmrange_info VARCHAR(32),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	382 filename VARCHAR(256) NOT NULL,
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	383 notes VARCHAR(256)
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	384 );
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	385
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	386 COMMENT ON TABLE salix IS 'Longitudinal section of the salix line parameters of a river' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	387 COMMENT ON COLUMN salix.evaluation_by IS 'File header line info "Auswerter"' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	388 COMMENT ON COLUMN salix.kmrange_info IS 'File header line info "Strecke"' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	389 COMMENT ON COLUMN salix.filename IS 'Name without type extension of the imported file' ;
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	390 COMMENT ON COLUMN salix.notes IS 'File header line info "weitere Bemerkungen"' ;
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	391
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	392 CREATE SEQUENCE SALIX_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	393
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	394
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	395 CREATE TABLE salix_values (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	396 id NUMERIC(9,0) PRIMARY KEY,
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: 8968 diff changeset	397 salix_id NUMERIC(9,0) NOT NULL CONSTRAINT cSalixValuesSalix REFERENCES salix(id) ON DELETE CASCADE,
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	398 station NUMERIC(7,3) NOT NULL,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	399 factor NUMERIC(6,2) NOT NULL,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	400 mnw_mw_diff NUMERIC(6,2)
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	401 );
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	402
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	403 COMMENT ON COLUMN salix_values.station IS 'River km' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	404 COMMENT ON COLUMN salix_values.factor IS 'Salix "factor" of the station in m' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	405 COMMENT ON COLUMN salix_values.mnw_mw_diff IS 'Difference between MNW and MW in m (less than zero)' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	406
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	407 CREATE SEQUENCE SALIX_VALUES_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	408
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	409
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	410 CREATE TABLE salix_rank (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	411 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	412 min_value NUMERIC(6,2),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	413 max_value NUMERIC(6,2),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	414 name VARCHAR(16) NOT NULL
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	415 );
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	416 INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (1, NULL, -0.3, 'invalid');
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	417 INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (2, -0.3, 0.3, 'very good');
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	418 INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (3, 0.3, 0.5, 'good');
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	419 INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (4, 0.5, 1.0, 'moderate');
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	420 INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (5, 1.0, 1.5, 'bad');
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	421 INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (6, 1.5, NULL, 'very bad');
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	422
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	423
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	424 --Vegetation zone
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	425
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	426 CREATE TABLE vegetation_type (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	427 id NUMERIC(9,0) PRIMARY KEY,
9661 9b8ba3b83a15 Importer (s/u-info) vegetation zones: new database column in vegetation_type table for german type name, mschaefer parents: 9614 diff changeset	428 name VARCHAR(256) NOT NULL,
9b8ba3b83a15 Importer (s/u-info) vegetation zones: new database column in vegetation_type table for german type name, mschaefer parents: 9614 diff changeset	429 de_name VARCHAR(256) NOT NULL
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	430 );
9661 9b8ba3b83a15 Importer (s/u-info) vegetation zones: new database column in vegetation_type table for german type name, mschaefer parents: 9614 diff changeset	431 INSERT INTO vegetation_type (id, name, de_name) VALUES (1, 'zonal forest', 'Zonaler Wald');
9b8ba3b83a15 Importer (s/u-info) vegetation zones: new database column in vegetation_type table for german type name, mschaefer parents: 9614 diff changeset	432 INSERT INTO vegetation_type (id, name, de_name) VALUES (2, 'dry hartwood forest floodplain', 'Hartholzaue, trocken');
9b8ba3b83a15 Importer (s/u-info) vegetation zones: new database column in vegetation_type table for german type name, mschaefer parents: 9614 diff changeset	433 INSERT INTO vegetation_type (id, name, de_name) VALUES (3, 'wet hartwood forest floodplain', 'Hartholzaue, feucht');
9b8ba3b83a15 Importer (s/u-info) vegetation zones: new database column in vegetation_type table for german type name, mschaefer parents: 9614 diff changeset	434 INSERT INTO vegetation_type (id, name, de_name) VALUES (4, 'salix alba forest', 'Silberweidenwald');
9b8ba3b83a15 Importer (s/u-info) vegetation zones: new database column in vegetation_type table for german type name, mschaefer parents: 9614 diff changeset	435 INSERT INTO vegetation_type (id, name, de_name) VALUES (5, 'salix shrubs', 'Weidengebüsch');
9b8ba3b83a15 Importer (s/u-info) vegetation zones: new database column in vegetation_type table for german type name, mschaefer parents: 9614 diff changeset	436 INSERT INTO vegetation_type (id, name, de_name) VALUES (6, 'reed bed', 'Uferröhricht');
9b8ba3b83a15 Importer (s/u-info) vegetation zones: new database column in vegetation_type table for german type name, mschaefer parents: 9614 diff changeset	437 INSERT INTO vegetation_type (id, name, de_name) VALUES (7, 'bank pioneers', 'Uferpioniere');
9b8ba3b83a15 Importer (s/u-info) vegetation zones: new database column in vegetation_type table for german type name, mschaefer parents: 9614 diff changeset	438 INSERT INTO vegetation_type (id, name, de_name) VALUES (8, 'no vegetation', 'vegetationslos');
9b8ba3b83a15 Importer (s/u-info) vegetation zones: new database column in vegetation_type table for german type name, mschaefer parents: 9614 diff changeset	439 INSERT INTO vegetation_type (id, name, de_name) VALUES (9, 'water', 'Wasserfläche');
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	440
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	441
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	442 CREATE TABLE vegetation (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	443 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	444 river_id integer NOT NULL CONSTRAINT cVegetationRivers REFERENCES rivers(id) ON DELETE CASCADE,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	445 name VARCHAR(256),
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	446 filename VARCHAR(256) NOT NULL,
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	447 notes VARCHAR(256)
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	448 );
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	449
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	450 COMMENT ON TABLE vegetation IS 'Classification of the vegetation of a river' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	451 COMMENT ON COLUMN vegetation.name IS 'File header line info "Einteilung"' ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	452 COMMENT ON COLUMN vegetation.filename IS 'Name without type extension of the imported file' ;
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	453 COMMENT ON COLUMN vegetation.notes IS 'File header line info "weitere Bemerkungen"' ;
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	454
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	455 CREATE SEQUENCE VEGETATION_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	456
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	457
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	458 CREATE TABLE vegetation_zone (
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	459 id NUMERIC(9,0) PRIMARY KEY,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	460 vegetation_id NUMERIC(9,0) NOT NULL CONSTRAINT cVegetationZoneVegetation REFERENCES vegetation(id) ON DELETE CASCADE,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	461 vegetation_type_id NUMERIC(9,0) NOT NULL CONSTRAINT cVegetationZoneVegetationType REFERENCES vegetation_type(id) ON DELETE CASCADE,
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	462 min_overflow_days NUMERIC(3,0) NOT NULL,
9401 361de818f76e Added color fields to the vegetation zone database table and importer, max days field changed from exclusive to inclusive mschaefer parents: 9038 diff changeset	463 max_overflow_days NUMERIC(3,0) NOT NULL,
361de818f76e Added color fields to the vegetation zone database table and importer, max days field changed from exclusive to inclusive mschaefer parents: 9038 diff changeset	464 color_r NUMERIC(3,0) NOT NULL,
361de818f76e Added color fields to the vegetation zone database table and importer, max days field changed from exclusive to inclusive mschaefer parents: 9038 diff changeset	465 color_g NUMERIC(3,0) NOT NULL,
361de818f76e Added color fields to the vegetation zone database table and importer, max days field changed from exclusive to inclusive mschaefer parents: 9038 diff changeset	466 color_b NUMERIC(3,0) NOT NULL
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	467 );
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	468
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	469 COMMENT ON TABLE vegetation_zone IS 'Vegetation zone of a river station' ;
9508 056483975a9c Comments changed for vegetation_zone.min/max..days mschaefer parents: 9502 diff changeset	470 COMMENT ON COLUMN vegetation_zone.min_overflow_days IS 'Minimum number (exclusive) of overflow days in a year for the zone type' ;
056483975a9c Comments changed for vegetation_zone.min/max..days mschaefer parents: 9502 diff changeset	471 COMMENT ON COLUMN vegetation_zone.max_overflow_days IS 'Maximum number (inclusive) of overflow days in a year for the zone type' ;
9401 361de818f76e Added color fields to the vegetation zone database table and importer, max days field changed from exclusive to inclusive mschaefer parents: 9038 diff changeset	472 COMMENT ON COLUMN vegetation_zone.color_r IS 'Red value (0-255) of the zone color' ;
361de818f76e Added color fields to the vegetation zone database table and importer, max days field changed from exclusive to inclusive mschaefer parents: 9038 diff changeset	473 COMMENT ON COLUMN vegetation_zone.color_g IS 'Green value (0-255) of the zone color' ;
361de818f76e Added color fields to the vegetation zone database table and importer, max days field changed from exclusive to inclusive mschaefer parents: 9038 diff changeset	474 COMMENT ON COLUMN vegetation_zone.color_b IS 'Blue value (0-255) of the zone color' ;
8968 66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	475
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	476 CREATE SEQUENCE VEGETATION_ZONE_ID_SEQ ;
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	477
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	478
66471a3b3db2 Create new tables for S-INFO and U-INFO mschaefer parents: diff changeset	479 COMMIT;

Mercurial > dive4elements > river

annotate backend/doc/schema/postgresql-sinfo-uinfo.sql @ 9695:9e0ee81a9b7e