Mercurial > dive4elements > river

comparison flys-backend/doc/schema/sqlite.sql @ 163:4a3b2912a0cd

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
comparison
equal deleted inserted replaced
162:80669241956c 163:4a3b2912a0cd
64 name VARCHAR(256) NOT NULL UNIQUE, 64 name VARCHAR(256) NOT NULL UNIQUE,
65 type_id INTEGER NOT NULL REFERENCES main_value_types(id), 65 type_id INTEGER NOT NULL REFERENCES main_value_types(id),
66 UNIQUE (name, type_id) 66 UNIQUE (name, type_id)
67 ); 67 );
68 68
69 -- Table for time intervals
70 CREATE TABLE time_intervals (
71 id INTEGER PRIMARY KEY NOT NULL,
72 start_time TIMESTAMP NOT NULL,
73 stop_time TIMESTAMP,
74 CHECK (start_time <= stop_time)
75 );
76
69 -- Stammdaten 77 -- Stammdaten
70 CREATE TABLE main_values ( 78 CREATE TABLE main_values (
71 id INTEGER PRIMARY KEY NOT NULL, 79 id INTEGER PRIMARY KEY NOT NULL,
72 gauge_id INTEGER NOT NULL REFERENCES gauges(id), 80 gauge_id INTEGER NOT NULL REFERENCES gauges(id),
73 named_value_id INTEGER NOT NULL REFERENCES named_main_values(id), 81 named_value_id INTEGER NOT NULL REFERENCES named_main_values(id),
74 value NUMERIC NOT NULL, 82 value NUMERIC NOT NULL,
75 83
76 start_time TIMESTAMP DEFAULT NULL, 84 time_interval_id INTEGER REFERENCES time_intervals(id),
77 stop_time TIMESTAMP DEFAULT NULL,
78 85
79 -- TODO: better checks 86 -- TODO: better checks
80 CHECK (start_time <= stop_time), 87 UNIQUE (gauge_id, named_value_id, time_interval_id)
81 UNIQUE (gauge_id, named_value_id, start_time, stop_time)
82 ); 88 );
83 89
84 -- Abflusstafeln 90 -- Abflusstafeln
85 CREATE TABLE discharge_table ( 91 CREATE TABLE discharge_table (
86 id INTEGER PRIMARY KEY NOT NULL, 92 id INTEGER PRIMARY KEY NOT NULL,
87 gauge_id INTEGER NOT NULL REFERENCES gauges(id), 93 gauge_id INTEGER NOT NULL REFERENCES gauges(id),
88 94
89 start_time TIMESTAMP DEFAULT NULL, 95 time_interval_id INTEGER REFERENCES time_intervals(id),
90 stop_time TIMESTAMP DEFAULT NULL,
91 96
92 -- TODO: better checks 97 -- TODO: better checks
93 CHECK (start_time <= stop_time), 98 UNIQUE (gauge_id, time_interval_id)
94 UNIQUE (gauge_id, start_time, stop_time)
95 ); 99 );
96 100
97 -- Values of the Abflusstafeln 101 -- Values of the Abflusstafeln
98 CREATE TABLE discharge_table_values ( 102 CREATE TABLE discharge_table_values (
99 id INTEGER PRIMARY KEY NOT NULL, 103 id INTEGER PRIMARY KEY NOT NULL,
118 id INTEGER PRIMARY KEY NOT NULL, 122 id INTEGER PRIMARY KEY NOT NULL,
119 wst_id INTEGER NOT NULL REFERENCES wst(id), 123 wst_id INTEGER NOT NULL REFERENCES wst(id),
120 name VARCHAR(256) NOT NULL, 124 name VARCHAR(256) NOT NULL,
121 description VARCHAR, 125 description VARCHAR,
122 126
123 start_time TIMESTAMP DEFAULT NULL, 127 time_interval_id INTEGER REFERENCES time_intervals(id),
124 stop_time TIMESTAMP DEFAULT NULL,
125 128
126 CHECK (start_time <= stop_time),
127 UNIQUE (wst_id, name) 129 UNIQUE (wst_id, name)
128 ); 130 );
129 131
130 -- w values in WST file column 132 -- w values in WST file column
131 CREATE TABLE wst_column_values ( 133 CREATE TABLE wst_column_values (
http://dive4elements.wald.intevation.org