From 3cfbaea97d0bcbbabd46b56a2aeeac17b057c41f Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Fri, 5 Jun 2026 19:50:59 +0000 Subject: [PATCH] Write triggers for HUMANS --- db/schemas/lib/triggers/Makefile | 3 +- db/schemas/lib/triggers/create/humans.m4 | 155 +++++++++++++++++++++++ db/schemas/lib/triggers/drop/humans.m4 | 23 ++++ 3 files changed, 180 insertions(+), 1 deletion(-) create mode 100644 db/schemas/lib/triggers/create/humans.m4 create mode 100644 db/schemas/lib/triggers/drop/humans.m4 diff --git a/db/schemas/lib/triggers/Makefile b/db/schemas/lib/triggers/Makefile index a1ed24f..bd84238 100644 --- a/db/schemas/lib/triggers/Makefile +++ b/db/schemas/lib/triggers/Makefile @@ -40,7 +40,8 @@ ORDER := comm_ids \ groom_scans_b \ attendance \ arrivals_a \ - swelling_sources + swelling_sources \ + humans DROP_EXISTING := true diff --git a/db/schemas/lib/triggers/create/humans.m4 b/db/schemas/lib/triggers/create/humans.m4 new file mode 100644 index 0000000..0f33944 --- /dev/null +++ b/db/schemas/lib/triggers/create/humans.m4 @@ -0,0 +1,155 @@ +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 humans table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`macros.m4')dnl + + +RAISE INFO 'humans_func'; +CREATE OR REPLACE FUNCTION humans_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + BEGIN + -- Function for humans insert and update triggers + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + + IF TG_OP = 'UPDATE' THEN + cannot_change(`HUMANS', `EID') + END IF; + + 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; + + BEGIN + -- The EVENTS.Behavior must be sdb_other_species + + SELECT events.behavior, events.start, events.stop + , follows.fid, follows.focal, follows.date + INTO a_behavior , a_start , a_stop + , a_fid , a_focal , a_date + FROM events + JOIN follows ON (follows.fid = events.fid) + WHERE events.eid = NEW.eid + AND events.behavior <> 'sdb_other_species'; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of HUMANS' + , DETAIL = 'Humans can only be related to an event with an' + || ' EVENTS.Behavior value of (sdb_other_species)' + || ': Key (EID = (' + || NEW.eid + || '): Value (Researchers) = (' + || NEW.researchers + || '): Value (NonResearchers) = (' + || NEW.nonresearchers + || '): 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; + + -- There cannot be a related SPECIES_PRESENT row + + DECLARE + a_species species_present.species%TYPE; + + BEGIN + SELECT events.behavior, events.start, events.stop + , follows.fid, follows.focal, follows.date + , species_present.species + INTO a_behavior , a_start , a_stop + , a_fid , a_focal , a_date + , a_species + FROM events + JOIN follows ON (follows.fid = events.fid) + , species_present + WHERE events.eid = NEW.eid + AND species_present.eid = NEW.eid; + + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of HUMANS' + , DETAIL = 'Humans cannot be related to an event that is' + || ' also related to another species presence,' + || ' but there is a related SPECIES_PRESENT row' + || ': Key (EID = (' + || NEW.eid + || '): Value (Researchers) = (' + || NEW.researchers + || '): Value (NonResearchers) = (' + || NEW.nonresearchers + || '): 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 + || '): Key (SPECIES_PRESENT.EID) = (' + || NEW.eid + || '), Value (SPECIES_PRESENT.Species) = (' + || a_species + || ')'; + END IF; + END; + END; + END IF; + + RETURN NULL; + END; +$$; + + +RAISE INFO 'humans_trigger'; +CREATE TRIGGER humans_trigger + AFTER INSERT OR UPDATE + ON humans FOR EACH ROW + EXECUTE PROCEDURE humans_func(); diff --git a/db/schemas/lib/triggers/drop/humans.m4 b/db/schemas/lib/triggers/drop/humans.m4 new file mode 100644 index 0000000..e4f226b --- /dev/null +++ b/db/schemas/lib/triggers/drop/humans.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 humans table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl + +DROP FUNCTION IF EXISTS humans_func() CASCADE; -- 2.34.1