| external help file | mySQLite-help.xml |
|---|---|
| Module Name | mySQLite |
| online version | https://jdhitsolutions.com/yourls/52a4a6 |
| schema | 2.0.0 |
Convert or dump PowerShell objects into a SQLite database.
ConvertTo-MySQLiteDB -InputObject <Object[]> [-Path] <String> -TableName <String> [-Primary <String>] [-TypeName <String>] [-Append] [-Force] [-WhatIf] [-Confirm] [<CommonParameters>]This command is designed to make it easy to take the results of a PowerShell expression, or a collection of objects, and dump them into a SQLite database file. You will need to specify a table name for the data. By default, the command will create a corresponding property map table so that it will be easier to get the data back out with ConvertFrom-MySQLiteDB. This command will use the detected typename or you can specify a typename.
This command was designed with the assumption that you would create a single database and table per command. The Append parameter allows you to include additional tables in the same database file. It does NOT append data to an existing table. If you need to do that, create an Insert query with Invoke-MySQLiteQuery.
Nested objects or more complex properties will be converted to cliXML and stored in the database as byte array or blob.
This command will not overwrite an existing file unless you use -Force.
For best results, you should select only the properties you need as some properties may not serialize very well.
PS C:\> $data = Get-CimInstance win32_OperatingSystem -ComputerName $computers | Select-Object @{Name="Computername";Expression={$_.CSName}},@{Name="OS";Expression = {$_.caption}},InstallDate,Version,@{Name="IsServer";Expression={ If ($_.caption -match "server") {$True} else {$False}}}
PS C:\> $data | ConvertTo-MySQLiteDB -Path c:\work\Inventory.db -TableName OS -TypeName myOS -forceConvert the results of a PowerShell expression into a SQLite database file using a table called OS and a custom type name of myOS. This process will also create a table called propertymap_myOS with mappings for property names and the original type.
The first property name will be used as the first table column and the primary index so the values from your command need to be unique.
The database can be queried using Invoke-MySQLiteQuery or dumped out using ConvertFrom-MySQLiteDB.
NOTE: Storing objects in a database requires serializing nested objects. This is accomplished by converting objects to cliXML and storing that information as an array of bytes in the database. To convert back, the data must be converted to the original clixml string, saved to a temporary file, and then re-imported with Import-Clixml. This process is not guaranteed to be 100% error free. The converted object property should be the deserialized version of the original property.
PS C:\> Get-Process | Where-Object {$_.name -NotMatch "^(system|idle)$"} | Select-Object * | ConvertTo-MySQLiteDB -Path d:\temp\allproc.db -TableName Process -TypeName myProcess -Primary IDCreate a database from local process information. This example is specifying the primary key. You need to have one unique property to use as the primary key for the database. If you see a constraint error, you most likely need to set a primary key.
Enter the path to the SQLite database file.
Type: String
Parameter Sets: (All)
Aliases: database
Required: True
Position: 0
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseAppend a new table to an existing SQLite database file. This does NOT append data to an existing table.
Type: SwitchParameter
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseOverwrite the existing file.
Type: SwitchParameter
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseEnter the name of the new table. Table names are technically case-sensitive.
Type: String
Parameter Sets: (All)
Aliases:
Required: True
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseEnter a typename for your converted objects. If you don't specify one, it will be auto-detected. This name will also be used to construct the property map table.
Type: String
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseWhat object do you want to create? Typically this will be the result of a PowerShell expression or command. It is recommended that you be selective.
Type: Object[]
Parameter Sets: (All)
Aliases:
Required: True
Position: Named
Default value: None
Accept pipeline input: True (ByValue)
Accept wildcard characters: FalseSpecify the column name to use as the primary key or index. Otherwise, the first detected property will be used.
Type: String
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseShows what would happen if the cmdlet runs. The cmdlet is not run.
Type: SwitchParameter
Parameter Sets: (All)
Aliases: wi
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalsePrompts you for confirmation before running the cmdlet.
Type: SwitchParameter
Parameter Sets: (All)
Aliases: cf
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseThis cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.
Learn more about PowerShell: https://jdhitsolutions.com/yourls/newsletter