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

annotate backend/doc/schema/oracle-sinfo-uinfo.sql @ 9415:9744ce3c3853

Rework of fixanalysis computation and dWt and WQ facets. Got rid of strange remapping and bitshifting code by explicitely saving the column information and using it in the facets. The facets also put the valid station range into their xml-metadata

author	gernotbelger
date	Thu, 16 Aug 2018 16:27:53 +0200
parents	361de818f76e
children	69a7edd7d1e1

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),
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	88 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	89 dataprovider VARCHAR2(256),
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	90 evaluation_by VARCHAR2(256),
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	91 kmrange_info VARCHAR2(32),
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	92 filename VARCHAR2(256) NOT NULL,
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	93 notes VARCHAR2(256)
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	94 );
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	95 COMMENT ON TABLE infrastructure IS 'Longitudinal section of infrastructures of a river and a type' ;
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	96 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	97 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	98 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	99 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	100 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	101 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	102
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	103 CREATE SEQUENCE INFRASTRUCTURE_ID_SEQ ;
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
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	106 CREATE TABLE infrastructure_values (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	107 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	108 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	109 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	110 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	111 height NUMBER(6,2)
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	112 );
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	113 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	114 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	115 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	116
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	117 CREATE SEQUENCE INFRASTRUCTURE_VALUES_ID_SEQ ;
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
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	120 --Channel
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	121
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	122 CREATE TABLE channel (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	123 id NUMBER(9,0) PRIMARY KEY,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	124 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	125 kmrange_info VARCHAR2(32),
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	126 filename VARCHAR2(256) NOT NULL,
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	127 notes VARCHAR2(256),
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	128 year_from NUMBER(4,0),
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	129 year_to NUMBER(4,0)
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	130 );
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	131 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	132 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	133 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	134 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	135 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	136 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	137
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	138
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	139 CREATE SEQUENCE CHANNEL_ID_SEQ ;
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
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	142 CREATE TABLE channel_values (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	143 id NUMBER(9,0) PRIMARY KEY,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	144 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	145 station NUMBER(7,3) NOT NULL,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	146 width NUMBER(6,2),
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	147 depth NUMBER(6,2)
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	148 );
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	149 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	150 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	151 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	152 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	153
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	154 CREATE SEQUENCE CHANNEL_VALUES_ID_SEQ ;
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
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	157 --Collision
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	158
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	159 CREATE TABLE collision_type (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	160 id NUMBER(9,0) PRIMARY KEY,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	161 name VARCHAR2(64) NOT NULL UNIQUE
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	162 );
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	163 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	164 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	165
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	166 CREATE SEQUENCE COLLISION_TYPE_ID_SEQ ;
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
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	169 CREATE TABLE collision (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	170 id NUMBER(9,0) PRIMARY KEY,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	171 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	172 year NUMBER(4,0) NOT NULL,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	173 kmrange_info VARCHAR2(32),
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	174 filename VARCHAR2(256) NOT NULL,
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	175 notes VARCHAR2(256)
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	176 );
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	177 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	178 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	179 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	180 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	181 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	182
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	183 CREATE SEQUENCE COLLISION_ID_SEQ ;
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
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	186 CREATE TABLE collision_values (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	187 id NUMBER(9,0) PRIMARY KEY,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	188 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	189 station NUMBER(7,3) NOT NULL,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	190 event_date DATE NOT NULL,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	191 gauge_w NUMBER(6,2),
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	192 gauge_name VARCHAR2(64),
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	193 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	194 );
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	195 COMMENT ON TABLE collision_values IS 'Collision event' ;
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	196 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	197 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	198 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	199 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	200
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	201 CREATE SEQUENCE COLLISION_VALUES_ID_SEQ ;
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
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	204 --Tkh
ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	205
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	206 CREATE TABLE tkh (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	207 id NUMBER(9,0) PRIMARY KEY,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	208 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	209 day DATE,
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	210 kmrange_info VARCHAR2(32),
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	211 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	212 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	213 sounding_info VARCHAR2(64),
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	214 evaluation_by VARCHAR2(256),
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	215 notes VARCHAR2(256)
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	216 );
9032 1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	217 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	218 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	219 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	220 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	221 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	222 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	223 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	224 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	225
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	226 CREATE SEQUENCE TKH_ID_SEQ ;
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
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	229 CREATE TABLE tkh_column (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	230 id NUMBER(9,0) PRIMARY KEY,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	231 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	232 name VARCHAR2(64) NOT NULL
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	233 );
9032 1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	234 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	235 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	236
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	237 CREATE SEQUENCE TKH_COLUMN_ID_SEQ ;
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
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	240 CREATE TABLE tkh_values (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	241 id NUMBER(9,0) PRIMARY KEY,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	242 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	243 station NUMBER(7,3) NOT NULL,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	244 tkheight NUMBER(7,3)
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	245 );
9032 1f63e9d3b0ec New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle mschaefer parents: 9008 diff changeset	246 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	247 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	248 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	249
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	250 CREATE SEQUENCE TKH_VALUES_ID_SEQ ;
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
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	253 --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	254
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	255 CREATE TABLE flow_depth (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	256 id NUMBER(9,0) PRIMARY KEY,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	257 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	258 year NUMBER(4,0) NOT NULL,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	259 kmrange_info VARCHAR2(32),
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	260 filename VARCHAR2(256) NOT NULL,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	261 sounding_info VARCHAR2(64),
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	262 evaluation_by VARCHAR2(255),
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	263 notes VARCHAR2(256)
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	264 );
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	265
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	266 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	267 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	268 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	269 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	270 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	271 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	272 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	273
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	274 CREATE SEQUENCE FLOW_DEPTH_ID_SEQ ;
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
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	277 CREATE TABLE flow_depth_column (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	278 id NUMBER(9,0) PRIMARY KEY,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	279 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	280 name VARCHAR2(64) NOT NULL
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	281 );
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	282
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	283 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	284 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	285
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	286 CREATE SEQUENCE FLOW_DEPTH_COLUMN_ID_SEQ ;
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
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	289 CREATE TABLE flow_depth_values (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	290 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	291 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	292 station NUMBER(7,3) NOT NULL,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	293 depth NUMBER(7,3)
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	294 );
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	295
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	296 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	297 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	298 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	299
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	300 CREATE SEQUENCE FLOW_DEPTH_VALUES_ID_SEQ ;
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
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	303 --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	304
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	305 CREATE TABLE depth_evolution (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	306 id NUMBER(9,0) PRIMARY KEY,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	307 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	308 reference_year NUMBER(4,0) NOT NULL,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	309 start_year NUMBER(4,0) NOT NULL,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	310 curr_sounding VARCHAR2(64) NOT NULL,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	311 old_sounding VARCHAR2(64) NOT NULL,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	312 kmrange_info VARCHAR2(32),
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	313 curr_glw VARCHAR2(64) NOT NULL,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	314 old_glw VARCHAR2(64) NOT NULL,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	315 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	316 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	317 notes VARCHAR2(256)
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	318 );
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	319 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	320 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	321 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	322 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	323 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	324 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	325 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	326 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	327 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	328 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	329 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	330
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	331 CREATE SEQUENCE DEPTH_EVOLUTION_ID_SEQ ;
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
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	334 CREATE TABLE depth_evolution_values (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	335 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	336 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	337 station NUMBER(7,3) NOT NULL,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	338 total_change NUMBER(8,4),
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	339 change_per_year NUMBER(8,4)
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	340 );
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	341 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	342 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	343 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	344 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	345
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	346 CREATE SEQUENCE DEPTH_EVOLUTION_VALUES_ID_SEQ ;
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
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	349 --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	350
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	351 CREATE TABLE daily_discharge (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	352 id NUMBER(9,0) PRIMARY KEY,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	353 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	354 filename VARCHAR2(256) NOT NULL
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	355 );
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	356
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	357 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	358 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	359
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	360 CREATE SEQUENCE DAILY_DISCHARGE_ID_SEQ ;
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
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	363 CREATE TABLE daily_discharge_values (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	364 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	365 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	366 day DATE NOT NULL,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	367 discharge NUMBER(8,3) NOT NULL
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	368 );
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	369
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	370 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	371 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	372 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	373
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	374 CREATE SEQUENCE DAILY_DISCHARGE_VALUES_ID_SEQ ;
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
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	377 --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	378
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	379 CREATE TABLE salix (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	380 id NUMBER(9,0) PRIMARY KEY,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	381 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	382 evaluation_by VARCHAR2(255),
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	383 kmrange_info VARCHAR2(32),
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	384 filename VARCHAR2(256) NOT NULL,
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	385 notes VARCHAR2(256)
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	386 );
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	387
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	388 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	389 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	390 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	391 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	392 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	393
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	394 CREATE SEQUENCE SALIX_ID_SEQ ;
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
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	397 CREATE TABLE salix_values (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	398 id NUMBER(9,0) PRIMARY KEY,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	399 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	400 station NUMBER(7,3) NOT NULL,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	401 factor NUMBER(6,2) NOT NULL,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	402 mnw_mw_diff NUMBER(6,2)
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	403 );
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	404
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	405 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	406 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	407 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	408
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	409 CREATE SEQUENCE SALIX_VALUES_ID_SEQ ;
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
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	412 CREATE TABLE salix_rank (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	413 id NUMBER(9,0) PRIMARY KEY,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	414 min_value NUMBER(6,2),
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	415 max_value NUMBER(6,2),
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	416 name VARCHAR2(16) NOT NULL
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	417 );
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	418 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	419 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	420 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	421 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	422 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	423 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	424
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	425
9008 ce99d3db9477 Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script mschaefer parents: 8990 diff changeset	426 --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	427
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	428 CREATE TABLE vegetation_type (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	429 id NUMBER(9,0) PRIMARY KEY,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	430 name VARCHAR2(256) NOT NULL
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	431 );
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	432 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	433 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	434 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	435 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	436 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	437 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	438 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	439 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	440 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	441
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	442
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	443 CREATE TABLE vegetation (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	444 id NUMBER(9,0) PRIMARY KEY,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	445 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	446 name VARCHAR2(256),
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	447 filename VARCHAR2(256) NOT NULL,
9038 4c5eeaff554c Database column "comment" renamed to "notes" (restrictions in Oracle) mschaefer parents: 9032 diff changeset	448 notes VARCHAR2(256)
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	449 );
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	450
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	451 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	452 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	453 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	454 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	455
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	456 CREATE SEQUENCE VEGETATION_ID_SEQ ;
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
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	459 CREATE TABLE vegetation_zone (
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	460 id NUMBER(9,0) PRIMARY KEY,
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	461 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	462 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	463 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	464 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	465 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	466 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	467 color_b NUMBER(3,0) NOT NULL
8990 07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	468 );
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	469
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	470 COMMENT ON TABLE vegetation_zone IS 'Vegetation zone of a river station' ;
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	471 COMMENT ON COLUMN vegetation_zone.min_overflow_days IS 'Minimum 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.max_overflow_days IS 'Maximum number (inclusive) of overflow days in a year for the zone type' ;
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_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	474 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	475 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	476
07dcedddf839 Oracle schema extension added, a few changes in the postgresql schema mschaefer parents: diff changeset	477 CREATE SEQUENCE VEGETATION_ZONE_ID_SEQ ;

Mercurial > dive4elements > river

annotate backend/doc/schema/oracle-sinfo-uinfo.sql @ 9415:9744ce3c3853