From 38152cd5ff8b74f0dc002b89ef7df3c7f73c8382 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Thu, 4 Jun 2026 19:38:15 +0000 Subject: [PATCH] Require an ARRIVALS row have a related ROLES row --- db/schemas/lib/triggers/create/arrivals.m4 | 45 ++++++++++ db/schemas/lib/triggers/create/roles.m4 | 97 ++++++++++++++++++++++ db/schemas/lib/triggers/drop/roles.m4 | 1 + doc/src/tables/events.m4 | 4 +- 4 files changed, 144 insertions(+), 3 deletions(-) diff --git a/db/schemas/lib/triggers/create/arrivals.m4 b/db/schemas/lib/triggers/create/arrivals.m4 index b2bb29c..92b9db8 100644 --- a/db/schemas/lib/triggers/create/arrivals.m4 +++ b/db/schemas/lib/triggers/create/arrivals.m4 @@ -100,6 +100,51 @@ CREATE OR REPLACE FUNCTION arrivals_func () || a_date || ')'; END IF; + + -- There must be a related ROLES row. + SELECT events.behavior, events.start, events.stop + , follows.fid, follows.focal, follows.date + INTO a_behavior , a_start , a_stop + , a_fid , a_focal , a_date + 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); + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of ARRIVALS' + , DETAIL = 'Arrivals must have a related ROLES row' + || ': Key (EID = (' + || NEW.eid + || '): Value (Cycle) = (' + || NEW.cycle + || '): Value (DataSource) = (' + || NEW.datasource + || '): Key (ROLES.PID) = (' + || textualize(`a_pid') + || '), Value (ROLES.Role) = (' + || textualize(`a_role') + || '), Value (ROLES.Participant) = (' + || textualize(`a_participant') + || '): Key (EVENTS.EID) = (' + || NEW.eid + || '): Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || '): Key (FOLLOWS.FID) = (' + || a_fid + || '), Value (FOLLOWS.Focal) = (' + || a_focal + || '), Value (FOLLOWS.Date) = (' + || a_date + || ')'; + END IF; END; END IF; diff --git a/db/schemas/lib/triggers/create/roles.m4 b/db/schemas/lib/triggers/create/roles.m4 index 6da3071..aaae935 100644 --- a/db/schemas/lib/triggers/create/roles.m4 +++ b/db/schemas/lib/triggers/create/roles.m4 @@ -585,6 +585,97 @@ CREATE OR REPLACE FUNCTION roles_func () $$; +RAISE INFO 'roles_delete_func'; +CREATE OR REPLACE FUNCTION roles_delete_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_commid follows.commid%TYPE; + + -- ARRIVALS + a_seq arrivals.seq%TYPE; + a_neststart arrivals.neststart%TYPE; + a_nestend arrivals.nestend%TYPE; + a_cycle arrivals.cycle%TYPE; + BEGIN + -- Function for roles delete trigger + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + + -- Arrival events must have a related participant + -- (Because otherwise we cannot sequence them per arriving individual.) + SELECT events.start, events.stop + , follows.fid, follows.focal, follows.date, follows.commid + , arrivals.seq, arrivals.neststart, arrivals.nestend + , arrivals.cycle + INTO a_start , a_stop + , a_fid , a_focal , a_date , a_commid + , a_seq , a_neststart , a_nestend + , a_cycle + FROM events + JOIN follows ON (follows.fid = events.fid) + , arrivals + WHERE events.eid = OLD.eid + AND arrivals.eid = OLD.eid; + + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on DELETE of ROLES' + , DETAIL = 'Every ARRIVALS row must be related to a ROLES row,' + || ' so this ROLES row may not be deleted' + || ': Key (PID) = (' + || OLD.pid + || '), Value (EID) = (' + || OLD.eid + || '), Value (Role) = (' + || OLD.role + || '), Value (Participant) = (' + || OLD.participant + || '), Value (EVENTS.Behavior) = (sdb_arrival' + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || '), Key (FOLLOWS.FID) = (' + || a_fid + || '), Value (FOLLOWS.Focal) = (' + || a_focal + || '), Value (FOLLOWS.Date) = (' + || a_date + || '), Value (FOLLOWS.CommID) = (' + || a_commid + || '), Key (EID) = (' + || a_eid + || '), Value (Seq) = (' + || a_seq + || '), Value (NestStart) = (' + || a_neststart + || '), Value (NestStart) = (' + || a_nestend + || '), Value (Cycle) = (' + || a_cycle + || ')' + , HINT = 'To delete this row, use a transaction and' + || ' first delete the related ARRIVALS row.'; + END IF; + + RETURN NULL; + END; +$$; + + RAISE INFO 'roles_insert_commit_func'; CREATE OR REPLACE FUNCTION roles_insert_commit_func () RETURNS trigger @@ -1069,6 +1160,12 @@ CREATE TRIGGER roles_trigger ON roles FOR EACH ROW EXECUTE PROCEDURE roles_func(); +RAISE INFO 'roles_delete_trigger'; +CREATE TRIGGER roles_delete_trigger + AFTER DELETE + ON roles FOR EACH ROW + EXECUTE PROCEDURE roles_delete_func(); + RAISE INFO 'roles_insert_commit_trigger'; CREATE CONSTRAINT TRIGGER roles_insert_commit_trigger AFTER INSERT OR UPDATE diff --git a/db/schemas/lib/triggers/drop/roles.m4 b/db/schemas/lib/triggers/drop/roles.m4 index 3eec52b..e47d6c5 100644 --- a/db/schemas/lib/triggers/drop/roles.m4 +++ b/db/schemas/lib/triggers/drop/roles.m4 @@ -21,5 +21,6 @@ dnl m4 includes include(`copyright.m4')dnl DROP FUNCTION IF EXISTS roles_func() CASCADE; +DROP FUNCTION IF EXISTS roles_delete_func() CASCADE; DROP FUNCTION IF EXISTS roles_insert_commit_func() CASCADE; DROP FUNCTION IF EXISTS roles_delete_commit_func() CASCADE; diff --git a/doc/src/tables/events.m4 b/doc/src/tables/events.m4 index f7523dc..7bbec4b 100644 --- a/doc/src/tables/events.m4 +++ b/doc/src/tables/events.m4 @@ -206,9 +206,7 @@ The following table lists these rules and implications: The |ROLES| row related to the event, the row with a |ROLES|.\ |ROLES.EID| value equal to the EVENTS.\ |EVENTS.EID| value, designates the arriving/departing individual. - There may be at most one |ROLES| row related to the arrival event. - The system will generate a warning when there is no |ROLES| row - related to the arrival event. + There must be at most one |ROLES| row related to the arrival event. The EVENTS.\ |EVENTS.Start| column contains the arrival time of the individual identified in the related |ROLES| row. -- 2.34.1