From 4e2a417c7b0a5e84fa68968a84e3c111ec13489c Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Wed, 17 Jun 2026 15:49:05 +0000 Subject: [PATCH] Support recording pantgrunts that are not associated with a follow --- db/schemas/lib/triggers/create/events.m4 | 28 +++++++++----- db/schemas/sokwedb/indexes/create/watches.m4 | 8 ++++ db/schemas/sokwedb/indexes/drop/watches.m4 | 3 ++ doc/src/tables/events.m4 | 6 ++- doc/src/tables/watches.m4 | 40 ++++++++++++++++++++ include/global_constants.m4 | 1 + 6 files changed, 75 insertions(+), 11 deletions(-) diff --git a/db/schemas/lib/triggers/create/events.m4 b/db/schemas/lib/triggers/create/events.m4 index e5201ce..db6249d 100644 --- a/db/schemas/lib/triggers/create/events.m4 +++ b/db/schemas/lib/triggers/create/events.m4 @@ -83,8 +83,7 @@ CREATE OR REPLACE FUNCTION events_func () ,'sdb_food' ,'sdb_grooming' ,'sdb_groom_scan' - ,'sdb_other_species' - ,'sdb_pg_event') THEN + ,'sdb_other_species') THEN -- The event must be related to a follow. watch_type := 'sdb_follow'; ELSIF NEW.behavior IN ('sdb_arrival_a' @@ -138,11 +137,21 @@ CREATE OR REPLACE FUNCTION events_func () END IF; IF NEW.behavior = 'sdb_gps' - OR NEW.behavior = 'sdb_map' THEN + OR NEW.behavior = 'sdb_map' + OR NEW.behavior = 'sdb_pg_event' THEN -- The WATCHES row must be for a follow, if there is one, and if not - -- then for a location. - -- There can't exist a WATCHES row for a follow and a location for - -- the same individual for the same date, so this simplifies things. + -- then for a location, if it's gps or a map location, and then + -- for a pantgrunt, if it's a pantgrunt. + -- There can't exist a WATCHES row for a follow and a location, + -- or for a follow and a pantgrunt, for the same individual for + -- the same date, so this simplifies things. + + IF NEW.behavior = 'sdb_pg_event' THEN + watch_type := 'sdb_pantgrunt'; + ELSE + watch_type := 'sdb_location'; + END IF; + SELECT watches.focal, watches.commid, watches.date, watches.type , watches.notes INTO a_focal, a_commid , a_date , a_type @@ -150,15 +159,16 @@ CREATE OR REPLACE FUNCTION events_func () FROM watches WHERE watches.wid = NEW.wid AND watches.type <> 'sdb_follow' - AND watches.type <> 'sdb_location'; + AND watches.type <> watch_type; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on ' || TG_OP || ' of EVENTS' , DETAIL = 'Events with (Behavior) = (' || NEW.behavior || ') must be related to a WATCHES row with' - || ' (Type) = (sdb_follow) or (Type) = (sdb_location)' - || ': Key (EID) = (' + || ' (Type) = (sdb_follow) or (Type) = (' + || watch_type + || '), but this is not the case: Key (EID) = (' || NEW.eid || '): Value (WID) = (' || NEW.wid diff --git a/db/schemas/sokwedb/indexes/create/watches.m4 b/db/schemas/sokwedb/indexes/create/watches.m4 index 6a8c53e..db1cf36 100644 --- a/db/schemas/sokwedb/indexes/create/watches.m4 +++ b/db/schemas/sokwedb/indexes/create/watches.m4 @@ -28,6 +28,14 @@ CREATE UNIQUE INDEX IF NOT EXISTS WHERE type = 'sdb_follow' OR type = 'sdb_location'; + +CREATE UNIQUE INDEX IF NOT EXISTS + "(Type)=(sdb_follow) or (Type)=(sdb_pantgrunt) means Date + Focal must be unique" + ON watches + (date, focal) + WHERE type = 'sdb_follow' + OR type = 'sdb_pantgrunt'; + CREATE UNIQUE INDEX IF NOT EXISTS "On WATCHES where (Type)=(sdb_attendance), Date + Focal must be unique" ON watches diff --git a/db/schemas/sokwedb/indexes/drop/watches.m4 b/db/schemas/sokwedb/indexes/drop/watches.m4 index 11b0309..6714cef 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 "(Type)=(sdb_follow) or (Type)=(sdb_location) means Date + Focal must be unique"; +DROP INDEX IF EXISTS + "(Type)=(sdb_follow) or (Type)=(sdb_pantgrunt) means Date + Focal must be unique"; + DROP INDEX IF EXISTS "On WATCHES where (Type)=(sdb_attendance), Date + Focal must be unique"; diff --git a/doc/src/tables/events.m4 b/doc/src/tables/events.m4 index bd90f39..705c275 100644 --- a/doc/src/tables/events.m4 +++ b/doc/src/tables/events.m4 @@ -514,9 +514,11 @@ The following table lists these rules and implications: .. _EVENTS_pantgrunt_code: ``sdb_pg_event`` (Pantgrunt) - The EVENTS row must be associated with a follow. + The EVENTS row must be associated with either a follow or an + non-follow pantgrunt observervation. This means the |EVENTS.WID| column must reference a |WATCHES| row - with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_follow``. + with either a |WATCHES|.\ |WATCHES.Type| value of ``sdb_follow`` or + a |WATCHES|.\ |WATCHES.Type| value of ``sdb_pantgrunt``. 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/watches.m4 b/doc/src/tables/watches.m4 index 1f296a8..9e4b02b 100644 --- a/doc/src/tables/watches.m4 +++ b/doc/src/tables/watches.m4 @@ -173,6 +173,38 @@ The available |WATCHES.Type| values are: day, to record this the one WATCHES row is related to multiple rows on the |EVENTS| table. +``sdb_pantgrunt`` (Pantgrunt) + + Each row records a date on which an individual was observed to + pantgrunt, when there is no follow assocated with the pantgrunt. + There is one WATCHES row of this type per date, per some usually + non-meaningful |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| -- often + ``sdb_unk``. + + The |WATCHES.Focal| column contains little information that is + useful. + The |WATCHES.Focal| column contains the |BIOGRAPHY_DATA|.\ + |BIOGRAPHY_DATA.AnimID| of the individual that was purportedly the + focal of a follow, but no such follow of the individual exists. + When there is no focal on record, for whatever reason, the special + |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| value of ``sdb_unk``, + representing an unknown individual -- or, in this case, "no + individual", is expected to be the |WATCHES.Focal| value. + + The |WATCHES.CommID| column contains the code for the community + recorded at along with the pantgrunt; the |COMM_IDS|.\ + |COMM_IDS.CommID| of the community. + This is not necessarily the community the pantgrunting individual(s) + is(are) a member of, although it usually is. + + There may only be one row on WATCHES per day, per individual + recorded along with the pantgrunt data. + This means, the combination of |WATCHES.Type|, |WATCHES.Focal| and + |WATCHES.Date| must be unique. + When multiple pantgrunts are recorded in a follow, or are recorded + on some day when there is no follow, to record this the one WATCHES + row has multiple related rows on the |EVENTS| table. + WATCHES rows of the location type, |WATCHES.Type| = ``sdb_location``, may only exist when there is not a follow-type row, |WATCHES.Type| = ``sdb_follow``, for the individual on the given date. @@ -181,6 +213,14 @@ are followed there cannot be a row on WATCHES for that individual for that day with a |WATCHES.Type| of ``sdb_location``, and vice-versa. +WATCHES rows of the pantgrunt type, |WATCHES.Type| = ``sdb_pantgrunt``, +may only exist when there is not a follow-type row, |WATCHES.Type| = +``sdb_follow``, for the individual on the given date. +This means that if an individual pantrgunts on a day they +are followed there cannot be a row on WATCHES for that individual +for that day with a |WATCHES.Type| of ``sdb_pantgrunt``, and +vice-versa. + 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 diff --git a/include/global_constants.m4 b/include/global_constants.m4 index 5c9c031..b03fba6 100644 --- a/include/global_constants.m4 +++ b/include/global_constants.m4 @@ -197,6 +197,7 @@ dnl The WATCHES.Type values define(`sdb_attendance', `A') define(`sdb_follow', `F') define(`sdb_location', `L') +define(`sdb_pantgrunt', `P') divert(`0')dnl Output with m4 again ]}])dnl End of ifdef over the whole file. -- 2.34.1