From 436f899354da33190574264b7a853f61b68074d7 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Mon, 1 Jun 2026 20:22:32 +0000 Subject: [PATCH] Remove arrival related information from ATTENDANCE --- db/schemas/lib/triggers/create/attendance.m4 | 11 +- .../lib/triggers/create/biography_data.m4 | 28 +-- .../sokwedb/indexes/create/attendance.m4 | 17 +- db/schemas/sokwedb/indexes/drop/attendance.m4 | 6 +- .../sokwedb/tables/create/attendance.m4 | 26 --- db/schemas/sokwedb/tables/create/events.m4 | 31 ++++ doc/src/epilog.inc.m4 | 18 -- doc/src/tables/attendance.m4 | 170 +----------------- doc/src/tables/roles.m4 | 12 +- 9 files changed, 65 insertions(+), 254 deletions(-) diff --git a/db/schemas/lib/triggers/create/attendance.m4 b/db/schemas/lib/triggers/create/attendance.m4 index d66df11..b09cfff 100644 --- a/db/schemas/lib/triggers/create/attendance.m4 +++ b/db/schemas/lib/triggers/create/attendance.m4 @@ -53,8 +53,9 @@ CREATE OR REPLACE FUNCTION attendance_func () SELECT biography_data.entrydate, biography_data.departdate INTO a_entrydate , a_departdate FROM biography_data - WHERE NEW.date < biography_data.entrydate - OR NEW.date > biography_data.departdate; + WHERE biography_data.animid = NEW.animid + AND (NEW.date < biography_data.entrydate + OR NEW.date > biography_data.departdate); IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on ' || TG_OP || ' of ATTENDANCE' @@ -69,12 +70,6 @@ CREATE OR REPLACE FUNCTION attendance_func () || NEW.animid || '), Value (Date) = (' || NEW.date - || '), Value (Start) = (' - || NEW.start - || '), Key (Stop) = (' - || NEW.stop - || '), Value (CommID) = (' - || NEW.commid || '), Value (CommID) = (' || NEW.commid || '), Value (BIOGRAPHY_DATA.EntryDate) = (' diff --git a/db/schemas/lib/triggers/create/biography_data.m4 b/db/schemas/lib/triggers/create/biography_data.m4 index dfbfabb..f399e44 100644 --- a/db/schemas/lib/triggers/create/biography_data.m4 +++ b/db/schemas/lib/triggers/create/biography_data.m4 @@ -501,22 +501,20 @@ CREATE OR REPLACE FUNCTION biography_data_func () DECLARE a_atid attendance.atid%TYPE; a_date attendance.date%TYPE; - a_start attendance.start%TYPE; - a_stop attendance.stop%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.start - , attendnance.stop, attendance.commid - INTO a_atid , a_date , a_start - , a_stop , a_commid + 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, attendance.start; -- consistency + ORDER BY attendance.date; -- consistency IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' @@ -530,10 +528,6 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_atid || '), Value (ATTENDANCE.Date) = (' || a_date - || '), Value (ATTENDANCE.Start) = (' - || a_start - || '), Value (ATTANDANCE.Stop) = (' - || a_stop || '), Value (ATTANDANCE.Commid) = (' || a_commid || ')'; @@ -541,14 +535,12 @@ CREATE OR REPLACE FUNCTION biography_data_func () END IF; IF NEW.departdate <> OLD.departdate THEN - SELECT attendance.atid, attendance.date, attendance.start - , attendnance.stop, attendance.commid - INTO a_atid , a_date , a_start - , a_stop , a_commid + 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.departdate - ORDER BY attendance.date, attendance.start; -- consistency + ORDER BY attendance.date; -- consistency IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' @@ -562,10 +554,6 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_atid || '), Value (ATTENDANCE.Date) = (' || a_date - || '), Value (ATTENDANCE.Start) = (' - || a_start - || '), Value (ATTANDANCE.Stop) = (' - || a_stop || '), Value (ATTANDANCE.Commid) = (' || a_commid || ')'; diff --git a/db/schemas/sokwedb/indexes/create/attendance.m4 b/db/schemas/sokwedb/indexes/create/attendance.m4 index d3c00cf..118cf10 100644 --- a/db/schemas/sokwedb/indexes/create/attendance.m4 +++ b/db/schemas/sokwedb/indexes/create/attendance.m4 @@ -21,20 +21,13 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl +CREATE UNIQUE INDEX IF NOT EXISTS + "On ATTENDANCE, AnimID + Date must be unique" + ON attendance + (date, animid); + CREATE INDEX IF NOT EXISTS attendance_animid ON attendance (animid); -CREATE INDEX IF NOT EXISTS attendance_date ON attendance - (date); -CREATE INDEX IF NOT EXISTS attendance_start ON attendance - (start); -CREATE INDEX IF NOT EXISTS attendance_stop ON attendance - (stop); CREATE INDEX IF NOT EXISTS attendance_commid ON attendance (commid); -CREATE INDEX IF NOT EXISTS attendance_swelling ON attendance - (swelling); - --- Don't index ArrivalDegree, DepartureDegree, Recorder, Observer2 --- because we don't expect to search on them. - diff --git a/db/schemas/sokwedb/indexes/drop/attendance.m4 b/db/schemas/sokwedb/indexes/drop/attendance.m4 index 8b49dc6..01e4190 100644 --- a/db/schemas/sokwedb/indexes/drop/attendance.m4 +++ b/db/schemas/sokwedb/indexes/drop/attendance.m4 @@ -21,9 +21,7 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl +DROP INDEX IF EXISTS "On ATTENDANCE, AnimID + Date must be unique"; + DROP INDEX IF EXISTS attendance_animid; -DROP INDEX IF EXISTS attendance_date; -DROP INDEX IF EXISTS attendance_start; -DROP INDEX IF EXISTS attendance_stop; DROP INDEX IF EXISTS attendance_commid; -DROP INDEX IF EXISTS attendance_swelling; diff --git a/db/schemas/sokwedb/tables/create/attendance.m4 b/db/schemas/sokwedb/tables/create/attendance.m4 index 21dfa34..304c816 100644 --- a/db/schemas/sokwedb/tables/create/attendance.m4 +++ b/db/schemas/sokwedb/tables/create/attendance.m4 @@ -27,34 +27,8 @@ CREATE TABLE attendance ( key_column(`ATTENDANCE', `AtID', INTEGER) ,animid_column(`animid', `AnimID', `NOT NULL') ,date DATE NOT NULL - ,start TIME NOT NULL - noseconds_check(`Start') - ,stop TIME NOT NULL - noseconds_check(`Stop') ,commid TEXT NOT NULL REFERENCES comm_ids - ,swelling TEXT NOT NULL - REFERENCES cycle_states - ,bananas INTEGER - positive_check(`Bananas') - ,arrivaldegree INTEGER NOT NULL - CONSTRAINT "ArrivalDegree must be >= sdb_min_degree and <= sdb_max_degree" - CHECK (sdb_min_degree <= arrivaldegree - AND arrivaldegree <= sdb_max_degree) - ,departuredegree INTEGER NOT NULL - CONSTRAINT - "DepartureDegree must be >= sdb_min_degree and <= sdb_max_degree" - CHECK (sdb_min_degree <= departuredegree - AND departuredegree <= sdb_max_degree) - ,recorder TEXT NOT NULL - REFERENCES people - ,observer2 TEXT NOT NULL - REFERENCES people - ,cycleold TEXT NOT NULL - notonlyspaces_check(`CycleOld') - - CONSTRAINT "Start cannot be after Stop" - CHECK(start <= stop) ); grant_priv(`ATTENDANCE') diff --git a/db/schemas/sokwedb/tables/create/events.m4 b/db/schemas/sokwedb/tables/create/events.m4 index a5b22c4..b26bf1b 100644 --- a/db/schemas/sokwedb/tables/create/events.m4 +++ b/db/schemas/sokwedb/tables/create/events.m4 @@ -23,6 +23,31 @@ 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 FID, 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 FID 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) @@ -94,6 +119,12 @@ CREATE TABLE events ( _point_behavior_time(`sdb_aggression') _point_behavior_time(`sdb_groom_scan') + _pair_behavior_source(`sdb_aggression', `FID') + _pair_behavior_source(`sdb_arrival', `FID') + _pair_behavior_source(`sdb_food', `FID') + _pair_behavior_source(`sdb_grooming', `FID') + _pair_behavior_source(`sdb_groom_scan', `FID') + _behavior_certain(`sdb_groom_scan') ); diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index 86c2e38..436bd0a 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -155,26 +155,8 @@ sdb_generated_rst()dnl :ref:`AnimID ` .. |ATTENDANCE.Date| replace:: :ref:`Date ` -.. |ATTENDANCE.Start| replace:: - :ref:`Start ` -.. |ATTENDANCE.Stop| replace:: - :ref:`Stop ` .. |ATTENDANCE.CommID| replace:: :ref:`CommID ` -.. |ATTENDANCE.Swelling| replace:: - :ref:`Swelling ` -.. |ATTENDANCE.Bananas| replace:: - :ref:`Bananas ` -.. |ATTENDANCE.ArrivalDegree| replace:: - :ref:`ArrivalDegree ` -.. |ATTENDANCE.DepartureDegree| replace:: - :ref:`DepartureDegree ` -.. |ATTENDANCE.Recorder| replace:: - :ref:`Recorder ` -.. |ATTENDANCE.Observer2| replace:: - :ref:`Observer2 ` -.. |ATTENDANCE.CycleOld| replace:: - :ref:`CycleOld ` .. |BIOGRAPHY_DATA| replace:: :ref:`BIOGRAPHY_DATA ` diff --git a/doc/src/tables/attendance.m4 b/doc/src/tables/attendance.m4 index cacab69..5ff6508 100644 --- a/doc/src/tables/attendance.m4 +++ b/doc/src/tables/attendance.m4 @@ -25,30 +25,16 @@ ATTENDANCE ---------- .. |ATTENDANCE_summary| replace:: - Each row represents a period of time during which a single - chimpanzee was present at the feeding station. + Each row represents a day during which a chimpanzee was observed at + the feeding station. |ATTENDANCE_summary| -An individual should not be recorded as being at the feeding station -more than once during any given time period. +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 should not be another ATTENDANCE row for the -given individual on the given date with an |ATTENDANCE.Start| value -less than or equal to the given row's start time, when the other row's -|ATTENDANCE.Stop| value is greater than or equal to the given row's -start time. -A similar rule applies to the given row's stop time. -Which means, for any given |ATTENDANCE.AnimID|, for any given -|ATTENDANCE.Date|, that there should not be another ATTENDANCE row for the -given individual on the given date with an |ATTENDANCE.Start| value -less than or equal to the given row's stop time, when the other row's -|ATTENDANCE.Stop| value is greater than or equal to the given row's -stop time. -The system will generate a warning when there are overlaps in an -individual's presence at the feeding station. - -The |ATTENDANCE.Start| time cannot be after the |ATTENDANCE.Stop| time. +|ATTENDANCE.Date|, that there may not be another ATTENDANCE row for the +given individual. The |ATTENDANCE.Date| must be during a period when the arriving individual was under study. @@ -123,40 +109,6 @@ This date may not be before ``sdb_min_follow_date``. |notnull| -.. _ATTENDANCE.Start: - -Start -````` - -.. |ATTENDANCE.Start_summary| replace:: - The time the individual arrived at the feeding station. - -|ATTENDANCE.Start_summary| -As with many other time values, this value is precise to the minute. -The "starting minute" recorded in this column is an "inclusive -endpoint", the individual arrived during the designated minute. - -The value of this column cannot be before ``sdb_min_event_start``. -|noseconds| |notnull| - - -.. _ATTENDANCE.Stop: - -Stop -```` - -.. |ATTENDANCE.Stop_summary| replace:: - The time the individual departed the feeding station. - -|ATTENDANCE.Stop_summary| -As with many other time values, this value is precise to the minute. -The "ending minute" recorded in this column is an "inclusive -endpoint", the individual departed during the designated minute. - -The value of this column cannot be after ``sdb_max_event_stop``. -|noseconds| |notnull| - - .. _ATTENDANCE.CommID: CommID @@ -173,113 +125,3 @@ This value is not validated against the arriving individual's record of community membership as recorded in other database content. |cannot_change| |notnull| - - -.. _ATTENDANCE.Swelling: - -Swelling -```````` - -.. |ATTENDANCE.Swelling_summary| replace:: - A code indicating the degree of the individual's sexual swelling. - A |CYCLE_STATES|.\ |CYCLE_STATES.Code| value. - -|ATTENDANCE.Swelling_summary| -|notnull| - - -.. _ATTENDANCE.Bananas: - -Bananas -``````` - -.. |ATTENDANCE.Bananas_summary| replace:: - - An integer, the number of bananas that were given to the - individual. - -|ATTENDANCE.Bananas_summary| -This value must be a positive number, less than or equal to -``sdb_max_bananas``. - -This column may be |null| when there is no record of whether or not -bananas were given. - - -.. _ATTENDANCE.ArrivalDegree: - -ArrivalDegree -````````````` - -.. |ATTENDANCE.ArrivalDegree_summary| replace:: - - The direction from which the individual arrived, on a 360 degree - circle with ``0`` being north. - -|ATTENDANCE.ArrivalDegree_summary| -The value of this column must be an integer between ``sdb_min_degree`` -and ``sdb_max_degree``, inclusive. - -This column may be |null| when there is no record of the direction -from which the individual arrived. - - -.. _ATTENDANCE.DepartureDegree: - -DepartureDegree -``````````````` - -.. |ATTENDANCE.DepartureDegree_summary| replace:: - - The direction toward which the individual traveled when they - departed, on a 360 degree circle with ``0`` being north. - -|ATTENDANCE.DepartureDegree_summary| -The value of this column must be an integer between ``sdb_min_degree`` -and ``sdb_max_degree``, inclusive. - -This column may be |null| when there is no record of the direction -toward which the individual traveled when they departed. - - -.. _ATTENDANCE.Recorder: - -Recorder -```````` - -.. |ATTENDANCE.Recorder_summary| replace:: - - The observer who recorded the attendance record. - A |PEOPLE|.\ |PEOPLE.Person| value. - -|ATTENDANCE.Recorder_summary| |notnull| - - -.. _ATTENDANCE.Observer2: - -Observer2 -````````` - -.. |ATTENDANCE.Observer2_summary| replace:: - - A second observer who was present when the attendance was recorded. - A |PEOPLE|.\ |PEOPLE.Person| value. - -|ATTENDANCE.Observer2_summary| -|notnull| - - -.. _ATTENDANCE.CycleOld: - -CycleOld -```````` - -.. |ATTENDANCE.CycleOld_summary| replace:: - - Text comprising the initial digitization of information related to - the individuals sexual swelling. - -|ATTENDANCE.CycleOld_summary| - -|notonlyspaces| |notnull| - diff --git a/doc/src/tables/roles.m4 b/doc/src/tables/roles.m4 index eb19a64..751eec1 100644 --- a/doc/src/tables/roles.m4 +++ b/doc/src/tables/roles.m4 @@ -53,8 +53,16 @@ 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 the |ROLES|.\ |ROLES.Participant| must have been -recorded in the follow as an arriving individual and the |EVENTS| row +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 |EVENTS|.\ |EVENTS.Stop| that is the same as or after the given -- 2.34.1