From 16bb7fc2a85b14be21bdbc8f4aac5170349f7a0f Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Thu, 13 Aug 2026 17:29:56 +0000 Subject: [PATCH] Trigger NON_BREC_SIGHTING_SOURCES --- db/schemas/lib/triggers/Makefile | 3 +- .../lib/triggers/create/biography_data.m4 | 106 ++++++++++++ .../create/non_brec_sighting_sources.m4 | 156 ++++++++++++++++++ .../drop/non_brec_sighting_sources.m4 | 23 +++ 4 files changed, 287 insertions(+), 1 deletion(-) create mode 100644 db/schemas/lib/triggers/create/non_brec_sighting_sources.m4 create mode 100644 db/schemas/lib/triggers/drop/non_brec_sighting_sources.m4 diff --git a/db/schemas/lib/triggers/Makefile b/db/schemas/lib/triggers/Makefile index 973ee76..ee0e6b9 100644 --- a/db/schemas/lib/triggers/Makefile +++ b/db/schemas/lib/triggers/Makefile @@ -61,7 +61,8 @@ ORDER := comm_ids \ food_variations \ pantgrunt_event_log \ subadult_arrivals_log \ - siv_status_bout + siv_status_bout \ + non_brec_sighting_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 d29f90d..06f71f7 100644 --- a/db/schemas/lib/triggers/create/biography_data.m4 +++ b/db/schemas/lib/triggers/create/biography_data.m4 @@ -1428,6 +1428,112 @@ CREATE OR REPLACE FUNCTION biography_data_func () END IF; END; END IF; + + -- NON_BREC_SIGHTING_SOURCES + DECLARE + a_id non_brec_sighting_sources.id%TYPE; + a_commid non_brec_sighting_sources.commid%TYPE; + a_date non_brec_sighting_sources.date%TYPE; + + BEGIN + -- Cannot be sighted in a study community before the + -- individual was studied. + IF NEW.entrydate > OLD.entrydate THEN + SELECT non_brec_sighting_sources.id + , non_brec_sighting_sources.commid + , non_brec_sighting_sources.date + INTO a_id + , a_commid + , a_date + FROM non_brec_sighting_sources + JOIN comm_ids + ON (comm_ids.commid = non_brec_sighting_sources.commid) + WHERE non_brec_sighting_sources.animid = NEW.animid + AND non_brec_sighting_sources.date < NEW.entrydate + AND comm_ids.studied + ORDER BY non_brec_sighting_sources.date; -- consistency + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'An individual cannot be sighted,' + || ' in a study group, before they are under study' + || ': Key (AnimID) = (' + || NEW.animid + || '), Value (EntryDate) = (' + || NEW.entrydate + || ': Key (NON_BREC_SIGHTING_SOURCES.ID) = (' + || a_id + || '), Value (NON_BREC_SIGHTING_SOURCES.Date) = (' + || a_date + || '), Value (NON_BREC_SIGHTING_SOURCES.CommID) = (' + || a_commid + || ')'; + END IF; + END IF; + + -- Cannot be sighted before the individual was born. + IF NEW.birthdate > OLD.birthdate THEN + SELECT non_brec_sighting_sources.id + , non_brec_sighting_sources.commid + , non_brec_sighting_sources.date + INTO a_id + , a_commid + , a_date + FROM non_brec_sighting_sources + WHERE non_brec_sighting_sources.animid = NEW.animid + AND non_brec_sighting_sources.date < NEW.birthdate + ORDER BY non_brec_sighting_sources.date; -- consistency + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'An individual cannot be sighted' + || ' before they are born' + || ': Key (AnimID) = (' + || NEW.animid + || '), Value (BirthDate) = (' + || NEW.birthdate + || ': Key (NON_BREC_SIGHTING_SOURCES.ID) = (' + || a_id + || '), Value (NON_BREC_SIGHTING_SOURCES.Date) = (' + || a_date + || '), Value (NON_BREC_SIGHTING_SOURCES.CommID) = (' + || a_commid + || ')'; + END IF; + END IF; + + -- Cannot be sighted after study of the individual has ceased. + IF NEW.departdate < OLD.departdate THEN + SELECT non_brec_sighting_sources.id + , non_brec_sighting_sources.commid + , non_brec_sighting_sources.date + INTO a_id + , a_commid + , a_date + FROM non_brec_sighting_sources + WHERE non_brec_sighting_sources.animid = NEW.animid + AND non_brec_sighting_sources.date > NEW.departdate + ORDER BY non_brec_sighting_sources.date DESC; -- consistency + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'An individual cannot be sighted after' + || ' they are no longer under study' + || ': Key (AnimID) = (' + || NEW.animid + || '), Value (DepartDate) = (' + || NEW.departdate + || ': Key (NON_BREC_SIGHTING_SOURCES.ID) = (' + || a_id + || '), Value (Date) = (' + || a_date + || '), Value (CommID) = (' + || a_commid + || ')'; + END IF; + END IF; + END; + END IF; -- TG_OP = 'UPDATE' RETURN NULL; diff --git a/db/schemas/lib/triggers/create/non_brec_sighting_sources.m4 b/db/schemas/lib/triggers/create/non_brec_sighting_sources.m4 new file mode 100644 index 0000000..6359781 --- /dev/null +++ b/db/schemas/lib/triggers/create/non_brec_sighting_sources.m4 @@ -0,0 +1,156 @@ +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 non_brec_sighting_sources table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`macros.m4')dnl + + +RAISE INFO 'non_brec_sighting_sources_func'; +CREATE OR REPLACE FUNCTION non_brec_sighting_sources_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + DECLARE + -- ROLES + a_pid roles.pid%TYPE; + a_role roles.role%TYPE; + a_participant roles.participant%TYPE; + -- EVENTS + a_eid events.eid%TYPE; + a_behavior events.behavior%TYPE; + a_start events.start%TYPE; + a_stop events.stop%TYPE; + + BEGIN + -- Function for non_brec_sighting_sources insert and update triggers + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + + IF TG_OP = 'UPDATE' THEN + -- Allowing the ID to change makes life too complicated. + cannot_change(`NON_BREC_SIGHTING_SOURCES', `ID') + END IF; + + -- Cannot have a date before the individual was studied, when in + -- a study group. + DECLARE + this_entrydate biography_data.entrydate%TYPE; + this_birthcomm biography_data.birthcomm%TYPE; + + BEGIN + SELECT biography_data.entrydate + INTO this_entrydate + FROM biography_data + JOIN comm_ids ON (comm_ids.commid = NEW.commid) + WHERE biography_data.animid = NEW.animid + AND biography_data.entrydate > NEW.date + AND biography_data.birthcomm IS DISTINCT FROM NEW.commid + AND comm_ids.studied; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of NON_BREC_SIGHTING_SOURCES' + , DETAIL = 'An individual cannot be sighted, in a studied' + || ' community, before they are studied,' + || ' unless they are born into the community' + || ': Key (ID) = (' + || NEW.id + || '), Value (AnimID) = (' + || NEW.animid + || '), Value (Date) = (' + || NEW.Date + || '), Value (CommID) = (' + || NEW.commid + || '), Value (BIOGRAPHY_DATA.EntryDate) = (' + || this_entrydate + || ')'; + END IF; + END; + + -- Cannot have a date before the individual was born + DECLARE + this_birthdate biography_data.birthdate%TYPE; + + BEGIN + SELECT biography_data.birthdate + INTO this_birthdate + FROM biography_data + WHERE biography_data.animid = NEW.animid + AND biography_data.birthdate > NEW.date; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of NON_BREC_SIGHTING_SOURCES' + , DETAIL = 'An individual cannot be followed before they are' + || ' born' + || ': Key (ID) = (' + || NEW.id + || '), Value (AnimID) = (' + || NEW.animid + || '), Value (Date) = (' + || NEW.Date + || '), Value (CommID) = (' + || NEW.commid + || '), Value (BIOGRAPHY_DATA.BirthDate) = (' + || this_birthdate + || ')'; + END IF; + END; + + -- Cannot have a date after the study of the individual has stopped + DECLARE + this_departdate biography_data.departdate%TYPE; + + BEGIN + SELECT biography_data.departdate + INTO this_departdate + FROM biography_data + WHERE biography_data.animid = NEW.animid + AND NEW.date > biography_data.departdate; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of NON_BREC_SIGHTING_SOURCES' + , DETAIL = 'An individual cannot be sighted when they are no' + || ' longer under study' + || ': Key (ID) = (' + || NEW.id + || '), Value (AnimID) = (' + || NEW.animid + || '), Value (Date) = (' + || NEW.Date + || '), Value (CommID) = (' + || NEW.commid + || '), Value (BIOGRAPHY_DATA.DepartDate) = (' + || this_departdate + || ')'; + END IF; + END; + + RETURN NULL; + END; +$$; + + +RAISE INFO 'non_brec_sighting_sources_trigger'; +CREATE TRIGGER non_brec_sighting_sources_trigger + AFTER INSERT OR UPDATE + ON non_brec_sighting_sources FOR EACH ROW + EXECUTE PROCEDURE non_brec_sighting_sources_func(); diff --git a/db/schemas/lib/triggers/drop/non_brec_sighting_sources.m4 b/db/schemas/lib/triggers/drop/non_brec_sighting_sources.m4 new file mode 100644 index 0000000..9b836ba --- /dev/null +++ b/db/schemas/lib/triggers/drop/non_brec_sighting_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 non_brec_sighting_sources table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl + +DROP FUNCTION IF EXISTS non_brec_sighting_sources_func() CASCADE; -- 2.34.1