Mercurial > dive4elements > river

comparison backend/doc/schema/oracle-sinfo-uinfo.sql @ 9038:4c5eeaff554c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Database column "comment" renamed to "notes" (restrictions in Oracle)
author mschaefer
date Wed, 02 May 2018 06:56:21 +0200
parents 1f63e9d3b0ec
children 361de818f76e
comparison
equal deleted inserted replaced
9037:1574ce45c98d 9038:4c5eeaff554c
7 sinfo_selection CHAR(1) CHECK((sinfo_selection IS NULL) OR (sinfo_selection IN ('W','Q'))) 7 sinfo_selection CHAR(1) CHECK((sinfo_selection IS NULL) OR (sinfo_selection IN ('W','Q')))
8 ); 8 );
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)' ; 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)' ;
10 10
11 11
12 --Add new columns bed_height.sounding_width_info and bed_height.comment 12 --Add new columns bed_height.sounding_width_info and bed_height.notes
13 13
14 ALTER TABLE bed_height ADD ( 14 ALTER TABLE bed_height ADD (
15 sounding_width_info VARCHAR2(256), 15 sounding_width_info VARCHAR2(256),
16 "comment" VARCHAR2(256) 16 notes VARCHAR2(256)
17 ); 17 );
18 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"' ;
19 COMMENT ON COLUMN bed_height."comment" IS 'File header line info "weitere Bemerkungen"' ; 19 COMMENT ON COLUMN bed_height.notes IS 'File header line info "weitere Bemerkungen"' ;
20 20
21 21
22 --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)
23 23
24 ALTER TABLE bed_height_values ADD ( 24 ALTER TABLE bed_height_values ADD (
54 CREATE TABLE bed_mobility ( 54 CREATE TABLE bed_mobility (
55 id NUMBER(9,0) PRIMARY KEY, 55 id NUMBER(9,0) PRIMARY KEY,
56 river_id NUMBER(38,0) NOT NULL CONSTRAINT cBedMobilityRivers REFERENCES rivers(id) ON DELETE CASCADE, 56 river_id NUMBER(38,0) NOT NULL CONSTRAINT cBedMobilityRivers REFERENCES rivers(id) ON DELETE CASCADE,
57 kmrange_info VARCHAR2(32), 57 kmrange_info VARCHAR2(32),
58 filename VARCHAR2(256) NOT NULL, 58 filename VARCHAR2(256) NOT NULL,
59 "comment" VARCHAR2(256) 59 notes VARCHAR2(256)
60 ); 60 );
61 COMMENT ON TABLE bed_mobility IS 'Longitudinal section of the bed mobility of a river' ; 61 COMMENT ON TABLE bed_mobility IS 'Longitudinal section of the bed mobility of a river' ;
62 COMMENT ON COLUMN bed_mobility.kmrange_info IS 'File header line info "Strecke"' ; 62 COMMENT ON COLUMN bed_mobility.kmrange_info IS 'File header line info "Strecke"' ;
63 COMMENT ON COLUMN bed_mobility.filename IS 'Name without type extension of the imported file' ; 63 COMMENT ON COLUMN bed_mobility.filename IS 'Name without type extension of the imported file' ;
64 COMMENT ON COLUMN bed_mobility."comment" IS 'File header line info "weitere Bemerkungen"' ; 64 COMMENT ON COLUMN bed_mobility.notes IS 'File header line info "weitere Bemerkungen"' ;
65 65
66 CREATE SEQUENCE BED_MOBILITY_ID_SEQ ; 66 CREATE SEQUENCE BED_MOBILITY_ID_SEQ ;
67 67
68 68
69 CREATE TABLE bed_mobility_values ( 69 CREATE TABLE bed_mobility_values (
88 year NUMBER(4,0) CHECK((year >= 1700) AND (year <= 2199)), 88 year NUMBER(4,0) CHECK((year >= 1700) AND (year <= 2199)),
89 dataprovider VARCHAR2(256), 89 dataprovider VARCHAR2(256),
90 evaluation_by VARCHAR2(256), 90 evaluation_by VARCHAR2(256),
91 kmrange_info VARCHAR2(32), 91 kmrange_info VARCHAR2(32),
92 filename VARCHAR2(256) NOT NULL, 92 filename VARCHAR2(256) NOT NULL,
93 "comment" VARCHAR2(256) 93 notes VARCHAR2(256)
94 ); 94 );
95 COMMENT ON TABLE infrastructure IS 'Longitudinal section of infrastructures of a river and a type' ; 95 COMMENT ON TABLE infrastructure IS 'Longitudinal section of infrastructures of a river and a type' ;
96 COMMENT ON COLUMN infrastructure.year IS 'File header line info "Stand"' ; 96 COMMENT ON COLUMN infrastructure.year IS 'File header line info "Stand"' ;
97 COMMENT ON COLUMN infrastructure.dataprovider IS 'File header line info "Datenherkunft"' ; 97 COMMENT ON COLUMN infrastructure.dataprovider IS 'File header line info "Datenherkunft"' ;
98 COMMENT ON COLUMN infrastructure.evaluation_by IS 'File header line info "Auswerter"' ; 98 COMMENT ON COLUMN infrastructure.evaluation_by IS 'File header line info "Auswerter"' ;
99 COMMENT ON COLUMN infrastructure.kmrange_info IS 'File header line info "Strecke"' ; 99 COMMENT ON COLUMN infrastructure.kmrange_info IS 'File header line info "Strecke"' ;
100 COMMENT ON COLUMN infrastructure.filename IS 'Name without type extension of the imported file' ; 100 COMMENT ON COLUMN infrastructure.filename IS 'Name without type extension of the imported file' ;
101 COMMENT ON COLUMN infrastructure."comment" IS 'File header line info "weitere Bemerkungen"' ; 101 COMMENT ON COLUMN infrastructure.notes IS 'File header line info "weitere Bemerkungen"' ;
102 102
103 CREATE SEQUENCE INFRASTRUCTURE_ID_SEQ ; 103 CREATE SEQUENCE INFRASTRUCTURE_ID_SEQ ;
104 104
105 105
106 CREATE TABLE infrastructure_values ( 106 CREATE TABLE infrastructure_values (
122 CREATE TABLE channel ( 122 CREATE TABLE channel (
123 id NUMBER(9,0) PRIMARY KEY, 123 id NUMBER(9,0) PRIMARY KEY,
124 river_id NUMBER(38,0) NOT NULL CONSTRAINT cChannelRivers REFERENCES rivers(id) ON DELETE CASCADE, 124 river_id NUMBER(38,0) NOT NULL CONSTRAINT cChannelRivers REFERENCES rivers(id) ON DELETE CASCADE,
125 kmrange_info VARCHAR2(32), 125 kmrange_info VARCHAR2(32),
126 filename VARCHAR2(256) NOT NULL, 126 filename VARCHAR2(256) NOT NULL,
127 "comment" VARCHAR2(256), 127 notes VARCHAR2(256),
128 year_from NUMBER(4,0), 128 year_from NUMBER(4,0),
129 year_to NUMBER(4,0) 129 year_to NUMBER(4,0)
130 ); 130 );
131 COMMENT ON TABLE channel IS 'Longitudinal section of the navigable channel of a river' ; 131 COMMENT ON TABLE channel IS 'Longitudinal section of the navigable channel of a river' ;
132 COMMENT ON COLUMN channel.kmrange_info IS 'File header line info "Strecke"' ; 132 COMMENT ON COLUMN channel.kmrange_info IS 'File header line info "Strecke"' ;
133 COMMENT ON COLUMN channel.filename IS 'Name without type extension of the imported file' ; 133 COMMENT ON COLUMN channel.filename IS 'Name without type extension of the imported file' ;
134 COMMENT ON COLUMN channel."comment" IS 'File header line info "weitere Bemerkungen"' ; 134 COMMENT ON COLUMN channel.notes IS 'File header line info "weitere Bemerkungen"' ;
135 COMMENT ON COLUMN channel.year_from IS 'Start year of the period for which the channel values are valid, NULL when valid for the whole past'; 135 COMMENT ON COLUMN channel.year_from IS 'Start year of the period for which the channel values are valid, NULL when valid for the whole past';
136 COMMENT ON COLUMN channel.year_to IS 'End year of the period for which the channel values are valid, NULL when unlimited'; 136 COMMENT ON COLUMN channel.year_to IS 'End year of the period for which the channel values are valid, NULL when unlimited';
137 137
138 138
139 CREATE SEQUENCE CHANNEL_ID_SEQ ; 139 CREATE SEQUENCE CHANNEL_ID_SEQ ;
170 id NUMBER(9,0) PRIMARY KEY, 170 id NUMBER(9,0) PRIMARY KEY,
171 river_id NUMBER(38,0) NOT NULL CONSTRAINT cCollisionRivers REFERENCES rivers(id) ON DELETE CASCADE, 171 river_id NUMBER(38,0) NOT NULL CONSTRAINT cCollisionRivers REFERENCES rivers(id) ON DELETE CASCADE,
172 year NUMBER(4,0) NOT NULL, 172 year NUMBER(4,0) NOT NULL,
173 kmrange_info VARCHAR2(32), 173 kmrange_info VARCHAR2(32),
174 filename VARCHAR2(256) NOT NULL, 174 filename VARCHAR2(256) NOT NULL,
175 "comment" VARCHAR2(256) 175 notes VARCHAR2(256)
176 ); 176 );
177 COMMENT ON TABLE collision IS 'Longitudinal section of ship collisions with the river bed' ; 177 COMMENT ON TABLE collision IS 'Longitudinal section of ship collisions with the river bed' ;
178 COMMENT ON COLUMN collision.year IS 'File header line info "Jahr"' ; 178 COMMENT ON COLUMN collision.year IS 'File header line info "Jahr"' ;
179 COMMENT ON COLUMN collision.kmrange_info IS 'File header line info "Strecke"' ; 179 COMMENT ON COLUMN collision.kmrange_info IS 'File header line info "Strecke"' ;
180 COMMENT ON COLUMN collision.filename IS 'Name without type extension of the imported file' ; 180 COMMENT ON COLUMN collision.filename IS 'Name without type extension of the imported file' ;
181 COMMENT ON COLUMN collision."comment" IS 'File header line info "weitere Bemerkungen"' ; 181 COMMENT ON COLUMN collision.notes IS 'File header line info "weitere Bemerkungen"' ;
182 182
183 CREATE SEQUENCE COLLISION_ID_SEQ ; 183 CREATE SEQUENCE COLLISION_ID_SEQ ;
184 184
185 185
186 CREATE TABLE collision_values ( 186 CREATE TABLE collision_values (
210 kmrange_info VARCHAR2(32), 210 kmrange_info VARCHAR2(32),
211 filename VARCHAR2(256) NOT NULL, 211 filename VARCHAR2(256) NOT NULL,
212 groupname VARCHAR2(32) NOT NULL CHECK(groupname IN ('messung','berechnung')), 212 groupname VARCHAR2(32) NOT NULL CHECK(groupname IN ('messung','berechnung')),
213 sounding_info VARCHAR2(64), 213 sounding_info VARCHAR2(64),
214 evaluation_by VARCHAR2(256), 214 evaluation_by VARCHAR2(256),
215 "comment" VARCHAR2(256) 215 notes VARCHAR2(256)
216 ); 216 );
217 COMMENT ON TABLE tkh IS 'Longitudinal section of computed Transportkoerperhoehe of a river' ; 217 COMMENT ON TABLE tkh IS 'Longitudinal section of computed Transportkoerperhoehe of a river' ;
218 COMMENT ON COLUMN tkh.day IS 'File header line info "Datum"' ; 218 COMMENT ON COLUMN tkh.day IS 'File header line info "Datum"' ;
219 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"' ;
220 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' ; 221 COMMENT ON COLUMN tkh.groupname IS 'Group the file belongs to' ;
222 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"' ;
223 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"' ;
224 COMMENT ON COLUMN tkh."comment" IS 'File header line info "weitere Bemerkungen"' ; 224 COMMENT ON COLUMN tkh.notes IS 'File header line info "weitere Bemerkungen"' ;
225 225
226 CREATE SEQUENCE TKH_ID_SEQ ; 226 CREATE SEQUENCE TKH_ID_SEQ ;
227 227
228 228
229 CREATE TABLE tkh_column ( 229 CREATE TABLE tkh_column (
258 year NUMBER(4,0) NOT NULL, 258 year NUMBER(4,0) NOT NULL,
259 kmrange_info VARCHAR2(32), 259 kmrange_info VARCHAR2(32),
260 filename VARCHAR2(256) NOT NULL, 260 filename VARCHAR2(256) NOT NULL,
261 sounding_info VARCHAR2(64), 261 sounding_info VARCHAR2(64),
262 evaluation_by VARCHAR2(255), 262 evaluation_by VARCHAR2(255),
263 "comment" VARCHAR2(256) 263 notes VARCHAR2(256)
264 ); 264 );
265 265
266 COMMENT ON TABLE flow_depth IS 'Longitudinal section of computed flow depth of a river' ; 266 COMMENT ON TABLE flow_depth IS 'Longitudinal section of computed flow depth of a river' ;
267 COMMENT ON COLUMN flow_depth.year IS 'File header line info "Bezugsjahr"' ; 267 COMMENT ON COLUMN flow_depth.year IS 'File header line info "Bezugsjahr"' ;
268 COMMENT ON COLUMN flow_depth.kmrange_info IS 'File header line info "Strecke"' ; 268 COMMENT ON COLUMN flow_depth.kmrange_info IS 'File header line info "Strecke"' ;
269 COMMENT ON COLUMN flow_depth.filename IS 'Name without type extension of the imported file' ; 269 COMMENT ON COLUMN flow_depth.filename IS 'Name without type extension of the imported file' ;
270 COMMENT ON COLUMN flow_depth.sounding_info IS 'File header line info "Peilung"' ; 270 COMMENT ON COLUMN flow_depth.sounding_info IS 'File header line info "Peilung"' ;
271 COMMENT ON COLUMN flow_depth.evaluation_by IS 'File header line info "Auswerter"' ; 271 COMMENT ON COLUMN flow_depth.evaluation_by IS 'File header line info "Auswerter"' ;
272 COMMENT ON COLUMN flow_depth."comment" IS 'File header line info "weitere Bemerkungen"' ; 272 COMMENT ON COLUMN flow_depth.notes IS 'File header line info "weitere Bemerkungen"' ;
273 273
274 CREATE SEQUENCE FLOW_DEPTH_ID_SEQ ; 274 CREATE SEQUENCE FLOW_DEPTH_ID_SEQ ;
275 275
276 276
277 CREATE TABLE flow_depth_column ( 277 CREATE TABLE flow_depth_column (
312 kmrange_info VARCHAR2(32), 312 kmrange_info VARCHAR2(32),
313 curr_glw VARCHAR2(64) NOT NULL, 313 curr_glw VARCHAR2(64) NOT NULL,
314 old_glw VARCHAR2(64) NOT NULL, 314 old_glw VARCHAR2(64) NOT NULL,
315 filename VARCHAR2(256) NOT NULL, 315 filename VARCHAR2(256) NOT NULL,
316 groupname VARCHAR2(32) NOT NULL CHECK(groupname IN ('aktuell','etappe')), 316 groupname VARCHAR2(32) NOT NULL CHECK(groupname IN ('aktuell','etappe')),
317 "comment" VARCHAR2(256) 317 notes VARCHAR2(256)
318 ); 318 );
319 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' ;
320 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)' ;
321 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)' ;
322 COMMENT ON COLUMN depth_evolution.curr_sounding IS 'File header line info "Aktuelle Peilung / Epoche"' ; 322 COMMENT ON COLUMN depth_evolution.curr_sounding IS 'File header line info "Aktuelle Peilung / Epoche"' ;
324 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"' ;
325 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"' ;
326 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"' ;
327 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' ; 328 COMMENT ON COLUMN depth_evolution.groupname IS 'Group the file belongs to' ;
329 COMMENT ON COLUMN depth_evolution."comment" IS 'File header line info "weitere Bemerkungen"' ; 329 COMMENT ON COLUMN depth_evolution.notes IS 'File header line info "weitere Bemerkungen"' ;
330 330
331 CREATE SEQUENCE DEPTH_EVOLUTION_ID_SEQ ; 331 CREATE SEQUENCE DEPTH_EVOLUTION_ID_SEQ ;
332 332
333 333
334 CREATE TABLE depth_evolution_values ( 334 CREATE TABLE depth_evolution_values (
380 id NUMBER(9,0) PRIMARY KEY, 380 id NUMBER(9,0) PRIMARY KEY,
381 river_id NUMBER(38,0) NOT NULL CONSTRAINT cSalixRivers REFERENCES rivers(id) ON DELETE CASCADE, 381 river_id NUMBER(38,0) NOT NULL CONSTRAINT cSalixRivers REFERENCES rivers(id) ON DELETE CASCADE,
382 evaluation_by VARCHAR2(255), 382 evaluation_by VARCHAR2(255),
383 kmrange_info VARCHAR2(32), 383 kmrange_info VARCHAR2(32),
384 filename VARCHAR2(256) NOT NULL, 384 filename VARCHAR2(256) NOT NULL,
385 "comment" VARCHAR2(256) 385 notes VARCHAR2(256)
386 ); 386 );
387 387
388 COMMENT ON TABLE salix IS 'Longitudinal section of the salix line parameters of a river' ; 388 COMMENT ON TABLE salix IS 'Longitudinal section of the salix line parameters of a river' ;
389 COMMENT ON COLUMN salix.evaluation_by IS 'File header line info "Auswerter"' ; 389 COMMENT ON COLUMN salix.evaluation_by IS 'File header line info "Auswerter"' ;
390 COMMENT ON COLUMN salix.kmrange_info IS 'File header line info "Strecke"' ; 390 COMMENT ON COLUMN salix.kmrange_info IS 'File header line info "Strecke"' ;
391 COMMENT ON COLUMN salix.filename IS 'Name without type extension of the imported file' ; 391 COMMENT ON COLUMN salix.filename IS 'Name without type extension of the imported file' ;
392 COMMENT ON COLUMN salix."comment" IS 'File header line info "weitere Bemerkungen"' ; 392 COMMENT ON COLUMN salix.notes IS 'File header line info "weitere Bemerkungen"' ;
393 393
394 CREATE SEQUENCE SALIX_ID_SEQ ; 394 CREATE SEQUENCE SALIX_ID_SEQ ;
395 395
396 396
397 CREATE TABLE salix_values ( 397 CREATE TABLE salix_values (
443 CREATE TABLE vegetation ( 443 CREATE TABLE vegetation (
444 id NUMBER(9,0) PRIMARY KEY, 444 id NUMBER(9,0) PRIMARY KEY,
445 river_id NUMBER(38,0) NOT NULL CONSTRAINT cVegetationRivers REFERENCES rivers(id) ON DELETE CASCADE, 445 river_id NUMBER(38,0) NOT NULL CONSTRAINT cVegetationRivers REFERENCES rivers(id) ON DELETE CASCADE,
446 name VARCHAR2(256), 446 name VARCHAR2(256),
447 filename VARCHAR2(256) NOT NULL, 447 filename VARCHAR2(256) NOT NULL,
448 "comment" VARCHAR2(256) 448 notes VARCHAR2(256)
449 ); 449 );
450 450
451 COMMENT ON TABLE vegetation IS 'Classification of the vegetation of a river' ; 451 COMMENT ON TABLE vegetation IS 'Classification of the vegetation of a river' ;
452 COMMENT ON COLUMN vegetation.name IS 'File header line info "Einteilung"' ; 452 COMMENT ON COLUMN vegetation.name IS 'File header line info "Einteilung"' ;
453 COMMENT ON COLUMN vegetation.filename IS 'Name without type extension of the imported file' ; 453 COMMENT ON COLUMN vegetation.filename IS 'Name without type extension of the imported file' ;
454 COMMENT ON COLUMN vegetation."comment" IS 'File header line info "weitere Bemerkungen"' ; 454 COMMENT ON COLUMN vegetation.notes IS 'File header line info "weitere Bemerkungen"' ;
455 455
456 CREATE SEQUENCE VEGETATION_ID_SEQ ; 456 CREATE SEQUENCE VEGETATION_ID_SEQ ;
457 457
458 458
459 CREATE TABLE vegetation_zone ( 459 CREATE TABLE vegetation_zone (
http://dive4elements.wald.intevation.org