From f533215a8f5ba2b4f71a0c0286220069326fb4ce Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Thu, 4 Jun 2026 18:21:33 +0000 Subject: [PATCH] Remove ARRIVALS.ArID and use EID as the primary key ArID is a confusing affection and anti-performant. --- db/schemas/lib/triggers/create/arrivals.m4 | 37 ++++----- .../lib/triggers/create/biography_data.m4 | 49 ++++++----- db/schemas/lib/triggers/create/follows.m4 | 33 ++++---- db/schemas/sokwedb/indexes/create/arrivals.m4 | 7 +- db/schemas/sokwedb/indexes/drop/arrivals.m4 | 6 +- db/schemas/sokwedb/tables/create/arrivals.m4 | 17 +++- doc/diagrams/events.svg | 82 ++++++++----------- doc/src/epilog.inc.m4 | 2 - doc/src/tables/arrivals.m4 | 13 +-- 9 files changed, 108 insertions(+), 138 deletions(-) diff --git a/db/schemas/lib/triggers/create/arrivals.m4 b/db/schemas/lib/triggers/create/arrivals.m4 index 63e5898..b2bb29c 100644 --- a/db/schemas/lib/triggers/create/arrivals.m4 +++ b/db/schemas/lib/triggers/create/arrivals.m4 @@ -1,4 +1,4 @@ -dnl Copyright (C) 2025 The Meme Factory, Inc. http://www.karlpinc.com/ +dnl Copyright (C) 2025, 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 @@ -32,16 +32,15 @@ CREATE OR REPLACE FUNCTION arrivals_func () BEGIN -- Function for arrivals insert and update triggers -- - -- AGPL_notice(` --', `2025', + -- AGPL_notice(` --', `2026', `The Meme Factory, Inc., www.karlpinc.com') IF TG_OP = 'UPDATE' THEN - cannot_change(`ARRIVALS', `ArID') + cannot_change(`ARRIVALS', `EID') END IF; -- The EVENTS.Behavior must be sdb_arrival - IF TG_OP = 'INSERT' - OR NEW.EID <> OLD.EID THEN + IF TG_OP = 'INSERT' THEN DECLARE -- EVENTS a_behavior events.behavior%TYPE; @@ -73,8 +72,8 @@ CREATE OR REPLACE FUNCTION arrivals_func () MESSAGE = 'Error on ' || TG_OP || ' of ARRIVALS' , DETAIL = 'Arrivals can only be related to an event with an' || ' EVENTS.Behavior value of (sdb_arrival)' - || ': Key (ArID = (' - || NEW.arid + || ': Key (EID = (' + || NEW.eid || '): Value (Cycle) = (' || NEW.cycle || '): Value (DataSource) = (' @@ -160,8 +159,8 @@ CREATE OR REPLACE FUNCTION arrivals_func () , DETAIL = 'A female cannot be assigned the' || ' (sdb_male_swelling) code as a sexual cycle' || ' state' - || ': Key (ArID = (' - || NEW.arid + || ': Key (EID = (' + || NEW.eid || '): Value (Cycle) = (' || NEW.cycle || '): Key (ROLES.PID) = (' @@ -212,8 +211,8 @@ CREATE OR REPLACE FUNCTION arrivals_func () || ' be assigned the' || ' (sdb_adolescent_swelling) code as a sexual' || ' cycle state' - || ': Key (ArID = (' - || NEW.arid + || ': Key (EID = (' + || NEW.eid || '): Value (Cycle) = (' || NEW.cycle || '): Key (ROLES.PID) = (' @@ -260,8 +259,8 @@ CREATE OR REPLACE FUNCTION arrivals_func () || ' assigned the' || ' (sdb_adolescent_swelling) code as a sexual' || ' cycle state' - || ': Key (ArID = (' - || NEW.arid + || ': Key (EID = (' + || NEW.eid || '): Value (Cycle) = (' || NEW.cycle || '): Key (ROLES.PID) = (' @@ -311,8 +310,8 @@ CREATE OR REPLACE FUNCTION arrivals_func () || ' sdb_min_swelling_age_units old, cannot be' || ' assigned a cycle code that indicates' || ' sexual swelling' - || ': Key (ArID = (' - || NEW.arid + || ': Key (EID = (' + || NEW.eid || '): Value (Cycle) = (' || NEW.cycle || '): Key (ROLES.PID) = (' @@ -357,8 +356,8 @@ CREATE OR REPLACE FUNCTION arrivals_func () || ' sdb_max_swelling_age_units old, cannot be' || ' assigned a cycle code that indicates' || ' sexual swelling' - || ': Key (ArID = (' - || NEW.arid + || ': Key (EID = (' + || NEW.eid || '): Value (Cycle) = (' || NEW.cycle || '): Key (ROLES.PID) = (' @@ -402,8 +401,8 @@ CREATE OR REPLACE FUNCTION arrivals_func () , DETAIL = 'A non-female must be assigned the' || ' (sdb_male_swelling) code as a sexual' || ' cycle state' - || ': Key (ArID = (' - || NEW.arid + || ': Key (EID = (' + || NEW.eid || '): Value (Cycle) = (' || NEW.cycle || '): Key (ROLES.PID) = (' diff --git a/db/schemas/lib/triggers/create/biography_data.m4 b/db/schemas/lib/triggers/create/biography_data.m4 index f399e44..955da34 100644 --- a/db/schemas/lib/triggers/create/biography_data.m4 +++ b/db/schemas/lib/triggers/create/biography_data.m4 @@ -684,7 +684,6 @@ CREATE OR REPLACE FUNCTION biography_data_func () -- ARRIVALS DECLARE -- ARRIVALS - a_arid arrivals.arid%TYPE; a_cycle arrivals.cycle%TYPE; -- EVENTS a_eid events.eid%TYPE; @@ -711,11 +710,11 @@ CREATE OR REPLACE FUNCTION biography_data_func () -- wrong explaination for why the swelling code was rejected. SELECT roles.role , events.eid, events.behavior, events.start, events.stop - , arrivals.arid, arrivals.cycle + , arrivals.cycle , follows.fid, follows.focal, follows.date INTO a_role , a_eid , a_behavior , a_start , a_stop - , a_arid , a_cycle + , a_cycle , a_fid , a_focal , a_date FROM roles JOIN events ON (events.eid = roles.eid) @@ -750,8 +749,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_start || '), Value (EVENTS.Stop) = (' || a_stop - || '): Key (ARRIVALS.ArID) = (' - || a_arid + || '): Key (ARRIVALS.EID) = (' + || a_eid || '), Value (ARRIVALS.Cycle) = (' || a_cycle || '): Key (FOLLOWS.FID) = (' @@ -776,11 +775,11 @@ CREATE OR REPLACE FUNCTION biography_data_func () -- adolescent females. -- Can't be too young. SELECT roles.role , events.eid, events.behavior, events.start, events.stop - , arrivals.arid, arrivals.cycle + , arrivals.cycle , follows.fid, follows.focal, follows.date INTO a_role , a_eid , a_behavior , a_start , a_stop - , a_arid , a_cycle + , a_cycle , a_fid , a_focal , a_date FROM roles JOIN events ON (events.eid = roles.eid) @@ -823,8 +822,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_start || '), Value (EVENTS.Stop) = (' || a_stop - || '): Key (ARRIVALS.ArID) = (' - || a_arid + || '): Key (ARRIVALS.EID) = (' + || a_eid || '), Value (ARRIVALS.Cycle) = (' || a_cycle || '): Key (FOLLOWS.FID) = (' @@ -841,11 +840,11 @@ CREATE OR REPLACE FUNCTION biography_data_func () -- adolescent females. -- Can't be too old. SELECT roles.role , events.eid, events.behavior, events.start, events.stop - , arrivals.arid, arrivals.cycle + , arrivals.cycle , follows.fid, follows.focal, follows.date INTO a_role , a_eid , a_behavior , a_start , a_stop - , a_arid , a_cycle + , a_eid , a_cycle , a_fid , a_focal , a_date FROM roles JOIN events ON (events.eid = roles.eid) @@ -889,8 +888,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_start || '), Value (EVENTS.Stop) = (' || a_stop - || '): Key (ARRIVALS.ArID) = (' - || a_arid + || '): Key (ARRIVALS.EID) = (' + || a_eid || '), Value (ARRIVALS.Cycle) = (' || a_cycle || '): Key (FOLLOWS.FID) = (' @@ -911,11 +910,11 @@ CREATE OR REPLACE FUNCTION biography_data_func () -- Can't be too young. SELECT roles.role , events.eid, events.behavior, events.start, events.stop - , arrivals.arid, arrivals.cycle + , arrivals.cycle , follows.fid, follows.focal, follows.date INTO a_role , a_eid , a_behavior , a_start , a_stop - , a_arid , a_cycle + , a_eid , a_cycle , a_fid , a_focal , a_date FROM roles JOIN events ON (events.eid = roles.eid) @@ -959,8 +958,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_start || '), Value (EVENTS.Stop) = (' || a_stop - || '): Key (ARRIVALS.ArID) = (' - || a_arid + || '): Key (ARRIVALS.EID) = (' + || a_eid || '), Value (ARRIVALS.Cycle) = (' || a_cycle || '): Key (FOLLOWS.FID) = (' @@ -976,11 +975,11 @@ CREATE OR REPLACE FUNCTION biography_data_func () -- Can't be too old and still have a sexual swelling. SELECT roles.role , events.eid, events.behavior, events.start, events.stop - , arrivals.arid, arrivals.cycle + , arrivals.cycle , follows.fid, follows.focal, follows.date INTO a_role , a_eid , a_behavior , a_start , a_stop - , a_arid , a_cycle + , a_cycle , a_fid , a_focal , a_date FROM roles JOIN events ON (events.eid = roles.eid) @@ -1025,8 +1024,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_start || '), Value (EVENTS.Stop) = (' || a_stop - || '): Key (ARRIVALS.ArID) = (' - || a_arid + || '): Key (ARRIVALS.EID) = (' + || a_eid || '), Value (ARRIVALS.Cycle) = (' || a_cycle || '): Key (FOLLOWS.FID) = (' @@ -1047,11 +1046,11 @@ CREATE OR REPLACE FUNCTION biography_data_func () IF NEW.sex <> OLD.sex THEN SELECT roles.role , events.eid, events.behavior, events.start, events.stop - , arrivals.arid, arrivals.cycle + , arrivals.cycle , follows.fid, follows.focal, follows.date INTO a_role , a_eid , a_behavior , a_start , a_stop - , a_arid , a_cycle + , a_cycle , a_fid , a_focal , a_date FROM roles JOIN events ON (events.eid = roles.eid) @@ -1087,8 +1086,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_start || '), Value (EVENTS.Stop) = (' || a_stop - || '): Key (ARRIVALS.ArID) = (' - || a_arid + || '): Key (ARRIVALS.EID) = (' + || a_eid || '), Value (ARRIVALS.Cycle) = (' || a_cycle || '): Key (FOLLOWS.FID) = (' diff --git a/db/schemas/lib/triggers/create/follows.m4 b/db/schemas/lib/triggers/create/follows.m4 index 8567c21..b846791 100644 --- a/db/schemas/lib/triggers/create/follows.m4 +++ b/db/schemas/lib/triggers/create/follows.m4 @@ -118,7 +118,6 @@ CREATE OR REPLACE FUNCTION follows_func () a_entrydate biography_data.entrydate%TYPE; a_departdate biography_data.departdate%TYPE; -- ARRIVALS - a_arid arrivals.arid%TYPE; a_cycle arrivals.cycle%TYPE; BEGIN @@ -234,11 +233,11 @@ CREATE OR REPLACE FUNCTION follows_func () SELECT events.eid, events.behavior, events.start, events.stop , roles.pid, roles.role, roles.participant , biography_data.sex, biography_data.birthdate - , arrivals.arid, arrivals.cycle + , arrivals.cycle INTO a_eid , a_behavior , a_start , a_stop , a_pid , a_role , a_participant , a_sex , a_birthdate - , a_arid , a_cycle + , a_cycle FROM events JOIN roles ON (roles.eid = events.eid) @@ -292,8 +291,8 @@ CREATE OR REPLACE FUNCTION follows_func () || a_sex || '): Value (BIOGRAPHY_DATA.BirthDate) = (' || a_birthdate - || '): Key (ARRIVALS.ArID) = (' - || a_arid + || '): Key (ARRIVALS.EID) = (' + || a_eid || '), Value (ARRIVALS.Cycle) = (' || a_cycle || ')' @@ -305,11 +304,11 @@ CREATE OR REPLACE FUNCTION follows_func () SELECT events.eid, events.behavior, events.start, events.stop , roles.pid, roles.role, roles.participant , biography_data.sex, biography_data.birthdate - , arrivals.arid, arrivals.cycle + , arrivals.cycle INTO a_eid , a_behavior , a_start , a_stop , a_pid , a_role , a_participant , a_sex , a_birthdate - , a_arid , a_cycle + , a_cycle FROM events JOIN roles ON (roles.eid = events.eid) @@ -364,8 +363,8 @@ CREATE OR REPLACE FUNCTION follows_func () || a_sex || '): Value (BIOGRAPHY_DATA.BirthDate) = (' || a_birthdate - || '): Key (ARRIVALS.ArID) = (' - || a_arid + || '): Key (ARRIVALS.EID) = (' + || a_eid || '), Value (ARRIVALS.Cycle) = (' || a_cycle || ')' @@ -381,11 +380,11 @@ CREATE OR REPLACE FUNCTION follows_func () SELECT events.eid, events.behavior, events.start, events.stop , roles.pid, roles.role, roles.participant , biography_data.sex, biography_data.birthdate - , arrivals.arid, arrivals.cycle + , arrivals.cycle INTO a_eid , a_behavior , a_start , a_stop , a_pid , a_role , a_participant , a_sex , a_birthdate - , a_arid , a_cycle + , a_cycle FROM events JOIN roles ON (roles.eid = events.eid) @@ -441,8 +440,8 @@ CREATE OR REPLACE FUNCTION follows_func () || a_sex || '): Value (BIOGRAPHY_DATA.BirthDate) = (' || a_birthdate - || '): Key (ARRIVALS.ArID) = (' - || a_arid + || '): Key (ARRIVALS.EID) = (' + || a_eid || '), Value (ARRIVALS.Cycle) = (' || a_cycle || ')' @@ -458,11 +457,11 @@ CREATE OR REPLACE FUNCTION follows_func () SELECT events.eid, events.behavior, events.start, events.stop , roles.pid, roles.role, roles.participant , biography_data.sex, biography_data.birthdate - , arrivals.arid, arrivals.cycle + , arrivals.cycle INTO a_eid , a_behavior , a_start , a_stop , a_pid , a_role , a_participant , a_sex , a_birthdate - , a_arid , a_cycle + , a_cycle FROM events JOIN roles ON (roles.eid = events.eid) @@ -519,8 +518,8 @@ CREATE OR REPLACE FUNCTION follows_func () || a_sex || '): Value (BIOGRAPHY_DATA.BirthDate) = (' || a_birthdate - || '): Key (ARRIVALS.ArID) = (' - || a_arid + || '): Key (ARRIVALS.EID) = (' + || a_eid || '), Value (ARRIVALS.Cycle) = (' || a_cycle || ')' diff --git a/db/schemas/sokwedb/indexes/create/arrivals.m4 b/db/schemas/sokwedb/indexes/create/arrivals.m4 index 2482038..206b9b9 100644 --- a/db/schemas/sokwedb/indexes/create/arrivals.m4 +++ b/db/schemas/sokwedb/indexes/create/arrivals.m4 @@ -1,4 +1,4 @@ -dnl Copyright (C) 2025 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl Copyright (C) 2025, 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 @@ -21,11 +21,6 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl -CREATE UNIQUE INDEX IF NOT EXISTS - "ARRIVALS has, at most, a 1-to-1 relationship with EVENTS" - ON arrivals - (eid); - CREATE INDEX IF NOT EXISTS arrivals_cycle ON arrivals (cycle); CREATE INDEX IF NOT EXISTS arrivals_datasource ON arrivals diff --git a/db/schemas/sokwedb/indexes/drop/arrivals.m4 b/db/schemas/sokwedb/indexes/drop/arrivals.m4 index a9cd325..6c3e007 100644 --- a/db/schemas/sokwedb/indexes/drop/arrivals.m4 +++ b/db/schemas/sokwedb/indexes/drop/arrivals.m4 @@ -1,4 +1,4 @@ -dnl Copyright (C) 2025 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl Copyright (C) 2025, 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 @@ -21,9 +21,5 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl -DROP INDEX IF EXISTS - "ARRIVALS has, at most, a 1-to-1 relationship with EVENTS"; - - DROP INDEX IF EXISTS arrivals_cycle; DROP INDEX IF EXISTS arrivals_datasource; diff --git a/db/schemas/sokwedb/tables/create/arrivals.m4 b/db/schemas/sokwedb/tables/create/arrivals.m4 index 1d3b4ec..0756ace 100644 --- a/db/schemas/sokwedb/tables/create/arrivals.m4 +++ b/db/schemas/sokwedb/tables/create/arrivals.m4 @@ -1,4 +1,4 @@ -dnl Copyright (C) 2025 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl Copyright (C) 2025, 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 @@ -24,8 +24,7 @@ include(`grants.m4')dnl dnl CREATE TABLE arrivals ( - key_column(`ARRIVALS', `ArID', INTEGER) - ,eid INTEGER NOT NULL + eid INTEGER NOT NULL REFERENCES events ,seq INTEGER ,neststart BOOLEAN NOT NULL @@ -36,4 +35,16 @@ CREATE TABLE arrivals ( REFERENCES arrival_sources ); +-- Give the primary key index a useful name so the user has a clue +-- when they violate it. +CREATE UNIQUE INDEX + "ARRIVALS has, at most, a 1-to-1 relationship with EVENTS" + ON arrivals + (eid); + +ALTER TABLE arrivals + ADD PRIMARY KEY + USING INDEX + "ARRIVALS has, at most, a 1-to-1 relationship with EVENTS"; + grant_priv(`ARRIVALS') diff --git a/doc/diagrams/events.svg b/doc/diagrams/events.svg index c648687..ea05465 100644 --- a/doc/diagrams/events.svg +++ b/doc/diagrams/events.svg @@ -821,8 +821,8 @@ inkscape:pageopacity="0.0" inkscape:pageshadow="2" inkscape:zoom="3.6096384" - inkscape:cx="347.68026" - inkscape:cy="681.64723" + inkscape:cx="244.89988" + inkscape:cy="570.8328" inkscape:document-units="mm" inkscape:current-layer="layer3" inkscape:document-rotation="0" @@ -1534,39 +1534,21 @@ xml:space="preserve" style="font-style:normal;font-variant:normal;font-weight:bold;font-stretch:normal;font-size:2.81672px;line-height:125%;font-family:Sans;-inkscape-font-specification:'Sans, Bold';font-variant-ligatures:normal;font-variant-caps:normal;font-variant-numeric:normal;font-variant-east-asian:normal;text-align:start;letter-spacing:0px;word-spacing:0px;writing-mode:lr-tb;text-anchor:start;fill:#000000;fill-opacity:1;stroke:none;stroke-width:0.264067px;stroke-linecap:butt;stroke-linejoin:miter;stroke-opacity:1" x="29.438749" - y="136.66052" + y="140.35706" id="text2064-5" transform="scale(0.99793288,1.0020714)">ARRIVALS - ArID - + width="30.600002" + height="3.600003" + x="28.799999" + y="137.7" /> + width="30.6" + height="3.5999999" + x="28.799999" + y="148.5" /> + width="30.600002" + height="3.5999999" + x="28.799999" + y="144.89999" /> + width="30.599998" + height="3.6000009" + x="77.243652" + y="89.741661" /> + width="30.600002" + height="3.600003" + x="28.799999" + y="155.7" /> + width="30.600002" + height="3.5999939" + x="28.799999" + y="159.3" /> + width="30.6" + height="3.599997" + x="28.799999" + y="141.3" /> ` -.. |ARRIVALS.ArID| - replace:: :ref:`ArID ` .. |ARRIVALS.EID| replace:: :ref:`EID ` .. |ARRIVALS.Seq| diff --git a/doc/src/tables/arrivals.m4 b/doc/src/tables/arrivals.m4 index c2cd61a..04e1524 100644 --- a/doc/src/tables/arrivals.m4 +++ b/doc/src/tables/arrivals.m4 @@ -65,22 +65,13 @@ table. :depth: 2 -.. _ARRIVALS.ArID: - -ArID (Arrival ID) -````````````````` - -.. |ARRIVALS.ArID_summary| replace:: |idcol| - -|ARRIVALS.ArID_summary| |notnull| - - .. _ARRIVALS.EID: EID (Event ID) `````````````` .. |ARRIVALS.EID_summary| replace:: + |idcol| The |EVENTS|.\ |EVENTS.EID| identifying the arrival event that is related to the arrival and departure. @@ -170,4 +161,4 @@ DataSource (Data Source) document or procedure that was used to determine the arrival and departure information. -|ARRIVALS.ArID_summary| |notnull| +|ARRIVALS.DataSource_summary| |notnull| -- 2.34.1