From 2618ee178ef5cdfc181db4387c18c3170876c5cd Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Wed, 17 Jun 2026 00:47:31 +0000 Subject: [PATCH] Restore FOOD_EVENTS.FEID as a unique id, use EID+Seq for primary key A food bout event has multiple FOOD_EVENTS rows related to it. --- db/schemas/lib/triggers/create/food_events.m4 | 28 +++--- .../sokwedb/indexes/create/food_events.m4 | 6 +- .../sokwedb/indexes/drop/food_events.m4 | 3 +- .../sokwedb/tables/create/food_events.m4 | 13 ++- doc/diagrams/events.svg | 85 ++++++++++++------- doc/src/epilog.inc.m4 | 2 + doc/src/tables/food_events.m4 | 10 +++ 7 files changed, 95 insertions(+), 52 deletions(-) diff --git a/db/schemas/lib/triggers/create/food_events.m4 b/db/schemas/lib/triggers/create/food_events.m4 index 232cf3d..4bb0a7c 100644 --- a/db/schemas/lib/triggers/create/food_events.m4 +++ b/db/schemas/lib/triggers/create/food_events.m4 @@ -72,8 +72,8 @@ CREATE OR REPLACE FUNCTION food_events_func () MESSAGE = 'Error on ' || TG_OP || ' of FOOD_EVENTS' , DETAIL = 'Food events can only be related to an event with an' || ' EVENTS.Behavior value of (sdb_food)' - || ': Key (EID = (' - || NEW.eid + || ': Key (FEID = (' + || NEW.feid || '): Value (FoodPart) = (' || NEW.foodpart || '): Value (FoodName) = (' @@ -121,6 +121,7 @@ CREATE OR REPLACE FUNCTION food_events_commit_func () sdb_function_set_search_path AS $$ DECLARE + a_feid food_events.feid%TYPE; a_foodpart food_events.foodpart%TYPE; a_foodname food_events.foodname%TYPE; a_seq food_events.seq%TYPE; @@ -135,9 +136,9 @@ CREATE OR REPLACE FUNCTION food_events_commit_func () IF TG_OP = 'INSERT' OR NEW.seq <> OLD.seq THEN - SELECT food_events.foodpart, food_events.foodname + SELECT food_events.feid, food_events.foodpart, food_events.foodname , food_events.seq - INTO a_foodpart , a_foodname + INTO a_feid , a_foodpart , a_foodname , a_seq FROM food_events WHERE food_events.eid = NEW.eid @@ -148,7 +149,7 @@ CREATE OR REPLACE FUNCTION food_events_commit_func () WHERE previous.eid = NEW.eid AND previous.seq = food_events.seq - 1) -- Produce a consistent error message - ORDER BY food_events.seq, food_events.eid; + ORDER BY food_events.seq, food_events.feid; IF FOUND THEN DECLARE @@ -187,8 +188,8 @@ CREATE OR REPLACE FUNCTION food_events_commit_func () || ' with a Seq of (' || a_seq - 1 || ') to pair with the following FOOD_EVENTS row:' - || ' Key (EID = (' - || NEW.eid + || ' Key (FEID = (' + || a_feid || '): Value (FoodPart) = (' || a_foodpart || '): Value (FoodName) = (' @@ -232,6 +233,7 @@ CREATE OR REPLACE FUNCTION food_events_delete_commit_func () sdb_function_set_search_path AS $$ DECLARE + a_feid food_events.feid%TYPE; a_foodpart food_events.foodpart%TYPE; a_foodname food_events.foodname%TYPE; a_seq food_events.seq%TYPE; @@ -243,8 +245,10 @@ CREATE OR REPLACE FUNCTION food_events_delete_commit_func () `The Meme Factory, Inc., www.karlpinc.com') -- The Seq can't have gaps - SELECT food_events.foodpart, food_events.foodname, food_events.seq - INTO a_foodpart , a_foodname , a_seq + SELECT food_events.feid, food_events.foodpart, food_events.foodname + , food_events.seq + INTO a_feid , a_foodpart , a_foodname + , a_seq FROM food_events WHERE food_events.eid = OLD.eid AND food_events.seq = OLD.seq + 1 @@ -254,7 +258,7 @@ CREATE OR REPLACE FUNCTION food_events_delete_commit_func () WHERE this_slot.eid = OLD.eid AND this_slot.seq = OLD.seq) -- Produce a consistent error message - ORDER BY food_events.eid; + ORDER BY food_events.feid; IF FOUND THEN DECLARE @@ -293,8 +297,8 @@ CREATE OR REPLACE FUNCTION food_events_delete_commit_func () || ' with a Seq of (' || a_seq - 1 || ') to pair with the following FOOD_EVENTS row:' - || ' Key (EID = (' - || OLD.eid + || ' Key (FEID = (' + || a_feid || '): Value (FoodPart) = (' || a_foodpart || '): Value (FoodName) = (' diff --git a/db/schemas/sokwedb/indexes/create/food_events.m4 b/db/schemas/sokwedb/indexes/create/food_events.m4 index b304dc7..63be159 100644 --- a/db/schemas/sokwedb/indexes/create/food_events.m4 +++ b/db/schemas/sokwedb/indexes/create/food_events.m4 @@ -21,10 +21,8 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl -CREATE UNIQUE INDEX IF NOT EXISTS - "On FOOD_EVENTS, EID + Seq must be unique" - ON food_events - (eid, seq); +CREATE UNIQUE INDEX IF NOT EXISTS food_events_feid ON food_events + (feid); CREATE INDEX IF NOT EXISTS food_events_foodpart ON food_events (foodpart); diff --git a/db/schemas/sokwedb/indexes/drop/food_events.m4 b/db/schemas/sokwedb/indexes/drop/food_events.m4 index 2b12287..7e263e9 100644 --- a/db/schemas/sokwedb/indexes/drop/food_events.m4 +++ b/db/schemas/sokwedb/indexes/drop/food_events.m4 @@ -21,8 +21,7 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl -DROP INDEX IF EXISTS - "On FOOD_EVENTS, EID + Seq must be unique"; +DROP INDEX IF EXISTS food_events_feid; DROP INDEX IF EXISTS food_events_foodpart; DROP INDEX IF EXISTS food_events_foodname; diff --git a/db/schemas/sokwedb/tables/create/food_events.m4 b/db/schemas/sokwedb/tables/create/food_events.m4 index f63eeab..e34b0cd 100644 --- a/db/schemas/sokwedb/tables/create/food_events.m4 +++ b/db/schemas/sokwedb/tables/create/food_events.m4 @@ -24,7 +24,8 @@ include(`grants.m4')dnl dnl CREATE TABLE food_events ( - eid INTEGER NOT NULL + feid INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL + ,eid INTEGER NOT NULL REFERENCES events ,foodpart TEXT NOT NULL CONSTRAINT "FoodPart must be a FOOD_PARTS.Part" @@ -42,4 +43,14 @@ CREATE TABLE food_events ( CHECK (seq <= sdb_max_foods) ); +CREATE UNIQUE INDEX IF NOT EXISTS + "On FOOD_EVENTS, EID + Seq must be unique" + ON food_events + (eid, seq); + +ALTER TABLE food_events + ADD PRIMARY KEY + USING INDEX + "On FOOD_EVENTS, EID + Seq must be unique"; + grant_priv(`FOOD_EVENTS') diff --git a/doc/diagrams/events.svg b/doc/diagrams/events.svg index c02ce99..1a81d3f 100644 --- a/doc/diagrams/events.svg +++ b/doc/diagrams/events.svg @@ -876,9 +876,9 @@ borderopacity="1.0" inkscape:pageopacity="0.0" inkscape:pageshadow="2" - inkscape:zoom="1.8048192" - inkscape:cx="468.19094" - inkscape:cy="244.62284" + inkscape:zoom="5.1047996" + inkscape:cx="195.1105" + inkscape:cy="688.27384" inkscape:document-units="mm" inkscape:current-layer="layer3" inkscape:document-rotation="0" @@ -2834,7 +2834,7 @@ inkscape:connector-curvature="0" /> FOOD_EVENTS + style="font-style:italic;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:2.81672008px;font-family:Sans;-inkscape-font-specification:'Sans, Italic';font-variant-ligatures:normal;font-variant-caps:normal;font-variant-numeric:normal;font-variant-east-asian:normal;stroke-width:0.264067px">FEID + width="30.6" + height="3.599997" + x="28.799999" + y="172.8" /> + FOOD_EVENTS + + width="30.600002" + height="3.6000061" + x="28.799999" + y="180" /> + width="30.6" + height="3.5999939" + x="28.799999" + y="183.60001" /> + width="30.600002" + height="3.600003" + x="28.799999" + y="187.2" /> + width="30.6" + height="3.599997" + x="28.799999" + y="190.8" /> + width="30.6" + height="3.6000061" + x="28.799999" + y="194.39999" /> + width="30.6" + height="3.6000061" + x="28.799999" + y="176.39999" /> ` .. |FOOD_EVENTS| replace:: :ref:`FOOD_EVENTS ` +.. |FOOD_EVENTS.FEID| replace:: + :ref:`FEID ` .. |FOOD_EVENTS.EID| replace:: :ref:`EID ` .. |FOOD_EVENTS.FoodPart| replace:: diff --git a/doc/src/tables/food_events.m4 b/doc/src/tables/food_events.m4 index ef36240..5e0b81b 100644 --- a/doc/src/tables/food_events.m4 +++ b/doc/src/tables/food_events.m4 @@ -75,6 +75,16 @@ table. :depth: 2 +.. _FOOD_EVENTS.FEID: + +FEID (Food Event IDentifier) +```````````````````````````` + +.. |FOOD_EVENTS.FEID_summary| replace:: |idcol| + +|FOOD_EVENTS.FEID_summary| |notnull| + + .. _FOOD_EVENTS.EID: EID (Event ID) -- 2.34.1