Mercurial > dive4elements > river

comparison backend/doc/schema/postgresql-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
9 9
10 --Add new columns bed_height.sounding_width_info and bed_height.comment 10 --Add new columns bed_height.sounding_width_info and bed_height.comment
11 11
12 ALTER TABLE bed_height 12 ALTER TABLE bed_height
13 ADD sounding_width_info VARCHAR(256), 13 ADD sounding_width_info VARCHAR(256),
14 ADD "comment" VARCHAR(256); 14 ADD notes VARCHAR(256);
15 COMMENT ON COLUMN bed_height.sounding_width_info IS 'File header line info "ausgewertete Peilbreite"' ; 15 COMMENT ON COLUMN bed_height.sounding_width_info IS 'File header line info "ausgewertete Peilbreite"' ;
16 COMMENT ON COLUMN bed_height."comment" IS 'File header line info "weitere Bemerkungen"' ; 16 COMMENT ON COLUMN bed_height.notes IS 'File header line info "weitere Bemerkungen"' ;
17 17
18 18
19 --Add new columns bed_height_values.min_height (Talweg) and bed_height_values.max_height (Kammlage) (same type as existing height column) 19 --Add new columns bed_height_values.min_height (Talweg) and bed_height_values.max_height (Kammlage) (same type as existing height column)
20 20
21 ALTER TABLE bed_height_values 21 ALTER TABLE bed_height_values
50 CREATE TABLE bed_mobility ( 50 CREATE TABLE bed_mobility (
51 id NUMERIC(9,0) PRIMARY KEY, 51 id NUMERIC(9,0) PRIMARY KEY,
52 river_id integer NOT NULL CONSTRAINT cBedMobilityRivers REFERENCES rivers(id) ON DELETE CASCADE, 52 river_id integer NOT NULL CONSTRAINT cBedMobilityRivers REFERENCES rivers(id) ON DELETE CASCADE,
53 kmrange_info VARCHAR(32), 53 kmrange_info VARCHAR(32),
54 filename VARCHAR(256) NOT NULL, 54 filename VARCHAR(256) NOT NULL,
55 "comment" VARCHAR(256) 55 notes VARCHAR(256)
56 ); 56 );
57 COMMENT ON TABLE bed_mobility IS 'Longitudinal section of the bed mobility of a river' ; 57 COMMENT ON TABLE bed_mobility IS 'Longitudinal section of the bed mobility of a river' ;
58 COMMENT ON COLUMN bed_mobility.kmrange_info IS 'File header line info "Strecke"' ; 58 COMMENT ON COLUMN bed_mobility.kmrange_info IS 'File header line info "Strecke"' ;
59 COMMENT ON COLUMN bed_mobility.filename IS 'Name without type extension of the imported file' ; 59 COMMENT ON COLUMN bed_mobility.filename IS 'Name without type extension of the imported file' ;
60 COMMENT ON COLUMN bed_mobility."comment" IS 'File header line info "weitere Bemerkungen"' ; 60 COMMENT ON COLUMN bed_mobility.notes IS 'File header line info "weitere Bemerkungen"' ;
61 61
62 CREATE SEQUENCE BED_MOBILITY_ID_SEQ ; 62 CREATE SEQUENCE BED_MOBILITY_ID_SEQ ;
63 63
64 64
65 CREATE TABLE bed_mobility_values ( 65 CREATE TABLE bed_mobility_values (
84 year NUMERIC(4,0) CHECK((year >= 1700) AND (year <= 2199)), 84 year NUMERIC(4,0) CHECK((year >= 1700) AND (year <= 2199)),
85 dataprovider VARCHAR(256), 85 dataprovider VARCHAR(256),
86 evaluation_by VARCHAR(256), 86 evaluation_by VARCHAR(256),
87 kmrange_info VARCHAR(32), 87 kmrange_info VARCHAR(32),
88 filename VARCHAR(256) NOT NULL, 88 filename VARCHAR(256) NOT NULL,
89 "comment" VARCHAR(256) 89 notes VARCHAR(256)
90 ); 90 );
91 COMMENT ON TABLE infrastructure IS 'Longitudinal section of infrastructures of a river and a type' ; 91 COMMENT ON TABLE infrastructure IS 'Longitudinal section of infrastructures of a river and a type' ;
92 COMMENT ON COLUMN infrastructure.year IS 'File header line info "Stand"' ; 92 COMMENT ON COLUMN infrastructure.year IS 'File header line info "Stand"' ;
93 COMMENT ON COLUMN infrastructure.dataprovider IS 'File header line info "Datenherkunft"' ; 93 COMMENT ON COLUMN infrastructure.dataprovider IS 'File header line info "Datenherkunft"' ;
94 COMMENT ON COLUMN infrastructure.evaluation_by IS 'File header line info "Auswerter"' ; 94 COMMENT ON COLUMN infrastructure.evaluation_by IS 'File header line info "Auswerter"' ;
95 COMMENT ON COLUMN infrastructure.kmrange_info IS 'File header line info "Strecke"' ; 95 COMMENT ON COLUMN infrastructure.kmrange_info IS 'File header line info "Strecke"' ;
96 COMMENT ON COLUMN infrastructure.filename IS 'Name without type extension of the imported file' ; 96 COMMENT ON COLUMN infrastructure.filename IS 'Name without type extension of the imported file' ;
97 COMMENT ON COLUMN infrastructure."comment" IS 'File header line info "weitere Bemerkungen"' ; 97 COMMENT ON COLUMN infrastructure.notes IS 'File header line info "weitere Bemerkungen"' ;
98 98
99 CREATE SEQUENCE INFRASTRUCTURE_ID_SEQ ; 99 CREATE SEQUENCE INFRASTRUCTURE_ID_SEQ ;
100 100
101 101
102 CREATE TABLE infrastructure_values ( 102 CREATE TABLE infrastructure_values (
118 CREATE TABLE channel ( 118 CREATE TABLE channel (
119 id NUMERIC(9,0) PRIMARY KEY, 119 id NUMERIC(9,0) PRIMARY KEY,
120 river_id integer NOT NULL CONSTRAINT cChannelRivers REFERENCES rivers(id) ON DELETE CASCADE, 120 river_id integer NOT NULL CONSTRAINT cChannelRivers REFERENCES rivers(id) ON DELETE CASCADE,
121 kmrange_info VARCHAR(32), 121 kmrange_info VARCHAR(32),
122 filename VARCHAR(256) NOT NULL, 122 filename VARCHAR(256) NOT NULL,
123 "comment" VARCHAR(256), 123 notes VARCHAR(256),
124 year_from NUMERIC(4,0), 124 year_from NUMERIC(4,0),
125 year_to NUMERIC(4,0) 125 year_to NUMERIC(4,0)
126 ); 126 );
127 COMMENT ON TABLE channel IS 'Longitudinal section of the navigable channel of a river' ; 127 COMMENT ON TABLE channel IS 'Longitudinal section of the navigable channel of a river' ;
128 COMMENT ON COLUMN channel.kmrange_info IS 'File header line info "Strecke"' ; 128 COMMENT ON COLUMN channel.kmrange_info IS 'File header line info "Strecke"' ;
129 COMMENT ON COLUMN channel.filename IS 'Name without type extension of the imported file' ; 129 COMMENT ON COLUMN channel.filename IS 'Name without type extension of the imported file' ;
130 COMMENT ON COLUMN channel."comment" IS 'File header line info "weitere Bemerkungen"' ; 130 COMMENT ON COLUMN channel.notes IS 'File header line info "weitere Bemerkungen"' ;
131 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'; 131 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';
132 COMMENT ON COLUMN channel.year_to IS 'End year of the period for which the channel values are valid, NULL when unlimited'; 132 COMMENT ON COLUMN channel.year_to IS 'End year of the period for which the channel values are valid, NULL when unlimited';
133 133
134 134
135 CREATE SEQUENCE CHANNEL_ID_SEQ ; 135 CREATE SEQUENCE CHANNEL_ID_SEQ ;
166 id NUMERIC(9,0) PRIMARY KEY, 166 id NUMERIC(9,0) PRIMARY KEY,
167 river_id integer NOT NULL CONSTRAINT cCollisionRivers REFERENCES rivers(id) ON DELETE CASCADE, 167 river_id integer NOT NULL CONSTRAINT cCollisionRivers REFERENCES rivers(id) ON DELETE CASCADE,
168 year NUMERIC(4,0) NOT NULL, 168 year NUMERIC(4,0) NOT NULL,
169 kmrange_info VARCHAR(32), 169 kmrange_info VARCHAR(32),
170 filename VARCHAR(256) NOT NULL, 170 filename VARCHAR(256) NOT NULL,
171 "comment" VARCHAR(256) 171 notes VARCHAR(256)
172 ); 172 );
173 COMMENT ON TABLE collision IS 'Longitudinal section of ship collisions with the river bed' ; 173 COMMENT ON TABLE collision IS 'Longitudinal section of ship collisions with the river bed' ;
174 COMMENT ON COLUMN collision.year IS 'File header line info "Jahr"' ; 174 COMMENT ON COLUMN collision.year IS 'File header line info "Jahr"' ;
175 COMMENT ON COLUMN collision.kmrange_info IS 'File header line info "Strecke"' ; 175 COMMENT ON COLUMN collision.kmrange_info IS 'File header line info "Strecke"' ;
176 COMMENT ON COLUMN collision.filename IS 'Name without type extension of the imported file' ; 176 COMMENT ON COLUMN collision.filename IS 'Name without type extension of the imported file' ;
177 COMMENT ON COLUMN collision."comment" IS 'File header line info "weitere Bemerkungen"' ; 177 COMMENT ON COLUMN collision.notes IS 'File header line info "weitere Bemerkungen"' ;
178 178
179 CREATE SEQUENCE COLLISION_ID_SEQ ; 179 CREATE SEQUENCE COLLISION_ID_SEQ ;
180 180
181 181
182 CREATE TABLE collision_values ( 182 CREATE TABLE collision_values (
206 kmrange_info VARCHAR(32), 206 kmrange_info VARCHAR(32),
207 filename VARCHAR(256) NOT NULL, 207 filename VARCHAR(256) NOT NULL,
208 groupname VARCHAR(32) NOT NULL CHECK(groupname IN ('messung','berechnung')), 208 groupname VARCHAR(32) NOT NULL CHECK(groupname IN ('messung','berechnung')),
209 sounding_info VARCHAR(64), 209 sounding_info VARCHAR(64),
210 evaluation_by VARCHAR(256), 210 evaluation_by VARCHAR(256),
211 "comment" VARCHAR(256) 211 notes VARCHAR(256)
212 ); 212 );
213 COMMENT ON TABLE tkh IS 'Longitudinal section of computed Transportkörperhöhe of a river' ; 213 COMMENT ON TABLE tkh IS 'Longitudinal section of computed Transportkörperhöhe of a river' ;
214 COMMENT ON COLUMN tkh.day IS 'File header line info "Datum"' ; 214 COMMENT ON COLUMN tkh.day IS 'File header line info "Datum"' ;
215 COMMENT ON COLUMN tkh.kmrange_info IS 'File header line info "Strecke"' ; 215 COMMENT ON COLUMN tkh.kmrange_info IS 'File header line info "Strecke"' ;
216 COMMENT ON COLUMN tkh.filename IS 'Name without type extension of the imported file' ; 216 COMMENT ON COLUMN tkh.filename IS 'Name without type extension of the imported file' ;
217 COMMENT ON COLUMN tkh.groupname IS 'Group the file belongs to' ; 217 COMMENT ON COLUMN tkh.groupname IS 'Group the file belongs to' ;
218 COMMENT ON COLUMN tkh.sounding_info IS 'File header line info "Peilung"' ; 218 COMMENT ON COLUMN tkh.sounding_info IS 'File header line info "Peilung"' ;
219 COMMENT ON COLUMN tkh.evaluation_by IS 'File header line info "Auswerter"' ; 219 COMMENT ON COLUMN tkh.evaluation_by IS 'File header line info "Auswerter"' ;
220 COMMENT ON COLUMN tkh."comment" IS 'File header line info "weitere Bemerkungen"' ; 220 COMMENT ON COLUMN tkh.notes IS 'File header line info "weitere Bemerkungen"' ;
221 221
222 CREATE SEQUENCE TKH_ID_SEQ ; 222 CREATE SEQUENCE TKH_ID_SEQ ;
223 223
224 224
225 CREATE TABLE tkh_column ( 225 CREATE TABLE tkh_column (
254 year NUMERIC(4,0) NOT NULL, 254 year NUMERIC(4,0) NOT NULL,
255 kmrange_info VARCHAR(32), 255 kmrange_info VARCHAR(32),
256 filename VARCHAR(256) NOT NULL, 256 filename VARCHAR(256) NOT NULL,
257 sounding_info VARCHAR(64), 257 sounding_info VARCHAR(64),
258 evaluation_by VARCHAR(255), 258 evaluation_by VARCHAR(255),
259 "comment" VARCHAR(256) 259 notes VARCHAR(256)
260 ); 260 );
261 261
262 COMMENT ON TABLE flow_depth IS 'Longitudinal section of computed flow depth of a river' ; 262 COMMENT ON TABLE flow_depth IS 'Longitudinal section of computed flow depth of a river' ;
263 COMMENT ON COLUMN flow_depth.year IS 'File header line info "Bezugsjahr"' ; 263 COMMENT ON COLUMN flow_depth.year IS 'File header line info "Bezugsjahr"' ;
264 COMMENT ON COLUMN flow_depth.kmrange_info IS 'File header line info "Strecke"' ; 264 COMMENT ON COLUMN flow_depth.kmrange_info IS 'File header line info "Strecke"' ;
265 COMMENT ON COLUMN flow_depth.filename IS 'Name without type extension of the imported file' ; 265 COMMENT ON COLUMN flow_depth.filename IS 'Name without type extension of the imported file' ;
266 COMMENT ON COLUMN flow_depth.sounding_info IS 'File header line info "Peilung"' ; 266 COMMENT ON COLUMN flow_depth.sounding_info IS 'File header line info "Peilung"' ;
267 COMMENT ON COLUMN flow_depth.evaluation_by IS 'File header line info "Auswerter"' ; 267 COMMENT ON COLUMN flow_depth.evaluation_by IS 'File header line info "Auswerter"' ;
268 COMMENT ON COLUMN flow_depth."comment" IS 'File header line info "weitere Bemerkungen"' ; 268 COMMENT ON COLUMN flow_depth.notes IS 'File header line info "weitere Bemerkungen"' ;
269 269
270 CREATE SEQUENCE FLOW_DEPTH_ID_SEQ ; 270 CREATE SEQUENCE FLOW_DEPTH_ID_SEQ ;
271 271
272 272
273 CREATE TABLE flow_depth_column ( 273 CREATE TABLE flow_depth_column (
308 kmrange_info VARCHAR(32), 308 kmrange_info VARCHAR(32),
309 curr_glw VARCHAR(64) NOT NULL, 309 curr_glw VARCHAR(64) NOT NULL,
310 old_glw VARCHAR(64) NOT NULL, 310 old_glw VARCHAR(64) NOT NULL,
311 filename VARCHAR(256) NOT NULL, 311 filename VARCHAR(256) NOT NULL,
312 groupname VARCHAR(32) NOT NULL CHECK(groupname IN ('aktuell','etappe')), 312 groupname VARCHAR(32) NOT NULL CHECK(groupname IN ('aktuell','etappe')),
313 "comment" VARCHAR(256) 313 notes VARCHAR(256)
314 ); 314 );
315 COMMENT ON TABLE depth_evolution IS 'Longitudinal section of the evolution of the flow depth of a river' ; 315 COMMENT ON TABLE depth_evolution IS 'Longitudinal section of the evolution of the flow depth of a river' ;
316 COMMENT ON COLUMN depth_evolution.reference_year IS 'File header line info "Bezugsjahr" (GlW)' ; 316 COMMENT ON COLUMN depth_evolution.reference_year IS 'File header line info "Bezugsjahr" (GlW)' ;
317 COMMENT ON COLUMN depth_evolution.start_year IS 'File header line info "Ausgangsjahr" (GlW)' ; 317 COMMENT ON COLUMN depth_evolution.start_year IS 'File header line info "Ausgangsjahr" (GlW)' ;
318 COMMENT ON COLUMN depth_evolution.curr_sounding IS 'File header line info "Aktuelle Peilung / Epoche"' ; 318 COMMENT ON COLUMN depth_evolution.curr_sounding IS 'File header line info "Aktuelle Peilung / Epoche"' ;
320 COMMENT ON COLUMN depth_evolution.kmrange_info IS 'File header line info "Strecke"' ; 320 COMMENT ON COLUMN depth_evolution.kmrange_info IS 'File header line info "Strecke"' ;
321 COMMENT ON COLUMN depth_evolution.curr_glw IS 'File header line info "Aktuelle Wasserspiegellage"' ; 321 COMMENT ON COLUMN depth_evolution.curr_glw IS 'File header line info "Aktuelle Wasserspiegellage"' ;
322 COMMENT ON COLUMN depth_evolution.old_glw IS 'File header line info "Historische Wasserspiegellage"' ; 322 COMMENT ON COLUMN depth_evolution.old_glw IS 'File header line info "Historische Wasserspiegellage"' ;
323 COMMENT ON COLUMN depth_evolution.filename IS 'Name without type extension of the imported file' ; 323 COMMENT ON COLUMN depth_evolution.filename IS 'Name without type extension of the imported file' ;
324 COMMENT ON COLUMN depth_evolution.groupname IS 'Group the file belongs to' ; 324 COMMENT ON COLUMN depth_evolution.groupname IS 'Group the file belongs to' ;
325 COMMENT ON COLUMN depth_evolution."comment" IS 'File header line info "weitere Bemerkungen"' ; 325 COMMENT ON COLUMN depth_evolution.notes IS 'File header line info "weitere Bemerkungen"' ;
326 326
327 CREATE SEQUENCE DEPTH_EVOLUTION_ID_SEQ ; 327 CREATE SEQUENCE DEPTH_EVOLUTION_ID_SEQ ;
328 328
329 329
330 CREATE TABLE depth_evolution_values ( 330 CREATE TABLE depth_evolution_values (
376 id NUMERIC(9,0) PRIMARY KEY, 376 id NUMERIC(9,0) PRIMARY KEY,
377 river_id integer NOT NULL CONSTRAINT cSalixRivers REFERENCES rivers(id) ON DELETE CASCADE, 377 river_id integer NOT NULL CONSTRAINT cSalixRivers REFERENCES rivers(id) ON DELETE CASCADE,
378 evaluation_by VARCHAR(255), 378 evaluation_by VARCHAR(255),
379 kmrange_info VARCHAR(32), 379 kmrange_info VARCHAR(32),
380 filename VARCHAR(256) NOT NULL, 380 filename VARCHAR(256) NOT NULL,
381 "comment" VARCHAR(256) 381 notes VARCHAR(256)
382 ); 382 );
383 383
384 COMMENT ON TABLE salix IS 'Longitudinal section of the salix line parameters of a river' ; 384 COMMENT ON TABLE salix IS 'Longitudinal section of the salix line parameters of a river' ;
385 COMMENT ON COLUMN salix.evaluation_by IS 'File header line info "Auswerter"' ; 385 COMMENT ON COLUMN salix.evaluation_by IS 'File header line info "Auswerter"' ;
386 COMMENT ON COLUMN salix.kmrange_info IS 'File header line info "Strecke"' ; 386 COMMENT ON COLUMN salix.kmrange_info IS 'File header line info "Strecke"' ;
387 COMMENT ON COLUMN salix.filename IS 'Name without type extension of the imported file' ; 387 COMMENT ON COLUMN salix.filename IS 'Name without type extension of the imported file' ;
388 COMMENT ON COLUMN salix."comment" IS 'File header line info "weitere Bemerkungen"' ; 388 COMMENT ON COLUMN salix.notes IS 'File header line info "weitere Bemerkungen"' ;
389 389
390 CREATE SEQUENCE SALIX_ID_SEQ ; 390 CREATE SEQUENCE SALIX_ID_SEQ ;
391 391
392 392
393 CREATE TABLE salix_values ( 393 CREATE TABLE salix_values (
439 CREATE TABLE vegetation ( 439 CREATE TABLE vegetation (
440 id NUMERIC(9,0) PRIMARY KEY, 440 id NUMERIC(9,0) PRIMARY KEY,
441 river_id integer NOT NULL CONSTRAINT cVegetationRivers REFERENCES rivers(id) ON DELETE CASCADE, 441 river_id integer NOT NULL CONSTRAINT cVegetationRivers REFERENCES rivers(id) ON DELETE CASCADE,
442 name VARCHAR(256), 442 name VARCHAR(256),
443 filename VARCHAR(256) NOT NULL, 443 filename VARCHAR(256) NOT NULL,
444 "comment" VARCHAR(256) 444 notes VARCHAR(256)
445 ); 445 );
446 446
447 COMMENT ON TABLE vegetation IS 'Classification of the vegetation of a river' ; 447 COMMENT ON TABLE vegetation IS 'Classification of the vegetation of a river' ;
448 COMMENT ON COLUMN vegetation.name IS 'File header line info "Einteilung"' ; 448 COMMENT ON COLUMN vegetation.name IS 'File header line info "Einteilung"' ;
449 COMMENT ON COLUMN vegetation.filename IS 'Name without type extension of the imported file' ; 449 COMMENT ON COLUMN vegetation.filename IS 'Name without type extension of the imported file' ;
450 COMMENT ON COLUMN vegetation."comment" IS 'File header line info "weitere Bemerkungen"' ; 450 COMMENT ON COLUMN vegetation.notes IS 'File header line info "weitere Bemerkungen"' ;
451 451
452 CREATE SEQUENCE VEGETATION_ID_SEQ ; 452 CREATE SEQUENCE VEGETATION_ID_SEQ ;
453 453
454 454
455 CREATE TABLE vegetation_zone ( 455 CREATE TABLE vegetation_zone (
http://dive4elements.wald.intevation.org