# HG changeset patch # User Sascha L. Teichmann # Date 1371047794 -7200 # Node ID 0711ce5ca7016cc62f26e84d739155a9f0547a1d # Parent 16182a08ccf76a6a7366c89b38fe7a70af7439fc Backend: Modified schema for a better model of official lines. diff -r 16182a08ccf7 -r 0711ce5ca701 backend/doc/schema/oracle-drop.sql --- a/backend/doc/schema/oracle-drop.sql Wed Jun 12 14:28:37 2013 +0200 +++ b/backend/doc/schema/oracle-drop.sql Wed Jun 12 16:36:34 2013 +0200 @@ -57,6 +57,7 @@ DROP TABLE wst_column_values; DROP TABLE wst_columns; DROP TABLE wst_q_ranges; +DROP TABLE official_lines; DROP TABLE wsts; DROP TABLE wst_kinds; DROP SEQUENCE ANNOTATION_TYPES_ID_SEQ; @@ -86,11 +87,9 @@ DROP SEQUENCE WST_COLUMN_VALUES_ID_SEQ; DROP SEQUENCE WST_COLUMNS_ID_SEQ; DROP SEQUENCE WST_Q_RANGES_ID_SEQ; +DROP SEQUENCE OFFICIAL_LINES_ID_SEQ; DROP SEQUENCE WSTS_ID_SEQ; DROP VIEW wst_value_table; DROP VIEW wst_w_values ; DROP VIEW wst_q_values; -DROP VIEW official_lines; -DROP VIEW q_main_values; -DROP VIEW official_q_values; DROP VIEW wst_ranges; diff -r 16182a08ccf7 -r 0711ce5ca701 backend/doc/schema/oracle.sql --- a/backend/doc/schema/oracle.sql Wed Jun 12 14:28:37 2013 +0200 +++ b/backend/doc/schema/oracle.sql Wed Jun 12 16:36:34 2013 +0200 @@ -344,6 +344,17 @@ PRIMARY KEY (id) ); +-- OFFICIAL_LINES +CREATE SEQUENCE OFFICIAL_LINES_ID_SEQ; + +CREATE TABLE official_lines ( + id NUMBER(38,0) NOT NULL, + wst_column_id NUMBER(38,0) NOT NULL, + named_main_value_id NUMBER(38,0) NOT NULL, + + PRIMARY KEY (id), + UNIQUE (wst_column_id, named_main_value_id) +); -- WSTS --lookup table for wst kinds @@ -406,10 +417,13 @@ ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges ON DELETE CASCADE; ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts ON DELETE CASCADE; ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE; -ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES ON DELETE CASCADE; +ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES ON LETE CASCADE; ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; ALTER TABLE wsts ADD CONSTRAINT cWstsWstKinds FOREIGN KEY (kind) REFERENCES wst_kinds; +ALTER TABLE official_lines ADD CONSTRAINT cOffLinesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE; +ALTER TABLE official_lines ADD CONSTRAINT cOffLinesNamedMainValues FOREIGN KEY (named_main_value_id) REFERENCES named_main_values ON DELETE CASCADE; + -- VIEWS CREATE VIEW wst_value_table AS @@ -457,61 +471,6 @@ JOIN wst_columns wc ON wcqr.wst_column_id = wc.id ORDER BY wc.position, wcqr.wst_column_id, r.a; --- Views to make the 'Amtlichen Linien' easier to access. - -CREATE VIEW official_lines -AS - SELECT w.river_id AS river_id, - w.id AS wst_id, - wc.id AS wst_column_id, - wc.name AS name, - wc.position AS wst_column_pos - FROM wsts w - JOIN wst_columns wc - ON wc.wst_id = w.id - WHERE w.kind = 3; - -CREATE VIEW q_main_values -AS - SELECT riv.id AS river_id, - g.id AS gauge_id, - g.name AS gauge_name, - r.a AS a, - r.b AS b, - REGEXP_REPLACE( - nmv.name, '[:space:]*\(.*\)[:space:]*', '') AS name, - CAST(mv.value AS NUMERIC(38, 5)) AS value - FROM main_values mv - JOIN named_main_values nmv - ON mv.named_value_id = nmv.id - JOIN main_value_types mvt - ON nmv.type_id = mvt.id - JOIN gauges g - ON mv.gauge_id = g.id - JOIN ranges r - ON g.range_id = r.id - JOIN rivers riv - ON g.river_id = riv.id - WHERE mvt.name = 'Q' - ORDER BY g.id, CAST(mv.value AS NUMERIC(38,5)); - -CREATE VIEW official_q_values -AS - SELECT ol.river_id AS river_id, - wst_id, - wst_column_id, - gauge_id, - gauge_name, - a, - b, - ol.name, - value, - wst_column_pos - FROM official_lines ol - JOIN q_main_values qmv - ON ol.river_id = qmv.river_id - AND ol.name = qmv.name; - CREATE VIEW wst_ranges AS SELECT wc.id AS wst_column_id, diff -r 16182a08ccf7 -r 0711ce5ca701 backend/doc/schema/postgresql.sql --- a/backend/doc/schema/postgresql.sql Wed Jun 12 14:28:37 2013 +0200 +++ b/backend/doc/schema/postgresql.sql Wed Jun 12 16:36:34 2013 +0200 @@ -242,6 +242,16 @@ UNIQUE (wst_column_id, wst_q_range_id) ); +CREATE SEQUENCE OFFICIAL_LINES_ID_SEQ; + +CREATE TABLE official_lines ( + id int PRIMARY KEY NOT NULL, + wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE, + named_main_value_id int NOT NULL REFERENCES named_main_values(id) ON DELETE CASCADE, + + UNIQUE (wst_column_id, named_main_value_id) +); + CREATE VIEW wst_value_table AS SELECT wcv.position AS position, @@ -376,59 +386,6 @@ CHECK (a <= b) ); -CREATE VIEW official_lines -AS - SELECT w.river_id AS river_id, - w.id AS wst_id, - wc.id AS wst_column_id, - wc.name AS name, - wc.position AS wst_column_pos - FROM wsts w - JOIN wst_columns wc - ON wc.wst_id = w.id - WHERE w.kind = 3; - -CREATE VIEW q_main_values -AS - SELECT riv.id AS river_id, - g.id AS gauge_id, - g.name AS gauge_name, - r.a AS a, - r.b AS b, - REGEXP_REPLACE( - nmv.name, E'[:space:]*\\(.*\\)[:space:]*', '') AS name, - CAST(mv.value AS NUMERIC(38, 2)) AS value - FROM main_values mv - JOIN named_main_values nmv - ON mv.named_value_id = nmv.id - JOIN main_value_types mvt - ON nmv.type_id = mvt.id - JOIN gauges g - ON mv.gauge_id = g.id - JOIN ranges r - ON g.range_id = r.id - JOIN rivers riv - ON g.river_id = riv.id - WHERE mvt.name = 'Q' - ORDER BY g.id, CAST(mv.value AS NUMERIC(38,2)); - -CREATE VIEW official_q_values -AS - SELECT ol.river_id AS river_id, - wst_id, - wst_column_id, - gauge_id, - gauge_name, - a, - b, - ol.name, - value, - wst_column_pos - FROM official_lines ol - JOIN q_main_values qmv - ON ol.river_id = qmv.river_id - AND ol.name = qmv.name; - CREATE VIEW wst_ranges AS SELECT wc.id AS wst_column_id,