From ab9501beb85d6857b9cde33386928305e46bf2f4 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Wed, 15 Oct 2025 01:34:22 +0000 Subject: [PATCH] Cannot be too old or too young when the cycle code indicates swelling This also makes "special", the "0" and "MISS" cycle codes. And fixes so that MISS is allowed in all cases. --- db/schemas/lib/triggers/create/arrivals.m4 | 220 +++++++--- .../lib/triggers/create/biography_data.m4 | 402 ++++++++++++------ .../lib/triggers/create/cycle_states.m4 | 1 + db/schemas/lib/triggers/create/follows.m4 | 161 +++++++ doc/src/code_tables.m4 | 33 +- doc/src/tables/biography_data.m4 | 4 + include/global_constants.m4 | 6 + include/limits.m4 | 6 + 8 files changed, 640 insertions(+), 193 deletions(-) diff --git a/db/schemas/lib/triggers/create/arrivals.m4 b/db/schemas/lib/triggers/create/arrivals.m4 index b3b3ba0..63e5898 100644 --- a/db/schemas/lib/triggers/create/arrivals.m4 +++ b/db/schemas/lib/triggers/create/arrivals.m4 @@ -190,20 +190,73 @@ CREATE OR REPLACE FUNCTION arrivals_func () || a_date || ')' , HINT = 'sdb_follow_age_hint'; - ELSIF NEW.cycle = 'sdb_adolescent_swelling' THEN - -- The sdb_adolescent_swelling code (U) can only be assigned to - -- adolescent females. + ELSE + IF a_animid <> 'sdb_stranger_female' + AND a_animid <> 'sdb_stranger_female2' + AND a_animid <> 'sdb_stranger_female3' THEN - -- 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 + IF NEW.cycle = '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 younger than' - || ' sdb_min_adolescent_age' - || ' sdb_min_adolescent_age_units old, cannot be' + , 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' @@ -237,54 +290,105 @@ CREATE OR REPLACE FUNCTION arrivals_func () || a_date || ')' , HINT = 'sdb_follow_age_hint'; - END IF; + END IF; + -- End of adolescent swelling + ELSIF NEW.cycle <> 'sdb_no_swelling' + AND NEW.cycle <> 'sdb_missing_swelling' THEN + -- Not sdb_male_swelling and not sdb_adolescent_swelling + -- and not sdb_no_swelling and not sdb_missing_swelling. + -- The other swelling codes indicate some swelling and can + -- only be assigned to mature females. - -- The sdb_adolescent_swelling code (U) can only be assigned to - -- adolescent females. -- Can't be too old. + -- Can't be too young. + IF a_birthdate + > (a_date + - 'sdb_min_swelling_age sdb_min_swelling_age_units'::INTERVAL + ) THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of ARRIVALS' + , DETAIL = 'A non-mature female, a female younger than' + || ' sdb_min_swelling_age' + || ' sdb_min_swelling_age_units old, cannot be' + || ' assigned a cycle code that indicates' + || ' sexual swelling' + || ': 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; - 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'; + -- Can't be too old and still have a sexual swelling. + + IF a_birthdate + <= (a_date + - '1 year'::INTERVAL + - 'sdb_max_swelling_age sdb_max_swelling_age_units'::INTERVAL + ) THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of ARRIVALS' + , DETAIL = 'A female older than' + || ' sdb_max_swelling_age' + || ' sdb_max_swelling_age_units old, cannot be' + || ' assigned a cycle code that indicates' + || ' sexual swelling' + || ': 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; -- Not stranger female test END IF; END IF; ELSE diff --git a/db/schemas/lib/triggers/create/biography_data.m4 b/db/schemas/lib/triggers/create/biography_data.m4 index 6eda6e8..0dbe907 100644 --- a/db/schemas/lib/triggers/create/biography_data.m4 +++ b/db/schemas/lib/triggers/create/biography_data.m4 @@ -603,6 +603,10 @@ CREATE OR REPLACE FUNCTION biography_data_func () IF NEW.sex <> OLD.sex THEN -- The sdb_male_swelling code (n/a) cannot be assigned to -- females. + -- We don't check for sdb_male_swelling when checking for + -- non-zero swelling, below, so it is important that this check + -- comes first or the error message below could have the + -- wrong explaination for why the swelling code was rejected. SELECT roles.role , events.eid, events.behavior, events.start, events.stop , arrivals.arid, arrivals.cycle @@ -661,135 +665,277 @@ CREATE OR REPLACE FUNCTION biography_data_func () IF NEW.sex <> OLD.sex OR NEW.birthdate <> OLD.birthdate THEN - -- 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'; + IF NEW.animid <> 'sdb_stranger_female' + AND NEW.animid <> 'sdb_stranger_female2' + AND NEW.animid <> 'sdb_stranger_female3' THEN + + -- 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; + + -- Not sdb_male_swelling and not sdb_adolescent_swelling + -- and not sdb_no_swelling and not sdb_missing_swelling. + -- The other swelling codes indicate some swelling and can + -- only be assigned to mature 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_no_swelling' + AND arrivals.cycle <> 'sdb_adolescent_swelling' + AND arrivals.cycle <> 'sdb_missing_swelling' + AND NEW.birthdate + > (follows.date + - 'sdb_min_swelling_age sdb_min_swelling_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-mature female, a female younger than' + || ' sdb_min_swelling_age' + || ' sdb_min_swelling_age_units old, cannot be' + || ' assigned a cycle code that indicates' + || ' sexual swelling' + || ': 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; + + -- 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 + , 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_no_swelling' + AND arrivals.cycle <> 'sdb_adolescent_swelling' + AND arrivals.cycle <> 'sdb_missing_swelling' + AND NEW.birthdate + <= (follows.date + - '1 sdb_max_swelling_age_units'::INTERVAL + - 'sdb_max_swelling_age sdb_max_swelling_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 female older than' + || ' sdb_max_swelling_age' + || ' sdb_max_swelling_age_units old, cannot be' + || ' assigned a cycle code that indicates' + || ' sexual swelling' + || ': 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; END IF; END IF; ELSE diff --git a/db/schemas/lib/triggers/create/cycle_states.m4 b/db/schemas/lib/triggers/create/cycle_states.m4 index 6beeb0e..44d3da3 100644 --- a/db/schemas/lib/triggers/create/cycle_states.m4 +++ b/db/schemas/lib/triggers/create/cycle_states.m4 @@ -37,6 +37,7 @@ CREATE OR REPLACE FUNCTION cycle_states_func () IF TG_OP = 'UPDATE' THEN restrict_special_row(`CYCLE_STATES', `Code', sdb_male_swelling) restrict_special_row(`CYCLE_STATES', `Code', sdb_adolescent_swelling) + restrict_special_row(`CYCLE_STATES', `Code', sdb_no_swelling) END IF; RETURN NULL; diff --git a/db/schemas/lib/triggers/create/follows.m4 b/db/schemas/lib/triggers/create/follows.m4 index 6c52650..392f2f7 100644 --- a/db/schemas/lib/triggers/create/follows.m4 +++ b/db/schemas/lib/triggers/create/follows.m4 @@ -244,6 +244,9 @@ CREATE OR REPLACE FUNCTION follows_func () ON (biography_data.animid = roles.participant) JOIN arrivals ON (arrivals.eid = events.eid) WHERE events.fid = NEW.fid + AND roles.participant <> 'sdb_stranger_female' + AND roles.participant <> 'sdb_stranger_female2' + AND roles.participant <> 'sdb_stranger_female3' AND biography_data.sex = 'sdb_female' AND arrivals.cycle = 'sdb_adolescent_swelling' AND biography_data.birthdate @@ -312,6 +315,9 @@ CREATE OR REPLACE FUNCTION follows_func () ON (biography_data.animid = roles.participant) JOIN arrivals ON (arrivals.eid = events.eid) WHERE events.fid = NEW.fid + AND roles.participant <> 'sdb_stranger_female' + AND roles.participant <> 'sdb_stranger_female2' + AND roles.participant <> 'sdb_stranger_female3' AND biography_data.sex = 'sdb_female' AND arrivals.cycle = 'sdb_adolescent_swelling' AND biography_data.birthdate @@ -363,6 +369,161 @@ CREATE OR REPLACE FUNCTION follows_func () || ')' , HINT = 'sdb_follow_age_hint'; END IF; + + -- Not sdb_male_swelling and not sdb_adolescent_swelling + -- and not sdb_no_swelling. + -- The other swelling codes indicate some swelling and can + -- only be assigned to mature 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 roles.participant <> 'sdb_stranger_female' + AND roles.participant <> 'sdb_stranger_female2' + AND roles.participant <> 'sdb_stranger_female3' + AND biography_data.sex = 'sdb_female' + AND arrivals.cycle <> 'sdb_no_swelling' + AND arrivals.cycle <> 'sdb_adolescent_swelling' + AND arrivals.cycle <> 'sdb_missing_swelling' + -- Can't be sdb_male_swelling + AND biography_data.birthdate + AND biology_data.birthdate + > (NEW.date + - 'sdb_min_swelling_age sdb_min_swelling_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-mature female, a female younger than' + || ' sdb_min_swelling_age' + || ' sdb_min_swelling_age_units old, cannot be' + || ' assigned a cycle code that indicates' + || ' sexual swelling' + || ': 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; + + -- Not sdb_male_swelling and not sdb_adolescent_swelling + -- and not sdb_no_swelling. + -- The other swelling codes indicate some swelling and can + -- only be assigned to mature 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 roles.participant <> 'sdb_stranger_female' + AND roles.participant <> 'sdb_stranger_female2' + AND roles.participant <> 'sdb_stranger_female3' + AND biography_data.sex = 'sdb_female' + AND arrivals.cycle <> 'sdb_no_swelling' + AND arrivals.cycle <> 'sdb_adolescent_swelling' + AND arrivals.cycle <> 'sdb_missing_swelling' + -- Can't be sdb_male_swelling + AND biography_data.birthdate + AND biology_data.birthdate + <= (NEW.date + - '1 sdb_max_swelling_age_units'::INTERVAL + - 'sdb_max_swelling_age sdb_max_swelling_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 female older than' + || ' sdb_max_swelling_age' + || ' sdb_max_swelling_age_units old, cannot be' + || ' assigned a cycle code that indicates' + || ' sexual swelling' + || ': 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 b20f6ab..80b1c74 100644 --- a/doc/src/code_tables.m4 +++ b/doc/src/code_tables.m4 @@ -375,13 +375,32 @@ CYCLE_STATES 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 females. - -The value ``sdb_adolescent_swelling`` can only be assigned to -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. +``sdb_male_swelling`` + The only value which may be assigned to non-females. + This value may *not* be assigned to females. + +``sdb_adolescent_swelling`` + This value can only be assigned to 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. + The exception to this rule are the unknown females, those with a + |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.Animid| that is one of: + ``sdb_stranger_female``, ``sdb_stranger_female2``, and + ``sdb_stranger_female3``. + +``sdb_no_swelling`` + The value that means there is no swelling. + This value can only be assigned to females at least + sdb_min_swelling_age sdb_min_swelling_age_units old, and not more + than sdb_max_swelling_age sdb_max_swelling_age_units old. + The exception to this rule are the unknown females, those with a + |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.Animid| that is one of: + ``sdb_stranger_female``, ``sdb_stranger_female2``, and + ``sdb_stranger_female3``. + +``sdb_missing_swelling`` + The swelling information is missing. Column Descriptions diff --git a/doc/src/tables/biography_data.m4 b/doc/src/tables/biography_data.m4 index 8881205..1bc03e2 100644 --- a/doc/src/tables/biography_data.m4 +++ b/doc/src/tables/biography_data.m4 @@ -90,6 +90,10 @@ The row defining the unknown individual, the ``BIOGRAPHY_DATA`` row having an |BIOGRAPHY_DATA.AnimID| value of ``sdb_unk``, is :ref:`special ` and cannot be altered or deleted by ordinary user accounts. +Also :ref:`special ` to the system are the other +unknown individual |BIOGRAPHY_DATA.Animid| codes: +``sdb_stranger_male``, ``sdb_stranger_female``, +``sdb_stranger_female2``, and ``sdb_stranger_female3``. .. _BIOGRAPHY_DATA.AnimID: diff --git a/include/global_constants.m4 b/include/global_constants.m4 index 14d42aa..e55bba8 100644 --- a/include/global_constants.m4 +++ b/include/global_constants.m4 @@ -120,11 +120,17 @@ dnl The NestStart and NestEnd values define(`sdb_in_nest', `1') define(`sdb_out_of_nest', `0') +dnl +dnl Special sexual cycle values dnl dnl The Cycles value for non-females define(`sdb_male_swelling', `n/a') dnl The Cycles value for adolescent females define(`sdb_adolescent_swelling', `U') +dnl The Cycles value for females that mean "no swelling" +define(`sdb_no_swelling', `0') +dnl The Cycles value for missing data +define(`sdb_missing_swelling', `MISS') dnl dnl The Certainty value for when certain of the identity of an arriving diff --git a/include/limits.m4 b/include/limits.m4 index e2b8b6e..04be532 100644 --- a/include/limits.m4 +++ b/include/limits.m4 @@ -48,6 +48,12 @@ define(`sdb_min_adolescent_age_units', `years') define(`sdb_max_adolescent_age', `14') define(`sdb_max_adolescent_age_units', `years') +dnl Ages of females that are allowed to have swelling +define(`sdb_min_swelling_age', `8') +define(`sdb_min_swelling_age_units', `years') +define(`sdb_max_swelling_age', `48') +define(`sdb_max_swelling_age_units', `years') + dnl The minimum age of a male at the birth of their first offspring define(`sdb_min_m_birth', `10') define(`sdb_min_m_birth_units', `years') -- 2.34.1