Mercurial > lada > lada-server

changeset 751:37a8f4d157c7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Updated lada schema: Added status workflow model and cleaned up some triggers.
author Raimund Renkert <raimund.renkert@intevation.de>
date Fri, 30 Oct 2015 15:06:22 +0100
parents 214508fd95e7
children 0cf9387c8da2
files db_schema/lada_schema.sql
diffstat 1 files changed, 157 insertions(+), 258 deletions(-) [+]
line wrap: on
line diff
--- a/db_schema/lada_schema.sql	Tue Oct 27 10:29:30 2015 +0100
+++ b/db_schema/lada_schema.sql	Fri Oct 30 15:06:22 2015 +0100
@@ -61,127 +61,15 @@
 SET search_path = bund, pg_catalog;
 
 --
--- Name: is_kommentar_unique(); Type: FUNCTION; Schema: bund; Owner: -
---
-
-CREATE FUNCTION is_kommentar_unique() RETURNS trigger
-    LANGUAGE plpgsql
-    AS $$
-    BEGIN
-        -- Check that empname and salary are given
-        IF NEW.id IN (SELECT id from bund.kommentar) THEN
-            RAISE EXCEPTION 'Key (id)=(%) already present in bund.messung', NEW.id;
-        ELSE
-            RETURN NEW;
-        END IF;
-    END;
-$$;
-
-
---
--- Name: is_messung_unique(); Type: FUNCTION; Schema: bund; Owner: -
---
-
-CREATE FUNCTION is_messung_unique() RETURNS trigger
-    LANGUAGE plpgsql
-    AS $$
-    BEGIN
-        -- Check that empname and salary are given
-        IF NEW.id IN (SELECT id from bund.messung) THEN
-            RAISE EXCEPTION 'Key (id)=(%) already present in bund.messung', NEW.id;
-        ELSE
-            RETURN NEW;
-        END IF;
-    END;
-$$;
-
-
---
--- Name: is_messwert_unique(); Type: FUNCTION; Schema: bund; Owner: -
---
-
-CREATE FUNCTION is_messwert_unique() RETURNS trigger
+-- Name: update_time_status(); Type: FUNCTION; Schema: bund; Owner: -
+--
+
+CREATE FUNCTION update_time_status() RETURNS trigger
     LANGUAGE plpgsql
     AS $$
     BEGIN
-        -- Check that empname and salary are given
-        IF NEW.id IN (SELECT id from bund.messwert) THEN
-            RAISE EXCEPTION 'Key (id)=(%) already present in bund.messwert', NEW.id;
-        ELSE
-            RETURN NEW;
-        END IF;
-    END;
-$$;
-
-
---
--- Name: is_ort_unique(); Type: FUNCTION; Schema: bund; Owner: -
---
-
-CREATE FUNCTION is_ort_unique() RETURNS trigger
-    LANGUAGE plpgsql
-    AS $$
-    BEGIN
-        -- Check that empname and salary are given
-        IF NEW.id IN (SELECT id from bund.ort) THEN
-            RAISE EXCEPTION 'Key (id)=(%) already present in bund.ort', NEW.id;
-        ELSE
-            RETURN NEW;
-        END IF;
-    END;
-$$;
-
-
---
--- Name: is_probe_unique(); Type: FUNCTION; Schema: bund; Owner: -
---
-
-CREATE FUNCTION is_probe_unique() RETURNS trigger
-    LANGUAGE plpgsql
-    AS $$
-    BEGIN
-        -- Check that empname and salary are given
-        IF NEW.id IN (SELECT id from bund.probe) THEN
-            RAISE EXCEPTION 'Key (id)=(%) already present in bund.probe', NEW.id;
-        ELSE
-            RETURN NEW;
-        END IF;
-    END;
-$$;
-
-
---
--- Name: is_status_unique(); Type: FUNCTION; Schema: bund; Owner: -
---
-
-CREATE FUNCTION is_status_unique() RETURNS trigger
-    LANGUAGE plpgsql
-    AS $$
-    BEGIN
-        -- Check that empname and salary are given
-        IF NEW.id IN (SELECT id from bund.status) THEN
-            RAISE EXCEPTION 'Key (id)=(%) already present in bund.status', NEW.id;
-        ELSE
-            RETURN NEW;
-        END IF;
-    END;
-$$;
-
-
---
--- Name: is_zusatz_wert_unique(); Type: FUNCTION; Schema: bund; Owner: -
---
-
-CREATE FUNCTION is_zusatz_wert_unique() RETURNS trigger
-    LANGUAGE plpgsql
-    AS $$
-    BEGIN
-        -- Check that empname and salary are given
-        IF NEW.id IN (SELECT id from bund.zusatz_wert) THEN
-            RAISE EXCEPTION 'Key (id)=(%) already present in bund.zusatz_wert', NEW.id;
-        ELSE
-            RETURN NEW;
-        END IF;
+        NEW.tree_modified = now();
+        RETURN NEW;
     END;
 $$;
 
