changeset 907:ca1af8bbfe1f

merged.
author Raimund Renkert <raimund.renkert@intevation.de>
date Wed, 06 Apr 2016 12:20:12 +0200
parents 22052e4808c0 (current diff) df2908334150 (diff)
children 51051e85348f
files
diffstat 7 files changed, 181 insertions(+), 356 deletions(-) [+]
line wrap: on
line diff
--- a/db_schema/lada_schema.sql	Wed Apr 06 12:19:31 2016 +0200
+++ b/db_schema/lada_schema.sql	Wed Apr 06 12:20:12 2016 +0200
@@ -361,25 +361,26 @@
 --
 
 CREATE TABLE probe (
-    id integer DEFAULT nextval('probe_id_seq'::regclass) NOT NULL,
+    id integer PRIMARY KEY DEFAULT nextval('probe_id_seq'::regclass),
     test boolean DEFAULT false NOT NULL,
-    netzbetreiber_id character varying(2),
-    mst_id character varying(5),
-    labor_mst_id character varying(5),
+    netzbetreiber_id character varying(2) REFERENCES stammdaten.netz_betreiber,
+    mst_id character varying(5) NOT NULL
+        REFERENCES stammdaten.mess_stelle,
+    labor_mst_id character varying(5) NOT NULL
+        REFERENCES stammdaten.mess_stelle,
     hauptproben_nr character varying(20),
-    datenbasis_id smallint,
+    datenbasis_id smallint REFERENCES stammdaten.datenbasis,
     ba_id character varying(1),
-    probenart_id smallint NOT NULL,
+    probenart_id smallint NOT NULL REFERENCES stammdaten.probenart,
     media_desk character varying(100),
     media character varying(100),
-    umw_id character varying(3),
+    umw_id character varying(3) REFERENCES stammdaten.umwelt,
     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: -
 --
@@ -522,43 +523,6 @@
 
 
 --
--- 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,
-    labor_mst_id character varying(5),
-    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: -
 --
 
@@ -571,6 +535,37 @@
 
 
 --
+-- Name: messprogramm; Type: TABLE; Schema: land; Owner: -; Tablespace:
+--
+
+CREATE TABLE messprogramm (
+    id integer PRIMARY KEY DEFAULT nextval('messprogramm_id_seq'::regclass),
+    name character varying(256),
+    test boolean DEFAULT false NOT NULL,
+    netzbetreiber_id character varying(2) NOT NULL
+        REFERENCES stammdaten.netz_betreiber,
+    mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
+    labor_mst_id character varying(5) NOT NULL
+        REFERENCES stammdaten.mess_stelle,
+    datenbasis_id integer NOT NULL REFERENCES stammdaten.datenbasis,
+    ba_id character varying(1),
+    gem_id character varying(8),
+    ort_id integer REFERENCES stammdaten.ort,
+    media_desk character varying(100),
+    umw_id character varying(3) REFERENCES stammdaten.umwelt,
+    probenart_id integer NOT NULL REFERENCES stammdaten.probenart,
+    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: messprogramm_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: -
 --
 
@@ -578,6 +573,13 @@
 
 
 --
+-- 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_mmt; Type: TABLE; Schema: land; Owner: -; Tablespace:
 --
 
@@ -686,6 +688,33 @@
 )
 INHERITS (bund.probe);
 
+ALTER TABLE ONLY probe
+    ADD CONSTRAINT probe_pkey PRIMARY KEY (id);
+
+ALTER TABLE ONLY probe
+    ADD CONSTRAINT probe_datenbasis_id_fkey
+    FOREIGN KEY (datenbasis_id) REFERENCES stammdaten.datenbasis(id);
+
+ALTER TABLE ONLY probe
+    ADD CONSTRAINT probe_mst_id_fkey
+    FOREIGN KEY (mst_id) REFERENCES stammdaten.mess_stelle(id);
+
+ALTER TABLE ONLY probe
+    ADD CONSTRAINT probe_labor_mst_id_fkey
+    FOREIGN KEY (labor_mst_id) REFERENCES stammdaten.mess_stelle(id);
+
+ALTER TABLE ONLY probe
+    ADD CONSTRAINT probe_netzbetreiber_id_fkey
+    FOREIGN KEY (netzbetreiber_id) REFERENCES stammdaten.netz_betreiber(id);
+
+ALTER TABLE ONLY probe
+    ADD CONSTRAINT probe_probenart_id_fkey
+    FOREIGN KEY (probenart_id) REFERENCES stammdaten.probenart(id);
+
+ALTER TABLE ONLY probe
+    ADD CONSTRAINT probe_umw_id_fkey
+    FOREIGN KEY (umw_id) REFERENCES stammdaten.umwelt(id);
+
 
 --
 -- Name: probe_translation; Type: TABLE; Schema: land; Owner: -; Tablespace:
@@ -821,13 +850,6 @@
 -- 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);
 
 
