tests/oracle/Invoke-DBOQuery.Tests.ps1

Param (
    [switch]$Batch
)

if ($PSScriptRoot) { $commandName = $MyInvocation.MyCommand.Name.Replace(".Tests.ps1", ""); $here = $PSScriptRoot }
else { $commandName = "_ManualExecution"; $here = (Get-Item . ).FullName }

if (!$Batch) {
    # Is not a part of the global batch => import module
    #Explicitly import the module for testing
    Import-Module "$here\..\..\dbops.psd1" -Force; Get-DBOModuleFileList -Type internal | ForEach-Object { . $_.FullName }
}
else {
    # Is a part of a batch, output some eye-catching happiness
    Write-Host "Running Oracle $commandName tests" -ForegroundColor Cyan
}

. "$here\..\constants.ps1"
# install Oracle libs if needed
if (-not (Test-DBOSupportedSystem -Type Oracle)) {
    Install-DBOSupportLibrary -Type Oracle -Force -Scope CurrentUser 3>$null
}

$newDbName = 'test_dbops_invokedboquery'
$connParams = @{
    SqlInstance = $script:oracleInstance
    Credential  = $script:oracleCredential
    Type        = 'Oracle'
    Silent      = $true
    ConnectionAttribute = @{
        'DBA Privilege' = 'SYSDBA'
    }
}

