From 5b05b6da8e1e2b83c8a96b13905d2def7d5df971 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Mon, 29 Sep 2025 22:26:07 +0000 Subject: [PATCH] Check that a follow happens only when the individual is under study Add INSERT and UPDATE trigger to FOLLOWS table. Adjust BIOGRAPHY_DATA triggers for check of changes to Entrydate and Departdate. --- db/schemas/lib/triggers/Makefile | 3 +- .../lib/triggers/create/biography_data.m4 | 62 +++++++++++ db/schemas/lib/triggers/create/follows.m4 | 105 ++++++++++++++++++ db/schemas/lib/triggers/drop/follows.m4 | 23 ++++ 4 files changed, 192 insertions(+), 1 deletion(-) create mode 100644 db/schemas/lib/triggers/create/follows.m4 create mode 100644 db/schemas/lib/triggers/drop/follows.m4 diff --git a/db/schemas/lib/triggers/Makefile b/db/schemas/lib/triggers/Makefile index 118b352..2112f94 100644 --- a/db/schemas/lib/triggers/Makefile +++ b/db/schemas/lib/triggers/Makefile @@ -26,7 +26,8 @@ ORDER := comm_ids \ cycle_states \ _error_immutable_view \ follow_observers \ - biography + biography \ + follows DROP_EXISTING := true diff --git a/db/schemas/lib/triggers/create/biography_data.m4 b/db/schemas/lib/triggers/create/biography_data.m4 index e4754d9..918543b 100644 --- a/db/schemas/lib/triggers/create/biography_data.m4 +++ b/db/schemas/lib/triggers/create/biography_data.m4 @@ -378,6 +378,68 @@ CREATE OR REPLACE FUNCTION biography_data_func () END IF; END; + -- + -- Other table checking + -- + IF TG_OP = 'UPDATE' THEN + -- FOLLOWS + DECLARE + a_fid follows.fid%TYPE; + a_community follows.community%TYPE; + a_date follows.date%TYPE; + BEGIN + -- Cannot have a follow before the individual was studied. + SELECT follows.fid, follows.community, follows.date + INTO a_fid , a_community , a_date + FROM follows + WHERE follows.focal = NEW.animid + AND follows.date < NEW.entrydate + ORDER BY follows.date; -- consistency + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'An individual cannot be followed before they are' + || ' under study' + || ': Key (Animid) = (' + || NEW.animid + || '), Value (EntryDate) = (' + || NEW.entrydate + || ': Key (FOLLOWS.FID) = (' + || a_fid + || '), Value (Date) = (' + || a_date + || '), Value (Community) = (' + || a_community + || ')'; + END IF; + + -- Cannot have a follow after study of the individual has ceased. + SELECT follows.fid, follows.community, follows.date + INTO a_fid , a_community , a_date + FROM follows + WHERE follows.focal = NEW.animid + AND follows.date > NEW.departdate + ORDER BY follows.date DESC; -- consistency + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'An individual cannot be followed after they are' + || ' no longer under study' + || ': Key (Animid) = (' + || NEW.animid + || '), Value (DepartDate) = (' + || NEW.departdate + || ': Key (FOLLOWS.FID) = (' + || a_fid + || '), Value (Date) = (' + || a_date + || '), Value (Community) = (' + || a_community + || ')'; + END IF; + END; + END IF; + RETURN NULL; END; $$; diff --git a/db/schemas/lib/triggers/create/follows.m4 b/db/schemas/lib/triggers/create/follows.m4 new file mode 100644 index 0000000..c0777e6 --- /dev/null +++ b/db/schemas/lib/triggers/create/follows.m4 @@ -0,0 +1,105 @@ +dnl Copyright (C) 2025 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 follows table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`macros.m4')dnl + + +RAISE INFO 'follows_func'; +CREATE OR REPLACE FUNCTION follows_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + BEGIN + -- Function for biography_data insert and update triggers + -- + -- AGPL_notice(` --', `2025', + `The Meme Factory, Inc., www.karlpinc.com') + + -- Cannot have a follow date before the individual was studied + DECLARE + this_entrydate biography_data.entrydate%TYPE; + + BEGIN + SELECT biography_data.entrydate + INTO this_entrydate + FROM biography_data + WHERE biography_data.animid = NEW.focal + AND biography_data.entrydate > NEW.date; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of FOLLOWS' + , DETAIL = 'An individual cannot be followed before they are' + || ' studied' + || ': Key (FID) = (' + || NEW.fid + || '), Value (Focal) = (' + || NEW.focal + || '), Value (Date) = (' + || NEW.Date + || '), Value (Community) = (' + || NEW.community + || '), Value (BIOGRAPHY_DATA.EntryDate) = (' + || this_entrydate + || ')'; + END IF; + END; + + -- Cannot have a follow 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.focal + AND NEW.date > biography_data.departdate; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of FOLLOWS' + , DETAIL = 'An individual cannot be followed when they are no' + || ' longer under study' + || ': Key (FID) = (' + || NEW.fid + || '), Value (Focal) = (' + || NEW.focal + || '), Value (Date) = (' + || NEW.Date + || '), Value (Community) = (' + || NEW.community + || '), Value (BIOGRAPHY_DATA.DepartDate) = (' + || this_departdate + || ')'; + END IF; + END; + + RETURN NULL; + END; +$$; + + +RAISE INFO 'follows_trigger'; +CREATE TRIGGER follows_trigger + AFTER INSERT OR UPDATE + ON follows FOR EACH ROW + EXECUTE PROCEDURE follows_func(); diff --git a/db/schemas/lib/triggers/drop/follows.m4 b/db/schemas/lib/triggers/drop/follows.m4 new file mode 100644 index 0000000..3017808 --- /dev/null +++ b/db/schemas/lib/triggers/drop/follows.m4 @@ -0,0 +1,23 @@ +dnl Copyright (C) 2025 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 follows table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl + +DROP FUNCTION IF EXISTS follows_func() CASCADE; -- 2.34.1