Using winsqlite3.dll with PowerShell

The sources are hosted in this Github repository and consist of

PInvoke.ps1 : the wrappers for SQLite API
classes.ps1 : two classes, sqliteDB and sqliteStmt
kernel32.ps1 : for GetModuleHandle and GetProcAddress
test/* : Some tests (such as test/simple.ps1) to test and demonstrate the functionality of the previous three files
init.ps1: A simple script that sources the first three files that are required to use this library.

Simple test script

The following test script tries to demonstrate how the two classes sqliteDB and sqliteStmt can be used to

create an SQLite database
create a table
insert values into the table with bind variables
select some of the values from the able
finalize a statement
close a database

<#
   Tests for accessing the winsqlite3.dll

   Version 0.04
#>

set-strictMode -version latest

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

[sqliteDB]::version()

#
#  $db.exec writes warning if statement has error.
#
$db.exec('create table tab(foo, bar, baz' ) # incomplete input
$db.exec('create table tab(foo, bar, baz)')
$db.exec('create table tab(foo, bar, baz)') # table tab already exists

[sqliteStmt] $stmt = $db.prepareStmt('insert into tab values(?, ?, ?)')

$stmt.bind(1,  4 )
$stmt.bind(2, 'A')
$stmt.bind(3,  333)
$null = $stmt.step()

$stmt.reset()
$stmt.bind(1,  88)
$stmt.bind(2, 'BB')
$stmt.bind(3,  $null)
$null = $stmt.step()

$stmt.reset()
$stmt.bind(1,  111)
$stmt.bind(2, 'III')
$stmt.bind(3,  42)
$null = $stmt.step()

$stmt.reset()
$stmt.bindArrayStepReset( ( 44  ,'AA'  ,  99 ))
$stmt.bindArrayStepReset( (444 , 'AAA' , 999 ))

$db.exec('begin transaction')
$stmt.bindArrayStepReset( (555 , 'SSS' , 'trx') )
$stmt.bindArrayStepReset( (333 , 'EEE' , 'trx') )
$db.exec('commit')

$db.exec('begin transaction')
$stmt.bindArrayStepReset( (500 , 'Soo' , 'trx!') )
$stmt.bindArrayStepReset( (300 , 'Eoo' , 'trx!') )
$db.exec('rollback')

$stmt.finalize()

$stmt = $db.prepareStmt('select * from tab where foo > ? order by foo')

write-host "column count of stmt: $($stmt.column_count())"

$stmt.Bind(1, 50)

while ( $stmt.step()  -ne [sqlite]::DONE ) {
   echo "$($stmt.col(0)) | $($stmt.col(1)) | $($stmt.col(2))"
}

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

Github repository winsqlite3.dll-PowerShell, path: /test/simple.ps1

PInvoke.ps1

PInvoke.ps1 creates a the .NET class sqlstmt that consists of P/Invoke wrappers for the SQLite API.

These wrappers all look similar to

[DllImport("winsqlite3.dll", EntryPoint="sqlite3_bind_null")]
 public static extern IntPtr bind_null (
        IntPtr    stmt,
        IntPtr    index
 );

The source code is here

classes.ps1

classes.ps1 defines the two PowerShell classes sqliteDB and sqliteStmt.

It also defines two helper functions, charPtrToString and strToCharPtr which were needed because the SQLite API requires strings to be passed on the heap. (I am not sure if it was really necessary, but I found no other working way than this hack).

The source code is here.

kernel32.ps1

In order to read the version string from the DLL, I found no other way than to use GetModuleHandle and GetProcAddress which are found in the kernel32.dll.

add-type -typeDefinition @'

using System;
using System.Runtime.InteropServices;

public static class kernel32 {

  [DllImport("kernel32.dll", CharSet=CharSet.Auto)]
   public static extern IntPtr GetModuleHandle(string lpModuleName);

  [DllImport("kernel32", CharSet=CharSet.Ansi, ExactSpelling=true, SetLastError=true)]
   public static extern IntPtr GetProcAddress(IntPtr hModule, string procName);

}
'@

Github repository winsqlite3.dll-PowerShell, path: /kernel32.ps1

TODO

If [sqliteDB]::new(…) is invoked with a relative path, the database file is not found. Thus, relative paths should be merged with $pwd to obtain an absolute path.

The method bindArrayStepReset() currently needs to be called with one parameter (which is an array) $stmt.bindArrayStepReset( ( $val_1, $val_2, $val_3 ) ). It would be nice if the method could be called with multiples arguments: $stmt.bindArrayStepReset( $val_1, $val_2, $val_3 ).

The value of $handle in the sqliteStmt class might be set to 0 when its finalize() method is called.

sqliteStmt should probably implement System.Collections.IEnumerable so that it is possible to iterate over a select statement with a foreach statement.

The currently implemented blob interface only supports 32-bit lengths (sqlite3_bind_blob()). Sooner or later, sqlite3_bind_blob64() will probably be necessary.

However, it's not clear if this at all possible because there is only a sqlite3_column_bytes() function, and it returns an int. There should be, imho, a function that returns a 64-bit number when determining the size of a blob.

Done

2020-03-22: Implemented [sqliteDB]::version()

2020-03-28: Implemented sqlite3_column_count()

2020-04-02: Added the error message constant SQLITE_RANGE

2020-04-13: Implemented last_insert_rowid()

2020-04-16: Add support for 64-bit integers and doubles (reals)

2020-04-17: Added support for System.Boolean ([Bool]) in the bind method of classes.ps1. $true is inserted as the (32-bit) integer 1, $false as 0. Also added the respective tests in datatypes.ps1 test script.

2020-04-20: Version 0.06: Added better support for UTF-8. The respective test case is here.

2020-04-24: Added init.ps1

2020-04-24: Implemented sqlite3_next_stmt() and the corresponding (hidden) method [sqliteStmt]::nextStmt. This allows to iterate over all open statements and then finalize them in the [sqliteDB]::close() method.

The test case for this functionality is here.

2020-04-26: Added support for Blobs (Version 0.08)

2020-09-06: Wrote show-schema.ps1, a simple script to show the schema of an SQLite database.