From 8cb27442f0ada22141f85a76c94e1c0d6d9eb831 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Sat, 30 May 2026 00:05:42 +0000 Subject: [PATCH] Make sdb_mutual (Mutual) a special ROLE_CODES.Role value When there is one participant with a sdb_mutual role, there must be at least one other participant with this role. --- db/schemas/lib/triggers/create/roles.m4 | 108 ++++++++++++++++++++++++ doc/src/code_tables.m4 | 9 ++ doc/src/tables/roles.m4 | 12 +++ include/global_constants.m4 | 2 + 4 files changed, 131 insertions(+) diff --git a/db/schemas/lib/triggers/create/roles.m4 b/db/schemas/lib/triggers/create/roles.m4 index 179d63d..3e67ccb 100644 --- a/db/schemas/lib/triggers/create/roles.m4 +++ b/db/schemas/lib/triggers/create/roles.m4 @@ -552,6 +552,55 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () END IF; END IF; + IF NEW.role = 'sdb_mutual' THEN + -- When there is a sdb_mutual Role, the event must have another ROLES + -- row that has a sdb_mutual Role. + SELECT events.behavior ,events.start, events.stop + , follows.fid, follows.focal, follows.date, follows.commid + INTO a_behavior , a_start , a_stop + , a_fid , a_focal , a_date , a_commid + FROM events + JOIN follows ON (follows.fid = events.fid) + WHERE events.eid = NEW.eid + AND NOT EXISTS + (SELECT 1 + FROM roles + WHERE roles.eid = NEW.eid + AND roles.pid <> NEW.pid + AND roles.role = 'sdb_mutual'); + + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of ROLES' + , DETAIL = 'The Role of (sdb_mutual) requires another ROLES' + || ' rows having Role value and the same ROLES.EID' + || ' value, but no such ROLES row exists:' + || ' 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 (FOLLOWS.FID) = (' + || a_fid + || '), Value (FOLLOWS.Focal) = (' + || a_focal + || '), Value (FOLLOWS.Date) = (' + || a_date + || '), Value (FOLLOWS.CommID) = (' + || a_commid + || ')'; + END IF; + 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 @@ -713,6 +762,65 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () END; END IF; + IF OLD.role = 'sdb_mutual' THEN + -- When there is a sdb_mutual Role, the event must have another ROLES + -- row that has an sdb_mutual Role. + + -- There must be either be no sdb_mutual pairs, or at least one sdb_mutual + -- pair of ROLES rows. + DECLARE + mutual_count INTEGER; + + BEGIN + -- See how many actors there are + SELECT COUNT(*) + INTO mutual_count + FROM roles + WHERE roles.eid = OLD.eid + AND roles.role = 'sdb_mutual'; + + IF mutual_count = 1 THEN + SELECT events.behavior ,events.start, events.stop + , follows.fid, follows.focal, follows.date, follows.commid + INTO a_behavior , a_start , a_stop + , a_fid , a_focal , a_date , a_commid + FROM events + JOIN follows ON (follows.fid = events.fid) + WHERE events.eid = OLD.eid; + + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on DELETE of ROLES' + , DETAIL = 'The Role of (sdb_mutual) requires at least one other' + || ' ROLES row exist with the same Role value and the' + || ' same ROLES.EID value; but there is only one' + || ' row with a ROLES.Role of sdb_mutual' + || ': The deleted ROLES row is: Key (PID) = (' + || OLD.pid + || '), Value (EID) = (' + || OLD.eid + || '), Value (Role) = (' + || OLD.role + || '), Value (Participant) = (' + || OLD.participant + || '), 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 + || '), Value (FOLLOWS.CommID) = (' + || a_commid + || ')'; + END IF; + END; + 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 diff --git a/doc/src/code_tables.m4 b/doc/src/code_tables.m4 index 5862e0d..0d80c08 100644 --- a/doc/src/code_tables.m4 +++ b/doc/src/code_tables.m4 @@ -794,6 +794,15 @@ The ``sdb_actor`` and ``sdb_actee`` codes assigned one of these roles, the other participant must be assigned the other role. +The ``sdb_mutual`` code + The code ``sdb_mutual`` has a special meaning. + + It is used to describe a symmetric dyadic interaction behavior, + where event participants are required to be paired. + When there is such a dyadic interaction and one participant is + assigned this role, the other participant must also be assigned + this role. + Column Descriptions ``````````````````` diff --git a/doc/src/tables/roles.m4 b/doc/src/tables/roles.m4 index 5893bd5..d1b0535 100644 --- a/doc/src/tables/roles.m4 +++ b/doc/src/tables/roles.m4 @@ -55,6 +55,9 @@ An individual can only be a participant once, in any given event. This means that the combination of |ROLES.Participant| and |ROLES.EID| must be unique. +.. These next 2 paragraphs are redundant. They are also in + the ROLE_CODES speical values section. But it seems worthwhile.... + The |ROLES.Role| values of ``sdb_actor`` and ``sdb_actee`` are :ref:`special `. They are used in dyadic interactions, where they must be paired. @@ -63,6 +66,15 @@ with a |ROLES.Role| value of ``sdb_actor`` there must be another row on ROLES, with the given |ROLES.EID| value, with a |ROLES.Role| value of ``sdb_actee``, and vice-versa. |transaction commit| +The |ROLES.Role| value of ``sdb_mutual`` is :ref:`special +`. +It is used in dyadic interactions, where their must be 2 rows in +ROLES each of which uses this role. +This means that, for any given |ROLES.EID| value when there is a row +with a |ROLES.Role| value of ``sdb_mutual`` there must be another row +on ROLES, with the given |ROLES.EID| value, with a |ROLES.Role| value +of ``sdb_mutual``. |transaction commit| + For further information on the required, and expected, relationship between ROLES, |EVENTS|, and other tables see the documentation of the |EVENTS| table. diff --git a/include/global_constants.m4 b/include/global_constants.m4 index 56898f4..5d516f2 100644 --- a/include/global_constants.m4 +++ b/include/global_constants.m4 @@ -143,6 +143,8 @@ dnl dnl Special code values for dyadic asymetric interactions define(`sdb_actor', `Actor') define(`sdb_actee', `Actee') +dnl Special code value for dyadic symmetric interactions +define(`sdb_mutual', `Mutual') dnl dnl SIGHTING_RECORDS -- 2.34.1