@@ -898,27 +920,6 @@
 -- 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);
 
 
@@ -1008,14 +1009,6 @@
 
 
 --
--- Name: probe_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
---
-
-ALTER TABLE ONLY probe
-    ADD CONSTRAINT probe_pkey PRIMARY KEY (id);
-
-
---
 -- Name: status_protokoll_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
 --
 
@@ -1066,14 +1059,6 @@
 
 
 --
--- 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:
 --
 
@@ -1122,14 +1107,6 @@
 
 
 --
--- 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:
 --
 
@@ -1359,54 +1336,6 @@
 
 
 --
--- 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_labor_mst_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
---
-
-ALTER TABLE ONLY probe
-    ADD CONSTRAINT probe_labor_mst_id_fkey FOREIGN KEY (labor_mst_id) REFERENCES stammdaten.mess_stelle(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_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_protokoll_status_stufe_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
 --
 
@@ -1457,14 +1386,6 @@
 
 
 --
--- 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: -
 --
 
@@ -1481,46 +1402,6 @@
 
 
 --
--- 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: -
 --
 
@@ -1601,38 +1482,6 @@
 
 
 --
--- 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: -
 --
 
@@ -1641,14 +1490,6 @@
 
 
 --
--- 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_protokoll_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
 --
 
--- a/db_schema/stammdaten_schema.sql	Wed Apr 06 12:19:31 2016 +0200
+++ b/db_schema/stammdaten_schema.sql	Wed Apr 06 12:20:12 2016 +0200
@@ -77,6 +77,69 @@
 $$;
 
 
+CREATE SEQUENCE staat_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+CREATE TABLE staat (
+    id integer PRIMARY KEY DEFAULT nextval('staat_id_seq'::regclass),
+    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
+);
+
+ALTER SEQUENCE staat_id_seq OWNED BY staat.id;
+
+
+CREATE TABLE verwaltungseinheit (
+    id character varying(8) PRIMARY KEY,
+    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
+);
+
+
+CREATE TABLE netz_betreiber (
+    id character varying(2) PRIMARY KEY,
+    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)
+);
+
+
+CREATE TABLE mess_stelle (
+    id character varying(5) PRIMARY KEY,
+    netzbetreiber_id character varying(2),
+    beschreibung character varying(300),
+    mess_stelle character varying(60),
+    mst_typ character varying(1),
+    amtskennung character varying(6)
+);
+
+
 CREATE SEQUENCE auth_id_seq
     START WITH 1
     INCREMENT BY 1
@@ -149,11 +212,12 @@
 CREATE TABLE datensatz_erzeuger (
     id integer PRIMARY KEY
         DEFAULT nextval('datensatz_erzeuger_id_seq'::regclass),
-    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
+    netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
+    da_erzeuger_id character varying(2) NOT NULL,
+    mst_id character varying(5) NOT NULL REFERENCES mess_stelle,
+    bezeichnung character varying(120) NOT NULL,
+    letzte_aenderung timestamp without time zone,
+    UNIQUE(da_erzeuger_id, netzbetreiber_id)
 );
 
 ALTER SEQUENCE datensatz_erzeuger_id_seq OWNED BY datensatz_erzeuger.id;
@@ -336,17 +400,6 @@
 );
 
 
-CREATE TABLE mess_stelle (
-    id character varying(5) PRIMARY KEY,
-    netzbetreiber_id character varying(2),
-    beschreibung character varying(300),
-    mess_stelle character varying(60),
-    mst_typ character varying(1),
-    amtskennung character varying(6)
-);
-
-
-
 CREATE SEQUENCE messgroesse_id_seq
     START WITH 1
     INCREMENT BY 1
@@ -395,10 +448,11 @@
 CREATE TABLE messprogramm_kategorie (
     id integer PRIMARY KEY
         DEFAULT nextval('messprogramm_kategorie_id_seq'::regclass),
-    netzbetreiber_id character varying(2),
-    mpl_id character varying(3),
-    bezeichnung character varying(120),
-    letzte_aenderung timestamp without time zone
+    netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
+    mpl_id character varying(3) NOT NULL,
+    bezeichnung character varying(120) NOT NULL,
+    letzte_aenderung timestamp without time zone,
+    UNIQUE(mpl_id, netzbetreiber_id)
 );
 
 ALTER SEQUENCE messprogramm_kategorie_id_seq
