From 3edf164eab4ea0f419c65747159f1bafcdb7973d Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Mon, 11 May 2026 22:07:16 +0000 Subject: [PATCH] Simplify code by selecting EVENTS only once --- db/schemas/lib/triggers/create/roles.m4 | 64 ++++++++++++------------- 1 file changed, 32 insertions(+), 32 deletions(-) diff --git a/db/schemas/lib/triggers/create/roles.m4 b/db/schemas/lib/triggers/create/roles.m4 index 492a6a8..ce83ff7 100644 --- a/db/schemas/lib/triggers/create/roles.m4 +++ b/db/schemas/lib/triggers/create/roles.m4 @@ -1,4 +1,4 @@ -dnl Copyright (C) 2025 The Meme Factory, Inc. http://www.karlpinc.com/ +dnl Copyright (C) 2025, 2026 The Meme Factory, Inc. http://www.karlpinc.com/ dnl dnl This program is free software: you can redistribute it and/or modify it dnl under the terms of the GNU Affero General Public License as published by @@ -29,10 +29,16 @@ CREATE OR REPLACE FUNCTION roles_func () 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 events.fid%TYPE; + BEGIN -- Function for roles insert and update triggers -- - -- AGPL_notice(` --', `2025', + -- AGPL_notice(` --', `2025, 2026', `The Meme Factory, Inc., www.karlpinc.com') IF TG_OP = 'UPDATE' THEN @@ -51,31 +57,34 @@ CREATE OR REPLACE FUNCTION roles_func () cannot_change(`ROLES', `Participant') END IF; + IF TG_OP = 'INSERT' THEN + -- Get the event information so we have the behavior and + -- don't select the same data multiple times. + -- (An over-optimization -- the benefit is to keep the code simple.) + SELECT events.behavior ,events.start, events.stop, events.fid + INTO a_behavior , a_start , a_stop , a_fid + FROM events + WHERE events.eid = NEW.eid; + END IF; + -- The participant must be under study at the time of the follow. IF TG_OP = 'INSERT' THEN DECLARE a_entrydate biography_data.entrydate%TYPE; a_departdate biography_data.departdate%TYPE; - 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; BEGIN -- The follow cannot be before the participant's entrydate. SELECT biography_data.entrydate - , events.behavior ,events.start, events.stop - , follows.fid, follows.focal, follows.date + , follows.focal, follows.date INTO a_entrydate - , a_behavior , a_start , a_stop - , a_fid , a_focal , a_date + , a_focal , a_date FROM biography_data - , events - JOIN follows ON (follows.fid = events.fid) + , follows WHERE biography_data.animid = NEW.participant - AND events.eid = NEW.eid + AND follows.fid = a_fid AND follows.date < biography_data.entrydate; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING @@ -110,16 +119,13 @@ CREATE OR REPLACE FUNCTION roles_func () -- The follow cannot be after the participant's departdate. SELECT biography_data.entrydate - , events.behavior ,events.start, events.stop - , follows.fid, follows.focal, follows.date + , follows.focal, follows.date INTO a_departdate - , a_behavior , a_start , a_stop - , a_fid , a_focal , a_date + , a_focal , a_date FROM biography_data - , events - JOIN follows ON (follows.fid = events.fid) + , follows WHERE biography_data.animid = NEW.participant - AND events.eid = NEW.eid + AND follows.fid = a_fid AND follows.date > biography_data.departdate; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING @@ -156,32 +162,26 @@ CREATE OR REPLACE FUNCTION roles_func () END IF; -- There can be at most one related row on ROLES for arrival events - IF TG_OP = 'INSERT' THEN + IF TG_OP = 'INSERT' + AND a_behavior = 'sdb_arrival' THEN DECLARE a_pid roles.pid%TYPE; a_role roles.role%TYPE; a_participant roles.participant%TYPE; - 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; BEGIN SELECT roles.pid, roles.role, roles.participant - , events.behavior, events.start, events.stop - , follows.fid, follows.focal, follows.date, follows.community + , follows.focal, follows.date, follows.community INTO a_pid , a_role , a_participant - , a_behavior , a_start , a_stop - , a_fid , a_focal , a_date , a_community + , a_focal , a_date , a_community FROM roles - JOIN events ON (events.eid = roles.eid) - JOIN follows ON (follows.fid = events.fid) + , follows WHERE roles.eid = NEW.eid AND roles.pid <> NEW.pid - AND events.behavior = 'sdb_arrival'; + AND follows.fid = a_fid; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on INSERT of ROLES' -- 2.34.1