Search code examples
databasesqlitefirefoxdatabase-schemabookmarks

How can I add or remove a bookmark tag in Firefox via the command line?

I have been looking at the places database and at the Web Extension Bookmarks API but I'm not sure how to do it or if it is possible.

Solution

  • I believe that is not possible via the Web Extension Bookmarks API. That is reserved and can be used only within an add-on.

    To quote:

    JavaScript APIs for WebExtensions can be used inside the extension's background scripts and in any other documents bundled with the extension, including browser action or page action popups, sidebars, options pages, or new tab pages. A few of these APIs can also be accessed by an extension's content scripts (see the list in the content script guide).

    To use the more powerful APIs you need to request permission in your extension's manifest.json

    I don't know if you are on windows or *nix. The general idea would be to manipulate the SQLite database.

    For bookmarks you need a file called places.sqlite which you can find on windows at - C:\Users\login\AppData\Roaming\Mozilla\Firefox\Profiles

    On *nix you can do:

    find . -name 'places.sqlite' and usually you will get it somewhere in your home:

    e.g. /home/tukanos/.mozilla/firefox/tf8oiuhk.default/places.sqlite

    I'll cover only windows & powershell now (you can do that also for *nix):

    I think it is also good idea to follow a best practices guide for the places.sqlfile. Be sure to read it before you start changing the file.

    NOTE: make backup of your places.sqlite before playing with it!

    In powershell you would 

    Import-Module pssqlite

    (Download the module here if you are using PS version < 5.0)

    Then get your path for places.sqlite

    $db = 'C:\Users\login\AppData\Roaming\Mozilla\Firefox\Profiles\tf8oiuhk.default\places.sqlite'

    To query the sql database:

    $sqlFirefoxQuery = "SELECT places.id, 
                           places.URL,
                           places.GUID,
                           bookmarks.title,
                           bookmarks.id,
                           bookmarks.parent
                      FROM moz_places as places
                      JOIN moz_bookmarks as bookmarks 
                        ON places.id = bookmarks.fk"


