Mercurial > dive4elements > river

comparison backend/doc/schema/postgresql-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
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)' ; 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)' ;
8 8
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 ADD COLUMN sounding_width_info VARCHAR(256); 12 ALTER TABLE bed_height
13 ADD sounding_width_info VARCHAR(256),
14 ADD "comment" VARCHAR(256);
13 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"' ;
14
15 ALTER TABLE bed_height ADD COLUMN "comment" VARCHAR(256);
16 COMMENT ON COLUMN bed_height."comment" IS 'File header line info "weitere Bemerkungen"' ; 16 COMMENT ON COLUMN bed_height."comment" 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 ADD COLUMN min_height NUMERIC; 21 ALTER TABLE bed_height_values
22 ADD min_height NUMERIC,
23 ADD max_height NUMERIC,
24 ADD height01 NUMERIC,
25 ADD height02 NUMERIC,
26 ADD height03 NUMERIC,
27 ADD height04 NUMERIC,
28 ADD height05 NUMERIC,
29 ADD height06 NUMERIC,
30 ADD height07 NUMERIC,
31 ADD height08 NUMERIC,
32 ADD height09 NUMERIC,
33 ADD height10 NUMERIC;
22 COMMENT ON COLUMN bed_height_values.min_height IS 'Minimum bed height (Talweg) in m' ; 34 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 NUMERIC;
25 COMMENT ON COLUMN bed_height_values.max_height IS 'Maximum bed height (Kammlage) in m' ; 35 COMMENT ON COLUMN bed_height_values.max_height IS 'Maximum bed height (Kammlage) in m' ;
36 COMMENT ON COLUMN bed_height_values.height01 IS 'Bed height of section 1 in m' ;
37 COMMENT ON COLUMN bed_height_values.height02 IS 'Bed height of section 2 in m' ;
38 COMMENT ON COLUMN bed_height_values.height03 IS 'Bed height of section 3 in m' ;
39 COMMENT ON COLUMN bed_height_values.height04 IS 'Bed height of section 4 in m' ;
40 COMMENT ON COLUMN bed_height_values.height05 IS 'Bed height of section 5 in m' ;
41 COMMENT ON COLUMN bed_height_values.height06 IS 'Bed height of section 6 in m' ;
42 COMMENT ON COLUMN bed_height_values.height07 IS 'Bed height of section 7 in m' ;
43 COMMENT ON COLUMN bed_height_values.height08 IS 'Bed height of section 8 in m' ;
44 COMMENT ON COLUMN bed_height_values.height09 IS 'Bed height of section 9 in m' ;
45 COMMENT ON COLUMN bed_height_values.height10 IS 'Bed height of section 10 in m' ;
26 46
27 47
28 --Bed mobility 48 --Bed mobility
29 49
30 CREATE TABLE bed_mobility ( 50 CREATE TABLE bed_mobility (
183 id NUMERIC(9,0) PRIMARY KEY, 203 id NUMERIC(9,0) PRIMARY KEY,
184 river_id integer NOT NULL CONSTRAINT cTkhRivers REFERENCES rivers(id) ON DELETE CASCADE, 204 river_id integer NOT NULL CONSTRAINT cTkhRivers REFERENCES rivers(id) ON DELETE CASCADE,
185 day DATE, 205 day DATE,
186 kmrange_info VARCHAR(32), 206 kmrange_info VARCHAR(32),
187 filename VARCHAR(256) NOT NULL, 207 filename VARCHAR(256) NOT NULL,
208 groupname VARCHAR(32) NOT NULL CHECK(groupname IN ('messung','berechnung')),
188 sounding_info VARCHAR(64), 209 sounding_info VARCHAR(64),
189 evaluation_by VARCHAR(256), 210 evaluation_by VARCHAR(256),
190 "comment" VARCHAR(256) 211 "comment" VARCHAR(256)
191 ); 212 );
192 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' ;
193 COMMENT ON COLUMN tkh.day IS 'File header line info "Datum"' ; 214 COMMENT ON COLUMN tkh.day IS 'File header line info "Datum"' ;
194 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"' ;
195 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' ;
196 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"' ;
197 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"' ;
198 COMMENT ON COLUMN tkh."comment" IS 'File header line info "weitere Bemerkungen"' ; 220 COMMENT ON COLUMN tkh."comment" IS 'File header line info "weitere Bemerkungen"' ;
199 221
200 CREATE SEQUENCE TKH_ID_SEQ ; 222 CREATE SEQUENCE TKH_ID_SEQ ;
285 old_sounding VARCHAR(64) NOT NULL, 307 old_sounding VARCHAR(64) NOT NULL,
286 kmrange_info VARCHAR(32), 308 kmrange_info VARCHAR(32),
287 curr_glw VARCHAR(64) NOT NULL, 309 curr_glw VARCHAR(64) NOT NULL,
288 old_glw VARCHAR(64) NOT NULL, 310 old_glw VARCHAR(64) NOT NULL,
289 filename VARCHAR(256) NOT NULL, 311 filename VARCHAR(256) NOT NULL,
312 groupname VARCHAR(32) NOT NULL CHECK(groupname IN ('aktuell','etappe')),
290 "comment" VARCHAR(256) 313 "comment" VARCHAR(256)
291 ); 314 );
292 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' ;
293 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)' ;
294 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)' ;
296 COMMENT ON COLUMN depth_evolution.old_sounding IS 'File header line info "Historische Peilung / Epoche"' ; 319 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"' ; 320 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"' ; 321 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"' ; 322 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' ; 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' ;
301 COMMENT ON COLUMN depth_evolution."comment" IS 'File header line info "weitere Bemerkungen"' ; 325 COMMENT ON COLUMN depth_evolution."comment" IS 'File header line info "weitere Bemerkungen"' ;
302 326
303 CREATE SEQUENCE DEPTH_EVOLUTION_ID_SEQ ; 327 CREATE SEQUENCE DEPTH_EVOLUTION_ID_SEQ ;
304 328
305 329
http://dive4elements.wald.intevation.org