Ryan Malloy
501872d45d
constellation_full_name(text) returns full IAU name from 3-letter abbreviation (88-entry static table, IMMUTABLE). Returns NULL for invalid input — composable with constellation() in queries. Three rise_set_status functions classify body visibility as 'rises_and_sets', 'circumpolar', or 'never_rises' by sampling elevation at 48 points across 24h. Separate diagnostic path — called only when rise/set returns NULL, zero cost in normal case. 147 → 151 SQL objects. 25 → 26 regression suites. All pass.
205 lines
6.8 KiB
Plaintext
205 lines
6.8 KiB
Plaintext
-- v015_features.sql -- Tests for v0.15.0: constellation_full_name + rise_set_status
|
|
--
|
|
-- Verifies the constellation full name lookup and the rise/set
|
|
-- status diagnostic functions.
|
|
CREATE EXTENSION IF NOT EXISTS pg_orrery;
|
|
NOTICE: extension "pg_orrery" already exists, skipping
|
|
-- ============================================================
|
|
-- constellation_full_name: known abbreviations
|
|
-- ============================================================
|
|
SELECT constellation_full_name('Ari') AS aries;
|
|
aries
|
|
-------
|
|
Aries
|
|
(1 row)
|
|
|
|
SELECT constellation_full_name('CMa') AS canis_major;
|
|
canis_major
|
|
-------------
|
|
Canis Major
|
|
(1 row)
|
|
|
|
SELECT constellation_full_name('UMi') AS ursa_minor;
|
|
ursa_minor
|
|
------------
|
|
Ursa Minor
|
|
(1 row)
|
|
|
|
SELECT constellation_full_name('Ori') AS orion;
|
|
orion
|
|
-------
|
|
Orion
|
|
(1 row)
|
|
|
|
SELECT constellation_full_name('Cyg') AS cygnus;
|
|
cygnus
|
|
--------
|
|
Cygnus
|
|
(1 row)
|
|
|
|
SELECT constellation_full_name('Oct') AS octans;
|
|
octans
|
|
--------
|
|
Octans
|
|
(1 row)
|
|
|
|
SELECT constellation_full_name('TrA') AS tri_australe;
|
|
tri_australe
|
|
---------------------
|
|
Triangulum Australe
|
|
(1 row)
|
|
|
|
-- ============================================================
|
|
-- constellation_full_name: composability with constellation()
|
|
-- ============================================================
|
|
-- Chain: equatorial -> abbreviation -> full name
|
|
SELECT constellation_full_name(constellation(2.5303, 89.264)) AS polaris_full;
|
|
polaris_full
|
|
--------------
|
|
Ursa Minor
|
|
(1 row)
|
|
|
|
SELECT constellation_full_name(constellation(6.7525, -16.716)) AS sirius_full;
|
|
sirius_full
|
|
-------------
|
|
Canis Major
|
|
(1 row)
|
|
|
|
-- Chain with planet equatorial
|
|
SELECT constellation_full_name(
|
|
constellation(planet_equatorial(5, '2024-01-15 12:00:00+00'::timestamptz))
|
|
) AS jupiter_full;
|
|
jupiter_full
|
|
--------------
|
|
Aries
|
|
(1 row)
|
|
|
|
-- ============================================================
|
|
-- constellation_full_name: NULL for invalid abbreviation
|
|
-- ============================================================
|
|
SELECT constellation_full_name('XYZ') IS NULL AS invalid_returns_null;
|
|
invalid_returns_null
|
|
----------------------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT constellation_full_name('') IS NULL AS empty_returns_null;
|
|
empty_returns_null
|
|
--------------------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT constellation_full_name('Toolong') IS NULL AS toolong_returns_null;
|
|
toolong_returns_null
|
|
----------------------
|
|
t
|
|
(1 row)
|
|
|
|
-- ============================================================
|
|
-- constellation_full_name: all 88 are reachable (count check)
|
|
-- ============================================================
|
|
-- Use generate_series to count distinct full names from the
|
|
-- known constellation abbreviations via a spot check
|
|
SELECT count(DISTINCT constellation_full_name(abbr)) = 7
|
|
AS spot_check_7_names
|
|
FROM (VALUES ('Ari'), ('CMa'), ('UMi'), ('Ori'), ('Cyg'), ('Oct'), ('TrA')) AS t(abbr);
|
|
spot_check_7_names
|
|
--------------------
|
|
t
|
|
(1 row)
|
|
|
|
-- ============================================================
|
|
-- sun_rise_set_status: mid-latitude (Eagle, Idaho) in winter
|
|
-- Sun rises and sets normally
|
|
-- ============================================================
|
|
SELECT sun_rise_set_status('(43.7,-116.4,800)'::observer, '2024-01-15 00:00:00+00'::timestamptz)
|
|
AS sun_status_midlat;
|
|
sun_status_midlat
|
|
-------------------
|
|
rises_and_sets
|
|
(1 row)
|
|
|
|
-- ============================================================
|
|
-- sun_rise_set_status: 70N in June (midnight sun)
|
|
-- ============================================================
|
|
SELECT sun_rise_set_status('(70.0,25.0,0)'::observer, '2024-06-21 00:00:00+00'::timestamptz)
|
|
AS sun_status_midnight_sun;
|
|
sun_status_midnight_sun
|
|
-------------------------
|
|
circumpolar
|
|
(1 row)
|
|
|
|
-- ============================================================
|
|
-- sun_rise_set_status: 70N in December (polar night)
|
|
-- ============================================================
|
|
SELECT sun_rise_set_status('(70.0,25.0,0)'::observer, '2024-12-21 00:00:00+00'::timestamptz)
|
|
AS sun_status_polar_night;
|
|
sun_status_polar_night
|
|
------------------------
|
|
never_rises
|
|
(1 row)
|
|
|
|
-- ============================================================
|
|
-- moon_rise_set_status: mid-latitude — Moon normally rises/sets
|
|
-- ============================================================
|
|
SELECT moon_rise_set_status('(43.7,-116.4,800)'::observer, '2024-01-15 00:00:00+00'::timestamptz)
|
|
AS moon_status_midlat;
|
|
moon_status_midlat
|
|
--------------------
|
|
rises_and_sets
|
|
(1 row)
|
|
|
|
-- ============================================================
|
|
-- planet_rise_set_status: Jupiter from mid-latitude (normal)
|
|
-- ============================================================
|
|
SELECT planet_rise_set_status(5, '(43.7,-116.4,800)'::observer, '2024-01-15 00:00:00+00'::timestamptz)
|
|
AS jupiter_status_midlat;
|
|
jupiter_status_midlat
|
|
-----------------------
|
|
rises_and_sets
|
|
(1 row)
|
|
|
|
-- ============================================================
|
|
-- Consistency: status matches rise/set NULL contract
|
|
-- ============================================================
|
|
-- When sun_next_set returns NULL (circumpolar), status should say so
|
|
SELECT sun_next_set('(70.0,25.0,0)'::observer, '2024-06-21 00:00:00+00'::timestamptz) IS NULL
|
|
AS sun_no_set_null;
|
|
sun_no_set_null
|
|
-----------------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT sun_rise_set_status('(70.0,25.0,0)'::observer, '2024-06-21 00:00:00+00'::timestamptz)
|
|
= 'circumpolar' AS status_confirms_circumpolar;
|
|
status_confirms_circumpolar
|
|
-----------------------------
|
|
t
|
|
(1 row)
|
|
|
|
-- When sun_next_rise returns NULL (polar night), status should say so
|
|
SELECT sun_next_rise('(70.0,25.0,0)'::observer, '2024-12-21 00:00:00+00'::timestamptz) IS NULL
|
|
AS sun_no_rise_null;
|
|
sun_no_rise_null
|
|
------------------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT sun_rise_set_status('(70.0,25.0,0)'::observer, '2024-12-21 00:00:00+00'::timestamptz)
|
|
= 'never_rises' AS status_confirms_never_rises;
|
|
status_confirms_never_rises
|
|
-----------------------------
|
|
t
|
|
(1 row)
|
|
|
|
-- ============================================================
|
|
-- Error cases
|
|
-- ============================================================
|
|
-- Invalid body_id for planet_rise_set_status
|
|
DO $$ BEGIN PERFORM planet_rise_set_status(0, '(43.7,-116.4,800)'::observer, '2024-01-15 00:00:00+00'::timestamptz); EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'body_id=0: %', SQLERRM; END $$;
|
|
NOTICE: body_id=0: planet_rise_set_status: body_id 0 must be 1-8 (Mercury-Neptune)
|
|
DO $$ BEGIN PERFORM planet_rise_set_status(3, '(43.7,-116.4,800)'::observer, '2024-01-15 00:00:00+00'::timestamptz); EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'body_id=3(Earth): %', SQLERRM; END $$;
|
|
NOTICE: body_id=3(Earth): cannot observe Earth from Earth
|
|
DO $$ BEGIN PERFORM planet_rise_set_status(9, '(43.7,-116.4,800)'::observer, '2024-01-15 00:00:00+00'::timestamptz); EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'body_id=9: %', SQLERRM; END $$;
|
|
NOTICE: body_id=9: planet_rise_set_status: body_id 9 must be 1-8 (Mercury-Neptune)
|