PaperinikDB

1.5.4

tests/test_quack.ps1

                                # test_quack.ps1

# Validates Quack-related additions (New-DuckDBConnection -QuackToken,

# Start-DuckDBQuackServer, Connect-DuckDBQuack) and runs regression checks

# on the existing scalar/table-function and core-connection functionality.

# Always import from the local repo directory so tests target the version under development

Import-Module (Join-Path $PSScriptRoot '..' 'PaperinikDB.psd1') -Force

$passed = 0

$failed = 0

$skipped = 0

function Test-Result {

    param([string]$Name, $Expected, $Actual)

    if ("$Expected" -eq "$Actual") {

        Write-Host "  PASS: $Name" -ForegroundColor Green

        $script:passed++

    } else {

        Write-Host "  FAIL: $Name  |  expected='$Expected'  actual='$Actual'" -ForegroundColor Red

        $script:failed++

    }

}

function Test-True {

    param([string]$Name, [bool]$Result)

    if ($Result) {

        Write-Host "  PASS: $Name" -ForegroundColor Green

        $script:passed++

    } else {

        Write-Host "  FAIL: $Name  |  expected=True  actual=False" -ForegroundColor Red

        $script:failed++

    }

}

function Test-Skip {

    param([string]$Name, [string]$Reason)

    Write-Host "  SKIP: $Name  |  $Reason" -ForegroundColor Yellow

    $script:skipped++

}

# ─────────────────────────────────────────────────────────────────────────────

Write-Host "`n=== REGRESSION: Core connection ===" -ForegroundColor Cyan

$conn = New-DuckDBConnection

Test-True   'New-DuckDBConnection succeeds'        ($null -ne $conn)

$r = $conn.sql('SELECT 42 AS answer')

Test-Result 'Basic SELECT returns correct value'   42  $r.answer

$r = $conn.sql("SELECT 'hello' AS greeting")

Test-Result 'String SELECT returns correct value'  'hello'  $r.greeting

$conn.CloseDB()

# ─────────────────────────────────────────────────────────────────────────────

Write-Host "`n=== REGRESSION: Scalar UDFs ===" -ForegroundColor Cyan

$conn = New-DuckDBConnection

$conn.CreateFunction('triple_it', { param([int]$x) $x * 3 }, @([int]), [int])

$r = $conn.sql('SELECT triple_it(14) AS val')

Test-Result 'Scalar UDF triple_it(14) = 42'  42  $r.val

$conn.CreateFunction('concat_str', { param([string]$a, [string]$b) "$a$b" }, @([string], [string]), [string])

$r = $conn.sql("SELECT concat_str('foo','bar') AS val")

Test-Result 'Scalar UDF concat_str = foobar'  'foobar'  $r.val

$conn.CloseDB()

# ─────────────────────────────────────────────────────────────────────────────

Write-Host "`n=== REGRESSION: Table UDFs ===" -ForegroundColor Cyan

$conn = New-DuckDBConnection

$conn.CreateTableFunction(

    'two_rows',

    {

        @(

            [PSCustomObject]@{ id = 1; label = 'alpha' }

            [PSCustomObject]@{ id = 2; label = 'beta' }

        )

    },

    @{ id = [int]; label = [string] }

)

$r = $conn.sql('SELECT COUNT(*) AS n FROM two_rows()')

Test-Result 'Table UDF row count = 2'           2        $r.n

$r = $conn.sql('SELECT label FROM two_rows() WHERE id = 2')

Test-Result 'Table UDF filtered row = beta'     'beta'   $r.label

$conn.CloseDB()

# ─────────────────────────────────────────────────────────────────────────────

Write-Host "`n=== NEW: New-DuckDBConnection -QuackToken ===" -ForegroundColor Cyan

try {

    $conn = New-DuckDBConnection -QuackToken 'regression_test_token'

    Test-True   'Connection state is Open after -QuackToken'  ($conn.State -eq 'Open')

    # Verify the named secret was registered

    $secrets = $conn.sql("SELECT name, type FROM duckdb_secrets() WHERE type = 'quack'")

    Test-True   'Quack secret exists in duckdb_secrets()'  ($null -ne $secrets)

    $conn.CloseDB()

} catch {

    if ($_ -match 'quack|extension|Extension') {

        Test-Skip 'New-DuckDBConnection -QuackToken' "Quack extension unavailable: $_"

    } else {

        $script:failed++

        Write-Host "  FAIL: New-DuckDBConnection -QuackToken — $_" -ForegroundColor Red

    }

}

# ─────────────────────────────────────────────────────────────────────────────

Write-Host "`n=== NEW: Start-DuckDBQuackServer / Connect-DuckDBQuack ===" -ForegroundColor Cyan

