Let the user have the option to specify a typename instead of relying on using the detected type.

Add a PropertyMap parameter to ConvertFrom-MySqliteDB. The command should use the parameter first, then look for a table called propertymap_table, then a propertymap table, otherwise default to a custom object.

I would love to be able to support non-Windows platforms using PowerShell 7. Unfortunately, I have not had any luck finding compatible libraries, i.e., System.Data.SqLite.dll and SQLite.Interop.dll. I am more than happy to accept PRs or even pointers on where to look.

Need to make sure relative database paths like .\test.db get resolved to their full file system path.

Just starting to experiment with your module and encountered some issue

I tried your "computer inventory" example and I have no errors, so it looks like the module is properly installed,

then I tried to use Get-Process

$process = get-process
$process | ConvertTo-MySQLiteDB -Path c:\temp\test_process.db -TableName Process -TypeName myProcess -force

I get multiple occurrences of this error:

Exception calling "ExecuteNonQuery" with "0" argument(s): "SQL logic error
cannot start a transaction within a transaction"
At C:\Program Files\WindowsPowerShell\Modules\mySQLite\0.9.2\functions\Invoke-MySQLiteQuery.ps1:95 char:25
+                         [void]$cmd.ExecuteNonQuery()
+                         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SQLiteException

at a first glance it looks like the database is created, tables are created and 1 process details is saved inside the table.

P.S. I don't really need to get processes into SQLite it's just something that I noticed. not sure if it's due to the number of properties or some specific type.

I tried also with Get-ADUser, if I just leave the default properties, it gets imported into sqlite.
If I do the get-aduser with -properties * and try to put it into SQLite it fails with a different error (compared to the process example)

Exception calling "ExecuteNonQuery" with "0" argument(s): "SQL logic error
near "-": syntax error"

and not even the tables get created (so possibly a different issue)

regards

rename the command to make its purpose clearer.

When creating a database file it doesn't release the file until you close the terminal, is this expected behaviour?

I was toying around and used the command and then tried to delete the database file, it cannot delete until the terminal that spawned it is closed.

Blobbed properties via clixml data don't get properly restored.

PS C:\> [pscustomobject]@{Name="Jeff";Date=(Get-date);Msg = "This isn't that difficult. Is it?";Bits = (Get-Service bits)} | ConvertTo-MySQLiteDB -Path d:\temp\tst3.db -TableName test
PS C:\> Convertfrom-mySQLitedb -Path D:\temp\tst4.db -PropertyTable propertymap_System_Management_Automation_PSCustomObject -TableName test | fl


Name : Jeff
Date : 9/12/2022 9:01:31 AM
Msg  : This isn't that difficult. Is it?
Bits :

The Bits property is null. There is data in the table.

PS C:\> Invoke-MySQLiteQuery -Path D:\temp\tst4.db -q "Select * from test"

Name Date                 Msg                               Bits
---- ----                 ---                               ----
Jeff 9/12/2022 9:01:31 AM This isn't that difficult. Is it? {60, 79, 98, 106...}

Probably need commands for opening and closing database connections so that they can be used in scripted work.

Sorry I didn't know where else to log this @jdhitsolutions :

• What version of SQLite is being used here, as I'm looking for the latest version which supports read and write of the DB at the same time.
• Is there an autoincrement feature for ID so I don't have to add an $i++ in the loop to set to ID?
• Is there a bulk copy or bulk transaction feature for large inserts?

Add a parameter to support filtering with a query in ConvertFrom-MySQLiteDB.

When importing into a db, I am creating a propertymap table. But there might me multiple imports. Need a propertymap table for each table. Use conventction propertymap_

The name of this module confuses me...

It seems to be a portmanteau of MySQL and SQLite.

But from what I can gather, the project doesn't have anything to do with MySQL, only SQLite.

What is the meaning behind the module name?

Need a command or parameter with Get-MySqliteTable to show details. Someone might not remember all the table property names.

iq .\foo3.db -Query "Select * from sqlite_master"

