tests/test_comprehensive.ps1
|
# Comprehensive test for both scalar and table functions # Tests both CreateFunction (scalar UDFs) and CreateTableFunction (table-valued UDFs) # Load module from parent directory Import-Module PaperinikDB -Force $conn = New-DuckDBConnection $conn.Open() Write-Host "Connection opened" -ForegroundColor Green $passed = 0 $failed = 0 function Test-Result { param($Name, $Expected, $Actual) if ($Expected -eq $Actual) { Write-Host " PASS: $Name" -ForegroundColor Green $script:passed++ } else { Write-Host " FAIL: $Name - Expected '$Expected', got '$Actual'" -ForegroundColor Red $script:failed++ } } # ============== SCALAR FUNCTIONS ============== Write-Host "`n=== Scalar Functions ===" -ForegroundColor Cyan # Test 1: Simple scalar function $conn.CreateFunction('double_it', { param([int]$x) $x * 2 }, @([int]), [int]) $result = $conn.sql("SELECT double_it(21)") Test-Result "double_it(21) = 42" 42 $result.'double_it(21)' # Test 2: Multi-parameter scalar function $conn.CreateFunction('add_nums', { param([int]$a, [int]$b) $a + $b }, @([int], [int]), [int]) $result = $conn.sql("SELECT add_nums(17, 25)") Test-Result "add_nums(17, 25) = 42" 42 $result.'add_nums(17, 25)' # Test 3: String scalar function $conn.CreateFunction('greet', { param([string]$name) "Hello, $name!" }, @([string]), [string]) $result = $conn.sql("SELECT greet('World')") Test-Result "greet('World')" "Hello, World!" $result."greet('World')" # Test 4: Boolean scalar function $conn.CreateFunction('is_even', { param([int]$x) ($x % 2) -eq 0 }, @([int]), [bool]) $result = $conn.sql("SELECT is_even(4)") Test-Result "is_even(4) = true" $true $result.'is_even(4)' $result = $conn.sql("SELECT is_even(7)") Test-Result "is_even(7) = false" $false $result.'is_even(7)' # ============== TABLE FUNCTIONS ============== Write-Host "`n=== Table Functions ===" -ForegroundColor Cyan # Test 5: Simple table function (no params) $conn.CreateTableFunction( 'get_fruits', { @( [pscustomobject]@{ name = 'Apple'; color = 'Red' } [pscustomobject]@{ name = 'Banana'; color = 'Yellow' } [pscustomobject]@{ name = 'Grape'; color = 'Purple' } ) }, @{ 'name' = [string]; 'color' = [string] } ) $result = $conn.sql("SELECT * FROM get_fruits()") Test-Result "get_fruits() returns 3 rows" 3 ($result | Measure-Object).Count Test-Result "First fruit is Apple" "Apple" $result[0].name # Test 6: Table function with filtering $result = $conn.sql("SELECT name FROM get_fruits() WHERE color = 'Yellow'") Test-Result "Filter by Yellow color" "Banana" $result.name # Test 7: Parameterized table function $conn.CreateTableFunction( 'generate_sequence', { param([int]$n) 1..$n | ForEach-Object { [pscustomobject]@{ num = $_ squared = $_ * $_ } } }, @{ 'num' = [int]; 'squared' = [int] }, @([int]) ) $result = $conn.sql("SELECT * FROM generate_sequence(5)") Test-Result "generate_sequence(5) returns 5 rows" 5 ($result | Measure-Object).Count Test-Result "5 squared = 25" 25 $result[4].squared # Test 8: Combine scalar and table functions $result = $conn.sql("SELECT num, doubled FROM (SELECT num, double_it(num) as doubled FROM generate_sequence(3))") Test-Result "Combine table + scalar functions" 3 ($result | Measure-Object).Count Test-Result "double_it(3) in subquery = 6" 6 ($result | Where-Object { $_.num -eq 3 }).doubled # Test 9: Aggregate on table function $result = $conn.sql("SELECT SUM(squared) as total FROM generate_sequence(4)") Test-Result "SUM of squares 1+4+9+16 = 30" 30 $result.total # ============== SUMMARY ============== Write-Host "`n=== Summary ===" -ForegroundColor Cyan Write-Host "Passed: $passed" -ForegroundColor Green Write-Host "Failed: $failed" -ForegroundColor $(if ($failed -eq 0) { 'Green' } else { 'Red' }) $conn.Dispose() |