Private/Sqlite.ps1
|
# Reading a table out of an SQLite file, with the SQLite that ships with Windows. # # Several programs keep what a Check wants to read in an SQLite file: a VPN client its # log, a sync client its list of files that failed. Windows 10 and later carry SQLite as # winsqlite3.dll, so nothing has to be shipped or installed to read one. # # A file is opened as one nobody writes to ("immutable"), and that is not a choice. # Observed 2026-10-13: a log in a folder a Run may not write to could not be opened any # other way, because SQLite wants to put a lock beside the file; and a sync client holds # its file locked against every other reader. Opened as immutable, SQLite takes no lock # and writes nothing - which is also what a Check owes the machine. The price is that # what the program has written since it last tidied its file is not seen: a reading is # as old as the file's last change, and whoever reads says so. # The few calls of SQLite that reading a table takes. Open is with the flags for "read # only" and "the name is a URI" (0x41), so that the name can say "immutable". $script:SqliteDefinition = @( 'using System; using System.Collections.Generic; using System.Runtime.InteropServices; using System.Text;' 'namespace Gutcheck { public static class SqliteNative {' ' const string Dll = "winsqlite3.dll";' ' [DllImport(Dll, CallingConvention = CallingConvention.StdCall)] static extern int sqlite3_open_v2(byte[] name, out IntPtr db, int flags, IntPtr vfs);' ' [DllImport(Dll, CallingConvention = CallingConvention.StdCall)] static extern int sqlite3_close(IntPtr db);' ' [DllImport(Dll, CallingConvention = CallingConvention.StdCall)] static extern int sqlite3_prepare_v2(IntPtr db, byte[] sql, int n, out IntPtr stmt, IntPtr tail);' ' [DllImport(Dll, CallingConvention = CallingConvention.StdCall)] static extern int sqlite3_step(IntPtr stmt);' ' [DllImport(Dll, CallingConvention = CallingConvention.StdCall)] static extern int sqlite3_finalize(IntPtr stmt);' ' [DllImport(Dll, CallingConvention = CallingConvention.StdCall)] static extern int sqlite3_column_count(IntPtr stmt);' ' [DllImport(Dll, CallingConvention = CallingConvention.StdCall)] static extern IntPtr sqlite3_column_text(IntPtr stmt, int i);' ' [DllImport(Dll, CallingConvention = CallingConvention.StdCall)] static extern int sqlite3_column_bytes(IntPtr stmt, int i);' ' static byte[] Z(string s) { byte[] b = Encoding.UTF8.GetBytes(s); byte[] z = new byte[b.Length + 1]; Array.Copy(b, z, b.Length); return z; }' ' public static List<string[]> Query(string name, string sql) {' ' IntPtr db; int rc = sqlite3_open_v2(Z(name), out db, 0x41, IntPtr.Zero);' ' if (rc != 0) { sqlite3_close(db); throw new InvalidOperationException("SQLite " + rc); }' ' List<string[]> rows = new List<string[]>();' ' try { IntPtr st; rc = sqlite3_prepare_v2(db, Z(sql), -1, out st, IntPtr.Zero);' ' if (rc != 0) throw new InvalidOperationException("SQLite " + rc);' ' try { int n = sqlite3_column_count(st);' ' while ((rc = sqlite3_step(st)) == 100) { string[] r = new string[n];' ' for (int i = 0; i < n; i++) { IntPtr p = sqlite3_column_text(st, i); int len = sqlite3_column_bytes(st, i);' ' if (p == IntPtr.Zero) { r[i] = ""; } else { byte[] buf = new byte[len]; Marshal.Copy(p, buf, 0, len); r[i] = Encoding.UTF8.GetString(buf); } }' ' rows.Add(r); }' ' if (rc != 101) throw new InvalidOperationException("SQLite " + rc);' ' } finally { sqlite3_finalize(st); }' ' } finally { sqlite3_close(db); }' ' return rows; } } }' ) -join "`n" function Read-SqliteFile { <# .SYNOPSIS The rows a question returns from an SQLite file, each as its columns in text. The file is opened as one nobody writes to: no lock is taken and nothing is written. .DESCRIPTION Throws 'NoSqlite' on a Windows that does not carry winsqlite3.dll, and what SQLite said where the file would not open or the question was not understood. The question is the caller's to build, and to build from nothing a Check Definition could have put a quotation mark into. #> [CmdletBinding()] [OutputType([string[]])] param([Parameter(Mandatory)][string]$Path, [Parameter(Mandatory)][string]$Query) if (-not (Test-Path -LiteralPath (Join-Path ([Environment]::SystemDirectory) 'winsqlite3.dll'))) { throw 'NoSqlite' } if (-not ('Gutcheck.SqliteNative' -as [type])) { Add-Type -TypeDefinition $script:SqliteDefinition } $name = ([uri]$Path).AbsoluteUri + '?immutable=1' foreach ($row in [Gutcheck.SqliteNative]::Query($name, $Query)) { , $row } } |