From 83e773812cac77cacc021a5be3de22a6565adc9f Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Fri, 3 Jul 2026 00:27:45 +0000 Subject: [PATCH] Create DYADS view and make it INSERTable --- db/schemas/lib/triggers/Makefile | 1 + db/schemas/lib/triggers/create/dyads.m4 | 521 ++++++++ db/schemas/lib/triggers/drop/dyads.m4 | 25 + db/schemas/sokwedb/views/Makefile | 1 + db/schemas/sokwedb/views/create/dyads.m4 | 86 ++ doc/diagrams/dyads.svg | 1448 ++++++++++++++++++++++ doc/src/epilog.inc.m4 | 1 + doc/src/views.m4 | 9 + doc/src/views/dyads.m4 | 188 +++ 9 files changed, 2280 insertions(+) create mode 100644 db/schemas/lib/triggers/create/dyads.m4 create mode 100644 db/schemas/lib/triggers/drop/dyads.m4 create mode 100644 db/schemas/sokwedb/views/create/dyads.m4 create mode 100644 doc/diagrams/dyads.svg create mode 100644 doc/src/views/dyads.m4 diff --git a/db/schemas/lib/triggers/Makefile b/db/schemas/lib/triggers/Makefile index a64cf45..c08b919 100644 --- a/db/schemas/lib/triggers/Makefile +++ b/db/schemas/lib/triggers/Makefile @@ -46,6 +46,7 @@ ORDER := comm_ids \ repro_states \ pantgrunts \ obs \ + dyads \ pantgrunts_view \ brecord_notes \ colobus \ diff --git a/db/schemas/lib/triggers/create/dyads.m4 b/db/schemas/lib/triggers/create/dyads.m4 new file mode 100644 index 0000000..7cae496 --- /dev/null +++ b/db/schemas/lib/triggers/create/dyads.m4 @@ -0,0 +1,521 @@ +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 dyads view +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`macros.m4')dnl + +dnl Plpgsql fragment for use in errors to show content of NEW row +dnl +dnl Syntax: _show_row() +dnl +dnl Remarks: +dnl This makes everything more readable. +dnl +changequote({,}) +define({_show_row}, {'Key (WID) = (' + || textualize(`NEW.wid') + || '), Value (Date) = (' + || textualize(`NEW.date') + || '), Value (Focal) = (' + || textualize(`NEW.focal') + || '), Value (Type) = (' + || textualize(`NEW.type') + || '), Value (CommID) = (' + || textualize(`NEW.commid') + || '), Key (EID) = (' + || textualize(`NEW.eid') + || '), Value (Behavior) = (' + || textualize(`NEW.behavior') + || '), Value (Start) = (' + || textualize(`NEW.start') + || '), Value (Stop) = (' + || textualize(`NEW.stop') + || '), Value (Certainty) = (' + || textualize(`NEW.certainty') + || '), Key (Actor_PID) = (' + || textualize(`NEW.actor_pid') + || '), Value (Actor) = (' + || textualize(`NEW.actor') + || '), Key (Recipient_PID) = (' + || textualize(`NEW.recipient_pid') + || '), Value (Recipient) = (' + || textualize(`NEW.recipient') + || '), Value (Notes) = (' + || textualize(`NEW.notes') + || '), Value (Event_Notes) = (' + || textualize(`NEW.event_notes') + || ')'dnl +}) +changequote(`,') + +RAISE INFO 'dyads_insert_func'; +CREATE OR REPLACE FUNCTION dyads_insert_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + DECLARE + target_wid watches.wid%TYPE; + target_focal watches.focal%TYPE; + target_commid watches.commid%TYPE; + target_date watches.date%TYPE; + target_type watches.type%TYPE; + target_notes watches.notes%TYPE; + + target_eid events.eid%TYPE; + target_behavior events.behavior%TYPE; + target_start events.start%TYPE; + target_stop events.stop%TYPE; + target_certainty events.certainty%TYPE; + target_event_notes events.notes%TYPE; + + target_actor_pid roles.pid%TYPE; + target_recipient_pid roles.pid%TYPE; + + create_obs BOOLEAN; + BEGIN + -- Function for dyads instead of insert trigger + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + -- + -- Remarks: + -- Do not enforce any conditions on the ID values. The place + -- to do that is in the triggers on the tables, so the rules are + -- consistent no matter the interface used to change table content. + + -- + -- Initial validation of data supplied + -- + + IF NEW.eid IS NULL THEN + create_obs := FALSE; + + -- Use other data values to find the EID to which to relate the + -- new ROLES rows. + + -- Behavior must be supplied - to lookup EVENTS + IF NEW.behavior IS NULL THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into DYADS' + , DETAIL = 'The supplied Behavior value may not be NULL' + || ' when EID is NULL' + || ': ' + || _show_row(); + END IF; + target_behavior := NEW.behavior; + + -- Start must be supplied - to lookup EVENTS + IF NEW.start IS NULL THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into DYADS' + , DETAIL = 'The supplied Start value may not be NULL' + || ' when EID is NULL' + || ': ' + || _show_row(); + END IF; + target_start := NEW.start; + + -- Stop must be supplied - to lookup EVENTS + IF NEW.stop IS NULL THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into DYADS' + , DETAIL = 'The supplied Stop value may not be NULL' + || ' when EID is NULL' + || ': ' + || _show_row(); + END IF; + target_stop := NEW.stop; + + IF NEW.wid IS NULL THEN + -- Use Date, Focal Type, Behavior, Start, and Stop to query OBS. + + -- Date must be supplied - to lookup WATCHES + IF NEW.date IS NULL THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into DYADS' + , DETAIL = 'The supplied Date value may not be NULL' + || ' when WID is NULL' + || ': ' + || _show_row(); + END IF; + + -- Focal must be supplied - to lookup WATCHES + IF NEW.focal IS NULL THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into DYADS' + , DETAIL = 'The supplied Focal value may not be NULL' + || ' when WID is NULL' + || ': ' + || _show_row(); + END IF; + + -- Type must be supplied - to lookup WATCHES + IF NEW.type IS NULL THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into DYADS' + , DETAIL = 'The supplied Type value may not be NULL' + || ' when WID is NULL' + || ': ' + || _show_row(); + END IF; + + SELECT obs.wid , obs.date , obs.focal , obs.type , obs.commid + , obs.certainty + , obs.notes, obs.event_notes + INTO target_wid, target_date, target_focal, target_type, target_commid + , target_certainty + , target_notes, target_event_notes + FROM obs + WHERE obs.focal = NEW.focal + AND obs.date = NEW.date + AND obs.type = NEW.type + AND obs.behavior = NEW.behavior + AND obs.start = NEW.start + AND obs.stop = NEW.stop; + + IF FOUND THEN + -- Validate supplied values against db values + IF (NEW.commid IS NOT NULL + AND NEW.commid <> target_commid) + OR (NEW.certainty IS NOT NULL + AND NEW.certainty <> target_certainty) + OR (NEW.notes IS NOT NULL + AND NEW.notes <> target_notes) + OR (NEW.event_notes IS NOT NULL + AND NEW.event_notes <> target_event_notes) THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into DYADS' + , DETAIL = 'The values in the database for the OBS' + ||' row with (OBS.Focal) = (' + || NEW.focal + || ') and (OBS.Date = (' + || NEW.date + || ') and (OBS.Type = (' + || NEW.Type + || ') and (OBS.Behavior = (' + || NEW.behavior + || ') and (OBS.Start = (' + || NEW.start + || ') and (OBS.Stop = (' + || NEW.stop + || '), which are:' + || ' Key (WID) = (' + || target_wid + || '), Value (Date) = (' + || target_date + || '), Value (Focal) = (' + || target_focal + || '), Value (Type) = (' + || target_type + || '), Value (CommID) = (' + || target_commid + || '), Key (EID) = (' + || target_eid + || '), Value (Certainty) = (' + || target_certainty + || '), Value (Notes) = (' + || target_notes + || '), Value (Event_Notes) = (' + || target_event_notes + || ') do not match the values supplied to the INSERT' + || ': ' + || _show_row(); + END IF; + ELSE + -- No such OBS row, make one + create_obs := TRUE; + END IF; + ELSE -- NEW.wid IS NOT NULL + target_wid := NEW.wid; + + -- Use WID, Behavior, Start, and Stop to query OBS + SELECT obs.wid , obs.date , obs.focal , obs.type , obs.commid + , obs.eid , obs.certainty + , obs.notes, obs.event_notes + INTO target_wid, target_date, target_focal, target_type, target_commid + , target_eid, target_certainty + , target_notes, target_event_notes + FROM obs + WHERE obs.wid = NEW.wid + AND obs.behavior = NEW.behavior + AND obs.start = NEW.start + AND obs.stop = NEW.stop; + + IF FOUND THEN + -- Validate supplied values against db values + IF (NEW.date IS NOT NULL + AND NEW.date <> target_date) + OR (NEW.focal IS NOT NULL + AND NEW.focal <> target_focal) + OR (NEW.type IS NOT NULL + AND NEW.type <> target_type) + OR (NEW.commid IS NOT NULL + AND NEW.commid <> target_commid) + OR (NEW.certainty IS NOT NULL + AND NEW.certainty <> target_certainty) + OR (NEW.notes IS NOT NULL + AND NEW.notes <> target_notes) + OR (NEW.event_notes IS NOT NULL + AND NEW.event_notes <> target_event_notes) THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into DYADS' + , DETAIL = 'The values in the database for the OBS' + ||' row with (OBS.WID) = (' + || NEW.wid + || ') and (OBS.Behavior = (' + || textualize(`NEW.behavior') + || ') and (OBS.Start = (' + || textualize(`NEW.start') + || ') and (OBS.Stop = (' + || textualize(`NEW.stop') + || '), which are:' + || ' Key (WID) = (' + || target_wid + || '), Value (Date) = (' + || target_date + || '), Value (Focal) = (' + || target_focal + || '), Value (Type) = (' + || target_type + || '), Value (CommID) = (' + || target_commid + || '), Key (EID) = (' + || target_eid + || '), Value (Certainty) = (' + || target_certainty + || '), Value (Notes) = (' + || target_notes + || '), Value (Event_Notes) = (' + || target_event_notes + || ') do not match the values supplied to the INSERT' + || ': ' + || _show_row(); + END IF; + ELSE + -- No such OBS row, make one + create_obs := TRUE; + END IF; + END IF; + + IF create_obs THEN + WITH ins AS + (INSERT INTO obs (wid , date , focal , type , commid + , eid , behavior , start , stop + , certainty + , notes , event_notes) + VALUES ( NEW.wid, NEW.date, NEW.focal, NEW.type, NEW.commid + , NEW.eid, NEW.behavior, NEW.start, NEW.stop + , NEW.certainty + , NEW.notes, NEW.event_notes) + RETURNING wid, date, focal, type, commid + , eid, behavior, start, stop + , certainty + , notes, event_notes) + SELECT ins.wid , ins.date , ins.focal , ins.type , ins.commid + , ins.eid , ins.behavior , ins.start , ins.stop + , ins.certainty + , ins.notes , ins.event_notes + INTO target_wid, target_date, target_focal, target_type, target_commid + , target_eid, target_behavior, target_start, target_stop + , target_certainty + , target_notes, target_event_notes + FROM ins; + END IF; + + ELSE -- NEW.EID IS NOT NULL + target_eid := NEW.eid; + + -- Use EID to lookup OBS + SELECT obs.wid , obs.date , obs.focal , obs.type , obs.commid + , obs.behavior , obs.start , obs.stop , obs.certainty + , obs.notes, obs.event_notes + INTO target_wid, target_date, target_focal, target_type, target_commid + , target_behavior, target_start, target_stop, target_certainty + , target_notes, target_event_notes + FROM obs + WHERE obs.eid = NEW.eid; + + IF FOUND THEN + -- Validate supplied values against db values + IF (NEW.wid IS NOT NULL + AND NEW.wid <> target_wid) + OR (NEW.date IS NOT NULL + AND NEW.date <> target_date) + OR (NEW.focal IS NOT NULL + AND NEW.focal <> target_focal) + OR (NEW.type IS NOT NULL + AND NEW.type <> target_type) + OR (NEW.commid IS NOT NULL + AND NEW.commid <> target_commid) + OR (NEW.behavior IS NOT NULL + AND NEW.behavior <> target_behavior) + OR (NEW.start IS NOT NULL + AND NEW.start <> target_start) + OR (NEW.stop IS NOT NULL + AND NEW.stop <> target_stop) + OR (NEW.certainty IS NOT NULL + AND NEW.certainty <> target_certainty) + OR (NEW.notes IS NOT NULL + AND NEW.notes <> target_notes) + OR (NEW.event_notes IS NOT NULL + AND NEW.event_notes <> target_event_notes) THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into DYADS' + , DETAIL = 'The values in the database for the OBS' + || ' row with (OBS.EID) = (' + || NEW.eid + || '), which are:' + || ' Key (WID) = (' + || target_wid + || '), Value (Date) = (' + || target_date + || '), Value (Focal) = (' + || target_focal + || '), Value (Type) = (' + || target_type + || '), Value (CommID) = (' + || target_commid + || '), Key (EID) = (' + || target_eid + || '), Value (Behavior) = (' + || target_behavior + || '), Value (Start) = (' + || target_start + || '), Value (Stop) = (' + || target_stop + || '), Value (Certainty) = (' + || target_certainty + || '), Value (Notes) = (' + || target_notes + || '), Value (Event_Notes) = (' + || target_event_notes + || ') do not match the values supplied to the INSERT' + || ': ' + || _show_row(); + END IF; + ELSE + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into DYADS' + , DETAIL = 'The EVENTS row with (EVENTS.EID) = (' + || NEW.eid + || ') does not exist' + || ': ' + || _show_row(); + END IF; + END IF; + + -- The requirement that there be non-NULL data in the rest of the columns + -- is checked by the underlying tables. + + -- + -- Insert the ROLES + -- (Do one row at a time, just so we can check the PID values.) + -- + + -- Actor (or Mutual) + INSERT INTO roles (eid, role, participant) + VALUES (target_eid + ,CASE + WHEN NEW.twosided THEN + 'sdb_mutual' + ELSE + 'sdb_actor' + END + ,NEW.actor); + target_actor_pid := CURRVAL('roles_pid_seq'); + + IF NEW.actor_pid IS NOT NULL + AND NEW.actor_pid <> target_actor_pid THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into DYADS' + , DETAIL = 'The value of the ROLES.PID inserted into the database' + || ' for the actor (or mutual pangrunter in the' + || ' Actor column), (PID) = (' + || target_actor_pid + || '), does not match the value supplied to the INSERT' + || ': ' + || _show_row(); + END IF; + + -- Recipient (or Mutual) + INSERT INTO roles (eid, role, participant) + VALUES (target_eid + ,CASE + WHEN NEW.twosided THEN + 'sdb_mutual' + ELSE + 'sdb_actee' + END + ,NEW.recipient); + target_recipient_pid := CURRVAL('roles_pid_seq'); + + IF NEW.recipient_pid IS NOT NULL + AND NEW.recipient_pid <> target_recipient_pid THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into DYADS' + , DETAIL = 'The value of the ROLES.PID inserted into the database' + || ' for the recipient (or mutual pangrunter in the' + || ' Recipient column), (PID) = (' + || target_recipient_pid + || '), does not match the value supplied to the INSERT' + || ': ' + || _show_row(); + END IF; + + -- Assign values to all the NEW columns. In theory then RETURNING + -- will return the newly inserted, or existing, data values. + NEW.wid := target_wid; + NEW.focal := target_focal; + NEW.commid := target_commid; + NEW.date := target_date; + NEW.type := target_type; + NEW.eid := target_eid; + NEW.behavior := target_behavior; + NEW.start := target_start; + NEW.stop := target_stop; + NEW.certainty := target_certainty; + NEW.actor_pid := target_actor_pid; + -- We know Actor has a value. + NEW.recipient_pid := target_recipient_pid; + -- We know Recipient has a value. + NEW.notes := target_notes; + NEW.event_notes := target_event_notes; + + RETURN NEW; + END; +$$; + + +CREATE TRIGGER dyads_insert_trigger + INSTEAD OF INSERT + ON dyads FOR EACH ROW + EXECUTE PROCEDURE dyads_insert_func(); + +CREATE TRIGGER dyads_update_trigger + INSTEAD OF UPDATE + ON dyads FOR EACH ROW + EXECUTE PROCEDURE _error_immutable_view(); + +CREATE TRIGGER dyads_delete_trigger + INSTEAD OF DELETE + ON dyads FOR EACH ROW + EXECUTE PROCEDURE _error_immutable_view(); diff --git a/db/schemas/lib/triggers/drop/dyads.m4 b/db/schemas/lib/triggers/drop/dyads.m4 new file mode 100644 index 0000000..078b29f --- /dev/null +++ b/db/schemas/lib/triggers/drop/dyads.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 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 dyads view +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl + +DROP FUNCTION IF EXISTS dyads_insert_func() CASCADE; +DROP FUNCTION IF EXISTS dyads_update_func() CASCADE; +DROP FUNCTION IF EXISTS dyads_delete_func CASCADE; diff --git a/db/schemas/sokwedb/views/Makefile b/db/schemas/sokwedb/views/Makefile index a9fd519..a3856ed 100644 --- a/db/schemas/sokwedb/views/Makefile +++ b/db/schemas/sokwedb/views/Makefile @@ -22,6 +22,7 @@ # database after the views they reference. ORDER := biography \ obs \ + dyads \ pantgrunts_view ## diff --git a/db/schemas/sokwedb/views/create/dyads.m4 b/db/schemas/sokwedb/views/create/dyads.m4 new file mode 100644 index 0000000..dbff0af --- /dev/null +++ b/db/schemas/sokwedb/views/create/dyads.m4 @@ -0,0 +1,86 @@ +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 OR REPLACE VIEW dyads ( + wid + ,date + ,focal + ,type + ,commid + ,eid + ,behavior + ,start + ,stop + ,certainty + ,actor_pid + ,actor + ,recipient_pid + ,recipient + ,twosided + ,notes + ,event_notes + ) + AS + SELECT + obs.wid + ,obs.date + ,obs.focal + ,obs.type + ,obs.commid + ,obs.eid + ,obs.behavior + ,obs.start + ,obs.stop + ,obs.certainty + ,actors.pid AS actor_pid + ,actors.participant AS actor + ,recipients.pid AS recipient_pid + ,recipients.participant AS recipient + ,CASE + WHEN actors.role = 'sdb_mutual' THEN + TRUE + ELSE + FALSE + END AS twosided + ,obs.notes + ,obs.event_notes + FROM obs + JOIN roles AS actors + ON (actors.eid = obs.eid + AND (actors.role = 'sdb_actor' + OR actors.role = 'sdb_mutual')) + JOIN roles AS recipients + ON (recipients.eid = obs.eid + AND (recipients.role = 'sdb_actee' + OR recipients.role = 'sdb_mutual')) + WHERE (actors.role = 'sdb_actor' + OR -- Without further conditions, when dyads are mutual, + -- the two individuals appear as 4 rows: paired, paired + -- in reverse order, and each matched with themselves. + -- Assure consistent placement, among query executions, + -- in the actor or recipient columns. + actors.pid < recipients.pid); + +grant_priv(`DYADS') diff --git a/doc/diagrams/dyads.svg b/doc/diagrams/dyads.svg new file mode 100644 index 0000000..ead599b --- /dev/null +++ b/doc/diagrams/dyads.svg @@ -0,0 +1,1448 @@ + +image/svg+xmlRole = 'Actee'OR Role = 'Mutual'The actor (ormutual participant)observedThe recipient (ormutual participant)observedThe observed event theactor (or mutualparticipant)participated inThe observed event therecipient (ormutual participant)participated inTRUE whenRole = 'Mutual'Role = 'Actor'OR Role = 'Mutual'OBSWIDCommIDTypeEvent_NotesTwoSidedDateFocalEIDBehaviorStartStopCertaintyNotesROLES (ACTORS)PID (Actor_PID)EIDParticipant (Actor)ROLES (RECIPIENTS)PID (Recipient_PID)EIDParticipant (Recipient) diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index 8d552d1..3f137b0 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -730,6 +730,7 @@ sdb_generated_rst()dnl .. View name substitutions. .. |BIOGRAPHY| replace:: :ref:`BIOGRAPHY ` +.. |DYADS| replace:: :ref:`DYADS ` .. |OBS| replace:: :ref:`OBS ` .. |PANTGRUNTS_VIEW| replace:: :ref:`PANTGRUNTS_VIEW ` diff --git a/doc/src/views.m4 b/doc/src/views.m4 index 6b98846..b96cb11 100644 --- a/doc/src/views.m4 +++ b/doc/src/views.m4 @@ -58,6 +58,14 @@ in the old MS Access database. Others reproduce common query patterns, eliminiating the need to connect (join) multiple tables. ++-------------------+----------------------+--------------------------+----------------------+ +| View | One row for each | Purpose | Tables/Views used | ++===================+======================+==========================+======================+ +| |DYADS| | |EVENTS| row, where | Simplifies querying -- | |WATCHES|, |EVENTS|, | +| | the behavior | puts the two individals | |ROLES| (twice) | +| | observed is a dyadic | involved in a dyadic | | +| | interaction | interactions into a | | +| | | single row. | | +-------------------+----------------------+--------------------------+----------------------+ | |OBS| | |EVENTS| row, so one | Simplifies querying -- | |WATCHES|, |EVENTS| | | (Observations) | row for every | attaches a date and a | | @@ -72,5 +80,6 @@ Others reproduce common query patterns, eliminiating the need to connect .. toctree:: :maxdepth: 3 + views/dyads.rst views/obs.rst views/pantgrunts_view.rst diff --git a/doc/src/views/dyads.m4 b/doc/src/views/dyads.m4 new file mode 100644 index 0000000..7bf5721 --- /dev/null +++ b/doc/src/views/dyads.m4 @@ -0,0 +1,188 @@ +.. 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 + +.. _DYADS: + +DYADS +----- + +.. |DYADS_summary| replace:: + Each row represents a dyadic interaction between two individuals. + Having both individuals appear in one row can make the data eaiser + to work with. + +|DYADS_summary| + +The view puts the individual performing an action and the +individual receiving the action in separate columns. +There is a flag to indicate whether the action is mutual; when +|true| ,there is not meaning to the placement of the interacting +individuals into these two columns. + +This view extends the |OBS| view with columns for the individual +performing the action and the individual receiving the action. + +The DYADS view can be a core component when querying dyadic +interactions. + +For an example use of the DYADS view, see the |PANTGRUNTS_VIEW| +documentation. +It joins the DYADS view with the |PANTGRUNTS| table to provide a +more-or-less complete report on pantgrunt data. +It uses DYADS to supply the date, time, and participants and adds to +that the detailed data on pantgrunts found in |PANTGRUNTS|. +Queries similar to the ``SELECT`` statement found within the +|PANTGRUNTS_VIEW| definition can be written to report on the other +behaviors recorded as dyadic interactions -- groomings, matings, +aggressions, etc. + +.. _DYADS_Definition: + +Definition +`````````` +.. include:: /view_sql/sokwedb/dyads.sql + :code: sql + + +.. _DYADS_ER_Diagram: + +ER Diagram +`````````` + +.. figure:: sdb_er_image_path(images/dyads) + :alt: DYADS Entity-Relationship Diagram + :width: 100 % + + DYADS + + +.. _DYADS_Columns: + +Columns of the DYADS View +````````````````````````` + +.. Configure the From column to be 35% of the line length, Description 40% + Otherwise, the column names can be hyphenated and contain line breaks. +.. tabularcolumns:: l \Y{.35} \Y{.40} + +.. table:: The columns of the DYADS view + :widths: auto + :class: longtable + + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Column | From | Description | + +=================+=================================================+=====================================================================+ + | WID | |WATCHES|.\ |WATCHES.WID| | Identifier of the related |WATCHES| row | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Date | |WATCHES|.\ |WATCHES.Date| | Date of the event | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Focal | |WATCHES|.\ |WATCHES.Focal| | Focal of follow, or focal of a non-existant follow, or an | + | | | un-interesting AnimID | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Type | |WATCHES|.\ |WATCHES.Type| | Category of observation, often determining the data collection | + | | | protocol: follow, feeding station attendance, groom scans, etc. | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | CommID | |WATCHES|.\ |WATCHES.CommID| | The community identifier associated with the Date/Focal/Type | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | EID | |EVENTS|.\ |EVENTS.EID| | Identifier of the |EVENTS| row | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Behavior | |EVENTS|.\ |EVENTS.Behavior| | Code designating the type of event observed | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Start | |EVENTS|.\ |EVENTS.Start| | Time the event started (inclusive) | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Stop | |EVENTS|.\ |EVENTS.Stop| | Time the event finished (inclusive) | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Certainty | |EVENTS|.\ |EVENTS.Certainty| | Certainty of the event observation, when meaningful | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Actor_PID | |ROLES|.\ |ROLES.PID| | Identifier of the |ROLES| row containing the individual performing | + | | | the action, or mutually acting | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Actor | |ROLES|.\ |ROLES.Participant| | The |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| of the individual | + | | | performing the action, or mutually actintg | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Recipient | |ROLES|.\ |ROLES.Participant| | The |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| of the individual | + | | | receiving the action, or mutually acting | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Recipient_PID | |ROLES|.\ |ROLES.PID| | Identifier of the |ROLES| row containig the individual receiving | + | | | the action, or mutually acting | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | TwoSided | An expression based on |ROLES|.\ |ROLES.Role| | Boolean, |true| when both the Actor and the Recipient were | + | | | performing the action | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Notes | |WATCHES|.\ |WATCHES.Notes| | Textual notes on the observation for the Date/Focal/Type | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Event_Notes | |EVENTS|.\ |EVENTS.Notes| | Textual notes on the event | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + + +.. _DYADS_Operations_Allowed: + +Operations Allowed +`````````````````` + +INSERT + INSERTing a row into DYADS inserts two rows into |ROLES|. + One row may also be inserted into |EVENTS|, and one may be + inserted into |WATCHES|. + + The columns EID, WID, Focal, Date, Type, Behavior, Start, and Stop + may all be used, in the various combinations described below, to + relate the new |ROLES| rows to existing |EVENTS| rows, or to create + new |EVENTS| and |WATCHES| rows when they do not already exist. + + If an EID is supplied, that is all that is required. + The identified |EVENTS| row must already exist. + + If an EID is not supplied, the columns WID, Focal, Date, Type, + Behavior, Start, and Stop are used to query |OBS| to discover if a + matching |EVENTS| row exists or if there is no match and new one + must be inserted. + The Behavior, Start, and Stop columns must be supplied in this case. + + If a WID is supplied it is used, along with Behavior, Start, and + Stop, to match against database content. + If a WID is not supplied, the Focal, Date, and Type columns must + be supplied. + Then they are used, along with Behavior, Start, and Stop, to match + against database content. + + If no match is found, a row is inserted into |OBS|. + This means the rules regarding what data must be supplied when + INSERTing into |OBS|, and when |OBS| re-uses existing rows or + creates new rows, also apply to DYADS. + + If an existing |EVENTS| row matches, the new |ROLES| rows are + related to the |EVENTS| row so discovered. + Otherwise they are related to the newly created |EVENTS| row. + + When existing rows are found in the database, all (non-|null|) data + values supplied must match the data values that already exist. + + The WID, EID, Actor_PID, and Recipient_PID columns do not have + their values inserted into new rows. + If non-|null| values are supplied for these columns they must match + the values already existing in, or inserted into, the database. + +UPDATE + This operation is not allowed. + +DELETE + This operation is not allowed. -- 2.34.1