Tests for the PowerShell Wrapper for winsqlite3.dll

This test inserts integers (also 64-bit), a string, a double and a $null value and then selects them again. A warning is written if the selected values are different from the inserted ones.

#
#  Version 0.05
#
set-strictMode -version latest

function compare_next_row($stmt, $expected_id, $expected_val) {

   if ($stmt.step() -eq [sqlite]::DONE) {
      write-warning 'DONE not expected'
   }
   if ($stmt.col(0) -ne $expected_id) {
      write-warning "expected id $expected_id, got $($stmt.col(0))"
   }
   if ($stmt.col(1) -ne $expected_val) {
      write-warning "expected val $expected_val, got $($stmt.col(1))"
   }
}


[sqliteDB] $db = [sqliteDB]::new("$($pwd)\datatypes.db", $true)

$db.exec('create table T (
    id   integer primary key,
    val
)')


$stmtIns = $db.prepareStmt('insert into T values (?, ?)');

[byte[]] $byteArray = 42, 99, 255, 2, 18, 37, 0, 127, 222, 199

$stmtIns.bindArrayStepReset( ( 1,                  1 ))
$stmtIns.bindArrayStepReset( ( 2,                 -2 ))
$stmtIns.bindArrayStepReset( ( 3,        0x987654321 )) # 64 bit number
$stmtIns.bindArrayStepReset( ( 4, 0xffffffffffffffff )) # 64 bit number
$stmtIns.bindArrayStepReset( ( 5,'0xfedcba9876543210')) # String
$stmtIns.bindArrayStepReset( ( 6,              12.34 )) # Real (Double)
$stmtIns.bindArrayStepReset( ( 7,              $null ))
$stmtIns.bindArrayStepReset( ( 8,              $true ))
$stmtIns.bindArrayStepReset( ( 9,              $false))
$stmtIns.bindArrayStepReset( (10,              $true ))
$stmtIns.bindArrayStepReset( (11,              $false))
$stmtIns.bindArrayStepReset( (12, $byteArray         ))


$stmtIns.finalize()

$stmtSel = $db.prepareStmt('select * from T order by id')

compare_next_row $stmtSel  1                   1
compare_next_row $stmtSel  2                  -2
compare_next_row $stmtSel  3         0x987654321
compare_next_row $stmtSel  4                  -1    # -1 == 0xffffffffffffffff in 64-bit
compare_next_row $stmtSel  5 '0xfedcba9876543210'
compare_next_row $stmtSel  6               12.34
compare_next_row $stmtSel  7               $null
compare_next_row $stmtSel  8               $true
compare_next_row $stmtSel  9               $false
compare_next_row $stmtSel 10                   1   # $true  is stored as 1
compare_next_row $stmtSel 11                   0   # $false is stored as 0

if ($stmtSel.step() -eq [sqlite]::DONE) {
   write-warning 'DONE not expected'
}
if ($stmtSel.col(0) -ne 12) {
   write-warning "expected id 12, got $($stmtSel.col(0))"
}

if ($stmtSel.column_type(1) -ne [sqlite]::BLOB ) {
   write-warning "expected BLOB"
}

$arySel = $stmtSel.col(1)
if (-not ($arySel -is [Byte[]])) {
    write-warning "expected byte array, got $($arySel.GetType().FullName)"
}

if ($arySel.length -ne $byteArray.length) {
   write-warning "byte array length differs: $($arySel.length) -ne $($byteArray.length)"
}

for ($i = 0; $i -lt $arySel.length; $i++ ) {

  if ($arySel[$i].GetType().FullName -ne 'System.Byte') {
      write-warning $arySel[$i].GetType().FullName
  }
  if ($arySel[$i] -ne $byteArray[$i]) {
      write-warning "Byte $i differs ($($arySel[$i]) <> $($byteArray[$i]))"
  }
}

if ($stmtSel.step() -ne [sqlite]::DONE) {
   write-warning 'DONE expected'
}


$stmtSel.finalize()

$db.close()

last_insert_rowid.ps1 tests the functionality of sqlite3_last_insert_rowid(). See also rowid.

<#

   Test for last_insert_rowid()

#>

set-strictMode -version latest

function compare_next_row($stmt, $expected_id, $expected_val) {

   if ($stmt.step() -eq [sqlite]::DONE) {
      write-warning 'DONE not expected'
   }
   if ($stmt.col(0) -ne $expected_id) {
      write-warning "expected $expected_id"
   }
   if ($stmt.col(1) -ne $expected_val) {
      write-warning "expected $expected_val"
   }

}

