From 4a7bcbd4fcfbb13c8f86cf6ae9de8a4f36716db6 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Fri, 5 Jun 2026 17:26:51 +0000 Subject: [PATCH] Add triggers for SWELLING_SOURCES --- db/schemas/lib/triggers/Makefile | 3 +- .../lib/triggers/create/biography_data.m4 | 103 +++++++++++++++ .../lib/triggers/create/swelling_sources.m4 | 124 ++++++++++++++++++ .../lib/triggers/drop/swelling_sources.m4 | 23 ++++ doc/src/tables/swelling_sources.m4 | 10 +- 5 files changed, 256 insertions(+), 7 deletions(-) create mode 100644 db/schemas/lib/triggers/create/swelling_sources.m4 create mode 100644 db/schemas/lib/triggers/drop/swelling_sources.m4 diff --git a/db/schemas/lib/triggers/Makefile b/db/schemas/lib/triggers/Makefile index 09619ff..a1ed24f 100644 --- a/db/schemas/lib/triggers/Makefile +++ b/db/schemas/lib/triggers/Makefile @@ -39,7 +39,8 @@ ORDER := comm_ids \ groomings \ groom_scans_b \ attendance \ - arrivals_a + arrivals_a \ + swelling_sources DROP_EXISTING := true diff --git a/db/schemas/lib/triggers/create/biography_data.m4 b/db/schemas/lib/triggers/create/biography_data.m4 index 5dcfe26..d3a84ee 100644 --- a/db/schemas/lib/triggers/create/biography_data.m4 +++ b/db/schemas/lib/triggers/create/biography_data.m4 @@ -561,6 +561,109 @@ CREATE OR REPLACE FUNCTION biography_data_func () END IF; END; + -- SWELLING_SOURCES + DECLARE + a_id swelling_sources.id%TYPE; + a_date swelling_sources.date%TYPE; + a_swellingmin swelling_sources.swellingmin%TYPE; + a_swellingmax swelling_sources.swellingmax%TYPE; + + BEGIN + -- The individual must be female to have a record of sexual swelling + IF NEW.sex <>'sdb_female' AND + OLD.sex = 'sdb_female' THEN + SELECT swelling_sources.id, swelling_sources.date + , swelling_sources.swellingmin, swelling_sources.swellingmax + INTO a_id , a_date + , a_swellingmin , a_swellingmax + FROM swelling_sources + WHERE swelling_sources.animid = NEW.animid + AND swelling_sources.date < NEW.entrydate + ORDER BY swelling_sources.date; -- consistency + + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'An individual cannot have records of sexual swelling' + || ' unless they are female' + || ': Key (Animid) = (' + || NEW.animid + || '), Value (Sex) = (' + || NEW.sex + || ': Key (SWELLING_SOURCES.ID) = (' + || a_id + || '), Value (SWELLING_SOURCES.Date) = (' + || a_date + || '), Value (SWELLING_SOURCES.SwellingMin) = (' + || a_swellingmin + || '), Value (SWELLING_SOURCES.SwellingMax) = (' + || a_swellingmax + || ')'; + END IF; + END IF; + + -- Cannot have a swelling state record before + -- the individual was studied. + IF NEW.entrydate <> OLD.entrydate THEN + SELECT swelling_sources.id, swelling_sources.date + , swelling_sources.swellingmin, swelling_sources.swellingmax + INTO a_id , a_date + , a_swellingmin , a_swellingmax + FROM swelling_sources + WHERE swelling_sources.animid = NEW.animid + AND swelling_sources.date < NEW.entrydate + ORDER BY swelling_sources.date; -- consistency + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'An individual cannot have records of sexual swelling' + || ' before they are under study' + || ': Key (Animid) = (' + || NEW.animid + || '), Value (EntryDate) = (' + || NEW.entrydate + || ': Key (SWELLING_SOURCES.ID) = (' + || a_id + || '), Value (SWELLING_SOURCES.Date) = (' + || a_date + || '), Value (SWELLING_SOURCES.SwellingMin) = (' + || a_swellingmin + || '), Value (SWELLING_SOURCES.SwellingMax) = (' + || a_swellingmax + || ')'; + END IF; + END IF; + + IF NEW.departdate <> OLD.departdate THEN + SELECT swelling_sources.id, swelling_sources.date + , swelling_sources.swellingmin, swelling_sources.swellingmax + INTO a_id , a_date + , a_swellingmin , a_swellingmax + WHERE swelling_sources.animid = NEW.animid + AND NEW.departdate < swelling_sources.date + ORDER BY swelling_sources.date; -- consistency + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'An individual cannot have records of sexual swelling' + || ' after they are no longer under study' + || ': Key (Animid) = (' + || NEW.animid + || '), Value (EntryDate) = (' + || NEW.entrydate + || ': Key (SWELLING_SOURCES.ID) = (' + || a_id + || '), Value (SWELLING_SOURCES.Date) = (' + || a_date + || '), Value (SWELLING_SOURCES.SwellingMin) = (' + || a_swellingmin + || '), Value (SWELLING_SOURCES.SwellingMax) = (' + || a_swellingmax + || ')'; + END IF; + END IF; + END; + -- ROLES DECLARE diff --git a/db/schemas/lib/triggers/create/swelling_sources.m4 b/db/schemas/lib/triggers/create/swelling_sources.m4 new file mode 100644 index 0000000..ed92492 --- /dev/null +++ b/db/schemas/lib/triggers/create/swelling_sources.m4 @@ -0,0 +1,124 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc. http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify it +dnl under the terms of the GNU Affero General Public License as published by +dnl the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Triggers for the swelling_sources table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`macros.m4')dnl + + +RAISE INFO 'swelling_sources_func'; +CREATE OR REPLACE FUNCTION swelling_sources_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + BEGIN + -- Function for swelling_sources insert and update triggers + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + + IF TG_OP = 'UPDATE' THEN + cannot_change(`SWELLING_SOURCES', `ID') + END IF; + + -- An individual must be female. + IF TG_OP = 'INSERT' THEN + DECLARE + a_sex biography_data.sex%TYPE; + + BEGIN + SELECT biography_data.sex + INTO a_sex + FROM biography_data + WHERE biography_data.animid = NEW.animid + AND biography_data.sex <> 'sdb_female'; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of SWELLING_SOURCES' + , DETAIL = 'An individual must be female to have' + || ' a swelling measurement' + || ': Key (ID) = (' + || NEW.id + || '), Value (AnimID) = (' + || NEW.animid + || '), Value (Date) = (' + || NEW.date + || '), Value (SwellingMin) = (' + || NEW.swellingmin + || '), Value (SwellingMax) = (' + || NEW.swellingmax + || '), Value (BIOGRAPHY_DATA.Sex) = (' + || a_sex + || ')'; + END IF; + END; + END IF; + + IF TG_OP = 'INSERT' + OR NEW.date <> OLD.date THEN + DECLARE + a_entrydate biography_data.entrydate%TYPE; + a_departdate biography_data.departdate%TYPE; + + BEGIN + SELECT biography_data.entrydate, biography_data.departdate + INTO a_entrydate , a_departdate + FROM biography_data + WHERE biography_data.animid = NEW.animid + AND (NEW.date < biography_data.entrydate + OR NEW.date > biography_data.departdate); + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of SWELLING_SOURCES' + , DETAIL = 'An individual must be under study to have' + || ' a swelling measurement,' + || ' the SWELLING_SOURCES.Date cannot be before' + || ' BIOGRAPHY_DATA.EntryDate or after' + || ' BIOGRAPHY_DATA.DepartDate' + || ': Key (ID) = (' + || NEW.id + || '), Value (AnimID) = (' + || NEW.animid + || '), Value (Date) = (' + || NEW.date + || '), Value (SwellingMin) = (' + || NEW.swellingmin + || '), Value (SwellingMax) = (' + || NEW.swellingmax + || '), Value (BIOGRAPHY_DATA.EntryDate) = (' + || a_entrydate + || '), Value (BIOGRAPHY_DATA.DepartDate) = (' + || a_departdate + || ')'; + END IF; + END; + END IF; + + RETURN NULL; + END; +$$; + + +RAISE INFO 'swelling_sources_trigger'; +CREATE TRIGGER swelling_sources_trigger + AFTER INSERT OR UPDATE + ON swelling_sources FOR EACH ROW + EXECUTE PROCEDURE swelling_sources_func(); diff --git a/db/schemas/lib/triggers/drop/swelling_sources.m4 b/db/schemas/lib/triggers/drop/swelling_sources.m4 new file mode 100644 index 0000000..3d7b929 --- /dev/null +++ b/db/schemas/lib/triggers/drop/swelling_sources.m4 @@ -0,0 +1,23 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc. http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published by +dnl the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Drop triggers for swelling_sources table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl + +DROP FUNCTION IF EXISTS swelling_sources_func() CASCADE; diff --git a/doc/src/tables/swelling_sources.m4 b/doc/src/tables/swelling_sources.m4 index b07ed24..0cba179 100644 --- a/doc/src/tables/swelling_sources.m4 +++ b/doc/src/tables/swelling_sources.m4 @@ -25,13 +25,11 @@ SWELLING_SOURCES ---------------- .. |SWELLING_SOURCES_summary| replace:: + There is a row for each female chimpanzee, for each date sexual - swelling information was recorded, for each kind of record -- that - is not a TRec -- where the sexual swelling information was - recorded. - Rows exist both for non-BRec related sightings, and for BRec - related sighting when those sightings were recorded in the old, MS - Access based, database. + swelling information was recorded, for each kind of record where + the sexual swelling information was recorded, when the sexual + swelling information is not recorded elsewhere in SokweDB. Going forward, this table is used to make non-BRec related, changes to the per-day record of female chimpanzee sexual swelling information found in the |SWELLING_STATES| table. -- 2.34.1