Mercurial > lada > lada-server
comparison db_schema/stammdaten_schema.sql @ 872:e161d8f2d978
Add necessary constraints for status workflow.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Mon, 08 Feb 2016 18:31:08 +0100 |
| parents | 53d739275c50 |
| children | ad64ef3379a8 |
comparison
equal
deleted
inserted
replaced
| 871:53d739275c50 | 872:e161d8f2d978 |
|---|---|
| 96 ALTER SEQUENCE auth_id_seq OWNED BY auth.id; | 96 ALTER SEQUENCE auth_id_seq OWNED BY auth.id; |
| 97 | 97 |
| 98 | 98 |
| 99 CREATE TABLE auth_funktion ( | 99 CREATE TABLE auth_funktion ( |
| 100 id smallint PRIMARY KEY, | 100 id smallint PRIMARY KEY, |
| 101 funktion character varying(40) | 101 funktion character varying(40) UNIQUE NOT NULL |
| 102 ); | 102 ); |
| 103 INSERT INTO auth_funktion VALUES (0, 'Erfasser'); | 103 INSERT INTO auth_funktion VALUES (0, 'Erfasser'); |
| 104 INSERT INTO auth_funktion VALUES (1, 'Status-Erfasser'); | 104 INSERT INTO auth_funktion VALUES (1, 'Status-Erfasser'); |
| 105 INSERT INTO auth_funktion VALUES (2, 'Status-Land'); | 105 INSERT INTO auth_funktion VALUES (2, 'Status-Land'); |
| 106 INSERT INTO auth_funktion VALUES (3, 'Status-Leitstelle'); | 106 INSERT INTO auth_funktion VALUES (3, 'Status-Leitstelle'); |
| 866 | 866 |
| 867 | 867 |
| 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) UNIQUE NOT NULL |
| 872 ); | 872 ); |
| 873 INSERT INTO status_stufe VALUES (1, 'MST'); | 873 INSERT INTO status_stufe VALUES (1, 'MST'); |
| 874 INSERT INTO status_stufe VALUES (2, 'LAND'); | 874 INSERT INTO status_stufe VALUES (2, 'LAND'); |
| 875 INSERT INTO status_stufe VALUES (3, 'LST'); | 875 INSERT INTO status_stufe VALUES (3, 'LST'); |
| 876 | 876 |
| 877 | 877 |
| 878 CREATE TABLE status_wert ( | 878 CREATE TABLE status_wert ( |
| 879 id integer PRIMARY KEY, | 879 id integer PRIMARY KEY, |
| 880 wert character varying(50) | 880 wert character varying(50) UNIQUE NOT NULL |
| 881 ); | 881 ); |
| 882 INSERT INTO status_wert VALUES (0, 'nicht vergeben'); | 882 INSERT INTO status_wert VALUES (0, 'nicht vergeben'); |
| 883 INSERT INTO status_wert VALUES (1, 'plausibel'); | 883 INSERT INTO status_wert VALUES (1, 'plausibel'); |
| 884 INSERT INTO status_wert VALUES (2, 'nicht repräsentativ'); | 884 INSERT INTO status_wert VALUES (2, 'nicht repräsentativ'); |
| 885 INSERT INTO status_wert VALUES (3, 'nicht plausibel'); | 885 INSERT INTO status_wert VALUES (3, 'nicht plausibel'); |
| 888 INSERT INTO status_wert VALUES (8, 'zurückgesetzt'); | 888 INSERT INTO status_wert VALUES (8, 'zurückgesetzt'); |
| 889 | 889 |
| 890 | 890 |
| 891 CREATE TABLE status_kombi ( | 891 CREATE TABLE status_kombi ( |
| 892 id integer PRIMARY KEY, | 892 id integer PRIMARY KEY, |
| 893 stufe_id integer REFERENCES status_stufe, | 893 stufe_id integer REFERENCES status_stufe NOT NULL, |
| 894 wert_id integer REFERENCES status_wert | 894 wert_id integer REFERENCES status_wert NOT NULL, |
| 895 UNIQUE(stufe_id, wert_id) | |
| 895 ); | 896 ); |
| 896 -- 'zurückgesetzt' is left out here deliberately! | 897 -- 'zurückgesetzt' is left out here deliberately! |
| 897 INSERT INTO status_kombi VALUES (1, 1, 0); | 898 INSERT INTO status_kombi VALUES (1, 1, 0); |
| 898 INSERT INTO status_kombi VALUES (2, 1, 1); | 899 INSERT INTO status_kombi VALUES (2, 1, 1); |
| 899 INSERT INTO status_kombi VALUES (3, 1, 2); | 900 INSERT INTO status_kombi VALUES (3, 1, 2); |
| 910 | 911 |
| 911 | 912 |
| 912 CREATE SEQUENCE status_reihenfolge_id_seq; | 913 CREATE SEQUENCE status_reihenfolge_id_seq; |
| 913 CREATE TABLE status_reihenfolge ( | 914 CREATE TABLE status_reihenfolge ( |
| 914 id integer PRIMARY KEY DEFAULT nextval('status_reihenfolge_id_seq'), | 915 id integer PRIMARY KEY DEFAULT nextval('status_reihenfolge_id_seq'), |
| 915 von_id integer REFERENCES status_kombi, | 916 von_id integer REFERENCES status_kombi NOT NULL, |
| 916 zu_id integer REFERENCES status_kombi | 917 zu_id integer REFERENCES status_kombi NOT NULL, |
| 918 UNIQUE(von_id, zu_id) | |
| 917 ); | 919 ); |
| 918 | 920 |
| 919 CREATE FUNCTION populate_status_reihenfolge() RETURNS void AS $$ | 921 CREATE FUNCTION populate_status_reihenfolge() RETURNS void AS $$ |
| 920 DECLARE kombi_from RECORD; | 922 DECLARE kombi_from RECORD; |
| 921 DECLARE s_from integer; | 923 DECLARE s_from integer; |