@@ -214,7 +102,7 @@
         RAISE NOTICE 'messung is %',NEW.id;
         NEW.tree_modified = now();
         UPDATE land.messwert SET tree_modified = now() WHERE messungs_id = NEW.id;
-        UPDATE land.status SET tree_modified = now() WHERE messungs_id = NEW.id;
+        UPDATE bund.status_protokoll SET tree_modified = now() WHERE messungs_id = NEW.id;
         RETURN NEW;
     END;
 $$;
@@ -268,20 +156,6 @@
 
 
 --
--- Name: update_time_status(); Type: FUNCTION; Schema: land; Owner: -
---
-
-CREATE FUNCTION update_time_status() RETURNS trigger
-    LANGUAGE plpgsql
-    AS $$
-    BEGIN
-        NEW.tree_modified = now();
-        RETURN NEW;
-    END;
-$$;
-
-
---
 -- Name: update_time_zusatzwert(); Type: FUNCTION; Schema: land; Owner: -
 --
 
@@ -480,6 +354,7 @@
     messdauer integer,
     messzeitpunkt timestamp with time zone,
     fertig boolean DEFAULT false NOT NULL,
+    status integer,
     letzte_aenderung timestamp without time zone DEFAULT now()
 );
 
@@ -592,6 +467,7 @@
     test boolean DEFAULT false NOT NULL,
     netzbetreiber_id character varying(2),
     mst_id character varying(5),
+    labor_mst_id character varying(5),
     hauptproben_nr character varying(20),
     datenbasis_id smallint,
     ba_id character varying(1),
@@ -624,7 +500,14 @@
 -- Name: COLUMN probe.mst_id; Type: COMMENT; Schema: bund; Owner: -
 --
 
-COMMENT ON COLUMN probe.mst_id IS 'ID für Messstelle/Messlabor';
+COMMENT ON COLUMN probe.mst_id IS 'ID für Messstelle';
+
+
+--
+-- Name: COLUMN probe.labor_mst_id; Type: COMMENT; Schema: bund; Owner: -
+--
+
+COMMENT ON COLUMN probe.labor_mst_id IS '-- ID für Messlabor';
 
 
 --
@@ -682,29 +565,15 @@
 
 
 --
--- Name: status_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
---
-
-CREATE SEQUENCE status_id_seq
-    START WITH 1
-    INCREMENT BY 1
-    NO MINVALUE
-    NO MAXVALUE
-    CACHE 1;
-
-
---
--- Name: status; Type: TABLE; Schema: bund; Owner: -; Tablespace: 
---
-
-CREATE TABLE status (
-    id integer DEFAULT nextval('status_id_seq'::regclass) NOT NULL,
-    messungs_id integer NOT NULL,
-    erzeuger character varying(5) NOT NULL,
-    status smallint,
-    s_datum timestamp with time zone NOT NULL,
-    s_kommentar character varying(1024)
-);
+-- Name: status_protokoll; Type: TABLE; Schema: bund; Owner: -; Tablespace: 
+--
+
+CREATE TABLE status_protokoll (
+    status_stufe integer NOT NULL,
+    status_wert integer NOT NULL,
+    tree_modified timestamp without time zone DEFAULT now()
+)
+INHERITS (kommentar_m);
 
 
 --
@@ -950,13 +819,12 @@
 
 
 --
