Mercurial > lada > lada-server
diff db_schema/lada_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 | 47dc3c4e42dd |
| children | 9293d37c65f7 |
line wrap: on
line diff
--- a/db_schema/lada_schema.sql Fri Feb 05 14:51:14 2016 +0100 +++ b/db_schema/lada_schema.sql Fri Feb 05 17:01:28 2016 +0100 @@ -2,9 +2,6 @@ BEGIN; --- --- PostgreSQL database dump --- SET statement_timeout = 0; SET lock_timeout = 0; @@ -27,41 +24,6 @@ CREATE SCHEMA land; --- --- Name: stammdaten; Type: SCHEMA; Schema: -; Owner: - --- - -CREATE SCHEMA stammdaten; - - --- --- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: - --- - -CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; - - --- --- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: - --- - -COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; - - --- --- Name: postgis; Type: EXTENSION; Schema: -; Owner: - --- - -CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public; - - --- --- Name: EXTENSION postgis; Type: COMMENT; Schema: -; Owner: - --- - -COMMENT ON EXTENSION postgis IS 'PostGIS geometry, geography, and raster spatial types and functions'; - - SET search_path = bund, pg_catalog; -- @@ -173,74 +135,6 @@ $$; -SET search_path = stammdaten, pg_catalog; - --- --- Name: get_media_from_media_desk(character varying); Type: FUNCTION; Schema: stammdaten; Owner: - --- - -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; -$$; - - SET search_path = bund, pg_catalog; -- @@ -841,1323 +735,6 @@ INHERITS (bund.zusatz_wert); -SET search_path = stammdaten, pg_catalog; - --- --- Name: auth; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: --- - -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 -); - - - SET search_path = bund, pg_catalog; -- @@ -2379,155 +956,6 @@ ALTER TABLE ONLY zusatz_wert ALTER COLUMN letzte_aenderung SET DEFAULT now(); -SET search_path = stammdaten, pg_catalog; - --- --- 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); - - SET search_path = bund, pg_catalog; -- @@ -2740,336 +1168,6 @@ ADD CONSTRAINT zusatz_wert_probe_id_pzs_id_key UNIQUE (probe_id, pzs_id); -SET search_path = stammdaten, pg_catalog; - --- --- 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); - - SET search_path = bund, pg_catalog; -- @@ -3123,22 +1221,6 @@ CREATE INDEX probe_translation_probe_id_idx ON probe_translation USING btree (probe_id); -SET search_path = stammdaten, pg_catalog; - --- --- 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); - - SET search_path = bund, pg_catalog; -- @@ -3589,308 +1671,4 @@ ADD CONSTRAINT zusatz_wert_pzs_id_fkey FOREIGN KEY (pzs_id) REFERENCES stammdaten.proben_zusatz(id); -SET search_path = stammdaten, pg_catalog; - --- --- 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); - - --- --- Name: public; Type: ACL; Schema: -; Owner: - --- - -REVOKE ALL ON SCHEMA public FROM PUBLIC; -REVOKE ALL ON SCHEMA public FROM postgres; -GRANT ALL ON SCHEMA public TO postgres; -GRANT ALL ON SCHEMA public TO PUBLIC; - - --- --- PostgreSQL database dump complete --- - COMMIT;