tom@797: \set ON_ERROR_STOP on tom@797: tom@797: BEGIN; tom@797: tom@741: tom@741: SET statement_timeout = 0; tom@741: SET lock_timeout = 0; tom@741: SET client_encoding = 'UTF8'; tom@741: SET standard_conforming_strings = on; tom@741: SET check_function_bodies = false; tom@741: SET client_min_messages = warning; tom@741: tom@741: -- tom@741: -- Name: land; Type: SCHEMA; Schema: -; Owner: - tom@741: -- tom@741: tom@741: CREATE SCHEMA land; tom@741: tom@1097: SET search_path = land, pg_catalog; raimund@751: tom@741: 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: $$; raimund@926: tom@741: tom@741: -- tom@1097: -- Name: update_time_status(); Type: FUNCTION; Schema: land; Owner: - tom@741: -- tom@741: tom@1097: CREATE FUNCTION update_tree_modified() RETURNS trigger tom@741: LANGUAGE plpgsql tom@741: AS $$ tom@741: BEGIN tom@741: NEW.tree_modified = now(); tom@741: RETURN NEW; tom@741: END; tom@741: $$; tom@741: tom@741: tom@741: -- tom@1097: -- Name: update_time_messung(); Type: FUNCTION; Schema: land; Owner: - tom@741: -- tom@741: tom@1097: CREATE FUNCTION update_tree_modified_messung() RETURNS trigger tom@741: LANGUAGE plpgsql tom@741: AS $$ tom@741: BEGIN tom@1097: RAISE NOTICE 'messung is %',NEW.id; tom@741: NEW.tree_modified = now(); tom@1097: UPDATE land.messwert SET tree_modified = now() WHERE messungs_id = NEW.id; tom@1097: UPDATE land.status_protokoll SET tree_modified = now() WHERE messungs_id = NEW.id; tom@741: RETURN NEW; tom@741: END; tom@741: $$; tom@741: tom@741: tom@741: -- tom@741: -- Name: update_time_probe(); Type: FUNCTION; Schema: land; Owner: - tom@741: -- tom@741: tom@1097: CREATE FUNCTION update_tree_modified_probe() RETURNS trigger tom@741: LANGUAGE plpgsql tom@741: AS $$ tom@741: BEGIN tom@741: RAISE NOTICE 'probe is %',NEW.id; tom@741: NEW.tree_modified = now(); tom@741: RAISE NOTICE 'updating other rows'; tom@741: UPDATE land.messung SET tree_modified = now() WHERE probe_id = NEW.id; raimund@824: UPDATE land.ortszuordnung SET tree_modified = now() WHERE probe_id = NEW.id; tom@741: UPDATE land.zusatz_wert SET tree_modified = now() WHERE probe_id = NEW.id; tom@741: RETURN NEW; tom@741: END; tom@741: $$; tom@741: tom@741: tom@741: SET default_tablespace = ''; tom@741: tom@741: SET default_with_oids = false; tom@741: tom@741: tom@741: -- tom@1097: -- Name: messung_messung_id_alt_seq; Type: SEQUENCE; Schema: land; Owner: - tom@741: -- tom@741: tom@741: CREATE SEQUENCE messung_messung_id_alt_seq tom@741: START WITH 1 tom@741: INCREMENT BY 1 tom@741: NO MINVALUE tom@741: NO MAXVALUE tom@741: CACHE 1; tom@741: tom@741: tom@741: -- tom@1097: -- Name: probe_probe_id_seq; Type: SEQUENCE; Schema: land; Owner: - tom@741: -- tom@741: tom@741: CREATE SEQUENCE probe_probe_id_seq tom@741: START WITH 1 tom@741: INCREMENT BY 1 tom@741: NO MINVALUE tom@741: NO MAXVALUE tom@741: CACHE 1; tom@741: tom@741: tom@741: -- tom@900: -- Name: messprogramm; Type: TABLE; Schema: land; Owner: -; Tablespace: tom@900: -- tom@900: tom@900: CREATE TABLE messprogramm ( tom@1097: id serial PRIMARY KEY, tom@1097: kommentar character varying(1000), tom@900: test boolean DEFAULT false NOT NULL, tom@900: mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle, tom@1097: labor_mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle, tom@900: datenbasis_id integer NOT NULL REFERENCES stammdaten.datenbasis, tom@1120: ba_id integer DEFAULT 1 REFERENCES stammdaten.betriebsart, tom@1097: gem_id character varying(8) REFERENCES stammdaten.verwaltungseinheit, tom@989: ort_id integer NOT NULL REFERENCES stammdaten.ort, tom@1122: media_desk character varying(100) CHECK(media_desk LIKE '% %'), tom@900: umw_id character varying(3) REFERENCES stammdaten.umwelt, tom@900: probenart_id integer NOT NULL REFERENCES stammdaten.probenart, tom@989: probenintervall character varying(2) NOT NULL, tom@989: teilintervall_von integer NOT NULL, tom@989: teilintervall_bis integer NOT NULL, tom@1059: intervall_offset integer NOT NULL DEFAULT 0, tom@1077: gueltig_von integer NOT NULL CHECK(gueltig_von BETWEEN 1 AND 365), tom@1077: gueltig_bis integer NOT NULL CHECK(gueltig_bis BETWEEN 1 AND 365), tom@1097: probe_nehmer_id integer REFERENCES stammdaten.probenehmer, tom@900: probe_kommentar character varying(80), tom@1044: letzte_aenderung timestamp without time zone DEFAULT now() NOT NULL, tom@1063: CHECK (probenintervall = 'J' tom@1082: AND teilintervall_von BETWEEN gueltig_von AND gueltig_bis tom@1082: AND teilintervall_bis BETWEEN gueltig_von AND gueltig_bis tom@1063: AND intervall_offset BETWEEN 0 AND 364 tom@1063: OR probenintervall = 'H' tom@1065: AND teilintervall_von BETWEEN 1 AND 184 tom@1065: AND teilintervall_bis BETWEEN 1 AND 184 tom@1065: AND intervall_offset BETWEEN 0 AND 183 tom@1063: OR probenintervall = 'Q' tom@1065: AND teilintervall_von BETWEEN 1 AND 92 tom@1065: AND teilintervall_bis BETWEEN 1 AND 92 tom@1065: AND intervall_offset BETWEEN 0 AND 91 tom@1063: OR probenintervall = 'M' tom@1063: AND teilintervall_von BETWEEN 1 AND 31 tom@1063: AND teilintervall_bis BETWEEN 1 AND 31 tom@1063: AND intervall_offset BETWEEN 0 AND 30 tom@1063: OR probenintervall = 'W4' tom@1063: AND teilintervall_von BETWEEN 1 AND 28 tom@1063: AND teilintervall_bis BETWEEN 1 AND 28 tom@1063: AND intervall_offset BETWEEN 0 AND 27 tom@1063: OR probenintervall = 'W2' tom@1063: AND teilintervall_von BETWEEN 1 AND 14 tom@1063: AND teilintervall_bis BETWEEN 1 AND 14 tom@1063: AND intervall_offset BETWEEN 0 AND 13 tom@1063: OR probenintervall = 'W' tom@1063: AND teilintervall_von BETWEEN 1 AND 7 tom@1063: AND teilintervall_bis BETWEEN 1 AND 7 tom@1063: AND intervall_offset BETWEEN 0 AND 6 tom@1063: OR probenintervall = 'T' tom@1063: AND teilintervall_von = 1 tom@1063: AND teilintervall_bis = 1 tom@1063: AND intervall_offset = 0 tom@1063: ), tom@1054: CHECK (teilintervall_von <= teilintervall_bis) tom@900: ); raimund@926: CREATE TRIGGER letzte_aenderung_messprogramm BEFORE UPDATE ON messprogramm FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); tom@900: tom@900: tom@900: -- tom@799: -- Name: messprogramm_mmt; Type: TABLE; Schema: land; Owner: -; Tablespace: tom@741: -- tom@741: tom@741: CREATE TABLE messprogramm_mmt ( tom@1097: id serial PRIMARY KEY, tom@1097: messprogramm_id integer NOT NULL REFERENCES messprogramm ON DELETE CASCADE, tom@1097: mmt_id character varying(2) NOT NULL REFERENCES stammdaten.mess_methode, tom@741: messgroessen integer[], tom@741: letzte_aenderung timestamp without time zone DEFAULT now() tom@741: ); raimund@926: CREATE TRIGGER letzte_aenderung_messprogramm_mmt BEFORE UPDATE ON messprogramm_mmt FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); tom@741: tom@741: tom@741: -- tom@1097: -- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace: tom@741: -- tom@741: tom@1097: CREATE TABLE probe ( tom@1097: id serial PRIMARY KEY, tom@1129: id_alt character varying(16) UNIQUE NOT NULL tom@1126: DEFAULT 'sss' tom@1126: || lpad(nextval('land.probe_probe_id_seq')::varchar, 12, '0') tom@1126: || 'Y', tom@1097: test boolean DEFAULT false NOT NULL, tom@1097: mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle, tom@1097: labor_mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle, tom@1097: hauptproben_nr character varying(20), tom@1097: datenbasis_id smallint REFERENCES stammdaten.datenbasis, tom@1097: ba_id integer REFERENCES stammdaten.betriebsart, tom@1097: probenart_id smallint NOT NULL REFERENCES stammdaten.probenart, tom@1122: media_desk character varying(100) CHECK(media_desk LIKE '% %'), tom@1097: media character varying(100), tom@1097: umw_id character varying(3) REFERENCES stammdaten.umwelt, tom@1097: probeentnahme_beginn timestamp with time zone, tom@1097: probeentnahme_ende timestamp with time zone, tom@1097: mittelungsdauer bigint, tom@1097: letzte_aenderung timestamp without time zone DEFAULT now(), tom@1097: erzeuger_id integer REFERENCES stammdaten.datensatz_erzeuger, tom@1097: probe_nehmer_id integer REFERENCES stammdaten.probenehmer, tom@1097: mpl_id integer REFERENCES stammdaten.messprogramm_kategorie, tom@1097: mpr_id integer REFERENCES messprogramm, tom@1097: solldatum_beginn timestamp without time zone, tom@1097: solldatum_ende timestamp without time zone, tom@1097: tree_modified timestamp without time zone DEFAULT now(), tom@1126: UNIQUE (mst_id, hauptproben_nr), tom@1097: CHECK(solldatum_beginn <= solldatum_ende) tom@1097: ); tom@1097: CREATE TRIGGER letzte_aenderung_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); tom@1097: CREATE TRIGGER tree_modified_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_tree_modified_probe(); tom@741: tom@741: tom@741: -- tom@1097: -- Name: kommentar_p; Type: TABLE; Schema: land; Owner: -; Tablespace: tom@741: -- tom@741: tom@1097: CREATE TABLE kommentar_p ( tom@1097: id serial PRIMARY KEY, tom@1097: mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle, tom@1097: datum timestamp without time zone DEFAULT now(), tom@1097: text character varying(1024), tom@1097: probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE tom@1097: ); tom@741: tom@741: tom@741: -- raimund@808: -- Name: ortszuordnung; Type: TABLE; Schema: land; Owner: -; Tablespace: raimund@808: -- raimund@808: raimund@808: CREATE TABLE ortszuordnung ( tom@1097: id serial PRIMARY KEY, tom@1097: probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE, tom@1097: ort_id bigint NOT NULL REFERENCES stammdaten.ort, tom@1097: ortszuordnung_typ character varying(1) REFERENCES stammdaten.ortszuordnung_typ, tom@1097: ortszusatztext character varying(100), tom@1097: letzte_aenderung timestamp without time zone DEFAULT now(), tom@741: tree_modified timestamp without time zone DEFAULT now() tom@741: ); tom@1097: CREATE TRIGGER letzte_aenderung_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); tom@1097: CREATE TRIGGER tree_modified_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_tree_modified(); tom@741: tom@741: tom@741: -- tom@799: -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace: tom@741: -- tom@741: tom@741: CREATE TABLE zusatz_wert ( tom@1097: id serial PRIMARY KEY, tom@1097: probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE, tom@1097: pzs_id character varying(3) NOT NULL REFERENCES stammdaten.proben_zusatz, tom@1097: messwert_pzs double precision, tom@1097: messfehler real, tom@1097: letzte_aenderung timestamp without time zone DEFAULT now(), tom@1097: nwg_zu_messwert double precision, tom@1097: tree_modified timestamp without time zone DEFAULT now(), tom@1097: UNIQUE (probe_id, pzs_id) tom@1097: ); tom@1097: CREATE TRIGGER letzte_aenderung_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); tom@1097: CREATE TRIGGER tree_modified_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_tree_modified(); tom@741: tom@741: tom@741: -- tom@1097: -- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace: tom@741: -- tom@741: tom@1097: CREATE TABLE messung ( tom@1097: id serial PRIMARY KEY, tom@1097: id_alt integer DEFAULT nextval('land.messung_messung_id_alt_seq'::regclass) NOT NULL, tom@1097: probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE, tom@1097: nebenproben_nr character varying(10), tom@1097: mmt_id character varying(2) NOT NULL REFERENCES stammdaten.mess_methode ON DELETE CASCADE, tom@1097: messdauer integer, tom@1097: messzeitpunkt timestamp with time zone, tom@1097: fertig boolean DEFAULT false NOT NULL, tom@1097: status integer, tom@1097: letzte_aenderung timestamp without time zone DEFAULT now(), tom@1097: geplant boolean DEFAULT false NOT NULL, tom@1097: tree_modified timestamp without time zone DEFAULT now() tom@1097: ); tom@1097: CREATE TRIGGER letzte_aenderung_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); tom@1097: CREATE TRIGGER tree_modified_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_tree_modified_messung(); tom@741: tom@741: tom@741: -- tom@1097: -- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace: tom@741: -- tom@741: tom@1097: CREATE TABLE kommentar_m ( tom@1097: id serial PRIMARY KEY, tom@1097: mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle, tom@1097: datum timestamp without time zone DEFAULT now(), tom@1097: text character varying(1024), tom@1097: messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE tom@1097: ); tom@741: tom@741: tom@741: -- tom@1097: -- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace: tom@741: -- tom@741: tom@1097: CREATE TABLE messwert ( tom@1097: id serial PRIMARY KEY, tom@1097: messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE, tom@1097: messgroesse_id integer NOT NULL REFERENCES stammdaten.messgroesse, tom@1097: messwert_nwg character varying(1), tom@1097: messwert double precision NOT NULL, tom@1097: messfehler real, tom@1097: nwg_zu_messwert double precision, tom@1097: meh_id smallint NOT NULL REFERENCES stammdaten.mess_einheit, tom@1097: grenzwertueberschreitung boolean DEFAULT false, tom@1097: letzte_aenderung timestamp without time zone DEFAULT now(), tom@1097: tree_modified timestamp without time zone DEFAULT now(), tom@1097: UNIQUE (messungs_id, messgroesse_id) tom@1097: ); tom@1097: CREATE TRIGGER letzte_aenderung_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); tom@1097: CREATE TRIGGER tree_modified_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_tree_modified(); tom@741: tom@741: tom@741: -- tom@1097: -- Name: status_protokoll; Type: TABLE; Schema: land; Owner: -; Tablespace: tom@741: -- tom@741: tom@1097: CREATE TABLE status_protokoll ( tom@1097: id serial PRIMARY KEY, tom@1097: mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle, tom@1097: datum timestamp without time zone DEFAULT now(), tom@1097: text character varying(1024), tom@1097: messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE, tom@1097: status_kombi integer NOT NULL REFERENCES stammdaten.status_kombi, tom@1097: tree_modified timestamp without time zone DEFAULT now() tom@1097: ); tom@1097: CREATE TRIGGER tree_modified_status_protokoll BEFORE UPDATE ON status_protokoll FOR EACH ROW EXECUTE PROCEDURE update_tree_modified(); tom@741: tom@1097: ALTER TABLE ONLY messung tom@1097: ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id); tom@741: tom@741: tom@741: -- tom@799: -- Name: messung_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: tom@741: -- tom@741: tom@741: CREATE INDEX messung_probe_id_idx ON messung USING btree (probe_id); tom@741: tom@741: tom@741: -- tom@1097: -- Name: ort_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: tom@741: -- tom@741: tom@1097: CREATE INDEX ort_probe_id_idx ON ortszuordnung USING btree (probe_id); tom@1097: tom@1097: tom@1097: -- tom@1097: -- Name: zusatz_wert_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: tom@1097: -- tom@1097: tom@1097: CREATE INDEX zusatz_wert_probe_id_idx ON zusatz_wert USING btree (probe_id); tom@1097: tom@1097: tom@1097: -- tom@1097: -- Name: kommentar_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: tom@1097: -- tom@1097: tom@1097: CREATE INDEX kommentar_probe_id_idx ON kommentar_p USING btree (probe_id); tom@741: tom@741: tom@741: -- tom@799: -- Name: messwert_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: tom@741: -- tom@741: tom@741: CREATE INDEX messwert_messungs_id_idx ON messwert USING btree (messungs_id); tom@741: tom@741: tom@741: -- tom@1097: -- Name: status_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: tom@741: -- tom@741: tom@1097: CREATE INDEX status_messungs_id_idx ON status_protokoll USING btree (messungs_id); raimund@751: raimund@751: raimund@751: -- tom@1097: -- Name: kommentar_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: tom@741: -- tom@741: tom@1097: CREATE INDEX kommentar_messungs_id_idx ON kommentar_m USING btree (messungs_id); raimund@751: raimund@751: raimund@751: -- tom@1097: -- Name: COLUMN ortszuordnung.ortszuordnung_typ; Type: COMMENT; Schema: land; Owner: - tom@741: -- tom@741: tom@1097: COMMENT ON COLUMN ortszuordnung.ortszuordnung_typ IS 'E = Entnahmeport, U = Ursprungsort, Z = Ortszusatz'; raimund@808: raimund@808: raimund@808: -- tom@1097: -- Name: COLUMN probe.id; Type: COMMENT; Schema: land; Owner: - raimund@808: -- raimund@808: tom@1097: COMMENT ON COLUMN probe.id IS 'interner Probenschlüssel'; tom@741: tom@741: tom@741: -- tom@1097: -- Name: COLUMN probe.test; Type: COMMENT; Schema: land; Owner: - raimund@751: -- raimund@751: tom@1097: COMMENT ON COLUMN probe.test IS 'Ist Testdatensatz?'; tom@741: tom@741: tom@741: -- tom@1097: -- Name: COLUMN probe.mst_id; Type: COMMENT; Schema: land; Owner: - tom@741: -- tom@741: tom@1097: COMMENT ON COLUMN probe.mst_id IS 'ID für Messstelle'; tom@741: tom@741: tom@741: -- tom@1097: -- Name: COLUMN probe.labor_mst_id; Type: COMMENT; Schema: land; Owner: - tom@741: -- tom@741: tom@1097: COMMENT ON COLUMN probe.labor_mst_id IS '-- ID für Messlabor'; tom@1097: tom@1097: tom@1097: -- tom@1097: -- Name: COLUMN probe.hauptproben_nr; Type: COMMENT; Schema: land; Owner: - tom@1097: -- tom@1097: tom@1097: COMMENT ON COLUMN probe.hauptproben_nr IS 'externer Probensclüssel'; tom@1097: tom@1097: tom@1097: -- tom@1097: -- Name: COLUMN probe.ba_id; Type: COMMENT; Schema: land; Owner: - tom@1097: -- tom@1097: tom@1097: COMMENT ON COLUMN probe.ba_id IS 'ID der Betriebsart (normal/Routine oder Störfall/intensiv)'; tom@1097: tom@1097: tom@1097: -- tom@1097: -- Name: COLUMN probe.probenart_id; Type: COMMENT; Schema: land; Owner: - tom@1097: -- tom@1097: tom@1097: COMMENT ON COLUMN probe.probenart_id IS 'ID der Probenart(Einzel-, Sammel-, Misch- ...Probe)'; tom@1097: tom@1097: tom@1097: -- tom@1097: -- Name: COLUMN probe.media_desk; Type: COMMENT; Schema: land; Owner: - tom@1097: -- tom@1097: tom@1097: COMMENT ON COLUMN probe.media_desk IS 'Mediencodierung (Deskriptoren oder ADV-Codierung)'; tom@1097: tom@1097: tom@1097: -- tom@1097: -- Name: COLUMN probe.media; Type: COMMENT; Schema: land; Owner: - tom@1097: -- tom@1097: tom@1097: COMMENT ON COLUMN probe.media IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)'; tom@1097: tom@1097: tom@1097: -- tom@1097: -- Name: COLUMN probe.umw_id; Type: COMMENT; Schema: land; Owner: - tom@1097: -- tom@1097: tom@1097: COMMENT ON COLUMN probe.umw_id IS 'ID für Umweltbereich'; tom@1097: tom@1097: tom@1097: -- tom@1097: -- Name: COLUMN messprogramm.media_desk; Type: COMMENT; Schema: land; Owner: - tom@1097: -- tom@1097: tom@1097: COMMENT ON COLUMN messprogramm.media_desk IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)'; tom@741: tom@741: tom@797: COMMIT;