Mercurial > dive4elements > river

comparison backend/doc/schema/oracle.sql @ 6300:0711ce5ca701

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Backend: Modified schema for a better model of official lines.
author Sascha L. Teichmann <teichmann@intevation.de>
date Wed, 12 Jun 2013 16:36:34 +0200
parents 573112007ec7
children a8b065038c0c
comparison
equal deleted inserted replaced
6299:16182a08ccf7 6300:0711ce5ca701
342 q NUMBER(38,5) NOT NULL, 342 q NUMBER(38,5) NOT NULL,
343 range_id NUMBER(38,0) NOT NULL, 343 range_id NUMBER(38,0) NOT NULL,
344 PRIMARY KEY (id) 344 PRIMARY KEY (id)
345 ); 345 );
346 346
347 -- OFFICIAL_LINES
348 CREATE SEQUENCE OFFICIAL_LINES_ID_SEQ;
349
350 CREATE TABLE official_lines (
351 id NUMBER(38,0) NOT NULL,
352 wst_column_id NUMBER(38,0) NOT NULL,
353 named_main_value_id NUMBER(38,0) NOT NULL,
354
355 PRIMARY KEY (id),
356 UNIQUE (wst_column_id, named_main_value_id)
357 );
347 358
348 -- WSTS 359 -- WSTS
349 --lookup table for wst kinds 360 --lookup table for wst kinds
350 CREATE TABLE wst_kinds ( 361 CREATE TABLE wst_kinds (
351 id NUMBER PRIMARY KEY NOT NULL, 362 id NUMBER PRIMARY KEY NOT NULL,
404 ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; 415 ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
405 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE; 416 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
406 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges ON DELETE CASCADE; 417 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges ON DELETE CASCADE;
407 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts ON DELETE CASCADE; 418 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts ON DELETE CASCADE;
408 ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE; 419 ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
409 ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES ON DELETE CASCADE; 420 ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES ON LETE CASCADE;
410 ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; 421 ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
411 ALTER TABLE wsts ADD CONSTRAINT cWstsWstKinds FOREIGN KEY (kind) REFERENCES wst_kinds; 422 ALTER TABLE wsts ADD CONSTRAINT cWstsWstKinds FOREIGN KEY (kind) REFERENCES wst_kinds;
423
424 ALTER TABLE official_lines ADD CONSTRAINT cOffLinesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
425 ALTER TABLE official_lines ADD CONSTRAINT cOffLinesNamedMainValues FOREIGN KEY (named_main_value_id) REFERENCES named_main_values ON DELETE CASCADE;
412 426
413 -- VIEWS 427 -- VIEWS
414 428
415 CREATE VIEW wst_value_table AS 429 CREATE VIEW wst_value_table AS
416 SELECT 430 SELECT
455 JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id 469 JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id
456 JOIN ranges r ON wqr.range_id = r.id 470 JOIN ranges r ON wqr.range_id = r.id
457 JOIN wst_columns wc ON wcqr.wst_column_id = wc.id 471 JOIN wst_columns wc ON wcqr.wst_column_id = wc.id
458 ORDER BY wc.position, wcqr.wst_column_id, r.a; 472 ORDER BY wc.position, wcqr.wst_column_id, r.a;
459 473
460 -- Views to make the 'Amtlichen Linien' easier to access.
461
462 CREATE VIEW official_lines
463 AS
464 SELECT w.river_id AS river_id,
465 w.id AS wst_id,
466 wc.id AS wst_column_id,
467 wc.name AS name,
468 wc.position AS wst_column_pos
469 FROM wsts w
470 JOIN wst_columns wc
471 ON wc.wst_id = w.id
472 WHERE w.kind = 3;
473
474 CREATE VIEW q_main_values
475 AS
476 SELECT riv.id AS river_id,
477 g.id AS gauge_id,
478 g.name AS gauge_name,
479 r.a AS a,
480 r.b AS b,
481 REGEXP_REPLACE(
482 nmv.name, '[:space:]*\(.*\)[:space:]*', '') AS name,
483 CAST(mv.value AS NUMERIC(38, 5)) AS value
484 FROM main_values mv
485 JOIN named_main_values nmv
486 ON mv.named_value_id = nmv.id
487 JOIN main_value_types mvt
488 ON nmv.type_id = mvt.id
489 JOIN gauges g
490 ON mv.gauge_id = g.id
491 JOIN ranges r
492 ON g.range_id = r.id
493 JOIN rivers riv
494 ON g.river_id = riv.id
495 WHERE mvt.name = 'Q'
496 ORDER BY g.id, CAST(mv.value AS NUMERIC(38,5));
497
498 CREATE VIEW official_q_values
499 AS
500 SELECT ol.river_id AS river_id,
501 wst_id,
502 wst_column_id,
503 gauge_id,
504 gauge_name,
505 a,
506 b,
507 ol.name,
508 value,
509 wst_column_pos
510 FROM official_lines ol
511 JOIN q_main_values qmv
512 ON ol.river_id = qmv.river_id
513 AND ol.name = qmv.name;
514
515 CREATE VIEW wst_ranges 474 CREATE VIEW wst_ranges
516 AS 475 AS
517 SELECT wc.id AS wst_column_id, 476 SELECT wc.id AS wst_column_id,
518 wc.wst_id AS wst_id, 477 wc.wst_id AS wst_id,
519 Min(wcv.position) AS a, 478 Min(wcv.position) AS a,
http://dive4elements.wald.intevation.org