From 80e5a9c0dc8b10816c98d0b17462b2ebab52de9e Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Sat, 20 Jan 2024 19:19:30 +0000 Subject: [PATCH] Add LIMIT to SELECT INTO, and ORDER BY for consistant errors results --- db/schemas/lib/triggers/create/biography.m4 | 26 ++++++++++++++------ db/schemas/lib/triggers/create/comm_membs.m4 | 12 ++++++--- 2 files changed, 26 insertions(+), 12 deletions(-) diff --git a/db/schemas/lib/triggers/create/biography.m4 b/db/schemas/lib/triggers/create/biography.m4 index 404ecf6..9b6f221 100644 --- a/db/schemas/lib/triggers/create/biography.m4 +++ b/db/schemas/lib/triggers/create/biography.m4 @@ -1,4 +1,4 @@ -dnl Copyright (C) 2023 The Meme Factory, Inc. http://www.karlpinc.com/ +dnl Copyright (C) 2023, 2024 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 @@ -94,7 +94,8 @@ define({check_firstborn}, {dnl INTO fb_animid, fb_animname, fb_birthdate FROM biography WHERE biography.momid = $1.momid - ORDER BY biography.birthdate; + ORDER BY biography.birthdate + LIMIT 1; -- Find a random sibling with a bad FirstBorn -- This could be the row that's being inserted or updated; -- we don't bother with a separate test/error message. @@ -108,7 +109,8 @@ define({check_firstborn}, {dnl WHERE biography.momid = $1.momid AND biography.birthdate > fb_birthdate AND biography.firstborn <> 'sdb_not_firstborn' - ORDER BY biography.birthdate; + ORDER BY biography.birthdate + LIMIT 1; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'All but the youngest sibling (or siblings' @@ -162,7 +164,7 @@ CREATE OR REPLACE FUNCTION biography_func () BEGIN -- Function for biography insert and update triggers -- - -- AGPL_notice(` --', `2023', + -- AGPL_notice(` --', `2023, 2024', `The Meme Factory, Inc., www.karlpinc.com') IF TG_OP = 'UPDATE' THEN @@ -213,7 +215,9 @@ CREATE OR REPLACE FUNCTION biography_func () AND biography.bdmax > NEW.bdbin - 'sdb_min_m_birth sdb_min_m_birth_units' - ::interval); + ::interval) + ORDER BY biography.birthdate DESC + LIMIT 1; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'An individual cannot have offspring when too young' @@ -254,7 +258,9 @@ CREATE OR REPLACE FUNCTION biography_func () AND biography.animid = NEW.dadid AND NEW.bdmax < biography.bdmin - + 'sdb_min_m_birth sdb_min_m_birth_units'::interval); + + 'sdb_min_m_birth sdb_min_m_birth_units'::interval) + ORDER BY biography.sex + LIMIT 1; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'An individual cannot have offspring when too young' @@ -314,7 +320,9 @@ CREATE OR REPLACE FUNCTION biography_func () INTO startdate, commmid FROM comm_membs WHERE comm_membs.animid = NEW.animid - AND comm_membs.startdate < NEW.entrydate; + AND comm_membs.startdate < NEW.entrydate + ORDER BY comm_membs.startdate + LIMIT 1; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'An individual may not be placed in a community before' @@ -343,7 +351,9 @@ CREATE OR REPLACE FUNCTION biography_func () INTO enddate, commmid FROM comm_membs WHERE comm_membs.animid = NEW.animid - AND NEW.departdate < comm_membs.enddate; + AND NEW.departdate < comm_membs.enddate + ORDER BY comm_membs.enddate DESC + LIMIT 1; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'An individual may not be placed in a community after' diff --git a/db/schemas/lib/triggers/create/comm_membs.m4 b/db/schemas/lib/triggers/create/comm_membs.m4 index 4ed56b6..9b8dcf6 100644 --- a/db/schemas/lib/triggers/create/comm_membs.m4 +++ b/db/schemas/lib/triggers/create/comm_membs.m4 @@ -1,4 +1,4 @@ -dnl Copyright (C) 2023 The Meme Factory, Inc. http://www.karlpinc.com/ +dnl Copyright (C) 2023, 2024 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,7 +32,7 @@ CREATE OR REPLACE FUNCTION comm_membs_func () BEGIN -- Function for comm_membs insert and update triggers -- - -- AGPL_notice(` --', `2023', + -- AGPL_notice(` --', `2023, 2024', `The Meme Factory, Inc., www.karlpinc.com') -- An individual may not be recorded in more than one community on @@ -46,7 +46,9 @@ CREATE OR REPLACE FUNCTION comm_membs_func () WHERE comm_membs.animid = NEW.animid AND comm_membs.startdate <= NEW.enddate AND comm_membs.enddate >= NEW.startdate - AND comm_membs.commmid <> NEW.commmid; + AND comm_membs.commmid <> NEW.commmid + ORDER BY comm_membs.startdate + LIMIT 1; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'An individual may not be in more than one community' @@ -86,7 +88,9 @@ CREATE OR REPLACE FUNCTION comm_membs_func () AND comm_membs.animid = NEW.animid AND comm_membs.commid = NEW.commid AND (comm_membs.enddate = NEW.startdate - 1 - OR comm_membs.startdate = NEW.enddate + 1); + OR comm_membs.startdate = NEW.enddate + 1) + ORDER BY comm_membs.startdate + LIMIT 1; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Two COMM_MEMBS rows may not be used to place a' -- 2.34.1