mySQLite

0.11.2

functions/private.ps1


#region Private functions
Function resolvedb {
    [cmdletbinding()]
    Param([string]$Path)

    Write-Verbose "[$((Get-Date).TimeOfDay)] ResolveDB Resolving $path"
    #resolve or convert path into a full filesystem path
    $path = $ExecutionContext.SessionState.path.GetUnresolvedProviderPathFromPSPath($path)
    [PSCustomObject]@{
        Path   = $path
        Exists = Test-Path -Path $path
    }
    Write-Verbose "[$((Get-Date).TimeOfDay)] ResolveDB Resolved to $Path"
}
Function opendb {
    [cmdletbinding()]
    Param([string]$Path)

    $ConnectionString = "Data Source=$Path;Version=3"
    Write-Verbose "[$((Get-Date).TimeOfDay)] OpenDB Using connection string: $ConnectionString"
    $connection = New-Object System.Data.SQLite.SQLiteConnection -ArgumentList $ConnectionString
    $connection.Open()
    $connection
}


Function closedb {
    [cmdletbinding()]
    Param(
        [System.Data.SQLite.SQLiteConnection]$connection,
        [System.Data.SQLite.SQLiteCommand]$cmd
    )
    if ($connection.state -eq 'Open') {
        Write-Verbose "[$((Get-Date).TimeOfDay)] CloseDB Closing database connection"
        if ($cmd) {
            $cmd.Dispose()
        }
        $connection.close()
        $connection.Dispose()
    }
}

Function buildquery {
    [cmdletbinding()]
    Param(
        [parameter(Mandatory)]
        [object]$InputObject,
        [parameter(Mandatory)]
        [string]$TableName
    )
    Begin {
        Write-Verbose "[$((Get-Date).TimeOfDay)] Starting $($MyInvocation.MyCommand)"
    } #begin

    Process {
        #9/9/2022 Need to insert property names with a dash in []
        #this should fix Issue #14 JDH
        $list = [System.Collections.Generic.list[string]]::new()
        foreach ($n in $InputObject.PSObject.properties.name) {
            if ($n -match "^\S+\-\S+$") {
             # write-host "REPLACE DASHED $n" -ForegroundColor RED
                $n =   "[{0}]" -f $matches[0]
            }
           # Write-host "ADDING $n" -ForegroundColor CYAN
            $list.add($n)
        }
        $names = $list -join ","
        #$names = $InputObject.PSObject.Properties.name -join ","

        $InputObject.PSObject.Properties | ForEach-Object -Begin {
            $arr = [System.Collections.Generic.list[string]]::new()
        } -Process {
            if ($_.TypeNameOfValue -match "String|Int\d{2}|Double|DateTime|Long") {
                #9/12/2022 need to escape values that might have single quote
                $v = $_.Value -replace "'","''"
                $arr.Add(@(, $v))
            }
            elseif ($_.TypeNameOfValue -match "Boolean") {
                #turn Boolean into an INT
                $arr.Add(@(, ($_.value -as [int])))
            }
            else {
                #only create an entry if there is a value
                if ($null -ne $_.value) {
                    Write-Verbose "[$((Get-Date).TimeOfDay)] Creating cliXML for a blob"
                    $in = ($_.value | ConvertTo-CliXml) -replace "'","''"
                    $arr.Add(@(, "$($in)"))
                }
                else {
                    $arr.Add("")
                }
            }
        }
        $values = $arr -join "','"
      # If ($names.split(".").count -eq ($values -split "','").count) {
             "Insert Into $TableName ($names) values ('$values')"
             #$global:q= "Insert Into $TableName ($names) values ('$values')"
             #$global:n = $names
             #$global:v = $values
       # }
        # else {
        # Write-Warning "There is a mismatch between the number of column headings ($($names.split(".").count)) and values ($(($values -split "','").count))"
        # }
    } #process

    End {
        Write-Verbose "[$((Get-Date).TimeOfDay)] Ending $($MyInvocation.MyCommand)"

    } #end

} #close buildquery