@@ -425,14 +479,9 @@
   WHERE (mg_grp.messgroessengruppe_id = mmt_messgroesse_grp.messgroessengruppe_id);
 
 
-CREATE TABLE netz_betreiber (
-    id character varying(2) PRIMARY KEY,
-    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)
+CREATE TABLE ort_typ (
+    id smallint PRIMARY KEY,
+    ort_typ character varying(60)
 );
 
 
@@ -445,24 +494,24 @@
 
 CREATE TABLE ort (
     id integer PRIMARY KEY DEFAULT nextval('ort_id_seq'::regclass),
-    netzbetreiber_id character varying(2),
-    ort_id character varying(10),
-    langtext character varying(100),
-    staat_id smallint,
-    gem_id character varying(8),
+    netzbetreiber_id character varying(2) REFERENCES netz_betreiber,
+    ort_id character varying(10) NOT NULL,
+    langtext character varying(100) NOT NULL,
+    staat_id smallint NOT NULL REFERENCES staat,
+    gem_id character varying(8) REFERENCES verwaltungseinheit,
     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),
+    kda_id integer NOT NULL REFERENCES koordinaten_art,
+    koord_x_extern character varying(22) NOT NULL,
+    koord_y_extern character varying(22) NOT NULL,
     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),
+    ort_typ smallint REFERENCES ort_typ,
+    kurztext character varying(15) NOT NULL,
     berichtstext character varying(70),
     zone character varying(1),
     sektor character varying(2),
@@ -470,18 +519,19 @@
     mp_art character varying(10),
     aktiv character(1),
     anlage_id integer,
-    oz_id integer
+    oz_id integer,
+    UNIQUE(ort_id, netzbetreiber_id)
 );
 
+ALTER TABLE ONLY ort
+    ADD CONSTRAINT ort_anlage_fkey FOREIGN KEY (anlage_id) REFERENCES ort(id);
+
+ALTER TABLE ONLY ort
+    ADD CONSTRAINT ort_oz_fkey FOREIGN KEY (oz_id) REFERENCES ort(id);
+
 ALTER SEQUENCE ort_id_seq OWNED BY ort.id;
 
 
