From 0402f19f5acd69bc41ac5e893a794eb021c73c96 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Sun, 14 Jun 2026 23:20:27 +0000 Subject: [PATCH] Adjust docs and validation for WATCHES.Type = sdb_follow --- .../lib/triggers/create/biography_data.m4 | 63 +++++--- db/schemas/lib/triggers/create/events.m4 | 60 ++++++++ .../lib/triggers/create/follow_observers.m4 | 45 ++++++ db/schemas/lib/triggers/create/roles.m4 | 41 ++++- db/schemas/lib/triggers/create/watches.m4 | 145 ++++++++++-------- db/schemas/sokwedb/indexes/create/watches.m4 | 5 +- db/schemas/sokwedb/indexes/drop/watches.m4 | 2 +- db/schemas/sokwedb/tables/create/events.m4 | 8 - doc/src/tables/events.m4 | 21 ++- doc/src/tables/follow_observers.m4 | 4 + doc/src/tables/watches.m4 | 113 ++++++++++---- 11 files changed, 365 insertions(+), 142 deletions(-) diff --git a/db/schemas/lib/triggers/create/biography_data.m4 b/db/schemas/lib/triggers/create/biography_data.m4 index 783f24d..7646483 100644 --- a/db/schemas/lib/triggers/create/biography_data.m4 +++ b/db/schemas/lib/triggers/create/biography_data.m4 @@ -441,11 +441,12 @@ CREATE OR REPLACE FUNCTION biography_data_func () a_wid watches.wid%TYPE; a_commid watches.commid%TYPE; a_date watches.date%TYPE; + a_type watches.type%TYPE; BEGIN -- Cannot have a follow before the individual was studied. IF NEW.entrydate <> OLD.entrydate THEN - SELECT watches.wid, watches.commid, watches.date - INTO a_wid , a_commid , a_date + SELECT watches.wid, watches.commid, watches.date, watches.type + INTO a_wid , a_commid , a_date , a_type FROM watches WHERE watches.focal = NEW.animid AND watches.date < NEW.entrydate @@ -463,6 +464,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_wid || '), Value (Date) = (' || a_date + || '), Value (Type) = (' + || a_type || '), Value (CommID) = (' || a_commid || ')'; @@ -471,8 +474,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () -- Cannot have a follow after study of the individual has ceased. IF NEW.departdate <> OLD.departdate THEN - SELECT watches.wid, watches.commid, watches.date - INTO a_wid , a_commid , a_date + SELECT watches.wid, watches.commid, watches.date, watches.type + INTO a_wid , a_commid , a_date , a_type FROM watches WHERE watches.focal = NEW.animid AND watches.date > NEW.departdate @@ -490,6 +493,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_wid || '), Value (Date) = (' || a_date + || '), Value (Type) = (' + || a_type || '), Value (CommID) = (' || a_commid || ')'; @@ -912,6 +917,7 @@ CREATE OR REPLACE FUNCTION biography_data_func () a_wid watches.wid%TYPE; a_focal watches.focal%TYPE; a_date watches.date%TYPE; + a_type watches.type%TYPE; BEGIN -- The participants in an event cannot be in a follow that is @@ -919,10 +925,10 @@ CREATE OR REPLACE FUNCTION biography_data_func () IF NEW.entrydate > OLD.entrydate THEN SELECT roles.pid, roles.role , events.eid, events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.focal, watches.date, watches.type INTO a_pid , a_role , a_eid , a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_focal , a_date , a_type FROM roles JOIN events ON (events.eid = roles.eid) JOIN watches ON (watches.wid = events.wid) @@ -961,6 +967,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_focal || '), Value (WATCHES.Date) = (' || a_date + || '), Value (WATCHES.Type) = (' + || a_type || ')'; END IF; END IF; @@ -970,10 +978,10 @@ CREATE OR REPLACE FUNCTION biography_data_func () IF NEW.departdate < OLD.departdate THEN SELECT roles.pid, roles.role , events.eid, events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.focal, watches.date, watches.type INTO a_pid , a_role , a_eid , a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_focal , a_date , a_type FROM roles JOIN events ON (events.eid = roles.eid) JOIN watches ON (watches.wid = events.wid) @@ -1012,6 +1020,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_focal || '), Value (WATCHES.Date) = (' || a_date + || '), Value (WATCHES.Type) = (' + || a_type || ')'; END IF; END IF; @@ -1030,6 +1040,7 @@ CREATE OR REPLACE FUNCTION biography_data_func () a_wid watches.wid%TYPE; a_focal watches.focal%TYPE; a_date watches.date%TYPE; + a_type watches.type%TYPE; -- ROLES a_role roles.role%TYPE; @@ -1047,11 +1058,11 @@ CREATE OR REPLACE FUNCTION biography_data_func () SELECT roles.role , events.eid, events.behavior, events.start, events.stop , arrivals.cycle - , watches.wid, watches.focal, watches.date + , watches.wid, watches.focal, watches.date, watches.type INTO a_role , a_eid , a_behavior , a_start , a_stop , a_cycle - , a_wid , a_focal , a_date + , a_wid , a_focal , a_date , a_type FROM roles JOIN events ON (events.eid = roles.eid) JOIN arrivals ON (arrivals.eid = roles.eid) @@ -1096,6 +1107,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_focal || '), Value (WATCHES.Date) = (' || a_date + || '), Value (WATCHES.Type) = (' + || a_type || ')' , HINT = 'sdb_follow_age_hint'; END IF; @@ -1113,11 +1126,11 @@ CREATE OR REPLACE FUNCTION biography_data_func () SELECT roles.role , events.eid, events.behavior, events.start, events.stop , arrivals.cycle - , watches.wid, watches.focal, watches.date + , watches.wid, watches.focal, watches.date, watches.type INTO a_role , a_eid , a_behavior , a_start , a_stop , a_cycle - , a_wid , a_focal , a_date + , a_wid , a_focal , a_date , a_type FROM roles JOIN events ON (events.eid = roles.eid) JOIN arrivals ON (arrivals.eid = roles.eid) @@ -1169,6 +1182,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_focal || '), Value (WATCHES.Date) = (' || a_date + || '), Value (WATCHES.Type) = (' + || a_type || ')' , HINT = 'sdb_follow_age_hint'; END IF; @@ -1178,11 +1193,11 @@ CREATE OR REPLACE FUNCTION biography_data_func () SELECT roles.role , events.eid, events.behavior, events.start, events.stop , arrivals.cycle - , watches.wid, watches.focal, watches.date + , watches.wid, watches.focal, watches.date, watches.type INTO a_role , a_eid , a_behavior , a_start , a_stop , a_eid , a_cycle - , a_wid , a_focal , a_date + , a_wid , a_focal , a_date , a_type FROM roles JOIN events ON (events.eid = roles.eid) JOIN arrivals ON (arrivals.eid = roles.eid) @@ -1235,6 +1250,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_focal || '), Value (WATCHES.Date) = (' || a_date + || '), Value (WATCHES.Type) = (' + || a_type || ')' , HINT = 'sdb_follow_age_hint'; END IF; @@ -1248,11 +1265,11 @@ CREATE OR REPLACE FUNCTION biography_data_func () SELECT roles.role , events.eid, events.behavior, events.start, events.stop , arrivals.cycle - , watches.wid, watches.focal, watches.date + , watches.wid, watches.focal, watches.date, watches.type INTO a_role , a_eid , a_behavior , a_start , a_stop , a_eid , a_cycle - , a_wid , a_focal , a_date + , a_wid , a_focal , a_date , a_type FROM roles JOIN events ON (events.eid = roles.eid) JOIN arrivals ON (arrivals.eid = roles.eid) @@ -1305,6 +1322,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_focal || '), Value (WATCHES.Date) = (' || a_date + || '), Value (WATCHES.Type) = (' + || a_type || ')' , HINT = 'sdb_follow_age_hint'; END IF; @@ -1313,11 +1332,11 @@ CREATE OR REPLACE FUNCTION biography_data_func () SELECT roles.role , events.eid, events.behavior, events.start, events.stop , arrivals.cycle - , watches.wid, watches.focal, watches.date + , watches.wid, watches.focal, watches.date, watches.type INTO a_role , a_eid , a_behavior , a_start , a_stop , a_cycle - , a_wid , a_focal , a_date + , a_wid , a_focal , a_date , a_type FROM roles JOIN events ON (events.eid = roles.eid) JOIN arrivals ON (arrivals.eid = roles.eid) @@ -1371,6 +1390,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_focal || '), Value (WATCHES.Date) = (' || a_date + || '), Value (WATCHES.Type) = (' + || a_type || ')' , HINT = 'sdb_follow_age_hint'; END IF; @@ -1384,11 +1405,11 @@ CREATE OR REPLACE FUNCTION biography_data_func () SELECT roles.role , events.eid, events.behavior, events.start, events.stop , arrivals.cycle - , watches.wid, watches.focal, watches.date + , watches.wid, watches.focal, watches.date, watches.type INTO a_role , a_eid , a_behavior , a_start , a_stop , a_cycle - , a_wid , a_focal , a_date + , a_wid , a_focal , a_date , a_type FROM roles JOIN events ON (events.eid = roles.eid) JOIN arrivals ON (arrivals.eid = roles.eid) @@ -1433,6 +1454,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_focal || '), Value (WATCHES.Date) = (' || a_date + || '), Value (WATCHES.Type) = (' + || a_type || ')'; END IF; END IF; diff --git a/db/schemas/lib/triggers/create/events.m4 b/db/schemas/lib/triggers/create/events.m4 index 4738a3b..73a2003 100644 --- a/db/schemas/lib/triggers/create/events.m4 +++ b/db/schemas/lib/triggers/create/events.m4 @@ -67,6 +67,66 @@ CREATE OR REPLACE FUNCTION events_func () END IF; END IF; + IF TG_OP = 'INSERT' + AND NEW.behavior IN ('sdb_aggression' + ,'sdb_arrival' + ,'sdb_food' + ,'sdb_grooming' + ,'sdb_groom_scan' + ,'sdb_other_species' + ,'sdb_pantgrunt') THEN + -- The event must be related to a follow. + DECLARE + a_focal watches.focal%TYPE; + a_commid watches.commid%TYPE; + a_date watches.date%TYPE; + a_type watches.type%TYPE; + a_notes watches.notes%TYPE; + + BEGIN + SELECT watches.focal, watches.commid, watches.date, watches.type + , watches.notes + INTO a_focal, a_commid , a_date , a_type + , a_notes + FROM watches + WHERE watches.wid = NEW.wid + AND watches.type <> 'sdb_follow'; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of FOLLOW_OBSERVERS' + , DETAIL = 'Events with (Behavior) = (' + || NEW.behavior + || ') must be related to a WATCHES row with' + || ' (Type) = (sdb_follow)' + || '): Key (EID) = (' + || NEW.eid + || '): Value (WID) = (' + || NEW.wid + || '): Value (Behavior) = (' + || NEW.behavior + || '), Value (Start) = (' + || NEW.start + || '), Value (Stop) = (' + || NEW.stop + || '), Value (Certainty) = (' + || NEW.certainty + || ') : Key (WATCHES.WID) = (' + || NEW.wid + || '), Value (WATCHES.Focal) = (' + || a_focal + || '), Value (WATCHES.Date) = (' + || a_date + || '), Value (WATCHES.Type) = (' + || a_type + || '), Value (WATCHES.CommID) = (' + || a_commid + || '), Value (WATCHES.Notes) = (' + || a_notes + || ')'; + END IF; + END; + END IF; + RETURN NULL; END; $$; diff --git a/db/schemas/lib/triggers/create/follow_observers.m4 b/db/schemas/lib/triggers/create/follow_observers.m4 index d6fae84..f3f6341 100644 --- a/db/schemas/lib/triggers/create/follow_observers.m4 +++ b/db/schemas/lib/triggers/create/follow_observers.m4 @@ -28,6 +28,13 @@ CREATE OR REPLACE FUNCTION follow_observers_func () LANGUAGE plpgsql sdb_function_set_search_path AS $$ + DECLARE + a_focal watches.focal%TYPE; + a_commid watches.commid%TYPE; + a_date watches.date%TYPE; + a_type watches.type%TYPE; + a_notes watches.notes%TYPE; + BEGIN -- Function for follow_observers insert and update triggers -- @@ -39,6 +46,44 @@ CREATE OR REPLACE FUNCTION follow_observers_func () cannot_change(`FOLLOW_OBSERVERS', `FOID') END IF; + -- The observers can only have watched a follow. + SELECT watches.focal, watches.commid, watches.date, watches.type + , watches.notes + INTO a_focal, a_commid , a_date , a_type + , a_notes + FROM watches + WHERE watches.wid = NEW.wid + AND watches.type <> 'sdb_follow'; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of FOLLOW_OBSERVERS' + , DETAIL = 'Observers must be related to a WATCHES row with' + || ' (Type) = (sdb_follow)' + ||': Key (FOID) = (' + || NEW.FOID + || '): Value (WID) = (' + || NEW.WID + || '): (Period) = (' + || NEW.period + || '): (Obs_BRec) = (' + || NEW.obs_brec + || '): (Obs_Tiki) = (' + || NEW.obs_tiki + || ') : Key (WATCHES.WID) = (' + || NEW.wid + || '), Value (WATCHES.Focal) = (' + || a_focal + || '), Value (WATCHES.Date) = (' + || a_date + || '), Value (WATCHES.Type) = (' + || a_type + || '), Value (WATCHES.CommID) = (' + || a_commid + || '), Value (WATCHES.Notes) = (' + || a_notes + || ')'; + END IF; + changequote({,})dnl person_active({FOLLOW_OBSERVERS}, {OBS_BRec}, {'Key (FOID) = (' diff --git a/db/schemas/lib/triggers/create/roles.m4 b/db/schemas/lib/triggers/create/roles.m4 index 26c78f8..9c74b93 100644 --- a/db/schemas/lib/triggers/create/roles.m4 +++ b/db/schemas/lib/triggers/create/roles.m4 @@ -122,18 +122,20 @@ CREATE OR REPLACE FUNCTION roles_func () a_departdate biography_data.departdate%TYPE; a_focal watches.focal%TYPE; a_date watches.date%TYPE; + a_type watches.type%TYPE; BEGIN -- The follow cannot be before the participant's entrydate. SELECT biography_data.entrydate - , watches.focal, watches.date + , watches.focal, watches.date, watches.type INTO a_entrydate - , a_focal , a_date + , a_focal , a_date , a_type FROM biography_data , watches WHERE biography_data.animid = NEW.participant AND watches.wid = a_wid - AND watches.date < biography_data.entrydate; + AND watches.date < biography_data.entrydate + ORDER BY watches.date, watches.wid; -- Consistent error message IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on ' || TG_OP || ' of ROLES' @@ -162,19 +164,22 @@ CREATE OR REPLACE FUNCTION roles_func () || a_focal || '), Value (WATCHES.Date) = (' || a_date + || '), Value (WATCHES.Type) = (' + || a_type || ')'; END IF; -- The follow cannot be after the participant's departdate. SELECT biography_data.entrydate - , watches.focal, watches.date + , watches.focal, watches.date, watches.type INTO a_departdate - , a_focal , a_date + , a_focal , a_date , a_type FROM biography_data , watches WHERE biography_data.animid = NEW.participant AND watches.wid = a_wid - AND watches.date > biography_data.departdate; + AND watches.date > biography_data.departdate + ORDER BY watches.date, watches.wid; -- Consistent error message IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on ' || TG_OP || ' of ROLES' @@ -204,6 +209,8 @@ CREATE OR REPLACE FUNCTION roles_func () || a_focal || '), Value (WATCHES.Date) = (' || a_date + || '), Value (WATCHES.Type) = (' + || a_type || ')'; END IF; END; @@ -784,6 +791,7 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () a_wid watches.wid%TYPE; a_focal watches.focal%TYPE; a_date watches.date%TYPE; + a_type watches.type%TYPE; a_commid watches.commid%TYPE; missing_role role_codes.role%TYPE; @@ -807,8 +815,10 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () SELECT events.behavior ,events.start, events.stop , watches.wid, watches.focal, watches.date, watches.commid + , watches.type INTO a_behavior , a_start , a_stop , a_wid , a_focal , a_date , a_commid + , a_type FROM events JOIN watches ON (watches.wid = events.wid) WHERE events.eid = NEW.eid @@ -845,6 +855,8 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () || a_focal || '), Value (WATCHES.Date) = (' || a_date + || '), Value (WATCHES.Type) = (' + || a_type || '), Value (WATCHES.CommID) = (' || a_commid || ')'; @@ -856,8 +868,10 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () -- row that has a sdb_mutual Role. SELECT events.behavior ,events.start, events.stop , watches.wid, watches.focal, watches.date, watches.commid + , watches.type INTO a_behavior , a_start , a_stop , a_wid , a_focal , a_date , a_commid + , a_type FROM events JOIN watches ON (watches.wid = events.wid) WHERE events.eid = NEW.eid @@ -894,6 +908,8 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () || a_focal || '), Value (WATCHES.Date) = (' || a_date + || '), Value (WATCHES.Type) = (' + || a_type || '), Value (WATCHES.CommID) = (' || a_commid || ')'; @@ -905,8 +921,10 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () SELECT events.behavior ,events.start, events.stop , watches.wid, watches.focal, watches.date, watches.commid + , watches.type INTO a_behavior , a_start , a_stop , a_wid , a_focal , a_date , a_commid + , a_type FROM events JOIN watches ON (watches.wid = events.wid) WHERE events.eid = NEW.eid @@ -945,6 +963,8 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () || a_focal || '), Value (WATCHES.Date) = (' || a_date + || '), Value (WATCHES.Type) = (' + || a_type || '), Value (WATCHES.CommID) = (' || a_commid || ')'; @@ -971,6 +991,7 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () a_wid watches.wid%TYPE; a_focal watches.focal%TYPE; a_date watches.date%TYPE; + a_type watches.type%TYPE; a_commid watches.commid%TYPE; -- GROOMINGS @@ -1018,8 +1039,10 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () SELECT events.behavior ,events.start, events.stop , watches.wid, watches.focal, watches.date, watches.commid + , watches.type INTO a_behavior , a_start , a_stop , a_wid , a_focal , a_date , a_commid + , a_type FROM events JOIN watches ON (watches.wid = events.wid) WHERE events.eid = OLD.eid; @@ -1054,6 +1077,8 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () || a_focal || '), Value (WATCHES.Date) = (' || a_date + || '), Value (WATCHES.Type) = (' + || a_type || '), Value (WATCHES.CommID) = (' || a_commid || ')'; @@ -1081,8 +1106,10 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () IF mutual_count = 1 THEN SELECT events.behavior ,events.start, events.stop , watches.wid, watches.focal, watches.date, watches.commid + , watches.type INTO a_behavior , a_start , a_stop , a_wid , a_focal , a_date , a_commid + , a_type FROM events JOIN watches ON (watches.wid = events.wid) WHERE events.eid = OLD.eid; @@ -1113,6 +1140,8 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () || a_focal || '), Value (WATCHES.Date) = (' || a_date + || '), Value (WATCHES.Type) = (' + || a_type || '), Value (WATCHES.CommID) = (' || a_commid || ')'; diff --git a/db/schemas/lib/triggers/create/watches.m4 b/db/schemas/lib/triggers/create/watches.m4 index a79575c..27df462 100644 --- a/db/schemas/lib/triggers/create/watches.m4 +++ b/db/schemas/lib/triggers/create/watches.m4 @@ -49,6 +49,9 @@ CREATE OR REPLACE FUNCTION watches_func () IF TG_OP = 'UPDATE' THEN -- Allowing the WID to change makes life too complicated. cannot_change(`WATCHES', `WID') + + -- Likewise, the Type cannot change. + cannot_change(`WATCHES', `Type') END IF; -- Cannot have a follow date before the individual was studied @@ -655,78 +658,84 @@ CREATE OR REPLACE FUNCTION watches_insert_commit_func () BEGIN -- Function for watches insert trigger fired on transaction commit -- - -- AGPL_notice(` --', `2025', + -- AGPL_notice(` --', `2025, 2026', `The Meme Factory, Inc., www.karlpinc.com') - -- A row must exist on FOLLOW_OBSERVERS - PERFORM 1 - FROM follow_observers - WHERE follow_observers.wid = NEW.wid; - IF NOT FOUND THEN - RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'Error on INSERT of WATCHES' - , DETAIL = 'A row on WATCHES must have a related row on' - || ' FOLLOW_OBSERVERS' - || ': Key (WID) = (' - || NEW.wid - || '), Value (Focal) = (' - || NEW.focal - || '), Value (Date) = (' - || NEW.Date - || '), Value (CommID) = (' - || NEW.commid - || ')'; - END IF; - - IF TG_OP = 'UPDATE' - AND NEW.focal <> OLD.focal THEN - -- One of the individuals grooming must be the focal, and be either - -- the sdb_actor or the sdb_actee. + -- + -- Validate follows + -- + IF NEW.Type = 'sdb_follow' THEN - DECLARE - -- EVENTS - a_eid events.eid%TYPE; - a_behavior events.behavior%TYPE; - a_start events.start%TYPE; - a_stop events.stop%TYPE; + -- A row must exist on FOLLOW_OBSERVERS + PERFORM 1 + FROM follow_observers + WHERE follow_observers.wid = NEW.wid; + IF NOT FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on INSERT of WATCHES' + , DETAIL = 'A row on WATCHES must have a related row on' + || ' FOLLOW_OBSERVERS' + || ': Key (WID) = (' + || NEW.wid + || '), Value (Focal) = (' + || NEW.focal + || '), Value (Date) = (' + || NEW.Date + || '), Value (CommID) = (' + || NEW.commid + || ')'; + END IF; - BEGIN - SELECT events.eid, events.behavior, events.start, events.stop - INTO a_eid , a_behavior , a_start , a_stop - FROM events - WHERE events.wid = NEW.wid - AND events.behavior = 'sdb_grooming' - AND NOT EXISTS - (SELECT 1 - FROM roles - WHERE roles.eid = events.eid - 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 WATCHES' - , DETAIL = 'One of the individuals involved in the grooming event' - || ' event must be the focal, who must be either the' - || ' individual doing the grooming or the individual' - || ' being groomed' - || ': Key (WID) = (' - || NEW.wid - || '), Value (Focal) = (' - || NEW.focal - || '), Value (Date) = (' - || NEW.date - || '): Key (EVENTS.EID) = (' - || a_eid - || '): Value (EVENTS.Behavior) = (' - || a_behavior - || '), Value (EVENTS.Start) = (' - || a_start - || '), Value (EVENTS.Stop) = (' - || a_stop - || ')'; - END IF; - END; + IF TG_OP = 'UPDATE' + AND NEW.focal <> OLD.focal THEN + -- One of the individuals grooming must be the focal, and be either + -- the sdb_actor or the sdb_actee. + + DECLARE + -- EVENTS + a_eid events.eid%TYPE; + a_behavior events.behavior%TYPE; + a_start events.start%TYPE; + a_stop events.stop%TYPE; + + BEGIN + SELECT events.eid, events.behavior, events.start, events.stop + INTO a_eid , a_behavior , a_start , a_stop + FROM events + WHERE events.wid = NEW.wid + AND events.behavior = 'sdb_grooming' + AND NOT EXISTS + (SELECT 1 + FROM roles + WHERE roles.eid = events.eid + 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 WATCHES' + , DETAIL = 'One of the individuals involved in the grooming event' + || ' event must be the focal, who must be either the' + || ' individual doing the grooming or the individual' + || ' being groomed' + || ': Key (WID) = (' + || NEW.wid + || '), Value (Focal) = (' + || NEW.focal + || '), Value (Date) = (' + || NEW.date + || '): Key (EVENTS.EID) = (' + || a_eid + || '): Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || ')'; + END IF; + END; + END IF; END IF; RETURN NULL; diff --git a/db/schemas/sokwedb/indexes/create/watches.m4 b/db/schemas/sokwedb/indexes/create/watches.m4 index fb21926..07e324d 100644 --- a/db/schemas/sokwedb/indexes/create/watches.m4 +++ b/db/schemas/sokwedb/indexes/create/watches.m4 @@ -22,9 +22,10 @@ include(`constants.m4')dnl include(`indexmacros.m4')dnl CREATE UNIQUE INDEX IF NOT EXISTS - "On WATCHES, Date + Focal must be unique" + "On WATCHES where (Type)=(sdb_follow), Date + Focal must be unique" ON watches - (date, focal); + (date, focal) + WHERE type = 'sdb_follow'; CREATE INDEX IF NOT EXISTS watches_focal ON watches (focal); diff --git a/db/schemas/sokwedb/indexes/drop/watches.m4 b/db/schemas/sokwedb/indexes/drop/watches.m4 index 33e49be..85dd39d 100644 --- a/db/schemas/sokwedb/indexes/drop/watches.m4 +++ b/db/schemas/sokwedb/indexes/drop/watches.m4 @@ -23,7 +23,7 @@ include(`indexmacros.m4')dnl DROP INDEX IF EXISTS - "On WATCHES, Date + Focal must be unique"; + "On WATCHES where (Type)=(sdb_follow), Date + Focal must be unique"; DROP INDEX IF EXISTS watches_focal; DROP INDEX IF EXISTS watches_commid; diff --git a/db/schemas/sokwedb/tables/create/events.m4 b/db/schemas/sokwedb/tables/create/events.m4 index 3ad6215..d436907 100644 --- a/db/schemas/sokwedb/tables/create/events.m4 +++ b/db/schemas/sokwedb/tables/create/events.m4 @@ -123,14 +123,6 @@ CREATE TABLE events ( _point_behavior_time(`sdb_groom_scan_a') _point_behavior_time(`sdb_pantgrunt') - _pair_behavior_source(`sdb_aggression', `WID') - _pair_behavior_source(`sdb_arrival', `WID') - _pair_behavior_source(`sdb_food', `WID') - _pair_behavior_source(`sdb_grooming', `WID') - _pair_behavior_source(`sdb_groom_scan', `WID') - _pair_behavior_source(`sdb_other_species', `WID') - _pair_behavior_source(`sdb_pantgrunt', `WID') - _pair_behavior_source(`sdb_arrival_a', `AtID') _pair_behavior_source(`sdb_groom_scan_a', `AtID') diff --git a/doc/src/tables/events.m4 b/doc/src/tables/events.m4 index 9e7b9ba..0340571 100644 --- a/doc/src/tables/events.m4 +++ b/doc/src/tables/events.m4 @@ -112,7 +112,8 @@ The following table lists these rules and implications: ``sdb_aggression`` (Aggression) The EVENTS row must be associated with a follow. - This means the |EVENTS.WID| column must not be |null|. + This means the |EVENTS.WID| column must reference a |WATCHES| row + with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_follow``. A related row should exist on |AGGRESSIONS|; there should be a row on |AGGRESSIONS| with an |AGGRESSIONS|.\ |AGGRESSIONS.EID| value of @@ -194,7 +195,8 @@ The following table lists these rules and implications: ``sdb_arrival`` (Arrival) The EVENTS row must be associated with a follow. - This means the |EVENTS.WID| column must not be |null|. + This means the |EVENTS.WID| column must reference a |WATCHES| row + with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_follow``. A related row should exist on |ARRIVALS|; there should be a row on |ARRIVALS| with an |ARRIVALS|.\ |ARRIVALS.EID| value of the event's @@ -219,7 +221,8 @@ The following table lists these rules and implications: ``sdb_food`` (Food) The EVENTS row must be associated with a follow. - This means the |EVENTS.WID| column must not be |null|. + This means the |EVENTS.WID| column must reference a |WATCHES| row + with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_follow``. A related row should exist on |FOOD_EVENTS|; there should be a row on |FOOD_EVENTS| with a |FOOD_EVENTS|.\ |FOOD_EVENTS.EID| value of @@ -253,7 +256,8 @@ The following table lists these rules and implications: ``sdb_grooming`` (Grooming) The EVENTS row must be associated with a follow. - This means the |EVENTS.WID| column must not be |null|. + This means the |EVENTS.WID| column must reference a |WATCHES| row + with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_follow``. A related row should exist on |GROOMINGS|; there should be a row on |GROOMINGS| with a |GROOMINGS|.\ |GROOMINGS.EID| value of the @@ -299,7 +303,8 @@ The following table lists these rules and implications: ``sdb_groom_scan`` (SCAN interval Groomings) The EVENTS row must be associated with a follow. - This means the |EVENTS.WID| column must not be |null|. + This means the |EVENTS.WID| column must reference a |WATCHES| row + with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_follow``. A related row should exist on |GROOM_SCANS_B|; there should be a row on |GROOM_SCANS_B| with an |GROOM_SCANS_B|.\ |GROOM_SCANS_B.EID| value of @@ -348,7 +353,8 @@ The following table lists these rules and implications: ``sdb_other_species`` (Other Species) The EVENTS row must be associated with a follow. - This means the |EVENTS.WID| column must not be |null|. + This means the |EVENTS.WID| column must reference a |WATCHES| row + with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_follow``. A related row should exist on either |SPECIES_PRESENT| or on |HUMANS|. @@ -371,7 +377,8 @@ The following table lists these rules and implications: ``sdb_pantgrunt`` (Pantgrunt) The EVENTS row must be associated with a follow. - This means the |EVENTS.WID| column must not be |null|. + This means the |EVENTS.WID| column must reference a |WATCHES| row + with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_follow``. A related row should exist on |PANTGRUNTS|; there should be a row on |PANTGRUNTS| with a |PANTGRUNTS|.\ |PANTGRUNTS.EID| value of the diff --git a/doc/src/tables/follow_observers.m4 b/doc/src/tables/follow_observers.m4 index 01ad65f..a18159e 100644 --- a/doc/src/tables/follow_observers.m4 +++ b/doc/src/tables/follow_observers.m4 @@ -35,6 +35,10 @@ column). There are no data validation rules that prevent the BRec observer from being the same observer as the Tiki observer. +The observers can only have observed a follow. +This means that the |FOLLOW_OBSERVERS.WID| must be that of a |WATCHES| +row with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_follow``. + More than two observers may be related to a follow by creating multiple FOLLOW_OBSERVERS rows that are related to a single row in the |WATCHES| table. diff --git a/doc/src/tables/watches.m4 b/doc/src/tables/watches.m4 index d32a3be..20da4fa 100644 --- a/doc/src/tables/watches.m4 +++ b/doc/src/tables/watches.m4 @@ -25,28 +25,72 @@ WATCHES ------- .. |WATCHES_summary| replace:: - Each row represents a scheduled time during which observers are assigned - to follow a focal individual. -|WATCHES_summary| A scheduled follow may or may not have any related -observations, related |EVENTS| rows. + WATCHES contains one row, per date, per individual, whenever there + was an intention to observe a given individual on a given date. + It also contains one row for every date there was an ad-hoc + observation *not* associated with any particular individual. + There is not a row for every day every individual was observed + because SokweDB contains data on individuals collected due to + proximity with the intended target of observation. + +|WATCHES_summary| + +Examples of intended observations include the a follow of a focal +and the regular observation of individuals at the feeding +station. +WATCHES contains one row for each follow, and contains one row per +day, per individual observed at the feeding station. + +Examples of ad-hoc observations not associated with any one individual +are observations of pantgrunts recorded during happenstance +encounters. + +The |WATCHES.Type| column is used to distinguish and identify the +various important categories: follows, feeding station observations, +etc. +Each |WATCHES.Type| has it's own set of requirements that related data +must conform to. +The available |WATCHES.Type| values are: + +``sdb_follow`` (Follow) + Each row represents a date during which observers were assigned + to follow a focal individual. -The combination of |WATCHES.Focal| and |WATCHES.Date| must be unique. + A follow may or may not have any related observations, related + |EVENTS| rows. -The system will generate a warning when a WATCHES row does not have a -|ARRIVALS| related row that records the presence of the focal: does -not have a related |EVENTS| row where the |EVENTS.Behavior| code is -``sdb_arrival``, and a |ROLES| row, related to the event, where the -|ROLES|.\ |ROLES.Participant| has the same value as the follow's -|WATCHES.Focal| column. + The |WATCHES.Focal| column contains the |BIOGRAPHY_DATA|.\ + |BIOGRAPHY_DATA.AnimID| of the focal of the follow. -A scheduled follow must have at least one related |FOLLOW_OBSERVERS| -rows, to record the observers scheduled to conduct the follow. -|transaction commit| + The |WATCHES.CommID| column contains the code for the community + the observers are associated with and intended to follow; the + |COMM_IDS|.\ |COMM_IDS.CommID| of the community. + This is not necessarily the community the focal is a member of, + although it usually is. -The |WATCHES.Date| must be during a period when the focal individual -was under study, on or after the focal's |BIOGRAPHY_DATA|.\ -|BIOGRAPHY_DATA.EntryDate| and on or before the focal's + The system will generate a warning when a WATCHES row that + represents a follow does not have a |ARRIVALS| related row that + records the presence of the focal. + This means that warning is generated when a WATCHES row with a + |WATCHES.Type| value of ``sdb_follow`` does not have a related + |EVENTS| row where the |EVENTS.Behavior| code is ``sdb_arrival``, + and a |ROLES| row, related to the event, where the |ROLES|.\ + |ROLES.Participant| has the same value as the follow's + |WATCHES.Focal| column. + + A follow must have at least one related |FOLLOW_OBSERVERS| rows, to + record the observers scheduled to conduct the follow. + |transaction commit| + + The combination of |WATCHES.Type|, |WATCHES.Focal| and + |WATCHES.Date| must be unique. + +The |WATCHES.Date| must be during a period when the watched individual +was under study. +This means that |WATCHES.Date| must be on or after the watched +individual's -- the |WATCHES.Focal|'s -- |BIOGRAPHY_DATA|.\ +|BIOGRAPHY_DATA.EntryDate| and on or before the watched individual's |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.DepartDate|. .. contents:: @@ -68,11 +112,17 @@ WID (Watch period ID) Focal (Focal animal id) ``````````````````````` -.. |WATCHES.Focal_summary| replace:: The |BIOGRAPHY_DATA|.\ - |BIOGRAPHY_DATA.AnimID| identifying the individual intended - to be the focal under observation for the follow. +.. |WATCHES.Focal_summary| replace:: -|WATCHES.Focal_summary| |notnull| + The |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| identifying the + individual under observation. + +|WATCHES.Focal_summary| + +The designated individual is the focal, or the individual observed at +the feeding station, etc., depending on the |WATCHES.Type| value. + +|notnull| .. _WATCHES.CommID: @@ -82,11 +132,10 @@ CommID .. |WATCHES.CommID_summary| replace:: - A code for the community the observers are associated with and - intended to follow; the |COMM_IDS|.\ |COMM_IDS.CommID| of the - community. - This is not necessarily the community the focal is a member of, - although it usually is. + A code for the community the observers have recorded for association + with the watch period. + This is not necessarily the community the |WATCHES.Focal| is a + member of, although it usually is. |WATCHES.CommID_summary| |cannot_change| |notnull| @@ -97,7 +146,7 @@ Date ```` .. |WATCHES.Date_summary| replace:: - The date the follow was conducted. + The date when the watch period was conducted. |WATCHES.Date_summary| This date may not be before ``sdb_min_follow_date``. @@ -115,9 +164,13 @@ Type |WATCHES.Type_summary| One of the following values:: - ``sdb_follow`` -|notnull| + ``sdb_follow`` (Follow) + +See :ref:`overview of the WATCHES table `, above, for more +information. + +|cannot_change| |notnull| .. _WATCHES.Notes: @@ -127,6 +180,6 @@ Notes .. |WATCHES.Notes_summary| replace:: - Free form textual notes on the follow. + Free form textual notes on the watch period. |WATCHES.Notes_summary| |notonlyspaces| |notnull| -- 2.34.1