Mercurial > lada > lada-server
comparison db_schema/stammdaten_schema.sql @ 871:53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
These are not data to be changed by any user, thus part of the schema.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Mon, 08 Feb 2016 18:23:41 +0100 |
| parents | 82a51cafa0fe |
| children | e161d8f2d978 |
comparison
equal
deleted
inserted
replaced
| 870:82a51cafa0fe | 871:53d739275c50 |
|---|---|
| 868 -- Status workflow | 868 -- Status workflow |
| 869 CREATE TABLE status_stufe ( | 869 CREATE TABLE status_stufe ( |
| 870 id integer PRIMARY KEY, | 870 id integer PRIMARY KEY, |
| 871 stufe character varying(50) | 871 stufe character varying(50) |
| 872 ); | 872 ); |
| 873 INSERT INTO status_stufe VALUES (1, 'MST'); | |
| 874 INSERT INTO status_stufe VALUES (2, 'LAND'); | |
| 875 INSERT INTO status_stufe VALUES (3, 'LST'); | |
| 876 | |
| 873 | 877 |
| 874 CREATE TABLE status_wert ( | 878 CREATE TABLE status_wert ( |
| 875 id integer PRIMARY KEY, | 879 id integer PRIMARY KEY, |
| 876 wert character varying(50) | 880 wert character varying(50) |
| 877 ); | 881 ); |
| 882 INSERT INTO status_wert VALUES (0, 'nicht vergeben'); | |
| 883 INSERT INTO status_wert VALUES (1, 'plausibel'); | |
| 884 INSERT INTO status_wert VALUES (2, 'nicht repräsentativ'); | |
| 885 INSERT INTO status_wert VALUES (3, 'nicht plausibel'); | |
| 886 INSERT INTO status_wert VALUES (4, 'Rückfrage'); | |
| 887 INSERT INTO status_wert VALUES (7, 'nicht lieferbar'); | |
| 888 INSERT INTO status_wert VALUES (8, 'zurückgesetzt'); | |
| 889 | |
| 878 | 890 |
| 879 CREATE TABLE status_kombi ( | 891 CREATE TABLE status_kombi ( |
| 880 id integer PRIMARY KEY, | 892 id integer PRIMARY KEY, |
| 881 stufe_id integer REFERENCES status_stufe, | 893 stufe_id integer REFERENCES status_stufe, |
| 882 wert_id integer REFERENCES status_wert | 894 wert_id integer REFERENCES status_wert |
| 883 ); | 895 ); |
| 884 | 896 -- 'zurückgesetzt' is left out here deliberately! |
| 897 INSERT INTO status_kombi VALUES (1, 1, 0); | |
| 898 INSERT INTO status_kombi VALUES (2, 1, 1); | |
| 899 INSERT INTO status_kombi VALUES (3, 1, 2); | |
| 900 INSERT INTO status_kombi VALUES (4, 1, 3); | |
| 901 INSERT INTO status_kombi VALUES (5, 1, 7); | |
| 902 INSERT INTO status_kombi VALUES (6, 2, 1); | |
| 903 INSERT INTO status_kombi VALUES (7, 2, 2); | |
| 904 INSERT INTO status_kombi VALUES (8, 2, 3); | |
| 905 INSERT INTO status_kombi VALUES (9, 2, 4); | |
| 906 INSERT INTO status_kombi VALUES (10, 3, 1); | |
| 907 INSERT INTO status_kombi VALUES (11, 3, 2); | |
| 908 INSERT INTO status_kombi VALUES (12, 3, 3); | |
| 909 INSERT INTO status_kombi VALUES (13, 3, 4); | |
| 910 | |
| 911 | |
| 912 CREATE SEQUENCE status_reihenfolge_id_seq; | |
| 885 CREATE TABLE status_reihenfolge ( | 913 CREATE TABLE status_reihenfolge ( |
| 886 id integer PRIMARY KEY, | 914 id integer PRIMARY KEY DEFAULT nextval('status_reihenfolge_id_seq'), |
| 887 von_id integer REFERENCES status_kombi, | 915 von_id integer REFERENCES status_kombi, |
| 888 zu_id integer REFERENCES status_kombi | 916 zu_id integer REFERENCES status_kombi |
| 889 ); | 917 ); |
| 918 | |
| 919 CREATE FUNCTION populate_status_reihenfolge() RETURNS void AS $$ | |
| 920 DECLARE kombi_from RECORD; | |
| 921 DECLARE s_from integer; | |
| 922 DECLARE w_from integer; | |
| 923 DECLARE kombi_to RECORD; | |
| 924 DECLARE s_to integer; | |
| 925 DECLARE w_to integer; | |
| 926 | |
| 927 BEGIN | |
| 928 FOR kombi_from IN SELECT * FROM status_kombi LOOP | |
| 929 s_from := kombi_from.stufe_id; | |
| 930 w_from := kombi_from.wert_id; | |
| 931 | |
| 932 FOR kombi_to IN SELECT * FROM status_kombi LOOP | |
| 933 s_to := kombi_to.stufe_id; | |
| 934 w_to := kombi_to.wert_id; | |
| 935 | |
| 936 IF s_from = s_to AND w_to <> 0 AND w_from <> 4 THEN | |
| 937 -- At the same 'stufe', all permutations occur, | |
| 938 -- but 'nicht vergeben' is only allowed for von_id | |
| 939 -- and 'Rückfrage' is only allowed for zu_id | |
| 940 INSERT INTO status_reihenfolge (von_id, zu_id) | |
| 941 VALUES (kombi_from.id, kombi_to.id); | |
| 942 | |
| 943 ELSEIF s_to = s_from + 1 AND w_from <> 0 AND w_from <> 4 THEN | |
| 944 -- Going to the next 'stufe' all available status_kombi are allowed | |
| 945 -- in case current wert is not 'nicht vergeben' or 'Rückfrage' | |
| 946 INSERT INTO status_reihenfolge (von_id, zu_id) | |
| 947 VALUES (kombi_from.id, kombi_to.id); | |
| 948 | |
| 949 ELSEIF w_from = 4 AND s_to = 1 AND w_to >= 1 AND w_to <= 3 THEN | |
| 950 -- After 'Rückfrage' follows 'MST' with | |
| 951 -- 'plausibel', 'nicht plausibel' or 'nicht repräsentativ' | |
| 952 INSERT INTO status_reihenfolge (von_id, zu_id) | |
| 953 VALUES (kombi_from.id, kombi_to.id); | |
| 954 | |
| 955 END IF; | |
| 956 END LOOP; | |
| 957 END LOOP; | |
| 958 END; | |
| 959 $$ LANGUAGE plpgsql; | |
| 960 | |
| 961 SELECT populate_status_reihenfolge(); | |
| 962 DROP FUNCTION populate_status_reihenfolge(); | |
| 963 ALTER TABLE status_reihenfolge ALTER COLUMN id DROP DEFAULT; | |
| 964 DROP SEQUENCE status_reihenfolge_id_seq; | |
| 965 | |
| 890 | 966 |
| 891 CREATE VIEW status_erreichbar AS ( | 967 CREATE VIEW status_erreichbar AS ( |
| 892 SELECT DISTINCT k.wert_id, | 968 SELECT DISTINCT k.wert_id, |
| 893 j.wert_id AS cur_wert, | 969 j.wert_id AS cur_wert, |
| 894 j.stufe_id AS cur_stufe | 970 j.stufe_id AS cur_stufe |