Mercurial > dive4elements > river

comparison backend/doc/schema/oracle.sql @ 5838:5aa05a7a34b7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Rename modules to more fitting names.
author Sascha L. Teichmann <teichmann@intevation.de>
date Thu, 25 Apr 2013 15:23:37 +0200
parents flys-backend/doc/schema/oracle.sql@153456f84602
children 4f35b34f4efa
comparison
equal deleted inserted replaced
5837:d9901a08d0a6 5838:5aa05a7a34b7
1 -- ANNOTATION_TYPES
2 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;
3
4 CREATE TABLE annotation_types (
5 id NUMBER(38,0) NOT NULL,
6 name VARCHAR2(255) NOT NULL UNIQUE,
7 PRIMARY KEY (id)
8 );
9
10
11 -- ANNOTATIONS
12 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
13
14 CREATE TABLE annotations (
15 id NUMBER(38,0) NOT NULL,
16 attribute_id NUMBER(38,0) NOT NULL,
17 edge_id NUMBER(38,0),
18 position_id NUMBER(38,0),
19 range_id NUMBER(38,0),
20 type_id NUMBER(38,0),
21 PRIMARY KEY (id)
22 );
23
24
25 -- ATTRIBUTES
26 CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
27
28 CREATE TABLE attributes (
29 id NUMBER(38,0) NOT NULL,
30 value VARCHAR2(255) NOT NULL UNIQUE,
31 primary key (id)
32 );
33
34
35 -- CROSS_SECTION_LINES
36 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ;
37
38 CREATE TABLE cross_section_lines (
39 id NUMBER(38,0) NOT NULL,
40 km NUMBER(38,2),
41 cross_section_id NUMBER(38,0),
42 PRIMARY KEY (id)
43 );
44
45
46 -- CROSS_SECTION_POINTS
47 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
48
49 CREATE TABLE cross_section_points (
50 id NUMBER(38,0) NOT NULL,
51 col_pos NUMBER(38,0),
52 x NUMBER(38,2),
53 y NUMBER(38,2),
54 cross_section_line_id NUMBER(38,0),
55 PRIMARY KEY (id)
56 );
57
58
59 -- CROSS_SECTIONS
60 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
61
62 CREATE TABLE cross_sections (
63 id NUMBER(38,0) NOT NULL,
64 description VARCHAR2(255),
65 river_id NUMBER(38,0),
66 time_interval_id NUMBER(38,0),
67 PRIMARY KEY (id)
68 );
69
70 -- Indices for faster access of the points
71 CREATE INDEX cross_section_lines_km_idx
72 ON cross_section_lines(km);
73 CREATE INDEX cross_section_points_line_idx
74 ON cross_section_points(cross_section_line_id);
75
76 -- DISCHARGE_TABLE_VALUES
77 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
78
79 CREATE TABLE discharge_table_values (
80 id NUMBER(38,0) NOT NULL,
81 q NUMBER(38,2) NOT NULL,
82 w NUMBER(38,2) NOT NULL,
83 table_id NUMBER(38,0) NOT NULL,
84 UNIQUE (table_id, q, w),
85 PRIMARY KEY (id)
86 );
87
88
89 -- DISCHARGE_TABLES
90 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
91
92 CREATE TABLE discharge_tables (
93 id NUMBER(38,0) NOT NULL,
94 description VARCHAR2(255) NOT NULL,
95 bfg_id VARCHAR2(50),
96 kind NUMBER(38,0) NOT NULL DEFAULT 0,
97 gauge_id NUMBER(38,0) NOT NULL,
98 time_interval_id NUMBER(38,0),
99 PRIMARY KEY (id)
100 );
101
102
103 -- EDGES
104 CREATE SEQUENCE EDGES_ID_SEQ;
105
106 CREATE TABLE edges (
107 id NUMBER(38,0) NOT NULL,
108 bottom NUMBER(38,2),
109 top NUMBER(38,2),
110 PRIMARY KEY (id)
111 );
112
113
114 -- GAUGES
115 CREATE SEQUENCE GAUGES_ID_SEQ;
116
117 CREATE TABLE gauges (
118 id NUMBER(38,0) NOT NULL,
119 aeo NUMBER(38,2) NOT NULL,
120 datum NUMBER(38,2) NOT NULL,
121 name VARCHAR2(255) NOT NULL,
122 station NUMBER(38,2) NOT NULL,
123 official_number NUMBER(38,0) UNIQUE,
124 range_id NUMBER(38,0) NOT NULL,
125 -- remove river id here because range_id references river already
126 river_id NUMBER(38,0) NOT NULL,
127 PRIMARY KEY (id),
128 UNIQUE (name, river_id),
129 UNIQUE (river_id, station)
130 );
131
132
133 -- HYK_ENTRIES
134 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
135
136 CREATE TABLE hyk_entries (
137 id NUMBER(38,0) NOT NULL,
138 km NUMBER(38,2),
139 measure TIMESTAMP,
140 hyk_id NUMBER(38,0),
141 PRIMARY KEY (id)
142 );
143
144
145 -- HYK_FLOW_ZONE_TYPES
146 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;
147
148 CREATE TABLE hyk_flow_zone_types (
149 id NUMBER(38,0) NOT NULL,
150 description VARCHAR2(255),
151 name VARCHAR2(255),
152 PRIMARY KEY (id)
153 );
154
155
156 -- HYK_FLOW_ZONES
157 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ;
158
159 CREATE TABLE hyk_flow_zones (
160 id NUMBER(38,0) NOT NULL,
161 a NUMBER(38,2),
162 b NUMBER(38,2),
163 formation_id NUMBER(38,0),
164 type_id NUMBER(38,0),
165 primary key (id)
166 );
167
168
169 -- HYK_FORMATIONS
170 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
171
172 CREATE TABLE hyk_formations (
173 id NUMBER(38,0) NOT NULL,
174 bottom NUMBER(38,2),
175 distance_hf NUMBER(38,2),
176 distance_vl NUMBER(38,2),
177 distance_vr NUMBER(38,2),
178 formation_num NUMBER(38,0),
179 top NUMBER(38,2),
180 hyk_entry_id NUMBER(38,0),
181 PRIMARY KEY (id)
182 );
183
184
185 -- HYKS
186 CREATE SEQUENCE HYKS_ID_SEQ;
187
188 CREATE TABLE hyks (
189 id NUMBER(38,0) NOT NULL,
190 description VARCHAR2(255),
191 river_id NUMBER(38,0),
192 primary key (id)
193 );
194
195
196 -- MAIN_VALUE_TYPES
197 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
198
199 CREATE TABLE main_value_types (
200 id NUMBER(38,0) NOT NULL,
201 name VARCHAR2(255) NOT NULL UNIQUE,
202 PRIMARY KEY (id)
203 );
204
205
206 -- MAIN_VALUES
207 CREATE SEQUENCE MAIN_VALUES_ID_SEQ;
208
209 CREATE TABLE main_values (
210 id NUMBER(38,0) NOT NULL,
211 value NUMBER(38,2) NOT NULL,
212 gauge_id NUMBER(38,0) NOT NULL,
213 named_value_id NUMBER(38,0) NOT NULL,
214 time_interval_id NUMBER(38,0),
215
216 -- TODO: better checks
217 UNIQUE (gauge_id, named_value_id, time_interval_id),
218 PRIMARY KEY (id)
219 );
220
221
222 -- NAMED_MAIN_VALUES
223 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;
224
225 CREATE TABLE named_main_values (
226 id NUMBER(38,0) NOT NULL,
227 name VARCHAR2(256) NOT NULL UNIQUE,
228 type_id NUMBER(38,0) NOT NULL,
229 PRIMARY KEY (id)
230 );
231
232
233 -- POSITIONS
234 CREATE SEQUENCE POSITIONS_ID_SEQ;
235
236 CREATE TABLE positions (
237 id NUMBER(38,0) NOT NULL,
238 value VARCHAR2(255 char) NOT NULL UNIQUE,
239 PRIMARY KEY (id)
240 );
241
242
243 --- RANGES
244 CREATE SEQUENCE RANGES_ID_SEQ;
245
246 CREATE TABLE ranges (
247 id NUMBER(38,0) NOT NULL,
248 a NUMBER(38,10) NOT NULL,
249 b NUMBER(38,10),
250 river_id NUMBER(38,0),
251 UNIQUE (river_id, a, b),
252 PRIMARY KEY (id)
253 );
254
255
256
257 -- RIVERS
258 CREATE SEQUENCE RIVERS_ID_SEQ;
259
260 CREATE TABLE rivers (
261 id NUMBER(38,0) NOT NULL,
262 official_number NUMBER(38,0) UNIQUE,
263 km_up NUMBER(38,0) NOT NULL DEFAULT 0,
264 name VARCHAR2(255) NOT NULL UNIQUE,
265 wst_unit_id NUMBER(38,0) NOT NULL,
266 PRIMARY KEY (id)
267 );
268
269
270 -- TIME_INTERVALS
271 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
272
273 CREATE TABLE time_intervals (
274 id NUMBER(38,0) NOT NULL,
275 start_time TIMESTAMP NOT NULL,
276 stop_time TIMESTAMP,
277 PRIMARY KEY (id),
278 CHECK (start_time <= stop_time)
279 );
280
281
282 --- UNITS
283 CREATE SEQUENCE UNITS_ID_SEQ;
284
285 CREATE TABLE units (
286 id NUMBER(38,0) NOT NULL,
287 name VARCHAR2(255) NOT NULL UNIQUE,
288 PRIMARY KEY (id)
289 );
290
291
292 -- WST_COLUMN_Q_RANGES
293 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
294
295 CREATE TABLE wst_column_q_ranges (
296 id NUMBER(38,0) NOT NULL,
297 wst_column_id NUMBER(38,0) NOT NULL,
298 wst_q_range_id NUMBER(38,0) NOT NULL,
299 UNIQUE (wst_column_id, wst_q_range_id),
300 PRIMARY KEY (id)
301 );
302
303
304 -- WST_COLUMN_VALUES
305 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
306
307 CREATE TABLE wst_column_values (
308 id NUMBER(38,0) NOT NULL,
309 position NUMBER(38,5) NOT NULL,
310 w NUMBER(38,5) NOT NULL,
311 wst_column_id NUMBER(38,0) NOT NULL,
312 UNIQUE (position, wst_column_id),
313 UNIQUE (position, wst_column_id, w),
314 PRIMARY KEY (id)
315 );
316
317
318 -- WST_COLUMNS
319 CREATE SEQUENCE WST_COLUMNS_ID_SEQ;
320
321 CREATE TABLE wst_columns (
322 id NUMBER(38,0) NOT NULL,
323 description VARCHAR2(255),
324 name VARCHAR2(255) NOT NULL,
325 position NUMBER(38,0) NOT NULL DEFAULT 0,
326 time_interval_id NUMBER(38,0),
327 wst_id NUMBER(38,0) NOT NULL,
328 UNIQUE (wst_id, name),
329 UNIQUE (wst_id, position),
330 PRIMARY KEY (id)
331 );
332
333
334 -- WST_Q_RANGES
335 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
336
337 CREATE TABLE wst_q_ranges (
338 id NUMBER(38,0) NOT NULL,
339 q NUMBER(38,5) NOT NULL,
340 range_id NUMBER(38,0) NOT NULL,
341 PRIMARY KEY (id)
342 );
343
344
345 -- WSTS
346 --lookup table for wst kinds
347 CREATE TABLE wst_kinds (
348 id NUMBER PRIMARY KEY NOT NULL,
349 kind VARCHAR(64) NOT NULL
350 );
351 INSERT INTO wst_kinds (id, kind) VALUES (0, 'basedata');
352 INSERT INTO wst_kinds (id, kind) VALUES (1, 'basedata_additionals_marks');
353 INSERT INTO wst_kinds (id, kind) VALUES (2, 'basedata_fixations_wst');
354 INSERT INTO wst_kinds (id, kind) VALUES (3, 'basedata_officials');
355 INSERT INTO wst_kinds (id, kind) VALUES (4, 'basedata_heightmarks-points-relative_points');
356 INSERT INTO wst_kinds (id, kind) VALUES (5, 'basedata_flood-protections_relative_points');
357 INSERT INTO wst_kinds (id, kind) VALUES (6, 'morpho_waterlevel-differences');
358 INSERT INTO wst_kinds (id, kind) VALUES (7, 'morpho_waterlevels');
359
360
361 CREATE SEQUENCE WSTS_ID_SEQ;
362
363 CREATE TABLE wsts (
364 id NUMBER(38,0) NOT NULL,
365 description VARCHAR2(255) NOT NULL,
366 kind NUMBER(38,0) NOT NULL,
367 river_id NUMBER(38,0) NOT NULL,
368 UNIQUE (river_id, description),
369 PRIMARY KEY (id)
370 );
371
372
373 -- ADD CONSTRAINTs
374 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes;
375 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsEdges FOREIGN KEY (edge_id) REFERENCES edges;
376 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsPositions FOREIGN KEY (position_id) REFERENCES positions;
377 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsTypes FOREIGN KEY (type_id) REFERENCES annotation_types;
378 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
379 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
380 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFlowZoneTypes FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types;
381 ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values;
382 ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
383 ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types;
384 ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units;
385 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
386
387 -- Cascading references
388 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsRanges FOREIGN KEY (range_id) REFERENCES ranges ON DELETE CASCADE;
389 ALTER TABLE cross_section_lines ADD CONSTRAINT cQPSLinesCrossSections FOREIGN KEY (cross_section_id) REFERENCES cross_sections ON DELETE CASCADE;
390 ALTER TABLE cross_section_points ADD CONSTRAINT cQPSPointsCrossSectionLines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines ON DELETE CASCADE;
391 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
392 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesGauges FOREIGN KEY (gauge_id) REFERENCES gauges ON DELETE CASCADE;
393 ALTER TABLE discharge_table_values ADD CONSTRAINT cTableValuesDischargeTables FOREIGN KEY (table_id) REFERENCES discharge_tables ON DELETE CASCADE;
394 ALTER TABLE gauges ADD CONSTRAINT cGaugesRanges FOREIGN KEY (range_id) REFERENCES ranges ON DELETE CASCADE;
395 ALTER TABLE gauges ADD CONSTRAINT cGaugesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
396 ALTER TABLE hyk_entries ADD CONSTRAINT cHykEntriesHyks FOREIGN KEY (hyk_id) REFERENCES hyks ON DELETE CASCADE;
397 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFormations FOREIGN KEY (formation_id) REFERENCES hyk_formations ON DELETE CASCADE;
398 ALTER TABLE hyk_formations ADD CONSTRAINT cHykFormationsHykEntries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries ON DELETE CASCADE;
399 ALTER TABLE hyks ADD CONSTRAINT cHyksRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
400 ALTER TABLE main_values ADD CONSTRAINT cMainValuesGauges FOREIGN KEY (gauge_id) REFERENCES gauges ON DELETE CASCADE;
401 ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
402 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
403 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges ON DELETE CASCADE;
404 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts ON DELETE CASCADE;
405 ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
406 ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES ON DELETE CASCADE;
407 ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
408 ALTER TABLE wsts ADD CONSTRAINT cWstsWstKinds FOREIGN KEY (kind) REFERENCES wst_kinds;
409
410 -- VIEWS
411
412 CREATE VIEW wst_value_table AS
413 SELECT wcv.position AS position,
414 w,
415 (SELECT q
416 FROM wst_column_q_ranges wcqr
417 JOIN wst_q_ranges wqr
418 ON wcqr.wst_q_range_id = wqr.id
419 JOIN ranges r
420 ON r.id = wqr.range_id
421 WHERE wcqr.wst_column_id = wc.id
422 AND wcv.position BETWEEN r.a AND r.b) AS q,
423 wc.position AS column_pos,
424 w.id AS wst_id
425 FROM wst_column_values wcv
426 JOIN wst_columns wc
427 ON wcv.wst_column_id = wc.id
428 JOIN wsts w
429 ON wc.wst_id = w.id
430 ORDER BY wcv.position ASC,
431 wc.position DESC;
432
433 -- view to select the w values of a WST
434 CREATE VIEW wst_w_values AS
435 SELECT wcv.position AS km,
436 wcv.w AS w,
437 wc.position AS column_pos,
438 w.id AS wst_id
439 FROM wst_column_values wcv
440 JOIN wst_columns wc ON wcv.wst_column_id = wc.id
441 JOIN wsts w ON wc.wst_id = w.id
442 ORDER BY wcv.position, wc.position;
443
444 -- view to select the q values of a WST
445 CREATE VIEW wst_q_values AS
446 SELECT wc.position AS column_pos,
447 wqr.q AS q,
448 r.a AS a,
449 r.b AS b,
450 wc.wst_id AS wst_id
451 FROM wst_column_q_ranges wcqr
452 JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id
453 JOIN ranges r ON wqr.range_id = r.id
454 JOIN wst_columns wc ON wcqr.wst_column_id = wc.id
455 ORDER BY wc.position, wcqr.wst_column_id, r.a;
456
457 -- Views to make the 'Amtlichen Linien' easier to access.
458
459 CREATE VIEW official_lines
460 AS
461 SELECT w.river_id AS river_id,
462 w.id AS wst_id,
463 wc.id AS wst_column_id,
464 wc.name AS name,
465 wc.position AS wst_column_pos
466 FROM wsts w
467 JOIN wst_columns wc
468 ON wc.wst_id = w.id
469 WHERE w.kind = 3;
470
471 CREATE VIEW q_main_values
472 AS
473 SELECT riv.id AS river_id,
474 g.id AS gauge_id,
475 g.name AS gauge_name,
476 r.a AS a,
477 r.b AS b,
478 REGEXP_REPLACE(
479 nmv.name, '[:space:]*\(.*\)[:space:]*', '') AS name,
480 CAST(mv.value AS NUMERIC(38, 5)) AS value
481 FROM main_values mv
482 JOIN named_main_values nmv
483 ON mv.named_value_id = nmv.id
484 JOIN main_value_types mvt
485 ON nmv.type_id = mvt.id
486 JOIN gauges g
487 ON mv.gauge_id = g.id
488 JOIN ranges r
489 ON g.range_id = r.id
490 JOIN rivers riv
491 ON g.river_id = riv.id
492 WHERE mvt.name = 'Q'
493 ORDER BY g.id, CAST(mv.value AS NUMERIC(38,5));
494
495 CREATE VIEW official_q_values
496 AS
497 SELECT ol.river_id AS river_id,
498 wst_id,
499 wst_column_id,
500 gauge_id,
501 gauge_name,
502 a,
503 b,
504 ol.name,
505 value,
506 wst_column_pos
507 FROM official_lines ol
508 JOIN q_main_values qmv
509 ON ol.river_id = qmv.river_id
510 AND ol.name = qmv.name;
511
512 CREATE VIEW wst_ranges
513 AS
514 SELECT wc.id AS wst_column_id,
515 wc.wst_id AS wst_id,
516 Min(wcv.position) AS a,
517 Max(wcv.position) AS b
518 FROM wst_columns wc
519 JOIN wst_column_values wcv
520 ON wc.id = wcv.wst_column_id
521 GROUP BY wc.id,
522 wc.wst_id;
http://dive4elements.wald.intevation.org