Python and Firefox bookmarks on Windows

I'm working today on the creating a python program to reorganize my Firefox bookmarks. My previous post was about the PowerShell code I created to do this and since I haven't touched Python in a little bit, I figured what better way to relearn python than to write a program that requires database interaction.

While I am still working on this program I had an interesting error that drove me bonkers for 20 minutes or so. Unfortunately, I had actually found the answer within one or two google search results...but I didn't believe it could be that simple and kept searching.

The error I received when using a SELECT statement just to test the db connection:

sqlite3.DatabaseError: file is encrypted or is not a database

The FIX was very simple, and found at: https://deshmukhsuraj.wordpress.com/2015/02/07/windows-python-users-update-your-sqlite3/

The short of it is:
 - Download the proper file for your architecture from: http://www.sqlite.org/download.html
 - Unblock and Unzip
 - Copy the sqlite3.dll from the unzipped directory into your C:\python27\DLLs

I made a backup copy of the original sqlite3.dll in the folder. However, without any restarting or reloading of my dev tool, it worked as advertised.

Powersell to organize FireFox bookmarks

FireFox bookmarks are stored in the user's profile directory, in a sqlite database name "places.sqlite". As I keep working more on different things to do with PowerShell, I was at first annoyed that the scripts I had already written for simple file maintenance and organization couldn't be used as is with organizing the enormous amounts of bookmarks that I have. I searched around the interwebs and found a few decent examples but still wanted to roll my own.

Below is the current script as is. I only tested this on my version of FF, 47.0. However, this is a rather simplistic script and, following a best practice to backup data first, making a copy of the places.sqlite file will allow one to "roll back" to the original state.

One last note: I started this intending to make use of "System.Data.Sqlite" library. This was not hard to implement but was lengthier than I wanted. I then went to PSSqlite and found it to be a little easier to use.

#*******************************************************
#** File: Organize-FireFoxBM.ps1
#** Author: Dave Werden
#** General Notes:
#** - add 'COLLATE nocase' to end of search queries for
#**   case-insensitive searches. Similar to 'LIKE'
#**
#** - FK <= 8 is standard FF Folders
#** - Position == 0 is root level
#*******************************************************



#Need to check for pssqlite module before executing script
if(!(Get-Module pssqlite)) {
    Import-Module pssqlite
}

#*******************************************************
#** Bookmarks are stored in a profile folder in a
#** sqlite file: places.sqlite
#*******************************************************

#firefox profile folder
$profFFBasePath = "$($env:APPDATA)\Mozilla\Firefox\"
$profINIFile = "$($profFFBasePath)\profiles.ini"
$profFFProfPath = "$($profFFBasePath)\Profiles\"

#get firefox default profile name (folder name)
$profName = gc $profINIFile | select-string "Path" | split-string -separator "/" | select-string "default"

