tom@866: \set ON_ERROR_STOP on
tom@866: 
tom@866: BEGIN;
tom@866: 
tom@866: SET statement_timeout = 0;
tom@866: SET lock_timeout = 0;
tom@866: SET client_encoding = 'UTF8';
tom@866: SET standard_conforming_strings = on;
tom@866: SET check_function_bodies = false;
tom@866: SET client_min_messages = warning;
tom@866: 
tom@866: 
tom@866: CREATE SCHEMA stammdaten;
tom@866: 
tom@866: SET search_path = stammdaten, pg_catalog;
tom@866: 
raimund@926: CREATE FUNCTION update_letzte_aenderung() RETURNS trigger
raimund@926:     LANGUAGE plpgsql
raimund@926:     AS $$
raimund@926:     BEGIN
raimund@926:         NEW.letzte_aenderung = now();
raimund@926:         RETURN NEW;
raimund@926:     END;
raimund@926: $$;
tom@866: 
tom@866: CREATE FUNCTION get_media_from_media_desk(media_desk character varying) RETURNS character varying
tom@866:     LANGUAGE plpgsql
tom@866:     AS $$
tom@866: declare
tom@866:   result character varying(100);
tom@866:   d00 smallint;
tom@866:   d01 smallint;
tom@866:   d02 smallint;
tom@866:   d03 smallint;
tom@866: begin
tom@866:   if media_desk like 'D: %' then
tom@866:     d00 := substring(media_desk,4,2);
tom@866:     d01 := substring(media_desk,7,2);
tom@866:     d02 := substring(media_desk,10,2);
tom@866:     d03 := substring(media_desk,13,2);
tom@866:     if d00 = '00' then
tom@866:       result := null;
tom@866:     else
tom@866:       if d01 = '00' then
tom@866:         select s00.beschreibung into result FROM stammdaten.deskriptoren s00
tom@866:         where s00.ebene = 0 and s00.sn = d00::smallint;
tom@866:       else
tom@866:         if d02 = '00' or d00 <> '01' then
tom@866:           select s01.beschreibung into result FROM stammdaten.deskriptoren s01
tom@866:           where s01.ebene = 1 and s01.sn = d01::smallint
tom@866:             and s01.vorgaenger =
tom@866:               (select s00.id FROM stammdaten.deskriptoren s00
tom@866:                where s00.ebene = 0 and s00.sn = d00::smallint);
tom@866:         else
tom@866:           if d03 = '00' then
tom@866:             select s02.beschreibung into result FROM stammdaten.deskriptoren s02
tom@866:             where s02.ebene = 2 and s02.sn = d02::smallint
tom@866:               and s02.vorgaenger =
tom@866:                 (select s01.id FROM stammdaten.deskriptoren s01
tom@866:                  where s01.ebene = 1 and s01.sn = d01::smallint
tom@866:                    and s01.vorgaenger =
tom@866:                      (select s00.id FROM stammdaten.deskriptoren s00
tom@866:                       where s00.ebene = 0 and s00.sn = d00::smallint));
tom@866:           else
tom@866:             select s03.beschreibung into result FROM stammdaten.deskriptoren s03
tom@866:             where s03.ebene = 3 and s03.sn = d03::smallint
tom@866:               and s03.vorgaenger =
tom@866:               (select s02.id FROM stammdaten.deskriptoren s02
tom@866:               where s02.ebene = 2 and s02.sn = d02::smallint
tom@866:                 and s02.vorgaenger =
tom@866:                   (select s01.id FROM stammdaten.deskriptoren s01
tom@866:                   where s01.ebene = 1 and s01.sn = d01::smallint
tom@866:                     and s01.vorgaenger =
tom@866:                       (select s00.id FROM stammdaten.deskriptoren s00
tom@866:                       where s00.ebene = 0 and s00.sn = d00::smallint)));
tom@866:           end if;
tom@866:         end if;
tom@866:       end if;
tom@866:     end if;
tom@866:   else
tom@866:     result := null;
tom@866:   end if;
tom@866:   return (result);
tom@866: end;
tom@866: $$;
tom@866: 
tom@866: 
tom@902: CREATE SEQUENCE staat_id_seq
tom@902:     START WITH 1
tom@902:     INCREMENT BY 1
tom@902:     NO MINVALUE
tom@902:     NO MAXVALUE
tom@902:     CACHE 1;
tom@902: 
tom@902: CREATE TABLE staat (
tom@902:     id integer PRIMARY KEY DEFAULT nextval('staat_id_seq'::regclass),
tom@902:     staat character varying(50) NOT NULL,
tom@902:     hkl_id smallint NOT NULL,
tom@902:     staat_iso character varying(2) NOT NULL,
tom@902:     staat_kurz character varying(5),
tom@902:     eu character(1) DEFAULT NULL::bpchar,
tom@902:     koord_x_extern character varying(22),
tom@902:     koord_y_extern character varying(22),
tom@902:     kda_id integer
tom@902: );
tom@902: 
tom@902: ALTER SEQUENCE staat_id_seq OWNED BY staat.id;
tom@902: 
tom@902: 
tom@902: CREATE TABLE verwaltungseinheit (
tom@902:     id character varying(8) PRIMARY KEY,
tom@902:     bundesland character varying(8) NOT NULL,
tom@902:     kda_id integer,
tom@902:     kreis character varying(8),
tom@902:     nuts character varying(10),
tom@902:     regbezirk character varying(8),
tom@902:     bezeichnung character varying(80) NOT NULL,
tom@902:     is_bundesland character(1) NOT NULL,
tom@902:     is_gemeinde character(1) NOT NULL,
tom@902:     is_landkreis character(1) NOT NULL,
tom@902:     is_regbezirk character(1) NOT NULL,
tom@902:     koord_x_extern character varying(22),
tom@902:     koord_y_extern character varying(22),
tom@902:     plz character varying(6),
tom@902:     longitude double precision,
tom@902:     latitude double precision
tom@902: );
tom@902: 
tom@902: 
tom@902: CREATE TABLE netz_betreiber (
tom@902:     id character varying(2) PRIMARY KEY,
tom@902:     netzbetreiber character varying(50),
tom@902:     idf_netzbetreiber character varying(1),
tom@902:     is_bmn boolean DEFAULT false,
tom@902:     mailverteiler character varying(512),
tom@902:     aktiv boolean DEFAULT false,
tom@902:     zust_mst_id character varying(5)
tom@902: );
tom@902: 
tom@902: 
tom@902: CREATE TABLE mess_stelle (
tom@902:     id character varying(5) PRIMARY KEY,
tom@968:     netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
tom@902:     beschreibung character varying(300),
tom@902:     mess_stelle character varying(60),
tom@902:     mst_typ character varying(1),
tom@902:     amtskennung character varying(6)
tom@902: );
tom@902: 
tom@902: 
tom@866: CREATE SEQUENCE auth_id_seq
tom@866:     START WITH 1
tom@866:     INCREMENT BY 1
tom@866:     NO MINVALUE
tom@866:     NO MAXVALUE
tom@866:     CACHE 1;
tom@866: 
tom@868: CREATE TABLE auth (
tom@868:     id integer PRIMARY KEY DEFAULT nextval('auth_id_seq'::regclass),
tom@868:     ldap_group character varying(40) NOT NULL,
tom@868:     netzbetreiber_id character varying(2),
tom@868:     mst_id character varying(5),
tom@868:     labor_mst_id character varying(5),
tom@868:     funktion_id smallint
tom@868: );
tom@866: 
tom@866: ALTER SEQUENCE auth_id_seq OWNED BY auth.id;
tom@866: 
tom@866: 
tom@868: CREATE TABLE auth_funktion (
tom@868:     id smallint PRIMARY KEY,
tom@872:     funktion character varying(40) UNIQUE NOT NULL
tom@866: );
tom@869: INSERT INTO auth_funktion VALUES (0, 'Erfasser');
tom@869: INSERT INTO auth_funktion VALUES (1, 'Status-Erfasser');
tom@869: INSERT INTO auth_funktion VALUES (2, 'Status-Land');
tom@869: INSERT INTO auth_funktion VALUES (3, 'Status-Leitstelle');
tom@869: INSERT INTO auth_funktion VALUES (4, 'Stammdatenpflege-Land');
tom@866: 
tom@870: 
tom@866: CREATE SEQUENCE auth_lst_umw_id_seq
tom@866:     START WITH 1
tom@866:     INCREMENT BY 1
tom@866:     NO MINVALUE
tom@866:     NO MAXVALUE
tom@866:     CACHE 1;
tom@866: 
tom@868: CREATE TABLE auth_lst_umw (
tom@868:     id integer PRIMARY KEY DEFAULT nextval('auth_lst_umw_id_seq'::regclass),
tom@868:     lst_id character varying(5),
tom@868:     umw_id character varying(3)
tom@868: );
tom@866: 
tom@866: ALTER SEQUENCE auth_lst_umw_id_seq OWNED BY auth_lst_umw.id;
tom@866: 
tom@866: 
tom@866: CREATE SEQUENCE datenbasis_id_seq
tom@866:     START WITH 1
tom@866:     INCREMENT BY 1
tom@866:     NO MINVALUE
tom@866:     NO MAXVALUE
tom@866:     CACHE 1;
tom@866: 
tom@868: CREATE TABLE datenbasis (
tom@868:     id integer PRIMARY KEY DEFAULT nextval('datenbasis_id_seq'::regclass),
tom@868:     beschreibung character varying(30),
tom@868:     datenbasis character varying(6)
tom@868: );
tom@866: 
tom@866: ALTER SEQUENCE datenbasis_id_seq OWNED BY datenbasis.id;
tom@866: 
tom@866: 
tom@866: CREATE SEQUENCE datensatz_erzeuger_id_seq
tom@866:     START WITH 1
tom@866:     INCREMENT BY 1
tom@866:     NO MINVALUE
tom@866:     NO MAXVALUE
tom@866:     CACHE 1;
tom@866: 
tom@868: CREATE TABLE datensatz_erzeuger (
tom@868:     id integer PRIMARY KEY
tom@868:         DEFAULT nextval('datensatz_erzeuger_id_seq'::regclass),
tom@904:     netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
tom@904:     da_erzeuger_id character varying(2) NOT NULL,
tom@904:     mst_id character varying(5) NOT NULL REFERENCES mess_stelle,
tom@904:     bezeichnung character varying(120) NOT NULL,
tom@905:     letzte_aenderung timestamp without time zone,
tom@905:     UNIQUE(da_erzeuger_id, netzbetreiber_id)
tom@868: );
raimund@926: CREATE TRIGGER letzte_aenderung_datensatz_erzeuger BEFORE UPDATE ON datensatz_erzeuger FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
tom@866: 
tom@866: ALTER SEQUENCE datensatz_erzeuger_id_seq OWNED BY datensatz_erzeuger.id;
tom@866: 
tom@866: CREATE TABLE deskriptor_umwelt (
tom@868:     id integer PRIMARY KEY,
tom@866:     s00 integer NOT NULL,
tom@866:     s01 integer NOT NULL,
tom@866:     s02 integer,
tom@866:     s03 integer,
tom@866:     s04 integer,
tom@866:     s05 integer,
tom@866:     s06 integer,
tom@866:     s07 integer,
tom@866:     s08 integer,
tom@866:     s09 integer,
tom@866:     s10 integer,
tom@866:     s11 integer,
tom@866:     s12 integer,
tom@866:     umw_id character varying(3) NOT NULL
tom@866: );
tom@866: 
tom@866: 
tom@868: CREATE SEQUENCE deskriptoren_id_seq
tom@868:     START WITH 1
tom@868:     INCREMENT BY 1
tom@868:     NO MINVALUE
tom@868:     NO MAXVALUE
tom@868:     CACHE 1;
tom@866: 
tom@866: CREATE TABLE deskriptoren (
tom@868:     id integer PRIMARY KEY DEFAULT nextval('deskriptoren_id_seq'::regclass),
tom@866:     vorgaenger integer,
tom@866:     ebene smallint,
tom@866:     s_xx integer,
tom@866:     sn smallint,
tom@866:     beschreibung character varying(100),
tom@866:     bedeutung character varying(300)
tom@866: );
tom@866: 
tom@866: ALTER SEQUENCE deskriptoren_id_seq OWNED BY deskriptoren.id;
tom@866: 
tom@866: 
tom@954: CREATE SEQUENCE lada_user_id_seq
tom@954:     START WITH 1
tom@954:     INCREMENT BY 1
tom@954:     NO MINVALUE
tom@954:     NO MAXVALUE
tom@954:     CACHE 1;
tom@954: 
tom@954: CREATE TABLE lada_user (
tom@954:     id integer PRIMARY KEY DEFAULT nextval('lada_user_id_seq'::regclass),
tom@954:     name character varying(80) NOT NULL
tom@954: );
tom@954: 
tom@954: ALTER SEQUENCE lada_user_id_seq OWNED BY lada_user.id;
tom@954: 
tom@954: 
tom@954: CREATE SEQUENCE query_id_seq
tom@954:     START WITH 1
tom@954:     INCREMENT BY 1
tom@954:     NO MINVALUE
tom@954:     NO MAXVALUE
tom@954:     CACHE 1;
tom@954: 
tom@954: CREATE TABLE query (
tom@954:     id integer PRIMARY KEY DEFAULT nextval('query_id_seq'::regclass),
tom@954:     name character varying(80) NOT NULL,
tom@1093:     type character varying(30) NOT NULL
tom@1093:         CHECK(type IN('probe', 'messung', 'messprogramm', 'ort',
tom@1093:             'probenehmer', 'datensatzerzeuger', 'messprogrammkategorie')),
tom@954:     sql character varying(1500) NOT NULL,
tom@1093:     description character varying(100),
tom@1093:     UNIQUE (name, type)
tom@954: );
tom@954: 
tom@954: ALTER SEQUENCE query_id_seq OWNED BY query.id;
tom@954: 
tom@954: 
tom@866: CREATE SEQUENCE favorite_id_seq
tom@866:     START WITH 1
tom@866:     INCREMENT BY 1
tom@866:     NO MINVALUE
tom@866:     NO MAXVALUE
tom@866:     CACHE 1;
tom@866: 
tom@868: CREATE TABLE favorite (
tom@868:     id integer PRIMARY KEY DEFAULT nextval('favorite_id_seq'::regclass),
tom@954:     user_id integer NOT NULL REFERENCES lada_user,
tom@954:     query_id integer NOT NULL REFERENCES query ON DELETE CASCADE
tom@868: );
tom@866: 
tom@866: ALTER SEQUENCE favorite_id_seq OWNED BY favorite.id;
tom@866: 
tom@866: 
tom@866: CREATE SEQUENCE filter_id_seq
tom@866:     START WITH 1
tom@866:     INCREMENT BY 1
tom@866:     NO MINVALUE
tom@866:     NO MAXVALUE
tom@866:     CACHE 1;
tom@866: 
tom@868: CREATE TABLE filter (
tom@868:     id integer PRIMARY KEY DEFAULT nextval('filter_id_seq'::regclass),
tom@954:     query_id integer NOT NULL REFERENCES query ON DELETE CASCADE,
tom@868:     data_index character varying(50) NOT NULL,
tom@1093:     type character varying(10) NOT NULL
tom@1093:         CHECK(type IN('liststatus', 'listmst', 'listnetz', 'listumw', 'text')),
tom@868:     label character varying(50) NOT NULL,
tom@868:     multiselect boolean
tom@868: );
tom@866: 
tom@866: ALTER SEQUENCE filter_id_seq OWNED BY filter.id;
tom@866: 
tom@866: 
tom@866: CREATE SEQUENCE filter_value_id_seq
tom@866:     START WITH 1
tom@866:     INCREMENT BY 1
tom@866:     NO MINVALUE
tom@866:     NO MAXVALUE
tom@866:     CACHE 1;
tom@866: 
tom@868: CREATE TABLE filter_value (
tom@868:     id integer PRIMARY KEY DEFAULT nextval('filter_value_id_seq'::regclass),
tom@954:     user_id integer NOT NULL REFERENCES lada_user,
tom@954:     filter_id integer NOT NULL REFERENCES filter ON DELETE CASCADE,
tom@868:     value text
tom@868: );
tom@866: 
tom@866: ALTER SEQUENCE filter_value_id_seq OWNED BY filter_value.id;
tom@866: 
tom@866: 
tom@866: CREATE SEQUENCE koordinaten_art_id_seq
tom@866:     START WITH 1
tom@866:     INCREMENT BY 1
tom@866:     NO MINVALUE
tom@866:     NO MAXVALUE
tom@866:     CACHE 1;
tom@866: 
tom@868: CREATE TABLE koordinaten_art (
tom@868:     id integer PRIMARY KEY DEFAULT nextval('koordinaten_art_id_seq'::regclass),
tom@868:     koordinatenart character varying(50),
tom@868:     idf_geo_key character varying(1)
tom@868: );
tom@866: 
tom@866: ALTER SEQUENCE koordinaten_art_id_seq OWNED BY koordinaten_art.id;
tom@866: 
tom@866: 
tom@866: CREATE SEQUENCE mess_einheit_id_seq
tom@866:     START WITH 1
tom@866:     INCREMENT BY 1
tom@866:     NO MINVALUE
tom@866:     NO MAXVALUE
tom@866:     CACHE 1;
tom@866: 
tom@868: CREATE TABLE mess_einheit (
tom@868:     id integer PRIMARY KEY DEFAULT nextval('mess_einheit_id_seq'::regclass),
tom@868:     beschreibung character varying(50),
tom@868:     einheit character varying(12),
tom@868:     eudf_messeinheit_id character varying(8),
tom@868:     umrechnungs_faktor_eudf bigint
tom@868: );
tom@866: 
tom@866: ALTER SEQUENCE mess_einheit_id_seq OWNED BY mess_einheit.id;
tom@866: 
tom@866: 
tom@866: CREATE TABLE mess_methode (
tom@868:     id character varying(2) PRIMARY KEY,
tom@866:     beschreibung character varying(300),
tom@866:     messmethode character varying(50)
tom@866: );
tom@866: 
tom@866: 
tom@868: CREATE SEQUENCE messgroesse_id_seq
tom@868:     START WITH 1
tom@868:     INCREMENT BY 1
tom@868:     NO MINVALUE
tom@868:     NO MAXVALUE
tom@868:     CACHE 1;
tom@866: 
tom@866: CREATE TABLE messgroesse (
tom@868:     id integer PRIMARY KEY DEFAULT nextval('messgroesse_id_seq'::regclass),
tom@866:     beschreibung character varying(300),
tom@866:     messgroesse character varying(50) NOT NULL,
tom@866:     default_farbe character varying(9),
tom@866:     idf_nuklid_key character varying(6),
tom@866:     ist_leitnuklid boolean DEFAULT false,
tom@866:     eudf_nuklid_id bigint,
tom@866:     kennung_bvl character varying(7)
tom@866: );
tom@866: 
tom@866: ALTER SEQUENCE messgroesse_id_seq OWNED BY messgroesse.id;
tom@866: 
tom@866: 
tom@866: CREATE SEQUENCE messgroessen_gruppe_id_seq
tom@866:     START WITH 1
tom@866:     INCREMENT BY 1
tom@866:     NO MINVALUE
tom@866:     NO MAXVALUE
tom@866:     CACHE 1;
tom@866: 
tom@868: CREATE TABLE messgroessen_gruppe (
tom@868:     id integer PRIMARY KEY
tom@868:         DEFAULT nextval('messgroessen_gruppe_id_seq'::regclass),
tom@868:     bezeichnung character varying(80),
tom@868:     ist_leitnuklidgruppe character(1) DEFAULT NULL::bpchar
tom@868: );
tom@866: 
tom@866: ALTER SEQUENCE messgroessen_gruppe_id_seq OWNED BY messgroessen_gruppe.id;
tom@866: 
tom@866: 
tom@866: CREATE SEQUENCE messprogramm_kategorie_id_seq
tom@866:     START WITH 1
tom@866:     INCREMENT BY 1
tom@866:     NO MINVALUE
tom@866:     NO MAXVALUE
tom@866:     CACHE 1;
tom@866: 
tom@868: CREATE TABLE messprogramm_kategorie (
tom@868:     id integer PRIMARY KEY
tom@868:         DEFAULT nextval('messprogramm_kategorie_id_seq'::regclass),
tom@904:     netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
tom@904:     mpl_id character varying(3) NOT NULL,
tom@904:     bezeichnung character varying(120) NOT NULL,
tom@905:     letzte_aenderung timestamp without time zone,
tom@905:     UNIQUE(mpl_id, netzbetreiber_id)
tom@868: );
raimund@926: CREATE TRIGGER letzte_aenderung_messprogramm_kategorie BEFORE UPDATE ON messprogramm_kategorie FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
tom@866: 
tom@868: ALTER SEQUENCE messprogramm_kategorie_id_seq
tom@868:     OWNED BY messprogramm_kategorie.id;
tom@866: 
tom@866: 
tom@866: CREATE TABLE mg_grp (
tom@866:     messgroessengruppe_id integer NOT NULL,
tom@866:     messgroesse_id integer NOT NULL
tom@866: );
tom@866: 
tom@866: 
tom@866: CREATE TABLE mmt_messgroesse_grp (
tom@866:     messgroessengruppe_id integer NOT NULL,
tom@866:     mmt_id character varying(2) NOT NULL
tom@866: );
tom@866: 
tom@866: 
tom@866: CREATE VIEW mmt_messgroesse AS
tom@866:  SELECT mmt_messgroesse_grp.mmt_id,
tom@866:     mg_grp.messgroesse_id
tom@866:    FROM mmt_messgroesse_grp,
tom@866:     mg_grp
tom@866:   WHERE (mg_grp.messgroessengruppe_id = mmt_messgroesse_grp.messgroessengruppe_id);
tom@866: 
tom@866: 
tom@902: CREATE TABLE ort_typ (
tom@902:     id smallint PRIMARY KEY,
tom@902:     ort_typ character varying(60)
tom@866: );
tom@866: 
tom@866: 
tom@868: CREATE SEQUENCE ort_id_seq
tom@868:     START WITH 1
tom@868:     INCREMENT BY 1
tom@868:     NO MINVALUE
tom@868:     NO MAXVALUE
tom@868:     CACHE 1;
tom@866: 
tom@866: CREATE TABLE ort (
tom@868:     id integer PRIMARY KEY DEFAULT nextval('ort_id_seq'::regclass),
tom@912:     netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
tom@904:     ort_id character varying(10) NOT NULL,
tom@904:     langtext character varying(100) NOT NULL,
tom@904:     staat_id smallint NOT NULL REFERENCES staat,
tom@902:     gem_id character varying(8) REFERENCES verwaltungseinheit,
tom@866:     unscharf character(1) DEFAULT NULL::bpchar,
tom@866:     nuts_code character varying(10),
tom@904:     kda_id integer NOT NULL REFERENCES koordinaten_art,
tom@904:     koord_x_extern character varying(22) NOT NULL,
tom@904:     koord_y_extern character varying(22) NOT NULL,
tom@866:     hoehe_land real,
tom@866:     letzte_aenderung timestamp without time zone DEFAULT now(),
tom@866:     latitude double precision,
tom@866:     longitude double precision,
tom@866:     geom public.geometry(Point,4326),
tom@866:     shape public.geometry(MultiPolygon,4326),
tom@902:     ort_typ smallint REFERENCES ort_typ,
tom@904:     kurztext character varying(15) NOT NULL,
tom@866:     berichtstext character varying(70),
tom@866:     zone character varying(1),
tom@866:     sektor character varying(2),
tom@866:     zustaendigkeit character varying(10),
tom@866:     mp_art character varying(10),
tom@866:     aktiv character(1),
tom@866:     anlage_id integer,
tom@905:     oz_id integer,
tom@905:     UNIQUE(ort_id, netzbetreiber_id)
tom@866: );
raimund@926: CREATE TRIGGER letzte_aenderung_ort BEFORE UPDATE ON ort FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
tom@866: 
tom@902: ALTER TABLE ONLY ort
tom@902:     ADD CONSTRAINT ort_anlage_fkey FOREIGN KEY (anlage_id) REFERENCES ort(id);
tom@866: 
tom@902: ALTER TABLE ONLY ort
tom@902:     ADD CONSTRAINT ort_oz_fkey FOREIGN KEY (oz_id) REFERENCES ort(id);
tom@902: 
tom@902: ALTER SEQUENCE ort_id_seq OWNED BY ort.id;
tom@866: 
tom@866: 
tom@866: CREATE TABLE ortszuordnung_typ (
tom@868:     id character(1) PRIMARY KEY,
tom@866:     ortstyp character varying(60)
tom@866: );
tom@866: 
tom@866: 
tom@866: CREATE SEQUENCE pflicht_messgroesse_id_seq
tom@866:     START WITH 1
tom@866:     INCREMENT BY 1
tom@866:     NO MINVALUE
tom@866:     NO MAXVALUE
tom@866:     CACHE 1;
tom@866: 
tom@868: CREATE TABLE pflicht_messgroesse (
tom@868:     id integer PRIMARY KEY
tom@868:         DEFAULT nextval('pflicht_messgroesse_id_seq'::regclass),
tom@868:     messgroesse_id integer,
tom@868:     mmt_id character varying(2),
tom@868:     umw_id character varying(3),
tom@868:     datenbasis_id smallint NOT NULL
tom@868: );
tom@866: 
tom@866: ALTER SEQUENCE pflicht_messgroesse_id_seq OWNED BY pflicht_messgroesse.id;
tom@866: 
tom@866: 
tom@866: CREATE TABLE proben_zusatz (
tom@868:     id character varying(3) PRIMARY KEY,
tom@866:     meh_id integer,
tom@866:     beschreibung character varying(50) NOT NULL,
tom@866:     zusatzwert character varying(7) NOT NULL,
tom@866:     eudf_keyword character varying(40)
tom@866: );
tom@866: 
tom@866: 
tom@866: CREATE SEQUENCE probenart_id_seq
tom@866:     START WITH 1
tom@866:     INCREMENT BY 1
tom@866:     NO MINVALUE
tom@866:     NO MAXVALUE
tom@866:     CACHE 1;
tom@866: 
tom@868: CREATE TABLE probenart (
tom@868:     id integer PRIMARY KEY DEFAULT nextval('probenart_id_seq'::regclass),
tom@868:     beschreibung character varying(30),
tom@868:     probenart character varying(5) NOT NULL,
tom@868:     probenart_eudf_id character varying(1) NOT NULL
tom@868: );
tom@866: 
tom@866: ALTER SEQUENCE probenart_id_seq OWNED BY probenart.id;
tom@866: 
tom@866: 
tom@868: CREATE SEQUENCE probenehmer_id_seq
tom@868:     START WITH 1
tom@868:     INCREMENT BY 1
tom@868:     NO MINVALUE
tom@868:     NO MAXVALUE
tom@868:     CACHE 1;
tom@866: 
tom@866: CREATE TABLE probenehmer (
tom@868:     id integer PRIMARY KEY DEFAULT nextval('probenehmer_id_seq'::regclass),
tom@904:     netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
tom@904:     prn_id character varying(9) NOT NULL,
tom@866:     bearbeiter character varying(25),
tom@866:     bemerkung character varying(60),
tom@866:     betrieb character varying(80),
tom@904:     bezeichnung character varying(80) NOT NULL,
tom@904:     kurz_bezeichnung character varying(10) NOT NULL,
tom@866:     ort character varying(20),
tom@866:     plz character varying(5),
tom@866:     strasse character varying(30),
tom@866:     telefon character varying(20),
tom@866:     tp character varying(3),
tom@866:     typ character(1),
tom@905:     letzte_aenderung timestamp without time zone,
tom@905:     UNIQUE(prn_id, netzbetreiber_id)
tom@866: );
raimund@926: CREATE TRIGGER letzte_aenderung_probenehmer BEFORE UPDATE ON probenehmer FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
tom@866: 
tom@866: ALTER SEQUENCE probenehmer_id_seq OWNED BY probenehmer.id;
tom@866: 
tom@866: 
tom@868: CREATE SEQUENCE result_id_seq
tom@868:     START WITH 1
tom@868:     INCREMENT BY 1
tom@868:     NO MINVALUE
tom@868:     NO MAXVALUE
tom@868:     CACHE 1;
tom@866: 
tom@866: CREATE TABLE result (
tom@868:     id integer PRIMARY KEY DEFAULT nextval('result_id_seq'::regclass),
tom@954:     query_id integer NOT NULL REFERENCES query ON DELETE CASCADE,
tom@866:     data_index character varying(50) NOT NULL,
tom@866:     header character varying(50) NOT NULL,
tom@866:     width integer,
tom@866:     flex boolean,
tom@1093:     index integer NOT NULL,
tom@1093:     UNIQUE (query_id, index),
tom@988:     UNIQUE (query_id, data_index)
tom@866: );
tom@866: 
tom@866: ALTER SEQUENCE result_id_seq OWNED BY result.id;
tom@866: 
tom@866: 
tom@866: 
tom@870: -- Status workflow
tom@866: CREATE TABLE status_stufe (
tom@868:     id integer PRIMARY KEY,
tom@872:     stufe character varying(50) UNIQUE NOT NULL
tom@866: );
tom@871: INSERT INTO status_stufe VALUES (1, 'MST');
tom@871: INSERT INTO status_stufe VALUES (2, 'LAND');
tom@871: INSERT INTO status_stufe VALUES (3, 'LST');
tom@871: 
tom@866: 
tom@866: CREATE TABLE status_wert (
tom@868:     id integer PRIMARY KEY,
tom@872:     wert character varying(50) UNIQUE NOT NULL
tom@866: );
tom@871: INSERT INTO status_wert VALUES (0, 'nicht vergeben');
tom@871: INSERT INTO status_wert VALUES (1, 'plausibel');
tom@871: INSERT INTO status_wert VALUES (2, 'nicht repräsentativ');
tom@871: INSERT INTO status_wert VALUES (3, 'nicht plausibel');
tom@871: INSERT INTO status_wert VALUES (4, 'Rückfrage');
tom@871: INSERT INTO status_wert VALUES (7, 'nicht lieferbar');
tom@871: INSERT INTO status_wert VALUES (8, 'zurückgesetzt');
tom@871: 
tom@866: 
tom@870: CREATE TABLE status_kombi (
tom@870:     id integer PRIMARY KEY,
tom@872:     stufe_id integer REFERENCES status_stufe NOT NULL,
tom@872:     wert_id integer REFERENCES status_wert NOT NULL,
tom@872:     UNIQUE(stufe_id, wert_id)
tom@870: );
tom@871: -- 'zurückgesetzt' is left out here deliberately!
tom@871: INSERT INTO status_kombi VALUES (1, 1, 0);
tom@871: INSERT INTO status_kombi VALUES (2, 1, 1);
tom@871: INSERT INTO status_kombi VALUES (3, 1, 2);
tom@871: INSERT INTO status_kombi VALUES (4, 1, 3);
tom@871: INSERT INTO status_kombi VALUES (5, 1, 7);
tom@871: INSERT INTO status_kombi VALUES (6, 2, 1);
tom@871: INSERT INTO status_kombi VALUES (7, 2, 2);
tom@871: INSERT INTO status_kombi VALUES (8, 2, 3);
tom@871: INSERT INTO status_kombi VALUES (9, 2, 4);
tom@871: INSERT INTO status_kombi VALUES (10, 3, 1);
tom@871: INSERT INTO status_kombi VALUES (11, 3, 2);
tom@871: INSERT INTO status_kombi VALUES (12, 3, 3);
tom@871: INSERT INTO status_kombi VALUES (13, 3, 4);
tom@870: 
tom@871: 
tom@871: CREATE SEQUENCE status_reihenfolge_id_seq;
tom@870: CREATE TABLE status_reihenfolge (
tom@871:     id integer PRIMARY KEY DEFAULT nextval('status_reihenfolge_id_seq'),
tom@872:     von_id integer REFERENCES status_kombi NOT NULL,
tom@872:     zu_id integer REFERENCES status_kombi NOT NULL,
tom@872:     UNIQUE(von_id, zu_id)
tom@870: );
tom@870: 
tom@871: CREATE FUNCTION populate_status_reihenfolge() RETURNS void AS $$
tom@871: DECLARE kombi_from RECORD;
tom@871: DECLARE s_from integer;
tom@871: DECLARE w_from integer;
tom@871: DECLARE kombi_to RECORD;
tom@871: DECLARE s_to integer;
tom@871: DECLARE w_to integer;
tom@871: 
tom@871: BEGIN
tom@871: FOR kombi_from IN SELECT * FROM status_kombi LOOP
tom@871:     s_from := kombi_from.stufe_id;
tom@871:     w_from := kombi_from.wert_id;
tom@871: 
tom@871:     FOR kombi_to IN SELECT * FROM status_kombi LOOP
tom@871:         s_to := kombi_to.stufe_id;
tom@871:         w_to := kombi_to.wert_id;
tom@871: 
tom@884:         IF s_from = s_to AND w_to <> 0 THEN
tom@871:            -- At the same 'stufe', all permutations occur,
tom@871:            -- but 'nicht vergeben' is only allowed for von_id
tom@871:            INSERT INTO status_reihenfolge (von_id, zu_id)
tom@871:                   VALUES (kombi_from.id, kombi_to.id);
tom@871: 
tom@871:         ELSEIF s_to = s_from + 1 AND w_from <> 0 AND w_from <> 4 THEN
tom@871:            -- Going to the next 'stufe' all available status_kombi are allowed
tom@871:            -- in case current wert is not 'nicht vergeben' or 'Rückfrage'
tom@871:            INSERT INTO status_reihenfolge (von_id, zu_id)
tom@871:                   VALUES (kombi_from.id, kombi_to.id);
tom@871: 
tom@871:         ELSEIF w_from = 4 AND s_to = 1 AND w_to >= 1 AND w_to <= 3 THEN
tom@871:            -- After 'Rückfrage' follows 'MST' with
tom@871:            -- 'plausibel', 'nicht plausibel' or 'nicht repräsentativ'
tom@871:            INSERT INTO status_reihenfolge (von_id, zu_id)
tom@871:                   VALUES (kombi_from.id, kombi_to.id);
tom@871: 
tom@871:         END IF;
tom@871:     END LOOP;
tom@871: END LOOP;
tom@871: END;
tom@871: $$ LANGUAGE plpgsql;
tom@871: 
tom@871: SELECT populate_status_reihenfolge();
tom@871: DROP FUNCTION populate_status_reihenfolge();
tom@871: ALTER TABLE status_reihenfolge ALTER COLUMN id DROP DEFAULT;
tom@871: DROP SEQUENCE status_reihenfolge_id_seq;
tom@871: 
tom@871: 
tom@870: CREATE VIEW status_erreichbar AS (
tom@879:     SELECT r.id,
tom@879:            zu.wert_id,
tom@879:            zu.stufe_id,
tom@879:            von.wert_id AS cur_wert,
tom@879:            von.stufe_id AS cur_stufe
tom@879:     FROM stammdaten.status_reihenfolge r
tom@879:         JOIN stammdaten.status_kombi von
tom@879:             ON von.id = r.von_id
tom@879:         JOIN stammdaten.status_kombi zu
tom@879:             ON zu.id = r.zu_id
tom@870: );
tom@870: -- Status workflow
tom@870: 
tom@866: 
tom@866: CREATE TABLE umwelt (
tom@868:     id character varying(3) PRIMARY KEY,
tom@866:     beschreibung character varying(300),
tom@866:     umwelt_bereich character varying(80) NOT NULL,
tom@866:     meh_id integer
tom@866: );
tom@866: 
tom@866: 
tom@866: 
tom@866: 
tom@866: ALTER TABLE ONLY lada_user
tom@866:     ADD CONSTRAINT lada_user_name_key UNIQUE (name);
tom@866: 
tom@866: 
tom@866: ALTER TABLE ONLY mg_grp
tom@866:     ADD CONSTRAINT mg_grp_pkey PRIMARY KEY (messgroessengruppe_id, messgroesse_id);
tom@866: 
tom@866: 
tom@866: ALTER TABLE ONLY mmt_messgroesse_grp
tom@866:     ADD CONSTRAINT mmt_messgroesse_grp_pkey PRIMARY KEY (messgroessengruppe_id, mmt_id);
tom@866: 
tom@866: 
tom@866: ALTER TABLE ONLY proben_zusatz
tom@866:     ADD CONSTRAINT proben_zusatz_eudf_keyword_key UNIQUE (eudf_keyword);
tom@866: 
tom@866: 
tom@866: ALTER TABLE ONLY umwelt
tom@866:     ADD CONSTRAINT umwelt_umwelt_bereich_key UNIQUE (umwelt_bereich);
tom@866: 
tom@866: 
tom@866: CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id);
tom@866: 
tom@866: 
tom@866: 
tom@866: ALTER TABLE ONLY auth
tom@866:     ADD CONSTRAINT auth_funktion_fkey FOREIGN KEY (funktion_id) REFERENCES auth_funktion(id);
tom@866: 
tom@866: 
tom@866: 
tom@866: ALTER TABLE ONLY auth
tom@866:     ADD CONSTRAINT auth_labor_fkey FOREIGN KEY (labor_mst_id) REFERENCES mess_stelle(id);
tom@866: 
tom@866: 
tom@866: 
tom@866: ALTER TABLE ONLY auth_lst_umw
tom@866:     ADD CONSTRAINT auth_lst_umw_lst_fkey FOREIGN KEY (lst_id) REFERENCES mess_stelle(id);
tom@866: 
tom@866: 
tom@866: 
tom@866: ALTER TABLE ONLY auth_lst_umw
tom@866:     ADD CONSTRAINT auth_lst_umw_umw_fkey FOREIGN KEY (umw_id) REFERENCES umwelt(id);
tom@866: 
tom@866: 
tom@866: 
tom@866: ALTER TABLE ONLY auth
tom@866:     ADD CONSTRAINT auth_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES mess_stelle(id);
tom@866: 
tom@866: 
tom@866: 
tom@866: ALTER TABLE ONLY auth
tom@866:     ADD CONSTRAINT auth_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
tom@866: 
tom@866: 
tom@866: 
tom@866: ALTER TABLE ONLY deskriptoren
tom@866:     ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id);
tom@866: 
tom@866: 
tom@866: 
tom@866: ALTER TABLE ONLY pflicht_messgroesse
tom@866:     ADD CONSTRAINT pflicht_messgroesse_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES datenbasis(id);
tom@866: 
tom@866: 
tom@866: 
tom@866: ALTER TABLE ONLY pflicht_messgroesse
tom@866:     ADD CONSTRAINT pflicht_messgroesse_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES mess_methode(id);
tom@866: 
tom@866: 
tom@866: 
tom@866: ALTER TABLE ONLY pflicht_messgroesse
tom@866:     ADD CONSTRAINT pflicht_messgroesse_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES umwelt(id);
tom@866: 
tom@866: 
tom@866: 
tom@866: ALTER TABLE ONLY proben_zusatz
tom@866:     ADD CONSTRAINT proben_zusatz_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id);
tom@866: 
tom@866: 
tom@866: 
tom@866: ALTER TABLE ONLY staat
tom@866:     ADD CONSTRAINT staat_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
tom@866: 
tom@866: 
tom@866: 
tom@866: 
tom@866: 
tom@866: ALTER TABLE ONLY umwelt
tom@866:     ADD CONSTRAINT umwelt_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id);
tom@866: 
tom@866: 
tom@866: 
tom@866: ALTER TABLE ONLY verwaltungseinheit
tom@866:     ADD CONSTRAINT verwaltungseinheit_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
tom@866: 
tom@866: 
tom@866: COMMIT;