$server = $null

$client = $null

$quackToken = 'paperinik_' + [System.Guid]::NewGuid().ToString('N').Substring(0, 12)

try {

    # Start the server

    $server = Start-DuckDBQuackServer -Token $quackToken

    Test-True   'Start-DuckDBQuackServer returns a handle'    ($null -ne $server)

    Test-Result 'Server handle .Token matches supplied token' $quackToken  $server.Token

    Test-Result 'Server handle .Endpoint is default'         'quack:localhost'  $server.Endpoint

    Test-True   'Server handle exposes Stop() method'        (($server | Get-Member -Name 'Stop' -MemberType ScriptMethod | Measure-Object).Count -gt 0)

    Test-True   'Server handle has Connection property'       ($null -ne $server.Connection)

    # Allow the server runspace a moment to reach its listening state

    Start-Sleep -Milliseconds 800

    # Connect a client

    $client = Connect-DuckDBQuack -Token $quackToken

    Test-True   'Connect-DuckDBQuack returns an open connection'  ($client.State -eq 'Open')

    # Create a table on the server side and read it back

    [void]$client.sql("CREATE TABLE remote.quack_test AS SELECT 99 AS val, 'duckdb' AS name")

    $r = $client.sql('FROM remote.quack_test')

    Test-Result 'Remote table roundtrip — val'   99       $r.val

    Test-Result 'Remote table roundtrip — name'  'duckdb' $r.name

    $client.CloseDB()

    $client = $null

    $server.Stop()

    $server = $null

    Test-True 'Server stopped without error' $true

} catch {

    $msg = $_.Exception.Message

    if ($msg -match 'quack|extension|Extension|9494|port|Port') {

        Test-Skip 'Quack server/client round-trip' "Quack not available or port busy: $msg"

    } else {

        $script:failed++

        Write-Host "  FAIL: Quack round-trip — $msg" -ForegroundColor Red

        Write-Host "  $($_.ScriptStackTrace)" -ForegroundColor DarkRed

    }

} finally {

    if ($client) { try { $client.CloseDB() } catch {} }

    if ($server) { try { $server.Stop() } catch {} }

}

# ─────────────────────────────────────────────────────────────────────────────

Write-Host "`n=== NEW: sql() DDL / DML dispatch ===" -ForegroundColor Cyan

$conn = New-DuckDBConnection

# DDL — CREATE TABLE should return nothing

$ddlResult = $conn.sql('CREATE TABLE items (id INT, name VARCHAR, price DOUBLE)')

Test-True   'CREATE TABLE returns nothing'  ($null -eq $ddlResult)

# DML INSERT — should return nothing (row count → Verbose stream only)

$insResult = $conn.sql("INSERT INTO items VALUES (1, 'Apple', 1.50), (2, 'Banana', 0.75), (3, 'Cherry', 3.00)")

Test-True   'INSERT returns nothing'        ($null -eq $insResult)

# SELECT after INSERT — should return rows

$rows = $conn.sql('SELECT * FROM items ORDER BY id')

Test-True   'SELECT returns rows array'    ($rows -is [System.Array] -or $null -ne $rows)

Test-Result 'SELECT row count = 3'         3   ($rows | Measure-Object).Count

Test-Result 'First row name = Apple'       'Apple'  $rows[0].name

Test-Result 'Third row price = 3.0'        3.0      $rows[2].price

# DML UPDATE

$updResult = $conn.sql("UPDATE items SET price = 2.00 WHERE name = 'Apple'")

Test-True   'UPDATE returns nothing'       ($null -eq $updResult)

$r = $conn.sql("SELECT price FROM items WHERE name = 'Apple'")

Test-Result 'Price updated to 2.00'        2.0  $r.price

# DML DELETE

$delResult = $conn.sql("DELETE FROM items WHERE id = 3")

Test-True   'DELETE returns nothing'       ($null -eq $delResult)

$r = $conn.sql('SELECT COUNT(*) AS n FROM items')

Test-Result 'Row count after DELETE = 2'   2  $r.n

# INSERT … RETURNING — should yield rows

$retRows = $conn.sql("INSERT INTO items VALUES (4, 'Date', 5.00) RETURNING *")

Test-True   'INSERT RETURNING yields rows'  ($null -ne $retRows)

Test-Result 'RETURNING row id = 4'          4       $retRows.id

Test-Result 'RETURNING row name = Date'     'Date'  $retRows.name

# SELECT on empty result — should return nothing (not an error)

$empty = $conn.sql("SELECT * FROM items WHERE id = 999")

Test-True   'SELECT with no results returns nothing'  ($null -eq $empty)