--- Name: status; Type: TABLE; Schema: land; Owner: -; Tablespace: 
---
-
-CREATE TABLE status (
-    tree_modified timestamp without time zone DEFAULT now()
+-- Name: status_protokoll; Type: TABLE; Schema: land; Owner: -; Tablespace: 
+--
+
+CREATE TABLE status_protokoll (
 )
-INHERITS (bund.status);
+INHERITS (bund.status_protokoll);
 
 
 --
@@ -1815,6 +1683,26 @@
 
 
 --
+-- Name: status_stufe; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+CREATE TABLE status_stufe (
+    id integer NOT NULL,
+    stufe character varying(50)
+);
+
+
+--
+-- Name: status_wert; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+CREATE TABLE status_wert (
+    id integer NOT NULL,
+    wert character varying(50)
+);
+
+
+--
 -- Name: umwelt; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: 
 --
 
@@ -1887,6 +1775,20 @@
 ALTER TABLE ONLY list ALTER COLUMN id SET DEFAULT nextval('list_id_seq'::regclass);
 
 
+--
+-- Name: id; Type: DEFAULT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY status_protokoll ALTER COLUMN id SET DEFAULT nextval('kommentar_id_seq'::regclass);
+
+
+--
+-- Name: datum; Type: DEFAULT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY status_protokoll ALTER COLUMN datum SET DEFAULT now();
+
+
 SET search_path = land, pg_catalog;
 
 --
@@ -2026,7 +1928,21 @@
 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
 --
 
-ALTER TABLE ONLY status ALTER COLUMN id SET DEFAULT nextval('bund.status_id_seq'::regclass);
+ALTER TABLE ONLY status_protokoll ALTER COLUMN id SET DEFAULT nextval('bund.kommentar_id_seq'::regclass);
+
+
+--
+-- Name: datum; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY status_protokoll ALTER COLUMN datum SET DEFAULT now();
+
+
+--
+-- Name: tree_modified; Type: DEFAULT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY status_protokoll ALTER COLUMN tree_modified SET DEFAULT now();
 
 
 --
@@ -2188,19 +2104,11 @@
 
 
 --
--- Name: status_messungs_id_key; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: 
---
-
-ALTER TABLE ONLY status
-    ADD CONSTRAINT status_messungs_id_key UNIQUE (messungs_id);
-
-
---
--- Name: status_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: 
---
-
-ALTER TABLE ONLY status
-    ADD CONSTRAINT status_pkey PRIMARY KEY (id);
+-- Name: status_protokoll_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY status_protokoll
+    ADD CONSTRAINT status_protokoll_pkey PRIMARY KEY (id);
 
 
 --
@@ -2326,19 +2234,11 @@
 
 
 --
--- Name: status_messungs_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 
---
-
-ALTER TABLE ONLY status
-    ADD CONSTRAINT status_messungs_id_key UNIQUE (messungs_id);
-
-
---
--- Name: status_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 
---
-
-ALTER TABLE ONLY status
-    ADD CONSTRAINT status_pkey PRIMARY KEY (id);
+-- Name: status_protokoll_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY status_protokoll
+    ADD CONSTRAINT status_protokoll_pkey PRIMARY KEY (id);
 
 
 --
@@ -2520,6 +2420,22 @@
 
 
 --
+-- Name: status_stufe_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY status_stufe
+    ADD CONSTRAINT status_stufe_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: status_wert_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY status_wert
+    ADD CONSTRAINT status_wert_pkey PRIMARY KEY (id);
+
+
+--
 -- Name: umwelt_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 
 --
 
@@ -2612,6 +2528,15 @@
 CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id);
 
 
+SET search_path = bund, pg_catalog;
+
+--
+-- Name: tree_timestamp_status; Type: TRIGGER; Schema: bund; Owner: -
+--
+
+CREATE TRIGGER tree_timestamp_status BEFORE UPDATE ON status_protokoll FOR EACH ROW EXECUTE PROCEDURE update_time_status();
+
+
 SET search_path = land, pg_catalog;
 
 --
@@ -2643,75 +2568,12 @@
 
 
 --
--- Name: tree_timestamp_status; Type: TRIGGER; Schema: land; Owner: -
---
-
-CREATE TRIGGER tree_timestamp_status BEFORE UPDATE ON status FOR EACH ROW EXECUTE PROCEDURE update_time_status();
-
-
---
 -- Name: tree_timestamp_zusatzwert; Type: TRIGGER; Schema: land; Owner: -
 --
 
 CREATE TRIGGER tree_timestamp_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_time_zusatzwert();
 
 
