Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-minfo.sql @ 5224:2594904640c3

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
SCHEMA CHANGE: analogue r5201. Here for Oracle
author Tom Gottfried <tom@intevation.de>
date Mon, 11 Mar 2013 18:17:12 +0100
parents 8667f629d238
children db196abd16ca
comparison
equal deleted inserted replaced
5223:54f2a56752a1 5224:2594904640c3
18 unit_id NUMBER(38,0) NOT NULL, 18 unit_id NUMBER(38,0) NOT NULL,
19 PRIMARY KEY(id), 19 PRIMARY KEY(id),
20 CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id) 20 CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id)
21 ); 21 );
22 22
23 CREATE SEQUENCE BED_HEIGHT_TYPE_SEQ; 23
24 24 -- lookup table for bedheight types
25 CREATE TABLE bed_height_type ( 25 CREATE TABLE bed_height_type (
26 id NUMBER(38,0) NOT NULL, 26 id NUMBER(38,0) NOT NULL,
27 name VARCHAR(16) NOT NULL, 27 type VARCHAR(65) NOT NULL,
28 description VARCHAR(255),
29 PRIMARY KEY(id) 28 PRIMARY KEY(id)
30 ); 29 );
31 30 INSERT INTO bed_height_type VALUES (1, 'Querprofil')
31 INSERT INTO bed_height_type VALUES (2, 'Flächenpeilung')
32 INSERT INTO bed_height_type VALUES (3, 'Flächen- u. Querprofilpeilungen')
33 INSERT INTO bed_height_type VALUES (4, 'DGM')
32 34
33 35
34 CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ; 36 CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;
35 37
36 CREATE TABLE bed_height_single ( 38 CREATE TABLE bed_height_single (
276 sediment_yield_id NUMBER(38,0) NOT NULL, 278 sediment_yield_id NUMBER(38,0) NOT NULL,
277 station NUMBER(38,3) NOT NULL, 279 station NUMBER(38,3) NOT NULL,
278 value NUMBER(38,3) NOT NULL, 280 value NUMBER(38,3) NOT NULL,
279 PRIMARY KEY (id), 281 PRIMARY KEY (id),
280 CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id) 282 CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id)
281 );
282
283
284 CREATE SEQUENCE WATERLEVEL_ID_SEQ;
285
286 CREATE TABLE waterlevel (
287 id NUMBER(38,0) NOT NULL,
288 river_id NUMBER(38,0) NOT NULL,
289 unit_id NUMBER(38,0) NOT NULL,
290 description VARCHAR(256),
291 PRIMARY KEY (id),
292 CONSTRAINT fk_w_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
293 CONSTRAINT fk_w_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
294 );
295
296
297 CREATE SEQUENCE WATERLEVEL_Q_RANGES_ID_SEQ;
298
299 CREATE TABLE waterlevel_q_range (
300 id NUMBER(38,0) NOT NULL,
301 waterlevel_id NUMBER(38,0) NOT NULL,
302 q NUMBER(38,2) NOT NULL,
303 PRIMARY KEY (id),
304 CONSTRAINT fk_wqr_waterlevel_id FOREIGN KEY (waterlevel_id) REFERENCES waterlevel(id)
305 );
306
307
308 CREATE SEQUENCE WATERLEVEL_VALUES_ID_SEQ;
309
310 CREATE TABLE waterlevel_values (
311 id NUMBER(38,0) NOT NULL,
312 waterlevel_q_range_id NUMBER(38,0) NOT NULL,
313 station NUMBER(38,3) NOT NULL,
314 w NUMBER(38,2) NOT NULL,
315 PRIMARY KEY (id),
316 CONSTRAINT fk_wv_waterlevel_q_range_id FOREIGN KEY (waterlevel_q_range_id) REFERENCES waterlevel_q_range(id)
317 );
318
319
320 CREATE SEQUENCE WATERLEVEL_DIFFERENCE_ID_SEQ;
321
322 CREATE TABLE waterlevel_difference (
323 id NUMBER(38,0) NOT NULL,
324 river_id NUMBER(38,0) NOT NULL,
325 unit_id NUMBER(38,0) NOT NULL,
326 description VARCHAR(256),
327 PRIMARY KEY (id),
328 CONSTRAINT fk_wd_river_id FOREIGN KEY (river_id) REFERENCES rivers (id),
329 CONSTRAINT fk_wd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
330 );
331
332
333 CREATE SEQUENCE WATERLEVEL_DIFF_COLUMN_ID_SEQ;
334
335 CREATE TABLE waterlevel_difference_column (
336 id NUMBER(38,0) NOT NULL,
337 difference_id NUMBER(38,0) NOT NULL,
338 description VARCHAR(256),
339 PRIMARY KEY (id),
340 CONSTRAINT fk_wdc_difference_id FOREIGN KEY (difference_id) REFERENCES waterlevel_difference (id)
341 );
342
343
344 CREATE SEQUENCE WATERLEVEL_DIFF_VALUES_ID_SEQ;
345
346 CREATE TABLE waterlevel_difference_values (
347 id NUMBER(38,0) NOT NULL,
348 column_id NUMBER(38,0) NOT NULL,
349 station NUMBER(38,3) NOT NULL,
350 value NUMBER(38,2) NOT NULL,
351 PRIMARY KEY (id),
352 CONSTRAINT fk_wdv_column_id FOREIGN KEY (column_id) REFERENCES waterlevel_difference_column (id)
353 ); 283 );
354 284
355 285
356 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ; 286 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ;
357 CREATE TABLE measurement_station ( 287 CREATE TABLE measurement_station (
http://dive4elements.wald.intevation.org