sascha@164: BEGIN; sascha@164: sascha@164: -- Gewaesser sascha@168: CREATE SEQUENCE RIVERS_ID_SEQ; sascha@168: sascha@164: CREATE TABLE rivers ( sascha@164: id int PRIMARY KEY NOT NULL, sascha@168: name VARCHAR(256) NOT NULL UNIQUE sascha@164: ); sascha@164: sascha@164: -- Bruecke, Haefen, etc. sascha@170: CREATE SEQUENCE ATTRIBUTES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE attributes ( sascha@164: id int PRIMARY KEY NOT NULL, sascha@168: value VARCHAR(256) NOT NULL UNIQUE sascha@164: ); sascha@164: sascha@164: -- segments from/to at a river sascha@170: CREATE SEQUENCE RANGES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE ranges ( sascha@164: id int PRIMARY KEY NOT NULL, sascha@168: river_id int NOT NULL REFERENCES rivers(id), sascha@168: a NUMERIC NOT NULL, sascha@164: b NUMERIC, sascha@164: UNIQUE (river_id, a, b) sascha@164: ); sascha@164: sascha@164: -- Lage 'links', 'rechts', etc. sascha@170: CREATE SEQUENCE POSITIONS_ID_SEQ; sascha@170: sascha@164: CREATE TABLE positions ( sascha@164: id int PRIMARY KEY NOT NULL, sascha@168: value VARCHAR(256) NOT NULL UNIQUE sascha@164: ); sascha@164: sascha@164: -- Some object (eg. Hafen) at a segment of river sascha@164: -- plus its position. sascha@170: CREATE SEQUENCE ANNOTATIONS_ID_SEQ; sascha@170: sascha@164: CREATE TABLE annotations ( sascha@164: id int PRIMARY KEY NOT NULL, sascha@168: range_id int NOT NULL REFERENCES ranges(id), sascha@168: attribute_id int NOT NULL REFERENCES attributes(id), sascha@164: position_id int REFERENCES positions(id) sascha@164: ); sascha@164: sascha@164: -- Pegel sascha@170: CREATE SEQUENCE GAUGES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE gauges ( sascha@168: id int PRIMARY KEY NOT NULL, sascha@168: name VARCHAR(256) NOT NULL, sascha@168: river_id int NOT NULL REFERENCES rivers(id), sascha@168: station NUMERIC NOT NULL UNIQUE, sascha@168: aeo NUMERIC NOT NULL, sascha@164: sascha@164: -- Pegelnullpunkt sascha@168: datum NUMERIC NOT NULL, sascha@164: -- Streckengueltigkeit sascha@168: range_id int NOT NULL REFERENCES ranges (id), sascha@164: sascha@167: UNIQUE (name, river_id), sascha@194: UNIQUE (river_id, station) sascha@164: ); sascha@164: sascha@164: -- Type of a Hauptwert 'W', 'Q', 'D', etc. sascha@170: CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE main_value_types ( sascha@168: id int PRIMARY KEY NOT NULL, sascha@168: name VARCHAR(256) NOT NULL UNIQUE sascha@164: ); sascha@164: sascha@164: -- Named type of a Hauptwert (eg. HQ100) sascha@170: CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE named_main_values ( sascha@168: id int PRIMARY KEY NOT NULL, sascha@168: name VARCHAR(256) NOT NULL UNIQUE, sascha@168: type_id int NOT NULL REFERENCES main_value_types(id), sascha@164: UNIQUE (name, type_id) sascha@164: ); sascha@164: sascha@164: -- Table for time intervals sascha@170: CREATE SEQUENCE TIME_INTERVALS_ID_SEQ; sascha@170: sascha@164: CREATE TABLE time_intervals ( sascha@164: id int PRIMARY KEY NOT NULL, sascha@168: start_time TIMESTAMP NOT NULL, sascha@164: stop_time TIMESTAMP, sascha@164: CHECK (start_time <= stop_time) sascha@164: ); sascha@164: sascha@164: -- Stammdaten sascha@170: CREATE SEQUENCE MAIN_VALUES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE main_values ( sascha@168: id int PRIMARY KEY NOT NULL, sascha@168: gauge_id int NOT NULL REFERENCES gauges(id), sascha@168: named_value_id int NOT NULL REFERENCES named_main_values(id), sascha@168: value NUMERIC NOT NULL, sascha@164: sascha@164: time_interval_id int REFERENCES time_intervals(id), sascha@164: sascha@164: -- TODO: better checks sascha@164: UNIQUE (gauge_id, named_value_id, time_interval_id) sascha@164: ); sascha@164: sascha@164: -- Abflusstafeln sascha@170: CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ; sascha@170: sascha@167: CREATE TABLE discharge_tables ( sascha@164: id int PRIMARY KEY NOT NULL, sascha@164: gauge_id int NOT NULL REFERENCES gauges(id), sascha@467: kind int NOT NULL DEFAULT 0 sascha@164: time_interval_id int REFERENCES time_intervals(id), sascha@164: sascha@164: -- TODO: better checks sascha@164: UNIQUE (gauge_id, time_interval_id) sascha@164: ); sascha@164: sascha@164: -- Values of the Abflusstafeln sascha@170: CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE discharge_table_values ( sascha@168: id int PRIMARY KEY NOT NULL, sascha@168: table_id int NOT NULL REFERENCES discharge_tables(id), sascha@168: q NUMERIC NOT NULL, sascha@168: w NUMERIC NOT NULL, sascha@164: sascha@164: UNIQUE (table_id, q, w) sascha@164: ); sascha@164: sascha@164: -- WST files sascha@171: CREATE SEQUENCE WSTS_ID_SEQ; sascha@170: sascha@171: CREATE TABLE wsts ( sascha@168: id int PRIMARY KEY NOT NULL, sascha@168: river_id int NOT NULL REFERENCES rivers(id), sascha@168: description VARCHAR(256) NOT NULL, sascha@467: kind int NOT NULL DEFAULT 0 sascha@164: -- TODO: more meta infos sascha@164: UNIQUE (river_id, description) sascha@164: ); sascha@164: sascha@164: -- columns of WST files sascha@171: CREATE SEQUENCE WST_COLUMNS_ID_SEQ; sascha@170: sascha@171: CREATE TABLE wst_columns ( sascha@168: id int PRIMARY KEY NOT NULL, sascha@173: wst_id int NOT NULL REFERENCES wsts(id), sascha@168: name VARCHAR(256) NOT NULL, sascha@174: description VARCHAR(256), sascha@164: sascha@164: time_interval_id int REFERENCES time_intervals(id), sascha@164: sascha@164: UNIQUE (wst_id, name) sascha@164: ); sascha@164: sascha@164: -- w values in WST file column sascha@170: CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE wst_column_values ( sascha@168: id int PRIMARY KEY NOT NULL, sascha@171: wst_column_id int NOT NULL REFERENCES wst_columns(id), sascha@168: position NUMERIC NOT NULL, sascha@168: w NUMERIC NOT NULL, sascha@164: sascha@164: UNIQUE (position, wst_column_id), sascha@164: UNIQUE (position, wst_column_id, w) sascha@164: ); sascha@164: sascha@164: -- bind q values to range sascha@170: CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE wst_q_ranges ( sascha@168: id int PRIMARY KEY NOT NULL, sascha@168: range_id int NOT NULL REFERENCES ranges(id), sascha@168: q NUMERIC NOT NULL sascha@164: ); sascha@164: sascha@164: -- bind q ranges to wst columns sascha@170: CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ; sascha@170: sascha@164: CREATE TABLE wst_column_q_ranges ( sascha@164: id int PRIMARY KEY NOT NULL, sascha@173: wst_column_id int NOT NULL REFERENCES wst_columns(id), sascha@164: wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id), sascha@164: sascha@164: UNIQUE (wst_column_id, wst_q_range_id) sascha@164: ); sascha@164: sascha@164: COMMIT;