Mercurial > lada > lada-server

comparison db_schema/stammdaten_schema.sql @ 870:82a51cafa0fe

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Put status workflow definitions in one place.
author Tom Gottfried <tom@intevation.de>
date Mon, 08 Feb 2016 13:21:42 +0100
parents fc8349057de1
children 53d739275c50
comparison
equal deleted inserted replaced
869:fc8349057de1 870:82a51cafa0fe
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');
107 INSERT INTO auth_funktion VALUES (4, 'Stammdatenpflege-Land'); 107 INSERT INTO auth_funktion VALUES (4, 'Stammdatenpflege-Land');
108 108
109
109 CREATE SEQUENCE auth_lst_umw_id_seq 110 CREATE SEQUENCE auth_lst_umw_id_seq
110 START WITH 1 111 START WITH 1
111 INCREMENT BY 1 112 INCREMENT BY 1
112 NO MINVALUE 113 NO MINVALUE
113 NO MAXVALUE 114 NO MAXVALUE
862 ); 863 );
863 864
864 ALTER SEQUENCE staat_id_seq OWNED BY staat.id; 865 ALTER SEQUENCE staat_id_seq OWNED BY staat.id;
865 866
866 867
867 CREATE TABLE status_kombi ( 868 -- Status workflow
868 id integer PRIMARY KEY,
869 stufe_id integer,
870 wert_id integer
871 );
872
873 CREATE TABLE status_reihenfolge (
874 id integer PRIMARY KEY,
875 von_id integer,
876 zu_id integer
877 );
878
879 CREATE TABLE status_stufe ( 869 CREATE TABLE status_stufe (
880 id integer PRIMARY KEY, 870 id integer PRIMARY KEY,
881 stufe character varying(50) 871 stufe character varying(50)
882 ); 872 );
883 873
884 CREATE TABLE status_wert ( 874 CREATE TABLE status_wert (
885 id integer PRIMARY KEY, 875 id integer PRIMARY KEY,
886 wert character varying(50) 876 wert character varying(50)
887 ); 877 );
878
879 CREATE TABLE status_kombi (
880 id integer PRIMARY KEY,
881 stufe_id integer REFERENCES status_stufe,
882 wert_id integer REFERENCES status_wert
883 );
884
885 CREATE TABLE status_reihenfolge (
886 id integer PRIMARY KEY,
887 von_id integer REFERENCES status_kombi,
888 zu_id integer REFERENCES status_kombi
889 );
890
891 CREATE VIEW status_erreichbar AS (
892 SELECT DISTINCT k.wert_id,
893 j.wert_id AS cur_wert,
894 j.stufe_id AS cur_stufe
895 FROM stammdaten.status_kombi k
896 JOIN (SELECT r.zu_id,
897 kom.wert_id,
898 kom.stufe_id
899 FROM stammdaten.status_reihenfolge r
900 JOIN stammdaten.status_kombi kom
901 ON kom.id = r.von_id) j
902 ON j.zu_id = k.id
903 );
904 -- Status workflow
888 905
889 906
890 CREATE TABLE umwelt ( 907 CREATE TABLE umwelt (
891 id character varying(3) PRIMARY KEY, 908 id character varying(3) PRIMARY KEY,
892 beschreibung character varying(300), 909 beschreibung character varying(300),
913 longitude double precision, 930 longitude double precision,
914 latitude double precision 931 latitude double precision
915 ); 932 );
916 933
917 934
918 CREATE VIEW status_erreichbar AS (
919 SELECT DISTINCT k.wert_id,
920 j.wert_id AS cur_wert,
921 j.stufe_id AS cur_stufe
922 FROM stammdaten.status_kombi k
923 JOIN (SELECT r.zu_id,
924 kom.wert_id,
925 kom.stufe_id
926 FROM stammdaten.status_reihenfolge r
927 JOIN stammdaten.status_kombi kom
928 ON kom.id = r.von_id) j
929 ON j.zu_id = k.id
930 );
931
932 935
933 936
934 ALTER TABLE ONLY lada_user 937 ALTER TABLE ONLY lada_user
935 ADD CONSTRAINT lada_user_name_key UNIQUE (name); 938 ADD CONSTRAINT lada_user_name_key UNIQUE (name);
936 939
1106 ALTER TABLE ONLY staat 1109 ALTER TABLE ONLY staat
1107 ADD CONSTRAINT staat_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id); 1110 ADD CONSTRAINT staat_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
1108 1111
1109 1112
1110 1113
1111 ALTER TABLE ONLY status_kombi
1112 ADD CONSTRAINT status_kombi_stufe_id_fkey FOREIGN KEY (stufe_id) REFERENCES status_stufe(id);
1113
1114
1115
1116 ALTER TABLE ONLY status_kombi
1117 ADD CONSTRAINT status_kombi_wert_id_fkey FOREIGN KEY (wert_id) REFERENCES status_wert(id);
1118
1119
1120
1121 ALTER TABLE ONLY status_reihenfolge
1122 ADD CONSTRAINT status_reihenfolge_von_id_fkey FOREIGN KEY (von_id) REFERENCES status_kombi(id);
1123
1124
1125
1126 ALTER TABLE ONLY status_reihenfolge
1127 ADD CONSTRAINT status_reihenfolge_zu_id_fkey FOREIGN KEY (zu_id) REFERENCES status_kombi(id);
1128
1129 1114
1130 1115
1131 ALTER TABLE ONLY umwelt 1116 ALTER TABLE ONLY umwelt
1132 ADD CONSTRAINT umwelt_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id); 1117 ADD CONSTRAINT umwelt_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id);
1133 1118
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)