Mercurial > lada > lada-server

comparison db_schema/lada_schema.sql @ 768:f2ae2d734b80

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Updated database schema for status workflow.
author Raimund Renkert <raimund.renkert@intevation.de>
date Thu, 12 Nov 2015 12:03:03 +0100
parents 37a8f4d157c7
children 1cf168a038c9
comparison
equal deleted inserted replaced
767:ea6b3f008cd1 768:f2ae2d734b80
845 845
846 CREATE TABLE auth ( 846 CREATE TABLE auth (
847 id integer NOT NULL, 847 id integer NOT NULL,
848 ldap_group character varying(40) NOT NULL, 848 ldap_group character varying(40) NOT NULL,
849 netzbetreiber_id character varying(2), 849 netzbetreiber_id character varying(2),
850 mst_id character varying(5) 850 mst_id character varying(5),
851 labor_mst_id character varying(5),
852 funktion_id smallint
853 );
854
855
856 --
857 -- Name: auth_funktion; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
858 --
859
860 CREATE TABLE auth_funktion (
861 id smallint NOT NULL,
862 funktion character varying(40)
851 ); 863 );
852 864
853 865
854 -- 866 --
855 -- Name: auth_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - 867 -- Name: auth_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
866 -- 878 --
867 -- Name: auth_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - 879 -- Name: auth_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
868 -- 880 --
869 881
870 ALTER SEQUENCE auth_id_seq OWNED BY auth.id; 882 ALTER SEQUENCE auth_id_seq OWNED BY auth.id;
883
884
885 --
886 -- Name: auth_id_seq1; Type: SEQUENCE; Schema: stammdaten; Owner: -
887 --
888
889 CREATE SEQUENCE auth_id_seq1
890 START WITH 1
891 INCREMENT BY 1
892 NO MINVALUE
893 NO MAXVALUE
894 CACHE 1;
895
896
897 --
898 -- Name: auth_id_seq1; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
899 --
900
901 ALTER SEQUENCE auth_id_seq1 OWNED BY auth.id;
902
903
904 --
905 -- Name: auth_lst_umw; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
906 --
907
908 CREATE TABLE auth_lst_umw (
909 id integer NOT NULL,
910 lst_id character varying(5),
911 umw_id character varying(3)
912 );
913
914
915 --
916 -- Name: auth_lst_umw_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
917 --
918
919 CREATE SEQUENCE auth_lst_umw_id_seq
920 START WITH 1
921 INCREMENT BY 1
922 NO MINVALUE
923 NO MAXVALUE
924 CACHE 1;
925
926
927 --
928 -- Name: auth_lst_umw_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
929 --
930
931 ALTER SEQUENCE auth_lst_umw_id_seq OWNED BY auth_lst_umw.id;
871 932
872 933
873 -- 934 --
874 -- Name: datenbasis; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: 935 -- Name: datenbasis; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
875 -- 936 --
1681 1742
1682 ALTER SEQUENCE staat_id_seq OWNED BY staat.id; 1743 ALTER SEQUENCE staat_id_seq OWNED BY staat.id;
1683 1744
1684 1745
1685 -- 1746 --
1747 -- Name: status_kombi; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1748 --
1749
1750 CREATE TABLE status_kombi (
1751 id integer NOT NULL,
1752 stufe_id integer,
1753 wert_id integer
1754 );
1755
1756
1757 --
1758 -- Name: status_reihenfolge; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1759 --
1760
1761 CREATE TABLE status_reihenfolge (
1762 id integer NOT NULL,
1763 von_id integer,
1764 zu_id integer
1765 );
1766
1767
1768 --
1769 -- Name: status_erreichbar; Type: VIEW; Schema: stammdaten; Owner: -
1770 --
1771
1772 CREATE VIEW status_erreichbar AS
1773 SELECT DISTINCT k.wert_id,
1774 j.wert_id AS cur_wert,
1775 j.stufe_id AS cur_stufe
1776 FROM (status_kombi k
1777 JOIN ( SELECT r.zu_id,
1778 kom.wert_id,
1779 kom.stufe_id
1780 FROM (status_reihenfolge r
1781 JOIN status_kombi kom ON ((kom.id = r.von_id)))) j ON ((j.zu_id = k.id)));
1782
1783
1784 --
1686 -- Name: status_stufe; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: 1785 -- Name: status_stufe; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1687 -- 1786 --
1688 1787
1689 CREATE TABLE status_stufe ( 1788 CREATE TABLE status_stufe (
1690 id integer NOT NULL, 1789 id integer NOT NULL,
2258 2357
2259 2358
2260 SET search_path = stammdaten, pg_catalog; 2359 SET search_path = stammdaten, pg_catalog;
2261 2360
2262 -- 2361 --
2362 -- Name: auth_lst_umw_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2363 --
2364
2365 ALTER TABLE ONLY auth_lst_umw
2366 ADD CONSTRAINT auth_lst_umw_pkey PRIMARY KEY (id);
2367
2368
2369 --
2263 -- Name: auth_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 2370 -- Name: auth_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2264 -- 2371 --
2265 2372
2266 ALTER TABLE ONLY auth 2373 ALTER TABLE ONLY auth
2267 ADD CONSTRAINT auth_pkey PRIMARY KEY (id); 2374 ADD CONSTRAINT auth_pkey PRIMARY KEY (id);
2268 2375
2269 2376
2270 -- 2377 --
2378 -- Name: auth_role_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2379 --
2380
2381 ALTER TABLE ONLY auth_funktion
2382 ADD CONSTRAINT auth_role_pkey PRIMARY KEY (id);
2383
2384
2385 --
2271 -- Name: datenbasis_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 2386 -- Name: datenbasis_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2272 -- 2387 --
2273 2388
2274 ALTER TABLE ONLY datenbasis 2389 ALTER TABLE ONLY datenbasis
2275 ADD CONSTRAINT datenbasis_pkey PRIMARY KEY (id); 2390 ADD CONSTRAINT datenbasis_pkey PRIMARY KEY (id);
2418 ALTER TABLE ONLY staat 2533 ALTER TABLE ONLY staat
2419 ADD CONSTRAINT staat_pkey PRIMARY KEY (id); 2534 ADD CONSTRAINT staat_pkey PRIMARY KEY (id);
2420 2535
2421 2536
2422 -- 2537 --
2538 -- Name: status_kombi_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2539 --
2540
2541 ALTER TABLE ONLY status_kombi
2542 ADD CONSTRAINT status_kombi_pkey PRIMARY KEY (id);
2543
2544
2545 --
2546 -- Name: status_reihenfolge_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2547 --
2548
2549 ALTER TABLE ONLY status_reihenfolge
2550 ADD CONSTRAINT status_reihenfolge_pkey PRIMARY KEY (id);
2551
2552
2553 --
2423 -- Name: status_stufe_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 2554 -- Name: status_stufe_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2424 -- 2555 --
2425 2556
2426 ALTER TABLE ONLY status_stufe 2557 ALTER TABLE ONLY status_stufe
2427 ADD CONSTRAINT status_stufe_pkey PRIMARY KEY (id); 2558 ADD CONSTRAINT status_stufe_pkey PRIMARY KEY (id);
2963 3094
2964 3095
2965 SET search_path = stammdaten, pg_catalog; 3096 SET search_path = stammdaten, pg_catalog;
2966 3097
2967 -- 3098 --
3099 -- Name: auth_funktion_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3100 --
3101
3102 ALTER TABLE ONLY auth
3103 ADD CONSTRAINT auth_funktion_fkey FOREIGN KEY (funktion_id) REFERENCES auth_funktion(id);
3104
3105
3106 --
3107 -- Name: auth_labor_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3108 --
3109
3110 ALTER TABLE ONLY auth
3111 ADD CONSTRAINT auth_labor_fkey FOREIGN KEY (labor_mst_id) REFERENCES mess_stelle(id);
3112
3113
3114 --
3115 -- Name: auth_lst_umw_lst_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3116 --
3117
3118 ALTER TABLE ONLY auth_lst_umw
3119 ADD CONSTRAINT auth_lst_umw_lst_fkey FOREIGN KEY (lst_id) REFERENCES mess_stelle(id);
3120
3121
3122 --
3123 -- Name: auth_lst_umw_umw_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3124 --
3125
3126 ALTER TABLE ONLY auth_lst_umw
3127 ADD CONSTRAINT auth_lst_umw_umw_fkey FOREIGN KEY (umw_id) REFERENCES umwelt(id);
3128
3129
3130 --
2968 -- Name: auth_mst_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - 3131 -- Name: auth_mst_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
2969 -- 3132 --
2970 3133
2971 ALTER TABLE ONLY auth 3134 ALTER TABLE ONLY auth
2972 ADD CONSTRAINT auth_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES mess_stelle(id); 3135 ADD CONSTRAINT auth_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES mess_stelle(id);
3056 -- Name: staat_kda_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - 3219 -- Name: staat_kda_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3057 -- 3220 --
3058 3221
3059 ALTER TABLE ONLY staat 3222 ALTER TABLE ONLY staat
3060 ADD CONSTRAINT staat_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id); 3223 ADD CONSTRAINT staat_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
3224
3225
3226 --
3227 -- Name: status_kombi_stufe_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3228 --
3229
3230 ALTER TABLE ONLY status_kombi
3231 ADD CONSTRAINT status_kombi_stufe_id_fkey FOREIGN KEY (stufe_id) REFERENCES status_stufe(id);
3232
3233
3234 --
3235 -- Name: status_kombi_wert_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3236 --
3237
3238 ALTER TABLE ONLY status_kombi
3239 ADD CONSTRAINT status_kombi_wert_id_fkey FOREIGN KEY (wert_id) REFERENCES status_wert(id);
3240
3241
3242 --
3243 -- Name: status_reihenfolge_von_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3244 --
3245
3246 ALTER TABLE ONLY status_reihenfolge
3247 ADD CONSTRAINT status_reihenfolge_von_id_fkey FOREIGN KEY (von_id) REFERENCES status_kombi(id);
3248
3249
3250 --
3251 -- Name: status_reihenfolge_zu_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3252 --
3253
3254 ALTER TABLE ONLY status_reihenfolge
3255 ADD CONSTRAINT status_reihenfolge_zu_id_fkey FOREIGN KEY (zu_id) REFERENCES status_kombi(id);
3061 3256
3062 3257
3063 -- 3258 --
3064 -- Name: umwelt_meh_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - 3259 -- Name: umwelt_meh_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3065 -- 3260 --
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)