Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle.sql @ 3962:d609fd83310a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged flys-backend
author Thomas Arendsen Hein <thomas@intevation.de>
date Fri, 28 Sep 2012 12:15:04 +0200 (2012-09-28)
parents 948c7289fc42
children 83a42e6a562d
comparison
equal deleted inserted replaced
3938:c0cab28ba1ea 3962:d609fd83310a
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),
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),
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),
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),
82 w NUMBER(38,2),
83 table_id NUMBER(38,0),
84 PRIMARY KEY (id)
85 );
86
87
88 -- DISCHARGE_TABLES
89 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
90
91 CREATE TABLE discharge_tables (
92 id NUMBER(38,0) NOT NULL,
93 description VARCHAR2(255),
94 kind NUMBER(38,0),
95 gauge_id NUMBER(38,0),
96 time_interval_id NUMBER(38,0),
97 PRIMARY KEY (id)
98 );
99
100
101 -- EDGES
102 CREATE SEQUENCE EDGES_ID_SEQ;
103
104 CREATE TABLE edges (
105 id NUMBER(38,0) NOT NULL,
106 bottom NUMBER(38,2),
107 top NUMBER(38,2),
108 PRIMARY KEY (id)
109 );
110
111
112 -- GAUGES
113 CREATE SEQUENCE GAUGES_ID_SEQ;
114
115 CREATE TABLE gauges (
116 id NUMBER(38,0) NOT NULL,
117 aeo NUMBER(38,2),
118 datum NUMBER(38,2),
119 name VARCHAR2(255),
120 station NUMBER(38,2),
121 official_number NUMBER(38,0),
122 range_id NUMBER(38,0),
123 river_id NUMBER(38,0),
124 PRIMARY KEY (id)
125 );
126
127
128 -- HYK_ENTRIES
129 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
130
131 CREATE TABLE hyk_entries (
132 id NUMBER(38,0) NOT NULL,
133 km NUMBER(38,2),
134 measure TIMESTAMP,
135 hyk_id NUMBER(38,0),
136 PRIMARY KEY (id)
137 );
138
139
140 -- HYK_FLOW_ZONE_TYPES
141 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;
142
143 CREATE TABLE hyk_flow_zone_types (
144 id NUMBER(38,0) NOT NULL,
145 description VARCHAR2(255),
146 name VARCHAR2(255),
147 PRIMARY KEY (id)
148 );
149
150
151 -- HYK_FLOW_ZONES
152 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ;
153
154 CREATE TABLE hyk_flow_zones (
155 id NUMBER(38,0) NOT NULL,
156 a NUMBER(38,2),
157 b NUMBER(38,2),
158 formation_id NUMBER(38,0),
159 type_id NUMBER(38,0),
160 primary key (id)
161 );
162
163
164 -- HYK_FORMATIONS
165 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
166
167 CREATE TABLE hyk_formations (
168 id NUMBER(38,0) NOT NULL,
169 bottom NUMBER(38,2),
170 distance_hf NUMBER(38,2),
171 distance_vl NUMBER(38,2),
172 distance_vr NUMBER(38,2),
173 formation_num NUMBER(38,0),
174 top NUMBER(38,2),
175 hyk_entry_id NUMBER(38,0),
176 PRIMARY KEY (id)
177 );
178
179
180 -- HYKS
181 CREATE SEQUENCE HYKS_ID_SEQ;
182
183 CREATE TABLE hyks (
184 id NUMBER(38,0) NOT NULL,
185 description VARCHAR2(255),
186 river_id NUMBER(38,0),
187 primary key (id)
188 );
189
190
191 -- MAIN_VALUE_TYPES
192 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
193
194 CREATE TABLE main_value_types (
195 id NUMBER(38,0) NOT NULL,
196 name VARCHAR2(255),
197 PRIMARY KEY (id)
198 );
199
200
201 -- MAIN_VALUES
202 CREATE SEQUENCE MAIN_VALUES_ID_SEQ;
203
204 CREATE TABLE main_values (
205 id NUMBER(38,0) NOT NULL,
206 value NUMBER(38,2),
207 gauge_id NUMBER(38,0),
208 named_value_id NUMBER(38,0),
209 time_interval_id NUMBER(38,0),
210 PRIMARY KEY (id)
211 );
212
213
214 -- NAMED_MAIN_VALUES
215 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;
216
217 CREATE TABLE named_main_values (
218 id NUMBER(38,0) NOT NULL,
219 name VARCHAR2(255),
220 type_id NUMBER(38,0),
221 PRIMARY KEY (id)
222 );
223
224
225 -- POSITIONS
226 CREATE SEQUENCE POSITIONS_ID_SEQ;
227
228 CREATE TABLE positions (
229 id NUMBER(38,0) NOT NULL,
230 value VARCHAR2(255 char),
231 PRIMARY KEY (id)
232 );
233
234
235 --- RANGES
236 CREATE SEQUENCE RANGES_ID_SEQ;
237
238 CREATE TABLE ranges (
239 id NUMBER(38,0) NOT NULL,
240 a NUMBER(38,10),
241 b NUMBER(38,10),
242 river_id NUMBER(38,0),
243 PRIMARY KEY (id)
244 );
245
246
247 -- RIVERS
248 CREATE SEQUENCE RIVERS_ID_SEQ;
249
250 CREATE TABLE rivers (
251 id NUMBER(38,0) NOT NULL,
252 official_number NUMBER(38,0),
253 km_up NUMBER(38,0),
254 name VARCHAR2(255),
255 wst_unit_id NUMBER(38,0),
256 PRIMARY KEY (id)
257 );
258
259
260 -- TIME_INTERVALS
261 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
262
263 CREATE TABLE time_intervals (
264 id NUMBER(38,0) NOT NULL,
265 start_time TIMESTAMP,
266 stop_time TIMESTAMP,
267 PRIMARY KEY (id)
268 );
269
270
271 --- UNITS
272 CREATE SEQUENCE UNITS_ID_SEQ;
273
274 CREATE TABLE units (
275 id NUMBER(38,0) NOT NULL,
276 name VARCHAR2(255),
277 PRIMARY KEY (id)
278 );
279
280
281 -- WST_COLUMN_Q_RANGES
282 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
283
284 CREATE TABLE wst_column_q_ranges (
285 id NUMBER(38,0) NOT NULL,
286 wst_column_id NUMBER(38,0),
287 wst_q_range_id NUMBER(38,0),
288 PRIMARY KEY (id)
289 );
290
291
292 -- WST_COLUMN_VALUES
293 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
294
295 CREATE TABLE wst_column_values (
296 id NUMBER(38,0) NOT NULL,
297 position NUMBER(38,5),
298 w NUMBER(38,5),
299 wst_column_id NUMBER(38,0),
300 PRIMARY KEY (id)
301 );
302
303
304 -- WST_COLUMNS
305 CREATE SEQUENCE WST_COLUMNS_ID_SEQ;
306
307 CREATE TABLE wst_columns (
308 id NUMBER(38,0) NOT NULL,
309 description VARCHAR2(255),
310 name VARCHAR2(255),
311 position NUMBER(38,0),
312 time_interval_id NUMBER(38,0),
313 wst_id NUMBER(38,0),
314 PRIMARY KEY (id)
315 );
316
317
318 -- WST_Q_RANGES
319 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
320
321 CREATE TABLE wst_q_ranges (
322 id NUMBER(38,0) NOT NULL,
323 q NUMBER(38,5),
324 range_id NUMBER(38,0),
325 PRIMARY KEY (id)
326 );
327
328
329 -- WSTS
330 CREATE SEQUENCE WSTS_ID_SEQ;
331
332 CREATE TABLE wsts (
333 id NUMBER(38,0) NOT NULL,
334 description VARCHAR2(255),
335 kind NUMBER(38,0),
336 river_id NUMBER(38,0),
337 PRIMARY KEY (id)
338 );
339
340
341 -- ADD CONSTRAINTs
342 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsRanges FOREIGN KEY (range_id) REFERENCES ranges;
343 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsEdges FOREIGN KEY (edge_id) REFERENCES edges;
344 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsPositions FOREIGN KEY (position_id) REFERENCES positions;
345 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes;
346 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsTypes FOREIGN KEY (type_id) REFERENCES annotation_types;
347 ALTER TABLE cross_section_lines ADD CONSTRAINT cQPSLinesCrossSections FOREIGN KEY (cross_section_id) REFERENCES cross_sections;
348 ALTER TABLE cross_section_points ADD CONSTRAINT cQPSPointsCrossSectionLines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines;
349 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsRivers FOREIGN KEY (river_id) REFERENCES rivers;
350 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
351 ALTER TABLE discharge_table_values ADD CONSTRAINT cTableValuesDischargeTables foreign key (table_id) REFERENCES discharge_tables;
352 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
353 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesGauges FOREIGN KEY (gauge_id) REFERENCES gauges;
354 ALTER TABLE gauges ADD CONSTRAINT cGaugesRivers FOREIGN KEY (river_id) REFERENCES rivers;
355 ALTER TABLE gauges ADD CONSTRAINT cGaugesRanges FOREIGN KEY (range_id) REFERENCES ranges;
356 ALTER TABLE hyk_entries ADD CONSTRAINT cHykEntriesHyks FOREIGN KEY (hyk_id) REFERENCES hyks;
357 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFormations FOREIGN KEY (formation_id) REFERENCES hyk_formations;
358 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFlowZoneTypes FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types;
359 ALTER TABLE hyks ADD CONSTRAINT cHyksRivers FOREIGN KEY (river_id) REFERENCES rivers;
360 ALTER TABLE hyk_formations ADD CONSTRAINT cHykFormationsHykEntries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries;
361 ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
362 ALTER TABLE main_values ADD CONSTRAINT cMainValuesGauges FOREIGN KEY (gauge_id) REFERENCES gauges;
363 ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values;
364 ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types;
365 ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers;
366 ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units;
367 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
368 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges;
369 ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
370 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
371 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts;
372 ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES;
373 ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers;
374
375 -- VIEWS
376
377 CREATE VIEW wst_value_table AS
378 SELECT wcv.position AS position,
379 w,
380 (SELECT q
381 FROM wst_column_q_ranges wcqr
382 JOIN wst_q_ranges wqr
383 ON wcqr.wst_q_range_id = wqr.id
384 JOIN ranges r
385 ON r.id = wqr.range_id
386 WHERE wcqr.wst_column_id = wc.id
387 AND wcv.position BETWEEN r.a AND r.b) AS q,
388 wc.position AS column_pos,
389 w.id AS wst_id
390 FROM wst_column_values wcv
391 JOIN wst_columns wc
392 ON wcv.wst_column_id = wc.id
393 JOIN wsts w
394 ON wc.wst_id = w.id
395 ORDER BY wcv.position ASC,
396 wc.position DESC;
397
398 -- view to select the w values of a WST
399 CREATE VIEW wst_w_values AS
400 SELECT wcv.position AS km,
401 wcv.w AS w,
402 wc.position AS column_pos,
403 w.id AS wst_id
404 FROM wst_column_values wcv
405 JOIN wst_columns wc ON wcv.wst_column_id = wc.id
406 JOIN wsts w ON wc.wst_id = w.id
407 ORDER BY wcv.position, wc.position;
408
409 -- view to select the q values of a WST
410 CREATE VIEW wst_q_values AS
411 SELECT wc.position AS column_pos,
412 wqr.q AS q,
413 r.a AS a,
414 r.b AS b,
415 wc.wst_id AS wst_id
416 FROM wst_column_q_ranges wcqr
417 JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id
418 JOIN ranges r ON wqr.range_id = r.id
419 JOIN wst_columns wc ON wcqr.wst_column_id = wc.id
420 ORDER BY wc.position, wcqr.wst_column_id, r.a;
421
422 -- Views to make the 'Amtlichen Linien' easier to access.
423
424 CREATE VIEW official_lines
425 AS
426 SELECT w.river_id AS river_id,
427 w.id AS wst_id,
428 wc.id AS wst_column_id,
429 wc.name AS name,
430 wc.position AS wst_column_pos
431 FROM wsts w
432 JOIN wst_columns wc
433 ON wc.wst_id = w.id
434 WHERE w.kind = 3;
435
436 CREATE VIEW q_main_values
437 AS
438 SELECT riv.id AS river_id,
439 g.id AS gauge_id,
440 g.name AS gauge_name,
441 r.a AS a,
442 r.b AS b,
443 REGEXP_REPLACE(
444 nmv.name, '[:space:]*\(.*\)[:space:]*', '') AS name,
445 CAST(mv.value AS NUMERIC(38, 5)) AS value
446 FROM main_values mv
447 JOIN named_main_values nmv
448 ON mv.named_value_id = nmv.id
449 JOIN main_value_types mvt
450 ON nmv.type_id = mvt.id
451 JOIN gauges g
452 ON mv.gauge_id = g.id
453 JOIN ranges r
454 ON g.range_id = r.id
455 JOIN rivers riv
456 ON g.river_id = riv.id
457 WHERE mvt.name = 'Q'
458 ORDER BY g.id, CAST(mv.value AS NUMERIC(38,5));
459
460 CREATE VIEW official_q_values
461 AS
462 SELECT ol.river_id AS river_id,
463 wst_id,
464 wst_column_id,
465 gauge_id,
466 gauge_name,
467 a,
468 b,
469 ol.name,
470 value,
471 wst_column_pos
472 FROM official_lines ol
473 JOIN q_main_values qmv
474 ON ol.river_id = qmv.river_id
475 AND ol.name = qmv.name;
http://dive4elements.wald.intevation.org