
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;




