Modify Microsoft Edge Collections via PowerShell script

Manta Ray 1 Reputation point
2021-11-26T11:51:44.897+00:00

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

![152925-image.png]1

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,239 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,272 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Rich Matheisen 43,926 Reputation points
    2021-11-28T20:10:41.263+00:00

    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.


  2. Olaf Helper 38,786 Reputation points
    2021-11-29T12:51:51.963+00:00

    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.


  3. Rich Matheisen 43,926 Reputation points
    2021-11-29T22:25:45.373+00:00

    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.

    0 comments No comments