From f7b2c53be8374291f51990e0d981bf384cf02bfb Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Thu, 28 May 2026 17:46:05 +0000 Subject: [PATCH] Allow EVENTS and ATTENDANCE to relate, add EVENTS.AtID column --- db/schemas/lib/triggers/create/events.m4 | 3 + db/schemas/sokwedb/indexes/create/events.m4 | 6 +- db/schemas/sokwedb/indexes/drop/events.m4 | 1 + db/schemas/sokwedb/tables/create/events.m4 | 5 +- doc/diagrams/events.svg | 314 ++++++++++---------- doc/diagrams/follows.svg | 90 +++--- doc/src/epilog.inc.m4 | 2 + doc/src/tables/events.m4 | 41 ++- 8 files changed, 253 insertions(+), 209 deletions(-) diff --git a/db/schemas/lib/triggers/create/events.m4 b/db/schemas/lib/triggers/create/events.m4 index 670216b..5a0ce01 100644 --- a/db/schemas/lib/triggers/create/events.m4 +++ b/db/schemas/lib/triggers/create/events.m4 @@ -47,6 +47,9 @@ CREATE OR REPLACE FUNCTION events_func () -- against biography. Simpler to dis-allow change. cannot_change(`EVENTS', `FID') + -- 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 diff --git a/db/schemas/sokwedb/indexes/create/events.m4 b/db/schemas/sokwedb/indexes/create/events.m4 index 887269e..ae13f27 100644 --- a/db/schemas/sokwedb/indexes/create/events.m4 +++ b/db/schemas/sokwedb/indexes/create/events.m4 @@ -22,7 +22,11 @@ include(`constants.m4')dnl include(`indexmacros.m4')dnl CREATE INDEX IF NOT EXISTS events_fid ON events - (fid); + (fid) + WHERE fid IS NOT NULL; +CREATE INDEX IF NOT EXISTS events_atid ON events + (atid) + WHERE atid IS NOT NULL; 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/drop/events.m4 b/db/schemas/sokwedb/indexes/drop/events.m4 index c038b9a..6143c34 100644 --- a/db/schemas/sokwedb/indexes/drop/events.m4 +++ b/db/schemas/sokwedb/indexes/drop/events.m4 @@ -23,6 +23,7 @@ include(`indexmacros.m4')dnl DROP INDEX IF EXISTS events_fid; +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/tables/create/events.m4 b/db/schemas/sokwedb/tables/create/events.m4 index 074f336..6764225 100644 --- a/db/schemas/sokwedb/tables/create/events.m4 +++ b/db/schemas/sokwedb/tables/create/events.m4 @@ -25,8 +25,10 @@ dnl CREATE TABLE events ( key_column(`EVENTS', `EID', INTEGER) - ,fid INTEGER NOT NULL + ,fid INTEGER REFERENCES follows + ,atid INTEGER + REFERENCES attendance ,behavior TEXT NOT NULL CONSTRAINT "Behavior must be one of: sdb_aggression, sdb_arrival, sdb_foo, sdb_grooming, sdb_mating, sdb_other_species, sdb_pantgrunt" CHECK (behavior IN ('sdb_aggression' @@ -57,6 +59,7 @@ CREATE TABLE events ( CONSTRAINT "When the Behavior is (sdb_groom_scan) Start must equal Stop" CHECK (behavior <> 'sdb_groom_scan' OR start = stop) + null_xor_null(`FID', `AtID') ); grant_priv(`EVENTS') diff --git a/doc/diagrams/events.svg b/doc/diagrams/events.svg index 2220c5c..283a43c 100644 --- a/doc/diagrams/events.svg +++ b/doc/diagrams/events.svg @@ -793,8 +793,8 @@ inkscape:pageopacity="0.0" inkscape:pageshadow="2" inkscape:zoom="2.5523998" - inkscape:cx="229.58786" - inkscape:cy="895.04003" + inkscape:cx="206.47236" + inkscape:cy="142.80678" inkscape:document-units="mm" inkscape:current-layer="layer3" inkscape:document-rotation="0" @@ -2954,164 +2954,6 @@ x="30.667816" y="190.21294" style="stroke-width:0.264583px" /> - EVENTS - - EID - - Behavior - - FID - - Start - - - Notes - - Stop - - Certainty - - + EVENTS + + EID + + Behavior + + FID ∅ + + Start + + Stop + + Certainty + + + Notes + diff --git a/doc/diagrams/follows.svg b/doc/diagrams/follows.svg index bc82144..bcdcfe1 100644 --- a/doc/diagrams/follows.svg +++ b/doc/diagrams/follows.svg @@ -5,7 +5,7 @@ viewBox="0 0 215.9 279.4" version="1.1" id="svg1323" - inkscape:version="1.1.2 (0a00cf5339, 2022-02-04)" + inkscape:version="1.2.2 (b0a8486541, 2022-12-01)" sodipodi:docname="follows.svg" inkscape:export-filename="../../../../../../tmp/demography.png" inkscape:export-xdpi="96" @@ -562,18 +562,18 @@ borderopacity="1.0" inkscape:pageopacity="0.0" inkscape:pageshadow="2" - inkscape:zoom="1.2761999" - inkscape:cx="401.97465" - inkscape:cy="512.4589" + inkscape:zoom="2.5523998" + inkscape:cx="507.95334" + inkscape:cy="187.86242" inkscape:document-units="mm" inkscape:current-layer="layer3" inkscape:document-rotation="0" showgrid="true" units="in" - inkscape:window-width="2048" - inkscape:window-height="1223" - inkscape:window-x="0" - inkscape:window-y="28" + inkscape:window-width="1920" + inkscape:window-height="1176" + inkscape:window-x="1920" + inkscape:window-y="0" inkscape:window-maximized="1" inkscape:snap-text-baseline="false" inkscape:snap-global="true" @@ -1807,10 +1807,10 @@ style="font-style:normal;font-variant:normal;font-weight:bold;font-stretch:normal;font-size:2.816px;font-family:Sans;-inkscape-font-specification:'Sans, Bold';font-variant-ligatures:normal;font-variant-caps:normal;font-variant-numeric:normal;font-variant-east-asian:normal;stroke-width:0.263999px">EVENTSEIDBehaviorFIDFID ∅StartStopCertaintyNotes` .. |EVENTS.FID| replace:: :ref:`FID ` +.. |EVENTS.AtID| replace:: + :ref:`AtID ` .. |EVENTS.BEHAVIOR| replace:: :ref:`Behavior ` .. |EVENTS.Start| replace:: diff --git a/doc/src/tables/events.m4 b/doc/src/tables/events.m4 index 44c9a06..b955333 100644 --- a/doc/src/tables/events.m4 +++ b/doc/src/tables/events.m4 @@ -25,12 +25,22 @@ EVENTS ------ .. |EVENTS_summary| replace:: - Each row represents an *event* that occurred during a follow, whether + Each row represents an *event* that occurred, whether involving the 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| +Events are either related to a follow or related to a record of +attendance. +This means that either the |EVENTS.FID| or the |EVENTS.AtID| column +must be |null|, and one of these columns must not be |null|. + +Whether an event is related to a follow or to an attendance record 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. An ``sdb_aggression`` |EVENTS.Behavior| code is associated with the @@ -53,6 +63,9 @@ The following table lists these rules and implications: .. _EVENTS_aggression_code: ``sdb_aggression`` (Aggression) + The EVENTS row must be associated with a follow. + This means the |EVENTS.FID| column must not be |null|. + A related row should exist on |AGGRESSIONS|; there should be a row on |AGGRESSIONS| with an |AGGRESSIONS|.\ |AGGRESSIONS.EID| value of the event's |EVENTS.EID|. @@ -95,6 +108,9 @@ The following table lists these rules and implications: .. _EVENTS_arrival_code: ``sdb_arrival`` (Arrival) + The EVENTS row must be associated with a follow. + This means the |EVENTS.FID| column must not be |null|. + A related row should exist on |ARRIVALS|; there should be a row on |ARRIVALS| with an |ARRIVALS|.\ |ARRIVALS.EID| value of the event's |EVENTS.EID|. @@ -119,6 +135,9 @@ The following table lists these rules and implications: .. _EVENTS_food_code: ``sdb_food`` (Food) + The EVENTS row must be associated with a follow. + This means the |EVENTS.FID| column must not be |null|. + 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 the event's |EVENTS.EID|. @@ -150,6 +169,9 @@ The following table lists these rules and implications: .. _EVENTS_grooming_code: ``sdb_grooming`` (Grooming) + The EVENTS row must be associated with a follow. + This means the |EVENTS.FID| column must not be |null|. + A related row should exist on |GROOMINGS|; there should be a row on |GROOMINGS| with a |GROOMINGS|.\ |GROOMINGS.EID| value of the event's |EVENTS.EID|. @@ -193,6 +215,9 @@ The following table lists these rules and implications: .. _EVENTS_groom_scan_code: ``sdb_groom_scan`` (SCAN interval Groomings) + The EVENTS row must be associated with a follow. + This means the |EVENTS.FID| column must not be |null|. + 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 the event's |EVENTS.EID|. @@ -273,7 +298,19 @@ FID (Follow IDentifier) The |FOLLOWS|.\ |FOLLOWS.FID| that identifies the follow during which the event was recorded. -|EVENTS.FID_summary| |cannot_change| |notnull| +|EVENTS.FID_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: -- 2.34.1