From a4a934da31282bfcf8ec3828e4adb1d09a5b8032 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Mon, 15 Jun 2026 13:01:18 +0000 Subject: [PATCH] Drop the ATTENDANCE table, use a WATCHES.Type of sdb_attendance instead --- .../lib/triggers/create/biography_data.m4 | 79 +----- db/schemas/lib/triggers/create/events.m4 | 115 ++++---- db/schemas/lib/triggers/create/roles.m4 | 172 +++--------- db/schemas/sokwedb/indexes/create/events.m4 | 6 +- db/schemas/sokwedb/indexes/create/watches.m4 | 6 + db/schemas/sokwedb/indexes/drop/events.m4 | 1 - db/schemas/sokwedb/indexes/drop/watches.m4 | 3 + db/schemas/sokwedb/tables/create/events.m4 | 31 --- doc/diagrams/watches.svg | 253 ++---------------- doc/src/epilog.inc.m4 | 13 - doc/src/tables.m4 | 1 - doc/src/tables/attendance.m4 | 134 ---------- doc/src/tables/events.m4 | 60 ++--- doc/src/tables/roles.m4 | 39 ++- doc/src/tables/watches.m4 | 72 ++++- include/global_constants.m4 | 1 + 16 files changed, 246 insertions(+), 740 deletions(-) delete mode 100644 doc/src/tables/attendance.m4 diff --git a/db/schemas/lib/triggers/create/biography_data.m4 b/db/schemas/lib/triggers/create/biography_data.m4 index 7646483..1d74698 100644 --- a/db/schemas/lib/triggers/create/biography_data.m4 +++ b/db/schemas/lib/triggers/create/biography_data.m4 @@ -436,6 +436,7 @@ CREATE OR REPLACE FUNCTION biography_data_func () -- Other table checking -- IF TG_OP = 'UPDATE' THEN + -- WATCHES DECLARE a_wid watches.wid%TYPE; @@ -443,7 +444,7 @@ CREATE OR REPLACE FUNCTION biography_data_func () a_date watches.date%TYPE; a_type watches.type%TYPE; BEGIN - -- Cannot have a follow before the individual was studied. + -- Cannot have a watch period before the individual was studied. IF NEW.entrydate <> OLD.entrydate THEN SELECT watches.wid, watches.commid, watches.date, watches.type INTO a_wid , a_commid , a_date , a_type @@ -454,8 +455,9 @@ CREATE OR REPLACE FUNCTION biography_data_func () IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' - , DETAIL = 'An individual cannot be followed before they are' - || ' under study' + , DETAIL = 'An individual cannot be watched -- be followed,' + || ' or have an attendance record, etc. -- before' + || ' they are under study' || ': Key (Animid) = (' || NEW.animid || '), Value (EntryDate) = (' @@ -472,7 +474,7 @@ CREATE OR REPLACE FUNCTION biography_data_func () END IF; END IF; - -- Cannot have a follow after study of the individual has ceased. + -- Cannot have a watch period after study of the individual has ceased. IF NEW.departdate <> OLD.departdate THEN SELECT watches.wid, watches.commid, watches.date, watches.type INTO a_wid , a_commid , a_date , a_type @@ -483,8 +485,9 @@ CREATE OR REPLACE FUNCTION biography_data_func () IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' - , DETAIL = 'An individual cannot be followed after they are' - || ' no longer under study' + , DETAIL = 'An individual cannot be watched -- be followed,' + || ' or have an attendance record, etc. -- after' + || ' they are no longer under study' || ': Key (Animid) = (' || NEW.animid || '), Value (DepartDate) = (' @@ -502,70 +505,6 @@ CREATE OR REPLACE FUNCTION biography_data_func () END IF; END; - -- ATTENDANCE - DECLARE - a_atid attendance.atid%TYPE; - a_date attendance.date%TYPE; - a_commid attendance.commid%TYPE; - - a_start events.start%TYPE; - a_stop events.stop%TYPE; - BEGIN - -- Cannot have a record of being at the feeding station before - -- the individual was studied. - IF NEW.entrydate <> OLD.entrydate THEN - SELECT attendance.atid, attendance.date, attendance.commid - INTO a_atid , a_date , a_commid - FROM attendance - WHERE attendance.animid = NEW.animid - AND attendance.date < NEW.entrydate - ORDER BY attendance.date; -- consistency - IF FOUND THEN - RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' - , DETAIL = 'An individual cannot be at the feeding station' - || ' before they are under study' - || ': Key (Animid) = (' - || NEW.animid - || '), Value (EntryDate) = (' - || NEW.entrydate - || ': Key (ATTENDANCE.AtID) = (' - || a_atid - || '), Value (ATTENDANCE.Date) = (' - || a_date - || '), Value (ATTENDANCE.Commid) = (' - || a_commid - || ')'; - END IF; - END IF; - - IF NEW.departdate <> OLD.departdate THEN - SELECT attendance.atid, attendance.date, attendance.commid - INTO a_atid , a_date , a_commid - FROM attendance - WHERE attendance.animid = NEW.animid - AND NEW.departdate < attendance.date - ORDER BY attendance.date; -- consistency - IF FOUND THEN - RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' - , DETAIL = 'An individual cannot be at the feeding station' - || ' after they are no longer under study' - || ': Key (Animid) = (' - || NEW.animid - || '), Value (DepartDate) = (' - || NEW.departdate - || ': Key (ATTENDANCE.AtID) = (' - || a_atid - || '), Value (ATTENDANCE.Date) = (' - || a_date - || '), Value (ATTENDANCE.Commid) = (' - || a_commid - || ')'; - END IF; - END IF; - END; - -- LOCATIONS_B DECLARE a_lbid locations_b.lbid%TYPE; diff --git a/db/schemas/lib/triggers/create/events.m4 b/db/schemas/lib/triggers/create/events.m4 index 73a2003..fa3c969 100644 --- a/db/schemas/lib/triggers/create/events.m4 +++ b/db/schemas/lib/triggers/create/events.m4 @@ -48,9 +48,6 @@ CREATE OR REPLACE FUNCTION events_func () -- against biography. Simpler to dis-allow change. cannot_change(`EVENTS', `WID') - -- Likewise, allowing the AtID to change adds too much complication. - cannot_change(`EVENTS', `AtID') - -- If the Behavior is allowed to change, we need to add code here -- that checks all the tables related to the participants and the -- various event detail tables. This is way too much code; it is @@ -67,15 +64,7 @@ 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. + IF TG_OP = 'INSERT' THEN DECLARE a_focal watches.focal%TYPE; a_commid watches.commid%TYPE; @@ -83,47 +72,69 @@ CREATE OR REPLACE FUNCTION events_func () a_type watches.type%TYPE; a_notes watches.notes%TYPE; + watch_type watches.type%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; + -- The event's behavior must match the watch type + -- (follow, attendance, etc.). + + IF 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. + watch_type := 'sdb_follow'; + ELSE + -- NEW.behavior is sdb_arrival_a or sdb_groom_scan_a + -- The event must be related to a feeding station attendance record. + watch_type := 'sdb_attendance'; + END IF; + + 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 <> watch_type; + 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) = (' + || watch_type + || '): 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; diff --git a/db/schemas/lib/triggers/create/roles.m4 b/db/schemas/lib/triggers/create/roles.m4 index 9c74b93..6da736e 100644 --- a/db/schemas/lib/triggers/create/roles.m4 +++ b/db/schemas/lib/triggers/create/roles.m4 @@ -34,7 +34,6 @@ CREATE OR REPLACE FUNCTION roles_func () a_start events.start%TYPE; a_stop events.stop%TYPE; a_wid events.wid%TYPE; - a_atid events.atid%TYPE; BEGIN -- Function for roles insert and update triggers @@ -66,8 +65,8 @@ CREATE OR REPLACE FUNCTION roles_func () -- 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.wid, events.atid - INTO a_behavior , a_start , a_stop , a_wid , a_atid + SELECT events.behavior ,events.start, events.stop, events.wid + INTO a_behavior , a_start , a_stop , a_wid FROM events WHERE events.eid = NEW.eid; END IF; @@ -228,13 +227,10 @@ CREATE OR REPLACE FUNCTION roles_func () a_participant roles.participant%TYPE; a_focal watches.focal%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 SELECT roles.pid, roles.role, roles.participant INTO a_pid , a_role , a_participant @@ -243,37 +239,10 @@ CREATE OR REPLACE FUNCTION roles_func () AND roles.pid <> NEW.pid; IF FOUND THEN - IF a_behavior = 'sdb_arrival_a' THEN - SELECT attendance.animid, attendance.date, attendance.commid - INTO a_animid , a_date , a_commid - FROM attendance - WHERE attendance.atid = a_atid; - - observation_msg := 'Value (ATTENDANCE.AtID) = (' - || a_atid - || '), Value (ATTENDANCE.AnimID) = (' - || a_animid - || '), Value (ATTENDANCE.Date) = (' - || a_date - || '), Value (ATTENDANCE.CommID) = (' - || a_commid - || ')'; - ELSE -- a_behavior is sdb_arrival or sdb_food - SELECT watches.focal, watches.date, watches.commid - INTO a_focal , a_date , a_commid - FROM watches - WHERE watches.wid = a_wid; - - observation_msg := 'Value (WATCHES.WID) = (' - || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal - || '), Value (WATCHES.Date) = (' - || a_date - || '), Value (WATCHES.CommID) = (' - || a_commid - || ')'; - END IF; + SELECT watches.focal, watches.date, watches.commid + INTO a_focal , a_date , a_commid + FROM watches + WHERE watches.wid = a_wid; RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on INSERT of ROLES' @@ -302,8 +271,15 @@ CREATE OR REPLACE FUNCTION roles_func () || a_start || '), Value (EVENTS.Stop) = (' || a_stop - || '), ' - || observation_msg; + || '), Value (WATCHES.WID) = (' + || a_wid + || '), Value (WATCHES.Focal) = (' + || a_focal + || '), Value (WATCHES.Date) = (' + || a_date + || '), Value (WATCHES.CommID) = (' + || a_commid + || ')'; END IF; END; END IF; @@ -322,13 +298,10 @@ CREATE OR REPLACE FUNCTION roles_func () a_participant roles.participant%TYPE; a_focal watches.focal%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 -- -- Validate ROLES.Role for aggression and grooming events @@ -388,37 +361,10 @@ CREATE OR REPLACE FUNCTION roles_func () 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 watches.wid = a_wid; - - 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 watches.focal, watches.date, watches.commid - INTO a_focal , a_date , a_commid - FROM watches - WHERE watches.wid = a_wid; - observation_msg := 'Value (WATCHES.WID) = (' - || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal - || '), Value (WATCHES.Date) = (' - || a_date - || '), Value (WATCHES.CommID) = (' - || a_commid - || ')'; - END IF; + SELECT watches.focal, watches.date, watches.commid + INTO a_focal , a_date , a_commid + FROM watches + WHERE watches.wid = a_wid; RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on INSERT of ROLES' @@ -450,8 +396,15 @@ CREATE OR REPLACE FUNCTION roles_func () || a_start || '), Value (EVENTS.Stop) = (' || a_stop - || '), ' - || observation_msg; + || '), Value (WATCHES.WID) = (' + || a_wid + || '), Value (WATCHES.Focal) = (' + || a_focal + || '), Value (WATCHES.Date) = (' + || a_date + || '), Value (WATCHES.CommID) = (' + || a_commid + || ')'; END IF; END; END IF; @@ -504,64 +457,12 @@ CREATE OR REPLACE FUNCTION roles_func () END IF; -- - -- The only roles allowed for feeding station groom scans are - -- sdb_actor, sdb_actee, and sdb_mutual. + -- The only roles allowed for follow pantgrunts and 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; - - -- - -- The only roles allowed for follow pantgrunts are - --- sdb_actor, sdb_actee, and sdb_mutual. - -- - IF TG_OP = 'INSERT' - AND a_behavior = 'sdb_pantgrunt' + AND (a_behavior = 'sdb_pantgrunt' + OR a_behavior = 'sdb_groom_scan_a') AND NEW.role <> 'sdb_actor' AND NEW.role <> 'sdb_actee' AND NEW.role <> 'sdb_mutual' THEN @@ -577,7 +478,9 @@ CREATE OR REPLACE FUNCTION roles_func () WHERE watches.wid = a_wid; RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on INSERT of ROLES' - , DETAIL = 'Invalid Roles value for a sdb_pantgrunt event' + , DETAIL = 'Invalid Roles value for a ' + || a_behavior + || ' event' || ': Key (PID) = (' || NEW.pid || '), Value (EID) = (' @@ -601,8 +504,9 @@ CREATE OR REPLACE FUNCTION roles_func () || '), Value (WATCHES.CommID) = (' || a_commid || ')' - , HINT = 'When EVENTS.Behavior = (sdb_pantgrunt) the only' - || ' ROLES.Role values allowed are: sdb_actor,' + , HINT = 'When EVENTS.Behavior = (' + || a_behavoir + || ') the only ROLES.Role values allowed are: sdb_actor,' || ' sdb_actee, and sdb_mutual'; END; END IF; diff --git a/db/schemas/sokwedb/indexes/create/events.m4 b/db/schemas/sokwedb/indexes/create/events.m4 index d5e5d94..6a17b9d 100644 --- a/db/schemas/sokwedb/indexes/create/events.m4 +++ b/db/schemas/sokwedb/indexes/create/events.m4 @@ -22,11 +22,7 @@ include(`constants.m4')dnl include(`indexmacros.m4')dnl CREATE INDEX IF NOT EXISTS events_wid ON events - (wid) - WHERE wid IS NOT NULL; -CREATE INDEX IF NOT EXISTS events_atid ON events - (atid) - WHERE atid IS NOT NULL; + (wid); CREATE INDEX IF NOT EXISTS events_behavior ON events (behavior); CREATE INDEX IF NOT EXISTS events_start ON events diff --git a/db/schemas/sokwedb/indexes/create/watches.m4 b/db/schemas/sokwedb/indexes/create/watches.m4 index 9054145..dbd50c4 100644 --- a/db/schemas/sokwedb/indexes/create/watches.m4 +++ b/db/schemas/sokwedb/indexes/create/watches.m4 @@ -27,6 +27,12 @@ CREATE UNIQUE INDEX IF NOT EXISTS (date, focal) WHERE type = 'sdb_follow'; +CREATE UNIQUE INDEX IF NOT EXISTS + "On WATCHES where (Type)=(sdb_attendance), Date + Focal must be unique" + ON watches + (date, focal) + WHERE type = 'sdb_attendance'; + CREATE INDEX IF NOT EXISTS watches_date_focal_type ON watches (date, focal, type); CREATE INDEX IF NOT EXISTS watches_focal ON watches diff --git a/db/schemas/sokwedb/indexes/drop/events.m4 b/db/schemas/sokwedb/indexes/drop/events.m4 index 8c583b5..cffa127 100644 --- a/db/schemas/sokwedb/indexes/drop/events.m4 +++ b/db/schemas/sokwedb/indexes/drop/events.m4 @@ -23,7 +23,6 @@ include(`indexmacros.m4')dnl DROP INDEX IF EXISTS events_wid; -DROP INDEX IF EXISTS events_atid; DROP INDEX IF EXISTS events_behavior; DROP INDEX IF EXISTS events_start; DROP INDEX IF EXISTS events_stop; diff --git a/db/schemas/sokwedb/indexes/drop/watches.m4 b/db/schemas/sokwedb/indexes/drop/watches.m4 index db7cd34..c5fb7e7 100644 --- a/db/schemas/sokwedb/indexes/drop/watches.m4 +++ b/db/schemas/sokwedb/indexes/drop/watches.m4 @@ -25,6 +25,9 @@ include(`indexmacros.m4')dnl DROP INDEX IF EXISTS "On WATCHES where (Type)=(sdb_follow), Date + Focal must be unique"; +DROP INDEX IF EXISTS + "On WATCHES where (Type)=(sdb_attendance), Date + Focal must be unique"; + DROP INDEX IF EXISTS watches_date_focal_type; 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 d436907..70ce377 100644 --- a/db/schemas/sokwedb/tables/create/events.m4 +++ b/db/schemas/sokwedb/tables/create/events.m4 @@ -23,31 +23,6 @@ include(`tablemacros.m4')dnl include(`grants.m4')dnl dnl -dnl Macro to ensure behavior codes match with their source, either a follow -dnl or a feeding station attendance record. -dnl -dnl Syntax: _pair_behavior_source(behavior, source) -dnl -dnl Input: -dnl behavior An EVENTS.Behavior value -dnl source Either WID, for follow behaviors, or AtID for feeding -dnl station attendance behaviors. -dnl -dnl Remarks: -dnl This macro exists because there is a 63 character limit on the -dnl constraint names. All the behavior codes won't fit into a single -dnl constraint name or we'd have a single constraint for WID and -dnl another for AtID. The constraint name is used to tell the end-user -dnl what the problem is. So "cheat" and use separate constraints. -dnl (This is simpler to maintain in any case, and we don't care about -dnl efficiency.) -changequote([,]) -define([_pair_behavior_source], [CONSTRAINT - "The ($1) Behavior requires that $2 not be NULL" - CHECK(behavior <> '$1' - OR $2 IS NOT NULL)])dnl -changequote(`,')dnl See above - dnl Macro to ensure particular behavior codes enforce Start = Stop dnl dnl Syntax: _point_behavior_time(behavior) @@ -87,8 +62,6 @@ CREATE TABLE events ( key_column(`EVENTS', `EID', INTEGER) ,wid INTEGER REFERENCES watches - ,atid INTEGER - REFERENCES attendance ,behavior TEXT NOT NULL CONSTRAINT "Behavior must be one of the allowed special values" CHECK (behavior IN ('sdb_aggression' @@ -116,16 +89,12 @@ CREATE TABLE events ( CONSTRAINT "Start cannot be after Stop" CHECK (start <= stop) - null_xor_null(`WID', `AtID') _point_behavior_time(`sdb_aggression') _point_behavior_time(`sdb_groom_scan') _point_behavior_time(`sdb_groom_scan_a') _point_behavior_time(`sdb_pantgrunt') - _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') diff --git a/doc/diagrams/watches.svg b/doc/diagrams/watches.svg index 84978fe..b6425fa 100644 --- a/doc/diagrams/watches.svg +++ b/doc/diagrams/watches.svg @@ -688,9 +688,9 @@ borderopacity="1.0" inkscape:pageopacity="0.0" inkscape:pageshadow="2" - inkscape:zoom="2.5523998" - inkscape:cx="376.9002" - inkscape:cy="507.95334" + inkscape:zoom="3.6096384" + inkscape:cx="576.51204" + inkscape:cy="443.11918" inkscape:document-units="mm" inkscape:current-layer="layer3" inkscape:document-rotation="0" @@ -2015,21 +2015,20 @@ x="133.2" y="114.42921" />AtID ∅BehaviorWID ∅WIDBehaviorStartStartStopStopCertaintyCertaintyNotesNotesATTENDANCEAtIDAnimIDDateCommID *Individualattendingthe feedingstationEvents of the feedingstation attendancerecordRecords ofindividual'sfeedingstationatttendanceThe event's feedingstation atttendancerecord + clip-path="none" /> diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index 28da192..3180652 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -164,17 +164,6 @@ sdb_generated_rst()dnl .. |ARRIVAL_SOURCES.Description| replace:: :ref:`Description ` -.. |ATTENDANCE| - replace:: :ref:`ATTENDANCE ` -.. |ATTENDANCE.AtID| replace:: - :ref:`AtID ` -.. |ATTENDANCE.AnimID| replace:: - :ref:`AnimID ` -.. |ATTENDANCE.Date| replace:: - :ref:`Date ` -.. |ATTENDANCE.CommID| replace:: - :ref:`CommID ` - .. |BIOGRAPHY_DATA| replace:: :ref:`BIOGRAPHY_DATA ` .. |BIOGRAPHY_DATA.AnimID| replace:: @@ -327,8 +316,6 @@ sdb_generated_rst()dnl :ref:`EID ` .. |EVENTS.WID| replace:: :ref:`WID ` -.. |EVENTS.AtID| replace:: - :ref:`AtID ` .. |EVENTS.BEHAVIOR| replace:: :ref:`Behavior ` .. |EVENTS.Start| replace:: diff --git a/doc/src/tables.m4 b/doc/src/tables.m4 index b481f87..8c36418 100644 --- a/doc/src/tables.m4 +++ b/doc/src/tables.m4 @@ -36,7 +36,6 @@ and are therefore the result of at least a rudimentary analytical process. tables/aggressions.rst tables/arrivals.rst tables/arrivals_a.rst - tables/attendance.rst tables/biography_data.rst tables/biography_log.rst tables/comm_membs.rst diff --git a/doc/src/tables/attendance.m4 b/doc/src/tables/attendance.m4 deleted file mode 100644 index 578f971..0000000 --- a/doc/src/tables/attendance.m4 +++ /dev/null @@ -1,134 +0,0 @@ -.. Copyright (C) 2026 The Meme Factory, Inc. www.karlpinc.com - - This program is free software: you can redistribute it and/or modify - it under the terms of the GNU Affero General Public License as - published by the Free Software Foundation, either version 3 of the - License, or (at your option) any later version. - - This program is distributed in the hope that it will be useful, - but WITHOUT ANY WARRANTY; without even the implied warranty of - MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the - GNU Affero General Public License for more details. - - You should have received a copy of the GNU Affero General Public License - along with this program. If not, see . - -.. M4 setup -include(constants.m4)dnl -include(macros.m4)dnl -sdb_rst_quotes(`on')dnl -sdb_generated_rst()dnl - -.. _ATTENDANCE: - -ATTENDANCE ----------- - -.. |ATTENDANCE_summary| replace:: - Each row represents a day during which a chimpanzee was observed at - the feeding station. - -|ATTENDANCE_summary| - -An individual may not be recorded as being at the feeding station -more than once on any given day. -This means that for any given |ATTENDANCE.AnimID|, for any given -|ATTENDANCE.Date|, that there may not be another ATTENDANCE row for the -given individual. - -The system will generate a warning when an ATTENDANCE row does not -have a |ARRIVALS_A| related row that records the presence of the -individual: does not have a related |EVENTS| row where the -|EVENTS.Behavior| code is ``sdb_arrival_a``, and a |ROLES| row, -related to the event, where the |ROLES|.\ |ROLES.Participant| has the -same value as the attendance record's |ATTENDANCE.AnimID| column. - -The |ATTENDANCE.Date| must be during a period when the arriving individual -was under study. -This means that the |ATTENDANCE.Date| must be on or after the arriving -individual's |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.EntryDate| and on or -before the arriving individual's |BIOGRAPHY_DATA|.\ -|BIOGRAPHY_DATA.DepartDate|. - -The system will generate a warning if the ``sdb_male_swelling`` code -is assigned to a female, an individual with a |BIOGRAPHY_DATA|.\ -|BIOGRAPHY_DATA.Sex| value of ``sdb_female``. - -The system will generate a warning if any code other than the -``sdb_male_swelling`` code is assigned to an individual who is not a -female, an individual with a |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.Sex| -value that is not ``sdb_female``. - -Except for the unknown female individuals, those with a -|BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| of ``sdb_stranger_female``, -or ``sdb_stranger_female2``, or ``sdb_stranger_female3``, the system -will generate a warning if the ``sdb_adolescent_swelling`` code is -assigned to a female less than ``sdb_min_adolescent_age`` -sdb_min_adolescent_age_units old or more than -``sdb_max_adolescent_age`` sdb_max_adolescent_age_units old. - -Except for the unknown female individuals, those with a -|BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| of ``sdb_stranger_female``, -or ``sdb_stranger_female2``, or ``sdb_stranger_female3``, the system -will generate a warning if a code that is not one of -``sdb_male_swelling``, ``sdb_adolescent_swelling``, -``sdb_no_swelling``, and ``sdb_missing_swelling`` is assigned to a -female that is at least ``sdb_min_swelling_age`` -sdb_min_swelling_age_units old and less than or equal to -``sdb_max_swelling_age`` sdb_max_swelling_age_units old. - -.. contents:: - :depth: 2 - - -.. _ATTENDANCE.AtID: - -AtID (Attendance ID) -```````````````````` - -.. |ATTENDANCE.AtID_summary| replace:: |idcol| - -|ATTENDANCE.AtID_summary| |notnull| - - -.. _ATTENDANCE.AnimID: - -AnimID (Animal IDentifier) -`````````````````````````` - -.. |ATTENDANCE.AnimID_summary| replace:: - The |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| identifying the - individual observed at the feeding station. - -|ATTENDANCE.AnimID_summary| |notnull| - - -.. _ATTENDANCE.Date: - -Date -```` - -.. |ATTENDANCE.Date_summary| replace:: - The date the individual was observed at the feeding station. - -|ATTENDANCE.Date_summary| -This date may not be before ``sdb_min_follow_date``. -|notnull| - - -.. _ATTENDANCE.CommID: - -CommID -`````` - -.. |ATTENDANCE.CommID_summary| replace:: - - A code for the community observed at the feeding station. - A |COMM_IDS|.\ |COMM_IDS.CommID| value. - -|ATTENDANCE.CommID_summary| - -This value is not validated against the arriving individual's record -of community membership as recorded in other database content. - -|cannot_change| |notnull| diff --git a/doc/src/tables/events.m4 b/doc/src/tables/events.m4 index 0340571..cd6c72f 100644 --- a/doc/src/tables/events.m4 +++ b/doc/src/tables/events.m4 @@ -25,24 +25,22 @@ EVENTS ------ .. |EVENTS_summary| replace:: - Each row represents an *event* that occurred, whether - involving a focal or not. - Events may have been recorded during a follow or in the records - of attendance at the feeding station. - All recorded observations that occur during a follow are events. -|EVENTS_summary| + Each row represents an *event* that occurred. + Typically, events involve one or more chimpanzees. + Events may be recorded during a follow, whether involving a focal + or not, events may be recorded at the feeding station, or events + may be recorded as ad-hoc observations such as records of + pantgrunts recorded opportunisticly. -Events are either related to a follow or related to a record of -attendance. -This means that either the |EVENTS.WID| or the |EVENTS.AtID| column -must be |null|, and one of these columns must not be |null|. +|EVENTS_summary| -Whether an event is related to a follow or to an attendance record is -dependent upon the event's |EVENTS.Behavior| code. +Whether an event is related to a follow or to an attendance record or +to something else is dependent upon the event's |EVENTS.Behavior| +code. -Each |EVENTS.Behavior| code has a one-to-one association with a table -in the database. +Each |EVENTS.Behavior| code can have a one-to-one association with a +table in the database, a table that contains additional event detail. An ``sdb_aggression`` |EVENTS.Behavior| code is associated with the |AGGRESSIONS| table. An ``sdb_arrival`` |EVENTS.Behavior| code is associated with the @@ -65,7 +63,8 @@ The following table lists these rules and implications: ``sdb_arrival_a`` (Arrival at the feeding station) 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|. + This means the |EVENTS.WID| column must reference a |WATCHES| row + with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_attendance``. A related row should exist on |ARRIVALS_A|; there should be a row on |ARRIVALS_A| with an |ARRIVALS_A|.\ |ARRIVALS_A.EID| value of @@ -92,21 +91,21 @@ The following table lists these rules and implications: An individual should not be recorded as being at the feeding station more than once during any given time period. - This means that for any given EVENTS row, with a given (non-|null|) - |EVENTS.AtID| value, there should not be another EVENTS row with - that |EVENTS.AtID| value, with an |EVENTS.Start| value less than or + The system will generate a warning when there are overlaps in an + individual's presence at the feeding station. + This means that for any given EVENTS row, with a given + |EVENTS.WID| value, there should not be another EVENTS row with + that |EVENTS.WID| value, with an |EVENTS.Start| value less than or equal to the given EVENTS row's start time, when the other row's |EVENTS.Stop| value is greater than or equal to the given EVENTS row's start time. A similar rule applies to the given EVENTS row's stop time. - Which means, for any given EVENTS row, with a given (non-|null|) - |EVENTS.AtID| value, there should not be another EVENTS row with - that |EVENTS.AtID| value, with an |EVENTS.Start| value less than or + Which means, for any given EVENTS row, with a given + |EVENTS.WID| value, there should not be another EVENTS row with + that |EVENTS.WID| value, with an |EVENTS.Start| value less than or equal to the given EVENTS row's stop time, when the other row's |EVENTS.Stop| value is greater than or equal to the given EVENTS row's stop time. - The system will generate a warning when there are overlaps in an - individual's presence at the feeding station. .. _EVENTS_aggression_code: @@ -159,7 +158,8 @@ The following table lists these rules and implications: ``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|. + This means the |EVENTS.WID| column must reference a |WATCHES| row + with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_attendance``. The |ROLES| rows related to the event, the rows with a |ROLES|.\ |ROLES.EID| value equal to the EVENTS.\ |EVENTS.EID| value, @@ -445,18 +445,6 @@ WID (Watch period IDentifier) |EVENTS.WID_summary| |cannot_change| -.. _EVENTS.AtID: - -AtID (ATtendance IDentifier) -```````````````````````````` - -.. |EVENTS.AtID_summary| replace:: - The |ATTENDANCE|.\ |ATTENDANCE.AtID| that identifies the attendance - record during which the event was recorded. - -|EVENTS.AtID_summary| |cannot_change| - - .. _EVENTS.Behavior: Behavior diff --git a/doc/src/tables/roles.m4 b/doc/src/tables/roles.m4 index 308f183..afac534 100644 --- a/doc/src/tables/roles.m4 +++ b/doc/src/tables/roles.m4 @@ -41,34 +41,27 @@ more than a record of the presence of other species sighted during a follow. The participant in the event is required to be under study -on the date of the follow, for follow related events. -This means that, when the event occurs during a follow, the -|WATCHES|.\ |WATCHES.Date| related to the event must be between the -|ROLES.Participant|'s |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.EntryDate| -and their |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.DepartDate|, inclusive of -endpoints. -If this is not the case the system will immediately raise an error. -When the event occurs during attendance taking at the feeding station, -the |ATTENDANCE|.\ |ATTENDANCE.Date| related to the event should be -between the |ROLES.Participant|'s |BIOGRAPHY_DATA|.\ +on the date when the observation was made. +This means that the |WATCHES|.\ |WATCHES.Date| related to the event +must be between the |ROLES.Participant|'s |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.EntryDate| and their |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.DepartDate|, inclusive of endpoints. -If this is not the case the system will generate a warning. The system will generate a warning if a participant is not present in the follow at the time of the given event. -This means that, when the event occurred during a follow, the -|ROLES|.\ |ROLES.Participant| must have been recorded in the follow as -an arriving individual and the |EVENTS| row related to the arrival has -an |EVENTS|.\ |EVENTS.Start| that is the same as or before the -starting time\ [#f1]_ of the given event and an |EVENTS|.\ -|EVENTS.Stop| that is the same as or after the given event's ending -time\ [#f2]_. -When the event occurred during attendance taking at the feeding -station, the |ROLES|.\ |ROLES.Participant| must have been recorded at -the feeding station as an arriving individual and the |EVENTS| row -related to the arrival has an |EVENTS|.\ |EVENTS.Start| that is the -same as or before the starting time\ [#f1]_ of the given event and an +For follows, this means that, the |ROLES|.\ |ROLES.Participant| must +have been recorded in the follow as an arriving individual and the +|EVENTS| row recording the arrival has an |EVENTS|.\ |EVENTS.Behavior| +value of ``sdb_arrival``, has an |EVENTS|.\ |EVENTS.Start| that is the +same as or before the starting time\ [#f1]_ of the given event, and +has an |EVENTS|.\ |EVENTS.Stop| that is the same as or after the given +event's ending time\ [#f2]_. +For attendance taken at the feeding station, this means that the +|ROLES|.\ |ROLES.Participant| must have been recorded at the feeding +station as an arriving individual and the |EVENTS| row related to the +arrival has an |EVENTS|.\ |EVENTS.Behavior| value of +``sdb_arrival_a``, has an |EVENTS|.\ |EVENTS.Start| that is the same +as or before the starting time\ [#f1]_ of the given event, and has an |EVENTS|.\ |EVENTS.Stop| that is the same as or after the given event's ending time\ [#f2]_. diff --git a/doc/src/tables/watches.m4 b/doc/src/tables/watches.m4 index 20da4fa..5a935e6 100644 --- a/doc/src/tables/watches.m4 +++ b/doc/src/tables/watches.m4 @@ -49,8 +49,7 @@ 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) @@ -83,9 +82,74 @@ The available |WATCHES.Type| values are: record the observers scheduled to conduct the follow. |transaction commit| - The combination of |WATCHES.Type|, |WATCHES.Focal| and + There may only be one record of an individual being followed on any + given day. + This means, the combination of |WATCHES.Type|, |WATCHES.Focal| and + |WATCHES.Date| must be unique. + +``sdb_attendance`` (Attendance) + Each row represents a day during which a chimpanzee was observed at + the feeding station. + When a given chimpanzee is observed at the feeding station more + than once per-day, there are multiple |EVENTS| rows, with an + |EVENTS|.\ |EVENTS.Behavior| value of ``sdb_arrival_a``, related to + the WATCHES row recording the presence of the individual at the + feeding station on the given day. + + The |WATCHES.Focal| column contains the |BIOGRAPHY_DATA|.\ + |BIOGRAPHY_DATA.AnimID| of the individual that appeared at the + feeding station. + + The |WATCHES.CommID| column contains the code for the community the + observers recorded when the attendance was taken; the |COMM_IDS|.\ + |COMM_IDS.CommID| of the community. + This value is not validated against the arriving individual's + record of community membership as recorded in other database + content. + + The system will generate a warning when a WATCHES row that + represents attendance does not have a record of attendance -- an + |ARRIVALS_A| related row -- that records the presence of the + individual. + This means that a warning is generated when an individual does not + have a related |EVENTS| row where the |EVENTS.Behavior| code is + ``sdb_arrival_a``, and a |ROLES| row, related to the event, where + the |ROLES|.\ |ROLES.Participant| has the same value as the + |WATCHES.Focal| column. + + The system will generate a warning if the ``sdb_male_swelling`` code + is assigned to a female, an individual with a |BIOGRAPHY_DATA|.\ + |BIOGRAPHY_DATA.Sex| value of ``sdb_female``. + + The system will generate a warning if any code other than the + ``sdb_male_swelling`` code is assigned to an individual who is not a + female, an individual with a |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.Sex| + value that is not ``sdb_female``. + + Except for the unknown female individuals, those with a + |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| of ``sdb_stranger_female``, + or ``sdb_stranger_female2``, or ``sdb_stranger_female3``, the system + will generate a warning if the ``sdb_adolescent_swelling`` code is + assigned to a female less than ``sdb_min_adolescent_age`` + sdb_min_adolescent_age_units old or more than + ``sdb_max_adolescent_age`` sdb_max_adolescent_age_units old. + + Except for the unknown female individuals, those with a + |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| of ``sdb_stranger_female``, + or ``sdb_stranger_female2``, or ``sdb_stranger_female3``, the system + will generate a warning if a code that is not one of + ``sdb_male_swelling``, ``sdb_adolescent_swelling``, + ``sdb_no_swelling``, and ``sdb_missing_swelling`` is assigned to a + female that is at least ``sdb_min_swelling_age`` + sdb_min_swelling_age_units old and less than or equal to + ``sdb_max_swelling_age`` sdb_max_swelling_age_units old. + + For any given individual there may not be more than one record of + daily attendance at the feeding station on any given day. + This means, 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 @@ -167,6 +231,8 @@ One of the following values:: ``sdb_follow`` (Follow) + ``sdb_attendance`` (Attendance) + See :ref:`overview of the WATCHES table `, above, for more information. diff --git a/include/global_constants.m4 b/include/global_constants.m4 index fa97bb7..f4ded13 100644 --- a/include/global_constants.m4 +++ b/include/global_constants.m4 @@ -191,6 +191,7 @@ dnl dnl The WATCHES.Type values define(`sdb_follow', `F') +define(`sdb_attendance', `A') divert(`0')dnl Output with m4 again ]}])dnl End of ifdef over the whole file. -- 2.34.1