-CREATE TABLE ort_typ (
-    id smallint PRIMARY KEY,
-    ort_typ character varying(60)
-);
-
-
 CREATE TABLE ortszuordnung_typ (
     id character(1) PRIMARY KEY,
     ortstyp character varying(60)
@@ -542,20 +592,21 @@
 
 CREATE TABLE probenehmer (
     id integer PRIMARY KEY DEFAULT nextval('probenehmer_id_seq'::regclass),
-    netzbetreiber_id character varying(2),
-    prn_id character varying(9),
+    netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
+    prn_id character varying(9) NOT NULL,
     bearbeiter character varying(25),
     bemerkung character varying(60),
     betrieb character varying(80),
-    bezeichnung character varying(80),
-    kurz_bezeichnung character varying(10),
+    bezeichnung character varying(80) NOT NULL,
+    kurz_bezeichnung character varying(10) NOT NULL,
     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
+    letzte_aenderung timestamp without time zone,
+    UNIQUE(prn_id, netzbetreiber_id)
 );
 
 ALTER SEQUENCE probenehmer_id_seq OWNED BY probenehmer.id;
@@ -600,28 +651,6 @@
 
 
 
-CREATE SEQUENCE staat_id_seq
-    START WITH 1
-    INCREMENT BY 1
-    NO MINVALUE
-    NO MAXVALUE
-    CACHE 1;
-
-CREATE TABLE staat (
-    id integer PRIMARY KEY DEFAULT nextval('staat_id_seq'::regclass),
-    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
-);
-
-ALTER SEQUENCE staat_id_seq OWNED BY staat.id;
-
-
 -- Status workflow
 CREATE TABLE status_stufe (
     id integer PRIMARY KEY,
@@ -745,26 +774,6 @@
 );
 
 
-CREATE TABLE verwaltungseinheit (
-    id character varying(8) PRIMARY KEY,
-    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
-);
-
-
 
 
 ALTER TABLE ONLY lada_user
@@ -824,16 +833,6 @@
 
 
 
-ALTER TABLE ONLY datensatz_erzeuger
-    ADD CONSTRAINT datensatz_erzeuger_mst_id_fkey1 FOREIGN KEY (mst_id) REFERENCES mess_stelle(id);
-
-
-
-ALTER TABLE ONLY datensatz_erzeuger
-    ADD CONSTRAINT datensatz_erzeuger_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
-
-
-
 ALTER TABLE ONLY favorite
     ADD CONSTRAINT favorite_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
 
@@ -864,46 +863,6 @@
 
 
 
-ALTER TABLE ONLY messprogramm_kategorie
-    ADD CONSTRAINT messprogramm_kategorie_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
-
-
-
-ALTER TABLE ONLY ort
-    ADD CONSTRAINT ort_anlage_fkey FOREIGN KEY (anlage_id) REFERENCES ort(id);
-
-
-
-ALTER TABLE ONLY ort
-    ADD CONSTRAINT ort_gem_id_fkey FOREIGN KEY (gem_id) REFERENCES verwaltungseinheit(id);
-
-
-
-ALTER TABLE ONLY ort
-    ADD CONSTRAINT ort_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
-
-
-
-ALTER TABLE ONLY ort
-    ADD CONSTRAINT ort_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
-
-
-
-ALTER TABLE ONLY ort
-    ADD CONSTRAINT ort_ort_typ_fkey FOREIGN KEY (ort_typ) REFERENCES ort_typ(id);
-
-
-
-ALTER TABLE ONLY ort
-    ADD CONSTRAINT ort_oz_fkey FOREIGN KEY (oz_id) REFERENCES ort(id);
-
-
-
-ALTER TABLE ONLY ort
-    ADD CONSTRAINT ort_staat_id_fkey FOREIGN KEY (staat_id) REFERENCES staat(id);
-
-
-
 ALTER TABLE ONLY pflicht_messgroesse
     ADD CONSTRAINT pflicht_messgroesse_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES datenbasis(id);
 
@@ -924,11 +883,6 @@
 
 
 
-ALTER TABLE ONLY probenehmer
-    ADD CONSTRAINT probenehmer_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
-
-
-
 ALTER TABLE ONLY result
     ADD CONSTRAINT result_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
 
--- a/src/test/resources/datasets/dbUnit_messprogramm.json	Wed Apr 06 12:19:31 2016 +0200
+++ b/src/test/resources/datasets/dbUnit_messprogramm.json	Wed Apr 06 12:20:12 2016 +0200
@@ -5,6 +5,7 @@
         "test": false,
         "netzbetreiber_id": "06",
         "mst_id": "06010",
+        "labor_mst_id": "06010",
         "datenbasis_id": 2,
         "gem_id": "11000000",
         "media_desk": "D: 50 90 01 06 02 05 00 00 00 00 00 00",
--- a/src/test/resources/datasets/dbUnit_probe.json	Wed Apr 06 12:19:31 2016 +0200
+++ b/src/test/resources/datasets/dbUnit_probe.json	Wed Apr 06 12:20:12 2016 +0200
@@ -7,6 +7,7 @@
         "media": "Trinkwasser Zentralversorgung Oberflächenwasser aufbereitet",
         "media_desk": "D: 59 04 01 00 05 05 01 02 00 00 00 00",
         "mst_id": "06010",
+        "labor_mst_id": "06010",
         "netzbetreiber_id": "06",
         "probeentnahme_beginn": "2012-05-03 13:07:00",
         "probenart_id": 1,
--- a/src/test/resources/datasets/messprogramm.json	Wed Apr 06 12:19:31 2016 +0200
+++ b/src/test/resources/datasets/messprogramm.json	Wed Apr 06 12:20:12 2016 +0200
@@ -8,6 +8,7 @@
     "letzteAenderung": 1381413048800,
     "mediaDesk": "D: 01 01 01 01 01 01 00 00 00 00 00 00",
     "mstId": "06010",
+    "laborMstId": "06010",
     "name": "Test 1",
     "netzbetreiberId": "06",
     "ortId": null,
--- a/src/test/resources/datasets/probe.json	Wed Apr 06 12:19:31 2016 +0200
+++ b/src/test/resources/datasets/probe.json	Wed Apr 06 12:20:12 2016 +0200
@@ -10,6 +10,7 @@
     "mplId":"",
     "mprId":"",
     "mstId":"06010",
+    "laborMstId":"06010",
     "netzbetreiberId":"11",
     "probeNehmerId":3,
     "probenartId":1,
--- a/src/test/resources/log4j.properties	Wed Apr 06 12:19:31 2016 +0200
+++ b/src/test/resources/log4j.properties	Wed Apr 06 12:20:12 2016 +0200
@@ -1,9 +1,9 @@
-log4j.rootLogger=INFO, lada
+log4j.rootLogger=DEBUG, lada
 log4j.logger.org.apache.http=INFO
 log4j.logger.org.xnio=WARN
 log4j.logger.org.hibernate=INFO
 log4j.appender.lada=org.apache.log4j.RollingFileAppender
-log4j.appender.lada.File=../log/lada-server-test.log
+log4j.appender.lada.File=log/lada-server-test.log
 log4j.appender.lada.MaxFileSize=5MB
 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)