From 547d26c055bb182608cea5f2419a9d63fb13a50b Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Sat, 23 May 2026 23:36:03 +0000 Subject: [PATCH] Create, document, index, and trigger GROOMINGS --- db/schemas/lib/triggers/Makefile | 3 +- db/schemas/lib/triggers/create/follows.m4 | 46 ++++ db/schemas/lib/triggers/create/groomings.m4 | 234 ++++++++++++++++++ db/schemas/lib/triggers/create/roles.m4 | 191 +++++++++++++- db/schemas/lib/triggers/drop/groomings.m4 | 24 ++ db/schemas/lib/triggers/drop/roles.m4 | 2 + db/schemas/sokwedb/indexes/Makefile | 2 +- .../sokwedb/indexes/create/groomings.m4 | 38 +++ db/schemas/sokwedb/indexes/drop/groomings.m4 | 30 +++ db/schemas/sokwedb/tables/Makefile | 3 +- db/schemas/sokwedb/tables/create/groomings.m4 | 40 +++ doc/src/epilog.inc.m4 | 14 ++ doc/src/tables.m4 | 1 + doc/src/tables/events.m4 | 44 +++- doc/src/tables/groomings.m4 | 157 ++++++++++++ 15 files changed, 817 insertions(+), 12 deletions(-) create mode 100644 db/schemas/lib/triggers/create/groomings.m4 create mode 100644 db/schemas/lib/triggers/drop/groomings.m4 create mode 100644 db/schemas/sokwedb/indexes/create/groomings.m4 create mode 100644 db/schemas/sokwedb/indexes/drop/groomings.m4 create mode 100644 db/schemas/sokwedb/tables/create/groomings.m4 create mode 100644 doc/src/tables/groomings.m4 diff --git a/db/schemas/lib/triggers/Makefile b/db/schemas/lib/triggers/Makefile index fe1433f..c4b4290 100644 --- a/db/schemas/lib/triggers/Makefile +++ b/db/schemas/lib/triggers/Makefile @@ -35,7 +35,8 @@ ORDER := comm_ids \ arrivals \ aggressions \ sightings \ - food_events + food_events \ + groomings DROP_EXISTING := true diff --git a/db/schemas/lib/triggers/create/follows.m4 b/db/schemas/lib/triggers/create/follows.m4 index 5e59cf4..6e25986 100644 --- a/db/schemas/lib/triggers/create/follows.m4 +++ b/db/schemas/lib/triggers/create/follows.m4 @@ -606,6 +606,52 @@ CREATE OR REPLACE FUNCTION follows_commit_func () || ')'; END IF; + IF TG_OP = 'UPDATE' + AND NEW.focal <> OLD.focal THEN + -- One of the individuals grooming must be the focal. + + DECLARE + -- EVENTS + a_eid events.eid%TYPE; + a_behavior events.behavior%TYPE; + a_start events.start%TYPE; + a_stop events.stop%TYPE; + + BEGIN + SELECT events.eid, events.behavior, events.start, events.stop + INTO a_eid , a_behavior , a_start , a_stop + FROM events + WHERE events.fid = NEW.fid + AND events.behavior = 'sdb_grooming' + AND NOT EXISTS + (SELECT 1 + FROM roles + WHERE roles.eid = events.eid + AND roles.participant = NEW.focal); + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of FOLLOWS' + , DETAIL = 'One of the individuals involved in the grooming event' + || ' must the the focal' + || ': Key (FID) = (' + || NEW.fid + || '), Value (Focal) = (' + || NEW.focal + || '), Value (Date) = (' + || NEW.date + || '): Key (EVENTS.EID) = (' + || a_eid + || '): Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || ')'; + END IF; + END; + END IF; + RETURN NULL; END; $$; diff --git a/db/schemas/lib/triggers/create/groomings.m4 b/db/schemas/lib/triggers/create/groomings.m4 new file mode 100644 index 0000000..159f8e4 --- /dev/null +++ b/db/schemas/lib/triggers/create/groomings.m4 @@ -0,0 +1,234 @@ +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 groomings table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`macros.m4')dnl + + +RAISE INFO 'groomings_func'; +CREATE OR REPLACE FUNCTION groomings_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + BEGIN + -- Function for groomings insert and update triggers + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + + IF TG_OP = 'UPDATE' THEN + cannot_change(`GROOMINGS', `GrID') + END IF; + + -- The EVENTS.Behavior must be sdb_grooming + IF TG_OP = 'INSERT' + OR NEW.eid <> OLD.eid 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_grooming'; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of GROOMINGS' + , DETAIL = 'Groomings can only be related to an event with an' + || ' EVENTS.Behavior value of (sdb_grooming)' + || ': Key (GrID = (' + || NEW.grid + || '): Value (Initiator) = (' + || textualize(`NEW.initiator') + || '): Value (Terminator) = (' + || textualize(`NEW.terminator') + || '): Value (Problems) = (' + || NEW.problems + || '): Value (ExtractedBy) = (' + || NEW.extractedby + || '): 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; +$$; + + +dnl Macro for checking Initiator and Terminator are actors or actees +dnl in the grooming event. +dnl (Because they are so similar.) +dnl +dnl Synatx: _sdb_grm_actor_or_actee(Col) +dnl +dnl Input: +dnl Col The name of the column, (Initiator, Terminator) +dnl +changequote({,}) +define({_sdb_grm_actor_or_actee}, +{ IF NEW.$1 IS NOT NULL + AND (TG_OP = 'INSERT' + OR NEW.$1 IS DISTINCT FROM OLD.$1) THEN + -- The $1 must be one of the participants in the grooming + + SELECT events.start, events.stop, events.behavior + , follows.fid, follows.focal, follows.date + INTO a_start , a_stop , a_behavior + , a_fid , a_focal , a_date + FROM events + JOIN follows ON (follows.fid = events.fid) + WHERE events.eid = NEW.eid + AND NOT EXISTS + (SELECT 1 + FROM roles + WHERE roles.eid = NEW.eid + AND roles.pid = NEW.$1 + AND (roles.role = 'sdb_actor' + OR roles.role = 'sdb_actee')); + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of GROOMINGS' + , DETAIL = 'Bad $1 value (' + || textualize(NEW.$1) + || '); $1 must be one of the participaints' + || ' in the grooming event and must either be the' + || ' one doing the grooming or the one receiving the' + || ' grooming' + || ': Key (GrID = (' + || NEW.grid + || '): Value (Initiator) = (' + || textualize(`NEW.initiator') + || '): Value (Terminator) = (' + || textualize(`NEW.terminator') + || '): Value (Problems) = (' + || NEW.problems + || '): Value (ExtractedBy) = (' + || NEW.extractedby + || '): 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 IF; +})dnl +changequote(`,')dnl + + +RAISE INFO 'groomings_commit_func'; +CREATE OR REPLACE FUNCTION groomings_commit_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + BEGIN + -- Function for groomings insert and update triggers + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + + IF TG_OP = 'INSERT' + OR NEW.eid <> OLD.eid THEN + DECLARE + -- EVENTS + a_start events.start%TYPE; + a_stop events.stop%TYPE; + a_behavior events.behavior%TYPE; + -- FOLLOWS + a_fid follows.fid%TYPE; + a_focal follows.focal%TYPE; + a_date follows.date%TYPE; + + BEGIN + _sdb_grm_actor_or_actee(`Initiator') + _sdb_grm_actor_or_actee(`Terminator') + END; + END IF; + + RETURN NULL; + END; +$$; + + + +RAISE INFO 'groomings_trigger'; +CREATE TRIGGER groomings_trigger + AFTER INSERT OR UPDATE + ON groomings FOR EACH ROW + EXECUTE PROCEDURE groomings_func(); + +RAISE INFO 'groomings_commit_trigger'; +CREATE CONSTRAINT TRIGGER groomings_commit_trigger + AFTER INSERT OR UPDATE + ON groomings + DEFERRABLE INITIALLY DEFERRED + FOR EACH ROW + EXECUTE PROCEDURE groomings_commit_func(); diff --git a/db/schemas/lib/triggers/create/roles.m4 b/db/schemas/lib/triggers/create/roles.m4 index ed7112b..b0a6c04 100644 --- a/db/schemas/lib/triggers/create/roles.m4 +++ b/db/schemas/lib/triggers/create/roles.m4 @@ -227,10 +227,11 @@ CREATE OR REPLACE FUNCTION roles_func () END IF; -- - -- Validate aggression events + -- Validate aggression and grooming events -- IF TG_OP = 'INSERT' - AND a_behavior = 'sdb_aggression' THEN + AND (a_behavior = 'sdb_aggression' + OR a_behavior = 'sdb_grooming') THEN DECLARE a_pid roles.pid%TYPE; a_role roles.role%TYPE; @@ -241,12 +242,12 @@ CREATE OR REPLACE FUNCTION roles_func () BEGIN -- - -- Validate ROLES.Role for aggression events + -- Validate ROLES.Role for aggression and grooming events -- IF NEW.role <> 'sdb_actor' AND NEW.role <> 'sdb_actee' THEN - -- The ROLES rows for aggression events must have a role of sdb_actor - -- or sdb_actee. + -- The ROLES rows for aggression and grooming events must have + -- a role of sdb_actor or sdb_actee. SELECT follows.focal, follows.date, follows.community INTO a_focal , a_date , a_community FROM follows @@ -286,8 +287,8 @@ CREATE OR REPLACE FUNCTION roles_func () END IF; END IF; - -- There can be only one row with each aggression role per - -- aggression event. + -- There can be only one row with each aggression/grooming role per + -- aggression/grooming event. SELECT roles.pid, roles.role, roles.participant , follows.focal, follows.date, follows.community INTO a_pid , a_role , a_participant @@ -394,7 +395,8 @@ CREATE OR REPLACE FUNCTION roles_func () -- each must be distinct. -- IF TG_OP = 'INSERT' - AND a_behavior = 'sdb_aggression' THEN + AND (a_behavior = 'sdb_aggression' + OR a_behavior = 'sdb_grooming') THEN DECLARE a_pid roles.pid%TYPE; a_role roles.role%TYPE; @@ -546,6 +548,52 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () END IF; END IF; + -- One of the individuals involved in a grooming event must be + -- the focal + + SELECT events.behavior ,events.start, events.stop + , follows.fid, follows.focal, follows.date, follows.community + INTO a_behavior , a_start , a_stop + , a_fid , a_focal , a_date , a_community + FROM events + JOIN follows ON (follows.fid = events.fid) + WHERE events.eid = NEW.eid + AND events.behavior = 'sdb_grooming' + AND NOT EXISTS + (SELECT 1 + FROM roles + WHERE roles.eid = NEW.eid + AND roles.participant = follows.focal); + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of ROLES' + , DETAIL = 'One of the individuals involved in each grooming' + || ' event must be the focal' + || ': 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.Community) = (' + || a_community + || ')'; + END IF; + RETURN NULL; END; $$; @@ -569,6 +617,13 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () a_date follows.date%TYPE; a_community follows.community%TYPE; + -- GROOMINGS + a_other groomings.other%TYPE; + a_initiator groomings.initiator%TYPE; + a_terminator groomings.terminator%TYPE; + a_problems groomings.problems%TYPE; + a_extractedby groomings.extractedby%TYPE; + BEGIN -- Function for roles after commit delete trigger -- @@ -651,6 +706,126 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () END; END IF; + -- One of the individuals involved in a grooming event must be + -- the focal + + SELECT events.behavior ,events.start, events.stop + , follows.fid, follows.focal, follows.date, follows.community + INTO a_behavior , a_start , a_stop + , a_fid , a_focal , a_date , a_community + FROM events + JOIN follows ON (follows.fid = events.fid) + WHERE events.eid = OLD.eid + AND events.behavior = 'sdb_grooming' + AND follows.focal = OLD.participant + AND NOT EXISTS + (SELECT 1 + FROM roles + WHERE roles.eid = OLD.eid + AND roles.participant = follows.focal); + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on DELETE of ROLES' + , DETAIL = 'One of the individuals involved in each grooming' + || ' event must be the focal' + || ': The deleted ROLES row is Key (PID) = (' + || OLD.pid + || '), Value (EID) = (' + || OLD.eid + || '), Value (Role) = (' + || OLD.role + || '), Value (Participant) = (' + || OLD.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.Community) = (' + || a_community + || ')'; + END IF; + + -- The Initiator or Terminator in a grooming event must be + -- a participant and an sdb_actor or an sdb_actee. + + SELECT events.behavior ,events.start, events.stop + , follows.fid, follows.focal, follows.date, follows.community + , groomings.initiator, groomings.terminator + , groomings.problems, groomings.extractedby + INTO a_behavior , a_start , a_stop + , a_fid , a_focal , a_date , a_community + , a_initiator , a_terminator + , a_problems , a_extractedby + FROM events + JOIN follows ON (follows.fid = events.fid) + , groomings + WHERE events.eid = OLD.eid + AND events.behavior = 'sdb_grooming' + AND groomings.eid = OLD.eid + AND ((groomings.initiator IS NOT NULL + AND NOT EXISTS + (SELECT 1 + FROM roles + WHERE roles.pid = groomings.initiator + AND roles.eid = OLD.eid + AND (roles.role = 'sdb_actor' + OR roles.role = 'sdb_actee'))) + OR (groomings.terminator IS NOT NULL + AND NOT EXISTS + (SELECT 1 + FROM roles + WHERE roles.pid = groomings.terminator + AND roles.eid = OLD.eid + AND (roles.role = 'sdb_actor' + OR roles.role = 'sdb_actee')))); + + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on DELETE of ROLES' + , DETAIL = 'The initiator and terminator of a grooming event,' + || ' and the other grooming partner, must all' + || ' be one of the participants in the grooming event' + || ': The deleted ROLES row is Key (PID) = (' + || OLD.pid + || '), Value (EID) = (' + || OLD.eid + || '), Value (Role) = (' + || OLD.role + || '), Value (Participant) = (' + || OLD.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.Community) = (' + || a_community + || '), Value (GROOMINGS.Initiator) = (' + || textualize(`a_initiator') + || '), Value (GROOMINGS.Terminator) = (' + || textualize(`a_terminator') + || '), Value (GROOMINGS.Problems) = (' + || a_problems + || '), Value (GROOMINGS.ExtractedBy) = (' + || a_extractedby + || ')'; + END IF; + RETURN NULL; END; $$; diff --git a/db/schemas/lib/triggers/drop/groomings.m4 b/db/schemas/lib/triggers/drop/groomings.m4 new file mode 100644 index 0000000..488109b --- /dev/null +++ b/db/schemas/lib/triggers/drop/groomings.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 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 groomings_func() CASCADE; +DROP FUNCTION IF EXISTS groomings_commit_func() CASCADE; diff --git a/db/schemas/lib/triggers/drop/roles.m4 b/db/schemas/lib/triggers/drop/roles.m4 index b6fa2c5..3eec52b 100644 --- a/db/schemas/lib/triggers/drop/roles.m4 +++ b/db/schemas/lib/triggers/drop/roles.m4 @@ -21,3 +21,5 @@ dnl m4 includes include(`copyright.m4')dnl DROP FUNCTION IF EXISTS roles_func() CASCADE; +DROP FUNCTION IF EXISTS roles_insert_commit_func() CASCADE; +DROP FUNCTION IF EXISTS roles_delete_commit_func() CASCADE; diff --git a/db/schemas/sokwedb/indexes/Makefile b/db/schemas/sokwedb/indexes/Makefile index 50a3a4d..e2727e3 100644 --- a/db/schemas/sokwedb/indexes/Makefile +++ b/db/schemas/sokwedb/indexes/Makefile @@ -22,7 +22,7 @@ ORDER := biography_data biography_log comm_membs comm_memb_log \ follows follow_observers follow_studies events roles arrivals \ estrus_sources estrus_states aggression_event_log sightings \ - aggressions food_events + aggressions food_events groomings ## ## CAUTION: This Makefile is not designed to be run directly. It is normally diff --git a/db/schemas/sokwedb/indexes/create/groomings.m4 b/db/schemas/sokwedb/indexes/create/groomings.m4 new file mode 100644 index 0000000..adeb654 --- /dev/null +++ b/db/schemas/sokwedb/indexes/create/groomings.m4 @@ -0,0 +1,38 @@ +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 UNIQUE INDEX IF NOT EXISTS + "GROOMINGS has, at most, a 1-to-1 releationship with EVENTS" + ON groomings + (eid); + +CREATE INDEX IF NOT EXISTS groomings_other ON groomings + (other); +CREATE INDEX IF NOT EXISTS groomings_initiator ON groomings + (initiator); +CREATE INDEX IF NOT EXISTS groomings_terminator ON groomings + (terminator); +CREATE INDEX IF NOT EXISTS groomings_initiator ON groomings + (initiator); +-- We won't index ExtractedBy because this is not expected to be +-- frequently searched. diff --git a/db/schemas/sokwedb/indexes/drop/groomings.m4 b/db/schemas/sokwedb/indexes/drop/groomings.m4 new file mode 100644 index 0000000..1489828 --- /dev/null +++ b/db/schemas/sokwedb/indexes/drop/groomings.m4 @@ -0,0 +1,30 @@ +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 + "GROOMINGS has, at most, a 1-to-1 releationship with EVENTS"; + +DROP INDEX IF EXISTS groomings_other; +DROP INDEX IF EXISTS groomings_initiator; +DROP INDEX IF EXISTS groomings_terminator; +DROP INDEX IF EXISTS groomings_initiator; diff --git a/db/schemas/sokwedb/tables/Makefile b/db/schemas/sokwedb/tables/Makefile index 63e089c..b4899f3 100644 --- a/db/schemas/sokwedb/tables/Makefile +++ b/db/schemas/sokwedb/tables/Makefile @@ -38,7 +38,8 @@ ORDER := biography_data \ aggressions \ aggression_event_log \ non_brec_sighting_sources \ - food_events + food_events \ + groomings ## ## 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/groomings.m4 b/db/schemas/sokwedb/tables/create/groomings.m4 new file mode 100644 index 0000000..c5e4951 --- /dev/null +++ b/db/schemas/sokwedb/tables/create/groomings.m4 @@ -0,0 +1,40 @@ +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 groomings ( + key_column(`GROOMINGS', `GrID', INTEGER) + ,eid INTEGER NOT NULL + REFERENCES events + ,initiator INTEGER + REFERENCES roles (pid) + ,terminator INTEGER + REFERENCES roles (pid) + ,problems TEXT NOT NULL + notonlyspaces_check(`Problems') + ,extractedby TEXT NOT NULL + REFERENCES people +); + +grant_priv(`GROOMINGS') diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index 3c5c8c1..877d9bb 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -394,6 +394,20 @@ sdb_generated_rst()dnl .. |FOLLOW_STUDIES.Code| replace:: :ref:`Code ` +.. |GROOMINGS| replace:: :ref:`GROOMINGS ` +.. |GROOMINGS.GrID| replace:: + :ref:`GrID ` +.. |GROOMINGS.EID| replace:: + :ref:`EID ` +.. |GROOMINGS.Initiator| replace:: + :ref:`Initiator ` +.. |GROOMINGS.Terminator| replace:: + :ref:`Terminator ` +.. |GROOMINGS.Problems| replace:: + :ref:`Problems ` +.. |GROOMINGS.ExtractedBy| replace:: + :ref:`ExtractedBy ` + .. |HUMANS| replace:: :ref:`HUMANS ` .. |HUMANS.HID| replace:: :ref:`HID ` diff --git a/doc/src/tables.m4 b/doc/src/tables.m4 index 0199956..976ca63 100644 --- a/doc/src/tables.m4 +++ b/doc/src/tables.m4 @@ -45,6 +45,7 @@ and are therefore the result of at least a rudimentary analytical process. tables/follow_observers.rst tables/follow_studies.rst tables/food_events.rst + tables/groomings.rst tables/humans.rst tables/roles.rst tables/non_brec_sighting_sources.rst diff --git a/doc/src/tables/events.m4 b/doc/src/tables/events.m4 index fded63b..e31a368 100644 --- a/doc/src/tables/events.m4 +++ b/doc/src/tables/events.m4 @@ -89,6 +89,7 @@ The following table lists these rules and implications: say, the certainty of the individual with the role of ``sdb_actee``. + .. _EVENTS_arrival_code: ``sdb_arrival`` (Arrival) @@ -112,6 +113,7 @@ The following table lists these rules and implications: The |EVENTS|.\ |EVENTS.Stop| column contains the departure time of the the individual identified in the related |ROLES| row. + .. _EVENTS_food_code: ``sdb_food`` (Food) @@ -142,10 +144,50 @@ The following table lists these rules and implications: The |EVENTS|.\ |EVENTS.Stop| column contains the time the food bout finished. + .. _EVENTS_grooming_code: ``sdb_grooming`` (Grooming) - A row must exist on GROOMINGS. + A related row should exist on |GROOMINGS|; there should be a row on + |GROOMINGS| with an |GROOMINGS.EID| value of the event's + |EVENTS.EID|. + There may be at most one of these related |GROOMINGS| rows. + The system will generate a warning when there is no |GROOMINGS| + row related to the grooming event. + + The |ROLES| rows related to the event, the rows with a |ROLES.EID| + value equal to the |EVENTS.EID| value, designates the + individuals involved in the grooming event. + The |ROLES|.\ |ROLES.Role| code of each individual that the |ROLES| + table relates to the grooming event describes whether that + individual groomed or was groomed during the grooming event. + + There should be exactly two |ROLES| row related to the grooming event. + The only two |ROLES|.\ |ROLES.Role| codes allowed are ``sdb_actor`` and + ``sdb_actee``. + One of those two roles must be ``sdb_actor`` and the other must be + ``sdb_actee``. + The system will generate a warning when there are not exactly two + |ROLES| rows related to an grooming event. + + The two participants in a grooming event must be different + individuals. + This means that their |ROLES|.\ |ROLES.Participant| values must + differ. + + One of the two participants in a grooming event must be the focal + individual. + This means that on of the the |ROLES| rows related to the event + must have a |ROLES|.\ |ROLES.Participant| value equal to the + |FOLLOWS|.\ |FOLLOWS.Focal| of the follow that is related to the + grooming event. + + For grooming events, the |EVENTS|.\ |EVENTS.Certainty| column + records the certainty of the duration of the grooming event. + When this column is |true|, either the time of the start of the + grooming event or the time of the end of the grooming event, or + both, are uncertain. + .. _EVENTS_mating_code: diff --git a/doc/src/tables/groomings.m4 b/doc/src/tables/groomings.m4 new file mode 100644 index 0000000..120f890 --- /dev/null +++ b/doc/src/tables/groomings.m4 @@ -0,0 +1,157 @@ +.. 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 + +.. _GROOMINGS: + +GROOMINGS +----------- + +.. |GROOMINGS_summary| replace:: + Each row, taken together with the related |EVENTS| row, represents + a grooming event recording during a follow. + Groomings are recorded as dyadic pairs. + +|GROOMINGS_summary| + +Should there be multiple individuals, whether groomer or recipient, +involved in simultaneous grooming behavior, there should be multiple +rows in the GROOMINGS table. +There should be one row for each dyad. + + +.. caution:: + + The system's design cannot distinguish between multiple multi-party + grooming events that are recorded as part of a single follow and + occur at a given time, and a single, larger, multi-party grooming + event recorded in the given follow at the given time.\ [#f1]_ + +The related |EVENTS| row must be a grooming event; it must have an +|EVENTS|.\ |EVENTS.Behavior| value of ``sdb_grooming``. +This related |EVENTS| row supplies the time of the grooming and +relates to the follow, and the |ROLES| role related to the event +supplies information on the individuals involved. + +The initiator and the terminator of the grooming event, when either of +these are known, must each be one of the individuals who participated +in the grooming event. +Further, the initiator and the terminator of the grooming event, when +known, must each be either the groomer or the individual being +groomed. +This means the |GROOMINGS.Initiator| and |GROOMINGS.Terminator| values +must be a |ROLES|.\ |ROLES.PID| value of a |ROLES| row that has a +|ROLES|.\ |ROLES.EID| equal to the |GROOMINGS.EID| of the grooming +event and the |ROLES|.\ |ROLES.Role| value must be either +``sdb_actor`` or ``sdb_actee``. +|transaction commit| + +For further information, including additional data integrity rules, +see the documentation of the :ref:`EVENTS ` +table. + + +.. contents:: + :depth: 2 + + +.. _GROOMINGS.GrID: + +GrID (Grooming ID) +`````````````````` + +.. |GROOMINGS.GrID_summary| replace:: |idcol| + +|GROOMINGS.GrID_summary| |notnull| + + +.. _GROOMINGS.EID: + +EID (Event ID) +`````````````` + +.. |GROOMINGS.EID_summary| replace:: + The |EVENTS|.\ |EVENTS.EID| identifying the grooming event. + +|GROOMINGS.EID_summary| +The related event contains information on the time of the grooming +and is related to the participants in the grooming event. + +|notnull| + + +.. _GROOMINGS.Initiator: + +Initiator +````````` + +.. |GROOMINGS.Initiator_summary| replace:: + + The participant that initiated the grooming. + A |ROLES|.\ |ROLES.Role| value. + +|GROOMINGS.Initiator_summary| +This column may be |null| when there is no record of who initiated the +grooming. + + +.. _GROOMINGS.Terminator: + +Terminator +`````````` + +.. |GROOMINGS.Terminator_summary| replace:: + + The participant that initiated the grooming. + A |ROLES|.\ |ROLES.Role| value. + +|GROOMINGS.Terminator_summary| +This column may be |null| when there is no record of who terminated the +grooming. + + +.. _GROOMINGS.Problems: + +Problems +```````` + +.. |GROOMINGS.Problems_summary| replace:: + Text describing problems in the grooming observation. + +|GROOMINGS.Problems_summary| |notonlyspaces| |notnull| + + +.. _GROOMINGS.Extractedby: + +Extractedby +``````````` + +.. |GROOMINGS.Extractedby_summary| replace:: + The person who extracted the grooming information from the + written records and prepared it for data entry into the database. + +|GROOMINGS.Extractedby_summary| |notnull| + + +.. ::rubric:: Footnotes + +.. [#f1] + More information related to this problem can be found in the + documentation of the |AGGRESSIONS| table. -- 2.34.1