Search for Firefox Bookmarks with given combination of 1 to 3 keywords

When I found that Firefox stores its bookmarks in a sqlite database places.sqlite I wrote the following PowerShell script to search for bookmark by keyword combination.

Here is the code:

ipmo WPK            
            
if (! $sqlitedll)            
{            
    $sqlitedll = [System.Reflection.Assembly]::LoadFrom("C:\Program Files\System.Data.SQLite\bin\System.Data.SQLite.dll")             
}            
            
$ConnectionString = "Data Source=C:\Var\sqlite_ff4\places.sqlite"            
            
$conn = new-object System.Data.SQLite.SQLiteConnection             
$conn.ConnectionString = $ConnectionString             
$conn.Open()             
            
function Invoke-sqlite            
{            
    param( [string]$sql,            
           [System.Data.SQLite.SQLiteConnection]$connection            
           )            
    $cmd = new-object System.Data.SQLite.SQLiteCommand($sql,$connection)            
    $ds = New-Object system.Data.DataSet            
    $da = New-Object System.Data.SQLite.SQLiteDataAdapter($cmd)            
    $da.fill($ds) | Out-Null            
    return $ds.tables[0]            
}            
            
function Show-Bockmarks ($resource) {            
    New-Grid -Rows 2 -Columns 1 -width 1400 -hight 1000  {            
            
        New-StackPanel -Orientation horizontal -column 0 -row 0 -Children {            
             New-Label    '1. Keyword'            
             New-TextBox  -Name tag1 -width 200            
             New-Label    '2. Keyword'            
             New-TextBox  -Name tag2 -width 200            
             New-Label    '3. Keyword'            
             New-TextBox  -Name tag3 -width 200            
             New-Button -Name Search "search" -On_Click {            
            $text1 = $window | Get-ChildControl Tag1            
            $tag1 = $text1.Text            
            $text2 = $window | Get-ChildControl Tag2            
            $tag2 = $text2.Text            
            $text3 = $window | Get-ChildControl Tag3            
            $tag3 = $text3.Text            
            if ( $tag2 -ne '') {            
$clause2 = @"            
    join moz_bookmarks l2 on b.fk = l2.fk and b.id <> l2.id
    join moz_bookmarks t2 on l2.parent = t2.id and  t2.parent = 4 and upper(t2.title) = upper('$tag2')
"@                                    
            } else { $clause2 = '' }                    
            
            if ( $tag3 -ne '') {            
$clause3 = @"            
    join moz_bookmarks l3 on b.fk = l3.fk and b.id <> l3.id
    join moz_bookmarks t3 on l3.parent = t3.id and  t3.parent = 4 and upper(t3.title) = upper('$tag3')
"@                                    
            } else { $clause3 = '' }                    
            
$ff_sql = @"
SELECT b.title, datetime (b.dateAdded / 1000000, 'unixepoch', 'localtime') dateAdded , p.url
    from moz_bookmarks b
    join moz_bookmarks l1 on b.fk = l1.fk and b.id <> l1.id
    join moz_bookmarks t1 on l1.parent = t1.id and  t1.parent = 4 and upper(t1.title) = upper('$tag1')
    join moz_places p  on b.fk = p.id $clause2 $clause3
where b.title is not null and b.type = 1
"@            
            $conn = $resource.conn            
            $window.Title = "$($conn.database) Database Browser"            
            $TableView = $window | Get-ChildControl TableView            
            $TableView.ItemsSource = @(Invoke-sqlite -sql $ff_sql -connection $conn)            
             }             
             New-Button -Name Cancel "Close" -On_Click {$window.Close()}             
        }            
        New-ListView -Column 0 -Row 1 -Name TableView -View {            
           New-GridView -AllowsColumnReorder -Columns {            
               New-GridViewColumn "title"             
               New-GridViewColumn "dateAdded"             
               New-GridViewColumn "url"             
           }            
        }   -On_SelectionChanged {            
             start $this.selecteditem.url            
        }            
        #}            
            
    } -asjob -Resource $resource            
}            
            
Show-Bockmarks -resource @{conn = $conn}            

You have to install System.Data.SQLite
from http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

I had problems when I checked the install in GAC option. Therefore I installed it without that option and supply the absolute path in my script.

Note: You can't open places.sqlite while Firefox is running. I copied it and coded the path of the copy into my script.

Now I can enter a keyword, press search and the grid fills. Double clicking starts the bookmark in the default browser.