sascha@164: BEGIN; sascha@164: sascha@164: -- Gewaesser sascha@168: CREATE SEQUENCE RIVERS_ID_SEQ; sascha@168: sascha@164: CREATE TABLE rivers ( sascha@505: id int PRIMARY KEY NOT NULL, sascha@505: name VARCHAR(256) NOT NULL UNIQUE, sascha@505: km_up BOOLEAN NOT NULL DEFAULT true 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@758: -- Kante 'oben', 'unten' sascha@758: CREATE SEQUENCE EDGES_ID_SEQ; sascha@758: sascha@758: CREATE TABLE edges ( sascha@758: id int PRIMARY KEY NOT NULL, sascha@758: top NUMERIC, sascha@758: bottom NUMERIC sascha@758: ); sascha@758: sascha@763: -- Types of annotatations (Hafen, Bruecke, Zufluss, ...) sascha@763: CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ; sascha@763: sascha@763: CREATE TABLE annotation_types ( sascha@763: id int PRIMARY KEY NOT NULL, sascha@763: name VARCHAR(256) NOT NULL UNIQUE sascha@763: ); sascha@763: 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@758: position_id int REFERENCES positions(id), sascha@763: edge_id int REFERENCES edges(id), sascha@763: type_id int REFERENCES annotation_types(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@493: description VARCHAR(256) NOT NULL, sascha@470: kind int NOT NULL DEFAULT 0, sascha@496: time_interval_id int REFERENCES time_intervals(id) sascha@164: sascha@164: -- TODO: better checks sascha@496: -- UNIQUE (gauge_id, kind, 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@470: 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@471: position int NOT NULL DEFAULT 0, sascha@164: sascha@164: time_interval_id int REFERENCES time_intervals(id), sascha@164: sascha@471: UNIQUE (wst_id, name), sascha@471: UNIQUE (wst_id, position) 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@476: CREATE VIEW wst_value_table AS sascha@476: SELECT wcv.position AS position, sascha@476: w, sascha@476: (SELECT q sascha@476: FROM wst_column_q_ranges wcqr sascha@476: JOIN wst_q_ranges wqr sascha@476: ON wcqr.wst_q_range_id = wqr.id sascha@476: JOIN ranges r sascha@476: ON r.id = wqr.range_id sascha@476: WHERE wcqr.wst_column_id = wc.id sascha@476: AND wcv.position BETWEEN r.a AND r.b) AS q, sascha@476: wc.position AS column_pos, sascha@476: w.id AS wst_id sascha@476: FROM wst_column_values wcv sascha@476: JOIN wst_columns wc sascha@476: ON wcv.wst_column_id = wc.id sascha@476: JOIN wsts w sascha@476: ON wc.wst_id = w.id sascha@476: ORDER BY wcv.position ASC, sascha@476: wc.position DESC; sascha@476: sascha@754: -- view to select the w values of a WST sascha@754: CREATE VIEW wst_w_values AS sascha@754: SELECT wcv."position" AS km, sascha@754: wcv.w AS w, sascha@754: wc."position" AS column_pos, sascha@754: w.id AS wst_id sascha@754: FROM wst_column_values wcv sascha@754: JOIN wst_columns wc ON wcv.wst_column_id = wc.id sascha@754: JOIN wsts w ON wc.wst_id = w.id sascha@754: ORDER BY wcv."position", wc."position"; sascha@754: sascha@755: -- view to select the q values of a WST sascha@755: CREATE VIEW wst_q_values AS sascha@755: SELECT wc.position AS column_pos, sascha@755: wqr.q AS q, sascha@755: r.a AS a, sascha@755: r.b AS b, sascha@755: wc.wst_id AS wst_id sascha@755: FROM wst_column_q_ranges wcqr sascha@755: JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id sascha@755: JOIN ranges r ON wqr.range_id = r.id sascha@755: JOIN wst_columns wc ON wcqr.wst_column_id = wc.id sascha@755: ORDER BY wc.position, wcqr.wst_column_id, r.a; sascha@755: sascha@164: COMMIT;