Suggestion - Use native clixml methods to avoid need for temp files

SeSeKenny commented 1 year ago

[System.Management.Automation.PSSerializer] is available to build quasi convertto and convertfrom functions. Adjusting technique could avoid need for temp files. I will dump my edits that changed this behaviour so you can view function changes. If this is a welcome change let me know and I will submit a proper PR, but I would bump the assemblies up to current too. 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 OLD-buildquery {
    [cmdletbinding()]
    Param(
        [parameter(Mandatory)]
        [object]$InputObject,
        [parameter(Mandatory)]
        [string]$Tablename
    )
    Begin {
        Write-Verbose "[$((Get-Date).TimeOfDay)] Starting $($myinvocation.mycommand)"
    } #begin

    Process {
        $names = $InputObject.psobject.Properties.name -join ","

        $inputobject.psobject.Properties | ForEach-Object -Begin { $arr = @() } -Process {
            if ($_.TypeNameofValue -match "String|Int\d{2}|Double|Datetime|long") {
                $arr += @(, $_.Value)
            }
            elseif ($_.TypeNameofValue -match "Boolean") {
                #turn Boolean into an INT
                $arr += @(, ($_.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
                    $arr += @(, "$($in)")
                }
                else {
                    $arr += ""
                }
            }
        }
        $values = $arr -join "','"

        "Insert Into $Tablename ($names) values ('$values')"

    } #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"
        [text.encoding]::UTF8.getstring($bytes) | ConvertFrom-CliXml
    }
}

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

Convert-MySQLiteByteArray.ps1

Function Convert-MySQLiteByteArray {
    [cmdletbinding()]
    [Outputtype("Object")]
    Param(
        [Parameter(Position = 0, Mandatory,HelpMessage = "Specify the byte array from the blob property.")]
        [byte[]]$BlobProperty
    )
    Begin {
        Write-Verbose "[$((Get-Date).TimeofDay) BEGIN  ] Starting $($myinvocation.mycommand)"
    } #begin

    Process {
        Write-Verbose "[$((Get-Date).TimeofDay) PROCESS] Processing $($BlobProperty).length bytes"
        if ($BlobProperty.count -gt 0) {
            Write-Verbose "[$((Get-Date).TimeOfDay)] Converting from bytes to object"
            [text.encoding]::UTF8.getstring($BlobProperty) | ConvertFrom-CliXml
        }
    } #process

    End {
        Write-Verbose "[$((Get-Date).TimeofDay) END    ] Ending $($myinvocation.mycommand)"
    } #end

} #close Convert-MySQLiteByteArray