Mercurial > dive4elements > river

comparison backend/doc/schema/oracle-sinfo-uinfo.sql @ 9032:1f63e9d3b0ec

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
author mschaefer
date Fri, 27 Apr 2018 17:35:12 +0200
parents ce99d3db9477
children 4c5eeaff554c
comparison
equal deleted inserted replaced
9031:efd2de78d158 9032:1f63e9d3b0ec
1 SET AUTOCOMMIT ON; 1 SET AUTOCOMMIT ON;
2 2
3 3
4 --Add new column wsts.sinfo_selection 4 --Add new column wsts.sinfo_selection
5 5
6 ALTER TABLE wsts ADD COLUMN sinfo_selection CHAR(1) CHECK((sinfo_selection IS NULL) OR (sinfo_selection IN ('W','Q'))); 6 ALTER TABLE wsts ADD (
7 sinfo_selection CHAR(1) CHECK((sinfo_selection IS NULL) OR (sinfo_selection IN ('W','Q')))
8 );
7 COMMENT ON COLUMN wsts.sinfo_selection IS 'Whether and how the WST series is selected within S-INFO calculation types ("W"=without-Q-group, "Q"=with-Q-group, NULL=not-selectable)' ; 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)' ;
8 10
9 11
10 --Add new columns bed_height.sounding_width_info and bed_height.comment 12 --Add new columns bed_height.sounding_width_info and bed_height.comment
11 13
12 ALTER TABLE bed_height ADD COLUMN sounding_width_info VARCHAR2(256); 14 ALTER TABLE bed_height ADD (
15 sounding_width_info VARCHAR2(256),
16 "comment" VARCHAR2(256)
17 );
13 COMMENT ON COLUMN bed_height.sounding_width_info IS 'File header line info "ausgewertete Peilbreite"' ; 18 COMMENT ON COLUMN bed_height.sounding_width_info IS 'File header line info "ausgewertete Peilbreite"' ;
14
15 ALTER TABLE bed_height ADD COLUMN "comment" VARCHAR2(256);
16 COMMENT ON COLUMN bed_height."comment" IS 'File header line info "weitere Bemerkungen"' ; 19 COMMENT ON COLUMN bed_height."comment" IS 'File header line info "weitere Bemerkungen"' ;
17 20
18 21
19 --Add new columns bed_height_values.min_height (Talweg) and bed_height_values.max_height (Kammlage) (same type as existing height column) 22 --Add new columns bed_height_values.min_height (Talweg) and bed_height_values.max_height (Kammlage) (same type as existing height column)
20 23
21 ALTER TABLE bed_height_values ADD COLUMN min_height DOUBLE PRECISION; 24 ALTER TABLE bed_height_values ADD (
25 min_height FLOAT(126),
26 max_height FLOAT(126),
27 height01 FLOAT(32),
28 height02 FLOAT(32),
29 height03 FLOAT(32),
30 height04 FLOAT(32),
31 height05 FLOAT(32),
32 height06 FLOAT(32),
33 height07 FLOAT(32),
34 height08 FLOAT(32),
35 height09 FLOAT(32),
36 height10 FLOAT(32)
37 );
22 COMMENT ON COLUMN bed_height_values.min_height IS 'Minimum bed height (Talweg) in m' ; 38 COMMENT ON COLUMN bed_height_values.min_height IS 'Minimum bed height (Talweg) in m' ;
23
24 ALTER TABLE bed_height_values ADD COLUMN max_height DOUBLE PRECISION;
25 COMMENT ON COLUMN bed_height_values.max_height IS 'Maximum bed height (Kammlage) in m' ; 39 COMMENT ON COLUMN bed_height_values.max_height IS 'Maximum bed height (Kammlage) in m' ;
40 COMMENT ON COLUMN bed_height_values.height01 IS 'Bed height of section 1 in m' ;
41 COMMENT ON COLUMN bed_height_values.height02 IS 'Bed height of section 2 in m' ;
42 COMMENT ON COLUMN bed_height_values.height03 IS 'Bed height of section 3 in m' ;
43 COMMENT ON COLUMN bed_height_values.height04 IS 'Bed height of section 4 in m' ;
44 COMMENT ON COLUMN bed_height_values.height05 IS 'Bed height of section 5 in m' ;
45 COMMENT ON COLUMN bed_height_values.height06 IS 'Bed height of section 6 in m' ;
46 COMMENT ON COLUMN bed_height_values.height07 IS 'Bed height of section 7 in m' ;
47 COMMENT ON COLUMN bed_height_values.height08 IS 'Bed height of section 8 in m' ;
48 COMMENT ON COLUMN bed_height_values.height09 IS 'Bed height of section 9 in m' ;
49 COMMENT ON COLUMN bed_height_values.height10 IS 'Bed height of section 10 in m' ;
26 50
27 51
28 --Bed mobility 52 --Bed mobility
29 53
30 CREATE TABLE bed_mobility ( 54 CREATE TABLE bed_mobility (
79 CREATE SEQUENCE INFRASTRUCTURE_ID_SEQ ; 103 CREATE SEQUENCE INFRASTRUCTURE_ID_SEQ ;
80 104
81 105
82 CREATE TABLE infrastructure_values ( 106 CREATE TABLE infrastructure_values (
83 id NUMBER(9,0) PRIMARY KEY, 107 id NUMBER(9,0) PRIMARY KEY,
84 infrastructure_id NUMBER(9,0) CONSTRAINT cInfrastructureValuesInfrastructure REFERENCES infrastructure(id) ON DELETE CASCADE, 108 infrastructure_id NUMBER(9,0) CONSTRAINT cInfrastructureValuesInfrastru REFERENCES infrastructure(id) ON DELETE CASCADE,
85 station NUMBER(7,3) NOT NULL, 109 station NUMBER(7,3) NOT NULL,
86 attribute_id NUMBER(38,0) CONSTRAINT cInfrastructureValuesAttributes REFERENCES attributes(id), 110 attribute_id NUMBER(38,0) CONSTRAINT cInfrastructureValuesAttrib REFERENCES attributes(id),
87 height NUMBER(6,2) 111 height NUMBER(6,2)
88 ); 112 );
89 COMMENT ON TABLE infrastructure_values IS 'Infrastructure at a river station' ; 113 COMMENT ON TABLE infrastructure_values IS 'Infrastructure at a river station' ;
90 COMMENT ON COLUMN infrastructure_values.station IS 'River km' ; 114 COMMENT ON COLUMN infrastructure_values.station IS 'River km' ;
91 COMMENT ON COLUMN infrastructure_values.height IS 'Geodetic height of the top of the infrastructure in m' ; 115 COMMENT ON COLUMN infrastructure_values.height IS 'Geodetic height of the top of the infrastructure in m' ;
183 id NUMBER(9,0) PRIMARY KEY, 207 id NUMBER(9,0) PRIMARY KEY,
184 river_id NUMBER(38,0) NOT NULL CONSTRAINT cTkhRivers REFERENCES rivers(id) ON DELETE CASCADE, 208 river_id NUMBER(38,0) NOT NULL CONSTRAINT cTkhRivers REFERENCES rivers(id) ON DELETE CASCADE,
185 day DATE, 209 day DATE,
186 kmrange_info VARCHAR2(32), 210 kmrange_info VARCHAR2(32),
187 filename VARCHAR2(256) NOT NULL, 211 filename VARCHAR2(256) NOT NULL,
212 groupname VARCHAR2(32) NOT NULL CHECK(groupname IN ('messung','berechnung')),
188 sounding_info VARCHAR2(64), 213 sounding_info VARCHAR2(64),
189 evaluation_by VARCHAR2(256), 214 evaluation_by VARCHAR2(256),
190 "comment" VARCHAR2(256) 215 "comment" VARCHAR2(256)
191 ); 216 );
192 COMMENT ON TABLE tkh IS 'Longitudinal section of computed Transportkörperhöhe of a river' ; 217 COMMENT ON TABLE tkh IS 'Longitudinal section of computed Transportkoerperhoehe of a river' ;
193 COMMENT ON COLUMN tkh.day IS 'File header line info "Datum"' ; 218 COMMENT ON COLUMN tkh.day IS 'File header line info "Datum"' ;
194 COMMENT ON COLUMN tkh.kmrange_info IS 'File header line info "Strecke"' ; 219 COMMENT ON COLUMN tkh.kmrange_info IS 'File header line info "Strecke"' ;
195 COMMENT ON COLUMN tkh.filename IS 'Name without type extension of the imported file' ; 220 COMMENT ON COLUMN tkh.filename IS 'Name without type extension of the imported file' ;
221 COMMENT ON COLUMN tkh.groupname IS 'Group the file belongs to' ;
196 COMMENT ON COLUMN tkh.sounding_info IS 'File header line info "Peilung"' ; 222 COMMENT ON COLUMN tkh.sounding_info IS 'File header line info "Peilung"' ;
197 COMMENT ON COLUMN tkh.evaluation_by IS 'File header line info "Auswerter"' ; 223 COMMENT ON COLUMN tkh.evaluation_by IS 'File header line info "Auswerter"' ;
198 COMMENT ON COLUMN tkh."comment" IS 'File header line info "weitere Bemerkungen"' ; 224 COMMENT ON COLUMN tkh."comment" IS 'File header line info "weitere Bemerkungen"' ;
199 225
200 CREATE SEQUENCE TKH_ID_SEQ ; 226 CREATE SEQUENCE TKH_ID_SEQ ;
203 CREATE TABLE tkh_column ( 229 CREATE TABLE tkh_column (
204 id NUMBER(9,0) PRIMARY KEY, 230 id NUMBER(9,0) PRIMARY KEY,
205 tkh_id NUMBER(9,0) NOT NULL CONSTRAINT cTkhColumnTkh REFERENCES tkh(id) ON DELETE CASCADE, 231 tkh_id NUMBER(9,0) NOT NULL CONSTRAINT cTkhColumnTkh REFERENCES tkh(id) ON DELETE CASCADE,
206 name VARCHAR2(64) NOT NULL 232 name VARCHAR2(64) NOT NULL
207 ); 233 );
208 COMMENT ON TABLE tkh_column IS 'Longitudinal section of computed Transportkörperhöhe for a waterlevel series' ; 234 COMMENT ON TABLE tkh_column IS 'Longitudinal section of computed Transportkoerperhoehe for a waterlevel series' ;
209 COMMENT ON COLUMN tkh_column.name IS 'Name of the tkh computation column' ; 235 COMMENT ON COLUMN tkh_column.name IS 'Name of the tkh computation column' ;
210 236
211 CREATE SEQUENCE TKH_COLUMN_ID_SEQ ; 237 CREATE SEQUENCE TKH_COLUMN_ID_SEQ ;
212 238
213 239
215 id NUMBER(9,0) PRIMARY KEY, 241 id NUMBER(9,0) PRIMARY KEY,
216 tkh_column_id NUMBER(9,0) NOT NULL CONSTRAINT cTkhValuesTkhColumn REFERENCES tkh_column(id) ON DELETE CASCADE, 242 tkh_column_id NUMBER(9,0) NOT NULL CONSTRAINT cTkhValuesTkhColumn REFERENCES tkh_column(id) ON DELETE CASCADE,
217 station NUMBER(7,3) NOT NULL, 243 station NUMBER(7,3) NOT NULL,
218 tkheight NUMBER(7,3) 244 tkheight NUMBER(7,3)
219 ); 245 );
220 COMMENT ON TABLE tkh_values IS 'Transportkörperhöhe of a river station and referenced file column' ; 246 COMMENT ON TABLE tkh_values IS 'Transportkoerperhoehe of a river station and referenced file column' ;
221 COMMENT ON COLUMN tkh_values.station IS 'River km' ; 247 COMMENT ON COLUMN tkh_values.station IS 'River km' ;
222 COMMENT ON COLUMN tkh_values.tkheight IS 'Transportkörperhöhe of a river station computed for a waterlevel in m' ; 248 COMMENT ON COLUMN tkh_values.tkheight IS 'Transportkoerperhoehe of a river station computed for a waterlevel in m' ;
223 249
224 CREATE SEQUENCE TKH_VALUES_ID_SEQ ; 250 CREATE SEQUENCE TKH_VALUES_ID_SEQ ;
225 251
226 252
227 --Flow depth 253 --Flow depth
260 CREATE SEQUENCE FLOW_DEPTH_COLUMN_ID_SEQ ; 286 CREATE SEQUENCE FLOW_DEPTH_COLUMN_ID_SEQ ;
261 287
262 288
263 CREATE TABLE flow_depth_values ( 289 CREATE TABLE flow_depth_values (
264 id NUMBER(9,0) PRIMARY KEY, 290 id NUMBER(9,0) PRIMARY KEY,
265 flow_depth_column_id NUMBER(9,0) NOT NULL CONSTRAINT cFlowDepthValuesFlowDepthColumn REFERENCES flow_depth_column(id) ON DELETE CASCADE, 291 flow_depth_column_id NUMBER(9,0) NOT NULL CONSTRAINT cFlowDepthValuesFlowDepthCol REFERENCES flow_depth_column(id) ON DELETE CASCADE,
266 station NUMBER(7,3) NOT NULL, 292 station NUMBER(7,3) NOT NULL,
267 depth NUMBER(7,3) 293 depth NUMBER(7,3)
268 ); 294 );
269 295
270 COMMENT ON TABLE flow_depth_values IS 'Flow depth of a river station and referenced file column' ; 296 COMMENT ON TABLE flow_depth_values IS 'Flow depth of a river station and referenced file column' ;
285 old_sounding VARCHAR2(64) NOT NULL, 311 old_sounding VARCHAR2(64) NOT NULL,
286 kmrange_info VARCHAR2(32), 312 kmrange_info VARCHAR2(32),
287 curr_glw VARCHAR2(64) NOT NULL, 313 curr_glw VARCHAR2(64) NOT NULL,
288 old_glw VARCHAR2(64) NOT NULL, 314 old_glw VARCHAR2(64) NOT NULL,
289 filename VARCHAR2(256) NOT NULL, 315 filename VARCHAR2(256) NOT NULL,
316 groupname VARCHAR2(32) NOT NULL CHECK(groupname IN ('aktuell','etappe')),
290 "comment" VARCHAR2(256) 317 "comment" VARCHAR2(256)
291 ); 318 );
292 COMMENT ON TABLE depth_evolution IS 'Longitudinal section of the evolution of the flow depth of a river' ; 319 COMMENT ON TABLE depth_evolution IS 'Longitudinal section of the evolution of the flow depth of a river' ;
293 COMMENT ON COLUMN depth_evolution.reference_year IS 'File header line info "Bezugsjahr" (GlW)' ; 320 COMMENT ON COLUMN depth_evolution.reference_year IS 'File header line info "Bezugsjahr" (GlW)' ;
294 COMMENT ON COLUMN depth_evolution.start_year IS 'File header line info "Ausgangsjahr" (GlW)' ; 321 COMMENT ON COLUMN depth_evolution.start_year IS 'File header line info "Ausgangsjahr" (GlW)' ;
296 COMMENT ON COLUMN depth_evolution.old_sounding IS 'File header line info "Historische Peilung / Epoche"' ; 323 COMMENT ON COLUMN depth_evolution.old_sounding IS 'File header line info "Historische Peilung / Epoche"' ;
297 COMMENT ON COLUMN depth_evolution.kmrange_info IS 'File header line info "Strecke"' ; 324 COMMENT ON COLUMN depth_evolution.kmrange_info IS 'File header line info "Strecke"' ;
298 COMMENT ON COLUMN depth_evolution.curr_glw IS 'File header line info "Aktuelle Wasserspiegellage"' ; 325 COMMENT ON COLUMN depth_evolution.curr_glw IS 'File header line info "Aktuelle Wasserspiegellage"' ;
299 COMMENT ON COLUMN depth_evolution.old_glw IS 'File header line info "Historische Wasserspiegellage"' ; 326 COMMENT ON COLUMN depth_evolution.old_glw IS 'File header line info "Historische Wasserspiegellage"' ;
300 COMMENT ON COLUMN depth_evolution.filename IS 'Name without type extension of the imported file' ; 327 COMMENT ON COLUMN depth_evolution.filename IS 'Name without type extension of the imported file' ;
328 COMMENT ON COLUMN depth_evolution.groupname IS 'Group the file belongs to' ;
301 COMMENT ON COLUMN depth_evolution."comment" IS 'File header line info "weitere Bemerkungen"' ; 329 COMMENT ON COLUMN depth_evolution."comment" IS 'File header line info "weitere Bemerkungen"' ;
302 330
303 CREATE SEQUENCE DEPTH_EVOLUTION_ID_SEQ ; 331 CREATE SEQUENCE DEPTH_EVOLUTION_ID_SEQ ;
304 332
305 333
306 CREATE TABLE depth_evolution_values ( 334 CREATE TABLE depth_evolution_values (
307 id NUMBER(9,0) PRIMARY KEY, 335 id NUMBER(9,0) PRIMARY KEY,
308 depth_evolution_id NUMBER(9,0) NOT NULL CONSTRAINT cDepthEvolutionValuesDepthEvolution REFERENCES depth_evolution(id) ON DELETE CASCADE, 336 depth_evolution_id NUMBER(9,0) NOT NULL CONSTRAINT cDepthEvolutionValuesDepthEvol REFERENCES depth_evolution(id) ON DELETE CASCADE,
309 station NUMBER(7,3) NOT NULL, 337 station NUMBER(7,3) NOT NULL,
310 total_change NUMBER(8,4), 338 total_change NUMBER(8,4),
311 change_per_year NUMBER(8,4) 339 change_per_year NUMBER(8,4)
312 ); 340 );
313 COMMENT ON TABLE depth_evolution_values IS 'Evolution of the flow depth of a river station in a referenced period of time' ; 341 COMMENT ON TABLE depth_evolution_values IS 'Evolution of the flow depth of a river station in a referenced period of time' ;
332 CREATE SEQUENCE DAILY_DISCHARGE_ID_SEQ ; 360 CREATE SEQUENCE DAILY_DISCHARGE_ID_SEQ ;
333 361
334 362
335 CREATE TABLE daily_discharge_values ( 363 CREATE TABLE daily_discharge_values (
336 id NUMBER(9,0) PRIMARY KEY, 364 id NUMBER(9,0) PRIMARY KEY,
337 daily_discharge_id NUMBER(9,0) NOT NULL CONSTRAINT cDailyDischargeValueDailyDischarge REFERENCES daily_discharge(id) ON DELETE CASCADE, 365 daily_discharge_id NUMBER(9,0) NOT NULL CONSTRAINT cDailyDischargeValueDailyDisch REFERENCES daily_discharge(id) ON DELETE CASCADE,
338 day DATE NOT NULL, 366 day DATE NOT NULL,
339 discharge NUMBER(8,3) NOT NULL 367 discharge NUMBER(8,3) NOT NULL
340 ); 368 );
341 369
342 COMMENT ON TABLE daily_discharge_values IS 'Mean daily discharge of a date and a gauge' ; 370 COMMENT ON TABLE daily_discharge_values IS 'Mean daily discharge of a date and a gauge' ;
http://dive4elements.wald.intevation.org