---
--- Name: verify_kommentar_m_id; Type: TRIGGER; Schema: land; Owner: -
---
-
-CREATE TRIGGER verify_kommentar_m_id BEFORE INSERT ON kommentar_m FOR EACH ROW EXECUTE PROCEDURE bund.is_kommentar_unique();
-
-
---
--- Name: verify_kommentar_p_id; Type: TRIGGER; Schema: land; Owner: -
---
-
-CREATE TRIGGER verify_kommentar_p_id BEFORE INSERT ON kommentar_p FOR EACH ROW EXECUTE PROCEDURE bund.is_kommentar_unique();
-
-
---
--- Name: verify_messung_id; Type: TRIGGER; Schema: land; Owner: -
---
-
-CREATE TRIGGER verify_messung_id BEFORE INSERT ON messung FOR EACH ROW EXECUTE PROCEDURE bund.is_messung_unique();
-
-
---
--- Name: verify_messwert_id; Type: TRIGGER; Schema: land; Owner: -
---
-
-CREATE TRIGGER verify_messwert_id BEFORE INSERT ON messwert FOR EACH ROW EXECUTE PROCEDURE bund.is_messwert_unique();
-
-
---
--- Name: verify_ort_id; Type: TRIGGER; Schema: land; Owner: -
---
-
-CREATE TRIGGER verify_ort_id BEFORE INSERT ON ort FOR EACH ROW EXECUTE PROCEDURE bund.is_ort_unique();
-
-
---
--- Name: verify_probe_id; Type: TRIGGER; Schema: land; Owner: -
---
-
-CREATE TRIGGER verify_probe_id BEFORE INSERT ON probe FOR EACH ROW EXECUTE PROCEDURE bund.is_probe_unique();
-
-
---
--- Name: verify_status_id; Type: TRIGGER; Schema: land; Owner: -
---
-
-CREATE TRIGGER verify_status_id BEFORE INSERT ON status FOR EACH ROW EXECUTE PROCEDURE bund.is_status_unique();
-
-
---
--- Name: verify_zusatz_wert_id; Type: TRIGGER; Schema: land; Owner: -
---
-
-CREATE TRIGGER verify_zusatz_wert_id BEFORE INSERT ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE bund.is_zusatz_wert_unique();
-
-
 SET search_path = bund, pg_catalog;
 
 --
@@ -2747,6 +2609,14 @@
 
 
 --
+-- Name: messung_status_protokoll_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY messung
+    ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id);
+
+
+--
 -- Name: messwert_meh_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
 --
 
@@ -2795,6 +2665,14 @@
 
 
 --
+-- Name: probe_labor_mst_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY probe
+    ADD CONSTRAINT probe_labor_mst_id_fkey FOREIGN KEY (labor_mst_id) REFERENCES stammdaten.mess_stelle(id);
+
+
+--
 -- Name: probe_mst_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
 --
 
@@ -2803,14 +2681,6 @@
 
 
 --
--- Name: probe_mst_id_fkey1; Type: FK CONSTRAINT; Schema: bund; Owner: -
---
-
-ALTER TABLE ONLY probe
-    ADD CONSTRAINT probe_mst_id_fkey1 FOREIGN KEY (mst_id) REFERENCES stammdaten.mess_stelle(id);
-
-
---
 -- Name: probe_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
 --
 
@@ -2835,19 +2705,19 @@
 
 
 --
--- Name: status_erzeuger_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
---
-
-ALTER TABLE ONLY status
-    ADD CONSTRAINT status_erzeuger_fkey FOREIGN KEY (erzeuger) REFERENCES stammdaten.mess_stelle(id);
-
-
---
--- Name: status_messungs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
---
-
-ALTER TABLE ONLY status
-    ADD CONSTRAINT status_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id);
+-- Name: status_protokoll_status_stufe_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY status_protokoll
+    ADD CONSTRAINT status_protokoll_status_stufe_fkey FOREIGN KEY (status_stufe) REFERENCES stammdaten.status_stufe(id);
+
+
+--
+-- Name: status_protokoll_status_wert_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
+--
+
+ALTER TABLE ONLY status_protokoll
+    ADD CONSTRAINT status_protokoll_status_wert_fkey FOREIGN KEY (status_wert) REFERENCES stammdaten.status_wert(id);
 
 
 --
@@ -2965,6 +2835,14 @@
 
 
 --
+-- Name: messung_status_protokoll_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY messung
+    ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id);
+
+
+--
 -- Name: messung_translation_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
 --
 
@@ -3061,19 +2939,11 @@
 
 
 --
--- Name: status_erzeuger_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
---
-
-ALTER TABLE ONLY status
-    ADD CONSTRAINT status_erzeuger_fkey FOREIGN KEY (erzeuger) REFERENCES stammdaten.mess_stelle(id);
-
-
---
--- Name: status_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
---
-
-ALTER TABLE ONLY status
-    ADD CONSTRAINT status_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
+-- Name: status_protokoll_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
+--
+
+ALTER TABLE ONLY status_protokoll
+    ADD CONSTRAINT status_protokoll_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
 
 
 --
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)