$conn.CloseDB()

# ─────────────────────────────────────────────────────────────────────────────

Write-Host "`n=== NEW: Connect-DuckDBQuack alias validation ===" -ForegroundColor Cyan

try {

    Connect-DuckDBQuack -Token 'x' -Alias '123bad'

    $script:failed++

    Write-Host "  FAIL: Invalid alias '123bad' should have thrown" -ForegroundColor Red

} catch {

    Test-True 'Invalid SQL alias throws an error' ($_ -match 'valid SQL identifier')

}

# ─────────────────────────────────────────────────────────────────────────────

Write-Host "`n=== NEW: GetAssemblyVersions (AppDomain) ===" -ForegroundColor Cyan

$conn = New-DuckDBConnection

$conn.Open()   # connection must be open so CloseDB() can close it

$versions = $conn.GetAssemblyVersions()

Test-True 'GetAssemblyVersions returns results'              ($null -ne $versions)

Test-True 'DuckDB.NET.Data assembly is listed'               (($versions | Where-Object { $_.Assembly -like '*DuckDB.NET.Data*' }) -ne $null)

Test-True 'DuckDB.NET.Bindings assembly is listed'           (($versions | Where-Object { $_.Assembly -like '*DuckDB.NET.Bindings*' }) -ne $null)

Test-True 'Each entry has a Version property'                (($versions | Where-Object { $null -eq $_.Version }) -eq $null)

# Confirm no duplicate entries (LoadFile would have produced duplicates)

$dataCount = ($versions | Where-Object { $_.Assembly -like '*DuckDB.NET.Data*' } | Measure-Object).Count

Test-Result 'DuckDB.NET.Data appears exactly once'           1  $dataCount

$conn.CloseDB()

# ─────────────────────────────────────────────────────────────────────────────

Write-Host "`n=== NEW: vacuum_rebuild_indexes (ATTACH SQL, v1.5.4+) ===" -ForegroundColor Cyan

# vacuum_rebuild_indexes is an ATTACH-level option, not a connection-string key.

# Verify the AdditionalOptions doc note is reflected in behaviour: passing it

# via connection string should be avoided; using SQL ATTACH is the correct path.

$tmpDb = [System.IO.Path]::Combine([System.IO.Path]::GetTempPath(), "pdb_vri_$(Get-Random).duckdb")

try {

    # Create and populate the database, then close it

    $conn = New-DuckDBConnection -Path $tmpDb

    $conn.sql('CREATE TABLE t (id INT)')

    $conn.CloseDB()

    # Open a separate in-memory connection and ATTACH the file with vacuum_rebuild_indexes.

    # Correct syntax: alias comes BEFORE the option parentheses.

    # (A file cannot be ATTACHed while it is already open as the main database.)

    $conn = New-DuckDBConnection   # in-memory main db

    $conn.sql("ATTACH '$tmpDb' AS vri (vacuum_rebuild_indexes)")

    Test-True 'ATTACH with vacuum_rebuild_indexes succeeds'  ($conn.State -eq 'Open')

    $r = $conn.sql('SELECT COUNT(*) AS n FROM vri.t')

    Test-Result 'Attached table accessible after vacuum_rebuild_indexes'  0  $r.n

    $conn.CloseDB()

} catch {

    $script:failed++

    Write-Host "  FAIL: vacuum_rebuild_indexes ATTACH — $_" -ForegroundColor Red

} finally {

    if (Test-Path $tmpDb) { Remove-Item $tmpDb -ErrorAction SilentlyContinue }

    $wdb = $tmpDb -replace '\.duckdb$', '.wal'

    if (Test-Path $wdb)   { Remove-Item $wdb   -ErrorAction SilentlyContinue }

}

# ─────────────────────────────────────────────────────────────────────────────

Write-Host "`n=== NEW: Module version ===" -ForegroundColor Cyan

$manifest = Import-PowerShellDataFile (Join-Path $PSScriptRoot '..' 'PaperinikDB.psd1')

Test-Result 'Module version is 1.5.4'  '1.5.4'  $manifest.ModuleVersion

Test-True   'Tags include Quack'       ($manifest.PrivateData.PSData.Tags -contains 'Quack')

# ─────────────────────────────────────────────────────────────────────────────

Write-Host "`n=== Summary ===" -ForegroundColor Cyan

Write-Host "Passed:  $passed"  -ForegroundColor Green

if ($skipped -gt 0) { Write-Host "Skipped: $skipped" -ForegroundColor Yellow }

if ($failed -gt 0) {

    Write-Host "Failed:  $failed" -ForegroundColor Red

    exit 1

} else {

    Write-Host 'All tests passed!' -ForegroundColor Green

}