From d732a7dc29f864a7a0f00826cd6ab842fd18b6b3 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Mon, 6 Oct 2025 17:41:38 +0000 Subject: [PATCH] Change so sdb_male_swelling cannot be assigned to females --- db/schemas/lib/triggers/create/arrivals.m4 | 87 ++++++++----------- .../lib/triggers/create/biography_data.m4 | 12 +-- db/schemas/lib/triggers/create/follows.m4 | 67 -------------- doc/src/code_tables.m4 | 7 +- 4 files changed, 41 insertions(+), 132 deletions(-) diff --git a/db/schemas/lib/triggers/create/arrivals.m4 b/db/schemas/lib/triggers/create/arrivals.m4 index 2771010..aeabc78 100644 --- a/db/schemas/lib/triggers/create/arrivals.m4 +++ b/db/schemas/lib/triggers/create/arrivals.m4 @@ -88,52 +88,42 @@ CREATE OR REPLACE FUNCTION arrivals_func () IF a_sex = 'sdb_female' THEN IF NEW.code = 'sdb_male_swelling' THEN -- The sdb_male_swelling code (n/a) cannot be assigned to - -- non-juvenile females. - IF NEW.birthdate - >= (follows.date - - '1 year'::INTERVAL -- 'sdb_max_adolescent_age sdb_max_adolescent_age_units'::INTERVAL - ) THEN - -- The female is too old - RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'Error on ' || TG_OP || ' of ARRIVALS' - , DETAIL = 'A non-juvenile female, a female older than' - || ' sdb_max_adolescent_age' - || ' sdb_max_adolescent_age_units old, cannot be' - || ' assigned the' - || ' (sdb_male_swelling) code as a sexual cycle' - || ' state' - || ': Key (ArID = (' - || NEW.arid - || '): Value (Cycle) = (' - || NEW.cycle - || '): Key (ROLES.PID) = (' - || a_pid - || '), Value (ROLES.Role) = (' - || a_role - || ': Key (BIOGRAPHY_DATA.Animid) = (' - || a_animid - || '): Value (BIOGRAPHY_DATA.Sex) = (' - || a_sex - || '): Value (BIOGRAPHY_DATA.BirthDate) = (' - || a_birthdate - || '): Key (EVENTS.EID) = (' - || a_eid - || '): Value (EVENTS.Behavior) = (' - || a_behavior - || '), Value (EVENTS.Start) = (' - || a_start - || '), Value (EVENTS.Stop) = (' - || a_stop - || '): Key (FOLLOWS.FID) = (' - || a_fid - || '), Value (FOLLOWS.Focal) = (' - || a_focal - || '), Value (FOLLOWS.Date) = (' - || a_date - || ')' - , HINT = 'sdb_follow_age_hint'; - END IF; + -- females. + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of ARRIVALS' + , DETAIL = 'A female cannot be assigned the' + || ' (sdb_male_swelling) code as a sexual cycle' + || ' state' + || ': Key (ArID = (' + || NEW.arid + || '): Value (Cycle) = (' + || NEW.cycle + || '): Key (ROLES.PID) = (' + || a_pid + || '), Value (ROLES.Role) = (' + || a_role + || ': Key (BIOGRAPHY_DATA.Animid) = (' + || a_animid + || '): Value (BIOGRAPHY_DATA.Sex) = (' + || a_sex + || '): Value (BIOGRAPHY_DATA.BirthDate) = (' + || a_birthdate + || '): Key (EVENTS.EID) = (' + || a_eid + || '): Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || '): Key (FOLLOWS.FID) = (' + || a_fid + || '), Value (FOLLOWS.Focal) = (' + || a_focal + || '), Value (FOLLOWS.Date) = (' + || a_date + || ')' + , HINT = 'sdb_follow_age_hint'; ELSIF NEW.code = 'sdb_adolescent_swelling' THEN -- The sdb_adolescent_swelling code (U) can only be assigned to -- adolescent females. @@ -239,7 +229,7 @@ CREATE OR REPLACE FUNCTION arrivals_func () IF NEW.cycle <> 'sdb_male_swelling' THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on ' || TG_OP || ' of ARRIVALS' - , DETAIL = 'A non-female can only be assigned the' + , DETAIL = 'A non-female must be assigned the' || ' (sdb_male_swelling) code as a sexual' || ' cycle state' || ': Key (ArID = (' @@ -270,8 +260,7 @@ CREATE OR REPLACE FUNCTION arrivals_func () || a_focal || '), Value (FOLLOWS.Date) = (' || a_date - || ')' - , HINT = 'sdb_follow_age_hint'; + || ')'; END IF; END IF; END; diff --git a/db/schemas/lib/triggers/create/biography_data.m4 b/db/schemas/lib/triggers/create/biography_data.m4 index a89d4b9..d51c335 100644 --- a/db/schemas/lib/triggers/create/biography_data.m4 +++ b/db/schemas/lib/triggers/create/biography_data.m4 @@ -581,7 +581,7 @@ CREATE OR REPLACE FUNCTION biography_data_func () -- Check ARRIVALS.Cycle IF NEW.sex = 'sdb_female' THEN - -- The sdb_male_swelling code (n/a) cannot be assigned to non-juvenile + -- The sdb_male_swelling code (n/a) cannot be assigned to -- females. SELECT roles.role , events.eid, events.behavior, events.start, events.stop @@ -597,11 +597,6 @@ CREATE OR REPLACE FUNCTION biography_data_func () JOIN follows ON (follows.fid = events.fid) WHERE roles.participant = NEW.animid AND arrivals.cycle = 'sdb_male_swelling' - AND NEW.birthdate - >= (follows.date - - '1 sdb_max_adolescent_age_units'::INTERVAL -- 'sdb_max_adolescent_age sdb_max_adolescent_age_units'::INTERVAL - ) -- Produce a consistent error message ORDER BY follows.date, follows.fid , events.start, events.stop, events.eid @@ -609,10 +604,7 @@ CREATE OR REPLACE FUNCTION biography_data_func () IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' - , DETAIL = 'A non-juvenile female, a female older than' - || ' sdb_max_adolescent_age' - || ' sdb_max_adolescent_age_units old, cannot be' - || ' assigned the' + , DETAIL = 'A female cannot be assigned the' || ' (sdb_male_swelling) code as a sexual cycle state' || ': Key (Animid) = (' || NEW.animid diff --git a/db/schemas/lib/triggers/create/follows.m4 b/db/schemas/lib/triggers/create/follows.m4 index 64e8d29..8ec0c5e 100644 --- a/db/schemas/lib/triggers/create/follows.m4 +++ b/db/schemas/lib/triggers/create/follows.m4 @@ -227,73 +227,6 @@ CREATE OR REPLACE FUNCTION follows_func () -- Validate the ARRIVALS.Cycle - -- The sdb_male_swelling code (n/a) cannot be assigned to non-juvenile - -- females. - 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 - INTO a_eid , a_behavior , a_start , a_stop - , a_pid , a_role , a_participant - , a_sex , a_birthdate - , a_arid , a_cycle - FROM events - JOIN roles - ON (roles.eid = events.eid) - JOIN biography_data - ON (biography_data.animid = roles.participant) - JOIN arrivals ON (arrivals.eid = events.eid) - WHERE events.fid = NEW.fid - AND biography_data.sex = 'sdb_female' - AND arrivals.cycle = 'sdb_male_swelling' - AND biography_data.birthdate - >= (NEW.date - - '1 sdb_max_adolescent_age_units'::INTERVAL - - 'sdb_max_adolescent_age sdb_max_adolescent_age_units'::INTERVAL - ) - -- Produce a consistent error message - ORDER BY events.start, events.stop, events.eid - , roles.particpant, roles.role; - IF FOUND THEN - RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'Error on UPDATE of FOLLOWS' - , DETAIL = 'A non-juvenile female, a female older than' - || ' sdb_max_adolescent_age' - || ' sdb_max_adolescent_age_units old, cannot be' - || ' assigned the' - || ' (sdb_male_swelling) code as a sexual cycle state' - || ': Key (FID) = (' - || NEW.fid - || '), Value (Focal) = (' - || NEW.focal - || '), Value (Date) = (' - || NEW.date - || '): Key (EVENTS.EID) = (' - || a_eid - || '): Value (EVENTS.Behavior) = (' - || a_behavior - || '), Value (EVENTS.Start) = (' - || a_start - || '), Value (EVENTS.Stop) = (' - || a_stop - || '): Key (ROLES.PID) = (' - || a_pid - || '), Value (ROLES.Role) = (' - || a_role - || ': Key (BIOGRAPHY_DATA.Animid) = (' - || a_participant - || '): Value (BIOGRAPHY_DATA.Sex) = (' - || a_sex - || '): Value (BIOGRAPHY_DATA.BirthDate) = (' - || a_birthdate - || '): Key (ARRIVALS.ArID) = (' - || a_arid - || '), Value (ARRIVALS.Cycle) = (' - || a_cycle - || ')' - , HINT = 'sdb_follow_age_hint'; - END IF; - -- The sdb_adolescent_swelling code (U) can only be assigned to -- adolescent females. -- Can't be too young. SELECT events.eid, events.behavior, events.start, events.stop diff --git a/doc/src/code_tables.m4 b/doc/src/code_tables.m4 index 7bb87b5..b20f6ab 100644 --- a/doc/src/code_tables.m4 +++ b/doc/src/code_tables.m4 @@ -376,12 +376,7 @@ Special Values `````````````` The value ``sdb_male_swelling`` is the only value which may be -assigned to non-females, and it may not be assigned to non-juvenile -females -- those older than sdb_max_adolescent_age -sdb_max_adolescent_age_units. -The system will generate a warning when ``sdb_male_swelling`` is -assigned to females sdb_min_adolescent_age -sdb_min_adolescent_age_units, or older. +assigned to non-females, and it may not be assigned to females. The value ``sdb_adolescent_swelling`` can only be assigned to adolescent females -- those at least sdb_min_adolescent_age -- 2.34.1