From 89978d5615d1c5dc02867ff9d9ce28728ce10897 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Fri, 31 Jul 2026 16:54:41 +0000 Subject: [PATCH] Change build_swelling_states() so that it uses CYCLE_STATES.SSRank --- .../functions/create/build_swelling_states.m4 | 16 ++++++++++++++-- doc/src/functions/build_swelling_states.m4 | 4 ++++ 2 files changed, 18 insertions(+), 2 deletions(-) diff --git a/db/schemas/sokwedb/functions/create/build_swelling_states.m4 b/db/schemas/sokwedb/functions/create/build_swelling_states.m4 index 448a242..25cb0e7 100644 --- a/db/schemas/sokwedb/functions/create/build_swelling_states.m4 +++ b/db/schemas/sokwedb/functions/create/build_swelling_states.m4 @@ -122,19 +122,25 @@ CREATE OR REPLACE FUNCTION build_swelling_states(a_animid TEXT) FROM obs JOIN arrivals_a ON (arrivals_a.eid = obs.eid) JOIN roles ON (roles.eid = obs.eid) + JOIN cycle_states ON (cycle_states.code = arrivals_a.swelling) WHERE roles.participant = a_animid + AND cycle_states.ssrank IS NOT NULL UNION SELECT obs.date FROM obs JOIN arrivals ON (arrivals.eid = obs.eid) JOIN roles ON (roles.eid = obs.eid) + JOIN cycle_states ON (cycle_states.code = arrivals.cycle) WHERE roles.participant = a_animid + AND cycle_states.ssrank IS NOT NULL UNION SELECT obs.date FROM obs JOIN matings ON (matings.eid = obs.eid) JOIN roles ON (roles.eid = obs.eid) + JOIN cycle_states ON (cycle_states.code = matings.swelling) WHERE roles.participant = a_animid + AND cycle_states.ssrank IS NOT NULL UNION SELECT swelling_sources.date FROM swelling_sources @@ -150,24 +156,30 @@ CREATE OR REPLACE FUNCTION build_swelling_states(a_animid TEXT) FROM obs JOIN arrivals_a ON (arrivals_a.eid = obs.eid) JOIN roles ON (roles.eid = obs.eid) + JOIN cycle_states ON (cycle_states.code = arrivals_a.swelling) WHERE roles.participant = a_animid AND obs.date = a_date + AND cycle_states.ssrank IS NOT NULL UNION SELECT arrivals.cycle, arrivals.cycle , 'sdb_sokwedb_source' FROM obs JOIN arrivals ON (arrivals.eid = obs.eid) JOIN roles ON (roles.eid = obs.eid) + JOIN cycle_states ON (cycle_states.code = arrivals.cycle) WHERE roles.participant = a_animid AND obs.date = a_date + AND cycle_states.ssrank IS NOT NULL UNION SELECT matings.swelling, matings.swelling , 'sdb_sokwedb_source' FROM obs JOIN matings ON (matings.eid = obs.eid) JOIN roles ON (roles.eid = obs.eid) + JOIN cycle_states ON (cycle_states.code = matings.swelling) WHERE roles.participant = a_animid AND obs.date = a_date + AND cycle_states.ssrank IS NOT NULL UNION SELECT swelling_sources.swellingmin, swelling_sources.swellingmax , swelling_sources.source @@ -185,7 +197,7 @@ CREATE OR REPLACE FUNCTION build_swelling_states(a_animid TEXT) ON (sighting_records.code = swellings.source) JOIN cycle_states ON (cycle_states.code = swellings.swelling_min) - ORDER BY cycle_states.asnum + ORDER BY cycle_states.ssrank , sighting_records.priority LIMIT 1) @@ -196,7 +208,7 @@ CREATE OR REPLACE FUNCTION build_swelling_states(a_animid TEXT) ON (sighting_records.code = swellings.source) JOIN cycle_states ON (cycle_states.code = swellings.swelling_max) - ORDER BY cycle_states.asnum DESC + ORDER BY cycle_states.ssrank DESC , sighting_records.priority LIMIT 1) diff --git a/doc/src/functions/build_swelling_states.m4 b/doc/src/functions/build_swelling_states.m4 index 2e37c09..7d2714d 100644 --- a/doc/src/functions/build_swelling_states.m4 +++ b/doc/src/functions/build_swelling_states.m4 @@ -92,6 +92,10 @@ above tables, the source recorded in |SWELLING_SOURCES|.\ |SWELLING_SOURCES.Source| is that with the smallest |SIGHTING_RECORDS|.\ |SIGHTING_RECORDS.Priority| value. +The |CYCLE_STATES| codes to appear in |SWELLING_STATES|, and the +ranking of the codes, is determined by the |CYCLE_STATES|.\ +|CYCLE_STATES.SSRank| column. + Examples ```````` -- 2.34.1