From 0708de11b62cda7fbeca788f776eda6b56bb8e23 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Sat, 23 May 2026 21:04:40 +0000 Subject: [PATCH] The ROLES.Role of sdb_actor and sdb_actee must occur in pairs --- db/schemas/lib/triggers/create/roles.m4 | 207 ++++++++++++++++++++++++ doc/src/tables/roles.m4 | 8 + 2 files changed, 215 insertions(+) diff --git a/db/schemas/lib/triggers/create/roles.m4 b/db/schemas/lib/triggers/create/roles.m4 index 67ff26a..ed7112b 100644 --- a/db/schemas/lib/triggers/create/roles.m4 +++ b/db/schemas/lib/triggers/create/roles.m4 @@ -465,8 +465,215 @@ CREATE OR REPLACE FUNCTION roles_func () $$; +RAISE INFO 'roles_insert_commit_func'; +CREATE OR REPLACE FUNCTION roles_insert_commit_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + DECLARE + 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; + a_community follows.community%TYPE; + + missing_role role_codes.role%TYPE; + + BEGIN + -- Function for roles after commit on insert trigger + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + + IF NEW.role = 'sdb_actor' + OR NEW.role = 'sdb_actee' THEN + -- When there is a sdb_actor Role, the event must have another ROLES + -- row that has an sdb_acteee Role. And vice-versa. + CASE + WHEN NEW.role = 'sdb_actor' THEN + missing_role := 'sdb_actee'; + ELSE + missing_role := 'sdb_actor'; + END CASE; + + SELECT events.behavior ,events.start, events.stop + , follows.fid, follows.focal, follows.date, follows.community + INTO a_behavior , a_start , a_stop + , a_fid , a_focal , a_date , a_community + 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.role = missing_role); + + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of ROLES' + , DETAIL = 'The Roles of (sdb_actor) and (sdb_actee) must exist' + || ' in pairs of ROLES rows having the same ROLES.EID' + || ' value, but no such ROLES row having a Role of' + || ' (' || missing_role || ') 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.Community) = (' + || a_community + || ')'; + END IF; + END IF; + + RETURN NULL; + END; +$$; + + +RAISE INFO 'roles_delete_commit_func'; +CREATE OR REPLACE FUNCTION roles_delete_commit_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + DECLARE + -- EVENTS + 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; + a_community follows.community%TYPE; + + BEGIN + -- Function for roles after commit delete trigger + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + + IF OLD.role = 'sdb_actor' + OR OLD.role = 'sdb_actee' THEN + -- When there is a sdb_actor Role, the event must have another ROLES + -- row that has an sdb_acteee Role. And vice-versa. + + -- There must be either be no actor/actee pairs, or one actor/actee + -- pair of ROLES rows. + DECLARE + actor_count INTEGER; + actee_count INTEGER; + + BEGIN + -- See how many actors there are + SELECT COUNT(*) + INTO actor_count + FROM roles + WHERE roles.eid = OLD.eid + AND roles.role = 'sdb_actor'; + + -- See how many actees there are + SELECT COUNT(*) + INTO actee_count + FROM roles + WHERE roles.eid = OLD.eid + AND roles.role = 'sdb_actee'; + + IF NOT ((actor_count = 1 + AND actee_count = 1) + OR (actor_count = 0 + AND actee_count = 0)) THEN + + SELECT events.behavior ,events.start, events.stop + , follows.fid, follows.focal, follows.date, follows.community + INTO a_behavior , a_start , a_stop + , a_fid , a_focal , a_date , a_community + 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 Roles of (sdb_actor) and (sdb_actee) must exist' + || ' in pairs of ROLES rows having the same ROLES.EID' + || ' value, and there can be only one pair;' + || ' there are (' + || actor_count + || ') related ROLES rows with a Role of sdb_actor and (' + || actee_count + || ') related ROLES rows with a Role of sdb_actee' + || ': 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.Community) = (' + || a_community + || ')'; + END IF; + END; + END IF; + + RETURN NULL; + END; +$$; + + RAISE INFO 'roles_trigger'; CREATE TRIGGER roles_trigger AFTER INSERT OR UPDATE ON roles FOR EACH ROW EXECUTE PROCEDURE roles_func(); + +RAISE INFO 'roles_insert_commit_trigger'; +CREATE CONSTRAINT TRIGGER roles_insert_commit_trigger + AFTER INSERT OR UPDATE + ON roles + DEFERRABLE INITIALLY DEFERRED + FOR EACH ROW + EXECUTE PROCEDURE roles_insert_commit_func(); + +RAISE INFO 'roles_delete_commit_trigger'; +CREATE CONSTRAINT TRIGGER roles_delete_commit_trigger + AFTER INSERT OR UPDATE + ON roles + DEFERRABLE INITIALLY DEFERRED + FOR EACH ROW + EXECUTE PROCEDURE roles_delete_commit_func(); diff --git a/doc/src/tables/roles.m4 b/doc/src/tables/roles.m4 index 5dfe711..5893bd5 100644 --- a/doc/src/tables/roles.m4 +++ b/doc/src/tables/roles.m4 @@ -55,6 +55,14 @@ 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. +The |ROLES.Role| values of ``sdb_actor`` and ``sdb_actee`` are +:ref:`special `. +They are used in dyadic interactions, where they must be paired. +This means that, for any given |ROLES.EID| value when there is a row +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| + 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