changeset 2351:f993b735db71

Adjust oracle schema [I382] flys-backend/trunk@2931 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Bjoern Schilberg <bjoern@intevation.de>
date Mon, 10 Oct 2011 16:20:17 +0000
parents 3eb520b7a70e
children 8126ccb58f28
files flys-backend/ChangeLog flys-backend/doc/schema/oracle.sql
diffstat 2 files changed, 530 insertions(+), 83 deletions(-) [+]
line wrap: on
line diff
--- a/flys-backend/ChangeLog	Mon Oct 10 12:21:50 2011 +0000
+++ b/flys-backend/ChangeLog	Mon Oct 10 16:20:17 2011 +0000
@@ -1,3 +1,8 @@
+2011-10-10  Bjoern Schilberg <bjoern.schilberg@intevation.de>
+
+	* doc/schema/oracle.sql:
+	  Adjust oracle schema [I382].
+
 2011-10-10  Felix Wolfsteller <felix.wolfsteller@intevation.de>
 
 	* src/main/java/de/intevation/flys/importer/ImportWst.java,
--- a/flys-backend/doc/schema/oracle.sql	Mon Oct 10 12:21:50 2011 +0000
+++ b/flys-backend/doc/schema/oracle.sql	Mon Oct 10 16:20:17 2011 +0000
@@ -1,86 +1,528 @@
 BEGIN;
