sascha@161: BEGIN TRANSACTION; sascha@161: sascha@161: -- Gewaesser sascha@161: CREATE TABLE rivers ( sascha@161: id INTEGER PRIMARY KEY NOT NULL, sascha@161: name VARCHAR(256) NOT NULL UNIQUE sascha@161: ); sascha@161: sascha@161: -- Bruecke, Haefen, etc. sascha@161: CREATE TABLE attributes ( sascha@161: id INTEGER PRIMARY KEY NOT NULL, sascha@161: value VARCHAR(256) NOT NULL UNIQUE sascha@161: ); sascha@161: sascha@161: -- segments from/to at a river sascha@161: CREATE TABLE ranges ( sascha@161: id INTEGER PRIMARY KEY NOT NULL, sascha@161: river_id INTEGER NOT NULL REFERENCES river(id), sascha@161: a NUMERIC NOT NULL, sascha@161: b NUMERIC, sascha@161: UNIQUE (river_id, a, b) sascha@161: ); sascha@161: sascha@161: -- Lage 'links', 'rechts', etc. sascha@161: CREATE TABLE positions ( sascha@161: id INTEGER PRIMARY KEY NOT NULL, sascha@161: value VARCHAR(256) NOT NULL UNIQUE sascha@161: ); sascha@161: sascha@161: -- Some object (eg. Hafen) at a segment of river sascha@161: -- plus its position. sascha@161: CREATE TABLE annotations ( sascha@161: id INTEGER PRIMARY KEY NOT NULL, sascha@161: range_id INTEGER NOT NULL REFERENCES ranges(id), sascha@161: attribute_id INTEGER NOT NULL REFERENCES attributes(id), sascha@161: position_id INTEGER REFERENCES positions(id) sascha@161: ); sascha@161: sascha@161: -- Pegel sascha@161: CREATE TABLE gauges ( sascha@161: id INTEGER PRIMARY KEY NOT NULL, sascha@161: name VARCHAR(256) NOT NULL, sascha@161: river_id INTEGER NOT NULL REFERENCES river(id), sascha@161: station NUMERIC NOT NULL UNIQUE, sascha@161: aeo NUMERIC NOT NULL, sascha@161: sascha@161: -- Pegelnullpunkt sascha@161: datum NUMERIC NOT NULL, sascha@161: -- Streckengueltigkeit sascha@161: range_id INTEGER NOT NULL REFERENCES ranges (id), sascha@161: sascha@161: UNIQUE (name, river_id) sascha@161: ); sascha@161: sascha@161: -- Type of a Hauptwert 'W', 'Q', 'D', etc. sascha@161: CREATE TABLE main_value_types ( sascha@161: id INTEGER PRIMARY KEY NOT NULL, sascha@161: name VARCHAR(256) NOT NULL UNIQUE sascha@161: ); sascha@161: sascha@161: -- Named type of a Hauptwert (eg. HQ100) sascha@161: CREATE TABLE named_main_values ( sascha@161: id INTEGER PRIMARY KEY NOT NULL, sascha@161: name VARCHAR(256) NOT NULL UNIQUE, sascha@161: type_id INTEGER NOT NULL REFERENCES main_value_types(id), sascha@161: UNIQUE (name, type_id) sascha@161: ); sascha@161: sascha@161: -- Stammdaten sascha@161: CREATE TABLE main_values ( sascha@161: id INTEGER PRIMARY KEY NOT NULL, sascha@161: gauge_id INTEGER NOT NULL REFERENCES gauges(id), sascha@161: named_value_id INTEGER NOT NULL REFERENCES named_main_values(id), sascha@161: value NUMERIC NOT NULL, sascha@161: sascha@161: start_time TIMESTAMP DEFAULT NULL, sascha@161: stop_time TIMESTAMP DEFAULT NULL, sascha@161: sascha@161: -- TODO: better checks sascha@161: CHECK (start_time <= stop_time), sascha@161: UNIQUE (gauge_id, named_value_id, start_time, stop_time) sascha@161: ); sascha@161: sascha@161: -- Abflusstafeln sascha@161: CREATE TABLE discharge_table ( sascha@161: id INTEGER PRIMARY KEY NOT NULL, sascha@161: gauge_id INTEGER NOT NULL REFERENCES gauges(id), sascha@161: sascha@161: start_time TIMESTAMP DEFAULT NULL, sascha@161: stop_time TIMESTAMP DEFAULT NULL, sascha@161: sascha@161: -- TODO: better checks sascha@161: CHECK (start_time <= stop_time), sascha@161: UNIQUE (gauge_id, start_time, stop_time) sascha@161: ); sascha@161: sascha@161: -- Values of the Abflusstafeln sascha@161: CREATE TABLE discharge_table_values ( sascha@161: id INTEGER PRIMARY KEY NOT NULL, sascha@161: table_id INTEGER NOT NULL REFERENCES discharge_table(id), sascha@161: q NUMERIC NOT NULL, sascha@161: w NUMERIC NOT NULL, sascha@161: sascha@161: UNIQUE (table_id, q, w) sascha@161: ); sascha@161: sascha@161: -- WST files sascha@161: CREATE TABLE wst ( sascha@161: id INTEGER PRIMARY KEY NOT NULL, sascha@161: river_id INTEGER NOT NULL REFERENCES river(id), sascha@161: description VARCHAR(256) NOT NULL, sascha@161: -- TODO: more meta infos sascha@161: UNIQUE (river_id, description) sascha@161: ); sascha@161: sascha@161: -- columns of WST files sascha@161: CREATE TABLE wst_column ( sascha@161: id INTEGER PRIMARY KEY NOT NULL, sascha@161: wst_id INTEGER NOT NULL REFERENCES wst(id), sascha@161: name VARCHAR(256) NOT NULL, sascha@161: description VARCHAR, sascha@161: sascha@161: start_time TIMESTAMP DEFAULT NULL, sascha@161: stop_time TIMESTAMP DEFAULT NULL, sascha@161: sascha@161: CHECK (start_time <= stop_time), sascha@161: UNIQUE (wst_id, name) sascha@161: ); sascha@161: sascha@161: -- w values in WST file column sascha@161: CREATE TABLE wst_column_values ( sascha@161: id INTEGER PRIMARY KEY NOT NULL, sascha@161: wst_column_id INTEGER NOT NULL REFERENCES wst_column(id), sascha@161: position NUMERIC NOT NULL, sascha@161: w NUMERIC NOT NULL, sascha@161: sascha@161: UNIQUE (position, wst_column_id), sascha@161: UNIQUE (position, wst_column_id, w) sascha@161: ); sascha@161: sascha@161: -- bind q values to range sascha@161: CREATE TABLE wst_q_ranges ( sascha@161: id INTEGER PRIMARY KEY NOT NULL, sascha@161: range_id INTEGER NOT NULL REFERENCES ranges(id), sascha@161: q NUMERIC NOT NULL sascha@161: ); sascha@161: sascha@161: -- bind q ranges to wst columns sascha@161: CREATE TABLE wst_column_q_ranges ( sascha@161: id INTEGER PRIMARY KEY NOT NULL, sascha@161: wst_column_id INTEGER NOT NULL REFERENCES wst_column(id), sascha@161: wst_q_range_id INTEGER NOT NULL REFERENCES wst_q_ranges(id), sascha@161: sascha@161: UNIQUE (wst_column_id, wst_q_range_id) sascha@161: ); sascha@161: sascha@161: END TRANSACTION;