Mercurial > dive4elements > river

comparison backend/doc/schema/postgresql-minfo.sql @ 8072:5f28aa1be795

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
author Tom Gottfried <tom@intevation.de>
date Wed, 06 Aug 2014 19:17:12 +0200
parents bde5f5ec7c72
children 17db08570637
comparison
equal deleted inserted replaced
8071:6e6506ec0ae0 8072:5f28aa1be795
257 INSERT INTO grain_fraction VALUES (7, 'total', 0, 200); 257 INSERT INTO grain_fraction VALUES (7, 'total', 0, 200);
258 INSERT INTO grain_fraction VALUES (8, 'bed_load', 0.063, 200); 258 INSERT INTO grain_fraction VALUES (8, 'bed_load', 0.063, 200);
259 INSERT INTO grain_fraction VALUES (9, 'suspended_load', 0, 2); 259 INSERT INTO grain_fraction VALUES (9, 'suspended_load', 0, 2);
260 260
261 261
262 --lookup table for sediment yield kinds 262 --lookup table for sediment load kinds
263 CREATE TABLE sediment_yield_kinds ( 263 CREATE TABLE sediment_load_kinds (
264 id int PRIMARY KEY NOT NULL, 264 id int PRIMARY KEY NOT NULL,
265 kind VARCHAR(64) NOT NULL 265 kind VARCHAR(64) NOT NULL
266 ); 266 );
267 INSERT INTO sediment_yield_kinds (id, kind) VALUES (0, 'non-official'); 267 INSERT INTO sediment_load_kinds (id, kind) VALUES (0, 'non-official');
268 INSERT INTO sediment_yield_kinds (id, kind) VALUES (1, 'official'); 268 INSERT INTO sediment_load_kinds (id, kind) VALUES (1, 'official');
269 269
270 CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ; 270 CREATE SEQUENCE SEDIMENT_LOAD_LS_ID_SEQ;
271 271
272 CREATE TABLE sediment_yield ( 272 CREATE TABLE sediment_load_ls (
273 id int NOT NULL, 273 id int NOT NULL,
274 river_id int NOT NULL, 274 river_id int NOT NULL,
275 grain_fraction_id int, 275 grain_fraction_id int,
276 unit_id int NOT NULL, 276 unit_id int NOT NULL,
277 time_interval_id int NOT NULL, 277 time_interval_id int NOT NULL,
278 sq_time_interval_id int, 278 sq_time_interval_id int,
279 description VARCHAR(256), 279 description VARCHAR(256),
280 kind int, 280 kind int,
281 PRIMARY KEY (id), 281 PRIMARY KEY (id),
282 CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, 282 CONSTRAINT fk_slls_river_id FOREIGN KEY (river_id)
283 CONSTRAINT fk_sy_kind_id FOREIGN KEY (kind) REFERENCES sediment_yield_kinds(id), 283 REFERENCES rivers(id) ON DELETE CASCADE,
284 CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id), 284 CONSTRAINT fk_slls_kind_id FOREIGN KEY (kind)
285 CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id), 285 REFERENCES sediment_load_kinds(id),
286 CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id), 286 CONSTRAINT fk_slls_grain_fraction_id FOREIGN KEY (grain_fraction_id)
287 CONSTRAINT fk_sy_sq_time_interval_id FOREIGN KEY (sq_time_interval_id) 287 REFERENCES grain_fraction(id),
288 CONSTRAINT fk_slls_unit_id FOREIGN KEY (unit_id)
289 REFERENCES units(id),
290 CONSTRAINT fk_slls_time_interval_id FOREIGN KEY (time_interval_id)
291 REFERENCES time_intervals(id),
292 CONSTRAINT fk_slls_sq_time_interval_id FOREIGN KEY (sq_time_interval_id)
288 REFERENCES time_intervals(id) 293 REFERENCES time_intervals(id)
289 ); 294 );
290 295
291 296
292 CREATE SEQUENCE SEDIMENT_YIELD_VALUES_ID_SEQ; 297 CREATE SEQUENCE SEDIMENT_LOAD_LS_VALUES_ID_SEQ;
293 298
294 CREATE TABLE sediment_yield_values ( 299 CREATE TABLE sediment_load_ls_values (
295 id int NOT NULL, 300 id int NOT NULL,
296 sediment_yield_id int NOT NULL, 301 sediment_load_ls_id int NOT NULL,
297 station NUMERIC NOT NULL, 302 station NUMERIC NOT NULL,
298 value NUMERIC NOT NULL, 303 value NUMERIC NOT NULL,
299 PRIMARY KEY (id), 304 PRIMARY KEY (id),
300 CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id) ON DELETE CASCADE 305 CONSTRAINT fk_sllsv_sediment_load_ls_id FOREIGN KEY (sediment_load_ls_id)
306 REFERENCES sediment_load_ls(id) ON DELETE CASCADE
301 ); 307 );
302 308
303 309
304 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ; 310 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ;
305 CREATE TABLE measurement_station ( 311 CREATE TABLE measurement_station (
333 sq_time_interval_id int, 339 sq_time_interval_id int,
334 description VARCHAR(256), 340 description VARCHAR(256),
335 kind int, 341 kind int,
336 PRIMARY KEY (id), 342 PRIMARY KEY (id),
337 CONSTRAINT fk_sl_kind_id FOREIGN KEY (kind) 343 CONSTRAINT fk_sl_kind_id FOREIGN KEY (kind)
338 REFERENCES sediment_yield_kinds(id), 344 REFERENCES sediment_load_kinds(id),
339 CONSTRAINT fk_sl_grain_fraction_id FOREIGN KEY (grain_fraction_id) 345 CONSTRAINT fk_sl_grain_fraction_id FOREIGN KEY (grain_fraction_id)
340 REFERENCES grain_fraction(id), 346 REFERENCES grain_fraction(id),
341 CONSTRAINT fk_sl_time_interval_id FOREIGN KEY (time_interval_id) 347 CONSTRAINT fk_sl_time_interval_id FOREIGN KEY (time_interval_id)
342 REFERENCES time_intervals(id), 348 REFERENCES time_intervals(id),
343 CONSTRAINT fk_sl_sq_time_interval_id FOREIGN KEY (sq_time_interval_id) 349 CONSTRAINT fk_sl_sq_time_interval_id FOREIGN KEY (sq_time_interval_id)
http://dive4elements.wald.intevation.org