miércoles, 6 de febrero de 2013

Obter a lista de marcadores de Firefox [Bash][Twitcode XXV]

O comando é o que segue:
sqlite3 .mozilla/firefox/*.default/places.sqlite 'SELECT b.title, p.url FROM moz_bookmarks AS b JOIN moz_places AS p ON b.fk = p.id'

Por exemplo:
$ sqlite3 .mozilla/firefox/*.default/places.sqlite 'SELECT b.title, p.url FROM moz_bookmarks AS b JOIN moz_places AS p ON b.fk = p.id'
Getting Started|http://www.mozilla.com/en-US/firefox/central/
Help and Tutorials|http://www.mozilla.com/en-US/firefox/help/
Customize Firefox|http://www.mozilla.com/en-US/firefox/customize/
Get Involved|http://www.mozilla.com/en-US/firefox/community/
About Us|http://www.mozilla.com/en-US/about/
Etiquetas recentes|place:type=6&sort=14&maxResults=10
JamesM's kernel development tutorials|http://www.jamesmolloy.co.uk/tutorial_html/index.html
Máis visitados|place:sort=8&maxResults=10
Marcados recentemente|place:folder=BOOKMARKS_MENU&folder=UNFILED_BOOKMARKS&folder=TOOLBAR&queryType=1&sort=12&maxResults=10&excludeQueries=1
Historial|place:type=3&sort=4
Descargas|place:transition=7&sort=4
Etiquetas|place:type=6&sort=1
|place:folder=TOOLBAR
|place:folder=BOOKMARKS_MENU
|place:folder=UNFILED_BOOKMARKS
Macros - The C Preprocessor|http://gcc.gnu.org/onlinedocs/cpp/Macros.html#Macros
...
$ 


O proceso é sinxelo, a información está almaceada no arquivo `places.sqlite` do perfil de Firefox con formato sqlite3, podemos acceder os datos coa aplicación do mesmo nome.
sqlite3 .mozilla/firefox/*.default/places.sqlite

Podemos tomar a lista de táboas usando o comando `.schema`
sqlite> .schema
CREATE TABLE moz_anno_attributes (  id INTEGER PRIMARY KEY, name VARCHAR(32) UNIQUE NOT NULL);
CREATE TABLE moz_annos (  id INTEGER PRIMARY KEY, place_id INTEGER NOT NULL, anno_attribute_id INTEGER, mime_type VARCHAR(32) DEFAULT NULL, content LONGVARCHAR, flags INTEGER DEFAULT 0, expiration INTEGER DEFAULT 0, type INTEGER DEFAULT 0, dateAdded INTEGER DEFAULT 0, lastModified INTEGER DEFAULT 0);
CREATE TABLE moz_bookmarks (  id INTEGER PRIMARY KEY, type INTEGER, fk INTEGER DEFAULT NULL, parent INTEGER, position INTEGER, title LONGVARCHAR, keyword_id INTEGER, folder_type TEXT, dateAdded INTEGER, lastModified INTEGER, guid TEXT);
CREATE TABLE moz_bookmarks_roots (  root_name VARCHAR(16) UNIQUE, folder_id INTEGER);
CREATE TABLE moz_favicons (  id INTEGER PRIMARY KEY, url LONGVARCHAR UNIQUE, data BLOB, mime_type VARCHAR(32), expiration LONG, guid TEXT);
CREATE TABLE moz_historyvisits (  id INTEGER PRIMARY KEY, from_visit INTEGER, place_id INTEGER, visit_date INTEGER, visit_type INTEGER, session INTEGER);
CREATE TABLE moz_hosts (  id INTEGER PRIMARY KEY, host TEXT NOT NULL UNIQUE, frecency INTEGER, typed INTEGER NOT NULL DEFAULT 0, prefix TEXT);
CREATE TABLE moz_inputhistory (  place_id INTEGER NOT NULL, input LONGVARCHAR NOT NULL, use_count INTEGER, PRIMARY KEY (place_id, input));
CREATE TABLE moz_items_annos (  id INTEGER PRIMARY KEY, item_id INTEGER NOT NULL, anno_attribute_id INTEGER, mime_type VARCHAR(32) DEFAULT NULL, content LONGVARCHAR, flags INTEGER DEFAULT 0, expiration INTEGER DEFAULT 0, type INTEGER DEFAULT 0, dateAdded INTEGER DEFAULT 0, lastModified INTEGER DEFAULT 0);
CREATE TABLE moz_keywords (  id INTEGER PRIMARY KEY AUTOINCREMENT, keyword TEXT UNIQUE);
CREATE TABLE moz_places (   id INTEGER PRIMARY KEY, url LONGVARCHAR, title LONGVARCHAR, rev_host LONGVARCHAR, visit_count INTEGER DEFAULT 0, hidden INTEGER DEFAULT 0 NOT NULL, typed INTEGER DEFAULT 0 NOT NULL, favicon_id INTEGER, frecency INTEGER DEFAULT -1 NOT NULL, last_visit_date INTEGER , guid TEXT);
CREATE UNIQUE INDEX moz_annos_placeattributeindex ON moz_annos (place_id, anno_attribute_id);
CREATE UNIQUE INDEX moz_bookmarks_guid_uniqueindex ON moz_bookmarks (guid);
CREATE INDEX moz_bookmarks_itemindex ON moz_bookmarks (fk, type);
CREATE INDEX moz_bookmarks_itemlastmodifiedindex ON moz_bookmarks (fk, lastModified);
CREATE INDEX moz_bookmarks_parentindex ON moz_bookmarks (parent, position);
CREATE UNIQUE INDEX moz_favicons_guid_uniqueindex ON moz_favicons (guid);
CREATE INDEX moz_historyvisits_dateindex ON moz_historyvisits (visit_date);
CREATE INDEX moz_historyvisits_fromindex ON moz_historyvisits (from_visit);
CREATE INDEX moz_historyvisits_placedateindex ON moz_historyvisits (place_id, visit_date);
CREATE UNIQUE INDEX moz_items_annos_itemattributeindex ON moz_items_annos (item_id, anno_attribute_id);
CREATE INDEX moz_places_faviconindex ON moz_places (favicon_id);
CREATE INDEX moz_places_frecencyindex ON moz_places (frecency);
CREATE UNIQUE INDEX moz_places_guid_uniqueindex ON moz_places (guid);
CREATE INDEX moz_places_hostindex ON moz_places (rev_host);
CREATE INDEX moz_places_lastvisitdateindex ON moz_places (last_visit_date);
CREATE UNIQUE INDEX moz_places_url_uniqueindex ON moz_places (url);
CREATE INDEX moz_places_visitcount ON moz_places (visit_count);
sqlite> 

As dúas táboas que nos interesan son estas:
CREATE TABLE moz_bookmarks (  id INTEGER PRIMARY KEY, type INTEGER, fk INTEGER DEFAULT NULL, parent INTEGER, position INTEGER, title LONGVARCHAR, keyword_id INTEGER, folder_type TEXT, dateAdded INTEGER, lastModified INTEGER, guid TEXT);
CREATE TABLE moz_places (   id INTEGER PRIMARY KEY, url LONGVARCHAR, title LONGVARCHAR, rev_host LONGVARCHAR, visit_count INTEGER DEFAULT 0, hidden INTEGER DEFAULT 0 NOT NULL, typed INTEGER DEFAULT 0 NOT NULL, favicon_id INTEGER, frecency INTEGER DEFAULT -1 NOT NULL, last_visit_date INTEGER , guid TEXT);

A primeira contén o título do marcador e a outra a url, están relacionadas entre o campo `fk` da primeira e o `id` da segunda, así a query para obte-los datos é:
select b.title, p.url from moz_bookmarks as b join moz_places as p on b.fk = p.id;

Saúdos

No hay comentarios:

Publicar un comentario