# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1454934102 -3600
# Node ID 82a51cafa0fe93a87c2aeb281c0dc0e6c408abd7
# Parent  fc8349057de1f61eaddd1b62fe4e3a424a9628d0
Put status workflow definitions in one place.

diff -r fc8349057de1 -r 82a51cafa0fe db_schema/stammdaten_schema.sql
--- 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