Mercurial > dive4elements > river
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;