Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-minfo.sql @ 2858:c3b2673eafbf

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Adjusted PostgreSQL port of MINFO schema to match Oracle one. flys-backend/trunk@4322 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Fri, 27 Apr 2012 15:47:00 +0000
parents 13b3fcaa1b34
children 8f232119011e
comparison
equal deleted inserted replaced
2857:5dfe06f149f3 2858:c3b2673eafbf
166 description VARCHAR(256), 166 description VARCHAR(256),
167 PRIMARY KEY(id), 167 PRIMARY KEY(id),
168 CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) 168 CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id)
169 ); 169 );
170 170
171
172 CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ;
173
174 CREATE TABLE discharge_zone (
175 id int NOT NULL,
176 river_id int NOT NULL,
177 gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance
178 value NUMERIC NOT NULL,
179 lower_discharge VARCHAR(16) NOT NULL,
180 upper_discharge VARCHAR(16),
181 PRIMARY KEY(id),
182 CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id)
183 );
184
185
186 CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ;
187
188 CREATE TABLE flow_velocity_model (
189 id int NOT NULL,
190 river_id int NOT NULL,
191 discharge_zone_id int NOT NULL,
192 description VARCHAR(256),
193 PRIMARY KEY (id),
194 CONSTRAINT fk_fvm_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
195 CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id)
196 );
197
198
199 CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ;
200
201 CREATE TABLE flow_velocity_model_values (
202 id int NOT NULL,
203 flow_velocity_model_id int NOT NULL,
204 station NUMERIC NOT NULL,
205 q NUMERIC NOT NULL,
206 total_channel NUMERIC NOT NULL,
207 main_channel NUMERIC NOT NULL,
208 shear_stress NUMERIC NOT NULL,
209 PRIMARY KEY(id),
210 CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id)
211 );
212
213
214
215 CREATE SEQUENCE FV_MEASURE_ID_SEQ;
216
217 CREATE TABLE flow_velocity_measurements (
218 id int NOT NULL,
219 river_id int NOT NULL,
220 description VARCHAR(256),
221 PRIMARY KEY (id),
222 CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id)
223 );
224
225 CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ;
226
227 CREATE TABLE flow_velocity_measure_values (
228 id int NOT NULL,
229 measurements_id int NOT NULL,
230 station NUMERIC NOT NULL,
231 datetime TIMESTAMP,
232 w NUMERIC NOT NULL,
233 q NUMERIC NOT NULL,
234 v NUMERIC NOT NULL,
235 description VARCHAR(256),
236 PRIMARY KEY (id),
237 CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id)
238 );
239
240
241 CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ;
242
243 CREATE TABLE grain_fraction (
244 id int NOT NULL,
245 name VARCHAR(64) NOT NULL,
246 lower NUMERIC,
247 upper NUMERIC,
248 unit_id int,
249 PRIMARY KEY (id),
250 CONSTRAINT fk_gf_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
251 );
252
253
254 CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ;
255
256 CREATE TABLE sediment_yield (
257 id int NOT NULL,
258 river_id int NOT NULL,
259 grain_fraction_id int,
260 unit_id int NOT NULL,
261 time_interval_id int NOT NULL,
262 description VARCHAR(256),
263 PRIMARY KEY (id),
264 CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
265 CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id),
266 CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id),
267 CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
268 );
269
270
271 CREATE SEQUENCE SEDIMENT_YIELD_VALUES_ID_SEQ;
272
273 CREATE TABLE sediment_yield_values (
274 id int NOT NULL,
275 sediment_yield_id int NOT NULL,
276 station NUMERIC NOT NULL,
277 value NUMERIC NOT NULL,
278 PRIMARY KEY (id),
279 CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id)
280 );
281
282
283 CREATE SEQUENCE WATERLEVEL_ID_SEQ;
284
285 CREATE TABLE waterlevel (
286 id int NOT NULL,
287 river_id int NOT NULL,
288 unit_id int NOT NULL,
289 description VARCHAR(256),
290 PRIMARY KEY (id),
291 CONSTRAINT fk_w_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
292 CONSTRAINT fk_w_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
293 );
294
295
296 CREATE SEQUENCE WATERLEVEL_Q_RANGES_ID_SEQ;
297
298 CREATE TABLE waterlevel_q_range (
299 id int NOT NULL,
300 waterlevel_id int NOT NULL,
301 q NUMERIC NOT NULL,
302 PRIMARY KEY (id),
303 CONSTRAINT fk_wqr_waterlevel_id FOREIGN KEY (waterlevel_id) REFERENCES waterlevel(id)
304 );
305
306
307 CREATE SEQUENCE WATERLEVEL_VALUES_ID_SEQ;
308
309 CREATE TABLE waterlevel_values(
310 id int NOT NULL,
311 waterlevel_q_range_id int NOT NULL,
312 station NUMERIC NOT NULL,
313 w NUMERIC NOT NULL,
314 PRIMARY KEY (id),
315 CONSTRAINT fk_wv_waterlevel_q_range_id FOREIGN KEY (waterlevel_q_range_id) REFERENCES waterlevel_q_range(id)
316 );
317
318
319 CREATE SEQUENCE WATERLEVEL_DIFFERENCE_ID_SEQ;
320
321 CREATE TABLE waterlevel_difference (
322 id int NOT NULL,
323 river_id int NOT NULL,
324 unit_id int NOT NULL,
325 description VARCHAR(256),
326 PRIMARY KEY (id),
327 CONSTRAINT fk_wd_river_id FOREIGN KEY (river_id) REFERENCES rivers (id),
328 CONSTRAINT fk_wd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
329 );
330
331
332 CREATE SEQUENCE WATERLEVEL_DIFF_COLUMN_ID_SEQ;
333
334 CREATE TABLE waterlevel_difference_column (
335 id int NOT NULL,
336 difference_id int NOT NULL,
337 description VARCHAR(256),
338 PRIMARY KEY (id),
339 CONSTRAINT fk_wdc_difference_id FOREIGN KEY (difference_id) REFERENCES waterlevel_difference (id)
340 );
341
342
343 CREATE SEQUENCE WATERLEVEL_DIFF_VALUES_ID_SEQ;
344
345 CREATE TABLE waterlevel_difference_values (
346 id int NOT NULL,
347 column_id int NOT NULL,
348 station NUMERIC NOT NULL,
349 value NUMERIC NOT NULL,
350 PRIMARY KEY (id),
351 CONSTRAINT fk_wdv_column_id FOREIGN KEY (column_id) REFERENCES waterlevel_difference_column (id)
352 );
353
171 COMMIT; 354 COMMIT;
http://dive4elements.wald.intevation.org