
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: 
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: 
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: 
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: 
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@2353:     cross_section_line_id   NUMBER(38,0),
bjoern@2351:     PRIMARY KEY             (id)
bjoern@2351: );
bjoern@2351: 
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: 
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: 
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: 
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,
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: 
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,
bjoern@2351:     name                VARCHAR2(255),
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,
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: 
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: 
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,
bjoern@2351:     value               VARCHAR2(255 char),
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,
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: 
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: 
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: 
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,
bjoern@2351:     name                VARCHAR2(255),
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,
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: 
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: 
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: 
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: 
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@2352: -- ADD CONSTRAINTs
bjoern@2352: ALTER TABLE annotations ADD CONSTRAINT cAnnotationsRanges FOREIGN KEY (range_id) REFERENCES ranges;
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 cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes;
bjoern@2352: ALTER TABLE annotations ADD CONSTRAINT cAnnotationsTypes FOREIGN KEY (type_id) REFERENCES annotation_types;
bjoern@2352: ALTER TABLE cross_section_lines ADD CONSTRAINT cQPSLinesCrossSections FOREIGN KEY (cross_section_id) REFERENCES cross_sections;
bjoern@2352: ALTER TABLE cross_section_points ADD CONSTRAINT cQPSPointsCrossSectionLines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines;
bjoern@2352: ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsRivers FOREIGN KEY (river_id) REFERENCES rivers;
bjoern@2352: ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
bjoern@2352: ALTER TABLE discharge_table_values ADD CONSTRAINT cTableValuesDischargeTables foreign key (table_id) REFERENCES discharge_tables;
bjoern@2352: ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
bjoern@2352: ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesGauges FOREIGN KEY (gauge_id) REFERENCES gauges;
bjoern@2352: ALTER TABLE gauges ADD CONSTRAINT cGaugesRivers FOREIGN KEY (river_id) REFERENCES rivers;
bjoern@2352: ALTER TABLE gauges ADD CONSTRAINT cGaugesRanges FOREIGN KEY (range_id) REFERENCES ranges;
bjoern@2352: ALTER TABLE hyk_entries ADD CONSTRAINT cHykEntriesHyks FOREIGN KEY (hyk_id) REFERENCES hyks;
bjoern@2352: ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFormations FOREIGN KEY (formation_id) REFERENCES hyk_formations;
bjoern@2352: ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFlowZoneTypes FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types;
bjoern@2352: ALTER TABLE hyks ADD CONSTRAINT cHyksRivers FOREIGN KEY (river_id) REFERENCES rivers;
bjoern@2352: ALTER TABLE hyk_formations ADD CONSTRAINT cHykFormationsHykEntries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries;
bjoern@2352: ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
bjoern@2352: ALTER TABLE main_values ADD CONSTRAINT cMainValuesGauges FOREIGN KEY (gauge_id) REFERENCES gauges;
bjoern@2352: ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values;
bjoern@2352: ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types;
bjoern@2352: ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers;
bjoern@2352: ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units;
bjoern@2352: ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
bjoern@2352: ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges;
bjoern@2352: ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
bjoern@2352: ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
bjoern@2352: ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts;
bjoern@2352: ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES;
bjoern@2352: ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers;




