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@1275: DECLARE id_value text; raimund@1175: BEGIN raimund@1275: IF NEW.gem_id IS NULL THEN raimund@1275: id_value = NEW.id; raimund@1275: ELSE raimund@1275: id_value = NEW.gem_id; raimund@1275: END IF; raimund@1275: value = '#'::text || lpad(id_value, 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, mstanko@1258: ort_typ character varying(60), mstanko@1258: code character varying(3) 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;