Invoke-SqliteQuery -DataSource $db -QueryTimeout 20 -Query $sqlFirefoxQuery | Out-file -Encoding UTF8 Bookmarks_firefox.txt

    The logic is the same for inserting, updating, deleting data.

    Below you can find example of insert and delete as that was your question.

    To insert data use sql INSERT INTO For example:

    insert into moz_bookmarks (attributes) VALUES (your_values), (next_values)

    e.g.

    INSERT INTO moz_bookmarks (type, parent, title, dateAdded)  
     VALUES (1,5,'MyBookmark',CURRENT_TIMESTAMP)

    To remove values you should use sql's delete:

    DELETE FROM from moz_places
     WHERE id=1112

    (don't forget to close your Firefox)

    Use your database link - to find the attributes: https://developer.mozilla.org/en-US/docs/Mozilla/Tech/Places/Database

    Edit - How to get a tag from sqlite DB?

    To simply list all tabs in the bookmarks directory:

    select * from moz_bookmarks where parent=4

    explanation: This will get you all the bookmarks stored in your places.sqlite file. The tags are stored in the column title the TAGs folder has id=4

    You can check the ID from in table moz_bookmarks_roots:

    ╔═══╦═════════════════╦═══════════════╗
║   ║ root_name       ║ folder_id     ║
╠═══╬═════════════════╬═══════════════╣
║ 1 ║ places          ║   1           ║
║ 2 ║ menu            ║   2           ║
║ 3 ║ toolbar         ║   3           ║
║ 4 ║ tags            ║   4           ║
║ 5 ║ unfiled         ║   5           ║
╚═══╩═════════════════╩═══════════════╝

    Now how to find a tag with certain bookmarks?

    1. First find your bookmark which you want to work with e.g. title from this question "%How can I add or remove a bookmark tag%"

      SELECT * FROM moz_places where title LIKE '%How can I add or remove a bookmark tag%'

    The result:

    id              : 20079
url             : https://stackoverflow.com/questions/51124179/how-can-i-add-or-remove-a-bookmark-tag-in-firefox-via-the-command-line/51264053?noredirect=1#
                  comment89523757_51264053
title           : How can I add or remove a bookmark tag in Firefox via the command line? - Stack Overflow
rev_host        : moc.wolfrevokcats.
visit_count     : 1
hidden          : 0
typed           : 0
favicon_id      : 238
frecency        : 125
last_visit_date : 1531301813682000
guid            : w6vYlxdKQU6V
foreign_count   : 2
url_hash        : 47357855952022

    1. Then you go to moz_bookmarks table where you can find the actual records (id from moz_places is fk from moz_bookmarks).

      select * from moz_bookmarks where fk=20079

    This result:

        id           : 76
    type         : 1
    fk           : 20079
    parent       : 75
    position     : 0
    title        : How can I add or remove a bookmark tag in Firefox via the command line? - Stack Overflow
    keyword_id   :
    folder_type  :
    dateAdded    : 1531298577890000
    lastModified : 1531298577894000
    guid         : R0961JLfZYKj

    id           : 78
    type         : 1
    fk           : 20079
    parent       : 77
    position     : 0
    title        :
    keyword_id   :
    folder_type  :
    dateAdded    : 1531298593884000
    lastModified : 1531298593884000
    guid         : BW__oMQbHerd

    As you see you get two records. One has parent=75 which is the bookmark folder. Second one is a mapping of fk=20079 (id) to parent=77 which are the actual tag(s):

        SELECT * FROM moz_bookmarks where id = 77

    Where you get the tags in the title attribute:

        id           : 77
    type         : 2
    fk           :
    parent       : 4
    position     : 0
    title        : stack_overflow test
    keyword_id   :
    folder_type  :
    dateAdded    : 1531298593878000
    lastModified : 1531298593884000
    guid         : AVcyFpGkrfzV

    So the tags in my case were: stack_overflow test

    The E-R schema for places.sqlite you can find here.

    Edit 2 Forgot to answer the question directly.

    When changing the sqlite database close Firefox instance.

    To add a tag - you have to understand the logic (I have to pick a new bookmark which has no tags yet):

    First to create I have to have a new bookmark

    $sqlFirefoxQuery = "SELECT * FROM moz_places where title LIKE '%Cnn%'"

PS C:\> Invoke-SqliteQuery -DataSource $db -QueryTimeout 20 -Query $sqlFirefoxQuery


id              : 20223
url             : https://edition.cnn.com/
title           : CNN International - Breaking News, US News, World News and Video
rev_host        : moc.nnc.noitide.
visit_count     : 1
hidden          : 0
typed           : 0
favicon_id      : 2015
frecency        : 75
last_visit_date : 1531392673997000
guid            : qqCRafq4FIcn
foreign_count   : 1
url_hash        : 47358730651511

    Then I select from moz_bookmarks table:

    $sqlFirefoxQuery = "select * from moz_bookmarks where fk=20223"
PS C:\> Invoke-SqliteQuery -DataSource $db -QueryTimeout 20 -Query $sqlFirefoxQuery

id           : 79
type         : 1
fk           : 20223
parent       : 75
position     : 1
title        : CNN International - Breaking News, US News, World News and Video
keyword_id   :
folder_type  :
dateAdded    : 1531392718191000
lastModified : 1531392718197000
guid         : 2ywnKBmbDWJI

    As you see there is only one record instead of two (check above the section where I select the moz_bookmarks and find two records)

    You then have to find out a free id(s) at table moz_bookmarks. In my case it is id=80 and id=81 (can be different in your case):

    Now comes the tricky part where you have to get GUID. You can get one via GUI - SQLite Manager and its SELECT GENERATE_GUID(). However, we need one without gui so we have to create it.

    If you check the gecko's python makeGuid() function you will see that they are using md5, hex. I will do it with similar functions hex() and randomblob().

    First to generate the tag record: Then the record itself (type=2, parent=4):

    id           : 80
type         : 2
fk           :
parent       : 4
position     : 0
title        : news insert_firefox_tag_example
keyword_id   :
folder_type  :
dateAdded    : 1531298593878000
lastModified : 1531298593884000
guid         : <generated_GUID>

    The actual insert:

    $sqlFirefoxQuery = "INSERT INTO moz_bookmarks (id, type, parent, position, title, dateAdded, lastModified, guid)
     VALUES (80,
             2,
             4,
             0,
             'news insert_firefox_tag_example',
             strftime('%s',CURRENT_TIMESTAMP)*1000000,
             strftime('%s',CURRENT_TIMESTAMP)*1000000,
             hex(randomblob(2)) || hex(randomblob(2)) || hex(randomblob(2)))"

    Then you have to create the record that links it:

        id          : 81
    type       : 1
    fk          : 20223
    parent       : 80
    position     : 0
    title        :
    keyword_id   :
    folder_type  :
    dateAdded    : 1531298593884000
    lastModified : 1531298593884000
    guid         : <generated_GUID>

    The actual insert:

    $sqlFirefoxQuery = "INSERT INTO moz_bookmarks (id, type, fk, parent, position, dateAdded, lastModified, guid)
     VALUES (81,
             1,
             20223,
             80,
             0,
             strftime('%s',CURRENT_TIMESTAMP)*1000000,
             strftime('%s',CURRENT_TIMESTAMP)*1000000,
             hex(randomblob(2)) || hex(randomblob(2)) || hex(randomblob(2)))"

    Now you can check your record where you have added two new tags news insert_firefox_tag_example.

    To update a tag with new_tag (you have to add the current tags too!):

    $sqlFirefoxQuery = "UPDATE moz_bookmarks
                       SET title = 'stack_overflow test new_tag' 
                     WHERE id = 77"

    Then execute it:

    Invoke-SqliteQuery -DataSource $db -QueryTimeout 20 -Query $sqlFirefoxQuery

    You will get updated tags:

    PS C:\> $sqlFirefoxQuery = "SELECT * FROM moz_bookmarks where id = 77"
PS C:\> Invoke-SqliteQuery -DataSource $db -QueryTimeout 20 -Query $sqlFirefoxQuery


id           : 77
type         : 2
fk           :
parent       : 4
position     : 0
title        : stack_overflow test new_tag
keyword_id   :
folder_type  :
dateAdded    : 1531298593878000
lastModified : 1531298593884000
guid         : AVcyFpGkrfzV

    To remove the tags you can do simply put an empty string (don't forget that the changes will be probably seen after Firefox restart):

    $sqlFirefoxQuery = "UPDATE moz_bookmarks
                       SET title = '' 
                     WHERE id = 77"

    The result:

    PS C:\> $sqlFirefoxQuery = "SELECT * FROM moz_bookmarks where id = 77"
PS C:\> Invoke-SqliteQuery -DataSource $db -QueryTimeout 20 -Query $sqlFirefoxQuery


id           : 77
type         : 2
fk           :
parent       : 4
position     : 0
title        :
keyword_id   :
folder_type  :
dateAdded    : 1531298593878000
lastModified : 1531298593884000
guid         : AVcyFpGkrfzV

    So that is it.