#set var for full path to default profile folder where the bookmarks are located and go there
$profBookMarksPath = $($profFFProfPath + "\" +  $profName)
cd $profBookMarksPath

#*******************************************************
#** Need a sqlite provider
#** sqlite provider DL'd from https://psqlite.codeplex.com/
#** unzipped to system PS modules folder...had to unblock DLLs
#*******************************************************

$database = ".\places.sqlite"

#*******************************************************
## query strings - Data pulls only
#*******************************************************

#This query will return the ID,  URL, GUID, Title, and Parent Folder of all bookmars
$sqlQueryBMs = "SELECT moz_places.id, moz_places.URL, moz_places.GUID, moz_bookmarks.title, moz_bookmarks.id, moz_bookmarks.parent
                FROM moz_places, moz_bookmarks
                WHERE moz_bookmarks.fk = moz_places.id"

#*******************************************************
## query strings - Data inserts only
## Trigger needed to be inplace before inserting folders
#*******************************************************

#I hand-jammed this in for my own organization plan...
#could do this with an array and a loop/LINQ
#could also..maybe will...add subfolders using this method
$sqlInserts = 'insert into moz_bookmarks (type, parent, title,dateAdded) VALUES (2,2,"BMs_Temp",CURRENT_TIMESTAMP),
                (2,2,"My_Family",CURRENT_TIMESTAMP),
                (2,2,"My_Job",CURRENT_TIMESTAMP),
                (2,2,"My_Money",CURRENT_TIMESTAMP),
                (2,2,"My_Radio",CURRENT_TIMESTAMP),
                (2,2,"My_Searches",CURRENT_TIMESTAMP),
                (2,2,"My_Coding",CURRENT_TIMESTAMP),
                (2,2,"My_Hobbies",CURRENT_TIMESTAMP)'

#Entries into moz_bookmarks need a GUID
#This will add a GUID to any newly created folder
#need to fix trigger to create more firefox-centric guids!
$sqlCreateTrigger = "CREATE TRIGGER AutoGenerateGUID
                    AFTER INSERT ON moz_bookmarks
                    FOR EACH ROW
                    WHEN (NEW.guid IS NULL)
                    BEGIN
                       UPDATE moz_bookmarks SET guid = (select hex( randomblob(4)) || hex( randomblob(2)) ||
                                  substr( hex( randomblob(2)), 2) )  WHERE rowid = NEW.rowid;
                    END"


#*******************************************************
## query strings - Data mods only
#*******************************************************

#Steps
#   1. What taxonomy do I use to sort bookmarks
#   2a. Pull all Bookmarks from original folders and move to target folders
#       i. Based upon keyword search of URL (and/or Title?) 
#       ii. DO NOT pull from target folders
#   2b. Pull all Bookmarks from original folders and move to a single temp Folder
#       i. Would probably make it easier to:
#            1) Find Dups
#            2) Clean up/Identify 'straglers' after all move operations completed
#
#Choice: 2b
#
#   3. Automated Cleanup of empty folders


#first, modify bookmarks to have parent id of our temp folder
$sqlMoveAllToTemp = 'UPDATE moz_bookmarks set parent = (Select id from moz_bookmarks where title = "BMs_Temp") 
                     where fk > 4
                     AND parent < (Select id from moz_bookmarks where title = "BMs_Temp")'

#Taxonomy...arrays of keywords :-)
$keywordsFamily = @("werden","ancestry","family","DNA","genealogy","worden")
$keywordsJob = @("compsec","embed","FCA","NGC","jobsearch","usajobs")
$keywordsMoney = @("bank","finance","money","account","loan","finance","credit","checking","savings")
$keywordsRadio = @("RF","frequency","shortwave","QR","CW","Morse","HF","UHF","VHF")
$keywordsSearches = @("google")
$keywordsCoding = @("python","perl","linux","database","php","coding","code","scripting","visual studio","netbeans","java")
$keywordsHobbies = @("jeep","wrangler","fish","guitar","Audible","audio book")


foreach ($word in $keywordsHobbies)
{
    $sqlUpdate = "UPDATE moz_bookmarks
                    SET parent = (SELECT id from moz_bookmarks WHERE title = `"My_Hobbies`") WHERE moz_bookmarks.title like `"%$($word)%`" COLLATE nocase;"
    Invoke-SqliteQuery -DataSource $database -QueryTimeout 10 -Query $sqlUpdate -SqlParameters @{ word = $word }
}

foreach ($word in $keywordsSearches)
{
    $sqlUpdate = "UPDATE moz_bookmarks
                    SET parent = (SELECT id from moz_bookmarks WHERE title = `"My_Searches`") WHERE moz_bookmarks.title like `"%$($word)%`" COLLATE nocase;"
    Invoke-SqliteQuery -DataSource $database -QueryTimeout 10 -Query $sqlUpdate -SqlParameters @{ word = $word }
}

foreach ($word in $keywordsMoney)
{
    $sqlUpdate = "UPDATE moz_bookmarks
                    SET parent = (SELECT id from moz_bookmarks WHERE title = `"My_Money`") WHERE moz_bookmarks.title like `"%$($word)%`" COLLATE nocase;"
    Invoke-SqliteQuery -DataSource $database -QueryTimeout 10 -Query $sqlUpdate -SqlParameters @{ word = $word }
}

foreach ($word in $keywordsJob)
{
    $sqlUpdate = "UPDATE moz_bookmarks
                    SET parent = (SELECT id from moz_bookmarks WHERE title = `"My_Job`") WHERE moz_bookmarks.title like `"%$($word)%`" COLLATE nocase;"
    Invoke-SqliteQuery -DataSource $database -QueryTimeout 10 -Query $sqlUpdate -SqlParameters @{ word = $word }
}

foreach ($word in $keywordsCoding)
{
    $sqlUpdate = "UPDATE moz_bookmarks
                    SET parent = (SELECT id from moz_bookmarks WHERE title = `"My_Coding`") WHERE moz_bookmarks.title like `"%$($word)%`" COLLATE nocase;"
    Invoke-SqliteQuery -DataSource $database -QueryTimeout 10 -Query $sqlUpdate -SqlParameters @{ word = $word }
}

foreach ($word in $keywordsRadio)
{
    $sqlUpdate = "UPDATE moz_bookmarks
                    SET parent = (SELECT id from moz_bookmarks WHERE title = `"My_Radio`") WHERE moz_bookmarks.title like `"%$($word)%`" COLLATE nocase;"
    Invoke-SqliteQuery -DataSource $database -QueryTimeout 10 -Query $sqlUpdate -SqlParameters @{ word = $word }
}

foreach ($word in $keywordsFamily)
{
    $sqlUpdate = "UPDATE moz_bookmarks
                    SET parent = (SELECT id from moz_bookmarks WHERE title = `"My_Family`") WHERE moz_bookmarks.title like `"%$($word)%`" COLLATE nocase;"
    Invoke-SqliteQuery -DataSource $database -QueryTimeout 10 -Query $sqlUpdate -SqlParameters @{ word = $word }
}


#*******************************************************
## Uncomment below to Execute other Queries from above
#*******************************************************

#Invoke-SqliteQuery -DataSource $database -QueryTimeout 10 -Query $sqlInserts

#Invoke-SqliteQuery -DataSource $database -QueryTimeout 10 -Query $sqlCreateTrigger

#Invoke-SqliteQuery -DataSource $database -QueryTimeout 10 -Query $sqlMoveAllToTemp


#*******************************************************
## Clean up
#*******************************************************


#drop the trigger...get original db back to original triggerless state
$sqlDeleteTrigger = "DROP TRIGGER AutoGenerateGUID"

Invoke-SqliteQuery -DataSource $database -QueryTimeout 10 -Query $sqlDeleteTrigger