From 35a463686e541b503242c3495111d4b73603e3d3 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Fri, 3 Jul 2026 00:32:08 +0000 Subject: [PATCH] Adjust PANTGRUNTS_VIEW to use DYADS view -- add Event_Notes column --- .../lib/triggers/create/pantgrunts_view.m4 | 525 ++++++++++++------ .../sokwedb/views/create/pantgrunts_view.m4 | 63 +-- doc/src/views/pantgrunts_view.m4 | 55 +- 3 files changed, 408 insertions(+), 235 deletions(-) diff --git a/db/schemas/lib/triggers/create/pantgrunts_view.m4 b/db/schemas/lib/triggers/create/pantgrunts_view.m4 index 5eebe53..d178eee 100644 --- a/db/schemas/lib/triggers/create/pantgrunts_view.m4 +++ b/db/schemas/lib/triggers/create/pantgrunts_view.m4 @@ -60,6 +60,8 @@ define({_show_row}, {'Value (Date) = (' || textualize(`NEW.enteredby') || '), Value (Notes) = (' || textualize(`NEW.notes') + || '), Value (Event_Notes) = (' + || textualize(`NEW.event_notes') || '), Value (Type) = (' || textualize(`NEW.type') || '), Key (WID) = (' @@ -82,14 +84,22 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () 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_notes events.notes%TYPE; + target_behavior events.behavior%TYPE; + target_time events.start%TYPE; + target_event_notes events.notes%TYPE; target_actor_pid roles.pid%TYPE; target_recipient_pid roles.pid%TYPE; + target_twosided BOOLEAN; + BEGIN -- Function for pantgrunts_view instead of insert trigger -- @@ -102,204 +112,350 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () -- consistent no matter the interface used to change table content. -- - -- Initial validation of data supplied + -- The reason why we go through all this searching existing db + -- content, in various ways, is to get a WATCHES.Type value + -- to send to the DYADS view. We want to do this in a way + -- that's consistent with the way DYADS searches. -- - - -- 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. + -- In this particular case the Start and Stop values are guarenteed + -- to be identical, and the default watch type is 'sdb_pantgrunt'. + -- And Certainty is guarenteed to be sdb_identity_certain. So this + -- code is aware of that and does not even mention Certainty to the + -- user. + -- + -- If the are more views written that model off this view, the + -- best approach is to probably write a general-purpose function + -- that discovers the WATCHES.Type and have each view call that + -- function. This would make writing such views trivial: call the + -- function and insert into DYADS. Unfortunately, writing such + -- a function is currently out-of-scope. -- - -- Find the WATCHES row to use, or create one + -- Initial validation of data supplied -- - - 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 + IF NEW.eid IS NULL THEN + + -- Use other data values to find the EID to which to relate the + -- new ROLES rows. + + -- 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 = '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' + , DETAIL = 'The supplied Time value may not be NULL' + || ' when EID is NULL' || ': ' || _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(); + target_time := NEW.time; + + IF NEW.wid IS NULL THEN + -- Use Date, Focal, and Time 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 PANTGRUNTS_VIEW' + , 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 PANTGRUNTS_VIEW' + , DETAIL = 'The supplied Focal 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.notes, obs.event_notes + INTO target_wid, target_date, target_focal, target_type, target_commid + , target_notes, target_event_notes + FROM obs + WHERE obs.focal = NEW.focal + AND obs.date = NEW.date + AND obs.behavior = 'sdb_pg_event' + AND obs.start = NEW.time + AND obs.stop = NEW.time; + +-- Validation is already done by OBS. Leaving this comment on +-- the chance there's some utility if and when this code +-- is abstracted into a function. +-- (CAUTION: textualiz`'e() and _show_ro`'w() macros are turned off.) +-- +-- IF FOUND THEN +-- -- Validate supplied values against db values +-- IF (NEW.type IS NOT NULL +-- AND NEW.type <> target_type) +-- OR (NEW.commid IS NOT NULL +-- AND NEW.commid <> target_commid) +-- 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 PANTGRUNTS_VIEW' +-- , DETAIL = 'The values in the database for the OBS' +-- ||' row with (OBS.WID) = (' +-- || textuali`'ze(`NEW.wid') +-- || ') and (OBS.Behavior = (' +-- || 'sdb_pg_event' +-- || ') and (OBS.Start = (' +-- || textuali`'ze(`NEW.time') +-- || ') and (OBS.Stop = (' +-- || textuali`'ze(`NEW.time') +-- || '), 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 (Notes) = (' +-- || target_notes +-- || '), Value (Event_Notes) = (' +-- || target_event_notes +-- || ') do not match the values supplied to the INSERT' +-- || ': ' +-- || _show_ro`'w(); +-- END IF; +-- ELSE + IF NOT FOUND THEN + -- No such OBS row, find a target_type + IF NEW.type IS NULL THEN + SELECT watches.type + INTO 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 NOT FOUND THEN + -- When a WATCHES row must be created then there must + -- not be an existing follow, so use the pantgrunt Type. + target_type := 'sdb_pantgrunt'; + END IF; + ELSE + -- Use the user's chosen type and let the errors fly as they may. + target_type := NEW.type; + END IF; + END IF; + ELSE -- NEW.wid IS NOT NULL + target_wid := NEW.wid; + + -- Use WID and Time to query OBS + SELECT obs.wid , obs.date , obs.focal , obs.type , obs.commid + , obs.eid + , obs.notes, obs.event_notes + INTO target_wid, target_date, target_focal, target_type, target_commid + , target_eid + , target_notes, target_event_notes + FROM obs + WHERE obs.wid = NEW.wid + AND obs.behavior = 'sdb_pg_event' + AND obs.start = NEW.time + AND obs.stop = NEW.time; + +-- Validation is already done by OBS. Leaving this comment on +-- the chance there's some utility if and when this code +-- is abstracted into a function. +-- (CAUTION: textualiz`'e() and _show_ro`'w() macros are turned off.) +-- +-- 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.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 PANTGRUNTS_VIEW' +-- , DETAIL = 'The values in the database for the OBS' +-- ||' row with (OBS.WID) = (' +-- || NEW.wid +-- || ') and (OBS.Behavior = (' +-- || 'sdb_pg_event' +-- || ') and (OBS.Start = (' +-- || textuali`'ze(`NEW.time') +-- || ') and (OBS.Stop = (' +-- || textuali`'ze(`NEW.time') +-- || '), 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 (Notes) = (' +-- || target_notes +-- || '), Value (Event_Notes) = (' +-- || target_event_notes +-- || ') do not match the values supplied to the INSERT' +-- || ': ' +-- || _show_ro`'w(); +-- END IF; +-- ELSE + IF NOT FOUND THEN + -- No such OBS row, find a target_type + IF NEW.type IS NULL THEN + SELECT watches.type + INTO target_type + FROM watches + WHERE watches.wid = NEW.wid; + + IF NOT FOUND THEN + -- When a WATCHES row must be created then there must + -- not be an existing follow, so use the pantgrunt Type. + target_type := 'sdb_pantgrunt'; + END IF; + ELSE + -- Use the user's chosen type and let the errors fly as they may. + target_type := NEW.type; + END IF; + END IF; 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 + 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.start + , obs.notes, obs.event_notes + INTO target_wid, target_date, target_focal, target_type, target_commid + , target_time + , target_notes, target_event_notes + FROM obs + WHERE obs.eid = NEW.eid + AND obs.behavior = 'sdb_pg_event'; + + 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 <> 'sdb_follow' + AND NEW.type <> 'sdb_pantgrunt') + OR (NEW.commid IS NOT NULL + AND NEW.commid <> target_commid) + OR (NEW.time IS NOT NULL + AND NEW.time <> target_time) + 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 PANTGRUNTS_VIEW' + , 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) = (' + || 'sdb_pg_event' + || '), Value (Start) = (' + || target_time + || '), Value (Stop) = (' + || target_time + || '), 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 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' + , DETAIL = 'The EVENTS row with (EVENTS.EID) = (' + || NEW.eid + || ') and (EVENTS.Behavior) = (' + || 'sdb_pg_event' + || ') does not exist' || ': ' || _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; + -- 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.) + -- Insert the DYADS row. -- - - -- 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; + WITH ins AS + (INSERT INTO dyads (wid , date , focal , type , commid + , eid , behavior , start , stop + , certainty + , actor_pid , actor + , recipient_pid , recipient + , twosided + , notes , event_notes) + VALUES ( NEW.wid, NEW.date, NEW.focal, target_type, NEW.commid + , NEW.eid, 'sdb_pg_event', NEW.time, NEW.time + , 'sdb_identity_certain' + , NEW.actor_pid, NEW.actor + , NEW.recipient_pid, NEW.recipient + , NEW.twosided + , NEW.notes, NEW.event_notes) + RETURNING wid, date, focal, type, commid + , eid, start + , actor_pid, actor + , recipient_pid, recipient + , twosided + , notes, event_notes) + SELECT ins.wid , ins.date , ins.focal , ins.type , ins.commid + , ins.eid , ins.start + , ins.actor_pid, ins.recipient_pid + , ins.twosided + , ins.notes , ins.event_notes + INTO target_wid, target_date, target_focal, target_type, target_commid + , target_eid, target_time + , target_actor_pid, target_recipient_pid + , target_twosided + , target_notes, target_event_notes + FROM ins; -- -- Insert into PANTGRUNTS @@ -317,12 +473,23 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () ,NEW.source ,NEW.enteredby); - + -- 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.time := target_time; 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; + -- We know all the PANTGRUNTS columns have values. + RETURN NEW; END; $$; diff --git a/db/schemas/sokwedb/views/create/pantgrunts_view.m4 b/db/schemas/sokwedb/views/create/pantgrunts_view.m4 index cf73828..ed6848c 100644 --- a/db/schemas/sokwedb/views/create/pantgrunts_view.m4 +++ b/db/schemas/sokwedb/views/create/pantgrunts_view.m4 @@ -37,6 +37,7 @@ CREATE OR REPLACE VIEW pantgrunts_view ( ,source ,enteredby ,notes + ,event_notes -- Administrative information ,type ,wid @@ -45,58 +46,28 @@ CREATE OR REPLACE VIEW pantgrunts_view ( ,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 + dyads.date AS date + ,dyads.focal AS focal + ,dyads.start AS time + ,dyads.actor AS actor + ,dyads.recipient 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 + ,dyads.twosided AS twosided + ,dyads.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) + ,dyads.notes AS notes + ,dyads.event_notes AS event_notes + ,dyads.type AS type + ,dyads.wid AS wid + ,dyads.eid AS eid + ,dyads.actor_pid AS actor_pid + ,dyads.recipient_pid AS recipient_pid + FROM dyads 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); + ON (pantgrunts.eid = dyads.eid); grant_priv(`PANTGRUNTS_VIEW') diff --git a/doc/src/views/pantgrunts_view.m4 b/doc/src/views/pantgrunts_view.m4 index 9bf02fc..58496e9 100644 --- a/doc/src/views/pantgrunts_view.m4 +++ b/doc/src/views/pantgrunts_view.m4 @@ -103,7 +103,10 @@ Columns of the PANTGRUNTS_VIEW View +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ | Source | |PANTGRUNTS|.\ |PANTGRUNTS.Source| | Code for the source of the pantgrunt information | +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ - | Notes | |EVENTS|.\ |EVENTS.Notes| | Textual notes on the pantgrunt event | + | Notes | |WATCHES|.\ |WATCHES.Notes| | Textual notes on the follow or the ad-hoc observation of the | + | | | pantgrunt | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Event_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 | @@ -129,16 +132,48 @@ Operations Allowed `````````````````` 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. + INSERTing a row into PANTGRUNTS_VIEW inserts a row into + |PANTGRUNTS| and two rows into into |ROLES|. + One row may be inserted into |EVENTS|, and one may be inserted into + |WATCHES|. + + The columns EID, WID, Focal, Date, and Time may all be used, in the + various combinations described below, to relate new |PANTGRUNTS| + and |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, Date, and Time are used + to query |OBS| to discover if a matching |EVENTS| row exists or if + there is no match and a new one must be inserted. + The Time column must be supplied in this case. + + If a WID is supplied it is used, along with Time, to match against + database content. + If a WID is not supplied, the Focal and Date columns must + be supplied. + Then they are used, along with Time, to match against database + content. + + If an existing |EVENTS| row matches, the new |PANTGRUNTS| row and + the new |ROLES| rows are related to the |EVENTS| row so discovered. + Otherwise they are related to the newly created |EVENTS| row. + + INSERTing into PANTGRUNTS_VIEW inserts a row into |DYADS|. + This means the rules regarding what data must be supplied when + INSERTing into |DYADS|, and when |DYADS| re-uses existing rows or + creates new rows, also apply to PANTGRUNTS_VIEW. + + 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. -- 2.34.1