Mercurial > lada > lada-server
diff db_schema/stammdaten_schema.sql @ 866:d47e6b8f3897
Reorganise database setup scripts.
First step towards better readability.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Fri, 05 Feb 2016 17:01:28 +0100 |
| parents | |
| children | 66069d69d6ee |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/db_schema/stammdaten_schema.sql Fri Feb 05 17:01:28 2016 +0100 @@ -0,0 +1,2167 @@ +\set ON_ERROR_STOP on + +BEGIN; + +SET statement_timeout = 0; +SET lock_timeout = 0; +SET client_encoding = 'UTF8'; +SET standard_conforming_strings = on; +SET check_function_bodies = false; +SET client_min_messages = warning; + + +CREATE SCHEMA stammdaten; + +SET search_path = stammdaten, pg_catalog; + + +CREATE FUNCTION get_media_from_media_desk(media_desk character varying) RETURNS character varying + LANGUAGE plpgsql + AS $$ +declare + result character varying(100); + d00 smallint; + d01 smallint; + d02 smallint; + d03 smallint; +begin + if media_desk like 'D: %' then + d00 := substring(media_desk,4,2); + d01 := substring(media_desk,7,2); + d02 := substring(media_desk,10,2); + d03 := substring(media_desk,13,2); + if d00 = '00' then + result := null; + else + if d01 = '00' then + select s00.beschreibung into result FROM stammdaten.deskriptoren s00 + where s00.ebene = 0 and s00.sn = d00::smallint; + else + if d02 = '00' or d00 <> '01' then + select s01.beschreibung into result FROM stammdaten.deskriptoren s01 + where s01.ebene = 1 and s01.sn = d01::smallint + and s01.vorgaenger = + (select s00.id FROM stammdaten.deskriptoren s00 + where s00.ebene = 0 and s00.sn = d00::smallint); + else + if d03 = '00' then + select s02.beschreibung into result FROM stammdaten.deskriptoren s02 + where s02.ebene = 2 and s02.sn = d02::smallint + and s02.vorgaenger = + (select s01.id FROM stammdaten.deskriptoren s01 + where s01.ebene = 1 and s01.sn = d01::smallint + and s01.vorgaenger = + (select s00.id FROM stammdaten.deskriptoren s00 + where s00.ebene = 0 and s00.sn = d00::smallint)); + else + select s03.beschreibung into result FROM stammdaten.deskriptoren s03 + where s03.ebene = 3 and s03.sn = d03::smallint + and s03.vorgaenger = + (select s02.id FROM stammdaten.deskriptoren s02 + where s02.ebene = 2 and s02.sn = d02::smallint + and s02.vorgaenger = + (select s01.id FROM stammdaten.deskriptoren s01 + where s01.ebene = 1 and s01.sn = d01::smallint + and s01.vorgaenger = + (select s00.id FROM stammdaten.deskriptoren s00 + where s00.ebene = 0 and s00.sn = d00::smallint))); + end if; + end if; + end if; + end if; + else + result := null; + end if; + return (result); +end; +$$; + + +CREATE TABLE auth ( + id integer NOT NULL, + ldap_group character varying(40) NOT NULL, + netzbetreiber_id character varying(2), + mst_id character varying(5), + labor_mst_id character varying(5), + funktion_id smallint +); + + +-- +-- Name: auth_funktion; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE auth_funktion ( + id smallint NOT NULL, + funktion character varying(40) +); + + +-- +-- Name: auth_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE auth_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: auth_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE auth_id_seq OWNED BY auth.id; + + +-- +-- Name: auth_id_seq1; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE auth_id_seq1 + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: auth_id_seq1; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE auth_id_seq1 OWNED BY auth.id; + + +-- +-- Name: auth_lst_umw; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE auth_lst_umw ( + id integer NOT NULL, + lst_id character varying(5), + umw_id character varying(3) +); + + +-- +-- Name: auth_lst_umw_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE auth_lst_umw_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: auth_lst_umw_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE auth_lst_umw_id_seq OWNED BY auth_lst_umw.id; + + +-- +-- Name: datenbasis; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE datenbasis ( + id integer NOT NULL, + beschreibung character varying(30), + datenbasis character varying(6) +); + + +-- +-- Name: datenbasis_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE datenbasis_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: datenbasis_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE datenbasis_id_seq OWNED BY datenbasis.id; + + +-- +-- Name: datensatz_erzeuger; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE datensatz_erzeuger ( + id integer NOT NULL, + netzbetreiber_id character varying(2), + da_erzeuger_id character varying(2), + mst_id character varying(5), + bezeichnung character varying(120), + letzte_aenderung timestamp without time zone +); + + +-- +-- Name: datensatz_erzeuger_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE datensatz_erzeuger_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: datensatz_erzeuger_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE datensatz_erzeuger_id_seq OWNED BY datensatz_erzeuger.id; + + +-- +-- Name: de_vg; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE de_vg ( + id integer NOT NULL, + use double precision, + rs character varying(12), + gf double precision, + rau_rs character varying(12), + gen character varying(50), + des character varying(75), + isn double precision, + bemerk character varying(75), + nambild character varying(16), + ags character varying(12), + rs_alt character varying(20), + wirksamkei date, + debkg_id character varying(16), + length numeric, + shape_area numeric, + geom public.geometry(MultiPolygon,4326) +); + + +-- +-- Name: de_vg_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE de_vg_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: de_vg_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE de_vg_id_seq OWNED BY de_vg.id; + + +-- +-- Name: deskriptor_umwelt; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE deskriptor_umwelt ( + id integer NOT NULL, + s00 integer NOT NULL, + s01 integer NOT NULL, + s02 integer, + s03 integer, + s04 integer, + s05 integer, + s06 integer, + s07 integer, + s08 integer, + s09 integer, + s10 integer, + s11 integer, + s12 integer, + umw_id character varying(3) NOT NULL +); + + +-- +-- Name: deskriptoren; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE deskriptoren ( + id integer NOT NULL, + vorgaenger integer, + ebene smallint, + s_xx integer, + sn smallint, + beschreibung character varying(100), + bedeutung character varying(300) +); + + +-- +-- Name: deskriptoren_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE deskriptoren_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: deskriptoren_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE deskriptoren_id_seq OWNED BY deskriptoren.id; + + +-- +-- Name: favorite; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE favorite ( + id integer NOT NULL, + user_id integer NOT NULL, + query_id integer NOT NULL +); + + +-- +-- Name: favorite_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE favorite_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: favorite_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE favorite_id_seq OWNED BY favorite.id; + + +-- +-- Name: filter; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE filter ( + id integer NOT NULL, + query_id integer NOT NULL, + data_index character varying(50) NOT NULL, + type character varying(10) NOT NULL, + label character varying(50) NOT NULL, + multiselect boolean +); + + +-- +-- Name: filter_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE filter_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: filter_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE filter_id_seq OWNED BY filter.id; + + +-- +-- Name: filter_value; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE filter_value ( + id integer NOT NULL, + query_id integer NOT NULL, + user_id integer NOT NULL, + filter_id integer NOT NULL, + value text +); + + +-- +-- Name: filter_value_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE filter_value_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: filter_value_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE filter_value_id_seq OWNED BY filter_value.id; + + +-- +-- Name: koordinaten_art; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE koordinaten_art ( + id integer NOT NULL, + koordinatenart character varying(50), + idf_geo_key character varying(1) +); + + +-- +-- Name: koordinaten_art_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE koordinaten_art_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: koordinaten_art_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE koordinaten_art_id_seq OWNED BY koordinaten_art.id; + + +-- +-- Name: lada_user; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE lada_user ( + id integer NOT NULL, + name character varying(80) NOT NULL +); + + +-- +-- Name: lada_user_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE lada_user_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: lada_user_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE lada_user_id_seq OWNED BY lada_user.id; + + +-- +-- Name: mess_einheit; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE mess_einheit ( + id integer NOT NULL, + beschreibung character varying(50), + einheit character varying(12), + eudf_messeinheit_id character varying(8), + umrechnungs_faktor_eudf bigint +); + + +-- +-- Name: mess_einheit_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE mess_einheit_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: mess_einheit_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE mess_einheit_id_seq OWNED BY mess_einheit.id; + + +-- +-- Name: mess_methode; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE mess_methode ( + id character varying(2) NOT NULL, + beschreibung character varying(300), + messmethode character varying(50) +); + + +-- +-- Name: mess_stelle; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE mess_stelle ( + id character varying(5) NOT NULL, + netzbetreiber_id character varying(2), + beschreibung character varying(300), + mess_stelle character varying(60), + mst_typ character varying(1), + amtskennung character varying(6) +); + + +-- +-- Name: messgroesse; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE messgroesse ( + id integer NOT NULL, + beschreibung character varying(300), + messgroesse character varying(50) NOT NULL, + default_farbe character varying(9), + idf_nuklid_key character varying(6), + ist_leitnuklid boolean DEFAULT false, + eudf_nuklid_id bigint, + kennung_bvl character varying(7) +); + + +-- +-- Name: messgroesse_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE messgroesse_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: messgroesse_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE messgroesse_id_seq OWNED BY messgroesse.id; + + +-- +-- Name: messgroessen_gruppe; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE messgroessen_gruppe ( + id integer NOT NULL, + bezeichnung character varying(80), + ist_leitnuklidgruppe character(1) DEFAULT NULL::bpchar +); + + +-- +-- Name: messgroessen_gruppe_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE messgroessen_gruppe_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: messgroessen_gruppe_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE messgroessen_gruppe_id_seq OWNED BY messgroessen_gruppe.id; + + +-- +-- Name: messprogramm_kategorie; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE messprogramm_kategorie ( + id integer NOT NULL, + netzbetreiber_id character varying(2), + mpl_id character varying(3), + bezeichnung character varying(120), + letzte_aenderung timestamp without time zone +); + + +-- +-- Name: messprogramm_kategorie_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE messprogramm_kategorie_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: messprogramm_kategorie_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE messprogramm_kategorie_id_seq OWNED BY messprogramm_kategorie.id; + + +-- +-- Name: mg_grp; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE mg_grp ( + messgroessengruppe_id integer NOT NULL, + messgroesse_id integer NOT NULL +); + + +-- +-- Name: mmt_messgroesse_grp; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE mmt_messgroesse_grp ( + messgroessengruppe_id integer NOT NULL, + mmt_id character varying(2) NOT NULL +); + + +-- +-- Name: mmt_messgroesse; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW mmt_messgroesse AS + SELECT mmt_messgroesse_grp.mmt_id, + mg_grp.messgroesse_id + FROM mmt_messgroesse_grp, + mg_grp + WHERE (mg_grp.messgroessengruppe_id = mmt_messgroesse_grp.messgroessengruppe_id); + + +-- +-- Name: netz_betreiber; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE netz_betreiber ( + id character varying(2) NOT NULL, + netzbetreiber character varying(50), + idf_netzbetreiber character varying(1), + is_bmn boolean DEFAULT false, + mailverteiler character varying(512), + aktiv boolean DEFAULT false, + zust_mst_id character varying(5) +); + + +-- +-- Name: ort; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE ort ( + id integer NOT NULL, + netzbetreiber_id character varying(2), + ort_id character varying(10), + langtext character varying(100), + staat_id smallint, + gem_id character varying(8), + unscharf character(1) DEFAULT NULL::bpchar, + nuts_code character varying(10), + kda_id integer, + koord_x_extern character varying(22), + koord_y_extern character varying(22), + hoehe_land real, + letzte_aenderung timestamp without time zone DEFAULT now(), + latitude double precision, + longitude double precision, + geom public.geometry(Point,4326), + shape public.geometry(MultiPolygon,4326), + ort_typ smallint, + kurztext character varying(15), + berichtstext character varying(70), + zone character varying(1), + sektor character varying(2), + zustaendigkeit character varying(10), + mp_art character varying(10), + aktiv character(1), + anlage_id integer, + oz_id integer +); + + +-- +-- Name: ort_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE ort_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: ort_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE ort_id_seq OWNED BY ort.id; + + +-- +-- Name: ort_typ; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE ort_typ ( + id smallint NOT NULL, + ort_typ character varying(60) +); + + +-- +-- Name: ortszuordnung_typ; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE ortszuordnung_typ ( + id character(1) NOT NULL, + ortstyp character varying(60) +); + + +-- +-- Name: pflicht_messgroesse; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE pflicht_messgroesse ( + id integer NOT NULL, + messgroesse_id integer, + mmt_id character varying(2), + umw_id character varying(3), + datenbasis_id smallint NOT NULL +); + + +-- +-- Name: pflicht_messgroesse_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE pflicht_messgroesse_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: pflicht_messgroesse_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE pflicht_messgroesse_id_seq OWNED BY pflicht_messgroesse.id; + + +-- +-- Name: proben_zusatz; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE proben_zusatz ( + id character varying(3) NOT NULL, + meh_id integer, + beschreibung character varying(50) NOT NULL, + zusatzwert character varying(7) NOT NULL, + eudf_keyword character varying(40) +); + + +-- +-- Name: probenart; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE probenart ( + id integer NOT NULL, + beschreibung character varying(30), + probenart character varying(5) NOT NULL, + probenart_eudf_id character varying(1) NOT NULL +); + + +-- +-- Name: probenart_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE probenart_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: probenart_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE probenart_id_seq OWNED BY probenart.id; + + +-- +-- Name: probenehmer; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE probenehmer ( + id integer NOT NULL, + netzbetreiber_id character varying(2), + prn_id character varying(9), + bearbeiter character varying(25), + bemerkung character varying(60), + betrieb character varying(80), + bezeichnung character varying(80), + kurz_bezeichnung character varying(10), + ort character varying(20), + plz character varying(5), + strasse character varying(30), + telefon character varying(20), + tp character varying(3), + typ character(1), + letzte_aenderung timestamp without time zone +); + + +-- +-- Name: probenehmer_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE probenehmer_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: probenehmer_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE probenehmer_id_seq OWNED BY probenehmer.id; + + +-- +-- Name: query; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE query ( + id integer NOT NULL, + name character varying(80) NOT NULL, + type character varying(30) NOT NULL, + sql character varying(1500) NOT NULL, + description character varying(100) +); + + +-- +-- Name: query_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE query_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: query_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE query_id_seq OWNED BY query.id; + + +-- +-- Name: result; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE result ( + id integer NOT NULL, + query_id integer NOT NULL, + data_index character varying(50) NOT NULL, + header character varying(50) NOT NULL, + width integer, + flex boolean, + index integer +); + + +-- +-- Name: result_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE result_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: result_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE result_id_seq OWNED BY result.id; + + +-- +-- Name: s_00_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_00_view AS + SELECT deskriptoren.s_xx AS s00, + deskriptoren.bedeutung, + deskriptoren.beschreibung, + deskriptoren.sn + FROM deskriptoren + WHERE (deskriptoren.ebene = 0); + + +-- +-- Name: s_01_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_01_view AS + SELECT d1.s_xx AS s01, + d2.s_xx AS s00, + d1.bedeutung, + d1.beschreibung, + d1.sn + FROM (deskriptoren d1 + JOIN deskriptoren d2 ON ((d1.vorgaenger = d2.id))) + WHERE (d1.ebene = 1); + + +-- +-- Name: s_02_s_01_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_02_s_01_view AS + SELECT d1.s_xx AS s01, + d2.s_xx AS s02 + FROM (deskriptoren d1 + JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id))) + WHERE (d2.ebene = 2); + + +-- +-- Name: s_02_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_02_view AS + SELECT DISTINCT deskriptoren.s_xx AS s00, + deskriptoren.bedeutung, + deskriptoren.beschreibung, + deskriptoren.sn + FROM deskriptoren + WHERE (deskriptoren.ebene = 2); + + +-- +-- Name: s_03_s_01_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_03_s_01_view AS + SELECT d1.s_xx AS s01, + d2.s_xx AS s03 + FROM (deskriptoren d1 + JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id))) + WHERE ((d2.ebene = 3) AND (d1.ebene = 1)); + + +-- +-- Name: s_03_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_03_view AS + SELECT d1.s_xx AS s03, + d2.s_xx AS s02, + d1.bedeutung, + d1.beschreibung, + d1.sn + FROM (deskriptoren d1 + JOIN deskriptoren d2 ON ((d1.vorgaenger = d2.id))) + WHERE ((d1.ebene = 3) AND (d2.ebene = 2)) +UNION + SELECT d1.s_xx AS s03, + NULL::integer AS s02, + d1.bedeutung, + d1.beschreibung, + d1.sn + FROM (deskriptoren d1 + JOIN deskriptoren d2 ON ((d1.vorgaenger = d2.id))) + WHERE ((d1.ebene = 3) AND (d2.ebene = 1)); + + +-- +-- Name: s_04_s_01_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_04_s_01_view AS + SELECT DISTINCT d1.s_xx AS s01, + d2.s_xx AS s04 + FROM (deskriptoren d1 + JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id))) + WHERE (d2.ebene = 4); + + +-- +-- Name: s_04_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_04_view AS + SELECT DISTINCT deskriptoren.s_xx AS s04, + deskriptoren.bedeutung, + deskriptoren.beschreibung, + deskriptoren.sn + FROM deskriptoren + WHERE (deskriptoren.ebene = 4); + + +-- +-- Name: s_05_s_01_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_05_s_01_view AS + SELECT d1.s_xx AS s01, + d2.s_xx AS s05 + FROM (deskriptoren d1 + JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id))) + WHERE (d2.ebene = 5); + + +-- +-- Name: s_05_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_05_view AS + SELECT DISTINCT deskriptoren.s_xx AS s05, + deskriptoren.bedeutung, + deskriptoren.beschreibung, + deskriptoren.sn + FROM deskriptoren + WHERE (deskriptoren.ebene = 5); + + +-- +-- Name: s_06_s_01_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_06_s_01_view AS + SELECT d1.s_xx AS s01, + d2.s_xx AS s06 + FROM (deskriptoren d1 + JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id))) + WHERE (d2.ebene = 6); + + +-- +-- Name: s_06_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_06_view AS + SELECT DISTINCT deskriptoren.s_xx AS s06, + deskriptoren.bedeutung, + deskriptoren.beschreibung, + deskriptoren.sn + FROM deskriptoren + WHERE (deskriptoren.ebene = 6); + + +-- +-- Name: s_07_s_01_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_07_s_01_view AS + SELECT d1.s_xx AS s01, + d2.s_xx AS s07 + FROM (deskriptoren d1 + JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id))) + WHERE (d2.ebene = 7); + + +-- +-- Name: s_07_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_07_view AS + SELECT DISTINCT deskriptoren.s_xx AS s07, + deskriptoren.bedeutung, + deskriptoren.beschreibung, + deskriptoren.sn + FROM deskriptoren + WHERE (deskriptoren.ebene = 7); + + +-- +-- Name: s_08_s_01_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_08_s_01_view AS + SELECT d1.s_xx AS s01, + d2.s_xx AS s08 + FROM (deskriptoren d1 + JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id))) + WHERE (d2.ebene = 8); + + +-- +-- Name: s_08_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_08_view AS + SELECT DISTINCT deskriptoren.s_xx AS s08, + deskriptoren.bedeutung, + deskriptoren.beschreibung, + deskriptoren.sn + FROM deskriptoren + WHERE (deskriptoren.ebene = 8); + + +-- +-- Name: s_09_s_01_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_09_s_01_view AS + SELECT d1.s_xx AS s01, + d2.s_xx AS s09 + FROM (deskriptoren d1 + JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id))) + WHERE (d2.ebene = 9); + + +-- +-- Name: s_09_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_09_view AS + SELECT DISTINCT deskriptoren.s_xx AS s09, + deskriptoren.bedeutung, + deskriptoren.beschreibung, + deskriptoren.sn + FROM deskriptoren + WHERE (deskriptoren.ebene = 9); + + +-- +-- Name: s_10_s_01_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_10_s_01_view AS + SELECT d1.s_xx AS s01, + d2.s_xx AS s10 + FROM (deskriptoren d1 + JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id))) + WHERE (d2.ebene = 10); + + +-- +-- Name: s_10_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_10_view AS + SELECT DISTINCT deskriptoren.s_xx AS s10, + deskriptoren.bedeutung, + deskriptoren.beschreibung, + deskriptoren.sn + FROM deskriptoren + WHERE (deskriptoren.ebene = 10); + + +-- +-- Name: s_11_s_01_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_11_s_01_view AS + SELECT d1.s_xx AS s01, + d2.s_xx AS s11 + FROM (deskriptoren d1 + JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id))) + WHERE (d2.ebene = 11); + + +-- +-- Name: s_11_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_11_view AS + SELECT DISTINCT deskriptoren.s_xx AS s11, + deskriptoren.bedeutung, + deskriptoren.beschreibung, + deskriptoren.sn + FROM deskriptoren + WHERE (deskriptoren.ebene = 11); + + +-- +-- Name: s_12_s_01_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_12_s_01_view AS + SELECT d1.s_xx AS s01, + d2.s_xx AS s12 + FROM (deskriptoren d1 + JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id))) + WHERE (d2.ebene = 12); + + +-- +-- Name: s_12_view; Type: VIEW; Schema: stammdaten; Owner: - +-- + +CREATE VIEW s_12_view AS + SELECT DISTINCT deskriptoren.s_xx AS s12, + deskriptoren.bedeutung, + deskriptoren.beschreibung, + deskriptoren.sn + FROM deskriptoren + WHERE (deskriptoren.ebene = 12); + + +-- +-- Name: staat; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE staat ( + id integer NOT NULL, + staat character varying(50) NOT NULL, + hkl_id smallint NOT NULL, + staat_iso character varying(2) NOT NULL, + staat_kurz character varying(5), + eu character(1) DEFAULT NULL::bpchar, + koord_x_extern character varying(22), + koord_y_extern character varying(22), + kda_id integer +); + + +-- +-- Name: staat_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: - +-- + +CREATE SEQUENCE staat_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: staat_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: - +-- + +ALTER SEQUENCE staat_id_seq OWNED BY staat.id; + + +-- +-- Name: status_kombi; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE status_kombi ( + id integer NOT NULL, + stufe_id integer, + wert_id integer +); + + +-- +-- Name: status_reihenfolge; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE status_reihenfolge ( + id integer NOT NULL, + von_id integer, + zu_id integer +); + + +-- +-- Name: status_stufe; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE status_stufe ( + id integer NOT NULL, + stufe character varying(50) +); + + +-- +-- Name: status_wert; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE status_wert ( + id integer NOT NULL, + wert character varying(50) +); + + +-- +-- Name: umwelt; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE umwelt ( + id character varying(3) NOT NULL, + beschreibung character varying(300), + umwelt_bereich character varying(80) NOT NULL, + meh_id integer +); + + +-- +-- Name: verwaltungseinheit; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE TABLE verwaltungseinheit ( + id character varying(8) NOT NULL, + bundesland character varying(8) NOT NULL, + kda_id integer, + kreis character varying(8), + nuts character varying(10), + regbezirk character varying(8), + bezeichnung character varying(80) NOT NULL, + is_bundesland character(1) NOT NULL, + is_gemeinde character(1) NOT NULL, + is_landkreis character(1) NOT NULL, + is_regbezirk character(1) NOT NULL, + koord_x_extern character varying(22), + koord_y_extern character varying(22), + plz character varying(6), + longitude double precision, + latitude double precision +); + +-- +-- Name status_erreichbar; Type: VIEW; Schema: stammdaten; Owner: -; +-- +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 +); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY auth ALTER COLUMN id SET DEFAULT nextval('auth_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY datenbasis ALTER COLUMN id SET DEFAULT nextval('datenbasis_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY datensatz_erzeuger ALTER COLUMN id SET DEFAULT nextval('datensatz_erzeuger_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY de_vg ALTER COLUMN id SET DEFAULT nextval('de_vg_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY deskriptoren ALTER COLUMN id SET DEFAULT nextval('deskriptoren_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY favorite ALTER COLUMN id SET DEFAULT nextval('favorite_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY filter ALTER COLUMN id SET DEFAULT nextval('filter_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY filter_value ALTER COLUMN id SET DEFAULT nextval('filter_value_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY koordinaten_art ALTER COLUMN id SET DEFAULT nextval('koordinaten_art_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY lada_user ALTER COLUMN id SET DEFAULT nextval('lada_user_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY mess_einheit ALTER COLUMN id SET DEFAULT nextval('mess_einheit_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY messgroesse ALTER COLUMN id SET DEFAULT nextval('messgroesse_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY messgroessen_gruppe ALTER COLUMN id SET DEFAULT nextval('messgroessen_gruppe_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY messprogramm_kategorie ALTER COLUMN id SET DEFAULT nextval('messprogramm_kategorie_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY ort ALTER COLUMN id SET DEFAULT nextval('ort_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY pflicht_messgroesse ALTER COLUMN id SET DEFAULT nextval('pflicht_messgroesse_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY probenart ALTER COLUMN id SET DEFAULT nextval('probenart_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY probenehmer ALTER COLUMN id SET DEFAULT nextval('probenehmer_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY query ALTER COLUMN id SET DEFAULT nextval('query_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY result ALTER COLUMN id SET DEFAULT nextval('result_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY staat ALTER COLUMN id SET DEFAULT nextval('staat_id_seq'::regclass); + + +-- +-- Name: auth_lst_umw_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY auth_lst_umw + ADD CONSTRAINT auth_lst_umw_pkey PRIMARY KEY (id); + + +-- +-- Name: auth_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY auth + ADD CONSTRAINT auth_pkey PRIMARY KEY (id); + + +-- +-- Name: auth_role_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY auth_funktion + ADD CONSTRAINT auth_role_pkey PRIMARY KEY (id); + + +-- +-- Name: datenbasis_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY datenbasis + ADD CONSTRAINT datenbasis_pkey PRIMARY KEY (id); + + +-- +-- Name: datensatz_erzeuger_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY datensatz_erzeuger + ADD CONSTRAINT datensatz_erzeuger_pkey PRIMARY KEY (id); + + +-- +-- Name: de_vg_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY de_vg + ADD CONSTRAINT de_vg_pkey PRIMARY KEY (id); + + +-- +-- Name: deskriptor_umwelt_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY deskriptor_umwelt + ADD CONSTRAINT deskriptor_umwelt_pkey PRIMARY KEY (id); + + +-- +-- Name: favorite_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY favorite + ADD CONSTRAINT favorite_pkey PRIMARY KEY (id); + + +-- +-- Name: filter_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY filter + ADD CONSTRAINT filter_pkey PRIMARY KEY (id); + + +-- +-- Name: filter_value_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY filter_value + ADD CONSTRAINT filter_value_pkey PRIMARY KEY (id); + + +-- +-- Name: koordinaten_art_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY koordinaten_art + ADD CONSTRAINT koordinaten_art_pkey PRIMARY KEY (id); + + +-- +-- Name: lada_user_name_key; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY lada_user + ADD CONSTRAINT lada_user_name_key UNIQUE (name); + + +-- +-- Name: lada_user_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY lada_user + ADD CONSTRAINT lada_user_pkey PRIMARY KEY (id); + + +-- +-- Name: mess_einheit_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY mess_einheit + ADD CONSTRAINT mess_einheit_pkey PRIMARY KEY (id); + + +-- +-- Name: mess_methode_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY mess_methode + ADD CONSTRAINT mess_methode_pkey PRIMARY KEY (id); + + +-- +-- Name: mess_stelle_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY mess_stelle + ADD CONSTRAINT mess_stelle_pkey PRIMARY KEY (id); + + +-- +-- Name: messgroesse_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY messgroesse + ADD CONSTRAINT messgroesse_pkey PRIMARY KEY (id); + + +-- +-- Name: messgroessen_gruppe_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY messgroessen_gruppe + ADD CONSTRAINT messgroessen_gruppe_pkey PRIMARY KEY (id); + + +-- +-- Name: messprogramm_kategorie_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY messprogramm_kategorie + ADD CONSTRAINT messprogramm_kategorie_pkey PRIMARY KEY (id); + + +-- +-- Name: mg_grp_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY mg_grp + ADD CONSTRAINT mg_grp_pkey PRIMARY KEY (messgroessengruppe_id, messgroesse_id); + + +-- +-- Name: mmt_messgroesse_grp_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY mmt_messgroesse_grp + ADD CONSTRAINT mmt_messgroesse_grp_pkey PRIMARY KEY (messgroessengruppe_id, mmt_id); + + +-- +-- Name: netz_betreiber_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY netz_betreiber + ADD CONSTRAINT netz_betreiber_pkey PRIMARY KEY (id); + + +-- +-- Name: ort_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY ort + ADD CONSTRAINT ort_pkey PRIMARY KEY (id); + + +-- +-- Name: ort_typ_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY ort_typ + ADD CONSTRAINT ort_typ_pkey PRIMARY KEY (id); + + +-- +-- Name: ortszuordnung_typ_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY ortszuordnung_typ + ADD CONSTRAINT ortszuordnung_typ_pkey PRIMARY KEY (id); + + +-- +-- Name: pflicht_messgroesse_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY pflicht_messgroesse + ADD CONSTRAINT pflicht_messgroesse_pkey PRIMARY KEY (id); + + +-- +-- Name: pk_deskriptoren; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY deskriptoren + ADD CONSTRAINT pk_deskriptoren PRIMARY KEY (id); + + +-- +-- Name: proben_zusatz_eudf_keyword_key; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY proben_zusatz + ADD CONSTRAINT proben_zusatz_eudf_keyword_key UNIQUE (eudf_keyword); + + +-- +-- Name: proben_zusatz_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY proben_zusatz + ADD CONSTRAINT proben_zusatz_pkey PRIMARY KEY (id); + + +-- +-- Name: probenart_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY probenart + ADD CONSTRAINT probenart_pkey PRIMARY KEY (id); + + +-- +-- Name: probenehmer_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY probenehmer + ADD CONSTRAINT probenehmer_pkey PRIMARY KEY (id); + + +-- +-- Name: query_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY query + ADD CONSTRAINT query_pkey PRIMARY KEY (id); + + +-- +-- Name: result_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY result + ADD CONSTRAINT result_pkey PRIMARY KEY (id); + + +-- +-- Name: staat_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY staat + ADD CONSTRAINT staat_pkey PRIMARY KEY (id); + + +-- +-- Name: status_kombi_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY status_kombi + ADD CONSTRAINT status_kombi_pkey PRIMARY KEY (id); + + +-- +-- Name: status_reihenfolge_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY status_reihenfolge + ADD CONSTRAINT status_reihenfolge_pkey PRIMARY KEY (id); + + +-- +-- Name: status_stufe_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY status_stufe + ADD CONSTRAINT status_stufe_pkey PRIMARY KEY (id); + + +-- +-- Name: status_wert_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY status_wert + ADD CONSTRAINT status_wert_pkey PRIMARY KEY (id); + + +-- +-- Name: umwelt_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY umwelt + ADD CONSTRAINT umwelt_pkey PRIMARY KEY (id); + + +-- +-- Name: umwelt_umwelt_bereich_key; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY umwelt + ADD CONSTRAINT umwelt_umwelt_bereich_key UNIQUE (umwelt_bereich); + + +-- +-- Name: verwaltungseinheit_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY verwaltungseinheit + ADD CONSTRAINT verwaltungseinheit_pkey PRIMARY KEY (id); + + +-- +-- Name: de_vg_geom_gist; Type: INDEX; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE INDEX de_vg_geom_gist ON de_vg USING gist (geom); + + +-- +-- Name: fts_stauts_kooin10001; Type: INDEX; Schema: stammdaten; Owner: -; Tablespace: +-- + +CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id); + + +-- +-- Name: auth_funktion_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY auth + ADD CONSTRAINT auth_funktion_fkey FOREIGN KEY (funktion_id) REFERENCES auth_funktion(id); + + +-- +-- Name: auth_labor_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY auth + ADD CONSTRAINT auth_labor_fkey FOREIGN KEY (labor_mst_id) REFERENCES mess_stelle(id); + + +-- +-- Name: auth_lst_umw_lst_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY auth_lst_umw + ADD CONSTRAINT auth_lst_umw_lst_fkey FOREIGN KEY (lst_id) REFERENCES mess_stelle(id); + + +-- +-- Name: auth_lst_umw_umw_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY auth_lst_umw + ADD CONSTRAINT auth_lst_umw_umw_fkey FOREIGN KEY (umw_id) REFERENCES umwelt(id); + + +-- +-- Name: auth_mst_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY auth + ADD CONSTRAINT auth_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES mess_stelle(id); + + +-- +-- Name: auth_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY auth + ADD CONSTRAINT auth_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id); + + +-- +-- Name: datensatz_erzeuger_mst_id_fkey1; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY datensatz_erzeuger + ADD CONSTRAINT datensatz_erzeuger_mst_id_fkey1 FOREIGN KEY (mst_id) REFERENCES mess_stelle(id); + + +-- +-- Name: datensatz_erzeuger_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY datensatz_erzeuger + ADD CONSTRAINT datensatz_erzeuger_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id); + + +-- +-- Name: favorite_query_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY favorite + ADD CONSTRAINT favorite_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id); + + +-- +-- Name: favorite_user_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY favorite + ADD CONSTRAINT favorite_user_id_fkey FOREIGN KEY (user_id) REFERENCES lada_user(id); + + +-- +-- Name: filter_query_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY filter + ADD CONSTRAINT filter_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id); + + +-- +-- Name: filter_value_filter_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY filter_value + ADD CONSTRAINT filter_value_filter_id_fkey FOREIGN KEY (filter_id) REFERENCES filter(id); + + +-- +-- Name: filter_value_query_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY filter_value + ADD CONSTRAINT filter_value_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id); + + +-- +-- Name: filter_value_user_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY filter_value + ADD CONSTRAINT filter_value_user_id_fkey FOREIGN KEY (user_id) REFERENCES lada_user(id); + + +-- +-- Name: fk_deskriptoren_vorgaenger; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY deskriptoren + ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id); + + +-- +-- Name: messprogramm_kategorie_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY messprogramm_kategorie + ADD CONSTRAINT messprogramm_kategorie_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id); + + +-- +-- Name: ort_anlage_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY ort + ADD CONSTRAINT ort_anlage_fkey FOREIGN KEY (anlage_id) REFERENCES ort(id); + + +-- +-- Name: ort_gem_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY ort + ADD CONSTRAINT ort_gem_id_fkey FOREIGN KEY (gem_id) REFERENCES verwaltungseinheit(id); + + +-- +-- Name: ort_kda_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY ort + ADD CONSTRAINT ort_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id); + + +-- +-- Name: ort_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY ort + ADD CONSTRAINT ort_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id); + + +-- +-- Name: ort_ort_typ_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY ort + ADD CONSTRAINT ort_ort_typ_fkey FOREIGN KEY (ort_typ) REFERENCES ort_typ(id); + + +-- +-- Name: ort_oz_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY ort + ADD CONSTRAINT ort_oz_fkey FOREIGN KEY (oz_id) REFERENCES ort(id); + + +-- +-- Name: ort_staat_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY ort + ADD CONSTRAINT ort_staat_id_fkey FOREIGN KEY (staat_id) REFERENCES staat(id); + + +-- +-- Name: pflicht_messgroesse_datenbasis_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY pflicht_messgroesse + ADD CONSTRAINT pflicht_messgroesse_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES datenbasis(id); + + +-- +-- Name: pflicht_messgroesse_mmt_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY pflicht_messgroesse + ADD CONSTRAINT pflicht_messgroesse_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES mess_methode(id); + + +-- +-- Name: pflicht_messgroesse_umw_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY pflicht_messgroesse + ADD CONSTRAINT pflicht_messgroesse_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES umwelt(id); + + +-- +-- Name: proben_zusatz_meh_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY proben_zusatz + ADD CONSTRAINT proben_zusatz_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id); + + +-- +-- Name: probenehmer_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY probenehmer + ADD CONSTRAINT probenehmer_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id); + + +-- +-- Name: result_query_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY result + ADD CONSTRAINT result_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id); + + +-- +-- Name: staat_kda_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY staat + ADD CONSTRAINT staat_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id); + + +-- +-- Name: status_kombi_stufe_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY status_kombi + ADD CONSTRAINT status_kombi_stufe_id_fkey FOREIGN KEY (stufe_id) REFERENCES status_stufe(id); + + +-- +-- Name: status_kombi_wert_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY status_kombi + ADD CONSTRAINT status_kombi_wert_id_fkey FOREIGN KEY (wert_id) REFERENCES status_wert(id); + + +-- +-- Name: status_reihenfolge_von_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY status_reihenfolge + ADD CONSTRAINT status_reihenfolge_von_id_fkey FOREIGN KEY (von_id) REFERENCES status_kombi(id); + + +-- +-- Name: status_reihenfolge_zu_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY status_reihenfolge + ADD CONSTRAINT status_reihenfolge_zu_id_fkey FOREIGN KEY (zu_id) REFERENCES status_kombi(id); + + +-- +-- Name: umwelt_meh_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY umwelt + ADD CONSTRAINT umwelt_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id); + + +-- +-- Name: verwaltungseinheit_kda_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: - +-- + +ALTER TABLE ONLY verwaltungseinheit + ADD CONSTRAINT verwaltungseinheit_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id); + + +COMMIT;