AdoSQLiteModule.psm1
|
# AdoSQLiteModule.psm1 <# Module: AdoSQLiteModule Description: A PowerShell module providing ADO.NET-backed SQLite cmdlets with names similar to PSSQLite, but implemented using ADO.NET (System.Data.SQLite or Microsoft.Data.Sqlite) or sqlite3 CLI fallback. Supported PowerShell versions: 5.1+ (tested) and PowerShell 7+. Exported Cmdlets: - Invoke-AdoSQLiteQuery - Invoke-AdoSQLiteNonQuery - Invoke-AdoSQLiteBulkCopy Usage: See README included in module manifest for install/upload instructions. #> # --- Helper: Load and detect available ADO.NET provider --- function Get-AdoSqliteProvider { <# Returns a hashtable with keys: ProviderName: 'System.Data.SQLite' | 'Microsoft.Data.Sqlite' | 'sqlite3' Assembly: loaded assembly object or $null Types: hashtable mapping 'Connection','Command','Parameter' #> if ($script:__AdoSqliteProvider) { return $script:__AdoSqliteProvider } $prov = @{ ProviderName = $null; Assembly = $null; Types = @{} } if(-not $PSScriptRoot){ $PSScriptRoot = (Split-Path -Parent $MyInvocation.MyCommand.Path) } $asm=[Reflection.Assembly]::LoadFrom((Join-Path $PSScriptRoot 'System.Data.SQLite.dll')) 2>$null if ($asm) { $prov.ProviderName = 'System.Data.SQLite' $prov.Assembly = $asm $prov.Types['Connection'] = $asm.GetType('System.Data.SQLite.SQLiteConnection') $prov.Types['Command'] = $asm.GetType('System.Data.SQLite.SQLiteCommand') $prov.Types['Parameter'] = $asm.GetType('System.Data.SQLite.SQLiteParameter') $script:__AdoSqliteProvider = $prov return $prov } throw "AdoSQLiteModule: No SQLite ADO.NET provider found. Install System.Data.SQLite (PS5.1) or Microsoft.Data.Sqlite (PS7+) or ensure sqlite3 is accessible." } function New-AdoSqliteConnectionString { param( [Parameter(Mandatory)][string] $DatabaseFilePath, [string] $Additional = '' ) return "Data Source=$DatabaseFilePath;$Additional" } # --- Core query execution (returns PSObjects by default) --- function Invoke-AdoSQLiteQuery { <# .SYNOPSIS Execute a SQL query and return results. .PARAMETER Database Path to sqlite database file. .PARAMETER Query SQL text. Use named parameters like @name. .PARAMETER Queries Array of SQL statements to run sequentially. .PARAMETER Parameters Hashtable of parameterName -> value. .PARAMETER AsDataTable Return a System.Data.DataTable object. .PARAMETER AsScalar Return single scalar value from first column of first row. .PARAMETER Timeout Command timeout in seconds. #> [CmdletBinding()] param( [Parameter(Mandatory=$true,Position=0)][string] $Database, [Parameter(Position=1)][string] $Query, [Parameter()][string[]] $Queries, [Parameter()][hashtable] $Parameters, [switch] $AsDataTable, [switch] $AsScalar, [int] $Timeout = 30 ) if (-not $Database) { throw 'Invoke-AdoSQLiteQuery: -Database is required.' } $prov = Get-AdoSqliteProvider $returnMode = if ($AsDataTable) { 'DataTable' } elseif ($AsScalar) { 'Scalar' } else { 'PSObject' } function Exec([string] $sql) { if ($prov.ProviderName -eq 'sqlite3') { $out = & sqlite3 -header -csv $Database $sql 2>&1 if ($LASTEXITCODE -ne 0) { throw "sqlite3 error: $out" } if (-not $out) { return @() } $csv = $out -join "`n" $rows = $csv | ConvertFrom-Csv if ($returnMode -eq 'DataTable') { $dt = New-Object System.Data.DataTable foreach ($c in $rows[0].PSObject.Properties.Name) { [void]$dt.Columns.Add($c) } foreach ($r in $rows) { $nr = $dt.NewRow() foreach ($c in $rows[0].PSObject.Properties.Name) { $nr[$c] = $r.$c } $dt.Rows.Add($nr) } return $dt } elseif ($returnMode -eq 'Scalar') { return $rows[0].PSObject.Properties.Value[0] } else { return ,$rows } } else { $connType = $prov.Types['Connection']; $cmdType = $prov.Types['Command']; $paramType = $prov.Types['Parameter'] $cs = New-AdoSqliteConnectionString -DatabaseFilePath $Database $conn = [Activator]::CreateInstance($connType, $cs) try { $conn.Open() $cmd = [Activator]::CreateInstance($cmdType, $sql, $conn) $cmd.CommandTimeout = $Timeout if ($Parameters) { foreach ($k in $Parameters.Keys) { $pname = if ($k -like '@*') { $k } else { '@' + $k } $pval = $Parameters[$k] try { $p = [Activator]::CreateInstance($paramType, $pname, $pval) $cmd.Parameters.Add($p) | Out-Null } catch { try { $cmd.Parameters.AddWithValue($pname,$pval) | Out-Null } catch { $pp = New-Object -TypeName $paramType $pp.ParameterName = $pname; $pp.Value = $pval $cmd.Parameters.Add($pp) | Out-Null } } } } if ($returnMode -eq 'Scalar') { return $cmd.ExecuteScalar() } $reader = $cmd.ExecuteReader() try { $dt = New-Object System.Data.DataTable $dt.Load($reader) if ($returnMode -eq 'DataTable') { return $dt } $out = foreach ($row in $dt.Rows) { $obj = New-Object PSObject foreach ($col in $dt.Columns) { $obj | Add-Member -NotePropertyName $col.ColumnName -NotePropertyValue $row[$col.ColumnName] } $obj } return ,$out } finally { $reader.Close() } } finally { try { $conn.Close() } catch {}; $conn.Dispose() } } } if ($Queries -and $Queries.Count -gt 0) { $results = @() foreach ($q in $Queries) { $results += Exec -sql $q } return $results } elseif ($Query) { return Exec -sql $Query } else { throw 'Invoke-AdoSQLiteQuery: provide -Query or -Queries.' } } # --- NonQuery wrapper (INSERT/UPDATE/DELETE) --- function Invoke-AdoSQLiteNonQuery { [CmdletBinding()] param( [Parameter(Mandatory=$true,Position=0)][string] $Database, [Parameter(Mandatory=$true,Position=1)][string] $Query, [hashtable] $Parameters, [int] $Timeout = 30 ) if (-not $Database) { throw 'Invoke-AdoSQLiteNonQuery: -Database required.' } $prov = Get-AdoSqliteProvider if ($prov.ProviderName -eq 'sqlite3') { $out = & sqlite3 $Database $Query 2>&1 if ($LASTEXITCODE -ne 0) { throw "sqlite3 error: $out" } return $LASTEXITCODE } else { $connType = $prov.Types['Connection']; $cmdType = $prov.Types['Command']; $paramType = $prov.Types['Parameter'] $cs = New-AdoSqliteConnectionString -DatabaseFilePath $Database $conn = [Activator]::CreateInstance($connType, $cs) try { $conn.Open() $cmd = [Activator]::CreateInstance($cmdType, $Query, $conn) $cmd.CommandTimeout = $Timeout if ($Parameters) { foreach ($k in $Parameters.Keys) { $pname = if ($k -like '@*') { $k } else { '@' + $k } $pval = $Parameters[$k] try { $p = [Activator]::CreateInstance($paramType, $pname, $pval); $cmd.Parameters.Add($p) | Out-Null } catch { try { $cmd.Parameters.AddWithValue($pname,$pval) | Out-Null } catch { $pp = New-Object -TypeName $paramType; $pp.ParameterName=$pname; $pp.Value=$pval; $cmd.Parameters.Add($pp)|Out-Null } } } } $affected = $cmd.ExecuteNonQuery() return $affected } finally { try { $conn.Close() } catch {}; $conn.Dispose() } } } # --- Bulk insert using prepared statement + transaction --- function Invoke-AdoSQLiteBulkCopy { <# .SYNOPSIS Bulk-insert rows into a SQLite table using prepared statements and transaction. .PARAMETER Database Path to sqlite DB. .PARAMETER Table Target table name. .PARAMETER DataTable Can be a System.Data.DataTable, an array of PSObjects, or array of hashtables. .PARAMETER Conflict Conflict behaviour: e.g., 'REPLACE' or 'IGNORE' (will use "INSERT OR <Conflict> ..."). .PARAMETER BatchSize Rows per transaction commit. #> [CmdletBinding()] param( [Parameter(Mandatory=$true,Position=0)][string] $Database, [Parameter(Mandatory=$true,Position=1)][string] $Table, [Parameter(Mandatory=$true,Position=2)][object] $DataTable, [string] $Conflict = '', [int] $BatchSize = 1000, [int] $Timeout = 120 ) if (-not $Database) { throw 'Invoke-AdoSQLiteBulkCopy: -Database required.' } if (-not $Table) { throw 'Invoke-AdoSQLiteBulkCopy: -Table required.' } if (-not $DataTable){ throw 'Invoke-AdoSQLiteBulkCopy: -DataTable required.' } # Normalize rows if ($DataTable -is [System.Data.DataTable]) { $columns = $DataTable.Columns | ForEach-Object { $_.ColumnName } $rowsEnum = $DataTable.Rows } else { $arr = @($DataTable) if ($arr.Count -eq 0) { return 0 } $first = $arr[0] if ($first -is [hashtable]) { $columns = $first.Keys } else { $columns = $first | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty Name } $rowsEnum = $arr } $prov = Get-AdoSqliteProvider if ($prov.ProviderName -eq 'sqlite3') { # CSV import fallback $tmp = Join-Path ([System.IO.Path]::GetTempPath()) ([System.IO.Path]::GetRandomFileName() + '.csv') $table = New-Object System.Data.DataTable foreach ($c in $columns) { $table.Columns.Add($c) | Out-Null } foreach ($r in $rowsEnum) { $nr = $table.NewRow() foreach ($c in $columns) { if ($r -is [System.Data.DataRow]) { $nr[$c] = $r[$c] } elseif ($r -is [hashtable]) { $nr[$c] = if ($r.ContainsKey($c)) { $r[$c] } else { $null } } else { $nr[$c] = $r.$c } } $table.Rows.Add($nr) } $table | Export-Csv -Path $tmp -NoTypeInformation -Encoding UTF8 $cmds = ".mode csv`n.import `"$tmp`" $Table" $out = & sqlite3 $Database $cmds 2>&1 Remove-Item $tmp -ErrorAction SilentlyContinue if ($LASTEXITCODE -ne 0) { throw "sqlite3 import failed: $out" } return $true } else { $connType = $prov.Types['Connection']; $cmdType = $prov.Types['Command']; $paramType = $prov.Types['Parameter'] $cs = New-AdoSqliteConnectionString -DatabaseFilePath $Database $conn = [Activator]::CreateInstance($connType, $cs) try { $conn.Open() $trans = $conn.BeginTransaction() $colList = ($columns -join ', ') $paramList = ($columns | ForEach-Object { '@' + $_ }) -join ', ' $insertSql = if ($Conflict) { "INSERT OR $Conflict INTO $Table ($colList) VALUES ($paramList);" } else { "INSERT INTO $Table ($colList) VALUES ($paramList);" } $cmd = [Activator]::CreateInstance($cmdType, $insertSql, $conn) $cmd.Transaction = $trans $cmd.CommandTimeout = $Timeout # create and reuse parameters $paramMap = @{} foreach ($c in $columns) { $pname = '@' + $c try { $p = [Activator]::CreateInstance($paramType, $pname, $null) } catch { $p = New-Object -TypeName $paramType -ArgumentList $pname, $null } $cmd.Parameters.Add($p) | Out-Null $paramMap[$c] = $p } $count = 0; $rowsAffected = 0 foreach ($r in $rowsEnum) { foreach ($c in $columns) { if ($r -is [System.Data.DataRow]) { $val = $r[$c] } elseif ($r -is [hashtable]) { $val = if ($r.ContainsKey($c)) { $r[$c] } else { $null } } else { $val = $r.$c } if ($null -eq $val) { $paramMap[$c].Value = [System.DBNull]::Value } else { $paramMap[$c].Value = $val } } $rowsAffected += $cmd.ExecuteNonQuery() $count++ if ($count -ge $BatchSize) { $trans.Commit(); $trans = $conn.BeginTransaction(); $cmd.Transaction = $trans; $count = 0 } } $trans.Commit() return $rowsAffected } finally { try { $conn.Close() } catch {}; $conn.Dispose() } } } # Export the public functions function New-AdoSQLiteConnection { [CmdletBinding()] param( [Parameter(Mandatory=$true,Position=0)][string] $Database, [switch] $Open, [int] $Timeout = 30 ) # Returns an opened ADO.NET connection object (or closed if -Open not specified) $prov = Get-AdoSqliteProvider $cs = New-AdoSqliteConnectionString -DatabaseFilePath $Database if ($prov.ProviderName -eq 'sqlite3') { throw 'New-SQLiteConnection: sqlite3 CLI provider does not expose a live connection object.' } $connType = $prov.Types['Connection'] $conn = [Activator]::CreateInstance($connType, $cs) if ($Open) { $conn.Open(); $conn.DefaultCommandTimeout = $Timeout -as [int] } else { } return $conn } function Out-AdoDataTable { <# Convert incoming objects (PSObjects / hashtables / DataRow arrays) into a System.Data.DataTable. Usage: $dt = $collection | Out-DataTable #> [CmdletBinding()] param( [Parameter(ValueFromPipeline=$true)][object] $InputObject, [switch] $InferFromFirst ) begin { $table = New-Object System.Data.DataTable $firstSeen = $false } process { if ($null -eq $InputObject) { return } if ($InputObject -is [System.Data.DataTable]) { $table = $InputObject; $firstSeen = $true; continue } if (-not $firstSeen) { # infer columns from first object if ($InputObject -is [hashtable]) { foreach ($k in $InputObject.Keys) { [void]$table.Columns.Add($k) } } elseif ($InputObject -is [System.Data.DataRow]) { $table = $InputObject.Table.Clone(); $firstSeen = $true } else { $props = $InputObject | Get-Member -MemberType NoteProperty, Property | Select-Object -ExpandProperty Name -Unique foreach ($p in $props) { [void]$table.Columns.Add($p) } } $firstSeen = $true } # add row $row = $table.NewRow() if ($InputObject -is [hashtable]) { foreach ($k in $InputObject.Keys) { $row[$k] = $InputObject[$k] } } elseif ($InputObject -is [System.Data.DataRow]) { $row.ItemArray = $InputObject.ItemArray } else { foreach ($col in $table.Columns) { $row[$col.ColumnName] = $InputObject.$($col.ColumnName) } } $table.Rows.Add($row) } end { return $table } } function Update-AdoSqlite { <# Convenience helper to UPDATE a table using parameters. Parameters: -Database <path> -Table <name> -Set <hashtable> # column->value -Where <string> # WHERE clause (without 'WHERE') optionally containing parameter names like @id -Parameters <hashtable> # extra parameters for WHERE or others Returns: number of rows affected #> [CmdletBinding()] param( [Parameter(Mandatory=$true)][string] $Database, [Parameter(Mandatory=$true)][string] $Table, [Parameter(Mandatory=$true)][hashtable] $Set, [string] $Where, [hashtable] $Parameters, [int] $Timeout = 30 ) if (-not $Set -or $Set.Count -eq 0) { throw 'Update-Sqlite: -Set must contain at least one column/value.' } $setPairs = @() $paramHash = @{} foreach ($k in $Set.Keys) { $pname = '@' + $k $setPairs += "$k = $pname" $paramHash[$k] = $Set[$k] } if ($Parameters) { foreach ($k in $Parameters.Keys) { $paramHash[$k] = $Parameters[$k] } } $setClause = $setPairs -join ', ' $whereClause = if ($Where) { "WHERE $Where" } else { '' } $sql = "UPDATE $Table SET $setClause $whereClause;" # Use existing non-query wrapper return Invoke-AdoSQLiteNonQuery -Database $Database -Query $sql -Parameters $paramHash -Timeout $Timeout } |