From 9b61f8bf2dabc97f4a328615e7ba43047495cc58 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Tue, 9 Jun 2026 21:34:10 +0000 Subject: [PATCH] Create, document, index, and trigger PANTGRUNTS --- db/schemas/lib/triggers/Makefile | 3 +- db/schemas/lib/triggers/create/pantgrunts.m4 | 122 ++++++++++++++ db/schemas/lib/triggers/create/roles.m4 | 51 ++++++ db/schemas/lib/triggers/drop/pantgrunts.m4 | 23 +++ db/schemas/sokwedb/tables/Makefile | 3 +- db/schemas/sokwedb/tables/create/events.m4 | 3 + .../sokwedb/tables/create/pantgrunts.m4 | 41 +++++ doc/src/epilog.inc.m4 | 12 ++ doc/src/tables.m4 | 1 + doc/src/tables/events.m4 | 38 ++++- doc/src/tables/pantgrunts.m4 | 149 ++++++++++++++++++ 11 files changed, 443 insertions(+), 3 deletions(-) create mode 100644 db/schemas/lib/triggers/create/pantgrunts.m4 create mode 100644 db/schemas/lib/triggers/drop/pantgrunts.m4 create mode 100644 db/schemas/sokwedb/tables/create/pantgrunts.m4 create mode 100644 doc/src/tables/pantgrunts.m4 diff --git a/db/schemas/lib/triggers/Makefile b/db/schemas/lib/triggers/Makefile index 9c84125..e5402ab 100644 --- a/db/schemas/lib/triggers/Makefile +++ b/db/schemas/lib/triggers/Makefile @@ -44,7 +44,8 @@ ORDER := comm_ids \ humans \ species_present \ repro_states \ - locations_b + locations_b \ + pantgrunts DROP_EXISTING := true diff --git a/db/schemas/lib/triggers/create/pantgrunts.m4 b/db/schemas/lib/triggers/create/pantgrunts.m4 new file mode 100644 index 0000000..8f7fef9 --- /dev/null +++ b/db/schemas/lib/triggers/create/pantgrunts.m4 @@ -0,0 +1,122 @@ +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 pantgrunts table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`macros.m4')dnl + + +RAISE INFO 'pantgrunts_func'; +CREATE OR REPLACE FUNCTION pantgrunts_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + BEGIN + -- Function for pantgrunts insert and update triggers + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + + IF TG_OP = 'UPDATE' THEN + cannot_change(`PANTGRUNTS', `EID') + END IF; + + -- The EVENTS.Behavior must be sdb_pantgrunt + IF TG_OP = 'INSERT' 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_pantgrunt' + ORDER BY roles.participant; -- Consistent error message + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of PANTGRUNTS' + , DETAIL = 'Pantgrunts can only be related to an event with an' + || ' EVENTS.Behavior value of (sdb_pantgrunt)' + || ': Key (EID = (' + || NEW.eid + || '): Value (MultiActors) = (' + || NEW.multiactors + || '): Value (MultiRecipients) = (' + || NEW.multirecipients + || '): Value (CommID) = (' + || NEW.commid + || '): Value (Source) = (' + || NEW.source + || '): Value (EnteredBy) = (' + || NEW.Enteredby + || '): 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 'pantgrunts_trigger'; +CREATE TRIGGER pantgrunts_trigger + AFTER INSERT OR UPDATE + ON pantgrunts FOR EACH ROW + EXECUTE PROCEDURE pantgrunts_func(); diff --git a/db/schemas/lib/triggers/create/roles.m4 b/db/schemas/lib/triggers/create/roles.m4 index 4d757b0..99493a0 100644 --- a/db/schemas/lib/triggers/create/roles.m4 +++ b/db/schemas/lib/triggers/create/roles.m4 @@ -549,6 +549,57 @@ CREATE OR REPLACE FUNCTION roles_func () 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 NEW.role <> 'sdb_actor' + AND NEW.role <> 'sdb_actee' + AND NEW.role <> 'sdb_mutual' THEN + DECLARE + a_focal follows.focal%TYPE; + a_date follows.date%TYPE; + a_commid follows.commid%TYPE; + + BEGIN + SELECT follows.focal, follows.date, follows.commid + INTO a_focal , a_date , a_commid + FROM follows + WHERE follows.fid = a_fid; + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on INSERT of ROLES' + , DETAIL = 'Invalid Roles value for a sdb_pantgrunt event' + || ': 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 (FOLLOWS.FID) = (' + || a_fid + || '), Value (FOLLOWS.Focal) = (' + || a_focal + || '), Value (FOLLOWS.Date) = (' + || a_date + || '), Value (FOLLOWS.CommID) = (' + || a_commid + || ')' + , HINT = 'When EVENTS.Behavior = (sdb_pantgrunt) the only' + || ' ROLES.Role values allowed are: sdb_actor,' + || ' sdb_actee, and sdb_mutual'; + END; + END IF; + -- -- Depending on the behavior, when there are multiple participants -- each role must be distinct. diff --git a/db/schemas/lib/triggers/drop/pantgrunts.m4 b/db/schemas/lib/triggers/drop/pantgrunts.m4 new file mode 100644 index 0000000..1c165e0 --- /dev/null +++ b/db/schemas/lib/triggers/drop/pantgrunts.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 aggressions table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl + +DROP FUNCTION IF EXISTS pantgrunts_func() CASCADE; diff --git a/db/schemas/sokwedb/tables/Makefile b/db/schemas/sokwedb/tables/Makefile index ab4cd49..d2f771e 100644 --- a/db/schemas/sokwedb/tables/Makefile +++ b/db/schemas/sokwedb/tables/Makefile @@ -44,7 +44,8 @@ ORDER := biography_data \ groom_scans_b \ arrivals_a \ repro_states \ - locations_b + locations_b \ + pantgrunts ## ## 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/events.m4 b/db/schemas/sokwedb/tables/create/events.m4 index 76df96f..ba45ce8 100644 --- a/db/schemas/sokwedb/tables/create/events.m4 +++ b/db/schemas/sokwedb/tables/create/events.m4 @@ -121,6 +121,7 @@ CREATE TABLE events ( _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_aggression', `FID') _pair_behavior_source(`sdb_arrival', `FID') @@ -128,6 +129,7 @@ CREATE TABLE events ( _pair_behavior_source(`sdb_grooming', `FID') _pair_behavior_source(`sdb_groom_scan', `FID') _pair_behavior_source(`sdb_other_species', `FID') + _pair_behavior_source(`sdb_pantgrunt', `FID') _pair_behavior_source(`sdb_arrival_a', `AtID') _pair_behavior_source(`sdb_groom_scan_a', `AtID') @@ -136,6 +138,7 @@ CREATE TABLE events ( _behavior_certain(`sdb_groom_scan') _behavior_certain(`sdb_groom_scan_a') _behavior_certain(`sdb_other_species') + _behavior_certain(`sdb_pantgrunt') ); grant_priv(`EVENTS') diff --git a/db/schemas/sokwedb/tables/create/pantgrunts.m4 b/db/schemas/sokwedb/tables/create/pantgrunts.m4 new file mode 100644 index 0000000..0c416a3 --- /dev/null +++ b/db/schemas/sokwedb/tables/create/pantgrunts.m4 @@ -0,0 +1,41 @@ +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 pantgrunts ( + eid INTEGER NOT NULL + REFERENCES events + ,multiactors BOOLEAN NOT NULL + ,multirecipients BOOLEAN NOT NULL + ,commid TEXT NOT NULL + REFERENCES comm_ids + ,source TEXT NOT NULL + REFERENCES pg_sources + ,enteredby TEXT NOT NULL + REFERENCES people +); + +eid_primary_key(`PANTGRUNTS') + +grant_priv(`PANTGRUNTS') diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index df2a121..ec264fa 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -483,6 +483,18 @@ sdb_generated_rst()dnl .. |OBS_PERIODS.Period| replace:: :ref:`Period ` +.. |PANTGRUNTS| replace:: :ref:`PANTGRUNTS ` +.. |PANTGRUNTS.EID| replace:: + :ref:`EID ` +.. |PANTGRUNTS.MultiActors| replace:: + :ref:`MultiActors ` +.. |PANTGRUNTS.MultiRrecipients| replace:: + :ref:`MultiRecipients ` +.. |PANTGRUNTS.CommID| replace:: + :ref:`CommID ` +.. |PANTGRUNTS.EnteredBy| replace:: + :ref:`EnteredBy ` + .. |PARITIES| replace:: :ref:`PARITIES ` .. |PARITIES.Code| replace:: :ref:`Code ` diff --git a/doc/src/tables.m4 b/doc/src/tables.m4 index 6dfb480..16fa0be 100644 --- a/doc/src/tables.m4 +++ b/doc/src/tables.m4 @@ -51,6 +51,7 @@ and are therefore the result of at least a rudimentary analytical process. tables/groomings.rst tables/humans.rst tables/locations_b.rst + tables/pantgrunts.rst tables/roles.rst tables/non_brec_sighting_sources.rst tables/species_present.rst diff --git a/doc/src/tables/events.m4 b/doc/src/tables/events.m4 index 5badfc2..99b7e68 100644 --- a/doc/src/tables/events.m4 +++ b/doc/src/tables/events.m4 @@ -370,7 +370,43 @@ The following table lists these rules and implications: .. _EVENTS_pantgrunt_code: ``sdb_pantgrunt`` (Pantgrunt) - A row must exist on PANTGRUNTS. + 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 |PANTGRUNTS|; there should be a row on + |PANTGRUNTS| with a |PANTGRUNTS|.\ |PANTGRUNTS.EID| value of the + event's |EVENTS.EID|. + There may be at most one of these related |PANTGRUNTS| rows. + The system will generate a warning when there is no |PANTGRUNTS| + row related to the pantgrunt event. + + The |ROLES| rows related to the event, the rows with a |ROLES|.\ + |ROLES.EID| value equal to the EVENTS.\ |EVENTS.EID| value, + designates the individuals involved in the pantgrunt event. + The |ROLES|.\ |ROLES.Role| code of each individual that the |ROLES| + table relates to the pantgrunt event describes whether that + individual gave or received the pantgrunt. + + There should be exactly two |ROLES| row related to the pantgrunt + event. + Only the codes ``sdb_actor``, ``sdb_actee``, and ``sdb_mutual`` + may be used as ROLES.\ |ROLES.Role| code values. + The system will generate a warning when there are not exactly two + |ROLES| rows related to a pantgrunt event. + + The participants in a pantgrunt event must be different + individuals. + This means that their |ROLES|.\ |ROLES.Participant| values must + differ. + + Both the |EVENTS|.\ |EVENTS.Start| and |EVENTS|.\ |EVENTS.Stop| + columns record the time of the scan when the pantgrunt was observed. + This means the value of the EVENTS.\ |EVENTS.Start| column must + equal the value of the |EVENTS|.\ |EVENTS.Stop| column. + + For pantgrunt events, the |EVENTS|.\ |EVENTS.Certainty| column must + be ``sdb_identity_certain``. + The |EVENTS.Start| time cannot be after the |EVENTS.Stop| time. diff --git a/doc/src/tables/pantgrunts.m4 b/doc/src/tables/pantgrunts.m4 new file mode 100644 index 0000000..11d2864 --- /dev/null +++ b/doc/src/tables/pantgrunts.m4 @@ -0,0 +1,149 @@ +.. 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 + +.. _PANTGRUNTS: + +PANTGRUNTS +---------- + +.. |PANTGRUNTS_summary| replace:: + Each row, taken together with the related |EVENTS| row, represents + a pantgrunt event recording during a follow. + Pantgrunts are recorded as dyadic pairs. + +|PANTGRUNTS_summary| + +Should there be multiple individuals, whether the exhibitor or +recipient, involved in simultaneous pantgrunt behavior, there should +be multiple rows in the PANTGRUNTS table. +There should be one row for each dyad. + + +.. caution:: + + The system's design cannot distinguish between multiple multi-party + pantgrunt events that are recorded as part of a single follow and + occur at a given time, and a single, larger, multi-party pantgrunt + event recorded in the given follow at the given time.\ [#f1]_ + +The related |EVENTS| row must be a pantgrunt event; it must have an +|EVENTS|.\ |EVENTS.Behavior| value of ``sdb_pantgrunt``. +This related |EVENTS| row supplies the time of the pantgrunt 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 + + +.. _PANTGRUNTS.EID: + +EID (Event ID) +`````````````` + +.. |PANTGRUNTS.EID_summary| replace:: + The |EVENTS|.\ |EVENTS.EID| identifying the pantgrunt event. + |idcol| + +|PANTGRUNTS.EID_summary| +The related event contains information on the time of the pantgrunt +and is related to the participants in the pantgrunt event. + +|notnull| + + +.. _PANTGRUNTS.MultiActors: + +MultiActors +``````````` + +.. |PANTGRUNTS.MultiActors_summary| replace:: + + A |boolean| value. + When |true|, there were multiple actors performing pantgrunts. + +|PANTGRUNTS.MultiActors_summary| +|notnull| + + +.. _PANTGRUNTS.MultiRecipients: + +MultiRecipients +``````````````` + +.. |PANTGRUNTS.MultiRecipients_summary| replace:: + + A |boolean| value. + When |true|, there were multiple recipients receiving pantgrunts. + +|PANTGRUNTS.MultiRecipients_summary| +|notnull| + + +.. _PANTGRUNTS.CommID: + +CommID +`````` + +.. |PANTGRUNTS.CommID_summary| replace:: + A code for the community involved in the pantgrunt event. + A |COMM_IDS|.\ |COMM_IDS.CommID| value. + This is not necessarily the community the focal is a member of. + +|PANTGRUNTS.CommID_summary| +|notnull| + + +.. _PANTGRUNTS.Source: + +Source +`````` + +.. |PANTGRUNTS.Source_summary| replace:: + A code for the source of the pantgrunt information. + A |PG_SOURCES|.\ |PG_SOURCES.Source| value. + +|PANTGRUNTS.Source_summary| +|notnull| + + +.. _PANTGRUNTS.Enteredby: + +EnteredBy +````````` + +.. |PANTGRUNTS.EnteredBy_summary| replace:: + The person who extracted the pantgrunt information from the + written records and prepared it for data entry into the database. + +|PANTGRUNTS.EnteredBy_summary| |notnull| + + +.. ::rubric:: Footnotes + +.. [#f1] + More information related to this problem can be found in the + documentation of the |AGGRESSIONS| table. -- 2.34.1