From 8c5c4452e6cdc87e467ffdc006a3a194608f1d4b Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Sun, 5 Oct 2025 17:39:28 +0000 Subject: [PATCH] ROLES.Participant must be under study --- .../lib/triggers/create/biography_data.m4 | 123 +++++++++++++++++- db/schemas/lib/triggers/create/follows.m4 | 123 ++++++++++++++++++ db/schemas/lib/triggers/create/roles.m4 | 105 +++++++++++++++ doc/src/tables/roles.m4 | 7 + 4 files changed, 357 insertions(+), 1 deletion(-) diff --git a/db/schemas/lib/triggers/create/biography_data.m4 b/db/schemas/lib/triggers/create/biography_data.m4 index 918543b..619e68f 100644 --- a/db/schemas/lib/triggers/create/biography_data.m4 +++ b/db/schemas/lib/triggers/create/biography_data.m4 @@ -1,4 +1,5 @@ -dnl Copyright (C) 2023, 2024 The Meme Factory, Inc. http://www.karlpinc.com/ +dnl Copyright (C) 2023, 2024, 2025 The Meme Factory, Inc. +dnl 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 @@ -438,6 +439,126 @@ CREATE OR REPLACE FUNCTION biography_data_func () || ')'; END IF; END; + + -- ROLES + + DECLARE + -- ROLES + a_pid roles.pid%TYPE; + a_role roles.role%TYPE; + -- EVENTS + a_eid events.eid%TYPE; + a_behavior events.behavior%TYPE; + a_start events.start%TYPE; + a_stop events.stop%TYPE; + -- FOLLOWS + a_fid follows.fid%TYPE; + a_focal follows.focal%TYPE; + a_date follows.date%TYPE; + + BEGIN + -- The participants in an event cannot be in a follow that is + -- before a participant's entry into the study + IF NEW.entrydate > OLD.entrydate THEN + SELECT roles.pid, roles.role + , events.eid, events.behavior, events.start, events.stop + , follows.fid, follows.focal, follows.date + INTO a_pid , a_role + , a_eid , a_behavior , a_start , a_stop + , a_fid , a_focal , a_date + FROM roles + JOIN events ON (events.eid = roles.eid) + JOIN follows ON (follows.fid = events.fid) + WHERE roles.participant = NEW.animid + AND follows.date < NEW.entrydate + -- Produce a consistent error message + ORDER BY follows.date, follows.fid + , roles.role, roles.pid + , events.behavior, events.start, events.stop, events.eid; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'An individual cannot particpate in a follow (be a' + || ' ROLES.Participant) before' + || ' their entry to the study' + || ' (before BIOGRAPHY_DATA.EntryDate)' + || ': Key (Animid) = (' + || NEW.animid + || '): Value (EntryDate) = (' + || NEW.entrydate + || '): Key (ROLES.PID) = (' + || a_pid + || '), Value (ROLES.Role) = (' + || a_role + || '): Key (EVENTS.EID) = (' + || a_eid + || '): Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || '): Key (FOLLOWS.FID) = (' + || a_fid + || '), Value (FOLLOWS.Focal) = (' + || a_focal + || '), Value (FOLLOWS.Date) = (' + || a_date + || ')'; + END IF; + END IF; + + -- The participants in an event cannot be in a follow that is + -- after a participant's departure from the study + IF NEW.departdate < OLD.departdate THEN + SELECT roles.pid, roles.role + , events.eid, events.behavior, events.start, events.stop + , follows.fid, follows.focal, follows.date + INTO a_pid , a_role + , a_eid , a_behavior , a_start , a_stop + , a_fid , a_focal , a_date + FROM roles + JOIN events ON (events.eid = roles.eid) + JOIN follows ON (follows.fid = events.fid) + WHERE roles.participant = NEW.animid + AND follows.date > NEW.departdate + -- Produce a consistent error message + ORDER BY follows.date, follows.fid + , roles.role, roles.pid + , events.behavior, events.start, events.stop, events.eid; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'An individual cannot particpate in a follow (be a' + || ' ROLES.Participant) after' + || ' their departure from the study' + || ' (before BIOGRAPHY_DATA.EntryDate)' + || ': Key (Animid) = (' + || NEW.animid + || '): Value (DepartDate) = (' + || NEW.departdate + || '): Key (ROLES.PID) = (' + || a_pid + || '), Value (ROLES.Role) = (' + || a_role + || '): Key (EVENTS.EID) = (' + || a_eid + || '): Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || '): Key (FOLLOWS.FID) = (' + || a_fid + || '), Value (FOLLOWS.Focal) = (' + || a_focal + || '), Value (FOLLOWS.Date) = (' + || a_date + || ')'; + END IF; + END IF; + END; END IF; RETURN NULL; diff --git a/db/schemas/lib/triggers/create/follows.m4 b/db/schemas/lib/triggers/create/follows.m4 index 19c7100..0336735 100644 --- a/db/schemas/lib/triggers/create/follows.m4 +++ b/db/schemas/lib/triggers/create/follows.m4 @@ -98,6 +98,129 @@ CREATE OR REPLACE FUNCTION follows_func () END IF; END; + IF TG_OP = 'UPDATE' + AND NEW.date <> OLD.date THEN + DECLARE + -- BIOGRAPHY_DATA + a_entrydate biography_data.entrydate%TYPE; + a_departdate biography_data.departdate%TYPE; + -- 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 + -- The participants in an event cannot be in a follow that is + -- before a participant's entry into the study + SELECT events.eid, events.behavior, events.start, events.stop + , roles.pid, roles.role, roles.participant + , biography_data.entrydate + INTO a_eid , a_behavior , a_start , a_stop + , a_pid , a_role , a_participant + , a_entrydate + FROM events + JOIN roles + ON (roles.eid = events.eid) + JOIN biography_data + ON (biography_data.animid = roles.participant) + WHERE events.fid = NEW.fid + AND biography_data.entrydate < NEW.date + -- A consistent error message + ORDER BY events.start, events.stop, events.eid + , roles.particpant, roles.role; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of FOLLOWS' + , DETAIL = 'An individual cannot particpate (be a' + || ' ROLES.Participant) in a follow before' + || ' their entry to the study' + || ' (before BIOGRAPHY_DATA.EntryDate)' + || '): Key (FID) = (' + || NEW.fid + || '), Value (Focal) = (' + || NEW.focal + || '), Value (Community) = (' + || NEW.community + || '), Value (Date) = (' + || NEW.date + || '): Key (EVENTS.EID) = (' + || a_eid + || '): Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || ': Key (ROLES.PID) = (' + || a_pid + || '), Value (ROLES.Role) = (' + || a_role + || '), Value (ROLES.Participant) = (' + || a_participant + || '): Value (BIOGRAPHY_DATA.EntryDate) = (' + || a_entrydate + || ')'; + END IF; + + -- The participants in an event cannot be in a follow that is + -- after a participant's departure from the study + SELECT events.eid, events.behavior, events.start, events.stop + , roles.pid, roles.role, roles.participant + , biography_data.departdate + INTO a_eid , a_behavior , a_start , a_stop + , a_pid , a_role , a_participant + , a_departdate + FROM events + JOIN roles + ON (roles.eid = events.eid) + JOIN biography_data + ON (biography_data.animid = roles.participant) + WHERE events.fid = NEW.fid + AND biography_data.departdate > NEW.date + -- A consistent error message + ORDER BY events.start, events.stop, events.eid + , roles.particpant, roles.role; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of FOLLOWS' + , DETAIL = 'An individual cannot particpate (be a' + || ' ROLES.Participant) in a follow after' + || ' their departure from the study' + || ' (after BIOGRAPHY_DATA.EntryDate)' + || '): Key (FID) = (' + || NEW.fid + || '), Value (Focal) = (' + || NEW.focal + || '), Value (Community) = (' + || NEW.community + || '), Value (Date) = (' + || NEW.date + || '): Key (EVENTS.EID) = (' + || a_eid + || '): Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || ': Key (ROLES.PID) = (' + || a_pid + || '), Value (ROLES.Role) = (' + || a_role + || '), Value (ROLES.Participant) = (' + || a_participant + || '): Value (BIOGRAPHY_DATA.DepartDate) = (' + || a_departdate + || ')'; + END IF; + END; + END IF; + RETURN NULL; END; $$; diff --git a/db/schemas/lib/triggers/create/roles.m4 b/db/schemas/lib/triggers/create/roles.m4 index 4abf1b4..9f7f783 100644 --- a/db/schemas/lib/triggers/create/roles.m4 +++ b/db/schemas/lib/triggers/create/roles.m4 @@ -46,6 +46,111 @@ CREATE OR REPLACE FUNCTION roles_func () cannot_change(`ROLES', `EID') END IF; + -- The participant must be under study at the time of the follow. + IF TG_OP = 'INSERT' + OR (NEW.participant <> OLD.participant) THEN + DECLARE + a_entrydate biography_data.entrydate%TYPE; + a_departdate biography_data.departdate%TYPE; + a_behavior events.behavior%TYPE; + a_start events.start%TYPE; + a_stop events.stop%TYPE; + a_fid follows.fid%TYPE; + a_focal follows.focal%TYPE; + a_date follows.date%TYPE; + + BEGIN + -- The follow cannot be before the participant's entrydate. + SELECT biography_data.entrydate + , events.behavior ,events.start, events.stop + , follows.fid, follows.focal, follows.date + INTO a_entrydate + , a_behavior , a_start , a_stop + , a_fid , a_focal , a_date + FROM biography_data + , events + JOIN follows ON (follows.fid = events.fid) + WHERE biography_data.animid = NEW.participant + AND events.eid = NEW.eid + AND follows.date < biography_data.entrydate; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of ROLES' + , DETAIL = 'An individual cannot particpate in a follow before' + || ' their entry to the study' + || ' (before BIOGRAPHY_DATA.EntryDate)' + || ': Key (PID) = (' + || NEW.pid + || '), Value (EID) = (' + || NEW.eid + || '), Value (Role) = (' + || NEW.role + || '), Value (Participant) = (' + || NEW.participant + || '): Value (BIOGRAPHY_DATA.EntryDate) = (' + || a_entrydate + || '): Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || '): Key (FOLLOWS.FID) = (' + || a_fid + || '), Value (FOLLOWS.Focal) = (' + || a_focal + || '), Value (FOLLOWS.Date) = (' + || a_date + || ')'; + END IF; + + -- The follow cannot be after the participant's departdate. + SELECT biography_data.entrydate + , events.behavior ,events.start, events.stop + , follows.fid, follows.focal, follows.date + INTO a_departdate + , a_behavior , a_start , a_stop + , a_fid , a_focal , a_date + FROM biography_data + , events + JOIN follows ON (follows.fid = events.fid) + WHERE biography_data.animid = NEW.participant + AND events.eid = NEW.eid + AND follows.date > biography_data.departdate; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of ROLES' + , DETAIL = 'An individual cannot particpate in a follow after' + || ' their departure from the study' + || ' (after BIOGRAPHY_DATA.DepartDate)' + || ' event with a EVENTS.Behavior of (sdb_arrival):' + || ': Key (PID) = (' + || NEW.pid + || '), Value (EID) = (' + || NEW.eid + || '), Value (Role) = (' + || NEW.role + || '), Value (Participant) = (' + || NEW.participant + || '): Value (BIOGRAPHY_DATA.EntryDate) = (' + || a_departdate + || '): Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || '): Value (FOLLOWS.FID) = (' + || a_fid + || '), Value (FOLLOWS.Focal) = (' + || a_focal + || '), Value (FOLLOWS.Date) = (' + || a_date + || ')'; + END IF; + END; + END IF; + -- There can be at most one related row on ROLES for arrival events IF TG_OP = 'INSERT' THEN DECLARE diff --git a/doc/src/tables/roles.m4 b/doc/src/tables/roles.m4 index 5f19f68..86ec712 100644 --- a/doc/src/tables/roles.m4 +++ b/doc/src/tables/roles.m4 @@ -35,6 +35,13 @@ Usually, there will be at least one participant. But this is not true of events that are no more than a record of the presence of other species sighted during a follow. +The participant in the event must be under study on the date of the +follow; the related |FOLLOWS|.\ |FOLLOWS.Date| must be between the +|ROLES.Participant|'s |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA|.\ +|BIOGRAPHY_DATA.EntryDate| and the|BIOGRAPHY_DATA|.\ +|BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.DepartDate|, inclusive of +endpoints. + For further information on the required, and expected, relationship between ROLES, |EVENTS|, and other tables see the documentation of the |EVENTS| table. -- 2.34.1