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@1175: CREATE FUNCTION set_ort_id() RETURNS trigger
raimund@1175:     LANGUAGE plpgsql
raimund@1175:     AS $$
raimund@1179:     DECLARE value text;
raimund@1175:     BEGIN
raimund@1175:         value = '#'::text || lpad((NEW.id::character varying)::text, 9, '0'::text);
raimund@1175:         IF NEW.ort_id IS NULL THEN
raimund@1175:             NEW.ort_id = value;
raimund@1175:         END IF;
raimund@1175:         IF NEW.langtext IS NULL THEN
raimund@1175:             NEW.langtext = value;
raimund@1175:         END IF;
raimund@1175:         IF NEW.kurztext IS NULL THEN
raimund@1175:             NEW.kurztext = value;
raimund@1175:         END IF;
raimund@1175:         RETURN NEW;
raimund@1175:     END;
raimund@1175: $$;
raimund@1175: 
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@1097: CREATE TABLE koordinaten_art (
tom@1097:     id serial PRIMARY KEY,
tom@1097:     koordinatenart character varying(50),
tom@1097:     idf_geo_key character varying(1)
tom@1097: );
tom@1097: 
tom@1097: 
tom@1097: CREATE TABLE mess_einheit (
tom@1097:     id serial PRIMARY KEY,
tom@1097:     beschreibung character varying(50),
tom@1097:     einheit character varying(12),
tom@1097:     eudf_messeinheit_id character varying(8),
tom@1097:     umrechnungs_faktor_eudf bigint
tom@1097: );
tom@1097: 
tom@1097: 
tom@1097: CREATE TABLE umwelt (
tom@1097:     id character varying(3) PRIMARY KEY,
tom@1097:     beschreibung character varying(300),
tom@1097:     umwelt_bereich character varying(80) NOT NULL,
tom@1097:     meh_id integer REFERENCES mess_einheit,
tom@1097:     UNIQUE (umwelt_bereich)
tom@1097: );
tom@1097: 
tom@1097: 
tom@1097: CREATE TABLE betriebsart (
tom@1119:     id smallint PRIMARY KEY,
tom@1097:     name character varying(30) NOT NULL
tom@1097: );
tom@1119: INSERT INTO betriebsart VALUES(1, 'Normal-/Routinebetrieb');
tom@1119: INSERT INTO betriebsart VALUES(2, 'Störfall-/Intensivbetrieb');
tom@1097: 
tom@902: 
tom@902: CREATE TABLE staat (
tom@1097:     id serial PRIMARY KEY,
tom@1223:     staat character varying(50) NOT NULL UNIQUE,
tom@1223:     hkl_id smallint NOT NULL UNIQUE,
tom@1223:     staat_iso character varying(2) UNIQUE,
tom@1223:     staat_kurz character varying(5) UNIQUE,
tom@1222:     eu boolean,
tom@902:     koord_x_extern character varying(22),
tom@902:     koord_y_extern character varying(22),
tom@1097:     kda_id integer REFERENCES koordinaten_art
tom@902: );
tom@902: 
tom@902: CREATE TABLE verwaltungseinheit (
mstanko@1188:     id character varying(8) NOT NULL PRIMARY KEY,
tom@902:     bezeichnung character varying(80) NOT NULL,
mstanko@1188:     regbezirk character varying(8),
mstanko@1188:     kreis character varying(8),
mstanko@1188:     bundesland character varying(8) NOT NULL,
mstanko@1188:     is_gemeinde boolean DEFAULT false NOT NULL,
mstanko@1188:     is_landkreis boolean DEFAULT false NOT NULL,
mstanko@1188:     is_regbezirk boolean DEFAULT false NOT NULL,
mstanko@1188:     is_bundesland boolean DEFAULT false NOT NULL,
tom@902:     plz character varying(6),
mstanko@1188:     nuts character varying(10),
mstanko@1188:     mittelpunkt public.geometry(Point)
tom@902: );
tom@902: 
mstanko@1188: CREATE TABLE verwaltungsgrenze (
mstanko@1188:     id serial PRIMARY KEY,
tom@1208:     gem_id character varying(8) NOT NULL REFERENCES verwaltungseinheit,
tom@1249:     shape public.geometry(MultiPolygon, 4326) NOT NULL
mstanko@1188: );
tom@1209: CREATE INDEX verwaltungsgrenze_sp_idx ON verwaltungsgrenze USING gist (shape);
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@1097:     aktiv boolean DEFAULT false
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@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@1097: CREATE TABLE auth (
tom@1097:     id serial PRIMARY KEY,
tom@1097:     ldap_group character varying(40) NOT NULL,
tom@1097:     netzbetreiber_id character varying(2) REFERENCES netz_betreiber,
tom@1097:     mst_id character varying(5) REFERENCES mess_stelle,
tom@1097:     labor_mst_id character varying(5) REFERENCES mess_stelle,
tom@1097:     funktion_id smallint REFERENCES auth_funktion
tom@1097: );
tom@1097: 
tom@866: 
tom@868: CREATE TABLE auth_lst_umw (
tom@1097:     id serial PRIMARY KEY,
tom@1097:     mst_id character varying(5) REFERENCES mess_stelle,
tom@1097:     umw_id character varying(3) REFERENCES umwelt
tom@868: );
tom@866: 
tom@866: 
tom@868: CREATE TABLE datenbasis (
tom@1097:     id serial PRIMARY KEY,
tom@868:     beschreibung character varying(30),
tom@868:     datenbasis character varying(6)
tom@868: );
tom@866: 
tom@866: 
tom@868: CREATE TABLE datensatz_erzeuger (
tom@1097:     id serial PRIMARY KEY,
tom@904:     netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
tom@1097:     datensatz_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@1097:     UNIQUE(datensatz_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: 
tom@866: CREATE TABLE deskriptor_umwelt (
tom@1097:     id serial 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@1097:     umw_id character varying(3) NOT NULL REFERENCES umwelt
tom@866: );
tom@866: 
tom@866: 
tom@866: CREATE TABLE deskriptoren (
tom@1097:     id serial PRIMARY KEY,
tom@1097:     vorgaenger integer REFERENCES deskriptoren,
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@954: 
tom@954: CREATE TABLE lada_user (
tom@1097:     id serial PRIMARY KEY,
tom@1097:     name character varying(80) NOT NULL,
tom@1097:     UNIQUE (name)
tom@954: );
tom@954: 
tom@1097: 
tom@1097: CREATE TABLE query_type (
tom@1097:     id serial PRIMARY KEY,
tom@1097:     type character varying(30) NOT NULL
tom@1097: );
tom@1097: INSERT INTO query_type VALUES(0, 'probe');
tom@1097: INSERT INTO query_type VALUES(1, 'messung');
tom@1097: INSERT INTO query_type VALUES(2, 'messprogramm');
tom@1097: INSERT INTO query_type VALUES(3, 'ort');
tom@1097: INSERT INTO query_type VALUES(4, 'probenehmer');
tom@1097: INSERT INTO query_type VALUES(5, 'datensatzerzeuger');
tom@1097: INSERT INTO query_type VALUES(6, 'messprogrammkategorie');
tom@954: 
tom@954: 
tom@954: CREATE TABLE query (
tom@1097:     id serial PRIMARY KEY,
tom@954:     name character varying(80) NOT NULL,
tom@1097:     type integer NOT NULL REFERENCES query_type,
tom@1097:     sql character varying(2500) NOT NULL,
tom@1093:     description character varying(100),
tom@1093:     UNIQUE (name, type)
tom@954: );
tom@954: 
tom@866: 
tom@868: CREATE TABLE favorite (
tom@1097:     id serial PRIMARY KEY,
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@1097: 
tom@1097: CREATE TABLE filter_type (
tom@1097:     id serial PRIMARY KEY,
tom@1097:     type character varying(10) NOT NULL
tom@1097: );
tom@1097: INSERT INTO filter_type VALUES(0, 'text');
tom@1097: INSERT INTO filter_type VALUES(1, 'listmst');
tom@1097: INSERT INTO filter_type VALUES(2, 'listnetz');
tom@1097: INSERT INTO filter_type VALUES(3, 'listumw');
tom@1097: INSERT INTO filter_type VALUES(4, 'liststatus');
tom@1097: INSERT INTO filter_type VALUES(5, 'number');
tom@866: 
tom@866: 
tom@868: CREATE TABLE filter (
tom@1097:     id serial PRIMARY KEY,
tom@954:     query_id integer NOT NULL REFERENCES query ON DELETE CASCADE,
tom@868:     data_index character varying(50) NOT NULL,
tom@1097:     type integer NOT NULL REFERENCES filter_type,
tom@868:     label character varying(50) NOT NULL,
tom@868:     multiselect boolean
tom@868: );
tom@866: 
tom@866: 
tom@868: CREATE TABLE filter_value (
tom@1097:     id serial PRIMARY KEY,
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: 
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@866: CREATE TABLE messgroesse (
tom@1097:     id serial PRIMARY KEY,
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: 
tom@868: CREATE TABLE messgroessen_gruppe (
tom@1097:     id serial PRIMARY KEY,
tom@868:     bezeichnung character varying(80),
tom@868:     ist_leitnuklidgruppe character(1) DEFAULT NULL::bpchar
tom@868: );
tom@866: 
tom@866: 
tom@868: CREATE TABLE messprogramm_kategorie (
tom@1097:     id serial PRIMARY KEY,
tom@904:     netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
tom@1097:     code character varying(3) NOT NULL,
tom@904:     bezeichnung character varying(120) NOT NULL,
tom@905:     letzte_aenderung timestamp without time zone,
tom@1097:     UNIQUE(code, 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@866: 
tom@866: CREATE TABLE mg_grp (
tom@1097:     messgroessengruppe_id integer NOT NULL REFERENCES messgroessen_gruppe,
tom@1097:     messgroesse_id integer NOT NULL REFERENCES messgroesse
tom@866: );
tom@1097: ALTER TABLE ONLY mg_grp
tom@1097:     ADD CONSTRAINT mg_grp_pkey PRIMARY KEY (messgroessengruppe_id, messgroesse_id);
tom@866: 
tom@866: 
tom@866: CREATE TABLE mmt_messgroesse_grp (
tom@1097:     messgroessengruppe_id integer NOT NULL REFERENCES messgroessen_gruppe,
tom@1097:     mmt_id character varying(2) NOT NULL REFERENCES mess_methode
tom@866: );
tom@1097: ALTER TABLE ONLY mmt_messgroesse_grp
tom@1097:     ADD CONSTRAINT mmt_messgroesse_grp_pkey PRIMARY KEY (messgroessengruppe_id, mmt_id);
tom@1097: 
tom@1097: 
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@1097: CREATE TABLE kta (
tom@1097:   id serial NOT NULL,
tom@1097:   code character varying(7),
tom@1097:   bezeichnung character varying(80),
tom@1097:   CONSTRAINT kta_pkey PRIMARY KEY (id)
tom@1097: );
tom@1097: COMMENT ON TABLE kta
tom@1097:   IS 'kernteschnische Anlagen';
tom@866: 
tom@1097: CREATE TABLE ortszusatz (
raimund@1143:     ozs_id character varying(7) PRIMARY KEY,
raimund@1143:     ortszusatz character varying(80) NOT NULL
tom@1097: );
tom@866: 
tom@866: CREATE TABLE ort (
tom@1097:     id serial PRIMARY KEY,
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,
raimund@1176:     staat_id smallint 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@1213:     geom public.geometry(Point,4326) NOT NULL,
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,
raimund@1143:     oz_id character varying(7) REFERENCES ortszusatz(ozs_id),
tom@1097:     hoehe_ueber_nn real,
tom@905:     UNIQUE(ort_id, netzbetreiber_id)
tom@866: );
tom@1097: 
raimund@926: CREATE TRIGGER letzte_aenderung_ort BEFORE UPDATE ON ort FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
raimund@1175: CREATE TRIGGER set_ort_id_ort BEFORE INSERT ON ort FOR EACH ROW EXECUTE PROCEDURE set_ort_id();
tom@866: 
tom@902: ALTER TABLE ONLY ort
tom@1097:     ADD CONSTRAINT ort_kta_fkey FOREIGN KEY (anlage_id) REFERENCES kta(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@868: CREATE TABLE pflicht_messgroesse (
tom@1097:     id serial PRIMARY KEY,
tom@868:     messgroesse_id integer,
tom@1097:     mmt_id character varying(2) REFERENCES mess_methode,
tom@1097:     umw_id character varying(3) REFERENCES umwelt,
tom@1097:     datenbasis_id smallint NOT NULL REFERENCES datenbasis
tom@868: );
tom@866: 
tom@866: 
tom@866: CREATE TABLE proben_zusatz (
tom@868:     id character varying(3) PRIMARY KEY,
tom@1097:     meh_id integer REFERENCES mess_einheit,
tom@866:     beschreibung character varying(50) NOT NULL,
tom@866:     zusatzwert character varying(7) NOT NULL,
tom@1097:     eudf_keyword character varying(40),
tom@1097:     UNIQUE (eudf_keyword)
tom@866: );
tom@866: 
tom@866: 
tom@868: CREATE TABLE probenart (
tom@1097:     id serial PRIMARY KEY,
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: 
tom@866: CREATE TABLE probenehmer (
tom@1097:     id serial PRIMARY KEY,
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: 
tom@866: CREATE TABLE result (
tom@1097:     id serial PRIMARY KEY,
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: 
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: 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@1115: INSERT INTO status_kombi VALUES (14, 1, 8);
tom@1115: INSERT INTO status_kombi VALUES (15, 2, 8);
tom@1115: INSERT INTO status_kombi VALUES (16, 3, 8);
tom@870: 
tom@871: 
tom@870: CREATE TABLE status_reihenfolge (
tom@1097:     id serial PRIMARY KEY,
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@1115:         ELSEIF s_to = s_from + 1
tom@1115:                AND w_from <> 0 AND w_from <> 4
tom@1115:                AND w_from <> 8 AND w_to <> 8 THEN
tom@871:            -- Going to the next 'stufe' all available status_kombi are allowed
tom@1115:            -- in case current wert is not 'nicht vergeben', 'Rückfrage' or
tom@1115:            -- 'zurückgesetzt' and we are not trying to set 'zurückgesetzt'
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@1115:         ELSEIF w_to = 8 AND s_from = s_to THEN
tom@1115:            -- 'zurückgesetzt' can only be set on the same 'stufe'
tom@1115:            INSERT INTO status_reihenfolge (von_id, zu_id)
tom@1115:                   VALUES (kombi_from.id, kombi_to.id);
tom@1115: 
tom@1115:         ELSEIF w_from = 8 AND s_to = s_from - 1 THEN
tom@1115:            -- after 'zurückgesetzt' always follows the next lower 'stufe'
tom@1115:            INSERT INTO status_reihenfolge (von_id, zu_id)
tom@1115:                   VALUES (kombi_from.id, kombi_to.id);
tom@1115: 
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@1097: -- Mappings for import
tom@866: 
tom@1097: CREATE TABLE messprogramm_transfer (
tom@1097:     id serial PRIMARY KEY,
tom@1097:     messprogramm_s character varying(1) NOT NULL,
tom@1097:     messprogramm_c character varying(100) NOT NULL,
tom@1097:     ba_id integer NOT NULL REFERENCES betriebsart,
tom@1097:     UNIQUE (messprogramm_s)
tom@866: );
tom@866: 
tom@1097: -- Mappings for import
tom@866: 
tom@866: CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id);
tom@866: 
tom@866: 
tom@866: COMMIT;