Mercurial > dive4elements > river

comparison backend/doc/schema/postgresql-minfo.sql @ 8412:17db08570637

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
author Tom Gottfried <tom@intevation.de>
date Wed, 15 Oct 2014 19:20:26 +0200
parents 5f28aa1be795
children ffbeffe2d266
comparison
equal deleted inserted replaced
8411:b8c6cb36607e 8412:17db08570637
308 308
309 309
310 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ; 310 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ;
311 CREATE TABLE measurement_station ( 311 CREATE TABLE measurement_station (
312 id int NOT NULL, 312 id int NOT NULL,
313 range_id int NOT NULL,
314 reference_gauge_id int,
315 time_interval_id int,
313 name VARCHAR(256) NOT NULL, 316 name VARCHAR(256) NOT NULL,
314 river_id int NOT NULL,
315 station NUMERIC NOT NULL,
316 range_id int,
317 measurement_type VARCHAR(64) NOT NULL, 317 measurement_type VARCHAR(64) NOT NULL,
318 riverside VARCHAR(16), 318 riverside VARCHAR(16),
319 reference_gauge_id int,
320 -- store name of reference gauges here too, as not all are in gauges 319 -- store name of reference gauges here too, as not all are in gauges
321 reference_gauge_name VARCHAR(64), 320 reference_gauge_name VARCHAR(64),
322 observation_timerange_id int,
323 operator VARCHAR(64), 321 operator VARCHAR(64),
324 description VARCHAR(512), 322 comment VARCHAR(512),
325 PRIMARY KEY (id), 323 PRIMARY KEY (id),
326 CONSTRAINT fk_ms_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, 324 CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id)
327 CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE, 325 REFERENCES ranges(id) ON DELETE CASCADE,
328 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id) ON DELETE CASCADE, 326 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id)
329 CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id) 327 REFERENCES gauges(id) ON DELETE CASCADE,
328 CONSTRAINT fk_time_interval_id FOREIGN KEY (time_interval_id)
329 REFERENCES time_intervals(id)
330 ); 330 );
331 331
332 332
333 CREATE SEQUENCE SEDIMENT_LOAD_ID_SEQ; 333 CREATE SEQUENCE SEDIMENT_LOAD_ID_SEQ;
334 334
372 id int NOT NULL, 372 id int NOT NULL,
373 river_id int NOT NULL, 373 river_id int NOT NULL,
374 time_interval_id int NOT NULL, 374 time_interval_id int NOT NULL,
375 description VARCHAR(256), 375 description VARCHAR(256),
376 PRIMARY KEY (id), 376 PRIMARY KEY (id),
377 CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, 377 CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id)
378 CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) 378 REFERENCES rivers(id) ON DELETE CASCADE,
379 CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id)
380 REFERENCES time_intervals(id)
379 ); 381 );
380 382
381 383
382 CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ; 384 CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ;
383 385
393 ntot int, 395 ntot int,
394 noutl int, 396 noutl int,
395 cferguson NUMERIC, 397 cferguson NUMERIC,
396 cduan NUMERIC, 398 cduan NUMERIC,
397 PRIMARY KEY (id), 399 PRIMARY KEY (id),
398 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) ON DELETE CASCADE, 400 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id)
399 CONSTRAINT fk_mstation_id FOREIGN KEY (measurement_station_id) REFERENCES measurement_station(id) ON DELETE CASCADE 401 REFERENCES sq_relation(id) ON DELETE CASCADE,
402 CONSTRAINT fk_mstation_id FOREIGN KEY (measurement_station_id)
403 REFERENCES measurement_station(id) ON DELETE CASCADE
400 ); 404 );
401 COMMIT; 405 COMMIT;
http://dive4elements.wald.intevation.org