Mercurial > lada > lada-server
changeset 870:82a51cafa0fe
Put status workflow definitions in one place.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Mon, 08 Feb 2016 13:21:42 +0100 (2016-02-08) |
parents | fc8349057de1 |
children | 53d739275c50 |
files | db_schema/stammdaten_schema.sql |
diffstat | 1 files changed, 29 insertions(+), 44 deletions(-) [+] |
line wrap: on
line diff
--- a/db_schema/stammdaten_schema.sql Mon Feb 08 12:45:26 2016 +0100 +++ b/db_schema/stammdaten_schema.sql Mon Feb 08 13:21:42 2016 +0100 @@ -106,6 +106,7 @@ INSERT INTO auth_funktion VALUES (3, 'Status-Leitstelle'); INSERT INTO auth_funktion VALUES (4, 'Stammdatenpflege-Land'); + CREATE SEQUENCE auth_lst_umw_id_seq START WITH 1 INCREMENT BY 1 @@ -864,18 +865,7 @@ ALTER SEQUENCE staat_id_seq OWNED BY staat.id; -CREATE TABLE status_kombi ( - id integer PRIMARY KEY, - stufe_id integer, - wert_id integer -); - -CREATE TABLE status_reihenfolge ( - id integer PRIMARY KEY, - von_id integer, - zu_id integer -); - +-- Status workflow CREATE TABLE status_stufe ( id integer PRIMARY KEY, stufe character varying(50) @@ -886,6 +876,33 @@ wert character varying(50) ); +CREATE TABLE status_kombi ( + id integer PRIMARY KEY, + stufe_id integer REFERENCES status_stufe, + wert_id integer REFERENCES status_wert +); + +CREATE TABLE status_reihenfolge ( + id integer PRIMARY KEY, + von_id integer REFERENCES status_kombi, + zu_id integer REFERENCES status_kombi +); + +CREATE VIEW status_erreichbar AS ( + SELECT DISTINCT k.wert_id, + j.wert_id AS cur_wert, + j.stufe_id AS cur_stufe + FROM stammdaten.status_kombi k + JOIN (SELECT r.zu_id, + kom.wert_id, + kom.stufe_id + FROM stammdaten.status_reihenfolge r + JOIN stammdaten.status_kombi kom + ON kom.id = r.von_id) j + ON j.zu_id = k.id +); +-- Status workflow + CREATE TABLE umwelt ( id character varying(3) PRIMARY KEY, @@ -915,20 +932,6 @@ ); -CREATE VIEW status_erreichbar AS ( - SELECT DISTINCT k.wert_id, - j.wert_id AS cur_wert, - j.stufe_id AS cur_stufe - FROM stammdaten.status_kombi k - JOIN (SELECT r.zu_id, - kom.wert_id, - kom.stufe_id - FROM stammdaten.status_reihenfolge r - JOIN stammdaten.status_kombi kom - ON kom.id = r.von_id) j - ON j.zu_id = k.id -); - ALTER TABLE ONLY lada_user @@ -1108,24 +1111,6 @@ -ALTER TABLE ONLY status_kombi - ADD CONSTRAINT status_kombi_stufe_id_fkey FOREIGN KEY (stufe_id) REFERENCES status_stufe(id); - - - -ALTER TABLE ONLY status_kombi - ADD CONSTRAINT status_kombi_wert_id_fkey FOREIGN KEY (wert_id) REFERENCES status_wert(id); - - - -ALTER TABLE ONLY status_reihenfolge - ADD CONSTRAINT status_reihenfolge_von_id_fkey FOREIGN KEY (von_id) REFERENCES status_kombi(id); - - - -ALTER TABLE ONLY status_reihenfolge - ADD CONSTRAINT status_reihenfolge_zu_id_fkey FOREIGN KEY (zu_id) REFERENCES status_kombi(id); - ALTER TABLE ONLY umwelt