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;