changeset 746:9f3029b5fc90

merged.
author Raimund Renkert <raimund.renkert@intevation.de>
date Tue, 27 Oct 2015 09:59:21 +0100
parents e2a78d5afaaa (current diff) c7fcc46c6a57 (diff)
children 214508fd95e7
files
diffstat 7 files changed, 3352 insertions(+), 9 deletions(-) [+]
line wrap: on
line diff
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/db_schema/Dockerfile	Tue Oct 27 09:59:21 2015 +0100
@@ -0,0 +1,69 @@
+# Docker file for postgresql 9.4 on debain
+#
+# build with e.g. `docker build --force-rm=true -t koala/lada_db .',
+# then run with e.g.
+# `docker run --name lada_db -dp 2345:5432 koala/lada_db:latest'
+#
+
+FROM debian:jessie
+MAINTAINER tom.gottfried@intevation.de
+
+#
+# Use utf-8
+#
+RUN echo \
+    "locales locales/locales_to_be_generated multiselect en_US.UTF-8 UTF-8" | \
+    debconf-set-selections && \
+    echo "locales locales/default_environment_locale select en_US.UTF-8" | \
+    debconf-set-selections
+
+RUN apt-get update -y && apt-get install -y locales
+
+ENV LC_ALL en_US.UTF-8
+
+#
+# Install packages
+#
+RUN apt-get update && \
+    apt-get install -y postgresql-9.4-postgis-2.1 postgis
+
+#
+# Use user postgres to run the next commands
+#
+USER postgres
+
+RUN /etc/init.d/postgresql start && \
+    psql --command "CREATE USER admin WITH SUPERUSER PASSWORD 'secret';"
+
+#
+# Adjust PostgreSQL configuration so that remote connections to the
+# database are possible.
+#
+RUN echo "host all  all    0.0.0.0/0  md5" >> \
+    /etc/postgresql/9.4/main/pg_hba.conf
+
+RUN echo "listen_addresses='*'" >> /etc/postgresql/9.4/main/postgresql.conf
+
+#
+# Expose the PostgreSQL port
+#
+EXPOSE 5432
+
+#
+# Create database
+#
+# Don't mind scary messages like
+# 'FATAL: the database system is starting up'.
+# It's because of the -w
+#
+ADD . /opt/lada_sql/
+
+RUN /usr/lib/postgresql/9.4/bin/pg_ctl start -wD /etc/postgresql/9.4/main/ && \
+    /opt/lada_sql/setup-db.sh
+
+#
+# Set the default command to run when starting the container
+#
+CMD ["/usr/lib/postgresql/9.4/bin/postgres", "-D", \
+     "/var/lib/postgresql/9.4/main", "-c", \
+     "config_file=/etc/postgresql/9.4/main/postgresql.conf"]
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/db_schema/lada_schema.sql	Tue Oct 27 09:59:21 2015 +0100
@@ -0,0 +1,3222 @@
+--
+-- PostgreSQL database dump
+--
+
+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;
+
+--
+-- Name: bund; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA bund;
+
+
+--
+-- Name: land; Type: SCHEMA; Schema: -; Owner: -
+--
+
+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;
+
+--
+-- Name: is_kommentar_unique(); Type: FUNCTION; Schema: bund; Owner: -
+--
+
+CREATE FUNCTION is_kommentar_unique() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+    BEGIN
+        -- Check that empname and salary are given
+        IF NEW.id IN (SELECT id from bund.kommentar) THEN
+            RAISE EXCEPTION 'Key (id)=(%) already present in bund.messung', NEW.id;
+        ELSE
+            RETURN NEW;
+        END IF;
+    END;
+$$;
+
+
+--
+-- Name: is_messung_unique(); Type: FUNCTION; Schema: bund; Owner: -
+--
+
+CREATE FUNCTION is_messung_unique() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+    BEGIN
+        -- Check that empname and salary are given
+        IF NEW.id IN (SELECT id from bund.messung) THEN
+            RAISE EXCEPTION 'Key (id)=(%) already present in bund.messung', NEW.id;
+        ELSE
+            RETURN NEW;
+        END IF;
+    END;
+$$;
+
+
+--
+-- Name: is_messwert_unique(); Type: FUNCTION; Schema: bund; Owner: -
+--
+
+CREATE FUNCTION is_messwert_unique() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+    BEGIN
+        -- Check that empname and salary are given
+        IF NEW.id IN (SELECT id from bund.messwert) THEN
+            RAISE EXCEPTION 'Key (id)=(%) already present in bund.messwert', NEW.id;
+        ELSE
+            RETURN NEW;
+        END IF;
+    END;
+$$;
+
+
+--
+-- Name: is_ort_unique(); Type: FUNCTION; Schema: bund; Owner: -
+--
+
+CREATE FUNCTION is_ort_unique() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+    BEGIN
+        -- Check that empname and salary are given
+        IF NEW.id IN (SELECT id from bund.ort) THEN
+            RAISE EXCEPTION 'Key (id)=(%) already present in bund.ort', NEW.id;
+        ELSE
+            RETURN NEW;
+        END IF;
+    END;
+$$;
+
+
+--
+-- Name: is_probe_unique(); Type: FUNCTION; Schema: bund; Owner: -
+--
+
+CREATE FUNCTION is_probe_unique() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+    BEGIN
+        -- Check that empname and salary are given
+        IF NEW.id IN (SELECT id from bund.probe) THEN
+            RAISE EXCEPTION 'Key (id)=(%) already present in bund.probe', NEW.id;
+        ELSE
+            RETURN NEW;
+        END IF;
+    END;
+$$;
+
+
+--
+-- Name: is_status_unique(); Type: FUNCTION; Schema: bund; Owner: -
+--
+
+CREATE FUNCTION is_status_unique() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+    BEGIN
+        -- Check that empname and salary are given
+        IF NEW.id IN (SELECT id from bund.status) THEN
+            RAISE EXCEPTION 'Key (id)=(%) already present in bund.status', NEW.id;
+        ELSE
+            RETURN NEW;
+        END IF;
+    END;
+$$;
+
+
+--
+-- Name: is_zusatz_wert_unique(); Type: FUNCTION; Schema: bund; Owner: -
+--
+
+CREATE FUNCTION is_zusatz_wert_unique() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+    BEGIN
+        -- Check that empname and salary are given
+        IF NEW.id IN (SELECT id from bund.zusatz_wert) THEN
+            RAISE EXCEPTION 'Key (id)=(%) already present in bund.zusatz_wert', NEW.id;
+        ELSE
+            RETURN NEW;
+        END IF;
+    END;
+$$;
+
+
+SET search_path = land, pg_catalog;
+
+--
+-- Name: is_probe_fertig(integer); Type: FUNCTION; Schema: land; Owner: -
+--
+
+CREATE FUNCTION is_probe_fertig(i1 integer) RETURNS boolean
+    LANGUAGE plpgsql STABLE SECURITY DEFINER
+    AS $_$
+DECLARE result BOOLEAN;
+BEGIN
+        SELECT (count(id) > 0) INTO result from land.messung where probe_id = $1 and fertig = TRUE;
+        RETURN result;
+END;
+$_$;
+
+
+--
+-- Name: update_time_messung(); Type: FUNCTION; Schema: land; Owner: -
+--
+
+CREATE FUNCTION update_time_messung() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+    BEGIN
+        RAISE NOTICE 'messung is %',NEW.id;
+        NEW.tree_modified = now();
+        UPDATE land.messwert SET tree_modified = now() WHERE messungs_id = NEW.id;
+        UPDATE land.status SET tree_modified = now() WHERE messungs_id = NEW.id;
+        RETURN NEW;
+    END;
+$$;
+
+
+--
+-- Name: update_time_messwert(); Type: FUNCTION; Schema: land; Owner: -
+--
+
+CREATE FUNCTION update_time_messwert() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+    BEGIN
+        NEW.tree_modified = now();
+        RETURN NEW;
+    END;
+$$;
+
+
+--
+-- Name: update_time_ort(); Type: FUNCTION; Schema: land; Owner: -
+--
+
+CREATE FUNCTION update_time_ort() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+    BEGIN
+        NEW.tree_modified = now();
+        RETURN NEW;
+    END;
+$$;
+
+
+--
+-- Name: update_time_probe(); Type: FUNCTION; Schema: land; Owner: -
+--
+
+CREATE FUNCTION update_time_probe() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+    BEGIN
+        RAISE NOTICE 'probe is %',NEW.id;
+        NEW.tree_modified = now();
+        RAISE NOTICE 'updating other rows';
+        UPDATE land.messung SET tree_modified = now() WHERE probe_id = NEW.id;
+        UPDATE land.ort SET tree_modified = now() WHERE probe_id = NEW.id;
+        UPDATE land.zusatz_wert SET tree_modified = now() WHERE probe_id = NEW.id;
+        RETURN NEW;
+    END;
+$$;
+
+
+--
+-- Name: update_time_status(); Type: FUNCTION; Schema: land; Owner: -
+--
+
+CREATE FUNCTION update_time_status() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+    BEGIN
+        NEW.tree_modified = now();
+        RETURN NEW;
+    END;
+$$;
+
+
+--
+-- Name: update_time_zusatzwert(); Type: FUNCTION; Schema: land; Owner: -
+--
+
+CREATE FUNCTION update_time_zusatzwert() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+    BEGIN
+        NEW.tree_modified = now();
+        RETURN NEW;
+    END;
+$$;
+
+
+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;
+
+--
+-- Name: kommentar_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
+--
+
+CREATE SEQUENCE kommentar_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+SET default_tablespace = '';
+
+SET default_with_oids = false;
+
+--
+-- Name: kommentar; Type: TABLE; Schema: bund; Owner: -; Tablespace: 
+--
+
+CREATE TABLE kommentar (
+    id integer DEFAULT nextval('kommentar_id_seq'::regclass) NOT NULL,
+    erzeuger character varying(5) NOT NULL,
+    datum timestamp without time zone DEFAULT now(),
+    text character varying(1024)
+);
+
+
+--
+-- Name: kommentar_m; Type: TABLE; Schema: bund; Owner: -; Tablespace: 
+--
+
+CREATE TABLE kommentar_m (
+    messungs_id integer NOT NULL
+)
+INHERITS (kommentar);
+
+
+--
+-- Name: kommentar_p; Type: TABLE; Schema: bund; Owner: -; Tablespace: 
+--
+
+CREATE TABLE kommentar_p (
+    probe_id integer NOT NULL
+)
+INHERITS (kommentar);
+
+
+--
+-- Name: list; Type: TABLE; Schema: bund; Owner: -; Tablespace: 
+--
+
+CREATE TABLE list (
+    id integer NOT NULL,
+    typ character(1) NOT NULL,
+    bezeichnung character varying(20) NOT NULL,
+    beschreibuing character varying(512),
+    letzte_aenderung timestamp with time zone NOT NULL,
+    gueltig_bis timestamp with time zone
+);
+
+
+--
+-- Name: list_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
+--
+
+CREATE SEQUENCE list_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: list_id_seq; Type: SEQUENCE OWNED BY; Schema: bund; Owner: -
+--
+
+ALTER SEQUENCE list_id_seq OWNED BY list.id;
+
+
+--
+-- Name: list_zuordnung; Type: TABLE; Schema: bund; Owner: -; Tablespace: 
+--
+
+CREATE TABLE list_zuordnung (
+    list_id integer NOT NULL,
+    probe_id integer
+);
+
+
+--
+-- Name: messung_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
+--
+
+CREATE SEQUENCE messung_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: messung; Type: TABLE; Schema: bund; Owner: -; Tablespace: 
+--
+
+CREATE TABLE messung (
+    id integer DEFAULT nextval('messung_id_seq'::regclass) NOT NULL,
+    probe_id integer NOT NULL,
+    nebenproben_nr character varying(10),
+    mmt_id character varying(2) NOT NULL,
+    messdauer integer,
+    messzeitpunkt timestamp with time zone,
+    fertig boolean DEFAULT false NOT NULL,
+    letzte_aenderung timestamp without time zone DEFAULT now()
+);
+
+
+--
+-- Name: messung_messung_id_alt_seq; Type: SEQUENCE; Schema: bund; Owner: -
+--
+
+CREATE SEQUENCE messung_messung_id_alt_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: messung_messungs_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
+--
+
+CREATE SEQUENCE messung_messungs_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: messwert_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
+--
+
+CREATE SEQUENCE messwert_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: messwert; Type: TABLE; Schema: bund; Owner: -; Tablespace: 
+--
+
+CREATE TABLE messwert (
+    id integer DEFAULT nextval('messwert_id_seq'::regclass) NOT NULL,
+    messungs_id integer NOT NULL,
+    messgroesse_id integer NOT NULL,
+    messwert_nwg character varying(1),
+    messwert real NOT NULL,
+    messfehler real,
+    nwg_zu_messwert real,
+    meh_id smallint NOT NULL,
+    grenzwertueberschreitung boolean DEFAULT false,
+    letzte_aenderung timestamp without time zone DEFAULT now()
+);
+
+
+--
+-- Name: ort_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
+--
+
+CREATE SEQUENCE ort_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: ort; Type: TABLE; Schema: bund; Owner: -; Tablespace: 
+--
+
+CREATE TABLE ort (
+    id integer DEFAULT nextval('ort_id_seq'::regclass) NOT NULL,
+    probe_id integer NOT NULL,
+    ort_id bigint NOT NULL,
+    orts_typ character varying(1),
+    ortszusatztext character varying(100),
+    letzte_aenderung timestamp without time zone DEFAULT now()
+);
+
+
+--
+-- Name: COLUMN ort.orts_typ; Type: COMMENT; Schema: bund; Owner: -
+--
+
+COMMENT ON COLUMN ort.orts_typ IS 'E = Entnahmeport, U = Ursprungsort, Z = Ortszusatz';
+
+
+--
+-- Name: probe_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
+--
+
+CREATE SEQUENCE probe_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: probe; Type: TABLE; Schema: bund; Owner: -; Tablespace: 
+--
+
+CREATE TABLE probe (
+    id integer DEFAULT nextval('probe_id_seq'::regclass) NOT NULL,
+    test boolean DEFAULT false NOT NULL,
+    netzbetreiber_id character varying(2),
+    mst_id character varying(5),
+    hauptproben_nr character varying(20),
+    datenbasis_id smallint,
+    ba_id character varying(1),
+    probenart_id smallint NOT NULL,
+    media_desk character varying(100),
+    media character varying(100),
+    umw_id character varying(3),
+    probeentnahme_beginn timestamp with time zone,
+    probeentnahme_ende timestamp with time zone,
+    mittelungsdauer bigint,
+    letzte_aenderung timestamp without time zone DEFAULT now()
+);
+
+
+--
+-- Name: COLUMN probe.id; Type: COMMENT; Schema: bund; Owner: -
+--
+
+COMMENT ON COLUMN probe.id IS 'interner ProbenschlĂĽssel';
+
+
+--
+-- Name: COLUMN probe.test; Type: COMMENT; Schema: bund; Owner: -
+--
+
+COMMENT ON COLUMN probe.test IS 'Ist Testdatensatz?';
+
+
+--
+-- Name: COLUMN probe.mst_id; Type: COMMENT; Schema: bund; Owner: -
+--
+
+COMMENT ON COLUMN probe.mst_id IS 'ID fĂĽr Messstelle/Messlabor';
+
+
+--
+-- Name: COLUMN probe.hauptproben_nr; Type: COMMENT; Schema: bund; Owner: -
+--
+
+COMMENT ON COLUMN probe.hauptproben_nr IS 'externer ProbensclĂĽssel';
+
+
+--
+-- Name: COLUMN probe.ba_id; Type: COMMENT; Schema: bund; Owner: -
+--
+
+COMMENT ON COLUMN probe.ba_id IS 'ID der Betriebsart (normal/Routine oder Störfall/intensiv)';
+
+
+--
+-- Name: COLUMN probe.probenart_id; Type: COMMENT; Schema: bund; Owner: -
+--
+
+COMMENT ON COLUMN probe.probenart_id IS 'ID der Probenart(Einzel-, Sammel-, Misch- ...Probe)';
+
+
+--
+-- Name: COLUMN probe.media_desk; Type: COMMENT; Schema: bund; Owner: -
+--
+
+COMMENT ON COLUMN probe.media_desk IS 'Mediencodierung (Deskriptoren oder ADV-Codierung)';
+
+
+--
+-- Name: COLUMN probe.media; Type: COMMENT; Schema: bund; Owner: -
+--
+
+COMMENT ON COLUMN probe.media IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)';
+
+
+--
+-- Name: COLUMN probe.umw_id; Type: COMMENT; Schema: bund; Owner: -
+--
+
+COMMENT ON COLUMN probe.umw_id IS 'ID fĂĽr Umweltbereich';
+
+
+--
+-- Name: probe_probe_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
+--
+
+CREATE SEQUENCE probe_probe_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: status_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
+--
+
+CREATE SEQUENCE status_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: status; Type: TABLE; Schema: bund; Owner: -; Tablespace: 
+--
+
+CREATE TABLE status (
+    id integer DEFAULT nextval('status_id_seq'::regclass) NOT NULL,
+    messungs_id integer NOT NULL,
+    erzeuger character varying(5) NOT NULL,
+    status smallint,
+    s_datum timestamp with time zone NOT NULL,
+    s_kommentar character varying(1024)
+);
+
+
+--
+-- Name: zusatz_wert_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
+--
+
+CREATE SEQUENCE zusatz_wert_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: zusatz_wert; Type: TABLE; Schema: bund; Owner: -; Tablespace: 
+--
+
+CREATE TABLE zusatz_wert (
+    id integer DEFAULT nextval('zusatz_wert_id_seq'::regclass) NOT NULL,
+    probe_id integer NOT NULL,
+    pzs_id character varying(3) NOT NULL,
+    messwert_pzs real,
+    messfehler real,
+    letzte_aenderung timestamp without time zone DEFAULT now(),
+    nwg_zu_messwert real
+);
+
+
+SET search_path = land, pg_catalog;
+
+--
+-- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace: 
+--
+
+CREATE TABLE kommentar_m (
+)
+INHERITS (bund.kommentar_m);
+
+
+--
+-- Name: kommentar_p; Type: TABLE; Schema: land; Owner: -; Tablespace: 
+--
+
+CREATE TABLE kommentar_p (
+)
+INHERITS (bund.kommentar_p);
+
+
+--
+-- Name: messprogramm; Type: TABLE; Schema: land; Owner: -; Tablespace: 
+--
+
+CREATE TABLE messprogramm (
+    id integer NOT NULL,
+    name character varying(256),
+    test boolean DEFAULT false NOT NULL,
+    netzbetreiber_id character varying(2) NOT NULL,
+    mst_id character varying(5) NOT NULL,
+    datenbasis_id integer NOT NULL,
+    ba_id character varying(1),
+    gem_id character varying(8),
+    ort_id integer,
+    media_desk character varying(100),
+    umw_id character varying(3),
+    probenart_id integer NOT NULL,
+    probenintervall character varying(2),
+    teilintervall_von integer,
+    teilintervall_bis integer,
+    intervall_offset integer,
+    gueltig_von integer,
+    gueltig_bis integer,
+    probe_nehmer_id integer,
+    probe_kommentar character varying(80),
+    letzte_aenderung timestamp without time zone DEFAULT now() NOT NULL
+);
+
+
+--
+-- Name: COLUMN messprogramm.media_desk; Type: COMMENT; Schema: land; Owner: -
+--
+
+COMMENT ON COLUMN messprogramm.media_desk IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)';
+
+
+--
+-- Name: messprogramm_id_seq; Type: SEQUENCE; Schema: land; Owner: -
+--
+
+CREATE SEQUENCE messprogramm_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: messprogramm_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: -
+--
+
+ALTER SEQUENCE messprogramm_id_seq OWNED BY messprogramm.id;
+
+
+--
+-- Name: messprogramm_mmt; Type: TABLE; Schema: land; Owner: -; Tablespace: 
+--
+
+CREATE TABLE messprogramm_mmt (
+    id integer NOT NULL,
+    messprogramm_id integer NOT NULL,
+    mmt_id character varying(2) NOT NULL,
+    messgroessen integer[],
+    letzte_aenderung timestamp without time zone DEFAULT now()
+);
+
+
+--
+-- Name: messprogramm_mmt_id_seq; Type: SEQUENCE; Schema: land; Owner: -
+--
+
+CREATE SEQUENCE messprogramm_mmt_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: messprogramm_mmt_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: -
+--
+
+ALTER SEQUENCE messprogramm_mmt_id_seq OWNED BY messprogramm_mmt.id;
+
+
+--
+-- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace: 
+--
+
+CREATE TABLE messung (
+    geplant boolean DEFAULT false NOT NULL,
+    tree_modified timestamp without time zone DEFAULT now()
+)
+INHERITS (bund.messung);
+
+
+--
+-- Name: messung_translation; Type: TABLE; Schema: land; Owner: -; Tablespace: 
+--
+
+CREATE TABLE messung_translation (
+    id integer NOT NULL,
+    messungs_id integer NOT NULL,
+    messungs_id_alt integer DEFAULT nextval('bund.messung_messung_id_alt_seq'::regclass) NOT NULL
+);
+
+
+--
+-- Name: messung_translation_id_seq; Type: SEQUENCE; Schema: land; Owner: -
+--
+
+CREATE SEQUENCE messung_translation_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: messung_translation_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: -
+--
+
+ALTER SEQUENCE messung_translation_id_seq OWNED BY messung_translation.id;
+
+
+--
+-- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace: 
+--
+
+CREATE TABLE messwert (
+    tree_modified timestamp without time zone DEFAULT now()
+)
+INHERITS (bund.messwert);
+
+
+--
+-- Name: ort; Type: TABLE; Schema: land; Owner: -; Tablespace: 
+--
+
+CREATE TABLE ort (
+    tree_modified timestamp without time zone DEFAULT now()
+)
+INHERITS (bund.ort);
+
+
+--
+-- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace: 
+--
+
+CREATE TABLE probe (
+    erzeuger_id integer,
+    probe_nehmer_id integer,
+    mp_kat character(1),
+    mpl_id character varying(3),
+    mpr_id integer,
+    solldatum_beginn timestamp without time zone,
+    solldatum_ende timestamp without time zone,
+    tree_modified timestamp without time zone DEFAULT now()
+)
+INHERITS (bund.probe);
+
+
+--
+-- Name: probe_translation; Type: TABLE; Schema: land; Owner: -; Tablespace: 
+--
+
+CREATE TABLE probe_translation (
+    id integer NOT NULL,
+    probe_id integer NOT NULL,
+    probe_id_alt character varying(20) DEFAULT (('sss'::text || lpad(((nextval('bund.probe_probe_id_seq'::regclass))::character varying)::text, 12, '0'::text)) || 'Y'::text) NOT NULL
+);
+
+
+--
+-- Name: probe_translation_id_seq; Type: SEQUENCE; Schema: land; Owner: -
+--
+
+CREATE SEQUENCE probe_translation_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: probe_translation_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: -
+--
+
+ALTER SEQUENCE probe_translation_id_seq OWNED BY probe_translation.id;
+
+
+--
+-- Name: status; Type: TABLE; Schema: land; Owner: -; Tablespace: 
+--
+
+CREATE TABLE status (
+    tree_modified timestamp without time zone DEFAULT now()
+)
+INHERITS (bund.status);
+
+
+--
+-- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace: 
+--
+
+CREATE TABLE zusatz_wert (
+    tree_modified timestamp without time zone DEFAULT now()
+)
+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)
+);
+
+
+--
+-- 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: 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: 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: 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: 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: 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,
+    o_typ character varying(1),
+    netzbetreiber_id character varying(2),
+    bezeichnung character varying(10),
+    beschreibung 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)
+);
+
+
+--
+-- Name: COLUMN ort.o_typ; Type: COMMENT; Schema: stammdaten; Owner: -
+--
+
+COMMENT ON COLUMN ort.o_typ IS 'D = dynamischer Messpunkt (nicht vordefiniert)
+V = vordefinierter Messpunkt
+R = REI-Messpunkt
+S = Station
+Z = Ortzszusatz';
+
+
+--
+-- 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: 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: 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: 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
+);
+
+
+SET search_path = bund, pg_catalog;
+
+--
+-- Name: id; Type: DEFAULT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY kommentar_m ALTER COLUMN id SET DEFAULT nextval('kommentar_id_seq'::regclass);
+
+
+--
+-- Name: datum; Type: DEFAULT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY kommentar_m ALTER COLUMN datum SET DEFAULT now();
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY kommentar_p ALTER COLUMN id SET DEFAULT nextval('kommentar_id_seq'::regclass);
+
+
+--
+-- Name: datum; Type: DEFAULT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY kommentar_p ALTER COLUMN datum SET DEFAULT now();
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY list ALTER COLUMN id SET DEFAULT nextval('list_id_seq'::regclass);
+
+
+SET search_path = land, pg_catalog;
+
+--
+-- Name: id; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY kommentar_m ALTER COLUMN id SET DEFAULT nextval('bund.kommentar_id_seq'::regclass);
+
+
+--
+-- Name: datum; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY kommentar_m ALTER COLUMN datum SET DEFAULT now();
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY kommentar_p ALTER COLUMN id SET DEFAULT nextval('bund.kommentar_id_seq'::regclass);
+
+
+--
+-- Name: datum; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY kommentar_p ALTER COLUMN datum SET DEFAULT now();
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messprogramm ALTER COLUMN id SET DEFAULT nextval('messprogramm_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messprogramm_mmt ALTER COLUMN id SET DEFAULT nextval('messprogramm_mmt_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messung ALTER COLUMN id SET DEFAULT nextval('bund.messung_id_seq'::regclass);
+
+
+--
+-- Name: fertig; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messung ALTER COLUMN fertig SET DEFAULT false;
+
+
+--
+-- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messung ALTER COLUMN letzte_aenderung SET DEFAULT now();
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messung_translation ALTER COLUMN id SET DEFAULT nextval('messung_translation_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messwert ALTER COLUMN id SET DEFAULT nextval('bund.messwert_id_seq'::regclass);
+
+
+--
+-- Name: grenzwertueberschreitung; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messwert ALTER COLUMN grenzwertueberschreitung SET DEFAULT false;
+
+
+--
+-- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messwert ALTER COLUMN letzte_aenderung SET DEFAULT now();
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY ort ALTER COLUMN id SET DEFAULT nextval('bund.ort_id_seq'::regclass);
+
+
+--
+-- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY ort ALTER COLUMN letzte_aenderung SET DEFAULT now();
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY probe ALTER COLUMN id SET DEFAULT nextval('bund.probe_id_seq'::regclass);
+
+
+--
+-- Name: test; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY probe ALTER COLUMN test SET DEFAULT false;
+
+
+--
+-- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY probe ALTER COLUMN letzte_aenderung SET DEFAULT now();
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY probe_translation ALTER COLUMN id SET DEFAULT nextval('probe_translation_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY status ALTER COLUMN id SET DEFAULT nextval('bund.status_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY zusatz_wert ALTER COLUMN id SET DEFAULT nextval('bund.zusatz_wert_id_seq'::regclass);
+
+
+--
+-- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
+--
+
+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 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 koordinaten_art ALTER COLUMN id SET DEFAULT nextval('koordinaten_art_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 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 staat ALTER COLUMN id SET DEFAULT nextval('staat_id_seq'::regclass);
+
+
+SET search_path = bund, pg_catalog;
+
+--
+-- Name: kommentar_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY kommentar
+    ADD CONSTRAINT kommentar_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: list_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY list
+    ADD CONSTRAINT list_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: messung_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY messung
+    ADD CONSTRAINT messung_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: messwert_messungs_id_messgroesse_id_key; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY messwert
+    ADD CONSTRAINT messwert_messungs_id_messgroesse_id_key UNIQUE (messungs_id, messgroesse_id);
+
+
+--
+-- Name: messwert_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY messwert
+    ADD CONSTRAINT messwert_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: ort_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY ort
+    ADD CONSTRAINT ort_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: probe_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY probe
+    ADD CONSTRAINT probe_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: status_messungs_id_key; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY status
+    ADD CONSTRAINT status_messungs_id_key UNIQUE (messungs_id);
+
+
+--
+-- Name: status_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY status
+    ADD CONSTRAINT status_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: zusatz_wert_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY zusatz_wert
+    ADD CONSTRAINT zusatz_wert_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: zusatz_wert_probe_id_pzs_id_key; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY zusatz_wert
+    ADD CONSTRAINT zusatz_wert_probe_id_pzs_id_key UNIQUE (probe_id, pzs_id);
+
+
+SET search_path = land, pg_catalog;
+
+--
+-- Name: kommentar_m_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY kommentar_m
+    ADD CONSTRAINT kommentar_m_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: kommentar_p_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY kommentar_p
+    ADD CONSTRAINT kommentar_p_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: messprogramm_mmt_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY messprogramm_mmt
+    ADD CONSTRAINT messprogramm_mmt_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: messprogramm_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY messprogramm
+    ADD CONSTRAINT messprogramm_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: messung_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY messung
+    ADD CONSTRAINT messung_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: messung_translation_messungs_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY messung_translation
+    ADD CONSTRAINT messung_translation_messungs_id_key UNIQUE (messungs_id);
+
+
+--
+-- Name: messung_translation_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY messung_translation
+    ADD CONSTRAINT messung_translation_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: messwert_messungs_id_messgroesse_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY messwert
+    ADD CONSTRAINT messwert_messungs_id_messgroesse_id_key UNIQUE (messungs_id, messgroesse_id);
+
+
+--
+-- Name: messwert_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY messwert
+    ADD CONSTRAINT messwert_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: ort_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY ort
+    ADD CONSTRAINT ort_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: probe_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY probe
+    ADD CONSTRAINT probe_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: probe_translation_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY probe_translation
+    ADD CONSTRAINT probe_translation_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: probe_translation_probe_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY probe_translation
+    ADD CONSTRAINT probe_translation_probe_id_key UNIQUE (probe_id);
+
+
+--
+-- Name: status_messungs_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY status
+    ADD CONSTRAINT status_messungs_id_key UNIQUE (messungs_id);
+
+
+--
+-- Name: status_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY status
+    ADD CONSTRAINT status_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: zusatz_wert_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY zusatz_wert
+    ADD CONSTRAINT zusatz_wert_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: zusatz_wert_probe_id_pzs_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY zusatz_wert
+    ADD CONSTRAINT zusatz_wert_probe_id_pzs_id_key UNIQUE (probe_id, pzs_id);
+
+
+SET search_path = stammdaten, pg_catalog;
+
+--
+-- Name: auth_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY auth
+    ADD CONSTRAINT auth_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: 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: koordinaten_art_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY koordinaten_art
+    ADD CONSTRAINT koordinaten_art_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: 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: 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: staat_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY staat
+    ADD CONSTRAINT staat_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;
+
+--
+-- Name: p_list_zuord1; Type: INDEX; Schema: bund; Owner: -; Tablespace: 
+--
+
+CREATE INDEX p_list_zuord1 ON list_zuordnung USING btree (list_id);
+
+
+--
+-- Name: p_list_zuord2; Type: INDEX; Schema: bund; Owner: -; Tablespace: 
+--
+
+CREATE INDEX p_list_zuord2 ON list_zuordnung USING hash (list_id);
+
+
+SET search_path = land, pg_catalog;
+
+--
+-- Name: messung_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: 
+--
+
+CREATE INDEX messung_probe_id_idx ON messung USING btree (probe_id);
+
+
+--
+-- Name: messung_translation_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: 
+--
+
+CREATE INDEX messung_translation_messungs_id_idx ON messung_translation USING btree (messungs_id);
+
+
+--
+-- Name: messwert_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: 
+--
+
+CREATE INDEX messwert_messungs_id_idx ON messwert USING btree (messungs_id);
+
+
+--
+-- Name: probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: 
+--
+
+CREATE INDEX probe_id_idx ON probe USING btree (id);
+
+
+--
+-- Name: probe_translation_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: 
+--
+
+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 = land, pg_catalog;
+
+--
+-- Name: tree_timestamp_messung; Type: TRIGGER; Schema: land; Owner: -
+--
+
+CREATE TRIGGER tree_timestamp_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_time_messung();
+
+
+--
+-- Name: tree_timestamp_messwert; Type: TRIGGER; Schema: land; Owner: -
+--
+
+CREATE TRIGGER tree_timestamp_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_time_messwert();
+
+
+--
+-- Name: tree_timestamp_ort; Type: TRIGGER; Schema: land; Owner: -
+--
+
+CREATE TRIGGER tree_timestamp_ort BEFORE UPDATE ON ort FOR EACH ROW EXECUTE PROCEDURE update_time_ort();
+
+
+--
+-- Name: tree_timestamp_probe; Type: TRIGGER; Schema: land; Owner: -
+--
+
+CREATE TRIGGER tree_timestamp_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_time_probe();
+
+
+--
+-- Name: tree_timestamp_status; Type: TRIGGER; Schema: land; Owner: -
+--
+
+CREATE TRIGGER tree_timestamp_status BEFORE UPDATE ON status FOR EACH ROW EXECUTE PROCEDURE update_time_status();
+
+
+--
+-- Name: tree_timestamp_zusatzwert; Type: TRIGGER; Schema: land; Owner: -
+--
+
+CREATE TRIGGER tree_timestamp_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_time_zusatzwert();
+
+
+--
+-- Name: verify_kommentar_m_id; Type: TRIGGER; Schema: land; Owner: -
+--
+
+CREATE TRIGGER verify_kommentar_m_id BEFORE INSERT ON kommentar_m FOR EACH ROW EXECUTE PROCEDURE bund.is_kommentar_unique();
+
+
+--
+-- Name: verify_kommentar_p_id; Type: TRIGGER; Schema: land; Owner: -
+--
+
+CREATE TRIGGER verify_kommentar_p_id BEFORE INSERT ON kommentar_p FOR EACH ROW EXECUTE PROCEDURE bund.is_kommentar_unique();
+
+
+--
+-- Name: verify_messung_id; Type: TRIGGER; Schema: land; Owner: -
+--
+
+CREATE TRIGGER verify_messung_id BEFORE INSERT ON messung FOR EACH ROW EXECUTE PROCEDURE bund.is_messung_unique();
+
+
+--
+-- Name: verify_messwert_id; Type: TRIGGER; Schema: land; Owner: -
+--
+
+CREATE TRIGGER verify_messwert_id BEFORE INSERT ON messwert FOR EACH ROW EXECUTE PROCEDURE bund.is_messwert_unique();
+
+
+--
+-- Name: verify_ort_id; Type: TRIGGER; Schema: land; Owner: -
+--
+
+CREATE TRIGGER verify_ort_id BEFORE INSERT ON ort FOR EACH ROW EXECUTE PROCEDURE bund.is_ort_unique();
+
+
+--
+-- Name: verify_probe_id; Type: TRIGGER; Schema: land; Owner: -
+--
+
+CREATE TRIGGER verify_probe_id BEFORE INSERT ON probe FOR EACH ROW EXECUTE PROCEDURE bund.is_probe_unique();
+
+
+--
+-- Name: verify_status_id; Type: TRIGGER; Schema: land; Owner: -
+--
+
+CREATE TRIGGER verify_status_id BEFORE INSERT ON status FOR EACH ROW EXECUTE PROCEDURE bund.is_status_unique();
+
+
+--
+-- Name: verify_zusatz_wert_id; Type: TRIGGER; Schema: land; Owner: -
+--
+
+CREATE TRIGGER verify_zusatz_wert_id BEFORE INSERT ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE bund.is_zusatz_wert_unique();
+
+
+SET search_path = bund, pg_catalog;
+
+--
+-- Name: kommentar_m_messungs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY kommentar_m
+    ADD CONSTRAINT kommentar_m_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id);
+
+
+--
+-- Name: kommentar_p_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY kommentar_p
+    ADD CONSTRAINT kommentar_p_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id);
+
+
+--
+-- Name: messung_mmt_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY messung
+    ADD CONSTRAINT messung_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id);
+
+
+--
+-- Name: messung_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY messung
+    ADD CONSTRAINT messung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id);
+
+
+--
+-- Name: messwert_meh_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY messwert
+    ADD CONSTRAINT messwert_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES stammdaten.mess_einheit(id);
+
+
+--
+-- Name: messwert_messgroesse_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY messwert
+    ADD CONSTRAINT messwert_messgroesse_id_fkey FOREIGN KEY (messgroesse_id) REFERENCES stammdaten.messgroesse(id);
+
+
+--
+-- Name: messwert_messungs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY messwert
+    ADD CONSTRAINT messwert_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id);
+
+
+--
+-- Name: ort_ort_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY ort
+    ADD CONSTRAINT ort_ort_id_fkey FOREIGN KEY (ort_id) REFERENCES stammdaten.ort(id);
+
+
+--
+-- Name: ort_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY ort
+    ADD CONSTRAINT ort_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id);
+
+
+--
+-- Name: probe_datenbasis_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY probe
+    ADD CONSTRAINT probe_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES stammdaten.datenbasis(id);
+
+
+--
+-- Name: probe_mst_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY probe
+    ADD CONSTRAINT probe_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES stammdaten.mess_stelle(id);
+
+
+--
+-- Name: probe_mst_id_fkey1; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY probe
+    ADD CONSTRAINT probe_mst_id_fkey1 FOREIGN KEY (mst_id) REFERENCES stammdaten.mess_stelle(id);
+
+
+--
+-- Name: probe_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY probe
+    ADD CONSTRAINT probe_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES stammdaten.netz_betreiber(id);
+
+
+--
+-- Name: probe_probenart_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY probe
+    ADD CONSTRAINT probe_probenart_id_fkey FOREIGN KEY (probenart_id) REFERENCES stammdaten.probenart(id);
+
+
+--
+-- Name: probe_umw_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY probe
+    ADD CONSTRAINT probe_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES stammdaten.umwelt(id);
+
+
+--
+-- Name: status_erzeuger_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY status
+    ADD CONSTRAINT status_erzeuger_fkey FOREIGN KEY (erzeuger) REFERENCES stammdaten.mess_stelle(id);
+
+
+--
+-- Name: status_messungs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY status
+    ADD CONSTRAINT status_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id);
+
+
+--
+-- Name: zusatz_wert_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY zusatz_wert
+    ADD CONSTRAINT zusatz_wert_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id);
+
+
+--
+-- Name: zusatz_wert_pzs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY zusatz_wert
+    ADD CONSTRAINT zusatz_wert_pzs_id_fkey FOREIGN KEY (pzs_id) REFERENCES stammdaten.proben_zusatz(id);
+
+
+SET search_path = land, pg_catalog;
+
+--
+-- Name: kommentar_m_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY kommentar_m
+    ADD CONSTRAINT kommentar_m_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
+
+
+--
+-- Name: kommentar_p_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY kommentar_p
+    ADD CONSTRAINT kommentar_p_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
+
+
+--
+-- Name: messprogramm_datenbasis_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messprogramm
+    ADD CONSTRAINT messprogramm_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES stammdaten.datenbasis(id);
+
+
+--
+-- Name: messprogramm_mmt_messprogramm_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messprogramm_mmt
+    ADD CONSTRAINT messprogramm_mmt_messprogramm_id_fkey FOREIGN KEY (messprogramm_id) REFERENCES messprogramm(id) ON DELETE CASCADE;
+
+
+--
+-- Name: messprogramm_mmt_mmt_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messprogramm_mmt
+    ADD CONSTRAINT messprogramm_mmt_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id);
+
+
+--
+-- Name: messprogramm_mst_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messprogramm
+    ADD CONSTRAINT messprogramm_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES stammdaten.mess_stelle(id);
+
+
+--
+-- Name: messprogramm_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messprogramm
+    ADD CONSTRAINT messprogramm_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES stammdaten.netz_betreiber(id);
+
+
+--
+-- Name: messprogramm_ort_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messprogramm
+    ADD CONSTRAINT messprogramm_ort_id_fkey FOREIGN KEY (ort_id) REFERENCES stammdaten.ort(id);
+
+
+--
+-- Name: messprogramm_probenart_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messprogramm
+    ADD CONSTRAINT messprogramm_probenart_id_fkey FOREIGN KEY (probenart_id) REFERENCES stammdaten.probenart(id);
+
+
+--
+-- Name: messprogramm_umw_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messprogramm
+    ADD CONSTRAINT messprogramm_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES stammdaten.umwelt(id);
+
+
+--
+-- Name: messung_mmt_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messung
+    ADD CONSTRAINT messung_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id) ON DELETE CASCADE;
+
+
+--
+-- Name: messung_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messung
+    ADD CONSTRAINT messung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
+
+
+--
+-- Name: messung_translation_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messung_translation
+    ADD CONSTRAINT messung_translation_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
+
+
+--
+-- Name: messwert_meh_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messwert
+    ADD CONSTRAINT messwert_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES stammdaten.mess_einheit(id);
+
+
+--
+-- Name: messwert_messgroesse_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messwert
+    ADD CONSTRAINT messwert_messgroesse_id_fkey FOREIGN KEY (messgroesse_id) REFERENCES stammdaten.messgroesse(id);
+
+
+--
+-- Name: messwert_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messwert
+    ADD CONSTRAINT messwert_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
+
+
+--
+-- Name: ort_ort_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY ort
+    ADD CONSTRAINT ort_ort_id_fkey FOREIGN KEY (ort_id) REFERENCES stammdaten.ort(id);
+
+
+--
+-- Name: ort_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY ort
+    ADD CONSTRAINT ort_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
+
+
+--
+-- Name: probe_datenbasis_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY probe
+    ADD CONSTRAINT probe_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES stammdaten.datenbasis(id);
+
+
+--
+-- Name: probe_mst_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY probe
+    ADD CONSTRAINT probe_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES stammdaten.mess_stelle(id);
+
+
+--
+-- Name: probe_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY probe
+    ADD CONSTRAINT probe_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES stammdaten.netz_betreiber(id);
+
+
+--
+-- Name: probe_probenart_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY probe
+    ADD CONSTRAINT probe_probenart_id_fkey FOREIGN KEY (probenart_id) REFERENCES stammdaten.probenart(id);
+
+
+--
+-- Name: probe_translation_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY probe_translation
+    ADD CONSTRAINT probe_translation_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
+
+
+--
+-- Name: probe_umw_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY probe
+    ADD CONSTRAINT probe_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES stammdaten.umwelt(id);
+
+
+--
+-- Name: status_erzeuger_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY status
+    ADD CONSTRAINT status_erzeuger_fkey FOREIGN KEY (erzeuger) REFERENCES stammdaten.mess_stelle(id);
+
+
+--
+-- Name: status_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY status
+    ADD CONSTRAINT status_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
+
+
+--
+-- Name: zusatz_wert_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY zusatz_wert
+    ADD CONSTRAINT zusatz_wert_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
+
+
+--
+-- Name: zusatz_wert_pzs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY zusatz_wert
+    ADD CONSTRAINT zusatz_wert_pzs_id_fkey FOREIGN KEY (pzs_id) REFERENCES stammdaten.proben_zusatz(id);
+
+
+SET search_path = stammdaten, pg_catalog;
+
+--
+-- 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: 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: 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_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: 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: 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
+--
+
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/db_schema/setup-db.sh	Tue Oct 27 09:59:21 2015 +0100
@@ -0,0 +1,14 @@
+#!/bin/sh
+DIR=`dirname $0`
+
+ROLE_NAME=lada
+ROLE_PW=lada
+DB_NAME=lada
+
+psql --command "CREATE USER $ROLE_NAME PASSWORD '$ROLE_PW';"
+createdb -E UTF-8 $DB_NAME
+psql -d $DB_NAME -f $DIR/lada_schema.sql
+psql -d $DB_NAME --command \
+     "GRANT USAGE ON SCHEMA stammdaten, bund, land TO $ROLE_NAME;
+      GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES
+            ON ALL TABLES IN SCHEMA stammdaten, bund, land TO $ROLE_NAME;"
--- a/pom.xml	Tue Oct 27 09:58:25 2015 +0100
+++ b/pom.xml	Tue Oct 27 09:59:21 2015 +0100
@@ -246,9 +246,11 @@
                         <artifactId>wildfly-maven-plugin</artifactId>
                         <configuration>
                             <hostname>localhost</hostname>
