{"id":5347,"date":"2022-11-15T03:01:33","date_gmt":"2022-11-14T21:31:33","guid":{"rendered":"https:\/\/www.suramya.com\/blog\/?p=5347"},"modified":"2022-11-15T03:04:25","modified_gmt":"2022-11-14T21:34:25","slug":"extracting-firefox-sites-visited-for-archiving","status":"publish","type":"post","link":"https:\/\/www.suramya.com\/blog\/2022\/11\/extracting-firefox-sites-visited-for-archiving\/","title":{"rendered":"Extracting Firefox Sites visited for archiving"},"content":{"rendered":"

I have been using Firefox since it first version (0.1) launched back in 2003. At that time it was called Phoenix but had to change its name due to a trademark claim from Phoenix Technologies to Firebird which was then renamed to Firefox. Over the years I have upgraded in place so I had assumed that all my Browser History etc was still safely stored in the browser. A little while ago I realized that this wasn’t the case as there is a history page limit defined under the about:config. The property is called <\/p>\n

places.history.expiration.transient_current_max_pages: 137249<\/pre>\n
and on my system it is configured for 137249 entries. This was a disappointment as I wanted to save an archive of the sites I have visited over the years so I started looking at how to export the history from Firefox from the command line so that I can save it in another location as part of my regular backup. I knew that the history is stored in a SQLite database so I looked at the contents of the DB using a SQLite viewer. The DB was simple enough to understand but I didn’t want to recreate the wheel so I searched on Google to see if anyone else has already written the queries to extract the data and found this Reddit post<\/a> that gave the command to extract the data into a file. <\/p>\n
I tried the command out and it worked perfectly with just one small hitch. The command would not run unless I shutdown Firefox as the DB file was locked by FF. This was a big issue as it meant that I would have to close the browser every time the backup ran which is not feasible as the backup process needs to be as transparent and seamless as possible. <\/p>\n
Another search for the solution pointed me to this site<\/a> that explained how to connect to a locked DB in Read Only mode. Which was exactly what I needed, so I took the code from there and merged it with the previous version and came up with the following command:<\/p>\n
sqlite3 'file:places.sqlite?immutable=1' \"SELECT strftime('%d.%m.%Y %H:%M:%S', visit_date\/1000000, 'unixepoch', 'localtime'),\r\n                                                   url FROM moz_places, moz_historyvisits WHERE moz_places.id = moz_historyvisits.place_id ORDER BY visit_date;\" > dump.out <\/pre>\n
this command gives us an output that looks like:<\/p>\n
28.12.2020 12:30:52|http:\/\/maps.google.com\/\r\n28.12.2020 12:30:52|http:\/\/maps.google.com\/maps\r\n...\r\n...\r\n14.11.2022 04:37:17|https:\/\/www.google.com\/?gfe_rd=cr&ei=sPvqVZ_oOefI8AeNwZbYDQ&gws_rd=ssl,cr&fg=1\r\n<\/pre>\n
Once the file is created, I back it up with my other files as part of the nightly backup process on my system. In the next phase I am thinking about dumping this data into a PostgreSQL DB so that I can put a UI in front of it that will allow me to browse\/search through the file. But for now this is sufficient as the data is being backed up. <\/p>\n
I was able to get my browsing history going back to 2012 by restoring the oldest Firefox backup that I have on the system and then extracting the data from it. I still have some DVD’s with even older backups so when I get some time I will restore and extract the data from there as well. <\/p>\n
Well this is all for now. Will write more later.<\/p>\n
– Suramya<\/p>\n","protected":false},"excerpt":{"rendered":"
I have been using Firefox since it first version (0.1) launched back in 2003. At that time it was called Phoenix but had to change its name due to a trademark claim from Phoenix Technologies to Firebird which was then renamed to Firefox. Over the years I have upgraded in place so I had assumed […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"activitypub_content_warning":"","activitypub_content_visibility":"","activitypub_max_image_attachments":3,"activitypub_interaction_policy_quote":"anyone","activitypub_status":"","footnotes":""},"categories":[18,13,2],"tags":[],"class_list":["post-5347","post","type-post","status-publish","format-standard","hentry","category-computer-software","category-linuxunix-related","category-techie-stuff"],"_links":{"self":[{"href":"https:\/\/www.suramya.com\/blog\/wp-json\/wp\/v2\/posts\/5347","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.suramya.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.suramya.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.suramya.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.suramya.com\/blog\/wp-json\/wp\/v2\/comments?post=5347"}],"version-history":[{"count":5,"href":"https:\/\/www.suramya.com\/blog\/wp-json\/wp\/v2\/posts\/5347\/revisions"}],"predecessor-version":[{"id":5352,"href":"https:\/\/www.suramya.com\/blog\/wp-json\/wp\/v2\/posts\/5347\/revisions\/5352"}],"wp:attachment":[{"href":"https:\/\/www.suramya.com\/blog\/wp-json\/wp\/v2\/media?parent=5347"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.suramya.com\/blog\/wp-json\/wp\/v2\/categories?post=5347"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.suramya.com\/blog\/wp-json\/wp\/v2\/tags?post=5347"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}