Versioning on my system:
SQLite version 3.24.0 2018-06-04 19:24:41
PSVersion 5.1.14393.2791
Note: I create an Alias for sqlite3:
PS A:\sqlite_to_csv> Set-Alias -Name sqlite3 -Value A:\sqlite3\sqlite3.exe
Creating an Object in Powershell is generally the best way to utilize data, and in my case I need an easy way to generate JSON files, which Powershell can be quite helpful at.
First, let's make a sample database:
PS A:\sqlite_to_csv> sqlite3 sample.db
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
sqlite> CREATE TABLE sample (
...> id INTEGER PRIMARY KEY AUTOINCREMENT,
...> name TEXT,
...> twitter TEXT,
...> blog TEXT
...> );
sqlite> INSERT INTO sample
...> (name,twitter,blog)
...> VALUES
...> ('David Steimle','dbsteimle','rhymeswithtimely.blogspot.com');
sqlite> SELECT * FROM sample;
1|David Steimle|dbsteimle|rhymeswithtimely.blogspot.com
Now we have something to query.
First, we want to create the header, which will provide output in CSV mode with headers. For this I use a Here-String.
PS A:\sqlite_to_csv> $QuerySetup = @"
>> .mode csv
>> .headers on
>>
>> "@
Then we can define our query; we will do a simple SELECT only, for now, and combine it with our query setup.
PS A:\sqlite_to_csv> $Query = "SELECT * FROM sample;"
PS A:\sqlite_to_csv> $MySql = "$QuerySetup$Query"
PS A:\sqlite_to_csv> Write-Host $MySql
.mode csv
.headers on
SELECT * FROM sample;
We then assign our result to an Object with pipes:
PS A:\sqlite_to_csv> $MyObject = $MySql | sqlite3 .\sample.db | ConvertFrom-Csv
PS A:\sqlite_to_csv> $MyObject | Format-List
id : 1
name : David Steimle
twitter : dbsteimle
blog : rhymeswithtimely.blogspot.com
This Object is now usable in all the normal ways, such as turning it into JSON (I will output to a file, as that would be my end goal):
PS A:\sqlite_to_csv> $MyObject | ConvertTo-Json | Set-Content sample.json
PS A:\sqlite_to_csv> Get-Content .\sample.json
{
"id": "1",
"name": "David Steimle",
"twitter": "dbsteimle",
"blog": "rhymeswithtimely.blogspot.com"
}
While in a basic sense this works for me, I do need the ability to have sub-objects and arrays in my JSON. This means additional tables. The obvious place to start in this example would be with Social Media. First, we will make a table of social media sites:
sqlite> CREATE TABLE socialmedium (
...> id INTEGER PRIMARY KEY AUTOINCREMENT,
...> name TEXT,
...> url TEXT
...> );
sqlite> INSERT INTO socialmedium (name,url) VALUES
...> ('Twitter','https://twitter.com/');
sqlite> INSERT INTO socialmedium (name,url) VALUES
...> ('Instagram','https://instagram.com/');
sqlite> SELECT * FROM socialmedium;
id name url
---------- ---------- --------------------
1 Twitter https://twitter.com/
2 Instagram https://instagram.co
Then a lookup table with user details:
sqlite> CREATE TABLE socialuser (
...> id INTEGER PRIMARY KEY AUTOINCREMENT,
...> user INTEGER,
...> site INTEGER,
...> username TEXT
...> );
sqlite> INSERT INTO socialuser (user,site,username) VALUES
...> (1,1,'dbsteimle');
sqlite> SELECT * FROM socialuser;
id user site username
---------- ---------- ---------- ----------
1 1 1 dbsteimle
These tables use the PRIMARY KEY from the sample table as a reference point. To work with out current Object, we need to add a new Property, and assign it as an empty hash table:
PS A:\sqlite_to_csv> $MyObject | Add-Member -NotePropertyName SocialMedia -NotePropertyValue @{}
PS A:\sqlite_to_csv> $MyObject
id : 1
name : David Steimle
twitter : dbsteimle
blog : rhymeswithtimely.blogspot.com
SocialMedia : {}
We need to use an INNER JOIN in this scenario, since we are looking up values in two lookup tables:
PS A:\sqlite_to_csv> $Query = @"
>> SELECT user,name,url,username
>> FROM socialuser
>> INNER JOIN socialmedium ON socialmedium.id=socialuser.site
>> WHERE user=$($MyObject.id);
>> "@
PS A:\sqlite_to_csv> $MySql = "$QuerySetup$Query"
PS A:\sqlite_to_csv> Write-Host $MySql
.mode csv
.headers on
SELECT user,name,url,username
FROM socialuser
INNER JOIN socialmedium ON socialmedium.id=socialuser.site
WHERE user=1;
Now, pipe through sqlite3:
PS A:\sqlite_to_csv> $MyObject.SocialMedia = $MySql | sqlite3 .\sample.db | ConvertFrom-Csv
PS A:\sqlite_to_csv> $MyObject.SocialMedia
user name url username
---- ---- --- --------
1 Twitter https://twitter.com/ dbsteimle
And Conversion to JSON:
PS A:\sqlite_to_csv> $MyObject | ConvertTo-Json | Set-Content sample.json
PS A:\sqlite_to_csv> Get-Content .\sample.json
{
"id": "1",
"name": "David Steimle",
"twitter": "dbsteimle",
"blog": "rhymeswithtimely.blogspot.com",
"SocialMedia": {
"user": "1",
"name": "Twitter",
"url": "https://twitter.com/",
"username": "dbsteimle"
}
}
To best utilize this, we would want a function where we could utilize a known value as a parameter. We may not know that id 1 matches name 'David Steimle', but if we know the database, we might know to look for name="David Steimle"
function Get-MyObject {
param( [string]$Name )
$QuerySetup = @"
.mode csv
.headers on
"@
$Query = "SELECT * FROM sample WHERE name='$Name';"
$MySql = "$QuerySetup$Query"
$Temp = $MySql | sqlite3 .\sample.db | ConvertFrom-Csv
$Temp | Add-Member -NotePropertyName SocialMedia -NotePropertyValue @{}
$Query = @"
SELECT user,name,url,username
FROM socialuser
INNER JOIN socialmedium ON socialmedium.id=socialuser.site
WHERE user=$($Temp.id);
"@
$MySql = "$QuerySetup$Query"
$Temp.SocialMedia = $MySql | sqlite3 .\sample.db | ConvertFrom-Csv
$Temp | ConvertTo-Json | Set-Content ".\$Name.json"
return $Temp
}
PS A:\sqlite_to_csv> Get-MyObject -Name "David Steimle"
id : 1
name : David Steimle
twitter : dbsteimle
blog : rhymeswithtimely.blogspot.com
SocialMedia : @{user=1; name=Twitter; url=https://twitter.com/; username=dbsteimle}
PS A:\sqlite_to_csv> Get-Content '.\David Steimle.json'
{
"id": "1",
"name": "David Steimle",
"twitter": "dbsteimle",
"blog": "rhymeswithtimely.blogspot.com",
"SocialMedia": {
"user": "1",
"name": "Twitter",
"url": "https://twitter.com/",
"username": "dbsteimle"
}
}
Or, assign the result to a variable:
PS A:\sqlite_to_csv> $DavidSteimle = Get-MyObject -Name 'David Steimle'
PS A:\sqlite_to_csv> $DavidSteimle
id : 1
name : David Steimle
twitter : dbsteimle
blog : rhymeswithtimely.blogspot.com
SocialMedia : @{user=1; name=Twitter; url=https://twitter.com/; username=dbsteimle}
