Mercurial > dive4elements > river
view backend/doc/schema/oracle.sql @ 5915:4fafe8d147b2
Schema change: named_main_values.name is not unique, as we can have the same name for different types
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Tue, 07 May 2013 18:19:57 +0200 |
parents | 39cf5d4acf5d |
children | 131f5f58ff7d |
line wrap: on
line source
-- ANNOTATION_TYPES CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ; CREATE TABLE annotation_types ( id NUMBER(38,0) NOT NULL, name VARCHAR2(255) NOT NULL UNIQUE, PRIMARY KEY (id) ); -- ANNOTATIONS CREATE SEQUENCE ANNOTATIONS_ID_SEQ; CREATE TABLE annotations ( id NUMBER(38,0) NOT NULL, attribute_id NUMBER(38,0) NOT NULL, edge_id NUMBER(38,0), position_id NUMBER(38,0), range_id NUMBER(38,0), type_id NUMBER(38,0), PRIMARY KEY (id) ); -- ATTRIBUTES CREATE SEQUENCE ATTRIBUTES_ID_SEQ; CREATE TABLE attributes ( id NUMBER(38,0) NOT NULL, value VARCHAR2(255) NOT NULL UNIQUE, primary key (id) ); -- CROSS_SECTION_LINES CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ; CREATE TABLE cross_section_lines ( id NUMBER(38,0) NOT NULL, km NUMBER(38,2), cross_section_id NUMBER(38,0), PRIMARY KEY (id) ); -- CROSS_SECTION_POINTS CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ; CREATE TABLE cross_section_points ( id NUMBER(38,0) NOT NULL, col_pos NUMBER(38,0), x NUMBER(38,2), y NUMBER(38,2), cross_section_line_id NUMBER(38,0), PRIMARY KEY (id) ); -- CROSS_SECTIONS CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ; CREATE TABLE cross_sections ( id NUMBER(38,0) NOT NULL, description VARCHAR2(255), river_id NUMBER(38,0), time_interval_id NUMBER(38,0), PRIMARY KEY (id) ); -- Indices for faster access of the points CREATE INDEX cross_section_lines_km_idx ON cross_section_lines(km); CREATE INDEX cross_section_points_line_idx ON cross_section_points(cross_section_line_id); -- DISCHARGE_TABLE_VALUES CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ; CREATE TABLE discharge_table_values ( id NUMBER(38,0) NOT NULL, q NUMBER(38,4) NOT NULL, w NUMBER(38,2) NOT NULL, table_id NUMBER(38,0) NOT NULL, UNIQUE (table_id, q, w), PRIMARY KEY (id) ); -- DISCHARGE_TABLES CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ; CREATE TABLE discharge_tables ( id NUMBER(38,0) NOT NULL, description VARCHAR2(255) NOT NULL, bfg_id VARCHAR2(50), kind NUMBER(38,0) NOT NULL DEFAULT 0, gauge_id NUMBER(38,0) NOT NULL, time_interval_id NUMBER(38,0), PRIMARY KEY (id), UNIQUE(gauge_id, time_interval_id, kind) ); -- EDGES CREATE SEQUENCE EDGES_ID_SEQ; CREATE TABLE edges ( id NUMBER(38,0) NOT NULL, bottom NUMBER(38,2), top NUMBER(38,2), PRIMARY KEY (id) ); -- GAUGES CREATE SEQUENCE GAUGES_ID_SEQ; CREATE TABLE gauges ( id NUMBER(38,0) NOT NULL, aeo NUMBER(38,2) NOT NULL, datum NUMBER(38,2) NOT NULL, name VARCHAR2(255) NOT NULL, station NUMBER(38,2) NOT NULL, official_number NUMBER(38,0), range_id NUMBER(38,0) NOT NULL, -- TODO: remove river id here because range_id references river already river_id NUMBER(38,0) NOT NULL, PRIMARY KEY (id), UNIQUE (name, river_id), UNIQUE (official_number, river_id), UNIQUE (river_id, station) ); -- HYK_ENTRIES CREATE SEQUENCE HYK_ENTRIES_ID_SEQ; CREATE TABLE hyk_entries ( id NUMBER(38,0) NOT NULL, km NUMBER(38,2), measure TIMESTAMP, hyk_id NUMBER(38,0), PRIMARY KEY (id) ); -- HYK_FLOW_ZONE_TYPES CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ; CREATE TABLE hyk_flow_zone_types ( id NUMBER(38,0) NOT NULL, description VARCHAR2(255), name VARCHAR2(255), PRIMARY KEY (id) ); -- HYK_FLOW_ZONES CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ; CREATE TABLE hyk_flow_zones ( id NUMBER(38,0) NOT NULL, a NUMBER(38,2), b NUMBER(38,2), formation_id NUMBER(38,0), type_id NUMBER(38,0), primary key (id) ); -- HYK_FORMATIONS CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ; CREATE TABLE hyk_formations ( id NUMBER(38,0) NOT NULL, bottom NUMBER(38,2), distance_hf NUMBER(38,2), distance_vl NUMBER(38,2), distance_vr NUMBER(38,2), formation_num NUMBER(38,0), top NUMBER(38,2), hyk_entry_id NUMBER(38,0), PRIMARY KEY (id) ); -- HYKS CREATE SEQUENCE HYKS_ID_SEQ; CREATE TABLE hyks ( id NUMBER(38,0) NOT NULL, description VARCHAR2(255), river_id NUMBER(38,0), primary key (id) ); -- MAIN_VALUE_TYPES CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ; CREATE TABLE main_value_types ( id NUMBER(38,0) NOT NULL, name VARCHAR2(255) NOT NULL UNIQUE, PRIMARY KEY (id) ); -- MAIN_VALUES CREATE SEQUENCE MAIN_VALUES_ID_SEQ; CREATE TABLE main_values ( id NUMBER(38,0) NOT NULL, value NUMBER(38,2) NOT NULL, gauge_id NUMBER(38,0) NOT NULL, named_value_id NUMBER(38,0) NOT NULL, time_interval_id NUMBER(38,0), -- TODO: better checks UNIQUE (gauge_id, named_value_id, time_interval_id), PRIMARY KEY (id) ); -- NAMED_MAIN_VALUES CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ; CREATE TABLE named_main_values ( id NUMBER(38,0) NOT NULL, name VARCHAR2(256) NOT NULL, type_id NUMBER(38,0) NOT NULL, PRIMARY KEY (id) ); -- POSITIONS CREATE SEQUENCE POSITIONS_ID_SEQ; CREATE TABLE positions ( id NUMBER(38,0) NOT NULL, value VARCHAR2(255 char) NOT NULL UNIQUE, PRIMARY KEY (id) ); --- RANGES CREATE SEQUENCE RANGES_ID_SEQ; CREATE TABLE ranges ( id NUMBER(38,0) NOT NULL, a NUMBER(38,10) NOT NULL, b NUMBER(38,10), river_id NUMBER(38,0), UNIQUE (river_id, a, b), PRIMARY KEY (id) ); -- RIVERS CREATE SEQUENCE RIVERS_ID_SEQ; CREATE TABLE rivers ( id NUMBER(38,0) NOT NULL, official_number NUMBER(38,0), km_up NUMBER(38,0) NOT NULL DEFAULT 0, name VARCHAR2(255) NOT NULL UNIQUE, wst_unit_id NUMBER(38,0) NOT NULL, PRIMARY KEY (id) ); -- TIME_INTERVALS CREATE SEQUENCE TIME_INTERVALS_ID_SEQ; CREATE TABLE time_intervals ( id NUMBER(38,0) NOT NULL, start_time TIMESTAMP NOT NULL, stop_time TIMESTAMP, PRIMARY KEY (id), CHECK (start_time <= stop_time) ); --- UNITS CREATE SEQUENCE UNITS_ID_SEQ; CREATE TABLE units ( id NUMBER(38,0) NOT NULL, name VARCHAR2(255) NOT NULL UNIQUE, PRIMARY KEY (id) ); -- WST_COLUMN_Q_RANGES CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ; CREATE TABLE wst_column_q_ranges ( id NUMBER(38,0) NOT NULL, wst_column_id NUMBER(38,0) NOT NULL, wst_q_range_id NUMBER(38,0) NOT NULL, UNIQUE (wst_column_id, wst_q_range_id), PRIMARY KEY (id) ); -- WST_COLUMN_VALUES CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; CREATE TABLE wst_column_values ( id NUMBER(38,0) NOT NULL, position NUMBER(38,5) NOT NULL, w NUMBER(38,5) NOT NULL, wst_column_id NUMBER(38,0) NOT NULL, UNIQUE (position, wst_column_id), UNIQUE (position, wst_column_id, w), PRIMARY KEY (id) ); -- WST_COLUMNS CREATE SEQUENCE WST_COLUMNS_ID_SEQ; CREATE TABLE wst_columns ( id NUMBER(38,0) NOT NULL, description VARCHAR2(255), name VARCHAR2(255) NOT NULL, position NUMBER(38,0) NOT NULL DEFAULT 0, time_interval_id NUMBER(38,0), wst_id NUMBER(38,0) NOT NULL, UNIQUE (wst_id, name), UNIQUE (wst_id, position), PRIMARY KEY (id) ); -- WST_Q_RANGES CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; CREATE TABLE wst_q_ranges ( id NUMBER(38,0) NOT NULL, q NUMBER(38,5) NOT NULL, range_id NUMBER(38,0) NOT NULL, PRIMARY KEY (id) ); -- WSTS --lookup table for wst kinds CREATE TABLE wst_kinds ( id NUMBER PRIMARY KEY NOT NULL, kind VARCHAR(64) NOT NULL ); INSERT INTO wst_kinds (id, kind) VALUES (0, 'basedata'); INSERT INTO wst_kinds (id, kind) VALUES (1, 'basedata_additionals_marks'); INSERT INTO wst_kinds (id, kind) VALUES (2, 'basedata_fixations_wst'); INSERT INTO wst_kinds (id, kind) VALUES (3, 'basedata_officials'); INSERT INTO wst_kinds (id, kind) VALUES (4, 'basedata_heightmarks-points-relative_points'); INSERT INTO wst_kinds (id, kind) VALUES (5, 'basedata_flood-protections_relative_points'); INSERT INTO wst_kinds (id, kind) VALUES (6, 'morpho_waterlevel-differences'); INSERT INTO wst_kinds (id, kind) VALUES (7, 'morpho_waterlevels'); CREATE SEQUENCE WSTS_ID_SEQ; CREATE TABLE wsts ( id NUMBER(38,0) NOT NULL, description VARCHAR2(255) NOT NULL, kind NUMBER(38,0) NOT NULL, river_id NUMBER(38,0) NOT NULL, UNIQUE (river_id, description), PRIMARY KEY (id) ); -- ADD CONSTRAINTs ALTER TABLE annotations ADD CONSTRAINT cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes; ALTER TABLE annotations ADD CONSTRAINT cAnnotationsEdges FOREIGN KEY (edge_id) REFERENCES edges; ALTER TABLE annotations ADD CONSTRAINT cAnnotationsPositions FOREIGN KEY (position_id) REFERENCES positions; ALTER TABLE annotations ADD CONSTRAINT cAnnotationsTypes FOREIGN KEY (type_id) REFERENCES annotation_types; ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFlowZoneTypes FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types; ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values; ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types; ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units; ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; -- Cascading references ALTER TABLE annotations ADD CONSTRAINT cAnnotationsRanges FOREIGN KEY (range_id) REFERENCES ranges ON DELETE CASCADE; ALTER TABLE cross_section_lines ADD CONSTRAINT cQPSLinesCrossSections FOREIGN KEY (cross_section_id) REFERENCES cross_sections ON DELETE CASCADE; ALTER TABLE cross_section_points ADD CONSTRAINT cQPSPointsCrossSectionLines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines ON DELETE CASCADE; ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesGauges FOREIGN KEY (gauge_id) REFERENCES gauges ON DELETE CASCADE; ALTER TABLE discharge_table_values ADD CONSTRAINT cTableValuesDischargeTables FOREIGN KEY (table_id) REFERENCES discharge_tables ON DELETE CASCADE; ALTER TABLE gauges ADD CONSTRAINT cGaugesRanges FOREIGN KEY (range_id) REFERENCES ranges ON DELETE CASCADE; ALTER TABLE gauges ADD CONSTRAINT cGaugesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; ALTER TABLE hyk_entries ADD CONSTRAINT cHykEntriesHyks FOREIGN KEY (hyk_id) REFERENCES hyks ON DELETE CASCADE; ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFormations FOREIGN KEY (formation_id) REFERENCES hyk_formations ON DELETE CASCADE; ALTER TABLE hyk_formations ADD CONSTRAINT cHykFormationsHykEntries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries ON DELETE CASCADE; ALTER TABLE hyks ADD CONSTRAINT cHyksRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; ALTER TABLE main_values ADD CONSTRAINT cMainValuesGauges FOREIGN KEY (gauge_id) REFERENCES gauges ON DELETE CASCADE; ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE; 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 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; -- VIEWS CREATE VIEW wst_value_table AS SELECT wcv.position AS position, w, (SELECT q FROM wst_column_q_ranges wcqr JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id JOIN ranges r ON r.id = wqr.range_id WHERE wcqr.wst_column_id = wc.id AND wcv.position BETWEEN r.a AND r.b) AS q, wc.position AS column_pos, w.id AS wst_id FROM wst_column_values wcv JOIN wst_columns wc ON wcv.wst_column_id = wc.id JOIN wsts w ON wc.wst_id = w.id ORDER BY wcv.position ASC, wc.position DESC; -- view to select the w values of a WST CREATE VIEW wst_w_values AS SELECT wcv.position AS km, wcv.w AS w, wc.position AS column_pos, w.id AS wst_id FROM wst_column_values wcv JOIN wst_columns wc ON wcv.wst_column_id = wc.id JOIN wsts w ON wc.wst_id = w.id ORDER BY wcv.position, wc.position; -- view to select the q values of a WST CREATE VIEW wst_q_values AS SELECT wc.position AS column_pos, wqr.q AS q, r.a AS a, r.b AS b, wc.wst_id AS wst_id FROM wst_column_q_ranges wcqr JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id JOIN ranges r ON wqr.range_id = r.id 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, wc.wst_id AS wst_id, Min(wcv.position) AS a, Max(wcv.position) AS b FROM wst_columns wc JOIN wst_column_values wcv ON wc.id = wcv.wst_column_id GROUP BY wc.id, wc.wst_id;