-create table annotation_types (id number(10,0) not null, name varchar2(255), primary key (id));
-create table annotations (id number(10,0) not null, attribute_id number(10,0), edge_id number(10,0), position_id number(10,0), range_id number(10,0), type_id number(10,0), primary key (id));
-create table attributes (id number(10,0) not null, value varchar2(255), primary key (id));
-create table cross_section_lines (id number(10,0) not null, km number(19,2), cross_section_id number(10,0), primary key (id));
-create table cross_section_points (id number(10,0) not null, col_pos number(10,0), x number(19,2), y number(19,2), cross_section_line_id number(10,0), primary key (id));
-create table cross_sections (id number(10,0) not null, description varchar2(255), river_id number(10,0), time_interval_id number(10,0), primary key (id));
-create table discharge_table_values (id number(10,0) not null, q number(19,2), w number(19,2), table_id number(10,0), primary key (id));
-create table discharge_tables (id number(10,0) not null, description varchar2(255), kind number(10,0), gauge_id number(10,0), time_interval_id number(10,0), primary key (id));
-create table edges (id number(10,0) not null, bottom number(19,2), top number(19,2), primary key (id));
-create table gauges (id number(10,0) not null, aeo number(19,2), datum number(19,2), name varchar2(255), station number(19,2), range_id number(10,0), river_id number(10,0), primary key (id));
-create table hyk_entries (id number(10,0) not null, km number(19,2), measure date, hyk_id number(10,0), primary key (id));
-create table hyk_flow_zone_types (id number(10,0) not null, description varchar2(255), name varchar2(255), primary key (id));
-create table hyk_flow_zones (id number(10,0) not null, a number(19,2), b number(19,2), formation_id number(10,0), type_id number(10,0), primary key (id));
-create table hyk_formations (id number(10,0) not null, bottom number(19,2), distance_hf number(19,2), distance_vl number(19,2), distance_vr number(19,2), formation_num number(10,0), top number(19,2), hyk_entry_id number(10,0), primary key (id));
-create table hyks (id number(10,0) not null, description varchar2(255), river_id number(10,0), primary key (id));
-create table main_value_types (id number(10,0) not null, name varchar2(255), primary key (id));
-create table main_values (id number(10,0) not null, value number(19,2), gauge_id number(10,0), named_value_id number(10,0), time_interval_id number(10,0), primary key (id));
-create table named_main_values (id number(10,0) not null, name varchar2(255), type_id number(10,0), primary key (id));
-create table positions (id number(10,0) not null, value varchar2(255), primary key (id));
-create table ranges (id number(10,0) not null, a number(19,2), b number(19,2), river_id number(10,0), primary key (id));
-create table rivers (id number(10,0) not null, km_up number(1,0), name varchar2(255), primary key (id));
-create table time_intervals (id number(10,0) not null, start_time date, stop_time date, primary key (id));
-create table wst_column_q_ranges (id number(10,0) not null, wst_column_id number(10,0), wst_q_range_id number(10,0), primary key (id));
-create table wst_column_values (id number(10,0) not null, position number(19,2), w number(19,2), wst_column_id number(10,0), primary key (id));
-create table wst_columns (id number(10,0) not null, description varchar2(255), name varchar2(255), position number(10,0), time_interval_id number(10,0), wst_id number(10,0), primary key (id));
-create table wst_q_ranges (id number(10,0) not null, q number(19,2), range_id number(10,0), primary key (id));
-create table wsts (id number(10,0) not null, description varchar2(255), kind number(10,0), river_id number(10,0), primary key (id));
-alter table annotations add constraint FKC6AD7B2476703DB3 foreign key (range_id) references ranges;
-alter table annotations add constraint FKC6AD7B24E838461 foreign key (edge_id) references edges;
-alter table annotations add constraint FKC6AD7B24FF598161 foreign key (position_id) references positions;
-alter table annotations add constraint FKC6AD7B24483998D3 foreign key (attribute_id) references attributes;
-alter table annotations add constraint FKC6AD7B245AE11A50 foreign key (type_id) references annotation_types;
-alter table cross_section_lines add constraint FK4072AB66BF9BA960 foreign key (cross_section_id) references cross_sections;
-alter table cross_section_points add constraint FKD5088D5CF337114D foreign key (cross_section_line_id) references cross_section_lines;
-alter table cross_sections add constraint FKC2C43F4D2B365753 foreign key (river_id) references rivers;
-alter table cross_sections add constraint FKC2C43F4D452A8FDC foreign key (time_interval_id) references time_intervals;
-alter table discharge_table_values add constraint FK56DD4B10C1ABE65F foreign key (table_id) references discharge_tables;
-alter table discharge_tables add constraint FKF143882452A8FDC foreign key (time_interval_id) references time_intervals;
-alter table discharge_tables add constraint FKF143882B1A5433 foreign key (gauge_id) references gauges;
-alter table gauges add constraint FKB550CE9A2B365753 foreign key (river_id) references rivers;
-alter table gauges add constraint FKB550CE9A76703DB3 foreign key (range_id) references ranges;
-alter table hyk_entries add constraint FK19EBA6CBEDF9E6D3 foreign key (hyk_id) references hyks;
-alter table hyk_flow_zones add constraint FK22F80BDB3C831087 foreign key (formation_id) references hyk_formations;
-alter table hyk_flow_zones add constraint FK22F80BDB697E0D07 foreign key (type_id) references hyk_flow_zone_types;
-alter table hyk_formations add constraint FK97CFDC71A08D08C foreign key (hyk_entry_id) references hyk_entries;
-alter table hyks add constraint FK311A392B365753 foreign key (river_id) references rivers;
-alter table main_values add constraint FKD85C6268452A8FDC foreign key (time_interval_id) references time_intervals;
-alter table main_values add constraint FKD85C6268B1A5433 foreign key (gauge_id) references gauges;
-alter table main_values add constraint FKD85C6268DE623F5 foreign key (named_value_id) references named_main_values;
-alter table named_main_values add constraint FK5EA68A62CBC256EB foreign key (type_id) references main_value_types;
-alter table ranges add constraint FKC812EED62B365753 foreign key (river_id) references rivers;
-alter table wst_column_q_ranges add constraint FKA28D6C067DA2F764 foreign key (wst_column_id) references wst_columns;
-alter table wst_column_q_ranges add constraint FKA28D6C066A71480F foreign key (wst_q_range_id) references wst_q_ranges;
-alter table wst_column_values add constraint FK992F4F447DA2F764 foreign key (wst_column_id) references wst_columns;
-alter table wst_columns add constraint FKE77A73B6452A8FDC foreign key (time_interval_id) references time_intervals;
-alter table wst_columns add constraint FKE77A73B67425E13 foreign key (wst_id) references wsts;
-alter table wst_q_ranges add constraint FK70E459AB76703DB3 foreign key (range_id) references ranges;
-alter table wsts add constraint FK37D65B2B365753 foreign key (river_id) references rivers;
-create sequence ANNOTATIONS_ID_SEQ;
-create sequence ANNOTATION_TYPES_ID_SEQ;
-create sequence ATTRIBUTES_ID_SEQ;
+-- ANNOTATION_TYPES
+CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;
+
+CREATE TABLE annotation_types (
+    id              NUMBER(38,0) NOT NULL, 
+    name            VARCHAR2(255),
+    PRIMARY KEY     (id)
+);
+
+CREATE OR REPLACE TRIGGER annotation_types_trigger BEFORE INSERT ON annotation_types FOR each ROW
+    BEGIN
+        SELECT ANNOTATION_TYPES_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+-- ANNOTATIONS
+CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
+
+CREATE TABLE annotations (
+    id              NUMBER(38,0) NOT NULL, 
+    attribute_id    NUMBER(38,0),
+    edge_id         NUMBER(38,0),
+    position_id     NUMBER(38,0),
+    range_id        NUMBER(38,0),
+    type_id         NUMBER(38,0),
+    PRIMARY KEY     (id)
+);
+
+CREATE OR REPLACE TRIGGER annotations_trigger BEFORE INSERT ON annotations FOR each ROW
+    BEGIN
+        SELECT ANNOTATIONS_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+ALTER TABLE annotations ADD CONSTRAINT constraint_ranges FOREIGN KEY (range_id) REFERENCES ranges;
+ALTER TABLE annotations ADD CONSTRAINT constraint_edges FOREIGN KEY (edge_id) REFERENCES edges;
+ALTER TABLE annotations ADD CONSTRAINT constraint_positions FOREIGN KEY (position_id) REFERENCES positions;
+ALTER TABLE annotations ADD CONSTRAINT constraint_attributes FOREIGN KEY (attribute_id) REFERENCES attributes;
+ALTER TABLE annotations ADD CONSTRAINT constraint_types FOREIGN KEY (type_id) REFERENCES annotation_types;
+
+-- ATTRIBUTES 
+CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
+
+CREATE TABLE attributes (
+    id              NUMBER(38,0) NOT NULL, 
+    value           VARCHAR2(255), 
+    primary key     (id)
+);
+
+CREATE OR REPLACE TRIGGER attributes_trigger BEFORE INSERT ON attributes FOR each ROW
+    BEGIN
+        SELECT ATTRIBUTES_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+-- 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)
+);
+
+CREATE OR REPLACE TRIGGER cross_section_lines_trigger BEFORE INSERT ON cross_section_lines FOR each ROW
+    BEGIN
+        SELECT CROSS_SECTION_LINES_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+ALTER TABLE cross_section_lines ADD CONSTRAINT constraint_cross_sections FOREIGN KEY (cross_section_id) REFERENCES cross_sections;
+
+-- 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(10,0),
+    PRIMARY KEY             (id)
+);
+
+CREATE OR REPLACE TRIGGER cross_section_points_trigger BEFORE INSERT ON cross_section_points FOR each ROW
+    BEGIN
+        SELECT CROSS_SECTION_POINTS_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+ALTER TABLE cross_section_points ADD CONSTRAINT constraint_cross_section_lines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines;
+
+-- CROSS_SECTIONS
 create sequence CROSS_SECTIONS_ID_SEQ;
