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

annotate backend/doc/schema/oracle-sinfo-uinfo.sql @ 9657:a79881a892c9

Importer (s/u-info) extensions: depth-evolution: corrected directory name Bezug_aktueller_GlW, detecting and logging of wrong units, then cancelling, various checks of the plausibility of the meta data year values and cancelling in case of errors, detecting and logging missing change values, skipping those lines

author	mschaefer
date	Mon, 23 Mar 2020 15:26:50 +0100
parents	d889ffe2fb05
children	9b8ba3b83a15

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

Mercurial > dive4elements > river

annotate backend/doc/schema/oracle-sinfo-uinfo.sql @ 9657:a79881a892c9