From 323721fabcebd9541b690a79f83c8e34869b69e3 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Thu, 18 Jun 2026 00:16:19 +0000 Subject: [PATCH] Add PANTGRUNTS_VIEW view --- db/schemas/lib/triggers/Makefile | 3 +- .../lib/triggers/create/pantgrunts_view.m4 | 39 ++++++ db/schemas/sokwedb/views/Makefile | 3 +- .../sokwedb/views/create/pantgrunts_view.m4 | 102 +++++++++++++++ doc/src/epilog.inc.m4 | 1 + doc/src/views.m4 | 28 ++++ doc/src/views/pantgrunts_view.m4 | 120 ++++++++++++++++++ 7 files changed, 294 insertions(+), 2 deletions(-) create mode 100644 db/schemas/lib/triggers/create/pantgrunts_view.m4 create mode 100644 db/schemas/sokwedb/views/create/pantgrunts_view.m4 create mode 100644 doc/src/views/pantgrunts_view.m4 diff --git a/db/schemas/lib/triggers/Makefile b/db/schemas/lib/triggers/Makefile index 227523b..2248310 100644 --- a/db/schemas/lib/triggers/Makefile +++ b/db/schemas/lib/triggers/Makefile @@ -44,7 +44,8 @@ ORDER := comm_ids \ humans \ species_present \ repro_states \ - pantgrunts + pantgrunts \ + pantgrunts_view DROP_EXISTING := true diff --git a/db/schemas/lib/triggers/create/pantgrunts_view.m4 b/db/schemas/lib/triggers/create/pantgrunts_view.m4 new file mode 100644 index 0000000..2bfe64d --- /dev/null +++ b/db/schemas/lib/triggers/create/pantgrunts_view.m4 @@ -0,0 +1,39 @@ +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 pantgrunts_view view +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`macros.m4')dnl + + +CREATE TRIGGER pantgrunts_view_insert_trigger + INSTEAD OF INSERT + ON pantgrunts_view FOR EACH ROW + EXECUTE PROCEDURE _error_immutable_view(); + +CREATE TRIGGER pantgrunts_view_update_trigger + INSTEAD OF UPDATE + ON pantgrunts_view FOR EACH ROW + EXECUTE PROCEDURE _error_immutable_view(); + +CREATE TRIGGER pantgrunts_view_delete_trigger + INSTEAD OF DELETE + ON pantgrunts_view FOR EACH ROW + EXECUTE PROCEDURE _error_immutable_view(); diff --git a/db/schemas/sokwedb/views/Makefile b/db/schemas/sokwedb/views/Makefile index 9d88809..1f64fa2 100644 --- a/db/schemas/sokwedb/views/Makefile +++ b/db/schemas/sokwedb/views/Makefile @@ -20,7 +20,8 @@ # This determines the order in which the views are put into the # database. This is important because views must be put into the # database after the views they reference. -ORDER := biography +ORDER := biography \ + pantgrunts_view ## ## CAUTION: This Makefile is not designed to be run directly. It is normally diff --git a/db/schemas/sokwedb/views/create/pantgrunts_view.m4 b/db/schemas/sokwedb/views/create/pantgrunts_view.m4 new file mode 100644 index 0000000..cf73828 --- /dev/null +++ b/db/schemas/sokwedb/views/create/pantgrunts_view.m4 @@ -0,0 +1,102 @@ +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 pantgrunts_view ( + date + ,focal + ,time + ,actor + ,recipient + ,multiactors + ,multirecipients + ,twosided + ,commid + ,pg_commid + ,source + ,enteredby + ,notes + -- Administrative information + ,type + ,wid + ,eid + ,actor_pid + ,recipient_pid + ) + AS + -- The conditions on EVENTS.Behavior and WATCHES.Type are + -- redundant, because the database rules ensure the given + -- values when the PANTGRUNTS table is joined. However, + -- including them may help performance. + -- Similar conditions on the ROLES.Role regarding the + -- grunter and gruntee could also help, but adding these + -- adds a lot of verbage that makes the whole query less + -- clear. + SELECT + watches.date AS date + ,watches.focal AS focal + ,events.start AS time + ,grunters.participant AS actor + ,gruntees.participant AS recipient + ,pantgrunts.multiactors AS multiactors + ,pantgrunts.multirecipients AS multirecipients + ,CASE + WHEN grunters.role = 'sdb_mutual' THEN + TRUE + ELSE + FALSE + END AS twosided + ,watches.commid AS commid + ,pantgrunts.commid AS pg_commid + ,pantgrunts.source AS source + ,pantgrunts.enteredby AS enteredby + ,events.notes AS notes + ,watches.type AS type + ,watches.wid AS wid + ,events.eid AS eid + ,grunters.pid AS actor_pid + ,gruntees.pid AS recipient_pid + FROM watches + JOIN events + ON (events.wid = watches.wid + AND events.behavior = 'sdb_pg_event') + JOIN roles AS grunters + ON (grunters.eid = events.eid) + JOIN roles AS gruntees + ON (gruntees.eid = events.eid) + JOIN pantgrunts + ON (pantgrunts.eid = events.eid) + WHERE (watches.type = 'sdb_pantgrunt' + OR watches.type = 'sdb_follow') + AND (grunters.role = 'sdb_actor' + OR -- Grooming is mutual. Without further + -- conditions the two individuals appear as 4 + -- rows: paired, paired in reverse order, and + -- each matched with themselves. This + -- condition also assures consistent + -- placement, among query executions, in the + -- actor or recipient columns. + grunters.participant < gruntees.participant); + +grant_priv(`PANTGRUNTS_VIEW') diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index 3d9f3cc..5dc3963 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -613,6 +613,7 @@ sdb_generated_rst()dnl .. View name substitutions. .. |BIOGRAPHY| replace:: :ref:`BIOGRAPHY ` +.. |PANTGRUNTS_VIEW| replace:: :ref:`PANTGRUNTS_VIEW ` .. Function substitutions, which exist for the same reason diff --git a/doc/src/views.m4 b/doc/src/views.m4 index 77eaf3d..8a8b6e6 100644 --- a/doc/src/views.m4 +++ b/doc/src/views.m4 @@ -43,3 +43,31 @@ Others reproduce common query patterns, eliminiating the need to connect views/biography.rst + +.. _data_modification_views: + +Data Modification Views +======================= + +The views appearing in this section exist so that data can be more +easily uploaded into the database and more easily modifed. +These views may also be suitable for querying. + +Some exist to make the data look more like the "old" data, as it appeared +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 | ++===================+======================+=======================+======================+ +| |PANTGRUNTS_VIEW| | |EVENTS| row related | Upload pantgrunt | |WATCHES|, |EVENTS|, | +| | to a |PANTGRUNTS| | data, reproduce "old" | |ROLES|, | +| | row | data | |PANTGRUNTS| | ++-------------------+----------------------+-----------------------+----------------------+ + +.. toctree:: + :maxdepth: 3 + + views/pantgrunts_view.rst + diff --git a/doc/src/views/pantgrunts_view.m4 b/doc/src/views/pantgrunts_view.m4 new file mode 100644 index 0000000..4c23eda --- /dev/null +++ b/doc/src/views/pantgrunts_view.m4 @@ -0,0 +1,120 @@ +.. 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 + +.. _PANTGRUNTS_VIEW: + +PANTGRUNTS_VIEW +--------------- + +.. |PANTGRUNTS_VIEW_summary| replace:: + Each row represents a dyadic pantgrunt interaction between two + individuals. + Rows resemble the MS Access database structure, which may make the + data easier to work with. + +|PANTGRUNTS_VIEW_summary| + +The view puts the individual performing the pantgrunt and the +individual receiving the pantgrunt in separate columns. +There is a flag to indicate whether the pantgrunting is mutual; when +|true| ,there is not meaning to the placement of the interacting +individuals into these two columns. + + +.. _PANTGRUNTS_VIEW_Definition: + +Definition +`````````` +.. include:: /view_sql/sokwedb/pantgrunts_view.sql + :code: sql + + +.. _PANTGRUNTS_VIEW_Columns: + +Columns in the PANTGRUNTS_VIEW 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 in PANTGRUNTS_VIEW + :widths: auto + :class: longtable + + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Column | From | Description | + +=================+=================================================+=====================================================================+ + | Date | |WATCHES|.\ |WATCHES.Date| | Date of pantgrunt | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Focal | |WATCHES|.\ |WATCHES.Focal| | Focal of follow, or focal of a non-existant follow, or an | + | | | un-interesting AnimID | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Time | |EVENTS|.\ |EVENTS.Start| | Time of pantgrunt | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Actor | |ROLES|.\ |ROLES.Participant| | The |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| of the individual | + | | | pantgrunting, or mutually pantgrunting | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Recipient | |ROLES|.\ |ROLES.Participant| | The |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| of the individual | + | | | receiving the pantgrunt, or mutually pantgrunting | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | MultiActors | |PANTGRUNTS|.\ |PANTGRUNTS.MultiActors| | Boolean, |true| when there were multiple pantgrunters | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | MultiRecipients | |PANTGRUNTS|.\ |PANTGRUNTS.MultiRecipients| | Boolean, |true| when there were mulitple recipients of a pantgrunt | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | TwoSided | An expression based on |ROLES|.\ |ROLES.Role| | Boolean, |true| when both the Actor and the Recipient were | + | | | pantgrunting to each other | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | CommID | |WATCHES|.\ |WATCHES.CommID| | The community identifier associated either with the follow or with | + | | | the ad-hoc recording of the pantgrunt | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | PG_CommID | |PANTGRUNTS|.\ |PANTGRUNTS.CommId| | The community identifier associated with the record of the | + | | | pantgrunt | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Source | |PANTGRUNTS|.\ |PANTGRUNTS.Source| | Code for the source of the pantgrunt information | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Notes | |EVENTS|.\ |EVENTS.Notes| | Textual notes on the pantgrunt event | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | EnteredBy | |PANTGRUNTS|.\ |PANTGRUNTS.EnteredBy| | Code for the person who extracted the pantgrunt information from | + | | | the written records | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Type | |WATCHES|.\ |WATCHES.Type| | Type, reporting whether or not the pantgrunt was observed during a | + | | | follow | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | WID | |WATCHES|.\ |WATCHES.WID| | Identifier of the related |WATCHES| row | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | EID | |EVENTS|.\ |EVENTS.EID| | Identifier of the related |EVENTS| and |PANTGRUNTS| rows | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Actor_PID | |ROLES|.\ |ROLES.PID| | Identifier of the |ROLES| row containing the individual performing | + | | | the pantgrunt, or mutually pantgrunting | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Recipient_PID | |ROLES|.\ |ROLES.PID| | Identifier of the |ROLES| row containig the individual receiving | + | | | the pantgrunt, or mutually pantgrunting | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + + +.. _PANTGRUNTS_VIEW_Operations_Allowed: + +Operations Allowed +`````````````````` + +None. + -- 2.34.1