From c538a11e35766517acd74755113966f4a4159654 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Sun, 5 Oct 2025 23:18:43 +0000 Subject: [PATCH] Validate the use of CYCLE_STATES codes Also, clarify the docs as to what the validation is. There is a range of adolescent ages. --- db/include/macros.m4 | 4 + db/schemas/lib/triggers/create/arrivals.m4 | 238 +++++++++++++++ .../lib/triggers/create/biography_data.m4 | 279 +++++++++++++++++- db/schemas/lib/triggers/create/follows.m4 | 212 +++++++++++++ doc/src/code_tables.m4 | 10 +- 5 files changed, 740 insertions(+), 3 deletions(-) diff --git a/db/include/macros.m4 b/db/include/macros.m4 index 44a4668..86745ce 100644 --- a/db/include/macros.m4 +++ b/db/include/macros.m4 @@ -291,5 +291,9 @@ define({restrict_delete},{dnl })dnl changequote(`,')dnl +dnl Commonly used text +define(`sdb_follow_age_hint', +`The BIOGRAPHY.BirthDate and FOLLOWS.Date give the age on the day of the follow') + divert(`0')dnl Output with m4 again ]}])dnl End of ifdef over the whole file. diff --git a/db/schemas/lib/triggers/create/arrivals.m4 b/db/schemas/lib/triggers/create/arrivals.m4 index 7be5842..2771010 100644 --- a/db/schemas/lib/triggers/create/arrivals.m4 +++ b/db/schemas/lib/triggers/create/arrivals.m4 @@ -39,6 +39,244 @@ CREATE OR REPLACE FUNCTION arrivals_func () cannot_change(`ARRIVALS', `ArID') END IF; + -- BIOGRAPHY_DATA + IF TG_OP = 'INSERT' + OR NEW.cycle <> OLD.cycle THEN + -- Check that the cycle code is allowed, based on sex and age. + DECLARE + -- EVENTS + a_eid events.eid%TYPE; + a_behavior events.behavior%TYPE; + a_start events.start%TYPE; + a_stop events.stop%TYPE; + -- FOLLOWS + a_fid follows.fid%TYPE; + a_focal follows.focal%TYPE; + a_date follows.date%TYPE; + -- ROLES + a_role roles.role%TYPE; + -- BIOGRAPHY_DATA + a_animid biography_data.animid%TYPE; + a_sex biography_data.sex%TYPE; + a_birthdate biography_data.birthdate%TYPE; + + BEGIN + -- Get the data we need, and that needed for error message content too + SELECT events.eid, events.behavior, events.start, events.stop + , roles.role + , biography_data.animid, biography_data.sex + , biography_data.birthdate + , follows.fid, follows.focal, follows.date + INTO a_eid , a_behavior , a_start , a_stop + , a_role + , a_animid , a_sex + , a_birthdate + , a_fid , a_focal , a_date + FROM events + JOIN roles + ON (roles.eid = events.eid) + JOIN biography_data + ON (biography_data.animid = roles.participant) + JOIN follows + ON (follows.fid = events.fid) + WHERE roles.participant = NEW.animid + -- Produce a consistent error message + ORDER BY follows.date, follows.fid + , events.start, events.stop, events.eid + , roles.role, roles.pid; + + 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; + ELSIF NEW.code = 'sdb_adolescent_swelling' THEN + -- The sdb_adolescent_swelling code (U) can only be assigned to + -- adolescent females. + + -- Can't be too young. + IF a_birthdate + < (a_date +- 'sdb_min_adolescent_age sdb_min_adolescent_age_units'::INTERVAL + ) THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of ARRIVALS' + , DETAIL = 'A non-adolescent female, a female younger than' + || ' sdb_min_adolescent_age' + || ' sdb_min_adolescent_age_units old, cannot be' + || ' assigned the' + || ' (sdb_adolescent_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; + + -- The sdb_adolescent_swelling code (U) can only be assigned to + -- adolescent females. -- Can't be too old. + + IF a_birthdate + >= (a_date + - '1 year'::INTERVAL +- 'sdb_max_adolescent_age sdb_max_adolescent_age_units'::INTERVAL + ) THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of ARRIVALS' + , DETAIL = 'A non-adolescent female, a female older than' + || ' sdb_max_adolescent_age' + || ' sdb_max_adolescent_age_units old, cannot be' + || ' assigned the' + || ' (sdb_adolescent_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; + END IF; + ELSE + -- Not female + + -- Only the sdb_male_swelling code (n/a) can be assigned + -- to non-females. + 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' + || ' (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; + END IF; + END; + END IF; + RETURN NULL; END; $$; diff --git a/db/schemas/lib/triggers/create/biography_data.m4 b/db/schemas/lib/triggers/create/biography_data.m4 index 619e68f..a89d4b9 100644 --- a/db/schemas/lib/triggers/create/biography_data.m4 +++ b/db/schemas/lib/triggers/create/biography_data.m4 @@ -169,7 +169,7 @@ CREATE OR REPLACE FUNCTION biography_data_func () BEGIN -- Function for biography_data insert and update triggers -- - -- AGPL_notice(` --', `2023, 2024', + -- AGPL_notice(` --', `2023, 2024, 2025', `The Meme Factory, Inc., www.karlpinc.com') IF TG_OP = 'UPDATE' THEN @@ -559,6 +559,283 @@ CREATE OR REPLACE FUNCTION biography_data_func () END IF; END IF; END; + + -- ARRIVALS + DECLARE + -- ARRIVALS + a_arid arrivals.arid%TYPE; + a_cycle arrivals.cycle%TYPE; + -- EVENTS + a_eid events.eid%TYPE; + a_behavior events.behavior%TYPE; + a_start events.start%TYPE; + a_stop events.stop%TYPE; + -- FOLLOWS + a_fid follows.fid%TYPE; + a_focal follows.focal%TYPE; + a_date follows.date%TYPE; + -- ROLES + a_role roles.role%TYPE; + + BEGIN + -- Check ARRIVALS.Cycle + + IF NEW.sex = 'sdb_female' THEN + -- The sdb_male_swelling code (n/a) cannot be assigned to non-juvenile + -- females. + SELECT roles.role + , events.eid, events.behavior, events.start, events.stop + , arrivals.arid, arrivals.cycle + , follows.fid, follows.focal, follows.date + INTO a_role + , a_eid , a_behavior , a_start , a_stop + , a_arid , a_cycle + , a_fid , a_focal , a_date + FROM roles + JOIN events ON (events.eid = roles.eid) + JOIN arrivals ON (arrivals.eid = roles.eid) + 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 + , roles.role, roles.pid; + 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' + || ' (sdb_male_swelling) code as a sexual cycle state' + || ': Key (Animid) = (' + || NEW.animid + || '): Value (Sex) = (' + || NEW.sex + || '): Value (BirthDate) = (' + || NEW.birthdate + || '): Key (ROLES.PID) = (' + || a_pid + || '), Value (ROLES.Role) = (' + || a_role + || '): Key (EVENTS.EID) = (' + || a_eid + || '): Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || '): Key (ARRIVALS.ArID) = (' + || a_arid + || '), Value (ARRIVALS.Cycle) = (' + || a_cycle + || '): Key (FOLLOWS.FID) = (' + || a_fid + || '), Value (FOLLOWS.Focal) = (' + || a_focal + || '), Value (FOLLOWS.Date) = (' + || a_date + || ')' + , 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 roles.role + , events.eid, events.behavior, events.start, events.stop + , arrivals.arid, arrivals.cycle + , follows.fid, follows.focal, follows.date + INTO a_role + , a_eid , a_behavior , a_start , a_stop + , a_arid , a_cycle + , a_fid , a_focal , a_date + FROM roles + JOIN events ON (events.eid = roles.eid) + JOIN arrivals ON (arrivals.eid = roles.eid) + JOIN follows ON (follows.fid = events.fid) + WHERE roles.participant = NEW.animid + AND arrivals.cycle = 'sdb_adolescent_swelling' + AND NEW.birthdate + < (follows.date +- 'sdb_min_adolescent_age sdb_min_adolescent_age_units'::INTERVAL + ) + -- Produce a consistent error message + ORDER BY follows.date, follows.fid + , events.start, events.stop, events.eid + , roles.role, roles.pid; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'A non-adolescent female, a female younger than' + || ' sdb_min_adolescent_age' + || ' sdb_min_adolescent_age_units old, cannot be' + || ' assigned the' + || ' (sdb_adolescent_swelling) code as a sexual' + || ' cycle state' + || ': Key (Animid) = (' + || NEW.animid + || '): Value (Sex) = (' + || NEW.sex + || '): Value (BirthDate) = (' + || NEW.birthdate + || '): Key (ROLES.PID) = (' + || a_pid + || '), Value (ROLES.Role) = (' + || a_role + || '): Key (EVENTS.EID) = (' + || a_eid + || '): Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || '): Key (ARRIVALS.ArID) = (' + || a_arid + || '), Value (ARRIVALS.Cycle) = (' + || a_cycle + || '): Key (FOLLOWS.FID) = (' + || a_fid + || '), Value (FOLLOWS.Focal) = (' + || a_focal + || '), Value (FOLLOWS.Date) = (' + || a_date + || ')' + , HINT = 'sdb_follow_age_hint'; + END IF; + + -- The sdb_adolescent_swelling code (U) can only be assigned to + -- adolescent females. -- Can't be too old. + SELECT roles.role + , events.eid, events.behavior, events.start, events.stop + , arrivals.arid, arrivals.cycle + , follows.fid, follows.focal, follows.date + INTO a_role + , a_eid , a_behavior , a_start , a_stop + , a_arid , a_cycle + , a_fid , a_focal , a_date + FROM roles + JOIN events ON (events.eid = roles.eid) + JOIN arrivals ON (arrivals.eid = roles.eid) + JOIN follows ON (follows.fid = events.fid) + WHERE roles.participant = NEW.animid + AND arrivals.cycle = 'sdb_adolescent_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 + , roles.role, roles.pid; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'A non-adolescent female, a female older than' + || ' sdb_max_adolescent_age' + || ' sdb_max_adolescent_age_units old, cannot be' + || ' assigned the' + || ' (sdb_adolescent_swelling) code as a sexual' + || ' cycle state' + || ': Key (Animid) = (' + || NEW.animid + || '): Value (Sex) = (' + || NEW.sex + || '): Value (BirthDate) = (' + || NEW.birthdate + || '): Key (ROLES.PID) = (' + || a_pid + || '), Value (ROLES.Role) = (' + || a_role + || '): Key (EVENTS.EID) = (' + || a_eid + || '): Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || '): Key (ARRIVALS.ArID) = (' + || a_arid + || '), Value (ARRIVALS.Cycle) = (' + || a_cycle + || '): Key (FOLLOWS.FID) = (' + || a_fid + || '), Value (FOLLOWS.Focal) = (' + || a_focal + || '), Value (FOLLOWS.Date) = (' + || a_date + || ')' + , HINT = 'sdb_follow_age_hint'; + END IF; + ELSE + -- Not female. + -- Only the sdb_male_swelling code (n/a) can be assigned + -- to non-females. + SELECT roles.role + , events.eid, events.behavior, events.start, events.stop + , arrivals.arid, arrivals.cycle + , follows.fid, follows.focal, follows.date + INTO a_role + , a_eid , a_behavior , a_start , a_stop + , a_arid , a_cycle + , a_fid , a_focal , a_date + FROM roles + JOIN events ON (events.eid = roles.eid) + JOIN arrivals ON (arrivals.eid = roles.eid) + JOIN follows ON (follows.fid = events.fid) + WHERE roles.participant = NEW.animid + AND arrivals.cycle <> 'sdb_male_swelling' + -- Produce a consistent error message + ORDER BY follows.date, follows.fid + , events.start, events.stop, events.eid + , roles.role, roles.pid; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'A non-female can only be assigned the' + || ' (sdb_male_swelling) code as a sexual' + || ' cycle state' + || ': Key (Animid) = (' + || NEW.animid + || '): Value (Sex) = (' + || NEW.sex + || '): Value (BirthDate) = (' + || NEW.birthdate + || '): Key (ROLES.PID) = (' + || a_pid + || '), Value (ROLES.Role) = (' + || a_role + || '): Key (EVENTS.EID) = (' + || a_eid + || '): Value (EVENTS.Behavior) = (' + || a_behavior + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || '): Key (ARRIVALS.ArID) = (' + || a_arid + || '), Value (ARRIVALS.Cycle) = (' + || a_cycle + || '): Key (FOLLOWS.FID) = (' + || a_fid + || '), Value (FOLLOWS.Focal) = (' + || a_focal + || '), Value (FOLLOWS.Date) = (' + || a_date + || ')'; + END IF; + END IF; + END; END IF; RETURN NULL; diff --git a/db/schemas/lib/triggers/create/follows.m4 b/db/schemas/lib/triggers/create/follows.m4 index 0336735..64e8d29 100644 --- a/db/schemas/lib/triggers/create/follows.m4 +++ b/db/schemas/lib/triggers/create/follows.m4 @@ -102,6 +102,8 @@ CREATE OR REPLACE FUNCTION follows_func () AND NEW.date <> OLD.date THEN DECLARE -- BIOGRAPHY_DATA + a_sex biography_data.sex%TYPE; + a_birthdate biography_data.birthdate%TYPE; a_entrydate biography_data.entrydate%TYPE; a_departdate biography_data.departdate%TYPE; -- ROLES @@ -113,6 +115,9 @@ CREATE OR REPLACE FUNCTION follows_func () a_behavior events.behavior%TYPE; a_start events.start%TYPE; a_stop events.stop%TYPE; + -- ARRIVALS + a_arid arrivals.arid%TYPE; + a_cycle arrivals.cycle%TYPE; BEGIN -- The participants in an event cannot be in a follow that is @@ -218,6 +223,213 @@ CREATE OR REPLACE FUNCTION follows_func () || a_departdate || ')'; END IF; + + + -- 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 + , 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_adolescent_swelling' + AND biography_data.birthdate + AND biology_data.birthdate + < (NEW.date + - 'sdb_min_adolescent_age sdb_min_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-adolescent female, a female younger than' + || ' sdb_min_adolescent_age' + || ' sdb_min_adolescent_age_units old, cannot be' + || ' assigned the' + || ' (sdb_adolescent_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 old. + 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_adolescent_swelling' + AND biography_data.birthdate + AND biology_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-adolescent female, a female older than' + || ' sdb_max_adolescent_age' + || ' sdb_max_adolescent_age_units old, cannot be' + || ' assigned the' + || ' (sdb_adolescent_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; END; END IF; diff --git a/doc/src/code_tables.m4 b/doc/src/code_tables.m4 index 3fcbfeb..7bb87b5 100644 --- a/doc/src/code_tables.m4 +++ b/doc/src/code_tables.m4 @@ -377,10 +377,16 @@ 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. +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. The value ``sdb_adolescent_swelling`` can only be assigned to -adolescent females. +adolescent females -- those at least sdb_min_adolescent_age +sdb_min_adolescent_age_units old and not more than +sdb_max_adolescent_age sdb_max_adolescent_age_units old. Column Descriptions -- 2.34.1