From 91a3ad51f56c4e83fcd18562f5383fb90a928ee1 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Sat, 20 Jun 2026 02:09:17 +0000 Subject: [PATCH] Create, document, index, and trigger MATINGS --- db/schemas/lib/triggers/Makefile | 3 +- .../lib/triggers/create/biography_data.m4 | 55 ++++ db/schemas/lib/triggers/create/matings.m4 | 132 ++++++++++ db/schemas/lib/triggers/create/roles.m4 | 74 +++++- db/schemas/sokwedb/indexes/Makefile | 2 +- db/schemas/sokwedb/indexes/create/matings.m4 | 27 ++ db/schemas/sokwedb/indexes/create/watches.m4 | 7 + db/schemas/sokwedb/indexes/drop/matings.m4 | 25 ++ db/schemas/sokwedb/indexes/drop/watches.m4 | 3 + db/schemas/sokwedb/tables/Makefile | 3 +- db/schemas/sokwedb/tables/create/events.m4 | 2 + db/schemas/sokwedb/tables/create/matings.m4 | 49 ++++ db/schemas/sokwedb/tables/create/watches.m4 | 1 + doc/src/epilog.inc.m4 | 27 ++ doc/src/tables.m4 | 1 + doc/src/tables/events.m4 | 41 ++- doc/src/tables/matings.m4 | 239 ++++++++++++++++++ doc/src/tables/watches.m4 | 34 ++- include/global_constants.m4 | 1 + 19 files changed, 715 insertions(+), 11 deletions(-) create mode 100644 db/schemas/lib/triggers/create/matings.m4 create mode 100644 db/schemas/sokwedb/indexes/create/matings.m4 create mode 100644 db/schemas/sokwedb/indexes/drop/matings.m4 create mode 100644 db/schemas/sokwedb/tables/create/matings.m4 create mode 100644 doc/src/tables/matings.m4 diff --git a/db/schemas/lib/triggers/Makefile b/db/schemas/lib/triggers/Makefile index f617f3a..e66e5ab 100644 --- a/db/schemas/lib/triggers/Makefile +++ b/db/schemas/lib/triggers/Makefile @@ -47,7 +47,8 @@ ORDER := comm_ids \ pantgrunts \ pantgrunts_view \ brecord_notes \ - colobus + colobus \ + matings DROP_EXISTING := true diff --git a/db/schemas/lib/triggers/create/biography_data.m4 b/db/schemas/lib/triggers/create/biography_data.m4 index b211bef..138037e 100644 --- a/db/schemas/lib/triggers/create/biography_data.m4 +++ b/db/schemas/lib/triggers/create/biography_data.m4 @@ -830,6 +830,61 @@ CREATE OR REPLACE FUNCTION biography_data_func () || ')'; END IF; END IF; + + IF NEW.sex <> OLD.sex THEN + -- In mating events, the sdb_actor must be the male and the + -- sdb_actee the female. + SELECT roles.pid, roles.role + , events.eid, events.behavior, events.start + , watches.wid, watches.focal, watches.date, watches.type + INTO a_pid , a_role + , a_eid , a_behavior , a_start + , a_wid , a_focal , a_date , a_type + FROM roles + JOIN events ON (events.eid = roles.eid) + JOIN watches ON (watches.wid = events.wid) + WHERE roles.participant = NEW.animid + AND events.behavior = 'sdb_mating_event' + AND roles.participant = NEW.animid + AND ((roles.role = 'sdb_actor' + AND NEW.sex <> 'sdb_male') + OR (roles.role = 'sdb_actee' + AND NEW.sex <> 'sdb_female')) + -- Produce a consistent error message + ORDER BY watches.date, watches.wid + , roles.role, roles.pid + , events.start, events.eid; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'Invalid Sex; in mating events, the ROLES.Role' + || ' of (sdb_actor) must be male and the' + || ' ROLES.Role of (sdb_actee) must be female' + || ': Key (Animid) = (' + || NEW.animid + || '): Value (Sex) = (' + || NEW.Sex + || '): Key (ROLES.PID) = (' + || a_pid + || '), Value (ROLES.Role) = (' + || a_role + || '): Key (EVENTS.EID) = (' + || a_eid + || '): Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '): Key (WATCHES.WID) = (' + || a_wid + || '), Value (WATCHES.Focal) = (' + || a_focal + || '), Value (WATCHES.Date) = (' + || a_date + || '), Value (WATCHES.Type) = (' + || a_type + || ')'; + END IF; + END IF; END; -- ARRIVALS diff --git a/db/schemas/lib/triggers/create/matings.m4 b/db/schemas/lib/triggers/create/matings.m4 new file mode 100644 index 0000000..608609e --- /dev/null +++ b/db/schemas/lib/triggers/create/matings.m4 @@ -0,0 +1,132 @@ +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 matings table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`macros.m4')dnl + + +RAISE INFO 'matings_func'; +CREATE OR REPLACE FUNCTION matings_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + BEGIN + -- Function for matings insert and update triggers + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + + IF TG_OP = 'UPDATE' THEN + cannot_change(`MATINGS', `EID') + END IF; + + -- The EVENTS.Behavior must be sdb_mating_event + 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; + -- ROLES + a_pid roles.pid%TYPE; + a_role roles.role%TYPE; + a_participant roles.participant%TYPE; + + BEGIN + SELECT events.behavior, events.start, events.stop + , watches.wid, watches.focal, watches.date + , roles.pid, roles.role, roles.participant + INTO a_behavior , a_start , a_stop + , a_wid , a_focal , a_date + , a_pid , a_role , a_participant + FROM events + JOIN watches ON (watches.wid = events.wid) + LEFT OUTER JOIN roles ON (roles.eid = events.eid) + WHERE events.eid = NEW.eid + AND events.behavior <> 'sdb_mating_event' + ORDER BY roles.participant; -- Consistent error message + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of MATINGS' + , DETAIL = 'Matings can only be related to an event with an' + || ' EVENTS.Behavior value of (sdb_mating_event)' + || ': Key (EID = (' + || NEW.eid + || '): Value (Swelling) = (' + || NEW.swelling + || '): Value (CommID) = (' + || NEW.commid + || '): Value (EnteredBy) = (' + || NEW.Enteredby + || '): Value (Source) = (' + || NEW.source + || '): 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 (WATCHES.WID) = (' + || a_wid + || '), Value (WATCHES.Focal) = (' + || a_focal + || '), Value (WATCHES.Date) = (' + || a_date + || ')'; + END IF; + END; + END IF; + + changequote({,})dnl + person_active({MATINGS}, {ExtractedBy}, + {'Key (EID = (' + || NEW.EID + || '): Value (CommID) = (' + || NEW.commid + || '): Value (Source) = (' + || NEW.source + || ')'}) + changequote(`,')dnl + + + RETURN NULL; + END; +$$; + + +RAISE INFO 'matings_trigger'; +CREATE TRIGGER matings_trigger + AFTER INSERT OR UPDATE + ON matings FOR EACH ROW + EXECUTE PROCEDURE matings_func(); diff --git a/db/schemas/lib/triggers/create/roles.m4 b/db/schemas/lib/triggers/create/roles.m4 index 182585c..0b427a4 100644 --- a/db/schemas/lib/triggers/create/roles.m4 +++ b/db/schemas/lib/triggers/create/roles.m4 @@ -298,7 +298,8 @@ CREATE OR REPLACE FUNCTION roles_func () AND (a_behavior = 'sdb_aggression' OR a_behavior = 'sdb_grooming' OR a_behavior = 'sdb_groom_scan' - OR a_behavior = 'sdb_groom_scan_a') THEN + OR a_behavior = 'sdb_groom_scan_a' + OR a_behavior = 'sdb_mating') THEN DECLARE a_pid roles.pid%TYPE; a_role roles.role%TYPE; @@ -314,11 +315,12 @@ CREATE OR REPLACE FUNCTION roles_func () -- Validate ROLES.Role for aggression and grooming events -- IF (a_behavior = 'sdb_aggression' - OR a_behavior = 'sdb_groom_scan') + OR a_behavior = 'sdb_groom_scan' + OR a_behavior = 'sdb_mating') AND NEW.role <> 'sdb_actor' AND NEW.role <> 'sdb_actee' THEN - -- The ROLES rows for aggression and groom scan events must have - -- a role of sdb_actor or sdb_actee. + -- The ROLES rows for aggression, groom scan, and mating + -- events must have a role of sdb_actor or sdb_actee. SELECT watches.focal, watches.date, watches.commid INTO a_focal , a_date , a_commid FROM watches @@ -359,7 +361,7 @@ CREATE OR REPLACE FUNCTION roles_func () END IF; -- There can be only one row with each aggression/grooming role per - -- aggression/grooming event. + -- aggression/grooming/mating event. SELECT roles.pid, roles.role, roles.participant INTO a_pid , a_role , a_participant FROM roles @@ -528,7 +530,8 @@ CREATE OR REPLACE FUNCTION roles_func () IF TG_OP = 'INSERT' AND (a_behavior = 'sdb_aggression' OR a_behavior = 'sdb_grooming' - OR a_behavior = 'sdb_groom_scan') THEN + OR a_behavior = 'sdb_groom_scan' + OR a_behavior = 'sdb_mating_event') THEN DECLARE a_pid roles.pid%TYPE; a_role roles.role%TYPE; @@ -595,6 +598,65 @@ CREATE OR REPLACE FUNCTION roles_func () END; END IF; + -- + -- In mating events, the sdb_actor must be male and the sdb_actee + -- must be female. + -- + IF TG_OP = 'INSERT' + AND a_behavior = 'sdb_mating_event' THEN + DECLARE + target_sex biography_data.sex%TYPE; + a_sex biography_data.sex%TYPE; + + BEGIN + IF NEW.role = 'sdb_actor' THEN + target_sex := 'sdb_male'; + ELSE -- NEW.role = 'sdb_actee' + target_sex := 'sdb_female'; + END IF; + + SELECT biography_data.sex + INTO a_sex + WHERE biography_data.animid = NEW.participant + AND biography_data.sex <> target_sex; + + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on INSERT of ROLES' + , DETAIL = 'Invalid Participant value;' + || ' when the (Role) = (' + || NEW.role + || ' the sex of the participant must be (' + || target_sex + || ') but the sex is actually (' + || a_sex + || '): Inserting: 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 (WATCHES.WID) = (' + || a_wid + || '), Value (WATCHES.Focal) = (' + || a_focal + || '), Value (WATCHES.Date) = (' + || a_date + || '), Value (WATCHES.CommID) = (' + || a_commid + || ')'; + END IF; + END; + END IF; + RETURN NULL; END; $$; diff --git a/db/schemas/sokwedb/indexes/Makefile b/db/schemas/sokwedb/indexes/Makefile index cb7fc0e..ab3d9ed 100644 --- a/db/schemas/sokwedb/indexes/Makefile +++ b/db/schemas/sokwedb/indexes/Makefile @@ -24,7 +24,7 @@ ORDER := biography_data biography_log comm_membs comm_memb_log \ swelling_sources swelling_states aggression_event_log sightings \ aggressions food_events groomings attendance \ arrivals_a species_present repro_states locations_gps locations_map \ - colobus + colobus matings ## ## CAUTION: This Makefile is not designed to be run directly. It is normally diff --git a/db/schemas/sokwedb/indexes/create/matings.m4 b/db/schemas/sokwedb/indexes/create/matings.m4 new file mode 100644 index 0000000..e35e343 --- /dev/null +++ b/db/schemas/sokwedb/indexes/create/matings.m4 @@ -0,0 +1,27 @@ +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 matings_swelling ON matings + (swelling); +CREATE INDEX IF NOT EXISTS matings_commid ON matings + (commid); diff --git a/db/schemas/sokwedb/indexes/create/watches.m4 b/db/schemas/sokwedb/indexes/create/watches.m4 index 0fc7f4c..21eb1bc 100644 --- a/db/schemas/sokwedb/indexes/create/watches.m4 +++ b/db/schemas/sokwedb/indexes/create/watches.m4 @@ -35,6 +35,13 @@ CREATE UNIQUE INDEX IF NOT EXISTS WHERE type = 'sdb_follow' OR type = 'sdb_location'; +CREATE UNIQUE INDEX IF NOT EXISTS + "(Type)=(sdb_follow) or (Type)=(sdb_mating) means Date + Focal must be unique" + ON watches + (date, focal) + WHERE type = 'sdb_follow' + OR type = 'sdb_mating'; + CREATE UNIQUE INDEX IF NOT EXISTS "(Type)=(sdb_follow) or (Type)=(sdb_pantgrunt) means Date + Focal must be unique" ON watches diff --git a/db/schemas/sokwedb/indexes/drop/matings.m4 b/db/schemas/sokwedb/indexes/drop/matings.m4 new file mode 100644 index 0000000..1c2654d --- /dev/null +++ b/db/schemas/sokwedb/indexes/drop/matings.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 + +DROP INDEX IF EXISTS matings_swelling; +DROP INDEX IF EXISTS matings_commid; diff --git a/db/schemas/sokwedb/indexes/drop/watches.m4 b/db/schemas/sokwedb/indexes/drop/watches.m4 index 323cde8..9736e13 100644 --- a/db/schemas/sokwedb/indexes/drop/watches.m4 +++ b/db/schemas/sokwedb/indexes/drop/watches.m4 @@ -27,6 +27,9 @@ DROP INDEX IF EXISTS DROP INDEX IF EXISTS "(Type)=(sdb_follow) or (Type)=(sdb_location) means Date + Focal must be unique"; +DROP INDEX IF EXISTS + "(Type)=(sdb_follow) or (Type)=(sdb_mating) means Date + Focal must be unique"; + DROP INDEX IF EXISTS "(Type)=(sdb_follow) or (Type)=(sdb_pantgrunt) means Date + Focal must be unique"; diff --git a/db/schemas/sokwedb/tables/Makefile b/db/schemas/sokwedb/tables/Makefile index 51c5e20..34857f8 100644 --- a/db/schemas/sokwedb/tables/Makefile +++ b/db/schemas/sokwedb/tables/Makefile @@ -48,7 +48,8 @@ ORDER := biography_data \ locations_map \ pantgrunts \ brecord_notes \ - colobus + colobus \ + matings ## ## 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/events.m4 b/db/schemas/sokwedb/tables/create/events.m4 index 45c1fef..d2833c9 100644 --- a/db/schemas/sokwedb/tables/create/events.m4 +++ b/db/schemas/sokwedb/tables/create/events.m4 @@ -100,6 +100,7 @@ CREATE TABLE events ( _point_behavior_time(`sdb_gps') _point_behavior_time(`sdb_map') _point_behavior_time(`sdb_brec_note') + _point_behavior_time(`sdb_mating_event') _behavior_certain(`sdb_arrival_a') _behavior_certain(`sdb_groom_scan') @@ -110,6 +111,7 @@ CREATE TABLE events ( _behavior_certain(`sdb_map') _behavior_certain(`sdb_brec_note') _behavior_certain(`sdb_colobus') + _behavior_certain(`sdb_mating_event') ); grant_priv(`EVENTS') diff --git a/db/schemas/sokwedb/tables/create/matings.m4 b/db/schemas/sokwedb/tables/create/matings.m4 new file mode 100644 index 0000000..0f23fd5 --- /dev/null +++ b/db/schemas/sokwedb/tables/create/matings.m4 @@ -0,0 +1,49 @@ +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 matings ( + eid INTEGER NOT NULL + REFERENCES events + ,swelling TEXT NOT NULL + REFERENCES cycle_states + ,fail BOOLEAN NOT NULL + ,interference TEXT NOT NULL + notonlyspaces_check(`Interference') + ,incest BOOLEAN NOT NULL + ,consort BOOLEAN NOT NULL + ,guarding BOOLEAN NOT NULL + ,courting BOOLEAN NOT NULL + ,camp BOOLEAN NOT NULL + ,commid TEXT NOT NULL + REFERENCES comm_ids + ,extractedby TEXT NOT NULL + REFERENCES people + ,source TEXT NOT NULL + REFERENCES mating_sources +); + +eid_primary_key(`MATINGS') + +grant_priv(`MATINGS') diff --git a/db/schemas/sokwedb/tables/create/watches.m4 b/db/schemas/sokwedb/tables/create/watches.m4 index a705e58..0109795 100644 --- a/db/schemas/sokwedb/tables/create/watches.m4 +++ b/db/schemas/sokwedb/tables/create/watches.m4 @@ -54,6 +54,7 @@ CREATE TABLE watches ( OR type = 'sdb_follow' OR type = 'sdb_ag_scan' OR type = 'sdb_location' + OR type = 'sdb_mating' OR type = 'sdb_pantgrunt') ,notes TEXT NOT NULL notonlyspaces_check(`Notes') diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index 68a4956..886d900 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -548,6 +548,33 @@ sdb_generated_rst()dnl .. |MATING_SOURCES.Description| replace:: :ref:`Description ` +.. |MATINGS| replace:: + :ref:`MATINGS ` +.. |MATINGS.EID| replace:: + :ref:`EID ` +.. |MATINGS.Swelling| replace:: + :ref:`Swelling ` +.. |MATINGS.Fail| replace:: + :ref:`Fail ` +.. |MATINGS.Interference| replace:: + :ref:`Interference ` +.. |MATINGS.Incest| replace:: + :ref:`Incest ` +.. |MATINGS.Consort| replace:: + :ref:`Consort ` +.. |MATINGS.Guarding| replace:: + :ref:`Guarding ` +.. |MATINGS.Courting| replace:: + :ref:`Courting ` +.. |MATINGS.Camp| replace:: + :ref:`Camp ` +.. |MATINGS.CommID| replace:: + :ref:`CommID ` +.. |MATINGS.ExtractedBy| replace:: + :ref:`ExtractedBy ` +.. |MATINGS.Source| replace:: + :ref:`Source ` + .. |NON_BREC_SIGHTING_SOURCES| replace:: :ref:`NON_BREC_SIGHTING_SOURCES ` .. |NON_BREC_SIGHTING_SOURCES.ID| replace:: diff --git a/doc/src/tables.m4 b/doc/src/tables.m4 index 443330e..ce128e5 100644 --- a/doc/src/tables.m4 +++ b/doc/src/tables.m4 @@ -52,6 +52,7 @@ and are therefore the result of at least a rudimentary analytical process. tables/humans.rst tables/locations_gps.rst tables/locations_map.rst + tables/matings.rst tables/pantgrunts.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 a0cdaf0..38080a4 100644 --- a/doc/src/tables/events.m4 +++ b/doc/src/tables/events.m4 @@ -566,7 +566,46 @@ The following table lists these rules and implications: .. _EVENTS_mating_event_code: ``sdb_mating_event`` (Mating) - A row must exist on MATINGS. + The EVENTS row must be associated with either a follow or an + non-follow mating observervation. + This means the |EVENTS.WID| column must reference a |WATCHES| row + with either a |WATCHES|.\ |WATCHES.Type| value of ``sdb_follow`` or + a |WATCHES|.\ |WATCHES.Type| value of ``sdb_mating``. + + A related row should exist on |MATINGS|; there should be a row on + |MATINGS| with a |MATINGS|.\ |MATINGS.EID| value of the event's + |EVENTS.EID|. + There may be at most one of these related |MATINGS| rows. + The system will generate a warning when there is no |MATINGS| + row related to the mating event. + + The |ROLES| rows related to the event, the rows with a |ROLES|.\ + |ROLES.EID| value equal to the EVENTS.\ |EVENTS.EID| value, + designates the individuals involved in the pantgrunt event. + The |ROLES|.\ |ROLES.Role| code of each individual that the |ROLES| + table relates to the mating event describes whether that + individual was male or female. + + There should be exactly two |ROLES| row related to the pantgrunt + event. + Only the codes ``sdb_actor`` and ``sdb_actee`` may be used as + |ROLES|.\ |ROLES.Role| code values. + The system will generate a warning when there are not exactly two + |ROLES| rows related to a pantgrunt event. + The |ROLES|.\ |ROLES.Participant| must be male, must have a + |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.Sex| value of ``sdb_male``, when + the |ROLES|.\ |ROLES.Role| value is ``sdb_actor``. + The |ROLES|.\ |ROLES.Participant| must be female, must have a + |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.Sex| value of ``sdb_male``, when + the |ROLES|.\ |ROLES.Role| value is ``sdb_actee``. + + Both the EVENTS.\ |EVENTS.Start| and EVENTS.\ |EVENTS.Stop| + columns record the time the mating was observed. + This means the value of the EVENTS.\ |EVENTS.Start| column must + equal the value of the EVENTS.\ |EVENTS.Stop| column. + + For mating events, the EVENTS.\ |EVENTS.Certainty| column must + be ``sdb_identity_certain``. .. _EVENTS_other_species_code: diff --git a/doc/src/tables/matings.m4 b/doc/src/tables/matings.m4 new file mode 100644 index 0000000..3cdfa4d --- /dev/null +++ b/doc/src/tables/matings.m4 @@ -0,0 +1,239 @@ +.. 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 + +.. _MATINGS: + +MATINGS +------- + +.. |MATINGS_summary| replace:: + Each row, taken together with the related |EVENTS| row, represents + a mating event recording during a follow. + Matings are recorded as dyadic pairs. + +|MATINGS_summary| + +The related |EVENTS| row must be a mating event; it must have an +|EVENTS|.\ |EVENTS.Behavior| value of ``sdb_mating_event``. +This related |EVENTS| row supplies the time of the mating and +relates to the follow, and the |ROLES| role related to the event +supplies information on the individuals involved. + +The system will generate a warning if the community of the follow +related to the mating information, or that of the |WATCHES| row +that exists to support the record of mating, is not the community +associated with the mating record. +This means, if the MATINGS.\ |MATINGS.CommID| is not the +|WATCHES|.\ |WATCHES.CommID| of the related |WATCHES| row. + +For further information, including additional data integrity rules, +see the documentation of the :ref:`EVENTS ` +table. + + +.. contents:: + :depth: 2 + + +.. _MATINGS.EID: + +EID (Event ID) +`````````````` + +.. |MATINGS.EID_summary| replace:: + The |EVENTS|.\ |EVENTS.EID| identifying the grooming event. + |idcol| + +|MATINGS.EID_summary| +The related event contains information on the time of the mating +and is related to the participants in the mating event. + +|notnull| + + +.. _MATINGS.Swelling: + +Swelling +```````` + +.. |MATINGS.Swelling_summary| replace:: + A code designating the degree of the female's sexual swelling. + A |CYCLE_STATES|.\ |CYCLE_STATES.Code| value. + +|MATINGS.Swelling_summary| +|notnull| + + +.. _MATINGS.Fail: + +Fail +```` + +.. |MATINGS.Fail_summary| replace:: + A |boolean| value, |true| when the mating was not completed. + +|MATINGS.Fail_summary| +|notnull| + + +.. _MATINGS.Interference: + +Interference +```````````` + +.. |MATINGS.Interference_summary| replace:: + Text containing the |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID|\ s + of chimps that interfered with the mating and/or comments regarding + interference. + +|MATINGS.Interference_summary| +|notonlyspaces| +|notnull| + + +.. _MATINGS.Incest: + +Incest +`````` + +.. |MATINGS.Incest_summary| replace:: + A |boolean| value, |true| when the mating occurred between close kin. + +|MATINGS.Incest_summary| +|notnull| + + +.. _MATINGS.Consort: + +Consort +``````` + +.. |MATINGS.Consort_summary| replace:: + A |boolean| value, |true| when the mating occurred while on consort. + +|MATINGS.Consort_summary| +|notnull| + + +.. _MATINGS.Guarding: + +Guarding +```````` + +.. |MATINGS.Guarding_summary| replace:: + A |boolean| value, |true| when the mating occurred while the male + was exhibiting guarding behavior. + +|MATINGS.Guarding_summary| +|notnull| + + +.. _MATINGS.Courting: + +Courting +```````` + +.. |MATINGS.Courting_summary| replace:: + A |boolean| value, |true| when the male courted the female. + +|MATINGS.Courting_summary| +|notnull| + + +.. _MATINGS.Camp: + +Camp +`````` + +.. |MATINGS.Camp_summary| replace:: + A |boolean| value, |true| when the mating occurred at the feeding + station. + +|MATINGS.Camp_summary| +|notnull| + + +.. _MATINGS.CommID: + +CommID +`````` + +.. |MATINGS.CommID_summary| replace:: + A code for the community recorded along with the mating information. + A |COMM_IDS|.\ |COMM_IDS.CommID| value. + This is not necessarily the community the mating participants are a + member of. + +|MATINGS.CommID_summary| + +.. note:: + This is not the canonical source of information on the focal's + community at the time of observation or :ref:`the community under + observation when the data was collected `. + It may, in fact, differ from the community recorded elsewhere. + + This column exists because of the way the community was recorded in + the old MS Access database. + There, the community was recorded twice, once in the follow and + again with the mating information. + + When there is no related follow, there is still a related |WATCHES| + row that exists solely to provide information concerning mating + data. + In this case the community information in the |WATCHES| row was + initially the same as the value of this column. + But the data could change and become out of sync. + + This column exists so no information was lost in the conversion of + the MS Access data to SokweDB. + At some point in the future, perhaps when all inconsistencies + between the value of this column and the community information + recorded elsewhere are resolved, this column may be removed from + this table. + +|notnull| + + +.. _MATINGS.Extractedby: + +Extractedby +``````````` + +.. |MATINGS.Extractedby_summary| replace:: + Code for the student or volunteer who extracted the grooming + information from the written records and prepared it for data entry + into the database. + A |PEOPLE|.\ |PEOPLE.Person| value. + +|MATINGS.Extractedby_summary| |notnull| + + +.. _MATINGS.Source: + +Source +`````` + +.. |MATINGS.Source_summary| replace:: + A code designating the source of the mating information + A |MATING_SOURCES|.\ |MATING_SOURCES.Source| value. + +|MATINGS.Source_summary| +|notnull| diff --git a/doc/src/tables/watches.m4 b/doc/src/tables/watches.m4 index 092f083..36e9994 100644 --- a/doc/src/tables/watches.m4 +++ b/doc/src/tables/watches.m4 @@ -43,7 +43,7 @@ WATCHES contains one row for each follow, and contains one row per day, per individual observed at the feeding station. Examples of ad-hoc observations not associated with any one individual -are observations of pantgrunts recorded during happenstance +are observations of pantgrunts or matings recorded during happenstance encounters. The |WATCHES.Type| column is used to distinguish and identify the @@ -227,6 +227,38 @@ The available |WATCHES.Type| values are: day, to record this the one WATCHES row is related to multiple rows on the |EVENTS| table. +``sdb_mating`` (Mating) + + Each row records a date on which individuals were observed to + mate, when there is no follow assocated with the mating. + There is one WATCHES row of this type per date, per some usually + non-meaningful |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| -- often + ``sdb_unk``. + + The |WATCHES.Focal| column contains little information that is + useful. + The |WATCHES.Focal| column contains the |BIOGRAPHY_DATA|.\ + |BIOGRAPHY_DATA.AnimID| of the individual that was purportedly the + focal of a follow, but no such follow of the individual exists. + When there is no focal on record, for whatever reason, the special + |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| value of ``sdb_unk``, + representing an unknown individual -- or, in this case, "no + individual", is expected to be the |WATCHES.Focal| value. + + The |WATCHES.CommID| column contains the code for the community + recorded at along with the mating; the |COMM_IDS|.\ + |COMM_IDS.CommID| of the community. + This is not necessarily the community the mating individuals + are a member of, although it usually is. + + There may only be one row on WATCHES per day, per individual + recorded along with the mating data. + This means, the combination of |WATCHES.Type|, |WATCHES.Focal| and + |WATCHES.Date| must be unique. + When multiple matings are recorded in a follow, or are recorded + on some day when there is no follow, to record this the one WATCHES + row has multiple related rows on the |EVENTS| table. + ``sdb_pantgrunt`` (Pantgrunt) Each row records a date on which an individual was observed to diff --git a/include/global_constants.m4 b/include/global_constants.m4 index a017a0e..3069e2d 100644 --- a/include/global_constants.m4 +++ b/include/global_constants.m4 @@ -203,6 +203,7 @@ define(`sdb_brec', `B') define(`sdb_follow', `F') define(`sdb_ag_scan', `G') define(`sdb_location', `L') +define(`sdb_mating', `M') define(`sdb_pantgrunt', `P') divert(`0')dnl Output with m4 again -- 2.34.1