From 9ddfd9a83f13d64fbe7a4815370b0850e6d006fe Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Mon, 1 Jun 2026 21:26:21 +0000 Subject: [PATCH] Support groom scanning at the feeding station as an EVENTS row Uses the sdb_groom_scan_a EVENTS.Behavior code. Document rules and implement. --- db/schemas/lib/triggers/create/roles.m4 | 121 +++++++++++++++++---- db/schemas/sokwedb/tables/create/events.m4 | 4 + doc/src/tables/events.m4 | 40 +++++++ include/global_constants.m4 | 2 + 4 files changed, 147 insertions(+), 20 deletions(-) diff --git a/db/schemas/lib/triggers/create/roles.m4 b/db/schemas/lib/triggers/create/roles.m4 index 4fd98c2..6da3071 100644 --- a/db/schemas/lib/triggers/create/roles.m4 +++ b/db/schemas/lib/triggers/create/roles.m4 @@ -264,14 +264,20 @@ CREATE OR REPLACE FUNCTION roles_func () IF TG_OP = 'INSERT' AND (a_behavior = 'sdb_aggression' OR a_behavior = 'sdb_grooming' - OR a_behavior = 'sdb_groom_scan') THEN + OR a_behavior = 'sdb_groom_scan' + OR a_behavior = 'sdb_groom_scan_a') THEN DECLARE a_pid roles.pid%TYPE; a_role roles.role%TYPE; a_participant roles.participant%TYPE; + a_focal follows.focal%TYPE; - a_date follows.date%TYPE; - a_commid follows.commid%TYPE; + a_animid attendance.animid%TYPE; + + a_date DATE; + a_commid comm_ids.commid%TYPE; + + observation_msg TEXT; -- Report of the animal, date, and community BEGIN -- @@ -325,16 +331,45 @@ CREATE OR REPLACE FUNCTION roles_func () -- There can be only one row with each aggression/grooming role per -- aggression/grooming event. SELECT roles.pid, roles.role, roles.participant - , follows.focal, follows.date, follows.commid - INTO a_pid , a_role , a_participant - , a_focal , a_date , a_commid - FROM roles - , follows - WHERE roles.eid = NEW.eid + INTO a_pid , a_role , a_participant + FROM roles + WHERE roles.eid = NEW.eid AND roles.pid <> NEW.pid - AND roles.role = NEW.role - AND follows.fid = a_fid; + AND roles.role = NEW.role; + IF FOUND THEN + IF a_behavior = 'sdb_groom_scan_a' THEN + SELECT attendance.animid, attendance.date, attendance.commid + INTO a_animid , a_date , a_commid + FROM attendance + WHERE follows.fid = a_fid; + + observation_msg := 'Value (ATTENDANCE.AtID) = (' + || a_atid + || '), Value (ATTENDANCE.Focal) = (' + || a_focal + || '), Value (ATTENDANCE.Date) = (' + || a_date + || '), Value (ATTENDANCE.CommID) = (' + || a_commid + || ')'; + + ELSE -- Behavior is sdb_aggression or sdb_grooming or sdb_groom_scan + SELECT follows.focal, follows.date, follows.commid + INTO a_focal , a_date , a_commid + FROM follows + WHERE follows.fid = a_fid; + observation_msg := 'Value (FOLLOWS.FID) = (' + || a_fid + || '), Value (FOLLOWS.Focal) = (' + || a_focal + || '), Value (FOLLOWS.Date) = (' + || a_date + || '), Value (FOLLOWS.CommID) = (' + || a_commid + || ')'; + END IF; + RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on INSERT of ROLES' , DETAIL = 'There cannot be more than one row on ROLES' @@ -365,15 +400,8 @@ CREATE OR REPLACE FUNCTION roles_func () || a_start || '), Value (EVENTS.Stop) = (' || a_stop - || '), Value (FOLLOWS.FID) = (' - || a_fid - || '), Value (FOLLOWS.Focal) = (' - || a_focal - || '), Value (FOLLOWS.Date) = (' - || a_date - || '), Value (FOLLOWS.CommID) = (' - || a_commid - || ')'; + || '), ' + || observation_msg; END IF; END; END IF; @@ -425,6 +453,59 @@ CREATE OR REPLACE FUNCTION roles_func () END; END IF; + -- + -- The only roles allowed for feeding station groom scans are + -- sdb_actor, sdb_actee, and sdb_mutual. + -- + IF TG_OP = 'INSERT' + AND a_behavior = 'sdb_groom_scan_a' + AND NEW.role <> 'sdb_actor' + AND NEW.role <> 'sdb_actee' + AND NEW.role <> 'sdb_mutual' THEN + DECLARE + a_animid attendance.animid%TYPE; + a_date attendance.date%TYPE; + a_commid attendance.commid%TYPE; + + BEGIN + SELECT attendance.animid, attendance.date, attendance.commid + INTO a_animid , a_date , a_commid + FROM attendance + WHERE attendance.atid = a_atid; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on INSERT of ROLES' + , DETAIL = 'Invalid ROLES.Role value' + || ': 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 (ATTENDANCE.AtID) = (' + || a_atid + || '), Value (ATTENDANCE.AnimID) = (' + || a_animid + || '), Value (ATTENDANCE.Date) = (' + || a_date + || '), Value (ATTENDANCE.CommID) = (' + || a_commid + || ')' + , HINT = 'When EVENTS.Behavior = (sdb_groom_scan_a) the only' + || ' ROLES.Role values allowed are: sdb_actor,' + || ' sdb_actee, and sdb_mutual'; + END IF; + END; + END IF; + -- -- Depending on the behavior, when there are multiple participants -- each role must be distinct. diff --git a/db/schemas/sokwedb/tables/create/events.m4 b/db/schemas/sokwedb/tables/create/events.m4 index fc29e5d..55d740a 100644 --- a/db/schemas/sokwedb/tables/create/events.m4 +++ b/db/schemas/sokwedb/tables/create/events.m4 @@ -97,6 +97,7 @@ CREATE TABLE events ( , 'sdb_food' , 'sdb_grooming' , 'sdb_groom_scan' + , 'sdb_groom_scan_a' , 'sdb_mating' , 'sdb_other_species' , 'sdb_pantgrunt')) @@ -119,6 +120,7 @@ CREATE TABLE events ( _point_behavior_time(`sdb_aggression') _point_behavior_time(`sdb_groom_scan') + _point_behavior_time(`sdb_groom_scan_a') _pair_behavior_source(`sdb_aggression', `FID') _pair_behavior_source(`sdb_arrival', `FID') @@ -127,9 +129,11 @@ CREATE TABLE events ( _pair_behavior_source(`sdb_groom_scan', `FID') _pair_behavior_source(`sdb_arrival_a', `AtID') + _pair_behavior_source(`sdb_groom_scan_a', `AtID') _behavior_certain(`sdb_arrival_a') _behavior_certain(`sdb_groom_scan') + _behavior_certain(`sdb_groom_scan_a') ); grant_priv(`EVENTS') diff --git a/doc/src/tables/events.m4 b/doc/src/tables/events.m4 index 77387b3..f7523dc 100644 --- a/doc/src/tables/events.m4 +++ b/doc/src/tables/events.m4 @@ -153,6 +153,43 @@ The following table lists these rules and implications: ``sdb_actee``. +.. _EVENTS_groom_scan_a_code: + +``sdb_groom_scan_a`` (Attendance SCAN interval Groomings) + The EVENTS row must be associated with a record of attendance at + the feeding station. + This means the |EVENTS.AtID| column must not be |null|. + + The |ROLES| rows related to the event, the rows with a |ROLES|.\ + |ROLES.EID| value equal to the EVENTS.\ |EVENTS.EID| value, + designates the individuals involved in the grooming event. + The |ROLES|.\ |ROLES.Role| code of each individual that the |ROLES| + table relates to the grooming event describes whether that + individual groomed or was groomed during the attendance grooming + interval sampling event. + + There should be exactly two |ROLES| row related to the attendance + grooming interval sampling event. + Only the codes ``sdb_actor``, ``sdb_actee``, and ``sdb_mutual`` + may be used as ROLES.\ |ROLES.Role| code values. + The system will generate a warning when there are not exactly two + |ROLES| rows related to a attendance grooming event. + + The two participants in a attendance grooming event must be + different individuals. + This means that their |ROLES|.\ |ROLES.Participant| values must + differ. + + Both the |EVENTS|.\ |EVENTS.Start| and |EVENTS|.\ |EVENTS.Stop| + columns record the time of the scan when the attendance grooming was + observed. + This means the value of the EVENTS.\ |EVENTS.Start| column must + equal the value of the |EVENTS|.\ |EVENTS.Stop| column. + + For attendance grooming interval sampling events, the |EVENTS|.\ + |EVENTS.Certainty| column must be ``sdb_identity_certain``. + + .. _EVENTS_arrival_code: ``sdb_arrival`` (Arrival) @@ -386,6 +423,9 @@ The following list summarizes the available codes: ``sdb_aggression`` (Aggression) A row must exist on |AGGRESSIONS|. +``sdb_groom_scan_a`` (Attendance SCAN interval Groomings) + A row must exist on |GROOM_SCANS_B|. + ``sdb_arrival`` (Arrival) A row must exist on |ARRIVALS|. diff --git a/include/global_constants.m4 b/include/global_constants.m4 index 622e515..adb8316 100644 --- a/include/global_constants.m4 +++ b/include/global_constants.m4 @@ -117,6 +117,8 @@ dnl The arrival at the feeding station event define(`sdb_arrival_a', `AARR') dnl The aggression event define(`sdb_aggression', `AGG') +dnl The A-Record (attendance at feeding station) groom scan +define(`sdb_groom_scan_a', `AGSCAN') dnl The arrival event define(`sdb_arrival', `ARR') dnl The food/eating event -- 2.34.1