[sqliteDB] $db = [sqliteDB]::new("$($pwd)\last_insert_rowid.db", $true)

$db.exec('create table T (
    id   integer primary key,
    val  text
)')

$db.exec("insert into T (val) values ('foo')");
if ($db.last_insert_rowid() -ne 1) {
   write-warning 'expected rowid 1'
}

$db.exec("insert into T values (null, 'bar')");
if ($db.last_insert_rowid() -ne 2) {
   write-warning 'expected rowid 2'
}

$db.exec("insert into T values (42, 'baz')");
if ($db.last_insert_rowid() -ne 42) {
   write-warning 'expected rowid 42'
}

$db.exec("insert into T (val) values ('next')");
if ($db.last_insert_rowid() -ne 43) {
   write-warning 'expected rowid 43'
}

$stmt = $db.prepareStmt('select * from T order by id')

compare_next_row $stmt  1 'foo'
compare_next_row $stmt  2 'bar'
compare_next_row $stmt 42 'baz'
compare_next_row $stmt 43 'next'

if ($stmt.step() -ne [sqlite]::DONE) {
   write-warning 'expected: DONE'
}

$stmt.finalize()
$db.close()

This test consists of two files that have the same content but have a different encoding: encoding-utf8.ps1 is UTF-8 encoded while encoding-latin1.ps1 is latin 1 encoded.

These files insert some German umlaute and selects them again to test if they're equal.

In my tests, I found that encoding-latin1.ps1 succeeds in PowerShell 5.1 and that encoding-utf8.ps1 succeeds in both, PowerShell 5.1 and PowerShell 7.1.

set-strictMode -version latest

function compare_row($stmt, $id, $expected_val, $expected_length) {

   $stmt.reset()
   $stmt.Bind(1, $id)

   if ($stmt.step() -eq [sqlite]::DONE) {
      write-warning 'DONE not expected'
   }

   if ($stmt.col(0) -ne $expected_val) {
      write-warning "id = $id`: expected val: $expected_val, got $($stmt.col(0))"
   }
   if ($stmt.col(1) -ne $expected_length) {
      write-warning "id = $id`: expected length $expected_length, got $($stmt.col(1))"
   }

   if ($stmtSel.step() -ne [sqlite]::DONE) {
      write-warning "expected: DONE"
   }
}

[sqliteDB] $db = [sqliteDB]::new("$($pwd)\encoding.db", $true)


$db.exec('create table T (
    id   integer primary key,
    txt  text
)')

$db.exec("insert into T values(1, 'aouAOUBe')")
$db.exec("insert into T values(2, 'äöüÄÖÜßé')")

$stmtIns = $db.prepareStmt('insert into T values (?, ?)');
$stmtIns.bindArrayStepReset( ( 3, 'eBUOAuoa' ) )
$stmtIns.bindArrayStepReset( ( 4, 'éßÜÖÄüöä' ) )
$stmtIns.finalize()

$stmtSel = $db.prepareStmt('select txt, length(txt) len from T where id = ?')

compare_row $stmtSel 1 'aouAOUBe' 8
compare_row $stmtSel 2 'äöüÄÖÜßé' 8
compare_row $stmtSel 3 'eBUOAuoa' 8
compare_row $stmtSel 4 'éßÜÖÄüöä' 8

$stmtSel.finalize()

$db.close()

This test opens two statement handles and the closes the database without finalizing them. Thus, it tests if [sqliteDB]::close() closes all remaning open statement handles by using [sqliteDB]::nextStmt().

# vi: ft=conf
set-strictMode -version latest

[sqliteDB] $db = [sqliteDB]::new("$($pwd)\registry.db", $true)

$db.exec('create table T(c)')

[sqliteStmt] $stmt_1 = $db.prepareStmt('insert into T values (?)'   )
[sqliteStmt] $stmt_2 = $db.prepareStmt('select * from T where c > ?')

[IntPtr] $stmt_handle_1 = $db.nextStmt(0)
[IntPtr] $stmt_handle_2 = $db.nextStmt($stmt_handle_1)
[IntPtr] $stmt_handle_  = $db.nextStmt($stmt_handle_2)

#
# Of course, I just assume that nextStmt returns the
# handles in reverse order of how they were created.
#
if ($stmt_handle_1 -ne $stmt_2.handle) {
   write-warning "handles of statement 1 differ"
}

if ($stmt_handle_2 -ne $stmt_1.handle) {
   write-warning "handles of statement 2 differ"
}

if ($stmt_handle_ -ne 0) {
   write-warning "expected statement handle of 0"
}

$db.close()