From ae2aa8f8d2386ead3c0035a2d5c3ec078d793d03 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Fri, 19 Jun 2026 17:56:31 +0000 Subject: [PATCH] Write and document trigger rules for BRECORD_NOTES --- db/schemas/lib/triggers/Makefile | 3 +- .../lib/triggers/create/brecord_notes.m4 | 117 ++++++++++++++++++ db/schemas/lib/triggers/drop/brecord_notes.m4 | 23 ++++ doc/src/tables/brecord_notes.m4 | 4 + 4 files changed, 146 insertions(+), 1 deletion(-) create mode 100644 db/schemas/lib/triggers/create/brecord_notes.m4 create mode 100644 db/schemas/lib/triggers/drop/brecord_notes.m4 diff --git a/db/schemas/lib/triggers/Makefile b/db/schemas/lib/triggers/Makefile index 2248310..09ff188 100644 --- a/db/schemas/lib/triggers/Makefile +++ b/db/schemas/lib/triggers/Makefile @@ -45,7 +45,8 @@ ORDER := comm_ids \ species_present \ repro_states \ pantgrunts \ - pantgrunts_view + pantgrunts_view \ + brecord_notes DROP_EXISTING := true diff --git a/db/schemas/lib/triggers/create/brecord_notes.m4 b/db/schemas/lib/triggers/create/brecord_notes.m4 new file mode 100644 index 0000000..a21dd3b --- /dev/null +++ b/db/schemas/lib/triggers/create/brecord_notes.m4 @@ -0,0 +1,117 @@ +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 brecord_notes table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`macros.m4')dnl + + +RAISE INFO 'brecord_notes_func'; +CREATE OR REPLACE FUNCTION brecord_notes_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + BEGIN + -- Function for brecord_notes insert and update triggers + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + + IF TG_OP = 'UPDATE' THEN + cannot_change(`BRECORD_NOTES', `EID') + END IF; + + -- The EVENTS.Behavior must be sdb_colobus + IF TG_OP = 'INSERT' THEN + DECLARE + -- EVENTS + a_behavior events.behavior%TYPE; + a_start events.start%TYPE; + a_stop events.stop%TYPE; + -- WATCHES + a_wid watches.wid%TYPE; + a_focal watches.focal%TYPE; + a_date watches.date%TYPE; + + BEGIN + SELECT events.behavior, events.start, events.stop + , watches.wid, watches.focal, watches.date + INTO a_behavior , a_start , a_stop + , a_wid , a_focal , a_date + FROM events + JOIN watches ON (watches.wid = events.wid) + WHERE events.eid = NEW.eid + AND events.behavior <> 'sdb_colobus'; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of BRECORD_NOTES' + , DETAIL = 'B-Record note taking detail can only be related to an' + || ' event with an EVENTS.Behavior value of' + || ' (sdb_brec_note)' + || ': Key (EID = (' + || NEW.eid + || '): Value (Observation) = (' + || NEW.observation + || '): Value (Comments) = (' + || NEW.comments + || '): Value (Observer) = (' + || NEW.observer + || '): Value (Translator) = (' + || NEW.translator + || '): Value (TranscribedBy) = (' + || NEW.transcribedby + || '): Value (Voc) = (' + || NEW.voc + || '): Value (VocID) = (' + || NEW.vocid + || '): Value (GroomingAggression) = (' + || NEW.groomingaggression + || '): Value (Duplicate) = (' + || NEW.duplicate + || '): Key (EVENTS.EID) = (' + || NEW.eid + || '): Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || '): Key (WATCHES.WID) = (' + || a_wid + || '), Value (WATCHES.Focal) = (' + || a_focal + || '), Value (WATCHES.Date) = (' + || a_date + || ')'; + END IF; + END; + END IF; + + RETURN NULL; + END; +$$; + + +RAISE INFO 'brecord_notes_trigger'; +CREATE TRIGGER brecord_notes_trigger + AFTER INSERT OR UPDATE + ON brecord_notes FOR EACH ROW + EXECUTE PROCEDURE brecord_notes_func(); diff --git a/db/schemas/lib/triggers/drop/brecord_notes.m4 b/db/schemas/lib/triggers/drop/brecord_notes.m4 new file mode 100644 index 0000000..6a2f8cb --- /dev/null +++ b/db/schemas/lib/triggers/drop/brecord_notes.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 brecord_notes table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl + +DROP FUNCTION IF EXISTS brecord_notes_func() CASCADE; diff --git a/doc/src/tables/brecord_notes.m4 b/doc/src/tables/brecord_notes.m4 index 2bd059d..9d0a8ba 100644 --- a/doc/src/tables/brecord_notes.m4 +++ b/doc/src/tables/brecord_notes.m4 @@ -31,6 +31,10 @@ BRECORD_NOTES |BRECORD_NOTES_summary| +The related |EVENTS| row must be a B-Record translation event; it +must have an |EVENTS|.\ |EVENTS.Behavior| value of ``sdb_brec_note``. +This related |EVENTS| row supplies the time the note was taken. + Because this is textual data, no attempt is made to make the table's content more than a collection of plain text. -- 2.34.1