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