Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle.sql @ 3815:ecab7e7804a9 pre2.6-2012-01-04

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