bjoern@2351: -- ANNOTATION_TYPES bjoern@2351: CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE annotation_types ( bjoern@2351: id NUMBER(38,0) NOT NULL, tom@5783: name VARCHAR2(255) NOT NULL UNIQUE, bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- ANNOTATIONS bjoern@2351: CREATE SEQUENCE ANNOTATIONS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE annotations ( tom@5783: id NUMBER(38,0) NOT NULL, tom@5783: attribute_id NUMBER(38,0) NOT NULL, bjoern@2351: edge_id NUMBER(38,0), bjoern@2351: position_id NUMBER(38,0), bjoern@2351: range_id NUMBER(38,0), bjoern@2351: type_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- ATTRIBUTES bjoern@2351: CREATE SEQUENCE ATTRIBUTES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE attributes ( bjoern@2351: id NUMBER(38,0) NOT NULL, tom@6642: value VARCHAR2(255) UNIQUE, bjoern@2351: primary key (id) bjoern@2351: ); tom@6642: -- value can not be NOT NULL in Oracle: tom@6642: -- '' is needed here and silently converted to NULL in Oracle bjoern@2351: bjoern@2351: -- CROSS_SECTION_LINES bjoern@2351: CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE cross_section_lines ( bjoern@2351: id NUMBER(38,0) NOT NULL, tom@6735: km NUMBER(38,5) NOT NULL, tom@6030: cross_section_id NUMBER(38,0) NOT NULL, tom@6030: PRIMARY KEY (id), tom@6030: UNIQUE (km, cross_section_id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- CROSS_SECTION_POINTS bjoern@2351: CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE cross_section_points ( bjoern@2351: id NUMBER(38,0) NOT NULL, tom@6030: col_pos NUMBER(38,0) NOT NULL, tom@6030: x NUMBER(38,2) NOT NULL, tom@6030: y NUMBER(38,2) NOT NULL, tom@6030: cross_section_line_id NUMBER(38,0) NOT NULL, tom@6632: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- CROSS_SECTIONS ingo@2855: CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ; bjoern@2351: ingo@2855: CREATE TABLE cross_sections ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: description VARCHAR2(255), tom@6030: river_id NUMBER(38,0) NOT NULL, bjoern@2351: time_interval_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: sascha@3339: -- Indices for faster access of the points sascha@3339: CREATE INDEX cross_section_lines_km_idx sascha@3339: ON cross_section_lines(km); sascha@3339: CREATE INDEX cross_section_points_line_idx sascha@3339: ON cross_section_points(cross_section_line_id); bjoern@2351: bjoern@2351: -- DISCHARGE_TABLE_VALUES bjoern@2351: CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE discharge_table_values ( bjoern@2351: id NUMBER(38,0) NOT NULL, tom@5897: q NUMBER(38,4) NOT NULL, tom@5783: w NUMBER(38,2) NOT NULL, tom@5783: table_id NUMBER(38,0) NOT NULL, tom@5783: UNIQUE (table_id, q, w), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- DISCHARGE_TABLES bjoern@2351: CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE discharge_tables ( bjoern@2351: id NUMBER(38,0) NOT NULL, tom@5783: description VARCHAR2(255) NOT NULL, teichmann@4776: bfg_id VARCHAR2(50), tom@6632: kind NUMBER(38,0) DEFAULT 0 NOT NULL, tom@5783: gauge_id NUMBER(38,0) NOT NULL, bjoern@2351: time_interval_id NUMBER(38,0), tom@5883: PRIMARY KEY (id), tom@5923: UNIQUE(gauge_id, bfg_id, kind) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- EDGES bjoern@2351: CREATE SEQUENCE EDGES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE edges ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: bottom NUMBER(38,2), bjoern@2351: top NUMBER(38,2), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- GAUGES bjoern@2351: CREATE SEQUENCE GAUGES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE gauges ( bjoern@2351: id NUMBER(38,0) NOT NULL, tom@5207: aeo NUMBER(38,2) NOT NULL, tom@5207: datum NUMBER(38,2) NOT NULL, tom@5207: name VARCHAR2(255) NOT NULL, tom@7016: station NUMBER(38,4) NOT NULL, tom@5894: official_number NUMBER(38,0), tom@6871: range_id NUMBER(38,0), tom@5894: -- TODO: remove river id here because range_id references river already tom@5783: river_id NUMBER(38,0) NOT NULL, tom@5207: PRIMARY KEY (id), tom@5207: UNIQUE (name, river_id), tom@5894: UNIQUE (official_number, river_id), tom@5207: UNIQUE (river_id, station) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- HYK_ENTRIES bjoern@2351: CREATE SEQUENCE HYK_ENTRIES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE hyk_entries ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: km NUMBER(38,2), bjoern@2351: measure TIMESTAMP, bjoern@2351: hyk_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- HYK_FLOW_ZONE_TYPES bjoern@2351: CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE hyk_flow_zone_types ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: description VARCHAR2(255), bjoern@2351: name VARCHAR2(255), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- HYK_FLOW_ZONES bjoern@2351: CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE hyk_flow_zones ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: a NUMBER(38,2), bjoern@2351: b NUMBER(38,2), bjoern@2351: formation_id NUMBER(38,0), bjoern@2351: type_id NUMBER(38,0), bjoern@2351: primary key (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- HYK_FORMATIONS bjoern@2351: CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE hyk_formations ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: bottom NUMBER(38,2), bjoern@2351: distance_hf NUMBER(38,2), bjoern@2351: distance_vl NUMBER(38,2), bjoern@2351: distance_vr NUMBER(38,2), bjoern@2351: formation_num NUMBER(38,0), bjoern@2351: top NUMBER(38,2), bjoern@2351: hyk_entry_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- HYKS bjoern@2351: CREATE SEQUENCE HYKS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE hyks ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: description VARCHAR2(255), bjoern@2351: river_id NUMBER(38,0), bjoern@2351: primary key (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- MAIN_VALUE_TYPES bjoern@2351: CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE main_value_types ( bjoern@2351: id NUMBER(38,0) NOT NULL, tom@5783: name VARCHAR2(255) NOT NULL UNIQUE, bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- MAIN_VALUES bjoern@2351: CREATE SEQUENCE MAIN_VALUES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE main_values ( bjoern@2351: id NUMBER(38,0) NOT NULL, tom@5783: value NUMBER(38,2) NOT NULL, tom@5783: gauge_id NUMBER(38,0) NOT NULL, tom@5783: named_value_id NUMBER(38,0) NOT NULL, bjoern@2351: time_interval_id NUMBER(38,0), tom@5783: UNIQUE (gauge_id, named_value_id, time_interval_id), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- NAMED_MAIN_VALUES bjoern@2351: CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE named_main_values ( bjoern@2351: id NUMBER(38,0) NOT NULL, tom@5915: name VARCHAR2(256) NOT NULL, tom@5783: type_id NUMBER(38,0) NOT NULL, bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- POSITIONS bjoern@2351: CREATE SEQUENCE POSITIONS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE positions ( bjoern@2353: id NUMBER(38,0) NOT NULL, tom@5783: value VARCHAR2(255 char) NOT NULL UNIQUE, bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: --- RANGES bjoern@2351: CREATE SEQUENCE RANGES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE ranges ( bjoern@2351: id NUMBER(38,0) NOT NULL, tom@5158: a NUMBER(38,10) NOT NULL, sascha@2367: b NUMBER(38,10), bjoern@2351: river_id NUMBER(38,0), tom@5783: UNIQUE (river_id, a, b), tom@7005: PRIMARY KEY (id), tom@7005: CHECK (a < b) bjoern@2351: ); tom@5321: bjoern@2351: bjoern@2351: bjoern@2351: -- RIVERS bjoern@2351: CREATE SEQUENCE RIVERS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE rivers ( bjoern@2351: id NUMBER(38,0) NOT NULL, tom@7743: model_uuid CHAR(36 CHAR) UNIQUE, tom@5893: official_number NUMBER(38,0), tom@8411: km_up int DEFAULT 0 NOT NULL, tom@5783: name VARCHAR2(255) NOT NULL UNIQUE, tom@5783: wst_unit_id NUMBER(38,0) NOT NULL, tom@8411: PRIMARY KEY (id), tom@8411: CHECK(km_up IN(0,1)) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- TIME_INTERVALS bjoern@2351: CREATE SEQUENCE TIME_INTERVALS_ID_SEQ; bjoern@2351: aheinecke@5206: CREATE TABLE time_intervals ( bjoern@2351: id NUMBER(38,0) NOT NULL, tom@5158: start_time TIMESTAMP NOT NULL, bjoern@2351: stop_time TIMESTAMP, tom@5158: PRIMARY KEY (id), tom@5158: CHECK (start_time <= stop_time) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: --- UNITS bjoern@2351: CREATE SEQUENCE UNITS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE units ( bjoern@2353: id NUMBER(38,0) NOT NULL, tom@5783: name VARCHAR2(255) NOT NULL UNIQUE, bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- WST_COLUMN_Q_RANGES bjoern@2351: CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE wst_column_q_ranges ( bjoern@2351: id NUMBER(38,0) NOT NULL, tom@5783: wst_column_id NUMBER(38,0) NOT NULL, tom@5783: wst_q_range_id NUMBER(38,0) NOT NULL, tom@5783: UNIQUE (wst_column_id, wst_q_range_id), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- WST_COLUMN_VALUES bjoern@2351: CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE wst_column_values ( bjoern@2351: id NUMBER(38,0) NOT NULL, tom@5783: position NUMBER(38,5) NOT NULL, tom@5783: w NUMBER(38,5) NOT NULL, tom@5783: wst_column_id NUMBER(38,0) NOT NULL, tom@5783: UNIQUE (position, wst_column_id), tom@5783: UNIQUE (position, wst_column_id, w), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- WST_COLUMNS bjoern@2351: CREATE SEQUENCE WST_COLUMNS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE wst_columns ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: description VARCHAR2(255), tom@5783: name VARCHAR2(255) NOT NULL, tom@6502: source VARCHAR(256), tom@6632: position NUMBER(38,0) DEFAULT 0 NOT NULL, tom@6011: time_interval_id NUMBER(38,0), tom@5783: wst_id NUMBER(38,0) NOT NULL, tom@5783: UNIQUE (wst_id, name), tom@5783: UNIQUE (wst_id, position), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: -- WST_Q_RANGES bjoern@2351: CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE wst_q_ranges ( bjoern@2351: id NUMBER(38,0) NOT NULL, tom@5783: q NUMBER(38,5) NOT NULL, tom@5783: range_id NUMBER(38,0) NOT NULL, bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: teichmann@6300: -- OFFICIAL_LINES teichmann@6300: CREATE SEQUENCE OFFICIAL_LINES_ID_SEQ; teichmann@6300: teichmann@6300: CREATE TABLE official_lines ( teichmann@6300: id NUMBER(38,0) NOT NULL, teichmann@6300: wst_column_id NUMBER(38,0) NOT NULL, teichmann@6300: named_main_value_id NUMBER(38,0) NOT NULL, teichmann@6367: UNIQUE (wst_column_id, named_main_value_id), teichmann@6367: PRIMARY KEY (id) teichmann@6300: ); bjoern@2351: bjoern@2351: -- WSTS tom@5224: --lookup table for wst kinds tom@5224: CREATE TABLE wst_kinds ( tom@5224: id NUMBER PRIMARY KEY NOT NULL, tom@5224: kind VARCHAR(64) NOT NULL tom@5224: ); tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (0, 'basedata'); tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (1, 'basedata_additionals_marks'); tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (2, 'basedata_fixations_wst'); tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (3, 'basedata_officials'); tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (4, 'basedata_heightmarks-points-relative_points'); tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (5, 'basedata_flood-protections_relative_points'); tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (6, 'morpho_waterlevel-differences'); tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (7, 'morpho_waterlevels'); tom@5224: tom@5224: bjoern@2351: CREATE SEQUENCE WSTS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE wsts ( bjoern@2351: id NUMBER(38,0) NOT NULL, tom@5783: description VARCHAR2(255) NOT NULL, tom@5783: kind NUMBER(38,0) NOT NULL, tom@5783: river_id NUMBER(38,0) NOT NULL, tom@5783: UNIQUE (river_id, description), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2352: -- ADD CONSTRAINTs aheinecke@5113: ALTER TABLE annotations ADD CONSTRAINT cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes; bjoern@2352: ALTER TABLE annotations ADD CONSTRAINT cAnnotationsEdges FOREIGN KEY (edge_id) REFERENCES edges; bjoern@2352: ALTER TABLE annotations ADD CONSTRAINT cAnnotationsPositions FOREIGN KEY (position_id) REFERENCES positions; bjoern@2352: ALTER TABLE annotations ADD CONSTRAINT cAnnotationsTypes FOREIGN KEY (type_id) REFERENCES annotation_types; bjoern@2352: ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; bjoern@2352: ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; bjoern@2352: ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFlowZoneTypes FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types; aheinecke@5113: ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values; bjoern@2352: ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; bjoern@2352: ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types; bjoern@2352: ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units; bjoern@2352: ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; aheinecke@5113: aheinecke@5113: -- Cascading references aheinecke@5113: ALTER TABLE annotations ADD CONSTRAINT cAnnotationsRanges FOREIGN KEY (range_id) REFERENCES ranges ON DELETE CASCADE; aheinecke@5113: ALTER TABLE cross_section_lines ADD CONSTRAINT cQPSLinesCrossSections FOREIGN KEY (cross_section_id) REFERENCES cross_sections ON DELETE CASCADE; aheinecke@5113: ALTER TABLE cross_section_points ADD CONSTRAINT cQPSPointsCrossSectionLines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines ON DELETE CASCADE; aheinecke@5113: ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; aheinecke@5113: ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesGauges FOREIGN KEY (gauge_id) REFERENCES gauges ON DELETE CASCADE; aheinecke@5113: ALTER TABLE discharge_table_values ADD CONSTRAINT cTableValuesDischargeTables FOREIGN KEY (table_id) REFERENCES discharge_tables ON DELETE CASCADE; aheinecke@5113: ALTER TABLE gauges ADD CONSTRAINT cGaugesRanges FOREIGN KEY (range_id) REFERENCES ranges ON DELETE CASCADE; aheinecke@5113: ALTER TABLE gauges ADD CONSTRAINT cGaugesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; aheinecke@5113: ALTER TABLE hyk_entries ADD CONSTRAINT cHykEntriesHyks FOREIGN KEY (hyk_id) REFERENCES hyks ON DELETE CASCADE; aheinecke@5113: ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFormations FOREIGN KEY (formation_id) REFERENCES hyk_formations ON DELETE CASCADE; aheinecke@5113: ALTER TABLE hyk_formations ADD CONSTRAINT cHykFormationsHykEntries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries ON DELETE CASCADE; aheinecke@5113: ALTER TABLE hyks ADD CONSTRAINT cHyksRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; aheinecke@5113: ALTER TABLE main_values ADD CONSTRAINT cMainValuesGauges FOREIGN KEY (gauge_id) REFERENCES gauges ON DELETE CASCADE; aheinecke@5113: ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; aheinecke@5113: ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE; aheinecke@5113: ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges ON DELETE CASCADE; aheinecke@5113: ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts ON DELETE CASCADE; aheinecke@5113: ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE; tom@6632: ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES ON DELETE CASCADE; aheinecke@5113: ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; tom@5224: ALTER TABLE wsts ADD CONSTRAINT cWstsWstKinds FOREIGN KEY (kind) REFERENCES wst_kinds; bjoern@2355: teichmann@6300: ALTER TABLE official_lines ADD CONSTRAINT cOffLinesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE; teichmann@6300: ALTER TABLE official_lines ADD CONSTRAINT cOffLinesNamedMainValues FOREIGN KEY (named_main_value_id) REFERENCES named_main_values ON DELETE CASCADE; teichmann@6300: bjoern@2355: -- VIEWS bjoern@2355: bjoern@2355: CREATE VIEW wst_value_table AS tom@6078: SELECT tom@6078: wcv.position AS position, tom@6078: w, tom@6078: q, tom@6078: wc.position AS column_pos, tom@6078: w.id AS wst_id tom@6078: FROM wsts w tom@6078: JOIN wst_columns wc tom@6078: ON wc.wst_id=w.id tom@6078: JOIN wst_column_q_ranges wcqr tom@6078: ON wcqr.wst_column_id=wc.id tom@6078: JOIN wst_q_ranges wqr tom@6078: ON wcqr.wst_q_range_id=wqr.id tom@6078: JOIN ranges r tom@6078: ON wqr.range_id=r.id tom@6078: JOIN wst_column_values wcv felix@6177: ON wcv.wst_column_id=wc.id AND wcv.position between r.a and r.b bjoern@2355: ORDER BY wcv.position ASC, tom@6078: wc.position DESC; bjoern@2355: bjoern@2355: -- view to select the w values of a WST bjoern@2355: CREATE VIEW wst_w_values AS bjoern@2355: SELECT wcv.position AS km, bjoern@2355: wcv.w AS w, bjoern@2355: wc.position AS column_pos, bjoern@2355: w.id AS wst_id bjoern@2355: FROM wst_column_values wcv bjoern@2355: JOIN wst_columns wc ON wcv.wst_column_id = wc.id bjoern@2355: JOIN wsts w ON wc.wst_id = w.id bjoern@2355: ORDER BY wcv.position, wc.position; bjoern@2355: bjoern@2355: -- view to select the q values of a WST bjoern@2355: CREATE VIEW wst_q_values AS bjoern@2355: SELECT wc.position AS column_pos, bjoern@2355: wqr.q AS q, bjoern@2355: r.a AS a, bjoern@2355: r.b AS b, bjoern@2355: wc.wst_id AS wst_id bjoern@2355: FROM wst_column_q_ranges wcqr bjoern@2355: JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id bjoern@2355: JOIN ranges r ON wqr.range_id = r.id bjoern@2355: JOIN wst_columns wc ON wcqr.wst_column_id = wc.id bjoern@2355: ORDER BY wc.position, wcqr.wst_column_id, r.a; sascha@3335: teichmann@4651: CREATE VIEW wst_ranges teichmann@4651: AS teichmann@4651: SELECT wc.id AS wst_column_id, teichmann@4651: wc.wst_id AS wst_id, teichmann@4651: Min(wcv.position) AS a, teichmann@4651: Max(wcv.position) AS b teichmann@4651: FROM wst_columns wc teichmann@4651: JOIN wst_column_values wcv teichmann@4651: ON wc.id = wcv.wst_column_id teichmann@4651: GROUP BY wc.id, teichmann@4651: wc.wst_id;