From f4896fb6b12bf3ecfe6530b31970c00d8a3df98c Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Fri, 5 Jun 2026 23:38:37 +0000 Subject: [PATCH] Index and trigger SPECIES_PRESENT --- db/schemas/lib/triggers/Makefile | 3 +- .../lib/triggers/create/species_present.m4 | 158 ++++++++++++++++++ .../lib/triggers/drop/species_present.m4 | 23 +++ db/schemas/sokwedb/indexes/Makefile | 2 +- .../sokwedb/indexes/create/species_present.m4 | 25 +++ .../sokwedb/indexes/drop/species_present.m4 | 24 +++ 6 files changed, 233 insertions(+), 2 deletions(-) create mode 100644 db/schemas/lib/triggers/create/species_present.m4 create mode 100644 db/schemas/lib/triggers/drop/species_present.m4 create mode 100644 db/schemas/sokwedb/indexes/create/species_present.m4 create mode 100644 db/schemas/sokwedb/indexes/drop/species_present.m4 diff --git a/db/schemas/lib/triggers/Makefile b/db/schemas/lib/triggers/Makefile index bd84238..f6b77aa 100644 --- a/db/schemas/lib/triggers/Makefile +++ b/db/schemas/lib/triggers/Makefile @@ -41,7 +41,8 @@ ORDER := comm_ids \ attendance \ arrivals_a \ swelling_sources \ - humans + humans \ + species_present DROP_EXISTING := true diff --git a/db/schemas/lib/triggers/create/species_present.m4 b/db/schemas/lib/triggers/create/species_present.m4 new file mode 100644 index 0000000..308ed37 --- /dev/null +++ b/db/schemas/lib/triggers/create/species_present.m4 @@ -0,0 +1,158 @@ +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 species_present table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`macros.m4')dnl + + +RAISE INFO 'species_present_func'; +CREATE OR REPLACE FUNCTION species_present_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + BEGIN + -- Function for species_present insert and update triggers + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + + IF TG_OP = 'UPDATE' THEN + cannot_change(`SPECIES_PRESENT', `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 SPECIES_PRESENT' + , DETAIL = 'Species_Present 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 HUMANS row + + DECLARE + a_researchers humans.researchers%TYPE; + a_nonresearchers humans.nonresearchers%TYPE; + + BEGIN + SELECT events.behavior, events.start, events.stop + , follows.fid, follows.focal, follows.date + , humans.researchers, humans.nonresearchers + INTO a_behavior , a_start , a_stop + , a_fid , a_focal , a_date + , a_researchers , a_nonresearchers + FROM events + JOIN follows ON (follows.fid = events.fid) + , humans + WHERE events.eid = NEW.eid + AND humans.eid = NEW.eid; + + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of SPECIES_PRESENT' + , DETAIL = 'Species_Present cannot be related to an event that' + || ' is also related to a human presence,' + || ' but there is a related HUMANS 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 (HUMANS.EID) = (' + || NEW.eid + || '), Value (HUMANS.Researchers) = (' + || a_researchers + || '), Value (HUMANS.NonResearchers) = (' + || a_nonresearchers + || ')'; + END IF; + END; + END; + END IF; + + RETURN NULL; + END; +$$; + + +RAISE INFO 'species_present_trigger'; +CREATE TRIGGER species_present_trigger + AFTER INSERT OR UPDATE + ON species_present FOR EACH ROW + EXECUTE PROCEDURE species_present_func(); diff --git a/db/schemas/lib/triggers/drop/species_present.m4 b/db/schemas/lib/triggers/drop/species_present.m4 new file mode 100644 index 0000000..40fb302 --- /dev/null +++ b/db/schemas/lib/triggers/drop/species_present.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 species_present table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl + +DROP FUNCTION IF EXISTS species_present_func() CASCADE; diff --git a/db/schemas/sokwedb/indexes/Makefile b/db/schemas/sokwedb/indexes/Makefile index cc52eaf..5e56aa5 100644 --- a/db/schemas/sokwedb/indexes/Makefile +++ b/db/schemas/sokwedb/indexes/Makefile @@ -23,7 +23,7 @@ ORDER := biography_data biography_log comm_membs comm_memb_log \ follows follow_observers follow_studies events roles arrivals \ swelling_sources swelling_states aggression_event_log sightings \ aggressions food_events groomings attendance \ - arrivals_a + arrivals_a species_present ## ## CAUTION: This Makefile is not designed to be run directly. It is normally diff --git a/db/schemas/sokwedb/indexes/create/species_present.m4 b/db/schemas/sokwedb/indexes/create/species_present.m4 new file mode 100644 index 0000000..87e12fb --- /dev/null +++ b/db/schemas/sokwedb/indexes/create/species_present.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 + +CREATE INDEX IF NOT EXISTS species_present_species ON species_present + (species); diff --git a/db/schemas/sokwedb/indexes/drop/species_present.m4 b/db/schemas/sokwedb/indexes/drop/species_present.m4 new file mode 100644 index 0000000..235cfc0 --- /dev/null +++ b/db/schemas/sokwedb/indexes/drop/species_present.m4 @@ -0,0 +1,24 @@ +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 species_present_species; -- 2.34.1