From a2339fc26ae8359a11efb0db05680fec623656b4 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Mon, 10 Nov 2025 21:37:18 +0000 Subject: [PATCH] Add CYCLE_STATES.AsNum column --- conversion/load_support.sql | 18 ++++++------- .../codes/tables/create/cycle_states.m4 | 15 ++++++++--- doc/src/code_tables.m4 | 25 +++++++++++++++++++ 3 files changed, 46 insertions(+), 12 deletions(-) diff --git a/conversion/load_support.sql b/conversion/load_support.sql index e440461..c0d7b52 100644 --- a/conversion/load_support.sql +++ b/conversion/load_support.sql @@ -78,15 +78,15 @@ insert into comm_ids (commid, name, notes, membcriteria) , ('KL_KK', 'Kasekela/kalande', '', ''); -- cycle_states -INSERT INTO cycle_states (code, description) - VALUES ('0', 'Not swollen') - , ('0.25', '1/4 swollen') - , ('0.5', '1/2 swollen') - , ('0.75', '3/4 swollen') - , ('1', 'Fully swollen') - , ('U', 'Adolescent swelling') - , ('n/a', 'Male') - , ('MISS', 'Missing data'); -- problem #42 +INSERT INTO cycle_states (code, asnum, description) + VALUES ('0', 0, 'Not swollen') + , ('0.25', 0.25, '1/4 swollen') + , ('0.5', 0.5, '1/2 swollen') + , ('0.75', 0.75, '3/4 swollen') + , ('1', 1.0, 'Fully swollen') + , ('U', NULL, 'Adolescent swelling') + , ('n/a', NULL, 'Male') + , ('MISS', NULL, 'Missing data'); -- problem #42 -- dad_statuses INSERT INTO dad_statuses (status, description) diff --git a/db/schemas/codes/tables/create/cycle_states.m4 b/db/schemas/codes/tables/create/cycle_states.m4 index b343094..4533498 100644 --- a/db/schemas/codes/tables/create/cycle_states.m4 +++ b/db/schemas/codes/tables/create/cycle_states.m4 @@ -23,6 +23,15 @@ include(`tablemacros.m4')dnl include(`grants.m4')dnl dnl -support_table(`CYCLE_STATES', `Code', `TEXT', ` - emptytext_check(`Code')' -) +CREATE TABLE cycle_states ( + code TEXT PRIMARY KEY NOT NULL + emptytext_check(`Code') + nospaces_check(`Code') + ,asnum DOUBLE PRECISION + CONSTRAINT "AsNum must be between 0 and 1, inclusive" + CHECK (0 <= asnum AND asnum <= 1) + ,description TEXT NOT NULL + emptytext_check(`Description') +); + +grant_priv(`CYCLE_STATES') diff --git a/doc/src/code_tables.m4 b/doc/src/code_tables.m4 index 73e7750..2eca5ac 100644 --- a/doc/src/code_tables.m4 +++ b/doc/src/code_tables.m4 @@ -428,6 +428,31 @@ Code |CYCLE_STATES.Code_summary| |caseuniquekeycol| +.. _CYCLE_STATES.AsNum: + +AsNum +''''' + +.. |CYCLE_STATES.AsNum_summary| replace:: + A numeric representation of the estrus swelling value, a value able + to be used in numeric computations. + +|CYCLE_STATES.AsNum_summary| +This is a standard |double precision floating point number|, a very +common computer representation of a number that is used when +non-integral, or very large or very small values may be encountered. +As such, it has approximately 15 decimal digits of precision and +computations which use its value are inexact, but good enough because +15 decimal digits of precision are good enough. + +The value of this column must be between ``0`` and ``1``, inclusive. +The value of this column should be |null| when the |CYCLE_STATES.Code| +is only used in cases where a swelling value makes no sense. +.. NULL is allowed so that if such a code is inadvertantly included +.. in a computation the result will be NULL, alerting the user to +.. the problem. + + .. _CYCLE_STATES.Description: Description -- 2.34.1