-                            <port>19990</port>
+                            <port>9990</port>
+<!--
                             <username>admin</username>
                             <password>secret</password>
+-->
                         </configuration>
                         <!-- <executions>
                             <execution>
--- a/src/main/java/de/intevation/lada/rest/ProbeService.java	Tue Oct 27 09:58:25 2015 +0100
+++ b/src/main/java/de/intevation/lada/rest/ProbeService.java	Tue Oct 27 09:59:21 2015 +0100
@@ -340,9 +340,7 @@
         if (probe.getUmwId() == null || probe.getUmwId().equals("")) {
             probe = factory.findUmweltId(probe);
         }
-        if (probe.getMediaDesk() != null || probe.getMediaDesk().length() > 0) {
-            probe = factory.findMediaDesk(probe);
-        }
+        probe = factory.findMediaDesk(probe);
         /* Persist the new probe object*/
         Response newProbe = defaultRepo.create(probe, "land");
         LProbe ret = (LProbe)newProbe.getData();
@@ -452,6 +450,7 @@
         if (lock.isLocked(probe)) {
             return new Response(false, 697, null);
         }
+        probe = factory.findMediaDesk(probe);
         Violation violation = validator.validate(probe);
         if (violation.hasErrors()) {
             Response response = new Response(false, 604, null);
--- a/src/main/resources/probequery.json	Tue Oct 27 09:58:25 2015 +0100
+++ b/src/main/resources/probequery.json	Tue Oct 27 09:59:21 2015 +0100
@@ -14,6 +14,21 @@
     ]
 },
 {   "id": "1",
+    "name": "HP-Nr-Filer",
+    "description": "Abfrage der Proben mit Wildcardfilter fuer die Hauptproben-Nr.",
+    "sql": "select p.id as id, pt.probe_id_alt as probeId, p.netzbetreiber_id as netzbetreiberId, p.mst_id as mstId, p.umw_id as umwId, p.hauptproben_nr as hauptprobenNr from land.probe p join land.probe_translation pt on p.id = pt.probe_id where (p.hauptproben_nr like :hp_nr_id or '' = :hp_nr_id)",
+    "result": [
+        {"dataIndex": "probeId", "header": "ProbeId", "width": 120},
+        {"dataIndex": "netzbetreiberId", "header": "Land", "width": 50},
+        {"dataIndex": "mstId", "header": "MST", "width": 60},
+        {"dataIndex": "umwId", "header": "Umw-ID", "width": 60},
+        {"dataIndex": "hauptprobenNr", "header": "Proben-Nr", "width": 120}
+    ],
+    "filters": [
+        {"dataIndex": "hp_nr_id", "type": "text", "label": "Probe_id-Filter"}
+    ]
+},
+{   "id": "2",
     "name": "Probe-ID-Filer",
     "description": "Abfrage der Proben mit Wildcardfilter fuer die Probe_id",
     "sql": "select p.id as id, pt.probe_id_alt as probeId, p.netzbetreiber_id as netzbetreiberId, p.mst_id as mstId, p.umw_id as umwId, p.hauptproben_nr as hauptprobenNr from land.probe p join land.probe_translation pt on p.id = pt.probe_id where (pt.probe_id_alt like :probe_id or '' = :probe_id)",
@@ -28,7 +43,7 @@
         {"dataIndex": "probe_id", "type": "text", "label": "Probe_id-Filter"}
     ]
 },
