From aeec91825f92db595f5ce107eb954cb4a6ee034f Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Fri, 19 Jun 2026 00:09:59 +0000 Subject: [PATCH] Create, document, and trigger BRECORD_NOTES --- db/schemas/lib/triggers/create/events.m4 | 15 +- db/schemas/lib/triggers/create/roles.m4 | 5 +- db/schemas/sokwedb/indexes/create/watches.m4 | 7 + db/schemas/sokwedb/indexes/drop/watches.m4 | 2 + db/schemas/sokwedb/tables/Makefile | 3 +- .../sokwedb/tables/create/brecord_notes.m4 | 51 + db/schemas/sokwedb/tables/create/events.m4 | 3 + doc/diagrams/events2.svg | 1552 +++++++++++++++++ doc/src/epilog.inc.m4 | 22 + doc/src/er_diagrams.m4 | 1 + doc/src/er_diagrams/events2.m4 | 30 + doc/src/tables.m4 | 1 + doc/src/tables/brecord_notes.m4 | 180 ++ doc/src/tables/events.m4 | 52 + doc/src/tables/watches.m4 | 32 + include/global_constants.m4 | 3 + 16 files changed, 1951 insertions(+), 8 deletions(-) create mode 100644 db/schemas/sokwedb/tables/create/brecord_notes.m4 create mode 100644 doc/diagrams/events2.svg create mode 100644 doc/src/er_diagrams/events2.m4 create mode 100644 doc/src/tables/brecord_notes.m4 diff --git a/db/schemas/lib/triggers/create/events.m4 b/db/schemas/lib/triggers/create/events.m4 index db6249d..41daf89 100644 --- a/db/schemas/lib/triggers/create/events.m4 +++ b/db/schemas/lib/triggers/create/events.m4 @@ -138,16 +138,21 @@ CREATE OR REPLACE FUNCTION events_func () IF NEW.behavior = 'sdb_gps' OR NEW.behavior = 'sdb_map' - OR NEW.behavior = 'sdb_pg_event' THEN + OR NEW.behavior = 'sdb_pg_event' + OR NEW.behavior = 'sdb_brec_note' THEN -- The WATCHES row must be for a follow, if there is one, and if not - -- then for a location, if it's gps or a map location, and then - -- for a pantgrunt, if it's a pantgrunt. + -- then for a location, if it's gps or a map location, + -- for a pantgrunt, if it's a pantgrunt, and then for a + -- B record, if it's a B record note. -- 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. + -- or for a follow and a pantgrunt, or for a follow and a B record + -- watch, for the same individual for the same date, so this + -- simplifies things. IF NEW.behavior = 'sdb_pg_event' THEN watch_type := 'sdb_pantgrunt'; + ELSIF NEW.behavior = 'sdb_brec_note' THEN + watch_type := 'sdb_brec'; ELSE watch_type := 'sdb_location'; END IF; diff --git a/db/schemas/lib/triggers/create/roles.m4 b/db/schemas/lib/triggers/create/roles.m4 index d538a69..7aa8d63 100644 --- a/db/schemas/lib/triggers/create/roles.m4 +++ b/db/schemas/lib/triggers/create/roles.m4 @@ -71,9 +71,10 @@ CREATE OR REPLACE FUNCTION roles_func () WHERE events.eid = NEW.eid; END IF; - -- The event may not be a sdb_other_species type of event + -- The event may not be a sdb_other_species, or sdb_brec_note, type of event IF TG_OP = 'INSERT' - AND a_behavior = 'sdb_other_species' THEN + AND (a_behavior = 'sdb_other_species' + OR a_behavior = 'sdb_brec_note') THEN DECLARE a_focal watches.focal%TYPE; a_date watches.date%TYPE; diff --git a/db/schemas/sokwedb/indexes/create/watches.m4 b/db/schemas/sokwedb/indexes/create/watches.m4 index ebb262a..0fc7f4c 100644 --- a/db/schemas/sokwedb/indexes/create/watches.m4 +++ b/db/schemas/sokwedb/indexes/create/watches.m4 @@ -21,6 +21,13 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl +CREATE UNIQUE INDEX IF NOT EXISTS + "(Type)=(sdb_follow) or (Type)=(sdb_brec) means Date + Focal must be unique" + ON watches + (date, focal) + WHERE type = 'sdb_follow' + OR type = 'sdb_brec'; + CREATE UNIQUE INDEX IF NOT EXISTS "(Type)=(sdb_follow) or (Type)=(sdb_location) means 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 b95ee41..323cde8 100644 --- a/db/schemas/sokwedb/indexes/drop/watches.m4 +++ b/db/schemas/sokwedb/indexes/drop/watches.m4 @@ -21,6 +21,8 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl +DROP INDEX IF EXISTS + "(Type)=(sdb_follow) or (Type)=(sdb_brec) means Date + Focal must be unique"; DROP INDEX IF EXISTS "(Type)=(sdb_follow) or (Type)=(sdb_location) means Date + Focal must be unique"; diff --git a/db/schemas/sokwedb/tables/Makefile b/db/schemas/sokwedb/tables/Makefile index 3289b7e..657b42a 100644 --- a/db/schemas/sokwedb/tables/Makefile +++ b/db/schemas/sokwedb/tables/Makefile @@ -46,7 +46,8 @@ ORDER := biography_data \ repro_states \ locations_gps \ locations_map \ - pantgrunts + pantgrunts \ + brecord_notes ## ## 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/brecord_notes.m4 b/db/schemas/sokwedb/tables/create/brecord_notes.m4 new file mode 100644 index 0000000..fefd9a6 --- /dev/null +++ b/db/schemas/sokwedb/tables/create/brecord_notes.m4 @@ -0,0 +1,51 @@ +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 brecord_notes ( + eid INTEGER NOT NULL + REFERENCES events + ,observation TEXT NOT NULL + emptytext_check(`Observation') + ,comments TEXT NOT NULL + emptytext_check(`Comments') + ,observer TEXT NOT NULL + emptytext_check(`Observer') + ,translator TEXT NOT NULL + emptytext_check(`Translator') + ,transcribedby TEXT NOT NULL + emptytext_check(`TranscribedBy') + ,voc TEXT NOT NULL + emptytext_check(`Voc') + ,vocid TEXT NOT NULL + emptytext_check(`VocID') + ,groomingaggression TEXT NOT NULL + emptytext_check(`GroomingAggression') + ,duplicate TEXT NOT NULL + emptytext_check(`Duplicate') +); + +eid_primary_key(`BRECORD_NOTES') + +grant_priv(`BRECORD_NOTES') diff --git a/db/schemas/sokwedb/tables/create/events.m4 b/db/schemas/sokwedb/tables/create/events.m4 index a27e0a4..39c2830 100644 --- a/db/schemas/sokwedb/tables/create/events.m4 +++ b/db/schemas/sokwedb/tables/create/events.m4 @@ -67,6 +67,7 @@ CREATE TABLE events ( CHECK (behavior IN ('sdb_aggression' , 'sdb_arrival' , 'sdb_arrival_a' + , 'sdb_brec_note' , 'sdb_food' , 'sdb_gps' , 'sdb_grooming' @@ -98,6 +99,7 @@ CREATE TABLE events ( _point_behavior_time(`sdb_pg_event') _point_behavior_time(`sdb_gps') _point_behavior_time(`sdb_map') + _point_behavior_time(`sdb_brec_note') _behavior_certain(`sdb_arrival_a') _behavior_certain(`sdb_groom_scan') @@ -106,6 +108,7 @@ CREATE TABLE events ( _behavior_certain(`sdb_pg_event') _behavior_certain(`sdb_gps') _behavior_certain(`sdb_map') + _behavior_certain(`sdb_brec_note') ); grant_priv(`EVENTS') diff --git a/doc/diagrams/events2.svg b/doc/diagrams/events2.svg new file mode 100644 index 0000000..17fada2 --- /dev/null +++ b/doc/diagrams/events2.svg @@ -0,0 +1,1552 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + image/svg+xml + + + + + + + + + + + + + BRecorddetail + BRecordeventinformation + + BRECORD_NOTES + + Observation + + Comments + + Observer + + Translator + + TranscribedBy + + Voc + + EID + + + VocID + + Duplicate + + GroomingAggression + + + + + + EVENTS + + EID + + Behavior + + WID + + Start + + Stop + + Certainty * + + + Notes + + + + diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index 5dc3963..793d3d6 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -219,6 +219,28 @@ sdb_generated_rst()dnl .. |BIOGRAPHY_LOG.MadeBy| replace:: :ref:`MadeBy ` +.. |BRECORD_NOTES| replace:: :ref:`BRECORD_NOTES ` +.. |BRECORD_NOTES.EID| replace:: + :ref:`EID ` +.. |BRECORD_NOTES.Observation| replace:: + :ref:`Observation ` +.. |BRECORD_NOTES.Comments| replace:: + :ref:`Comments ` +.. |BRECORD_NOTES.Observer| replace:: + :ref:`Observer ` +.. |BRECORD_NOTES.Translator| replace:: + :ref:`Translator ` +.. |BRECORD_NOTES.TranscribedBy| replace:: + :ref:`TranscribedBy ` +.. |BRECORD_NOTES.Voc| replace:: + :ref:`Voc ` +.. |BRECORD_NOTES.VocID| replace:: + :ref:`VocID ` +.. |BRECORD_NOTES.GroomingAggression| replace:: + :ref:`GroomingAggression ` +.. |BRECORD_NOTES.Duplicate| replace:: + :ref:`Duplicate ` + .. |CERTAINTIES| replace:: :ref:`CERTAINTIES ` .. |CERTAINTIES.Certainty| replace:: :ref:`Certainty ` diff --git a/doc/src/er_diagrams.m4 b/doc/src/er_diagrams.m4 index 3562e94..6b74ba0 100644 --- a/doc/src/er_diagrams.m4 +++ b/doc/src/er_diagrams.m4 @@ -95,6 +95,7 @@ related to other tables -- when they are *not* log tables. er_diagrams/reproduction.rst er_diagrams/watches.rst er_diagrams/events.rst + er_diagrams/events2.rst .. rubric:: Footnotes diff --git a/doc/src/er_diagrams/events2.m4 b/doc/src/er_diagrams/events2.m4 new file mode 100644 index 0000000..698e6fd --- /dev/null +++ b/doc/src/er_diagrams/events2.m4 @@ -0,0 +1,30 @@ +.. 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 + + +Additional Events +----------------- + +.. figure:: sdb_er_image_path(images/events2) + :alt: Additional Events Entity-Relationship Diagram + :width: 100 % + + Additional Events diff --git a/doc/src/tables.m4 b/doc/src/tables.m4 index 7c07559..4e32581 100644 --- a/doc/src/tables.m4 +++ b/doc/src/tables.m4 @@ -38,6 +38,7 @@ and are therefore the result of at least a rudimentary analytical process. tables/arrivals_a.rst tables/biography_data.rst tables/biography_log.rst + tables/brecord_notes.rst tables/comm_membs.rst tables/comm_memb_log.rst tables/swelling_sources.rst diff --git a/doc/src/tables/brecord_notes.m4 b/doc/src/tables/brecord_notes.m4 new file mode 100644 index 0000000..2bd059d --- /dev/null +++ b/doc/src/tables/brecord_notes.m4 @@ -0,0 +1,180 @@ +.. 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 + +.. _BRECORD_NOTES: + +BRECORD_NOTES +------------- + +.. |BRECORD_NOTES_summary| replace:: + Each row contains the English translation of notes that were taken + at a particular time during a B-Record focal follow. + The related |EVENTS| row contains the time of the note taking. + +|BRECORD_NOTES_summary| + +Because this is textual data, no attempt is made to make the table's +content more than a collection of plain text. + +.. contents:: + :depth: 2 + + +.. _BRECORD_NOTES.EID: + +EID (Event ID) +`````````````` + +.. |BRECORD_NOTES.EID_summary| replace:: + The |EVENTS|.\ |EVENTS.EID| identifying a BRecord translation event. + |idcol| + +|BRECORD_NOTES.EID_summary| +The related event contains information on the time the textual record +was taken. +|notnull| + + +.. _BRECORD_NOTES.Observation: + +Observation +``````````` + +.. |BRECORD_NOTES.Observation_summary| replace:: + The (translated to English) text of the observation. + +|BRECORD_NOTES.Observation_summary| +A narrative account of the focal chimp’s behavior and interactions +with others. + +|notonlyspaces| +|notnull| + + +.. _BRECORD_NOTES.Comments: + +Comments +```````` + +.. |BRECORD_NOTES.Comments_summary| replace:: + Additional comments regarding the follow. + +|BRECORD_NOTES.Comments_summary| +Examples include: pages missing, duplicate times recorded by the field +assistant(s), or any other problems/questions. + +|notonlyspaces| +|notnull| + + +.. _BRECORD_NOTES.Observer: + +Observer +```````` + +.. |BRECORD_NOTES.Observer_summary| replace:: + First and last name of the field assistant(s) who recorded the + BRecord notes. + +|BRECORD_NOTES.Observer_summary| +|notonlyspaces| +|notnull| + + +.. _BRECORD_NOTES.Translator: + +Translator +`````````` + +.. |BRECORD_NOTES.Translator_summary| replace:: + Name of translator. + +|BRECORD_NOTES.Translator_summary| +|notonlyspaces| +|notnull| + + +.. _BRECORD_NOTES.TranscribedBy: + +TranscribedBy +````````````` + +.. |BRECORD_NOTES.TranscribedBy_summary| replace:: + Name of the person who transcribed the translation. + +|BRECORD_NOTES.TranscribedBy_summary| +|notonlyspaces| +|notnull| + + +.. _BRECORD_NOTES.Voc: + +Voc +``` + + +.. |BRECORD_NOTES.Voc_summary| replace:: + Text to do with the vocalizations made by the chimpanzees. + +|BRECORD_NOTES.Voc_summary| +|notonlyspaces| +|notnull| + + +.. _BRECORD_NOTES.VocID: + +VocID +````` + + +.. |BRECORD_NOTES.VocID_summary| replace:: + More text to do with the vocalizations made by the chimpanzees. + +|BRECORD_NOTES.VocID_summary| +|notonlyspaces| +|notnull| + + +.. _BRECORD_NOTES.GroomingAggression: + +GroomingAggression +`````````````````` + +.. |BRECORD_NOTES.GroomingAggression_summary| replace:: + Text containing very short codes that describe the grooming and + aggression that occurred. + +|BRECORD_NOTES.GroomingAggression_summary| +|notonlyspaces| +|notnull| + + +.. _BRECORD_NOTES.Duplicate: + +Duplicate +````````` + +.. |BRECORD_NOTES.Duplicate_summary| replace:: + Text related to duplicate data tracking. + +|BRECORD_NOTES.Duplicate_summary| +|notonlyspaces| +|notnull| diff --git a/doc/src/tables/events.m4 b/doc/src/tables/events.m4 index 96e059e..f5aea66 100644 --- a/doc/src/tables/events.m4 +++ b/doc/src/tables/events.m4 @@ -217,6 +217,55 @@ The following table lists these rules and implications: the the individual identified in the related |ROLES| row. +.. _EVENTS_brec_note_code: + +``sdb_brec_note`` (B-RECord note translation) + The EVENTS row must be associated with a |WATCHES| row representing + either a follow or a B-Record taking. + This means the |EVENTS.WID| column must reference a |WATCHES| row + with a |WATCHES|.\ |WATCHES.Type| value of either ``sdb_follow`` or + ``sdb_brec``. + Further, as explained in the |WATCHES| documentation, if there is a + follow for a given individual on a given day then the EVENTS row + must be associated with the follow. + + A related row should exist on |BRECORD_NOTES|; there should be a + row on |BRECORD_NOTES| with a |BRECORD_NOTES|.\ + |BRECORD_NOTES.EID| value of the event's |EVENTS.EID|. + There may be at most one of these related |BRECORD_NOTES| rows. + The system will generate a warning when there is no |BRECORD_NOTES| + row related to the B-Record note taking event. + + An individual should have only one B-Record note taken at any given + time. + This means that the system will generate a warning when the + combination of |EVENTS.WID| and |EVENTS.Start| is not unique. + + There can be no |ROLES| rows related to the event. + The concept behind this is that, even though B-Record notes are + associated with an individual, the related |WATCHES|.\ + |WATCHES.Focal|, B-Record note translations are an administrative + record. + The focal of the B-Record follow, therefore, is not playing a + paricular role in some behavioral category and so there should be + no row on |ROLES| for the individual. + + Both the EVENTS.\ |EVENTS.Start| and EVENTS.\ |EVENTS.Stop| + columns record the time the note was taken. + This means the value of the EVENTS.\ |EVENTS.Start| column must + equal the value of the EVENTS.\ |EVENTS.Stop| column. + + For B-Record note taking events, the EVENTS.\ |EVENTS.Certainty| + column must be ``sdb_identity_certain``. + + For B-Record note taking events, the EVENTS.\ |EVENTS.Notes| + column is expected to be empty. + (The expectation is that notes on the record-taking belong in the + |BRECORD_NOTES|.\ |BRECORD_NOTES.Comments| column.) + The system will generate a warning when EVENTS.\ |EVENTS.Notes| + is not the empty string. + + .. _EVENTS_food_code: ``sdb_food`` (Food) @@ -616,6 +665,9 @@ The following list summarizes the available codes: ``sdb_arrival`` (Arrival) A row must exist on |ARRIVALS|. +``sdb_brec_note`` (B-RECord note) + A row must exist on |BRECORD_NOTES|. + ``sdb_gps`` (GPS Location) A row must exist on |LOCATIONS_GPS|. diff --git a/doc/src/tables/watches.m4 b/doc/src/tables/watches.m4 index 067bbd9..092f083 100644 --- a/doc/src/tables/watches.m4 +++ b/doc/src/tables/watches.m4 @@ -117,6 +117,28 @@ The available |WATCHES.Type| values are: times on a single day, to record this the one WATCHES row has multiple related rows on the |EVENTS| table. +``sdb_brec`` (B-Record taken) + Each row represents a date on which an individual has B-Record + notes translated into English, but there is no follow on record in + the database. + + The |WATCHES.Focal| column contains the |BIOGRAPHY_DATA|.\ + |BIOGRAPHY_DATA.AnimID| of the individual. + + The |WATCHES.CommID| column contains the code for the community + recorded along with the B-Record notes; the |COMM_IDS|.\ + |COMM_IDS.CommID| of the community. + This is not necessarily the community the individual is a member + of, although it usually is. + + There may only be one row on WATCHES per day to record an + individual who has translated B-Record notes. + This means, the combination of |WATCHES.Type|, |WATCHES.Focal| and + |WATCHES.Date| must be unique. + If there were multiple times on a single day that B-Record notes + were taken, the one WATCHES row is related to multiple rows on the + |EVENTS| table. + ``sdb_follow`` (Follow) Each row represents a date during which observers were assigned to follow a focal individual. @@ -237,6 +259,14 @@ The available |WATCHES.Type| values are: 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 B-Record type, |WATCHES.Type| = ``sdb_brec``, +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 has translated B-Records 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_brec``, and +vice-versa. + 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. @@ -335,6 +365,8 @@ One of the following values: ``sdb_attendance`` (Attendance) +``sdb_brec`` (B-Record) + ``sdb_follow`` (Follow) ``sdb_ag_scan`` (Attendance Groom Scan) diff --git a/include/global_constants.m4 b/include/global_constants.m4 index 6a31384..263acf9 100644 --- a/include/global_constants.m4 +++ b/include/global_constants.m4 @@ -121,6 +121,8 @@ dnl The A-Record (attendance at feeding station) groom scan define(`sdb_groom_scan_a', `AGSCAN') dnl The arrival event define(`sdb_arrival', `ARR') +dnl B-Record note taking +define(`sdb_brec_note', `BREC') dnl The food/eating event define(`sdb_food', `FOOD') dnl The recording of a GPS location @@ -195,6 +197,7 @@ dnl dnl The WATCHES.Type values define(`sdb_attendance', `A') +define(`sdb_brec', `B') define(`sdb_follow', `F') define(`sdb_ag_scan', `G') define(`sdb_location', `L') -- 2.34.1