From d424cadb8463a2780a9b723d9953ab925a781534 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Mon, 25 May 2026 17:24:08 +0000 Subject: [PATCH] Adjust data integrity rules to allow "other" grooming participants --- db/schemas/lib/triggers/create/follows.m4 | 11 ++++++--- db/schemas/lib/triggers/create/roles.m4 | 27 +++++++++++++++-------- doc/src/tables/events.m4 | 23 +++++++++---------- 3 files changed, 37 insertions(+), 24 deletions(-) diff --git a/db/schemas/lib/triggers/create/follows.m4 b/db/schemas/lib/triggers/create/follows.m4 index 6e25986..4151438 100644 --- a/db/schemas/lib/triggers/create/follows.m4 +++ b/db/schemas/lib/triggers/create/follows.m4 @@ -608,7 +608,8 @@ CREATE OR REPLACE FUNCTION follows_commit_func () IF TG_OP = 'UPDATE' AND NEW.focal <> OLD.focal THEN - -- One of the individuals grooming must be the focal. + -- One of the individuals grooming must be the focal, and be either + -- the sdb_actor or the sdb_actee. DECLARE -- EVENTS @@ -627,12 +628,16 @@ CREATE OR REPLACE FUNCTION follows_commit_func () (SELECT 1 FROM roles WHERE roles.eid = events.eid - AND roles.participant = NEW.focal); + AND roles.participant = NEW.focal + AND (roles.role = 'sdb_actor' + OR roles.role = 'sdb_actee')); IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on UPDATE of FOLLOWS' , DETAIL = 'One of the individuals involved in the grooming event' - || ' must the the focal' + || ' event must be the focal, who must be either the' + || ' individual doing the grooming or the individual' + || ' being groomed' || ': Key (FID) = (' || NEW.fid || '), Value (Focal) = (' diff --git a/db/schemas/lib/triggers/create/roles.m4 b/db/schemas/lib/triggers/create/roles.m4 index 02abff6..695c028 100644 --- a/db/schemas/lib/triggers/create/roles.m4 +++ b/db/schemas/lib/triggers/create/roles.m4 @@ -244,9 +244,10 @@ CREATE OR REPLACE FUNCTION roles_func () -- -- Validate ROLES.Role for aggression and grooming events -- - IF NEW.role <> 'sdb_actor' + IF a_behavior = 'sdb_aggression' + AND NEW.role <> 'sdb_actor' AND NEW.role <> 'sdb_actee' THEN - -- The ROLES rows for aggression and grooming events must have + -- The ROLES rows for aggression events must have -- a role of sdb_actor or sdb_actee. SELECT follows.focal, follows.date, follows.community INTO a_focal , a_date , a_community @@ -392,7 +393,7 @@ CREATE OR REPLACE FUNCTION roles_func () -- -- Depending on the behavior, when there are multiple participants - -- each must be distinct. + -- each role must be distinct. -- IF TG_OP = 'INSERT' AND (a_behavior = 'sdb_aggression' @@ -549,7 +550,7 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () END IF; -- One of the individuals involved in a grooming event must be - -- the focal + -- the focal and must be the sdb_actor or the sdb_actee SELECT events.behavior ,events.start, events.stop , follows.fid, follows.focal, follows.date, follows.community @@ -563,12 +564,16 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () (SELECT 1 FROM roles WHERE roles.eid = NEW.eid - AND roles.participant = follows.focal); + AND roles.participant = follows.focal + AND (roles.role = 'sdb_actor' + OR roles.role = 'sdb_actee')); 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' + || ' event must be the focal, who must be either the' + || ' individual doing the grooming or the individual' + || ' being groomed' || ': Key (PID) = (' || NEW.pid || '), Value (EID) = (' @@ -706,7 +711,7 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () END IF; -- One of the individuals involved in a grooming event must be - -- the focal + -- the focal, who must be either the sdb_actor or the sdb_actee SELECT events.behavior ,events.start, events.stop , follows.fid, follows.focal, follows.date, follows.community @@ -721,12 +726,16 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () (SELECT 1 FROM roles WHERE roles.eid = OLD.eid - AND roles.participant = follows.focal); + AND roles.participant = follows.focal + AND (roles.role = 'sdb_actor' + OR roles.role = 'sdb_actee')); 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' + || ' event must be the focal, who must be either the' + || ' individual doing the grooming or the individual' + || ' being groomed' || ': The deleted ROLES row is Key (PID) = (' || OLD.pid || '), Value (EID) = (' diff --git a/doc/src/tables/events.m4 b/doc/src/tables/events.m4 index 9f8b7b0..9aee6a3 100644 --- a/doc/src/tables/events.m4 +++ b/doc/src/tables/events.m4 @@ -162,25 +162,24 @@ 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 exactly two |ROLES| row related to the grooming event. - The only two |ROLES|.\ |ROLES.Role| codes allowed are ``sdb_actor`` and - ``sdb_actee``. - One of those two roles must be ``sdb_actor`` and the other must be - ``sdb_actee``. - The system will generate a warning when there are not exactly two + 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``. + The system will generate a warning when there are less than two |ROLES| rows related to an grooming event. - The two participants in a grooming event must be different + The participants in a grooming event must be different individuals. This means that their |ROLES|.\ |ROLES.Participant| values must differ. - One of the two participants in a grooming event must be the focal - individual. + One of the individuals in a grooming event, either being groomed or + grooming, must be the focal individual. This means that on of the the |ROLES| rows related to the event - must have a |ROLES|.\ |ROLES.Participant| value equal to the - |FOLLOWS|.\ |FOLLOWS.Focal| of the follow that is related to the - grooming 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 |FOLLOWS|.\ |FOLLOWS.Focal| + 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. -- 2.34.1