-{   "id": "2",
+{   "id": "3",
     "name": "MST und UMW",
     "description": "Abfrage der Proben gefiltert nach Messtellen ID und ID des Umweltbereichs",
     "sql": "select p.id as id, pt.probe_id_alt as probeId, p.netzbetreiber_id as netzbetreiberId, p.mst_id as mstId, p.umw_id as umwId, p.hauptproben_nr as hauptprobenNr from land.probe p join land.probe_translation pt on p.id = pt.probe_id where (p.mst_id = :mst_id OR '' = :mst_id) and (p.umw_id = :umw_id OR '' = :umw_id)",
@@ -44,7 +59,7 @@
         {"dataIndex": "umw_id", "type": "listumw", "label": "Umweltbereich"}
     ]
 },
-{   "id": "3",
+{   "id": "4",
     "name": "Proben pro Land",
     "description": "Proben gefiltert nach Ländern",
     "sql": "select p.id as id, pt.probe_id_alt as probeId, p.netzbetreiber_id as netzbetreiberId, p.mst_id as mstId, p.umw_id as umwId, p.hauptproben_nr as hauptprobenNr from land.probe p join land.probe_translation pt on p.id = pt.probe_id where (netzbetreiber_id = :netz OR '' = :netz)",
@@ -59,7 +74,7 @@
         {"dataIndex": "netz", "type": "listnetz", "label": "Land"}
     ]
 },
