Mercurial > dive4elements > river
comparison backend/doc/schema/postgresql.sql @ 6300:0711ce5ca701
Backend: Modified schema for a better model of official lines.
| author | Sascha L. Teichmann <teichmann@intevation.de> |
|---|---|
| date | Wed, 12 Jun 2013 16:36:34 +0200 |
| parents | 573112007ec7 |
| children | 3b93f439e954 |
comparison
equal
deleted
inserted
replaced
| 6299:16182a08ccf7 | 6300:0711ce5ca701 |
|---|---|
| 238 id int PRIMARY KEY NOT NULL, | 238 id int PRIMARY KEY NOT NULL, |
| 239 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE, | 239 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE, |
| 240 wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id) ON DELETE CASCADE, | 240 wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id) ON DELETE CASCADE, |
| 241 | 241 |
| 242 UNIQUE (wst_column_id, wst_q_range_id) | 242 UNIQUE (wst_column_id, wst_q_range_id) |
| 243 ); | |
| 244 | |
| 245 CREATE SEQUENCE OFFICIAL_LINES_ID_SEQ; | |
| 246 | |
| 247 CREATE TABLE official_lines ( | |
| 248 id int PRIMARY KEY NOT NULL, | |
| 249 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE, | |
| 250 named_main_value_id int NOT NULL REFERENCES named_main_values(id) ON DELETE CASCADE, | |
| 251 | |
| 252 UNIQUE (wst_column_id, named_main_value_id) | |
| 243 ); | 253 ); |
| 244 | 254 |
| 245 CREATE VIEW wst_value_table AS | 255 CREATE VIEW wst_value_table AS |
| 246 SELECT | 256 SELECT |
| 247 wcv.position AS position, | 257 wcv.position AS position, |
| 374 a NUMERIC NOT NULL, | 384 a NUMERIC NOT NULL, |
| 375 b NUMERIC NOT NULL, | 385 b NUMERIC NOT NULL, |
| 376 CHECK (a <= b) | 386 CHECK (a <= b) |
| 377 ); | 387 ); |
| 378 | 388 |
| 379 CREATE VIEW official_lines | |
| 380 AS | |
| 381 SELECT w.river_id AS river_id, | |
| 382 w.id AS wst_id, | |
| 383 wc.id AS wst_column_id, | |
| 384 wc.name AS name, | |
| 385 wc.position AS wst_column_pos | |
| 386 FROM wsts w | |
| 387 JOIN wst_columns wc | |
| 388 ON wc.wst_id = w.id | |
| 389 WHERE w.kind = 3; | |
| 390 | |
| 391 CREATE VIEW q_main_values | |
| 392 AS | |
| 393 SELECT riv.id AS river_id, | |
| 394 g.id AS gauge_id, | |
| 395 g.name AS gauge_name, | |
| 396 r.a AS a, | |
| 397 r.b AS b, | |
| 398 REGEXP_REPLACE( | |
| 399 nmv.name, E'[:space:]*\\(.*\\)[:space:]*', '') AS name, | |
| 400 CAST(mv.value AS NUMERIC(38, 2)) AS value | |
| 401 FROM main_values mv | |
| 402 JOIN named_main_values nmv | |
| 403 ON mv.named_value_id = nmv.id | |
| 404 JOIN main_value_types mvt | |
| 405 ON nmv.type_id = mvt.id | |
| 406 JOIN gauges g | |
| 407 ON mv.gauge_id = g.id | |
| 408 JOIN ranges r | |
| 409 ON g.range_id = r.id | |
| 410 JOIN rivers riv | |
| 411 ON g.river_id = riv.id | |
| 412 WHERE mvt.name = 'Q' | |
| 413 ORDER BY g.id, CAST(mv.value AS NUMERIC(38,2)); | |
| 414 | |
| 415 CREATE VIEW official_q_values | |
| 416 AS | |
| 417 SELECT ol.river_id AS river_id, | |
| 418 wst_id, | |
| 419 wst_column_id, | |
| 420 gauge_id, | |
| 421 gauge_name, | |
| 422 a, | |
| 423 b, | |
| 424 ol.name, | |
| 425 value, | |
| 426 wst_column_pos | |
| 427 FROM official_lines ol | |
| 428 JOIN q_main_values qmv | |
| 429 ON ol.river_id = qmv.river_id | |
| 430 AND ol.name = qmv.name; | |
| 431 | |
| 432 CREATE VIEW wst_ranges | 389 CREATE VIEW wst_ranges |
| 433 AS | 390 AS |
| 434 SELECT wc.id AS wst_column_id, | 391 SELECT wc.id AS wst_column_id, |
| 435 wc.wst_id AS wst_id, | 392 wc.wst_id AS wst_id, |
| 436 Min(wcv.position) AS a, | 393 Min(wcv.position) AS a, |