Modify Microsoft Edge Collections via PowerShell script

Question

I want to script Microsoft collections and items via PowerShell for our enterprise environment. I have done some research on the internet but resources is scarce about the subject. Any help would be appreciated....

What I have learnt so far:

• Collections and Items are held in a SQLite db located in 'c:\Users\<username>\AppData\Local\Microsoft\Edge\User Data\Default\Collections\collectionsSQLite'
• Can succesfully interogate the db (Code at Annex 1)
• When I try to add a collection (haven't got to items yet) with parameters (title, date_created, date_modified) as displayed in Annex 2, I get the error as displayed in Annex 3
• When I add the 'Position' parameter the error is as displayed in Annex 4 and the db is corrupt after running the code
• To fix db just manually add and delete a collection directly on Edge
• Tables in db (Annex 5)
• Table structure for collections (Annex 6)

Annex 1

$lib = 'c:\Temp\Edge.Collections\sqlite-netFx451-binary-x64-2013-1.0.115.5\System.Data.SQLite.dll'  
$db = 'c:\Users\<username>\AppData\Local\Microsoft\Edge\User Data\Default\Collections\collectionsSQLite'  

Add-Type -Path $lib  

$con = New-Object -TypeName System.Data.SQLite.SQLiteConnection  
$con.ConnectionString = "Data Source=$($db)"  
$con.Open()  

$sql = $con.CreateCommand()  

$sql.CommandText = "SELECT * FROM collections"  

$adapter = New-Object -TypeName System.Data.SQLite.SQLiteDataAdapter $sql  
$data = New-Object System.Data.DataSet  
[void]$adapter.Fill($data)  

$data.tables.rows  
$sql.Dispose()  
$con.Close()  

Annex 2

$lib    = 'c:\Temp\Edge.Collections\sqlite-netFx451-binary-x64-2013-1.0.115.5\System.Data.SQLite.dll'  
$db     = 'c:\Users\<username>\AppData\Local\Microsoft\Edge\User Data\Default\Collections\collectionsSQLite'  

Add-Type -Path $lib  

$con    = New-Object -TypeName System.Data.SQLite.SQLiteConnection  
$con.ConnectionString = "Data Source=$($db)"  
$con.Open()  

# these are only test values  
[string]$id = '2169d472-9102-49c7-ad34-111111111111'  
[double]$date_created = 1637839186441.92  
[double]$date_modified = 1637869589135.59  
[string]$title = 'Test Collection'  
[long]$position = 998  

$sql = $con.CreateCommand()  
$sql.CommandText = "INSERT INTO collections (id, title, date_created, date_modified) VALUES (@id, @title, @date_created, @date_modified)"  
$sql.Parameters.AddWithValue("@id"                      , $id);  
$sql.Parameters.AddWithValue("@date_created"            , $date_created);  
$sql.Parameters.AddWithValue("@date_modified"           , $date_modified);  
$sql.Parameters.AddWithValue("@title"                   , $title);  

$sql.ExecuteNonQuery()  
$sql.Dispose()  
$con.Close()  

Annex 3

Exception calling "ExecuteNonQuery" with "0" argument(s): "constraint failed
NOT NULL constraint failed: collections.position"
At C:\Temp\Edge.Collections\coll.ps1:73 char:1

• $sql.ExecuteNonQuery()
• ~~~~~~~~~~~~~~~~~~~~~~
• CategoryInfo : NotSpecified: (:) [], MethodInvocationException
• FullyQualifiedErrorId : SQLiteException

Annex 4

Exception calling "ExecuteNonQuery" with "0" argument(s): "disk I/O error
disk I/O error"
At C:\Temp\Edge.Collections\coll.ps1:71 char:1

• $sql.ExecuteNonQuery()
• ~~~~~~~~~~~~~~~~~~~~~~
• CategoryInfo : NotSpecified: (:) [], MethodInvocationException
• FullyQualifiedErrorId : SQLiteException

Annex 5

collections
items
comments
meta
items_offline_data
favicons
collections_items_relationship
sync
collections_sync
collections_prism

Annex 6

]1

Answer 1

I think you'd do better by including one of the "SQL" tags in your question. It doesn't appear that there's anything PowerShell-related here.

The one thing that does look out of kilter is you've named three values in the INSERT command but supplied four parameters.

Also, I don't see anything directly relating to MS-EDGE in the code except for the name of a filesystem directory.

Answer 2

NOT NULL constraint failed: collections.position"

Seem the table "collections" do have a column named "position", which don't allow NULL values and you don't fill the column in your INSERT statement, what causes the error.

Answer 3

It looks to me as if Microsoft has used a 3rd-party (or maybe internally developed) bit of software here: index.wiki

System.Data.SQLLite is an ADO provider that has its own support.

support.wiki

If I'm right you may be able to have your questions answered there.