From 60e73dc06e5caf196e3dc5e81e9b3473cdb46c51 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Mon, 25 May 2026 18:12:49 +0000 Subject: [PATCH] Create, document, index, and trigger GROOM_SCANS --- db/schemas/lib/triggers/Makefile | 3 +- db/schemas/lib/triggers/create/groom_scans.m4 | 120 ++++++++++++++++ db/schemas/lib/triggers/create/roles.m4 | 9 +- db/schemas/lib/triggers/drop/groom_scans.m4 | 23 +++ db/schemas/sokwedb/indexes/Makefile | 2 +- .../sokwedb/indexes/create/groom_scans.m4 | 29 ++++ .../sokwedb/indexes/drop/groom_scans.m4 | 25 ++++ db/schemas/sokwedb/tables/Makefile | 3 +- .../sokwedb/tables/create/groom_scans.m4 | 36 +++++ doc/src/epilog.inc.m4 | 12 ++ doc/src/tables.m4 | 1 + doc/src/tables/events.m4 | 36 +++++ doc/src/tables/groom_scans.m4 | 135 ++++++++++++++++++ include/global_constants.m4 | 2 + 14 files changed, 430 insertions(+), 6 deletions(-) create mode 100644 db/schemas/lib/triggers/create/groom_scans.m4 create mode 100644 db/schemas/lib/triggers/drop/groom_scans.m4 create mode 100644 db/schemas/sokwedb/indexes/create/groom_scans.m4 create mode 100644 db/schemas/sokwedb/indexes/drop/groom_scans.m4 create mode 100644 db/schemas/sokwedb/tables/create/groom_scans.m4 create mode 100644 doc/src/tables/groom_scans.m4 diff --git a/db/schemas/lib/triggers/Makefile b/db/schemas/lib/triggers/Makefile index c4b4290..5b73741 100644 --- a/db/schemas/lib/triggers/Makefile +++ b/db/schemas/lib/triggers/Makefile @@ -36,7 +36,8 @@ ORDER := comm_ids \ aggressions \ sightings \ food_events \ - groomings + groomings \ + groom_scans DROP_EXISTING := true diff --git a/db/schemas/lib/triggers/create/groom_scans.m4 b/db/schemas/lib/triggers/create/groom_scans.m4 new file mode 100644 index 0000000..369a8f3 --- /dev/null +++ b/db/schemas/lib/triggers/create/groom_scans.m4 @@ -0,0 +1,120 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc. http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify it +dnl under the terms of the GNU Affero General Public License as published by +dnl the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Triggers for the groom_scans table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`macros.m4')dnl + + +RAISE INFO 'groom_scans_func'; +CREATE OR REPLACE FUNCTION groom_scans_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + BEGIN + -- Function for groom_scans insert and update triggers + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + + IF TG_OP = 'UPDATE' THEN + cannot_change(`GROOM_SCANS', `GrID') + END IF; + + -- The EVENTS.Behavior must be sdb_groom_scan + IF TG_OP = 'INSERT' + OR NEW.eid <> OLD.eid THEN + DECLARE + -- EVENTS + a_behavior events.behavior%TYPE; + a_start events.start%TYPE; + a_stop events.stop%TYPE; + -- FOLLOWS + a_fid follows.fid%TYPE; + a_focal follows.focal%TYPE; + a_date follows.date%TYPE; + -- ROLES + a_pid roles.pid%TYPE; + a_role roles.role%TYPE; + a_participant roles.participant%TYPE; + + BEGIN + SELECT events.behavior, events.start, events.stop + , follows.fid, follows.focal, follows.date + , roles.pid, roles.role, roles.participant + INTO a_behavior , a_start , a_stop + , a_fid , a_focal , a_date + , a_pid , a_role , a_participant + FROM events + JOIN follows ON (follows.fid = events.fid) + LEFT OUTER JOIN roles ON (roles.eid = events.eid) + WHERE events.eid = NEW.eid + AND events.behavior <> 'sdb_groom_scan'; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of GROOM_SCANS' + , DETAIL = 'Groom_Scans can only be related to an event with an' + || ' EVENTS.Behavior value of (sdb_groom_scan)' + || ': Key (GrID = (' + || NEW.grid + || '): Value (Initiator) = (' + || textualize(`NEW.initiator') + || '): Value (Terminator) = (' + || textualize(`NEW.terminator') + || '): Value (Problems) = (' + || NEW.problems + || '): Value (ExtractedBy) = (' + || NEW.extractedby + || '): Key (ROLES.PID) = (' + || textualize(`a_pid') + || '), Value (ROLES.Role) = (' + || textualize(`a_role') + || '), Value (ROLES.Participant) = (' + || textualize(`a_participant') + || '): Key (EVENTS.EID) = (' + || NEW.eid + || '): Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || '): Key (FOLLOWS.FID) = (' + || a_fid + || '), Value (FOLLOWS.Focal) = (' + || a_focal + || '), Value (FOLLOWS.Date) = (' + || a_date + || ')'; + END IF; + END; + END IF; + + RETURN NULL; + END; +$$; + + +RAISE INFO 'groom_scans_trigger'; +CREATE TRIGGER groom_scans_trigger + AFTER INSERT OR UPDATE + ON groom_scans FOR EACH ROW + EXECUTE PROCEDURE groom_scans_func(); diff --git a/db/schemas/lib/triggers/create/roles.m4 b/db/schemas/lib/triggers/create/roles.m4 index 4b09c60..2da7159 100644 --- a/db/schemas/lib/triggers/create/roles.m4 +++ b/db/schemas/lib/triggers/create/roles.m4 @@ -231,7 +231,8 @@ CREATE OR REPLACE FUNCTION roles_func () -- IF TG_OP = 'INSERT' AND (a_behavior = 'sdb_aggression' - OR a_behavior = 'sdb_grooming') THEN + OR a_behavior = 'sdb_grooming' + OR a_behavior = 'sdb_groom_scan') THEN DECLARE a_pid roles.pid%TYPE; a_role roles.role%TYPE; @@ -244,7 +245,8 @@ CREATE OR REPLACE FUNCTION roles_func () -- -- Validate ROLES.Role for aggression and grooming events -- - IF a_behavior = 'sdb_aggression' + IF (a_behavior = 'sdb_aggression' + OR a_behavior = 'sdb_groom_scan') AND NEW.role <> 'sdb_actor' AND NEW.role <> 'sdb_actee' THEN -- The ROLES rows for aggression events must have @@ -397,7 +399,8 @@ CREATE OR REPLACE FUNCTION roles_func () -- IF TG_OP = 'INSERT' AND (a_behavior = 'sdb_aggression' - OR a_behavior = 'sdb_grooming') THEN + OR a_behavior = 'sdb_grooming' + OR a_behavior = 'sdb_groom_scan') THEN DECLARE a_pid roles.pid%TYPE; a_role roles.role%TYPE; diff --git a/db/schemas/lib/triggers/drop/groom_scans.m4 b/db/schemas/lib/triggers/drop/groom_scans.m4 new file mode 100644 index 0000000..6990cca --- /dev/null +++ b/db/schemas/lib/triggers/drop/groom_scans.m4 @@ -0,0 +1,23 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc. http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published by +dnl the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Drop triggers for groom_scans table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl + +DROP FUNCTION IF EXISTS groom_scans_func() CASCADE; diff --git a/db/schemas/sokwedb/indexes/Makefile b/db/schemas/sokwedb/indexes/Makefile index e2727e3..11a3cea 100644 --- a/db/schemas/sokwedb/indexes/Makefile +++ b/db/schemas/sokwedb/indexes/Makefile @@ -22,7 +22,7 @@ ORDER := biography_data biography_log comm_membs comm_memb_log \ follows follow_observers follow_studies events roles arrivals \ estrus_sources estrus_states aggression_event_log sightings \ - aggressions food_events groomings + aggressions food_events groomings groom_scans ## ## CAUTION: This Makefile is not designed to be run directly. It is normally diff --git a/db/schemas/sokwedb/indexes/create/groom_scans.m4 b/db/schemas/sokwedb/indexes/create/groom_scans.m4 new file mode 100644 index 0000000..3e7f143 --- /dev/null +++ b/db/schemas/sokwedb/indexes/create/groom_scans.m4 @@ -0,0 +1,29 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`indexmacros.m4')dnl + +CREATE UNIQUE INDEX IF NOT EXISTS + "GROOM_SCANS has, at most, a 1-to-1 releationship with EVENTS" + ON groom_scans + (eid); +-- We won't index ExtractedBy because this is not expected to be +-- frequently searched. diff --git a/db/schemas/sokwedb/indexes/drop/groom_scans.m4 b/db/schemas/sokwedb/indexes/drop/groom_scans.m4 new file mode 100644 index 0000000..7d24017 --- /dev/null +++ b/db/schemas/sokwedb/indexes/drop/groom_scans.m4 @@ -0,0 +1,25 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`indexmacros.m4')dnl + +DROP INDEX IF EXISTS + "GROOM_SCANS has, at most, a 1-to-1 releationship with EVENTS"; diff --git a/db/schemas/sokwedb/tables/Makefile b/db/schemas/sokwedb/tables/Makefile index b4899f3..aeb15a9 100644 --- a/db/schemas/sokwedb/tables/Makefile +++ b/db/schemas/sokwedb/tables/Makefile @@ -39,7 +39,8 @@ ORDER := biography_data \ aggression_event_log \ non_brec_sighting_sources \ food_events \ - groomings + groomings \ + groom_scans ## ## CAUTION: This Makefile is not designed to be run directly. It is normally ## invoked by another Makefile. diff --git a/db/schemas/sokwedb/tables/create/groom_scans.m4 b/db/schemas/sokwedb/tables/create/groom_scans.m4 new file mode 100644 index 0000000..a7390ca --- /dev/null +++ b/db/schemas/sokwedb/tables/create/groom_scans.m4 @@ -0,0 +1,36 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`tablemacros.m4')dnl +include(`grants.m4')dnl +dnl + +CREATE TABLE groom_scans ( + key_column(`GROOM_SCANS', `GSID', INTEGER) + ,eid INTEGER NOT NULL + REFERENCES events + ,others BOOLEAN NOT NULL + ,duplicate BOOLEAN NOT NULL + ,extractedby TEXT NOT NULL + REFERENCES people +); + +grant_priv(`GROOM_SCANS') diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index 877d9bb..52f7241 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -394,6 +394,18 @@ sdb_generated_rst()dnl .. |FOLLOW_STUDIES.Code| replace:: :ref:`Code ` +.. |GROOM_SCANS| replace:: :ref:`GROOM_SCANS ` +.. |GROOM_SCANS.GSID| replace:: + :ref:`GSID ` +.. |GROOM_SCANS.EID| replace:: + :ref:`EID ` +.. |GROOM_SCANS.Others| replace:: + :ref:`Others ` +.. |GROOM_SCANS.Duplicate| replace:: + :ref:`Duplicate ` +.. |GROOM_SCANS.ExtractedBy| replace:: + :ref:`ExtractedBy ` + .. |GROOMINGS| replace:: :ref:`GROOMINGS ` .. |GROOMINGS.GrID| replace:: :ref:`GrID ` diff --git a/doc/src/tables.m4 b/doc/src/tables.m4 index 976ca63..51edd2d 100644 --- a/doc/src/tables.m4 +++ b/doc/src/tables.m4 @@ -45,6 +45,7 @@ and are therefore the result of at least a rudimentary analytical process. tables/follow_observers.rst tables/follow_studies.rst tables/food_events.rst + tables/groom_scans.rst tables/groomings.rst tables/humans.rst tables/roles.rst diff --git a/doc/src/tables/events.m4 b/doc/src/tables/events.m4 index 6715e42..5f6f885 100644 --- a/doc/src/tables/events.m4 +++ b/doc/src/tables/events.m4 @@ -190,6 +190,42 @@ The following table lists these rules and implications: both, are uncertain. +.. _EVENTS_groom_scan_code: + +``sdb_groom_scan`` (SCAN interval Groomings) + A related row should exist on |GROOM_SCANS|; there should be a row on + |GROOM_SCANS| with an |GROOM_SCANS.EID| value of the event's + |EVENTS.EID|. + There may be at most one of these related |GROOM_SCANS| rows. + The system will generate a warning when there is no |GROOM_SCANS| + row related to the grooming event. + + The |ROLES| rows related to the event, the rows with a |ROLES.EID| + value equal to the |EVENTS.EID| value, designates the + individuals involved in the grooming event. + The |ROLES|.\ |ROLES.Role| code of each individual that the |ROLES| + table relates to the grooming event describes whether that + individual groomed or was groomed during the groom interval + sampling event. + + There should be exactly two |ROLES| row related to the grooming + interval sampling event. + The only two |ROLES|.\ |ROLES.Role| codes allowed are ``sdb_actor`` and + ``sdb_actee``. + One of those two roles must be ``sdb_actor`` and the other must be + ``sdb_actee``. + The system will generate a warning when there are not exactly two + |ROLES| rows related to an grooming event. + + The two participants in a grooming interval sampling event must be + different individuals. + This means that their |ROLES|.\ |ROLES.Participant| values must + differ. + + For grooming interval sampling events, the |EVENTS|.\ + |EVENTS.Certainty| column is always |true|. + + .. _EVENTS_mating_code: ``sdb_mating`` (Mating) diff --git a/doc/src/tables/groom_scans.m4 b/doc/src/tables/groom_scans.m4 new file mode 100644 index 0000000..24eea19 --- /dev/null +++ b/doc/src/tables/groom_scans.m4 @@ -0,0 +1,135 @@ +.. 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 + +.. _GROOM_SCANS: + +GROOM_SCANS +----------- + +.. |GROOM_SCANS_summary| replace:: + Each row, taken together with the related |EVENTS| row, represents + a grooming event recorded during a follow, over a regular, + periodic, sampling interval, during which period all observed + grooming pairs were recorded. + Groomings are recorded as dyadic pairs. + +|GROOM_SCANS_summary| + +In this data set, all groomings observed in periodic 5 minute +intervals were recorded. + +Should there be multiple individuals, whether groomer or recipient, +involved in simultaneous grooming behavior, there should be multiple +rows in the GROOM_SCANS table. +There should be one row for each dyad. + + +.. caution:: + + The system's design cannot distinguish between multiple multi-party + grooming events that are recorded as part of a single follow and + occur at a given time, and a single, larger, multi-party grooming + event recorded in the given follow at the given time.\ [#f1]_ + +The related |EVENTS| row must be a grooming interval sampling event; it +must have an |EVENTS|.\ |EVENTS.Behavior| value of ``sdb_groom_scan``. +This related |EVENTS| row supplies the time of the grooming and +relates to the follow, and the |ROLES| role related to the event +supplies information on the individuals involved. + +For further information, including additional data integrity rules, +see the documentation of the :ref:`EVENTS ` +table. + + +.. contents:: + :depth: 2 + + +.. _GROOM_SCANS.GSID: + +GSID (Grooming Scan ID) +``````````````````````` + +.. |GROOM_SCANS.GSID_summary| replace:: |idcol| + +|GROOM_SCANS.GSID_summary| |notnull| + + +.. _GROOM_SCANS.EID: + +EID (Event ID) +`````````````` + +.. |GROOM_SCANS.EID_summary| replace:: + The |EVENTS|.\ |EVENTS.EID| identifying the groom sampling event. + +|GROOM_SCANS.EID_summary| +The related event contains information on the time of the grooming +and is related to the participants in the groom sampling event. + +|notnull| + + +.. _GROOM_SCANS.Others: + +Others +`````` + +.. |GROOM_SCANS.Others_summary| replace:: + A |boolean| value indicating whether others participated in the + grooming. + +|GROOM_SCANS.Others_summary| +|notnull| + + +.. _GROOM_SCANS.Duplicate: + +Duplicate +````````` + +.. |GROOM_SCANS.Duplicate_summary| replace:: + A |boolean| value indicating whether the grooming is duplicated due + to overlapping follows of different focals. + +|GROOM_SCANS.Duplicate_summary| +When there are duplicates only one of the duplicates is so marked. +|notnull| + + +.. _GROOM_SCANS.Extractedby: + +Extractedby +``````````` + +.. |GROOM_SCANS.Extractedby_summary| replace:: + The person who extracted the grooming information from the + written records and prepared it for data entry into the database. + +|GROOM_SCANS.Extractedby_summary| |notnull| + + +.. ::rubric:: Footnotes + +.. [#f1] + More information related to this problem can be found in the + documentation of the |AGGRESSIONS| table. diff --git a/include/global_constants.m4 b/include/global_constants.m4 index 8bab738..7cfc02a 100644 --- a/include/global_constants.m4 +++ b/include/global_constants.m4 @@ -110,6 +110,8 @@ dnl The arrival event define(`sdb_arrival', `ARR') dnl The food/eating event define(`sdb_food', `FOOD') +dnl Grooming interval sampling +define(`sdb_groom_scan', `GSCAN') dnl The grooming event define(`sdb_grooming', `GROOM') dnl The mating event -- 2.34.1