Mercurial > lada > lada-server
changeset 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 | 186d30e5b44a |
| children | 66069d69d6ee |
| files | db_schema/lada_schema.sql db_schema/setup-db.sh db_schema/stammdaten_schema.sql |
| diffstat | 3 files changed, 2172 insertions(+), 2003 deletions(-) [+] |
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;
--- a/db_schema/setup-db.sh Fri Feb 05 14:51:14 2016 +0100 +++ b/db_schema/setup-db.sh Fri Feb 05 17:01:28 2016 +0100 @@ -7,6 +7,11 @@ psql --command "CREATE USER $ROLE_NAME PASSWORD '$ROLE_PW';" createdb -E UTF-8 $DB_NAME + +psql -d $DB_NAME --command \ + "CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public" + +psql -d $DB_NAME -f $DIR/stammdaten_schema.sql psql -d $DB_NAME -f $DIR/lada_schema.sql psql -d $DB_NAME --command \ "GRANT USAGE ON SCHEMA stammdaten, bund, land TO $ROLE_NAME;
--- /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;