From 4a14c8dee5f4baa1fc54a99080ed4b8c8bd9f74c Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Sat, 4 Jul 2026 18:20:38 +0000 Subject: [PATCH] Allow focal grooming events to be mutual or of unknown direction This changes focal grooming events (EVENTS.Behavior = sdb_grooming) so they must be dyadic, instead of allowing individuals other than the actor and recipient to be participants in the event. The validation of dyadic behavioral events was re-worked to remove redundancies. --- db/schemas/lib/triggers/create/roles.m4 | 241 +++++++++--------------- doc/src/tables/events.m4 | 18 +- doc/src/tables/groomings.m4 | 6 +- 3 files changed, 103 insertions(+), 162 deletions(-) diff --git a/db/schemas/lib/triggers/create/roles.m4 b/db/schemas/lib/triggers/create/roles.m4 index e64880a..c43225f 100644 --- a/db/schemas/lib/triggers/create/roles.m4 +++ b/db/schemas/lib/triggers/create/roles.m4 @@ -292,13 +292,14 @@ CREATE OR REPLACE FUNCTION roles_func () END IF; -- - -- Validate aggression and grooming events + -- Validate the pairwise events, aggression, grooming, mating, and pantgrunts -- IF TG_OP = 'INSERT' AND (a_behavior = 'sdb_aggression' OR a_behavior = 'sdb_grooming' OR a_behavior = 'sdb_groom_scan' OR a_behavior = 'sdb_groom_scan_a' + OR a_behavior = 'sdb_pg_event' OR a_behavior = 'sdb_mating') THEN DECLARE a_pid roles.pid%TYPE; @@ -312,64 +313,21 @@ CREATE OR REPLACE FUNCTION roles_func () BEGIN -- - -- Validate ROLES.Role for aggression and grooming events + -- Validate ROLES.Role for aggression, grooming, and mating events. -- - IF (a_behavior = 'sdb_aggression' - OR a_behavior = 'sdb_groom_scan' - OR a_behavior = 'sdb_mating') - AND NEW.role <> 'sdb_actor' - AND NEW.role <> 'sdb_actee' THEN - -- The ROLES rows for aggression, groom scan, and mating - -- events must have a role of sdb_actor or sdb_actee. - SELECT watches.animid, watches.date, watches.commid - INTO a_animid , a_date , a_commid - FROM watches - WHERE watches.wid = a_wid; - IF FOUND THEN - RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'Error on INSERT of ROLES' - , DETAIL = 'Invalid Role value:' - || ' Inserting: Key (PID) = (' - || NEW.pid - || '), Value (EID) = (' - || NEW.eid - || '), Value (Role) = (' - || NEW.role - || '), Value (Participant) = (' - || NEW.participant - || '), Value (EVENTS.Behavior) = (' - || a_behavior - || '), Value (EVENTS.Start) = (' - || a_start - || '), Value (EVENTS.Stop) = (' - || a_stop - || '), Value (WATCHES.WID) = (' - || a_wid - || '), Value (WATCHES.AnimID) = (' - || a_animid - || '), Value (WATCHES.Date) = (' - || a_date - || '), Value (WATCHES.CommID) = (' - || a_commid - || ')' - , HINT = 'The ROLES.Role must be either (sdb_actor)' - || ' or (sdb_actee) for an event' - || ' with a EVENTS.Behavior of (' - || a_behavior - || ').'; - END IF; - END IF; - - -- There can be only one row with each aggression/grooming role per - -- aggression/grooming/mating event. - SELECT roles.pid, roles.role, roles.participant - INTO a_pid , a_role , a_participant - FROM roles - WHERE roles.eid = NEW.eid - AND roles.pid <> NEW.pid - AND roles.role = NEW.role; + -- (Don't really need to condition on the last behavior, but + -- it makes the code more clear.) + IF NEW.role <> 'sdb_actor' + AND NEW.role <> 'sdb_actee' + AND ((a_behavior = 'sdb_aggression' + OR a_behavior = 'sdb_groom_scan' + OR a_behavior = 'sdb_mating') + OR (NEW.role <> 'sdb_mutual' + AND (a_behavior = 'sdb_groom_scan_a' + OR a_behavior = 'sdb_pg_event' + OR (NEW.role <> 'sdb_unkpair' + AND a_behavior = 'sdb_grooming')))) THEN - IF FOUND THEN SELECT watches.animid, watches.date, watches.commid INTO a_animid , a_date , a_commid FROM watches @@ -377,13 +335,7 @@ CREATE OR REPLACE FUNCTION roles_func () RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on INSERT of ROLES' - , DETAIL = 'There cannot be more than one row on ROLES' - || ' with a ROLES.Role value of (' - || NEW.role - || ') for an' - || ' event with a EVENTS.Behavior of (' - || a_behavior - || '):' + , DETAIL = 'Invalid Role value:' || ' Inserting: Key (PID) = (' || NEW.pid || '), Value (EID) = (' @@ -392,13 +344,6 @@ CREATE OR REPLACE FUNCTION roles_func () || NEW.role || '), Value (Participant) = (' || NEW.participant - || '): But the following ROLES row already exists:' - || ': Key (PID) = (' - || a_pid - || '), Value (Role) = (' - || a_role - || '), Value (Participant) = (' - || a_participant || '), Value (EVENTS.Behavior) = (' || a_behavior || '), Value (EVENTS.Start) = (' @@ -414,6 +359,73 @@ CREATE OR REPLACE FUNCTION roles_func () || '), Value (WATCHES.CommID) = (' || a_commid || ')'; + + END IF; + + IF a_behavior = 'sdb_aggression' + OR a_behavior = 'sdb_groom_scan' + OR a_behavior = 'sdb_mating' THEN + -- Role is sdb_actor or sdb_actee. There can be only one row + -- with each of these roles per aggression/grooming/mating + -- event. + -- + -- This is checked at transaction commit, but that can be annoying + -- when uploading in bulk because only one row will produce an error. + -- So check here also. + -- + SELECT roles.pid, roles.role, roles.participant + INTO a_pid , a_role , a_participant + FROM roles + WHERE roles.eid = NEW.eid + AND roles.pid <> NEW.pid + AND roles.role = NEW.role; + + IF FOUND THEN + SELECT watches.animid, watches.date, watches.commid + INTO a_animid , a_date , a_commid + FROM watches + WHERE watches.wid = a_wid; + + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on INSERT of ROLES' + , DETAIL = 'There cannot be more than one row on ROLES' + || ' with a ROLES.Role value of (' + || NEW.role + || ') for an' + || ' event with a EVENTS.Behavior of (' + || a_behavior + || '):' + || ' Inserting: Key (PID) = (' + || NEW.pid + || '), Value (EID) = (' + || NEW.eid + || '), Value (Role) = (' + || NEW.role + || '), Value (Participant) = (' + || NEW.participant + || '): But the following ROLES row already exists:' + || ': Key (PID) = (' + || a_pid + || '), Value (Role) = (' + || a_role + || '), Value (Participant) = (' + || a_participant + || '), Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || '), Value (WATCHES.WID) = (' + || a_wid + || '), Value (WATCHES.AnimID) = (' + || a_animid + || '), Value (WATCHES.Date) = (' + || a_date + || '), Value (WATCHES.CommID) = (' + || a_commid + || ')'; + END IF; END IF; END; END IF; @@ -468,68 +480,12 @@ CREATE OR REPLACE FUNCTION roles_func () END; END IF; - -- - -- The only roles allowed for follow pantgrunts and feeding station - -- groom scans are - sdb_actor, sdb_actee, and sdb_mutual. - -- - IF TG_OP = 'INSERT' - AND (a_behavior = 'sdb_pg_event' - OR a_behavior = 'sdb_groom_scan_a') - AND NEW.role <> 'sdb_actor' - AND NEW.role <> 'sdb_actee' - AND NEW.role <> 'sdb_mutual' THEN - DECLARE - a_animid watches.animid%TYPE; - a_date watches.date%TYPE; - a_commid watches.commid%TYPE; - - BEGIN - SELECT watches.animid, watches.date, watches.commid - INTO a_animid , a_date , a_commid - FROM watches - WHERE watches.wid = a_wid; - RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'Error on INSERT of ROLES' - , DETAIL = 'Invalid Roles value for a ' - || a_behavior - || ' event' - || ': Key (PID) = (' - || NEW.pid - || '), Value (EID) = (' - || NEW.eid - || '), Value (Role) = (' - || NEW.role - || '), Value (Participant) = (' - || NEW.participant - || '), Value (EVENTS.Behavior) = (' - || a_behavior - || '), Value (EVENTS.Start) = (' - || a_start - || '), Value (EVENTS.Stop) = (' - || a_stop - || '), Value (WATCHES.WID) = (' - || a_wid - || '), Value (WATCHES.AnimID) = (' - || a_animid - || '), Value (WATCHES.Date) = (' - || a_date - || '), Value (WATCHES.CommID) = (' - || a_commid - || ')' - , HINT = 'When EVENTS.Behavior = (' - || a_behavoir - || ') the only ROLES.Role values allowed are: sdb_actor,' - || ' sdb_actee, and sdb_mutual'; - END; - END IF; - -- -- Depending on the behavior, when there are multiple participants -- each role must be distinct. -- IF TG_OP = 'INSERT' AND (a_behavior = 'sdb_aggression' - OR a_behavior = 'sdb_grooming' OR a_behavior = 'sdb_groom_scan' OR a_behavior = 'sdb_mating_event') THEN DECLARE @@ -896,7 +852,7 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () END IF; -- One of the individuals involved in a grooming event must be - -- the focal and must be the sdb_actor or the sdb_actee + -- the focal. SELECT events.behavior ,events.start, events.stop , watches.wid, watches.animid, watches.date, watches.commid @@ -912,16 +868,12 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () (SELECT 1 FROM roles WHERE roles.eid = NEW.eid - AND roles.participant = watches.animid - AND (roles.role = 'sdb_actor' - OR roles.role = 'sdb_actee')); + AND roles.participant = watches.animid); IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on ' || TG_OP || ' of ROLES' , DETAIL = 'One of the individuals involved in each grooming' - || ' event must be the focal, who must be either the' - || ' individual doing the grooming or the individual' - || ' being groomed' + || ' event must be the focal' || ': Key (PID) = (' || NEW.pid || '), Value (EID) = (' @@ -1065,6 +1017,7 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () END; END IF; + -- The sdb_mutual and sdb_unkpart roles must come in pairs. IF OLD.role = 'sdb_mutual' OR OLD.role = 'sdb_unkpart' THEN -- When there is one of these Roles, the event must have another ROLES @@ -1132,7 +1085,7 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () END IF; -- One of the individuals involved in a grooming event must be - -- the focal, who must be either the sdb_actor or the sdb_actee + -- the focal. SELECT events.behavior ,events.start, events.stop , watches.wid, watches.animid, watches.date, watches.commid @@ -1147,16 +1100,12 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () (SELECT 1 FROM roles WHERE roles.eid = OLD.eid - AND roles.participant = watches.animid - AND (roles.role = 'sdb_actor' - OR roles.role = 'sdb_actee')); + AND roles.participant = watches.animid); IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on DELETE of ROLES' , DETAIL = 'One of the individuals involved in each grooming' - || ' event must be the focal, who must be either the' - || ' individual doing the grooming or the individual' - || ' being groomed' + || ' event must be the focal' || ': The deleted ROLES row is Key (PID) = (' || OLD.pid || '), Value (EID) = (' @@ -1183,7 +1132,7 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () END IF; -- The Initiator or Terminator in a grooming event must be - -- a participant and an sdb_actor or an sdb_actee. + -- a participant. SELECT events.behavior ,events.start, events.stop , watches.wid, watches.animid, watches.date, watches.commid @@ -1204,17 +1153,13 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () (SELECT 1 FROM roles WHERE roles.pid = groomings.initiator - AND roles.eid = OLD.eid - AND (roles.role = 'sdb_actor' - OR roles.role = 'sdb_actee'))) + AND roles.eid = OLD.eid)) OR (groomings.terminator IS NOT NULL AND NOT EXISTS (SELECT 1 FROM roles WHERE roles.pid = groomings.terminator - AND roles.eid = OLD.eid - AND (roles.role = 'sdb_actor' - OR roles.role = 'sdb_actee')))); + AND roles.eid = OLD.eid))); IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING diff --git a/doc/src/tables/events.m4 b/doc/src/tables/events.m4 index db8fe78..df0975e 100644 --- a/doc/src/tables/events.m4 +++ b/doc/src/tables/events.m4 @@ -424,9 +424,10 @@ The following table lists these rules and implications: table relates to the grooming event describes whether that individual groomed or was groomed during the grooming event. - There should be at least two |ROLES| row related to the grooming event. - The two |ROLES|.\ |ROLES.Role| codes that are required to exist are - ``sdb_actor`` and ``sdb_actee``. + There should be exactly two |ROLES| row related to the attendance + grooming interval sampling event. + Only the codes ``sdb_actor``, ``sdb_actee``, ``sdb_mutual``, and + ``sdb_unkpair`` may be used as |ROLES|.\ |ROLES.Role| code values. The system will generate a warning when there are less than two |ROLES| rows related to an grooming event. @@ -435,13 +436,12 @@ The following table lists these rules and implications: This means that their |ROLES|.\ |ROLES.Participant| values must differ. - One of the individuals in a grooming event, either being groomed or - grooming, must be the focal individual. + One of the individuals in a grooming event must be the focal + individual. This means that on of the the |ROLES| rows related to the event - must have a |ROLES|.\ |ROLES.Role| value of either - ``sdb_actor`` or ``sdb_actee``, and have a |ROLES|.\ - |ROLES.Participant| value equal to the |WATCHES|.\ |WATCHES.AnimID| - of the follow that is related to the grooming event. + must have a |ROLES.Participant| value equal to the |WATCHES|.\ + |WATCHES.AnimID| of the follow that is related to the grooming + event. For grooming events, the EVENTS.\ |EVENTS.Certainty| column records the certainty of the duration of the grooming event. diff --git a/doc/src/tables/groomings.m4 b/doc/src/tables/groomings.m4 index 58add9a..2e7bf5f 100644 --- a/doc/src/tables/groomings.m4 +++ b/doc/src/tables/groomings.m4 @@ -53,14 +53,10 @@ supplies information on the individuals involved. The initiator and the terminator of the grooming event, when either of these are known, must each be one of the individuals who participated in the grooming event. -Further, the initiator and the terminator of the grooming event, when -known, must each be either the groomer or the individual being -groomed. This means the |GROOMINGS.Initiator| and |GROOMINGS.Terminator| values must be a |ROLES|.\ |ROLES.PID| value of a |ROLES| row that has a |ROLES|.\ |ROLES.EID| equal to the |GROOMINGS.EID| of the grooming -event and the |ROLES|.\ |ROLES.Role| value must be either -``sdb_actor`` or ``sdb_actee``. +event. |transaction commit| For further information, including additional data integrity rules, -- 2.34.1