Mercurial > dive4elements > river

annotate backend/doc/schema/oracle-sinfo-uinfo.sql @ 9625:07f02019065e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Nachtrag Pos. 20: infrastructure query added, calculation updated for extended access class
author mschaefer
date Mon, 14 Oct 2019 08:14:58 +0200
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 ;
http://dive4elements.wald.intevation.org