Mercurial > dive4elements > river
diff flys-backend/doc/schema/postgresql.sql @ 168:86a1bd9cc50e
More Hibernate/JPA stuff
flys-backend/trunk@1458 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Fri, 11 Mar 2011 14:41:18 +0000 |
parents | 15d515fe15f5 |
children | 88c14d5d45be |
line wrap: on
line diff
--- a/flys-backend/doc/schema/postgresql.sql Thu Mar 10 13:44:51 2011 +0000 +++ b/flys-backend/doc/schema/postgresql.sql Fri Mar 11 14:41:18 2011 +0000 @@ -1,22 +1,24 @@ BEGIN; -- Gewaesser +CREATE SEQUENCE RIVERS_ID_SEQ; + CREATE TABLE rivers ( id int PRIMARY KEY NOT NULL, - name VARCHAR(256) NOT NULL UNIQUE + name VARCHAR(256) NOT NULL UNIQUE ); -- Bruecke, Haefen, etc. CREATE TABLE attributes ( id int PRIMARY KEY NOT NULL, - value VARCHAR(256) NOT NULL UNIQUE + value VARCHAR(256) NOT NULL UNIQUE ); -- segments from/to at a river CREATE TABLE ranges ( id int PRIMARY KEY NOT NULL, - river_id int NOT NULL REFERENCES rivers(id), - a NUMERIC NOT NULL, + river_id int NOT NULL REFERENCES rivers(id), + a NUMERIC NOT NULL, b NUMERIC, UNIQUE (river_id, a, b) ); @@ -24,30 +26,30 @@ -- Lage 'links', 'rechts', etc. CREATE TABLE positions ( id int PRIMARY KEY NOT NULL, - value VARCHAR(256) NOT NULL UNIQUE + value VARCHAR(256) NOT NULL UNIQUE ); -- Some object (eg. Hafen) at a segment of river -- plus its position. CREATE TABLE annotations ( id int PRIMARY KEY NOT NULL, - range_id int NOT NULL REFERENCES ranges(id), - attribute_id int NOT NULL REFERENCES attributes(id), + range_id int NOT NULL REFERENCES ranges(id), + attribute_id int NOT NULL REFERENCES attributes(id), position_id int REFERENCES positions(id) ); -- Pegel CREATE TABLE gauges ( - id int PRIMARY KEY NOT NULL, - name VARCHAR(256) NOT NULL, - river_id int NOT NULL REFERENCES rivers(id), - station NUMERIC NOT NULL UNIQUE, - aeo NUMERIC NOT NULL, + id int PRIMARY KEY NOT NULL, + name VARCHAR(256) NOT NULL, + river_id int NOT NULL REFERENCES rivers(id), + station NUMERIC NOT NULL UNIQUE, + aeo NUMERIC NOT NULL, -- Pegelnullpunkt - datum NUMERIC NOT NULL, + datum NUMERIC NOT NULL, -- Streckengueltigkeit - range_id int NOT NULL REFERENCES ranges (id), + range_id int NOT NULL REFERENCES ranges (id), UNIQUE (name, river_id), UNIQUE (river_id, datum) @@ -55,32 +57,32 @@ -- Type of a Hauptwert 'W', 'Q', 'D', etc. CREATE TABLE main_value_types ( - id int PRIMARY KEY NOT NULL, - name VARCHAR(256) NOT NULL UNIQUE + id int PRIMARY KEY NOT NULL, + name VARCHAR(256) NOT NULL UNIQUE ); -- Named type of a Hauptwert (eg. HQ100) CREATE TABLE named_main_values ( - id int PRIMARY KEY NOT NULL, - name VARCHAR(256) NOT NULL UNIQUE, - type_id int NOT NULL REFERENCES main_value_types(id), + id int PRIMARY KEY NOT NULL, + name VARCHAR(256) NOT NULL UNIQUE, + type_id int NOT NULL REFERENCES main_value_types(id), UNIQUE (name, type_id) ); -- Table for time intervals CREATE TABLE time_intervals ( id int PRIMARY KEY NOT NULL, - start_time TIMESTAMP NOT NULL, + start_time TIMESTAMP NOT NULL, stop_time TIMESTAMP, CHECK (start_time <= stop_time) ); -- Stammdaten CREATE TABLE main_values ( - id int PRIMARY KEY NOT NULL, - gauge_id int NOT NULL REFERENCES gauges(id), - named_value_id int NOT NULL REFERENCES named_main_values(id), - value NUMERIC NOT NULL, + id int PRIMARY KEY NOT NULL, + gauge_id int NOT NULL REFERENCES gauges(id), + named_value_id int NOT NULL REFERENCES named_main_values(id), + value NUMERIC NOT NULL, time_interval_id int REFERENCES time_intervals(id), @@ -101,29 +103,29 @@ -- Values of the Abflusstafeln CREATE TABLE discharge_table_values ( - id int PRIMARY KEY NOT NULL, - table_id int NOT NULL REFERENCES discharge_tables(id), - q NUMERIC NOT NULL, - w NUMERIC NOT NULL, + id int PRIMARY KEY NOT NULL, + table_id int NOT NULL REFERENCES discharge_tables(id), + q NUMERIC NOT NULL, + w NUMERIC NOT NULL, UNIQUE (table_id, q, w) ); -- WST files CREATE TABLE wst ( - id int PRIMARY KEY NOT NULL, - river_id int NOT NULL REFERENCES rivers(id), - description VARCHAR(256) NOT NULL, + id int PRIMARY KEY NOT NULL, + river_id int NOT NULL REFERENCES rivers(id), + description VARCHAR(256) NOT NULL, -- TODO: more meta infos UNIQUE (river_id, description) ); -- columns of WST files CREATE TABLE wst_column ( - id int PRIMARY KEY NOT NULL, - wst_id int NOT NULL REFERENCES wst(id), - name VARCHAR(256) NOT NULL, - description VARCHAR, + id int PRIMARY KEY NOT NULL, + wst_id int NOT NULL REFERENCES wst(id), + name VARCHAR(256) NOT NULL, + description VARCHAR, time_interval_id int REFERENCES time_intervals(id), @@ -132,10 +134,10 @@ -- w values in WST file column CREATE TABLE wst_column_values ( - id int PRIMARY KEY NOT NULL, - wst_column_id int NOT NULL REFERENCES wst_column(id), - position NUMERIC NOT NULL, - w NUMERIC NOT NULL, + id int PRIMARY KEY NOT NULL, + wst_column_id int NOT NULL REFERENCES wst_column(id), + position NUMERIC NOT NULL, + w NUMERIC NOT NULL, UNIQUE (position, wst_column_id), UNIQUE (position, wst_column_id, w) @@ -143,9 +145,9 @@ -- bind q values to range CREATE TABLE wst_q_ranges ( - id int PRIMARY KEY NOT NULL, - range_id int NOT NULL REFERENCES ranges(id), - q NUMERIC NOT NULL + id int PRIMARY KEY NOT NULL, + range_id int NOT NULL REFERENCES ranges(id), + q NUMERIC NOT NULL ); -- bind q ranges to wst columns