From 8c60e1e63d15a2408d0fb22e571bc9570889d594 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Sat, 4 Jul 2026 16:20:18 +0000 Subject: [PATCH] Add a ROLE_CODES.Role special value of sdb_unkpair For dyadic interactions where sdb_mutual is not appropriate, either because the interaction has no direction or because sdb_mutual is already used. --- db/schemas/lib/triggers/create/roles.m4 | 32 +++++++++++++++---------- doc/src/code_tables.m4 | 17 +++++++++++++ include/global_constants.m4 | 3 +++ 3 files changed, 39 insertions(+), 13 deletions(-) diff --git a/db/schemas/lib/triggers/create/roles.m4 b/db/schemas/lib/triggers/create/roles.m4 index 673154a..e64880a 100644 --- a/db/schemas/lib/triggers/create/roles.m4 +++ b/db/schemas/lib/triggers/create/roles.m4 @@ -839,9 +839,10 @@ 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. + IF NEW.role = 'sdb_mutual' + OR NEW.role = 'sdb_unkpart' THEN + -- When there is one of these Roles, the event must have another ROLES + -- row that has a the same Role. SELECT events.behavior ,events.start, events.stop , watches.wid, watches.animid, watches.date, watches.commid , watches.type @@ -856,12 +857,14 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () FROM roles WHERE roles.eid = NEW.eid AND roles.pid <> NEW.pid - AND roles.role = 'sdb_mutual'); + AND roles.role = NEW.role); 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' + , DETAIL = 'The Role of (' + || NEW.role + || ') requires another ROLES' || ' rows having Role value and the same ROLES.EID' || ' value, but no such ROLES row exists:' || ' Key (PID) = (' @@ -1062,12 +1065,13 @@ 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. + IF OLD.role = 'sdb_mutual' + OR OLD.role = 'sdb_unkpart' THEN + -- When there is one of these Roles, the event must have another ROLES + -- row that has the same Role. - -- There must be either be no sdb_mutual pairs, or at least one sdb_mutual - -- pair of ROLES rows. + -- There must be either be no pairs, or at least one pair of ROLES + -- rows. DECLARE mutual_count INTEGER; @@ -1077,7 +1081,7 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () INTO mutual_count FROM roles WHERE roles.eid = OLD.eid - AND roles.role = 'sdb_mutual'; + AND roles.role = OLD.role; IF mutual_count = 1 THEN SELECT events.behavior ,events.start, events.stop @@ -1092,10 +1096,12 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on DELETE of ROLES' - , DETAIL = 'The Role of (sdb_mutual) requires at least one other' + , DETAIL = 'The Role of (' + || OLD.role + || ') 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' + || ' such row' || ': The deleted ROLES row is: Key (PID) = (' || OLD.pid || '), Value (EID) = (' diff --git a/doc/src/code_tables.m4 b/doc/src/code_tables.m4 index dbee3b6..0227202 100644 --- a/doc/src/code_tables.m4 +++ b/doc/src/code_tables.m4 @@ -1110,6 +1110,23 @@ The ``sdb_mutual`` code 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| + +The ``sdb_unkpair`` code + The code ``sdb_unkpair`` has a special meaning. + + It is used to describe dyadic interaction behavior where event + participants are required to be paired, but the direction of the + dyadic interation is either unknown or the interaction itself has + no intrinsic direction -- when the ``sdb_mutual`` code is not + appropriate. + When there is such a dyadic interaction and one participant is + assigned this role, the other participant must also be assigned + this role. + This means that, for any given |ROLES.EID| value when there is a + row with a |ROLES.Role| value of ``sdb_unkpair`` there must be + another row on ROLES, with the given |ROLES.EID| value, with a + |ROLES.Role| value of ``sdb_unkpair``. + |transaction commit| Column Descriptions diff --git a/include/global_constants.m4 b/include/global_constants.m4 index 3069e2d..9f6f642 100644 --- a/include/global_constants.m4 +++ b/include/global_constants.m4 @@ -167,6 +167,9 @@ define(`sdb_actor', `Actor') define(`sdb_actee', `Actee') dnl Special code value for dyadic symmetric interactions define(`sdb_mutual', `Mutual') +dnl Special code value for directed dyadic interactions where the +dnl direction is not known. +define(`sdb_unkpair', `UNKPair') dnl dnl SIGHTING_RECORDS -- 2.34.1