Function frombytes {
    [cmdletbinding()]
    Param([byte[]]$Bytes)

    #only process if there are bytes
    # Issue #3 7/20/2022 JDH
    if ($bytes.count -gt 0) {
        Write-Verbose "[$((Get-Date).TimeOfDay)] Converting from bytes to object"
        $tmpFile = [system.io.path]::GetTempFileName()
        [text.encoding]::UTF8.GetString($bytes) | Out-File -FilePath $tmpfile -Encoding utf8
        Import-Clixml -Path $tmpFile
        if (Test-Path $tmpfile) {
            Remove-Item $tmpFile
        }
    }
}

# cliXML functions added 20 Feb 2023 from Issue #16
# functions authored by https://github.com/SeSeKenny
function ConvertTo-CliXml {
    [CmdletBinding()]
    param (
        [Parameter(ValueFromPipeline=$true)]
        $Object
    )
    begin {
        $Objects=@()
    }
    process {
        $Objects+=$Object
    }
    end {
        if ($Objects.Count -eq 1) {$Objects=$Objects[0]}
        [System.Management.Automation.PSSerializer]::Serialize($Objects)
    }
}

function ConvertFrom-CliXml {
    [CmdletBinding()]
    param (
        [Parameter(ValueFromPipeline)]
        $Object
    )
    begin {
        $Objects=@()
    }
    process {
        $Objects+=$Object
    }
    end {
        [System.Management.Automation.PSSerializer]::Deserialize($Objects)
    }
}

#endregion

<#
archived
 
Function OLD-buildquery {
    [cmdletbinding()]
    Param(
        [parameter(Mandatory)]
        [object]$InputObject,
        [parameter(Mandatory)]
        [string]$TableName
    )
    Begin {
        Write-Verbose "[$((Get-Date).TimeOfDay)] Starting $($MyInvocation.MyCommand)"
    } #begin
 
    Process {
        #9/9/2022 Need to insert property names with a dash in []
        #this should fix Issue #14 JDH
        $list = [System.Collections.Generic.list[string]]::new()
        foreach ($n in $InputObject.PSObject.properties.name) {
            if ($n -match '^\S+\-\S+$') {
                # write-host "REPLACE DASHED $n" -ForegroundColor RED
                $n = '[{0}]' -f $matches[0]
            }
            # Write-host "ADDING $n" -ForegroundColor CYAN
            $list.add($n)
        }
        $names = $list -join ','
        #$names = $InputObject.PSObject.Properties.name -join ","
 
        $InputObject.PSObject.Properties | ForEach-Object -Begin {
            $arr = [System.Collections.Generic.list[string]]::new()
        } -Process {
            if ($_.TypeNameOfValue -match 'String|Int\d{2}|Double|DateTime|Long') {
                #9/12/2022 need to escape values that might have single quote
                $v = $_.Value -replace "'", "''"
                $arr.Add(@(, $v))
            }
            elseif ($_.TypeNameOfValue -match 'Boolean') {
                #turn Boolean into an INT
                $arr.Add(@(, ($_.value -as [int])))
            }
            else {
                #only create an entry if there is a value
                if ($null -ne $_.value) {
                    Write-Verbose "[$((Get-Date).TimeOfDay)] Creating cliXML for a blob"
                    #create a temporary cliXML file
                    $out = [system.io.path]::GetTempFileName()
                    #9/11/2022 This is a potential problem.
                    # https://stackoverflow.com/questions/27761453/how-to-properly-escape-single-quotes-in-sqlite-insert-statement-ios
                    $_.value | Export-Clixml -Path $out -Encoding UTF8 #-Depth 1
                    #for testing
                    # Copy-Item -path $out -Destination d:\temp\out.xml
                    $in = (Get-Content -Path $out -Encoding UTF8 -ReadCount 0 -Raw) -replace "'", "''"
                    $arr.Add(@(, "$($in)"))
                    Remove-Item -Path $out
                }
                else {
                    $arr.Add('')
                }
            }
        }
        $values = $arr -join "','"
        # If ($names.split(".").count -eq ($values -split "','").count) {
        "Insert Into $TableName ($names) values ('$values')"
        #$global:q= "Insert Into $TableName ($names) values ('$values')"
        #$global:n = $names
        #$global:v = $values
        # }
        # else {
        # Write-Warning "There is a mismatch between the number of column headings ($($names.split(".").count)) and values ($(($values -split "','").count))"
        # }
    } #process
 
    End {
        Write-Verbose "[$((Get-Date).TimeOfDay)] Ending $($MyInvocation.MyCommand)"
 
    } #end
 
} #close buildquery
 
#>