-create sequence CROSS_SECTION_LINES_ID_SEQ;
-create sequence CROSS_SECTION_POINTS_ID_SEQ;
-create sequence DISCHARGE_TABLES_ID_SEQ;
-create sequence DISCHARGE_TABLE_VALUES_ID_SEQ;
-create sequence EDGES_ID_SEQ;
-create sequence GAUGES_ID_SEQ;
-create sequence HYKS_ID_SEQ;
-create sequence HYK_ENTRIES_ID_SEQ;
-create sequence HYK_FLOW_ZONES_ID_SEQ;
-create sequence HYK_FLOW_ZONE_TYPES_ID_SEQ;
-create sequence HYK_FORMATIONS_ID_SEQ;
-create sequence MAIN_VALUES_ID_SEQ;
-create sequence MAIN_VALUE_TYPES_ID_SEQ;
-create sequence NAMED_MAIN_VALUES_ID_SEQ;
-create sequence POSITIONS_ID_SEQ;
-create sequence RANGES_ID_SEQ;
-create sequence RIVERS_ID_SEQ;
-create sequence TIME_INTERVALS_ID_SEQ;
-create sequence WSTS_ID_SEQ;
-create sequence WST_COLUMNS_ID_SEQ;
-create sequence WST_COLUMN_VALUES_ID_SEQ;
-create sequence WST_Q_RANGES_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)
+);
+
+CREATE OR REPLACE TRIGGER cross_sections_trigger BEFORE INSERT ON cross_sections FOR each ROW
+    BEGIN
+        SELECT CROSS_SECTIONS_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+ALTER TABLE cross_sections ADD CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;
+ALTER TABLE cross_sections ADD CONSTRAINT constraint_time_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
+
+-- DISCHARGE_TABLE_VALUES
+CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
+
+CREATE TABLE discharge_table_values (
+    id                  NUMBER(38,0) NOT NULL,
+    q                   NUMBER(38,2),
+    w                   NUMBER(38,2),
+    table_id            NUMBER(38,0),
+    PRIMARY KEY         (id)
+);
+
+CREATE OR REPLACE TRIGGER discharge_table_values_trigger BEFORE INSERT ON discharge_table_values FOR each ROW
+    BEGIN
+        SELECT DISCHARGE_TABLES_VALUES_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+ALTER TABLE discharge_table_values ADD CONSTRAINT constraint_discharge_tables foreign key (table_id) REFERENCES discharge_tables;
+
+-- DISCHARGE_TABLES
+CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
+
+CREATE TABLE discharge_tables (
+    id                  NUMBER(38,0) NOT NULL,
+    description         VARCHAR2(255),
+    kind                NUMBER(38,0),
+    gauge_id            NUMBER(38,0),
+    time_interval_id    NUMBER(38,0),
+    PRIMARY KEY         (id)
+);
+
+CREATE OR REPLACE TRIGGER discharge_tables_trigger BEFORE INSERT ON discharge_tables FOR each ROW
+    BEGIN
+        SELECT DISCHARGE_TABLES_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+ALTER TABLE discharge_tables ADD CONSTRAINT constraint_time_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
+ALTER TABLE discharge_tables ADD CONSTRAINT constraint_gauges FOREIGN KEY (gauge_id) REFERENCES gauges;
+
+-- 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)
+);
+
+CREATE OR REPLACE TRIGGER edges_trigger BEFORE INSERT ON edges FOR each ROW
+    BEGIN
+        SELECT EDGES_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+-- GAUGES
+CREATE SEQUENCE GAUGES_ID_SEQ;
+
+CREATE TABLE gauges (
+    id                  NUMBER(38,0) NOT NULL,
+    aeo                 NUMBER(38,2),
+    datum               NUMBER(38,2), 
+    name                VARCHAR2(255),
+    station             NUMBER(38,2),
+    range_id            NUMBER(38,0),
+    river_id            NUMBER(38,0),
+    PRIMARY KEY         (id)
+);
+
+CREATE OR REPLACE TRIGGER gauges_trigger BEFORE INSERT ON gauges FOR each ROW
+    BEGIN
+        SELECT GAUGES_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+ALTER TABLE gauges add CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;
+ALTER TABLE gauges add CONSTRAINT constraint_ranges FOREIGN KEY (range_id) REFERENCES ranges;
+
+-- 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)
+);
+
+CREATE OR REPLACE TRIGGER hyk_entries_trigger BEFORE INSERT ON hyk_entries FOR each ROW
+    BEGIN
+        SELECT HYK_ENTRIES_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+ALTER TABLE hyk_entries ADD CONSTRAINT constraint_hyks FOREIGN KEY (hyk_id) REFERENCES hyks;
+
+-- 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)
+);
+
+CREATE OR REPLACE TRIGGER hyk_flow_zone_types_trigger BEFORE INSERT ON hyk_flow_zone_types FOR each ROW
+    BEGIN
+        SELECT HYK_FLOW_ZONE_TYPES_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+-- 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)
+);
+
+CREATE OR REPLACE TRIGGER hyk_flow_zones_trigger BEFORE INSERT ON hyk_flow_zones FOR each ROW
+    BEGIN
+        SELECT HYK_FLOW_ZONES_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+ALTER TABLE hyk_flow_zones ADD CONSTRAINT constraint_hyk_formations FOREIGN KEY (formation_id) REFERENCES hyk_formations;
+ALTER TABLE hyk_flow_zones ADD CONSTRAINT constraint_hyk_flow_zone_types FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types;
+
+-- 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)
+);
+
+CREATE OR REPLACE TRIGGER hyk_formations_trigger BEFORE INSERT ON hyk_formations FOR each ROW
+    BEGIN
+        SELECT HYK_FORMATIONS_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+ALTER TABLE hyk_formations ADD CONSTRAINT constraint_hyk_entries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries;
+
+-- 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)
+);
+
+CREATE OR REPLACE TRIGGER hyks_trigger BEFORE INSERT ON hyks FOR each ROW
+    BEGIN
+        SELECT HYKS_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+ALTER TABLE hyks ADD CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;
+
+-- MAIN_VALUE_TYPES
+CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
+
+CREATE TABLE main_value_types (
+    id                  NUMBER(38,0) NOT NULL,
+    name                VARCHAR2(255),
+    PRIMARY KEY         (id)
+);
+
+CREATE OR REPLACE TRIGGER main_value_types_trigger BEFORE INSERT ON main_value_types FOR each ROW
+    BEGIN
+        SELECT MAIN_VALUE_TYPES_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+-- MAIN_VALUES
+CREATE SEQUENCE MAIN_VALUES_ID_SEQ;
+
+CREATE TABLE main_values (
+    id                  NUMBER(38,0) NOT NULL,
+    value               NUMBER(38,2),
+    gauge_id            NUMBER(38,0),
+    named_value_id      NUMBER(38,0),
+    time_interval_id    NUMBER(38,0),
+    PRIMARY KEY         (id)
+);
+
+CREATE OR REPLACE TRIGGER main_values_trigger BEFORE INSERT ON main_values FOR each ROW
+    BEGIN
+        SELECT MAIN_VALUES_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+ALTER TABLE main_values ADD CONSTRAINT constraint_time_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
+ALTER TABLE main_values ADD CONSTRAINT constraint_gauges FOREIGN KEY (gauge_id) REFERENCES gauges;
+ALTER TABLE main_values ADD CONSTRAINT constraint_named_main_values FOREIGN KEY (named_value_id) REFERENCES named_main_values;
+
+-- NAMED_MAIN_VALUES
+CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;
+
+CREATE TABLE named_main_values (
+    id                  NUMBER(38,0) NOT NULL,
+    name                VARCHAR2(255),
+    type_id             NUMBER(38,0),
+    PRIMARY KEY (id)
+);
+
+CREATE OR REPLACE TRIGGER named_main_values_trigger BEFORE INSERT ON named_main_values FOR each ROW
+    BEGIN
+        SELECT NAMED_MAIN_VALUES_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+ALTER TABLE named_main_values ADD CONSTRAINT constraint_main_value_types FOREIGN KEY (type_id) REFERENCES main_value_types;
+
+-- POSITIONS
+CREATE SEQUENCE POSITIONS_ID_SEQ;
+
+CREATE TABLE positions (
+    id                  NUMBER(10,0) NOT NULL,
+    value               VARCHAR2(255 char),
+    PRIMARY KEY         (id)
+);
+
+CREATE OR REPLACE TRIGGER positions_trigger BEFORE INSERT ON positions FOR each ROW
+    BEGIN
+        SELECT POSITIONS_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+--- RANGES
+CREATE SEQUENCE RANGES_ID_SEQ;
+
+CREATE TABLE ranges (
+    id                  NUMBER(38,0) NOT NULL,
+    a                   NUMBER(38,2),
+    b                   NUMBER(38,2),
+    river_id            NUMBER(38,0),
+    PRIMARY KEY         (id)
+);
+
+CREATE OR REPLACE TRIGGER ranges_trigger BEFORE INSERT ON ranges FOR each ROW
+    BEGIN
+        SELECT RANGES_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+ALTER TABLE ranges ADD CONSTRAINT contraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;
+
+-- RIVERS
+CREATE SEQUENCE RIVERS_ID_SEQ;
+
+CREATE TABLE rivers (
+    id                  NUMBER(38,0) NOT NULL,
+    km_up               NUMBER(38,0),
+    name                VARCHAR2(255),
+    wst_unit_id         NUMBER(38,0),
+    PRIMARY KEY         (id)
+);
+
+CREATE OR REPLACE TRIGGER rivers_trigger BEFORE INSERT ON rivers FOR each ROW
+    BEGIN
+        SELECT RIVERS_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+ALTER TABLE rivers ADD CONSTRAINT contraint_units FOREIGN KEY (wst_unit_id) REFERENCES units;
+
+-- TIME_INTERVALS
+CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
+
+CREATE TABLE time_intervals (
+    id                  NUMBER(38,0) NOT NULL, 
+    start_time          TIMESTAMP,
+    stop_time           TIMESTAMP,
+    PRIMARY KEY         (id)
+);
+
+CREATE OR REPLACE TRIGGER time_intervals_trigger BEFORE INSERT ON time_intervals FOR each ROW
+    BEGIN
+        SELECT TIME_INTERVALS_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+--- UNITS
+CREATE SEQUENCE UNITS_ID_SEQ;
+
+CREATE TABLE units (
+    id                  NUMBER(10,0) NOT NULL,
+    name                VARCHAR2(255),
+    PRIMARY KEY         (id)
+);
+
+CREATE OR REPLACE TRIGGER units_trigger BEFORE INSERT ON units FOR each ROW
+    BEGIN
+        SELECT UNITS_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+-- 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),
+    wst_q_range_id      NUMBER(38,0),
+    PRIMARY KEY         (id)
+);
+
+CREATE OR REPLACE TRIGGER wst_column_q_ranges_trigger BEFORE INSERT ON wst_column_q_ranges FOR each ROW
+    BEGIN
+        SELECT WST_COLUMN_Q_RANGES_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+ALTER TABLE wst_column_q_ranges ADD CONSTRAINT constraint_wst_colums FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
+ALTER TABLE wst_column_q_ranges ADD CONSTRAINT constraint_wst_q_ranges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges;
+
+-- WST_COLUMN_VALUES
+CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
+
+CREATE TABLE wst_column_values (
+    id                  NUMBER(38,0) NOT NULL,
+    position            NUMBER(38,2),
+    w                   NUMBER(38,2),
+    wst_column_id       NUMBER(38,0),
+    PRIMARY KEY         (id)
+);
+
+CREATE OR REPLACE TRIGGER wst_column_values_trigger BEFORE INSERT ON wst_column_values FOR each ROW
+    BEGIN
+        SELECT WST_COLUMN_VALUES_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+ALTER TABLE wst_column_values ADD CONSTRAINT constraint_wst_columns FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
+
+-- WST_COLUMNS
+CREATE SEQUENCE WST_COLUMNS_ID_SEQ;
+
+CREATE TABLE wst_columns (
+    id                  NUMBER(38,0) NOT NULL,
+    description         VARCHAR2(255),
+    name                VARCHAR2(255),
+    position            NUMBER(38,0),
+    time_interval_id    NUMBER(38,0),
+    wst_id              NUMBER(38,0),
+    PRIMARY KEY         (id)
+);
+
+CREATE OR REPLACE TRIGGER wst_columns_trigger BEFORE INSERT ON wst_columns FOR each ROW
+    BEGIN
+        SELECT WST_COLUMNS_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+ALTER TABLE wst_columns ADD CONSTRAINT constraint_time_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
+ALTER TABLE wst_columns ADD CONSTRAINT constraint_wsts FOREIGN KEY (wst_id) REFERENCES wsts;
+
+-- WST_Q_RANGES
+CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
+
+CREATE TABLE wst_q_ranges (
+    id                  NUMBER(38,0) NOT NULL,
+    q                   NUMBER(38,2),
+    range_id            NUMBER(38,0),
+    PRIMARY KEY         (id)
+);
+
+CREATE OR REPLACE TRIGGER wst_q_ranges_trigger BEFORE INSERT ON wst_q_ranges FOR each ROW
+    BEGIN
+        SELECT WST_Q_RANGES_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+ALTER TABLE wst_q_ranges ADD CONSTRAINT constraint_ranges FOREIGN KEY (range_id) REFERENCES RANGES;
+
+-- WSTS
+CREATE SEQUENCE WSTS_ID_SEQ;
+
+CREATE TABLE wsts (
+    id                  NUMBER(38,0) NOT NULL,
+    description         VARCHAR2(255),
+    kind                NUMBER(38,0),
+    river_id            NUMBER(38,0),
+    PRIMARY KEY         (id)
+);
+
+
+CREATE OR REPLACE TRIGGER wsts_trigger BEFORE INSERT ON wsts FOR each ROW
+    BEGIN
+        SELECT WSTS_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+ALTER TABLE wsts ADD CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;
+
 COMMIT;

http://dive4elements.wald.intevation.org