From a32e6bcd9cfe18026820a717cb8db766aee8e45c Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Thu, 18 Jun 2026 17:46:35 +0000 Subject: [PATCH] Make PANTGRUNTS_VIEW insertable --- .../lib/triggers/create/pantgrunts_view.m4 | 307 +++++++++++++++++- doc/src/views/pantgrunts_view.m4 | 17 +- 2 files changed, 322 insertions(+), 2 deletions(-) diff --git a/db/schemas/lib/triggers/create/pantgrunts_view.m4 b/db/schemas/lib/triggers/create/pantgrunts_view.m4 index 2bfe64d..5eebe53 100644 --- a/db/schemas/lib/triggers/create/pantgrunts_view.m4 +++ b/db/schemas/lib/triggers/create/pantgrunts_view.m4 @@ -15,6 +15,10 @@ dnl along with this program. If not, see . dnl dnl Triggers for the pantgrunts_view view dnl +dnl It would be nice to allow update and delete. But at this time +dnl there is not budget to do the work, which could involve a lot of checking +dnl of supplied values against existing database values. +dnl dnl Karl O. Pinc dnl m4 includes @@ -22,11 +26,312 @@ 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}, {'Value (Date) = (' + || textualize(`NEW.date') + || '), Value (Focal) = (' + || textualize(`NEW.focal') + || '), Value (Time) = (' + || textualize(`NEW.time') + || '), Value (Actor) = (' + || textualize(`NEW.actor') + || '), Value (Recipient) = (' + || textualize(`NEW.recipient') + || '), Value (MultiActors) = (' + || textualize(`NEW.multiactors') + || '), Value (MultiRecipients) = (' + || textualize(`NEW.multirecipients') + || '), Value (TwoSided) = (' + || textualize(`NEW.TwoSided') + || '), Value (CommID) = (' + || textualize(`NEW.commid') + || '), Value (PG_CommID) = (' + || textualize(`NEW.pg_commid') + || '), Value (Source) = (' + || textualize(`NEW.source') + || '), Value (EnteredBy) = (' + || textualize(`NEW.enteredby') + || '), Value (Notes) = (' + || textualize(`NEW.notes') + || '), Value (Type) = (' + || textualize(`NEW.type') + || '), Key (WID) = (' + || textualize(`NEW.wid') + || '), Key (EID) = (' + || textualize(`NEW.eid') + || '), Key (Actor_PID) = (' + || textualize(`NEW.actor_pid') + || '), Key (Recipient_PID) = (' + || textualize(`NEW.recipient_pid') + || ')'dnl +}) +changequote(`,') + +RAISE INFO 'pantgrunts_view_insert_func'; +CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + DECLARE + target_wid watches.wid%TYPE; + target_type watches.type%TYPE; + + target_eid events.eid%TYPE; + target_notes events.notes%TYPE; + + target_actor_pid roles.pid%TYPE; + target_recipient_pid roles.pid%TYPE; + + BEGIN + -- Function for pantgrunts_view 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 + -- + + -- Date must be supplied - to lookup WATCHES + IF NEW.date IS NULL THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into PANTGRUNTS_VIEW' + , DETAIL = 'The supplied Date value may not be 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 PANTGRUNTS_VIEW' + , DETAIL = 'The supplied Focal value may not be NULL' + || ': ' + || _show_row(); + END IF; + + -- Time must be supplied - to lookup EVENTS + IF NEW.time IS NULL THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into PANTGRUNTS_VIEW' + , DETAIL = 'The supplied Time value may not be NULL' + || ': ' + || _show_row(); + END IF; + + -- The requirement that there be non-NULL data in the rest of the columns + -- is checked by the underlying tables. + + -- + -- Find the WATCHES row to use, or create one + -- + + SELECT watches.wid, watches.type + INTO target_wid , target_type + FROM watches + WHERE watches.focal = NEW.focal + AND watches.date = NEW.date + AND (watches.type = 'sdb_follow' + OR watches.type = 'sdb_pantgrunt'); + + IF FOUND THEN + IF (NEW.wid IS NOT NULL + AND NEW.wid <> target_wid) + OR (NEW.type IS NOT NULL + AND NEW.type <> target_type) THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into PANTGRUNTS_VIEW' + , DETAIL = 'One or both of the values in the database, (WID) = (' + || target_wid + || ') and (Type) = (' + || target_type + || '), do not match the values supplied to the INSERT' + || ': ' + || _show_row(); + END IF; + ELSE -- NOT FOUND, no existing WATCHES row + IF NEW.type IS NOT NULL + AND NEW.type <> 'sdb_pantgrunt' THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into PANTGRUNTS_VIEW' + , DETAIL = 'The value of the WATCHES.Type inserted into the,' + || ' database (Type) = (sdb_pantgrunt' + || '), does not match the value supplied to the INSERT' + || ': ' + || _show_row(); + END IF; + + INSERT INTO watches (focal, commid, date, type, notes) + VALUES (NEW.focal + ,NEW.commid + ,NEW.date + ,'sdb_pantgrunt' + ,''); + target_wid := CURRVAL('watches_wid_seq'); + target_type := 'sdb_pantgrunt'; + + IF NEW.wid IS NOT NULL + AND NEW.wid <> target_wid THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into PANTGRUNTS_VIEW' + , DETAIL = 'The value of the WATCHES.WID inserted into the database,' + || ' (WID) = (' + || target_wid + || '), does not match the value supplied to the INSERT' + || ': ' + || _show_row(); + END IF; + END IF; + + -- + -- Find or create the EVENTS row + -- + + SELECT events.eid, events.notes + INTO target_eid, target_notes + FROM events + WHERE events.wid = target_wid + AND events.start = NEW.time + AND events.behavior = 'sdb_pg_event'; + + IF FOUND + AND ((NEW.eid IS NOT NULL + AND NEW.eid <> target_eid) + OR (NEW.notes IS NOT NULL + AND NEW.notes <> target_notes)) THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into PANTGRUNTS_VIEW' + , DETAIL = 'The values in the database, (EID) = (' + || target_eid + || ') and (Notes) = (' + || target_notes + || '), do not match the values supplied to the INSERT' + || ': ' + || _show_row(); + END IF; + + IF NOT FOUND THEN + INSERT INTO events (wid, behavior, start, stop, certainty, notes) + VALUES (target_wid + ,'sdb_pg_event' + ,NEW.time + ,NEW.time + ,'sdb_identity_certain' + ,NEW.notes); + target_eid := CURRVAL('events_eid_seq'); + + IF NEW.eid IS NOT NULL + AND NEW.eid <> target_eid THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into PANTGRUNTS_VIEW' + , DETAIL = 'The value of the EVENTS.EID inserted into the database,' + || ' (EID) = (' + || target_eid + || '), does not match the value supplied to the INSERT' + || ': ' + || _show_row(); + END IF; + END IF; + + -- + -- 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_eid THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into PANTGRUNTS_VIEW' + , 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_eid THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into PANTGRUNTS_VIEW' + , 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; + + -- + -- Insert into PANTGRUNTS + -- + INSERT INTO pantgrunts (eid + , multiactors + , multirecipients + , commid + , source + , enteredby) + VALUES (target_eid + ,NEW.multiactors + ,NEW.multirecipients + ,NEW.pg_commid + ,NEW.source + ,NEW.enteredby); + + + NEW.wid := target_wid; + NEW.type := target_type; + NEW.eid := target_eid; + NEW.actor_pid := target_actor_pid; + NEW.recipient_pid := target_recipient_pid; + RETURN NEW; + END; +$$; + CREATE TRIGGER pantgrunts_view_insert_trigger INSTEAD OF INSERT ON pantgrunts_view FOR EACH ROW - EXECUTE PROCEDURE _error_immutable_view(); + EXECUTE PROCEDURE pantgrunts_view_insert_func(); CREATE TRIGGER pantgrunts_view_update_trigger INSTEAD OF UPDATE diff --git a/doc/src/views/pantgrunts_view.m4 b/doc/src/views/pantgrunts_view.m4 index 4c23eda..7f894b4 100644 --- a/doc/src/views/pantgrunts_view.m4 +++ b/doc/src/views/pantgrunts_view.m4 @@ -116,5 +116,20 @@ Columns in the PANTGRUNTS_VIEW View Operations Allowed `````````````````` -None. +INSERT + The Focal, Date, and Time columns must be supplied. + The WID, Type, EID, Actor_PID, and Recipient_PID columns will not + have their values inserted into new rows. + If values are supplied for these columns they must match the values + already existing in, or inserted into, the database. + + The |WATCHES| and |EVENTS| table's rows may already exist. + The data values supplied for these tables must match the values + that already exist. + +UPDATE + This operation is not allowed. + +DELETE + This operation is not allowed. -- 2.34.1