bjoern@1255: BEGIN; 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, bjoern@2351: name VARCHAR2(255), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: CREATE OR REPLACE TRIGGER annotation_types_trigger BEFORE INSERT ON annotation_types FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT ANNOTATION_TYPES_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: -- ANNOTATIONS bjoern@2351: CREATE SEQUENCE ANNOTATIONS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE annotations ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: attribute_id NUMBER(38,0), 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: CREATE OR REPLACE TRIGGER annotations_trigger BEFORE INSERT ON annotations FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT ANNOTATIONS_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: ALTER TABLE annotations ADD CONSTRAINT constraint_ranges FOREIGN KEY (range_id) REFERENCES ranges; bjoern@2351: ALTER TABLE annotations ADD CONSTRAINT constraint_edges FOREIGN KEY (edge_id) REFERENCES edges; bjoern@2351: ALTER TABLE annotations ADD CONSTRAINT constraint_positions FOREIGN KEY (position_id) REFERENCES positions; bjoern@2351: ALTER TABLE annotations ADD CONSTRAINT constraint_attributes FOREIGN KEY (attribute_id) REFERENCES attributes; bjoern@2351: ALTER TABLE annotations ADD CONSTRAINT constraint_types FOREIGN KEY (type_id) REFERENCES annotation_types; 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, bjoern@2351: value VARCHAR2(255), bjoern@2351: primary key (id) bjoern@2351: ); bjoern@2351: bjoern@2351: CREATE OR REPLACE TRIGGER attributes_trigger BEFORE INSERT ON attributes FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT ATTRIBUTES_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / 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, bjoern@2351: km NUMBER(38,2), bjoern@2351: cross_section_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: CREATE OR REPLACE TRIGGER cross_section_lines_trigger BEFORE INSERT ON cross_section_lines FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT CROSS_SECTION_LINES_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: ALTER TABLE cross_section_lines ADD CONSTRAINT constraint_cross_sections FOREIGN KEY (cross_section_id) REFERENCES cross_sections; 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, bjoern@2351: col_pos NUMBER(38,0), bjoern@2351: x NUMBER(38,2), bjoern@2351: y NUMBER(38,2), bjoern@2351: cross_section_line_id NUMBER(10,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: CREATE OR REPLACE TRIGGER cross_section_points_trigger BEFORE INSERT ON cross_section_points FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT CROSS_SECTION_POINTS_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: ALTER TABLE cross_section_points ADD CONSTRAINT constraint_cross_section_lines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines; bjoern@2351: bjoern@2351: -- CROSS_SECTIONS bjoern@1255: create sequence CROSS_SECTIONS_ID_SEQ; bjoern@2351: bjoern@2351: create table cross_sections ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: description VARCHAR2(255), bjoern@2351: river_id NUMBER(38,0), bjoern@2351: time_interval_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: CREATE OR REPLACE TRIGGER cross_sections_trigger BEFORE INSERT ON cross_sections FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT CROSS_SECTIONS_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: ALTER TABLE cross_sections ADD CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers; bjoern@2351: ALTER TABLE cross_sections ADD CONSTRAINT constraint_time_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; 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, bjoern@2351: q NUMBER(38,2), bjoern@2351: w NUMBER(38,2), bjoern@2351: table_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: CREATE OR REPLACE TRIGGER discharge_table_values_trigger BEFORE INSERT ON discharge_table_values FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT DISCHARGE_TABLES_VALUES_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: ALTER TABLE discharge_table_values ADD CONSTRAINT constraint_discharge_tables foreign key (table_id) REFERENCES discharge_tables; 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, bjoern@2351: description VARCHAR2(255), bjoern@2351: kind NUMBER(38,0), bjoern@2351: gauge_id NUMBER(38,0), bjoern@2351: time_interval_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: CREATE OR REPLACE TRIGGER discharge_tables_trigger BEFORE INSERT ON discharge_tables FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT DISCHARGE_TABLES_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: ALTER TABLE discharge_tables ADD CONSTRAINT constraint_time_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; bjoern@2351: ALTER TABLE discharge_tables ADD CONSTRAINT constraint_gauges FOREIGN KEY (gauge_id) REFERENCES gauges; 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: CREATE OR REPLACE TRIGGER edges_trigger BEFORE INSERT ON edges FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT EDGES_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; 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, bjoern@2351: aeo NUMBER(38,2), bjoern@2351: datum NUMBER(38,2), bjoern@2351: name VARCHAR2(255), bjoern@2351: station NUMBER(38,2), bjoern@2351: range_id NUMBER(38,0), bjoern@2351: river_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: CREATE OR REPLACE TRIGGER gauges_trigger BEFORE INSERT ON gauges FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT GAUGES_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: ALTER TABLE gauges add CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers; bjoern@2351: ALTER TABLE gauges add CONSTRAINT constraint_ranges FOREIGN KEY (range_id) REFERENCES ranges; 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: CREATE OR REPLACE TRIGGER hyk_entries_trigger BEFORE INSERT ON hyk_entries FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT HYK_ENTRIES_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: ALTER TABLE hyk_entries ADD CONSTRAINT constraint_hyks FOREIGN KEY (hyk_id) REFERENCES hyks; 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: CREATE OR REPLACE TRIGGER hyk_flow_zone_types_trigger BEFORE INSERT ON hyk_flow_zone_types FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT HYK_FLOW_ZONE_TYPES_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; 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: CREATE OR REPLACE TRIGGER hyk_flow_zones_trigger BEFORE INSERT ON hyk_flow_zones FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT HYK_FLOW_ZONES_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: ALTER TABLE hyk_flow_zones ADD CONSTRAINT constraint_hyk_formations FOREIGN KEY (formation_id) REFERENCES hyk_formations; bjoern@2351: ALTER TABLE hyk_flow_zones ADD CONSTRAINT constraint_hyk_flow_zone_types FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types; 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: CREATE OR REPLACE TRIGGER hyk_formations_trigger BEFORE INSERT ON hyk_formations FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT HYK_FORMATIONS_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: ALTER TABLE hyk_formations ADD CONSTRAINT constraint_hyk_entries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries; 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: CREATE OR REPLACE TRIGGER hyks_trigger BEFORE INSERT ON hyks FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT HYKS_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: ALTER TABLE hyks ADD CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers; 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, bjoern@2351: name VARCHAR2(255), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: CREATE OR REPLACE TRIGGER main_value_types_trigger BEFORE INSERT ON main_value_types FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT MAIN_VALUE_TYPES_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; 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, bjoern@2351: value NUMBER(38,2), bjoern@2351: gauge_id NUMBER(38,0), bjoern@2351: named_value_id NUMBER(38,0), bjoern@2351: time_interval_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: CREATE OR REPLACE TRIGGER main_values_trigger BEFORE INSERT ON main_values FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT MAIN_VALUES_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: ALTER TABLE main_values ADD CONSTRAINT constraint_time_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; bjoern@2351: ALTER TABLE main_values ADD CONSTRAINT constraint_gauges FOREIGN KEY (gauge_id) REFERENCES gauges; bjoern@2351: ALTER TABLE main_values ADD CONSTRAINT constraint_named_main_values FOREIGN KEY (named_value_id) REFERENCES named_main_values; 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, bjoern@2351: name VARCHAR2(255), bjoern@2351: type_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: CREATE OR REPLACE TRIGGER named_main_values_trigger BEFORE INSERT ON named_main_values FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT NAMED_MAIN_VALUES_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: ALTER TABLE named_main_values ADD CONSTRAINT constraint_main_value_types FOREIGN KEY (type_id) REFERENCES main_value_types; bjoern@2351: bjoern@2351: -- POSITIONS bjoern@2351: CREATE SEQUENCE POSITIONS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE positions ( bjoern@2351: id NUMBER(10,0) NOT NULL, bjoern@2351: value VARCHAR2(255 char), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: CREATE OR REPLACE TRIGGER positions_trigger BEFORE INSERT ON positions FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT POSITIONS_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; 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, bjoern@2351: a NUMBER(38,2), bjoern@2351: b NUMBER(38,2), bjoern@2351: river_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: CREATE OR REPLACE TRIGGER ranges_trigger BEFORE INSERT ON ranges FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT RANGES_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: ALTER TABLE ranges ADD CONSTRAINT contraint_rivers FOREIGN KEY (river_id) REFERENCES rivers; 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, bjoern@2351: km_up NUMBER(38,0), bjoern@2351: name VARCHAR2(255), bjoern@2351: wst_unit_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: CREATE OR REPLACE TRIGGER rivers_trigger BEFORE INSERT ON rivers FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT RIVERS_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: ALTER TABLE rivers ADD CONSTRAINT contraint_units FOREIGN KEY (wst_unit_id) REFERENCES units; bjoern@2351: bjoern@2351: -- TIME_INTERVALS bjoern@2351: CREATE SEQUENCE TIME_INTERVALS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE time_intervals ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: start_time TIMESTAMP, bjoern@2351: stop_time TIMESTAMP, bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: CREATE OR REPLACE TRIGGER time_intervals_trigger BEFORE INSERT ON time_intervals FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT TIME_INTERVALS_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: --- UNITS bjoern@2351: CREATE SEQUENCE UNITS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE units ( bjoern@2351: id NUMBER(10,0) NOT NULL, bjoern@2351: name VARCHAR2(255), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: CREATE OR REPLACE TRIGGER units_trigger BEFORE INSERT ON units FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT UNITS_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; 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, bjoern@2351: wst_column_id NUMBER(38,0), bjoern@2351: wst_q_range_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: CREATE OR REPLACE TRIGGER wst_column_q_ranges_trigger BEFORE INSERT ON wst_column_q_ranges FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT WST_COLUMN_Q_RANGES_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: ALTER TABLE wst_column_q_ranges ADD CONSTRAINT constraint_wst_colums FOREIGN KEY (wst_column_id) REFERENCES wst_columns; bjoern@2351: ALTER TABLE wst_column_q_ranges ADD CONSTRAINT constraint_wst_q_ranges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges; 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, bjoern@2351: position NUMBER(38,2), bjoern@2351: w NUMBER(38,2), bjoern@2351: wst_column_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: CREATE OR REPLACE TRIGGER wst_column_values_trigger BEFORE INSERT ON wst_column_values FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT WST_COLUMN_VALUES_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: ALTER TABLE wst_column_values ADD CONSTRAINT constraint_wst_columns FOREIGN KEY (wst_column_id) REFERENCES wst_columns; 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), bjoern@2351: name VARCHAR2(255), bjoern@2351: position NUMBER(38,0), bjoern@2351: time_interval_id NUMBER(38,0), bjoern@2351: wst_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: CREATE OR REPLACE TRIGGER wst_columns_trigger BEFORE INSERT ON wst_columns FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT WST_COLUMNS_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: ALTER TABLE wst_columns ADD CONSTRAINT constraint_time_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; bjoern@2351: ALTER TABLE wst_columns ADD CONSTRAINT constraint_wsts FOREIGN KEY (wst_id) REFERENCES wsts; 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, bjoern@2351: q NUMBER(38,2), bjoern@2351: range_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: CREATE OR REPLACE TRIGGER wst_q_ranges_trigger BEFORE INSERT ON wst_q_ranges FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT WST_Q_RANGES_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: ALTER TABLE wst_q_ranges ADD CONSTRAINT constraint_ranges FOREIGN KEY (range_id) REFERENCES RANGES; bjoern@2351: bjoern@2351: -- WSTS bjoern@2351: CREATE SEQUENCE WSTS_ID_SEQ; bjoern@2351: bjoern@2351: CREATE TABLE wsts ( bjoern@2351: id NUMBER(38,0) NOT NULL, bjoern@2351: description VARCHAR2(255), bjoern@2351: kind NUMBER(38,0), bjoern@2351: river_id NUMBER(38,0), bjoern@2351: PRIMARY KEY (id) bjoern@2351: ); bjoern@2351: bjoern@2351: bjoern@2351: CREATE OR REPLACE TRIGGER wsts_trigger BEFORE INSERT ON wsts FOR each ROW bjoern@2351: BEGIN bjoern@2351: SELECT WSTS_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2351: END; bjoern@2351: / bjoern@2351: bjoern@2351: ALTER TABLE wsts ADD CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers; bjoern@2351: bjoern@1255: COMMIT;