Mercurial > lada > lada-server
comparison db_schema/stammdaten_schema.sql @ 1115:2f42a7607bbd
Client and server now expect these combinations to be modelled, too.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Thu, 27 Oct 2016 19:30:32 +0200 |
| parents | 46df70a27767 |
| children | d1d7d684800c |
comparison
equal
deleted
inserted
replaced
| 1114:ccd077c29f72 | 1115:2f42a7607bbd |
|---|---|
| 554 id integer PRIMARY KEY, | 554 id integer PRIMARY KEY, |
| 555 stufe_id integer REFERENCES status_stufe NOT NULL, | 555 stufe_id integer REFERENCES status_stufe NOT NULL, |
| 556 wert_id integer REFERENCES status_wert NOT NULL, | 556 wert_id integer REFERENCES status_wert NOT NULL, |
| 557 UNIQUE(stufe_id, wert_id) | 557 UNIQUE(stufe_id, wert_id) |
| 558 ); | 558 ); |
| 559 -- 'zurückgesetzt' is left out here deliberately! | |
| 560 INSERT INTO status_kombi VALUES (1, 1, 0); | 559 INSERT INTO status_kombi VALUES (1, 1, 0); |
| 561 INSERT INTO status_kombi VALUES (2, 1, 1); | 560 INSERT INTO status_kombi VALUES (2, 1, 1); |
| 562 INSERT INTO status_kombi VALUES (3, 1, 2); | 561 INSERT INTO status_kombi VALUES (3, 1, 2); |
| 563 INSERT INTO status_kombi VALUES (4, 1, 3); | 562 INSERT INTO status_kombi VALUES (4, 1, 3); |
| 564 INSERT INTO status_kombi VALUES (5, 1, 7); | 563 INSERT INTO status_kombi VALUES (5, 1, 7); |
| 568 INSERT INTO status_kombi VALUES (9, 2, 4); | 567 INSERT INTO status_kombi VALUES (9, 2, 4); |
| 569 INSERT INTO status_kombi VALUES (10, 3, 1); | 568 INSERT INTO status_kombi VALUES (10, 3, 1); |
| 570 INSERT INTO status_kombi VALUES (11, 3, 2); | 569 INSERT INTO status_kombi VALUES (11, 3, 2); |
| 571 INSERT INTO status_kombi VALUES (12, 3, 3); | 570 INSERT INTO status_kombi VALUES (12, 3, 3); |
| 572 INSERT INTO status_kombi VALUES (13, 3, 4); | 571 INSERT INTO status_kombi VALUES (13, 3, 4); |
| 572 INSERT INTO status_kombi VALUES (14, 1, 8); | |
| 573 INSERT INTO status_kombi VALUES (15, 2, 8); | |
| 574 INSERT INTO status_kombi VALUES (16, 3, 8); | |
| 573 | 575 |
| 574 | 576 |
| 575 CREATE TABLE status_reihenfolge ( | 577 CREATE TABLE status_reihenfolge ( |
| 576 id serial PRIMARY KEY, | 578 id serial PRIMARY KEY, |
| 577 von_id integer REFERENCES status_kombi NOT NULL, | 579 von_id integer REFERENCES status_kombi NOT NULL, |
| 600 -- At the same 'stufe', all permutations occur, | 602 -- At the same 'stufe', all permutations occur, |
| 601 -- but 'nicht vergeben' is only allowed for von_id | 603 -- but 'nicht vergeben' is only allowed for von_id |
| 602 INSERT INTO status_reihenfolge (von_id, zu_id) | 604 INSERT INTO status_reihenfolge (von_id, zu_id) |
| 603 VALUES (kombi_from.id, kombi_to.id); | 605 VALUES (kombi_from.id, kombi_to.id); |
| 604 | 606 |
| 605 ELSEIF s_to = s_from + 1 AND w_from <> 0 AND w_from <> 4 THEN | 607 ELSEIF s_to = s_from + 1 |
| 608 AND w_from <> 0 AND w_from <> 4 | |
| 609 AND w_from <> 8 AND w_to <> 8 THEN | |
| 606 -- Going to the next 'stufe' all available status_kombi are allowed | 610 -- Going to the next 'stufe' all available status_kombi are allowed |
| 607 -- in case current wert is not 'nicht vergeben' or 'Rückfrage' | 611 -- in case current wert is not 'nicht vergeben', 'Rückfrage' or |
| 612 -- 'zurückgesetzt' and we are not trying to set 'zurückgesetzt' | |
| 608 INSERT INTO status_reihenfolge (von_id, zu_id) | 613 INSERT INTO status_reihenfolge (von_id, zu_id) |
| 609 VALUES (kombi_from.id, kombi_to.id); | 614 VALUES (kombi_from.id, kombi_to.id); |
| 610 | 615 |
| 611 ELSEIF w_from = 4 AND s_to = 1 AND w_to >= 1 AND w_to <= 3 THEN | 616 ELSEIF w_from = 4 AND s_to = 1 AND w_to >= 1 AND w_to <= 3 THEN |
| 612 -- After 'Rückfrage' follows 'MST' with | 617 -- After 'Rückfrage' follows 'MST' with |
| 613 -- 'plausibel', 'nicht plausibel' or 'nicht repräsentativ' | 618 -- 'plausibel', 'nicht plausibel' or 'nicht repräsentativ' |
| 619 INSERT INTO status_reihenfolge (von_id, zu_id) | |
| 620 VALUES (kombi_from.id, kombi_to.id); | |
| 621 | |
| 622 ELSEIF w_to = 8 AND s_from = s_to THEN | |
| 623 -- 'zurückgesetzt' can only be set on the same 'stufe' | |
| 624 INSERT INTO status_reihenfolge (von_id, zu_id) | |
| 625 VALUES (kombi_from.id, kombi_to.id); | |
| 626 | |
| 627 ELSEIF w_from = 8 AND s_to = s_from - 1 THEN | |
| 628 -- after 'zurückgesetzt' always follows the next lower 'stufe' | |
| 614 INSERT INTO status_reihenfolge (von_id, zu_id) | 629 INSERT INTO status_reihenfolge (von_id, zu_id) |
| 615 VALUES (kombi_from.id, kombi_to.id); | 630 VALUES (kombi_from.id, kombi_to.id); |
| 616 | 631 |
| 617 END IF; | 632 END IF; |
| 618 END LOOP; | 633 END LOOP; |