Describe "Invoke-DBOQuery Oracle tests" -Tag $commandName, IntegrationTests {
    Context "Regular tests" {
        It "should run the query" {
            $query = "SELECT 1 AS A, 2 AS B FROM DUAL UNION ALL SELECT NULL AS A, 4 AS B FROM DUAL"
            $result = Invoke-DBOQuery -Query $query @connParams -As DataTable
            $result.Columns.ColumnName | Should -Be @('A','B')
            $result.A | Should -Be 1, ([DBNull]::Value)
            $result.B | Should -Be 2, 4
        }
        It "should select NULL" {
            $query = "SELECT NULL FROM DUAL"
            $result = Invoke-DBOQuery -Query $query @connParams -As DataTable
            $result.Columns.ColumnName | Should -Be @('NULL')
            $result.NULL | Should -Be ([DBNull]::Value)
        }
        It "should run the query with semicolon" {
            $query = "SELECT 1 AS A, 2 AS B FROM DUAL;
            SELECT 3 AS A, 4 AS B FROM DUAL"

            $result = Invoke-DBOQuery -Query $query @connParams -As DataTable
            $result[0].Columns.ColumnName | Should -Be @('A','B')
            $result[1].Columns.ColumnName | Should -Be @('A','B')
            $result[0].A | Should -Be 1
            $result[0].B | Should -Be 2
            $result[1].A | Should -Be 3
            $result[1].B | Should -Be 4
        }
        It "should run the query with semicolon as a dataset" {
            $query = "SELECT 1 AS A, 2 AS B FROM DUAL;
            SELECT 3 AS A, 4 AS B FROM DUAL"

            $result = Invoke-DBOQuery -Query $query @connParams -As Dataset
            $result.Tables[0].Columns.ColumnName | Should Be @('A','B')
            $result.Tables[1].Columns.ColumnName | Should Be @('A','B')
            $result.Tables[0].A | Should -Be 1
            $result.Tables[0].B | Should -Be 2
            $result.Tables[1].A | Should -Be 3
            $result.Tables[1].B | Should -Be 4
        }
        It "should run the query as a PSObject" {
            $query = "SELECT 1 AS A, 2 AS B FROM DUAL UNION ALL SELECT NULL AS A, 4 AS B FROM DUAL"
            $result = Invoke-DBOQuery -Query $query @connParams -As PSObject
            $result[0].psobject.properties.Name | Should -Be @('A','B')
            $result.A | Should -Be 1, $null
            $result.B | Should -Be 2, 4
        }
        It "should run the query as a SingleValue" {
            $query = "SELECT 1 AS A FROM DUAL"
            $result = Invoke-DBOQuery -Query $query @connParams -As SingleValue
            $result | Should -Be 1
        }
        It "should run the query from InputFile" {
            $file1 = Join-Path 'TestDrive:' 1.sql
            $file2 = Join-Path 'TestDrive:' 2.sql
            "SELECT 1 AS A, 2 AS B FROM DUAL" | Out-File $file1 -Force
            "SELECT 3 AS A, 4 AS B FROM DUAL" | Out-File $file2 -Force -Encoding bigendianunicode
            $result = Invoke-DBOQuery -InputFile $file1, $file2 @connParams -As DataTable
            $result[0].Columns.ColumnName | Should -Be @('A','B')
            $result[1].Columns.ColumnName | Should -Be @('A','B')
            $result[0].A | Should -Be 1
            $result[0].B | Should -Be 2
            $result[1].A | Should -Be 3
            $result[1].B | Should -Be 4
        }
        It "should run the query from InputObject" {
            $file1 = Join-Path 'TestDrive:' 1.sql
            $file2 = Join-Path 'TestDrive:' 2.sql
            "SELECT 1 AS A, 2 AS B FROM DUAL" | Out-File $file1 -Force
            "SELECT 3 AS A, 4 AS B FROM DUAL" | Out-File $file2 -Force -Encoding bigendianunicode
            $result = Get-Item $file1, $file2 | Invoke-DBOQuery @connParams -As DataTable
            $result[0].Columns.ColumnName | Should -Be @('A','B')
            $result[1].Columns.ColumnName | Should -Be @('A','B')
            $result[0].A | Should -Be 1
            $result[0].B | Should -Be 2
            $result[1].A | Should -Be 3
            $result[1].B | Should -Be 4
            $result = $file1, $file2 | Invoke-DBOQuery @connParams -As DataTable
            $result[0].Columns.ColumnName | Should -Be @('A','B')
            $result[1].Columns.ColumnName | Should -Be @('A','B')
            $result[0].A | Should -Be 1
            $result[0].B | Should -Be 2
            $result[1].A | Should -Be 3
            $result[1].B | Should -Be 4
        }
        It "should run the query with custom variables" {
            $query = "SELECT '#{Test}' AS A, '#{Test2}' AS B FROM DUAL UNION ALL SELECT '3' AS A, '4' AS B FROM DUAL"
            $result = Invoke-DBOQuery -Query $query @connParams -As DataTable -Variables @{ Test = '1'; Test2 = '2'}
            $result.Columns.ColumnName | Should -Be @('A','B')
            $result.A | Should -Be '1', '3'
            $result.B | Should -Be '2', '4'
        }
        It "should connect to the server from a custom variable" {
            $query = "SELECT 1 AS A, 2 AS B FROM DUAL UNION ALL SELECT 3 AS A, 4 AS B FROM DUAL"
            $result = Invoke-DBOQuery -Type Oracle -Query $query -SqlInstance '#{srv}' -Credential $script:oracleCredential -As DataTable -Variables @{ srv = $script:oracleInstance } -ConnectionAttribute @{
                'DBA Privilege' = 'SYSDBA'
            }
            $result.Columns.ColumnName | Should -Be @('A','B')
            $result.A | Should -Be '1', '3'
            $result.B | Should -Be '2', '4'
        }
        It "should run the query with custom parameters" {
            $query = "SELECT :p1 AS A, :p2 AS B FROM DUAL"
            $result = Invoke-DBOQuery -Query $query @connParams -Parameter @{ p1 = '1'; p2 = 'string'}
            $result.A | Should -Be 1
            $result.B | Should -Be string
        }
        It "should address column names automatically" {
            $query = "SELECT 1 AS A, 2, 3 FROM DUAL"
            $result = Invoke-DBOQuery -Query $query @connParams -As DataTable
            $result.Columns.ColumnName | Should -Be @('A', '2', '3')
            $result.A | Should Be 1
            $result.2 | Should Be 2
            $result.3 | Should Be 3
        }
    }
    Context "Negative tests" {
        It "should throw an unknown table error" {
            $query = "SELECT * FROM nonexistent"
            { $null = Invoke-DBOQuery -Query $query @connParams } | Should throw 'table or view does not exist'
        }
        It "should throw a connection timeout error" {
            $query = "SELECT 1/0 FROM DUAL"
            try { $null = Invoke-DBOQuery -Type Oracle -Query $query -SqlInstance localhost:6493 -Credential $script:oracleCredential -ConnectionTimeout 1}
            catch { $errVar = $_ }
            $errVar.Exception.Message | Should -Match "Connection request timed out"
        }
        It "should fail when credentials are wrong" {
            try { Invoke-DBOQuery -Type Oracle -Query 'SELECT 1 FROM DUAL' -SqlInstance $script:oracleInstance -Credential ([pscredential]::new('nontexistent', ([securestring]::new()))) }
            catch {$errVar = $_ }
            $errVar.Exception.Message | Should -Match 'null password given; logon denied'
            try { Invoke-DBOQuery -Type Oracle -Query 'SELECT 1 FROM DUAL' -SqlInstance $script:oracleInstance -UserName nontexistent -Password (ConvertTo-SecureString 'foo' -AsPlainText -Force) }
            catch {$errVar = $_ }
            $errVar.Exception.Message | Should -Match 'invalid username/password; logon denied'
        }
        It "should fail when input file is not found" {
            { Invoke-DBOQuery -InputFile '.\nonexistent' @connParams } | Should throw 'Cannot find path'
            { '.\nonexistent' | Invoke-DBOQuery @connParams } | Should throw 'Cannot find path'
        }
    }
}