type     : table
name     : Metadata
tbl_name : Metadata
rootpage : 2
sql      : CREATE TABLE Metadata (Author TEXT,Created TEXT,Computername TEXT,Comment TEXT)

type     : table
name     : data
tbl_name : data
rootpage : 3
sql      : CREATE TABLE data (Name text PRIMARY KEY,Date text,User text,size int)

Hi,

I really appreciate all the work put into this module. It's been overall easy to use and extremely useful. However, I am finding it difficult to properly error handle, or even make my code error aware when using Invoke-MySQLiteQuery. Not sure if this has been brought up before.

Basically, if I run the command and provide a malformed query, such as one that does not have an escaped ' special character, Invoke-MySQLiteQuery generates a warning/error message, but that warning does not seem to be piped to either STDOUT, or raise any sort of error into STDERR

Given the query saved into a variable named $query:

INSERT INTO company_assets ( id, hostname, fqdn, ipv4_address, os_platform, is_inactive, inactive_reason, segment_status, monitored_by_status, asset_source, asset_type, health_status, health_status_issue, was_asset_connected, date_first_seen, date_last_seen, last_connected_at, last_disconnected_at, segmented_at ) VALUES ( '2dfa2s5fda2', 'exampleserver1', 'exampleserver1.mycompany.com', 'NULL', 'Windows 10 Enterprise', 'True', 'MANUAL', 'Not segmented', 'Can't be monitored (inactive entity)', 'Active directory', 'Client', 'Unknown', 'NULL', 'False', '2024-07-15T11:33:50.2505436-04:00', '2024-07-15T11:33:50.2505678-04:00', 'NULL', 'NULL', 'NULL'

Where the phrase 'Can't be monitored (inactive entity)' is considered malformed, due to an unescaped ' character, executing this query by invoking Invoke-MySQLiteQuery -Path $DatabaseFileName -Query $query -ErrorVariable $sql_err, results in the following being displayed in my PS7 session.

WARNING: SQL logic error WARNING: INSERT INTO zn_assets ( id, hostname, fqdn, ipv4_address, os_platform, is_inactive, inactive_reason, segment_status, monitored_by_status, asset_source, asset_type, health_status, health_status_issue, was_asset_connected, date_first_seen, date_last_seen, last_connected_at, last_disconnected_at, segmented_at ) VALUES ( '2dfa2s5fda2', 'exampleserver1', 'exampleserver1.mycompany.com', 'NULL', 'Windows 10 Enterprise', 'True', 'MANUAL', 'Not segmented', 'Can't be monitored (inactive entity)', 'Active directory', 'Client', 'Unknown', 'NULL', 'False', '2024-07-15T11:33:50.2505436-04:00', '2024-07-15T11:33:50.2505678-04:00', 'NULL', 'NULL', 'NULL' ) WARNING:

Instead of raising an error into the $sql_err variable (configured as the -ErrorVariable), this is just displayed. Additionally, this is not captured even if you save the output of the command into a variable, such as $query_result = Invoke-MySQLiteQuery -Path $DatabaseFileName -Query $query -ErrorVariable $sql_err, making me believe it's not even getting piped into STDOUT. This makes it impossible to handle or even be aware when a query is not executed successfully. I am hoping this can be improved upon as everything else about this module is great. It just needs to be able to raise errors when SQLite does.

Here is the output of $PSVersionTable for reference:

`
Name Value

PSVersion 7.4.2
PSEdition Core
GitCommitId 7.4.2
OS Microsoft Windows 10.0.22631
Platform Win32NT
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
WSManStackVersion 3.0
`

Hi,

There's a check that runs at cmdlet starting on database file extension.
It seems to allow ".db" pattern only and won't run in any other case.
We use ".sqlite" extension for naming our Sqlite database files as it is allowed (also default in Sqlite DB Browser).
This is handy for us because we may use several DB engines on the same filesystem with no ambiguity.

Will you agree to modify these cmdlets in a more permisive way ?
Is there any strong reason not to ?

Thanks a lot by advance as doing this allows us to add your work in our team's tool box.

[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

Open-MySQLiteDB.ps1
Line: 7
[Parameter(Position=0,mMandatory)]

In New-MySQLiteDBTable see how to create a table with an autoincrementing column.

This was originally reported in Issue #13

I can run

get-aduser artd | ConvertTo-MySQLiteDB -Path .\art.db -TableName user -TypeName aduser

with no errors. But it fails when using all user properties.

get-aduser artd -Properties * | ConvertTo-MySQLiteDB -Path .\art2.db -TableName user -TypeName aduser

Exception calling "ExecuteNonQuery" with "0" argument(s): "SQL logic error
near "-": syntax error"
At C:\Program Files\WindowsPowerShell\Modules\mySQLite\0.9.2\functions\New-MySQLiteDBTable.ps1:119 char:21
+                     [void]$cmd.ExecuteNonQuery()
+                     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SQLiteException

The database gets created but not the table.

I did a quick test and get the same error with groups but here I get it even with the default output.

PS C:\> get-adgroup sales | ConvertTo-MySQLiteDB -Path .\salesgroup.db -TableName group -force
Exception calling "ExecuteNonQuery" with "0" argument(s): "SQL logic error
near "group": syntax error"
At C:\Program Files\WindowsPowerShell\Modules\mySQLite\0.9.2\functions\New-MySQLiteDBTable.ps1:119 char:21
+                     [void]$cmd.ExecuteNonQuery()
+                     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SQLiteException

Exception calling "ExecuteNonQuery" with "0" argument(s): "SQL logic error
near "group": syntax error"
At C:\Program Files\WindowsPowerShell\Modules\mySQLite\0.9.2\functions\Invoke-MySQLiteQuery.ps1:95 char:25
+                         [void]$cmd.ExecuteNonQuery()
+                         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SQLiteException

When converting from a db table, some blob that are empty are throwing errors. As are blobs that when converted are actually strings.

I've got text similar to this in a json file that I am importing where it contains the single quotes.

"Item": "Restrict user ability to access groups features in My Groups. Group and User Admin will have read-only access when the value of this setting is 'Yes'.",

Adding to the database I get this error

WARNING: SQL logic error
WARNING: Insert into database Values ('Self Service Group Management', '', '', '"General"',
'configuration\entra-id\groups\general.md', '10',
'https://entra.microsoft.com/#view/Microsoft_AAD_IAM/GroupsManagementMenuBlade/~/General', 'Restrict user ability to
access groups features in My Groups. Group and User Admin will have read-only access when the value of this setting is
'e[7mYese[0;93m'.', 'No')

The following code works for me:

$Date = Get-Date '01/01/2000'

$StateEntry = [PSCustomObject]@{
    ID = (New-Guid).Guid
    Date = $Date
}

$DbPath = "$env:TEMP\state.db"

[System.GC]::Collect()

if (Test-Path -Path $DbPath) {
    Remove-Item -Path $DbPath -Force -ErrorAction SilentlyContinue
}

$StateEntry | ConvertTo-MySQLiteDB -Path $DbPath -TableName State -TypeName State -Primary ID -Force

[System.GC]::Collect()

ConvertFrom-MySQLiteDB -Path $DbPath -TableName State -PropertyTable propertymap_State

However this does not work in the UK when the date is set to 29/02/2024; the raw table text contains the date in MM/DD/YYYY format, which fails to parse back to DateTime.

This is because of how PowerShell converts DateTime to String. When you use .ToString() it uses the current culture settings. When using string interpolation, it uses the default invariant culture.

> (Get-Date).ToString()
29/02/2024 00:00:00

> "$(Get-Date)"
02/29/2024 00:00:00

To fix this, you'd either need to store the strings in the local culture format (by calling .ToString() on them), or decode them differently:

$dateString = '29/02/2024 00:00:00'
$format = 'dd/MM/yyyy HH:mm:ss'
$culture = [System.Globalization.CultureInfo]::InvariantCulture

$date = [DateTime]::ParseExact($dateString, $format, $culture)