-{   "id": "4",
+{   "id": "5",
     "name": "alle Proben mit Ort",
     "description": "alle Proben mit Entnahmeort",
     "sql": "select p.id as id,  pt.probe_id_alt as probeId, p.netzbetreiber_id as netzbetreiberId, p.mst_id as mstId, p.umw_id as umwId, p.hauptproben_nr as hauptprobenNr, so.gem_id as gemId, v.bezeichnung as bezeichnung from land.probe p join land.probe_translation pt on p.id = pt.probe_id left outer join land.ort o on (p.id = o.probe_id) left outer join stammdaten.ort so on (o.ort_id = so.id) left outer join stammdaten.verwaltungseinheit v on (so.gem_id = v.id) where o.orts_typ = 'E' or o.orts_typ is null",
@@ -75,7 +90,7 @@
     "filters": [
     ]
 },
-{   "id": "5",
+{   "id": "6",
     "name": "Proben pro Land und UMW (Multiselect)",
     "description": "Abfrage aller Proben gefiltert pro Land und Umweltbereich (mit Mehrfachauswahl)",
     "sql": "select p.id as id, pt.probe_id_alt as probeId, p.netzbetreiber_id as netzbetreiberId, p.mst_id as mstId, to_char(p.probeentnahme_beginn,'YYYY-mm-dd HH24:MI') entnahmeVon, to_char(p.probeentnahme_ende,'YYYY-mm-dd HH24:MI') entnahmeBis, p.umw_id as umwId, p.hauptproben_nr as hauptprobenNr, o.orts_typ as ortsTyp, so.gem_id as gemId, v.bezeichnung as bezeichnung from land.probe p join land.probe_translation pt on p.id = pt.probe_id left outer join land.ort o on (p.id = o.probe_id) left outer join stammdaten.ort so on (o.ort_id = so.id) left outer join stammdaten.verwaltungseinheit v on (so.gem_id = v.id) where (o.orts_typ = 'E' or o.orts_typ is null) and (p.netzbetreiber_id = :netz OR '' =:netz) and (p.umw_id similar to (:umw_id) OR '' = :umw_id)",
@@ -95,5 +110,27 @@
         {"dataIndex": "netz", "type": "listnetz", "label": "Land"},
         {"dataIndex": "umw_id", "type": "listumw", "label": "Umweltbereich", "multiselect":true}
     ]
+},
+{   "id": "7",
+    "name": "Proben und Messungen pro Land und UMW (Multiselect)",
+    "description": "Abfrage aller Proben/Messungen gefiltert pro Land und Umweltbereich (mit Mehrfachauswahl)",
+    "sql": "select p.id as id, pt.probe_id_alt as probeId, p.netzbetreiber_id as netzbetreiberId, p.mst_id as mstId, to_char(p.probeentnahme_beginn,'YYYY-mm-dd HH24:MI') entnahmeVon, to_char(p.probeentnahme_ende,'YYYY-mm-dd HH24:MI') entnahmeBis, m.mmt_id as mmtId, p.umw_id as umwId, p.hauptproben_nr as hauptprobenNr, o.orts_typ as ortsTyp, so.gem_id as gemId, v.bezeichnung as bezeichnung from land.probe p left outer join land.probe_translation pt on p.id = pt.probe_id left outer join land.messung m on p.id = m.probe_id left outer join land.ort o on (p.id = o.probe_id) left outer join stammdaten.ort so on (o.ort_id = so.id) left outer join stammdaten.verwaltungseinheit v on (so.gem_id = v.id) where (o.orts_typ = 'E' or o.orts_typ is null) and (p.netzbetreiber_id = :netz OR '' =:netz) and (p.umw_id similar to (:umw_id) OR '' = :umw_id)",
+    "result": [
+        {"dataIndex": "probeId", "header": "ProbeId", "width": 100},
+        {"dataIndex": "netzbetreiberId", "header": "Land", "width": 50},
+        {"dataIndex": "mstId", "header": "MST", "width": 50},
+        {"dataIndex": "entnahmeVon", "header": "Entnahme von", "width": 120},
+        {"dataIndex": "entnahmeBis", "header": "Entnahme bis", "width": 120},
+        {"dataIndex": "mmtId", "header": "MMT", "width": 50},
+        {"dataIndex": "umwId", "header": "Umweltbereich", "width": 100},
+        {"dataIndex": "hauptprobenNr", "header": "Proben-Nr", "width": 100},
+        {"dataIndex": "ortsTyp", "header": "Ortstyp", "width": 50},
+        {"dataIndex": "genId", "header": "Gemeinde-ID", "width": 100},
+        {"dataIndex": "bezeichnung", "header": "Gemeinde", "flex": 1}
+    ],
+    "filters": [
+        {"dataIndex": "netz", "type": "listnetz", "label": "Land"},
+        {"dataIndex": "umw_id", "type": "listumw", "label": "Umweltbereich", "multiselect":true}
+    ]
 }
 ]
--- a/src/main/webapp/WEB-INF/classes/log4j.properties	Tue Oct 27 09:58:25 2015 +0100
+++ b/src/main/webapp/WEB-INF/classes/log4j.properties	Tue Oct 27 09:59:21 2015 +0100
@@ -3,7 +3,7 @@
 log4j.logger.org.openid4java=WARN
 log4j.logger.org.hibernate=ERROR
 log4j.appender.lada=org.apache.log4j.RollingFileAppender
-log4j.appender.lada.File=../log/lada-server.log
+log4j.appender.lada.File=/var/log/wildfly/lada-server.log
 log4j.appender.lada.MaxFileSize=1MB
 log4j.appender.lada.MaxBackupIndex=5
 log4j.appender.lada.layout=org.apache.log4j.PatternLayout
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)