Mercurial > dive4elements > river
diff flys-backend/doc/schema/sqlite.sql @ 163:4a3b2912a0cd
Factorized time intervals out into a separated table.
flys-backend/trunk@1432 c6561f87-3c4e-4783-a992-168aeb5c3f6f
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Wed, 09 Mar 2011 09:41:01 +0000 |
| parents | 119048655872 |
| children | 324e12ab7a05 |
line wrap: on
line diff
--- a/flys-backend/doc/schema/sqlite.sql Tue Feb 22 10:34:53 2011 +0000 +++ b/flys-backend/doc/schema/sqlite.sql Wed Mar 09 09:41:01 2011 +0000 @@ -66,32 +66,36 @@ UNIQUE (name, type_id) ); +-- Table for time intervals +CREATE TABLE time_intervals ( + id INTEGER PRIMARY KEY NOT NULL, + start_time TIMESTAMP NOT NULL, + stop_time TIMESTAMP, + CHECK (start_time <= stop_time) +); + -- Stammdaten CREATE TABLE main_values ( - id INTEGER PRIMARY KEY NOT NULL, - gauge_id INTEGER NOT NULL REFERENCES gauges(id), - named_value_id INTEGER NOT NULL REFERENCES named_main_values(id), - value NUMERIC NOT NULL, + id INTEGER PRIMARY KEY NOT NULL, + gauge_id INTEGER NOT NULL REFERENCES gauges(id), + named_value_id INTEGER NOT NULL REFERENCES named_main_values(id), + value NUMERIC NOT NULL, - start_time TIMESTAMP DEFAULT NULL, - stop_time TIMESTAMP DEFAULT NULL, + time_interval_id INTEGER REFERENCES time_intervals(id), -- TODO: better checks - CHECK (start_time <= stop_time), - UNIQUE (gauge_id, named_value_id, start_time, stop_time) + UNIQUE (gauge_id, named_value_id, time_interval_id) ); -- Abflusstafeln CREATE TABLE discharge_table ( - id INTEGER PRIMARY KEY NOT NULL, - gauge_id INTEGER NOT NULL REFERENCES gauges(id), + id INTEGER PRIMARY KEY NOT NULL, + gauge_id INTEGER NOT NULL REFERENCES gauges(id), - start_time TIMESTAMP DEFAULT NULL, - stop_time TIMESTAMP DEFAULT NULL, + time_interval_id INTEGER REFERENCES time_intervals(id), -- TODO: better checks - CHECK (start_time <= stop_time), - UNIQUE (gauge_id, start_time, stop_time) + UNIQUE (gauge_id, time_interval_id) ); -- Values of the Abflusstafeln @@ -120,10 +124,8 @@ name VARCHAR(256) NOT NULL, description VARCHAR, - start_time TIMESTAMP DEFAULT NULL, - stop_time TIMESTAMP DEFAULT NULL, + time_interval_id INTEGER REFERENCES time_intervals(id), - CHECK (start_time <= stop_time), UNIQUE (wst_id, name) );