Something went wrong. Try again.
Something went wrong. Try again.
4.2 kB · 135 lines
SQL
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136-- 01_structural_tests.sql: Validate function/view structure and parityBEGIN;SELECT plan(14);
-- =============================================================================-- Function Structure Tests-- =============================================================================
-- Test 1: Function existsSELECT has_function('pg_firstaid', 'pg_firstAid() function exists');
-- Test 2: Function returns 8 columnsSELECT is( (SELECT array_length(proargnames, 1) FROM pg_proc WHERE proname = 'pg_firstaid'), 8, 'pg_firstAid() returns 8 columns');
-- Test 3: Column names match expectedSELECT is( (SELECT proargnames FROM pg_proc WHERE proname = 'pg_firstaid'), ARRAY['severity','category','check_name','object_name', 'issue_description','current_value','recommended_action', 'documentation_link'], 'pg_firstAid() column names match expected');
-- Test 4: All severity values are validSELECT is( (SELECT count(*)::int FROM pg_firstAid() WHERE severity NOT IN ('CRITICAL','HIGH','MEDIUM','LOW','INFO')), 0, 'All severity values are valid (CRITICAL/HIGH/MEDIUM/LOW/INFO)');
-- Test 5: No null severity valuesSELECT is( (SELECT count(*)::int FROM pg_firstAid() WHERE severity IS NULL), 0, 'No NULL severity values in function results');
-- Test 6: No null category valuesSELECT is( (SELECT count(*)::int FROM pg_firstAid() WHERE category IS NULL), 0, 'No NULL category values in function results');
-- Test 7: No null check_name valuesSELECT is( (SELECT count(*)::int FROM pg_firstAid() WHERE check_name IS NULL), 0, 'No NULL check_name values in function results');
-- Test 8: Severity ordering is correct (CRITICAL before HIGH before MEDIUM etc.)SELECT ok( (WITH ordered AS ( SELECT severity, ROW_NUMBER() OVER () as rn, CASE severity WHEN 'CRITICAL' THEN 1 WHEN 'HIGH' THEN 2 WHEN 'MEDIUM' THEN 3 WHEN 'LOW' THEN 4 WHEN 'INFO' THEN 5 END as expected_order FROM pg_firstAid() ) SELECT bool_and( expected_order >= LAG(expected_order, 1, 0) OVER (ORDER BY rn) ) FROM ordered), 'Function results are ordered by severity (CRITICAL -> HIGH -> MEDIUM -> LOW -> INFO)');
-- =============================================================================-- View Structure Tests-- =============================================================================
-- Test 9: View existsSELECT has_view('public', 'v_pgfirstaid', 'v_pgfirstAid view exists');
-- Test 10: View has correct columns (includes severity_order)SELECT columns_are( 'public', 'v_pgfirstaid', ARRAY['severity','category','check_name','object_name', 'issue_description','current_value','recommended_action', 'documentation_link','severity_order'], 'v_pgfirstAid view has expected columns (including severity_order)');
-- =============================================================================-- Parity Tests-- =============================================================================
-- Test 11: Function and view return same check_names (function minus view)SELECT is( (SELECT count(*)::int FROM ( SELECT check_name FROM pg_firstAid() EXCEPT SELECT check_name FROM v_pgfirstaid ) diff), 0, 'No check_names in function that are missing from view');
-- Test 12: View has no extra check_names vs functionSELECT is( (SELECT count(*)::int FROM ( SELECT check_name FROM v_pgfirstaid EXCEPT SELECT check_name FROM pg_firstAid() ) diff), 0, 'No check_names in view that are missing from function');
-- Test 13: Row counts matchSELECT is( (SELECT count(*)::int FROM pg_firstAid()), (SELECT count(*)::int FROM v_pgfirstaid), 'Function and view return same number of rows');
-- Test 14: INFO checks always present (baseline sanity)SELECT ok( (SELECT count(*) > 0 FROM pg_firstAid() WHERE severity = 'INFO'), 'INFO severity results are always present');
SELECT * FROM finish();ROLLBACK;