MozillaZine


Export downloads history from Firefox SQLite DB

Discussion of general topics about Mozilla Firefox
w0mbat
 
Posts: 38
Joined: March 6th, 2009, 8:03 am
Location: kiev.ua

Post Posted April 7th, 2013, 10:02 am

This is mostly an answer, not a question, however I would be grateful for any optimizations or other hints for that quite ugly solution (have no experience with SQL). Example OS is Linux but main points can be applied for others as well.
The topic is how to export FF downloads history from the places.sqlite DB (or from downloads.sqlite, but it's a simpler case).

Previously I used to do it using sqlite utility like following:
Code: Select all
echo "
.mode line
.output $HOME/downloads.log

SELECT name,source,target,tempPath,datetime(startTime/1000000, 'unixepoch', 'localtime'),datetime(endTime/1000000, 'unixepoch', 'localtime'),state,referrer,currBytes,maxBytes,mimeType,autoResume
FROM moz_downloads;
" |
ionice -c3 nice -n 15 sqlite3 $FF_PROFILE_PATH/downloads.sqlite


Recently I noticed that it doesn't work and downloads.sqlite has very small size, almost empty (~3kB)—however all info is present in FF GUI.
The reason was that all the data for past was moved to the places.sqlite using different table and records structure. It's quite strange for me taking into account new dl logs are still written to downloads.sqlite.

Anyway, I wanted to save it from places.sqlite as well and the solution for this case is more complex.

Firstly, copy places.sqlite to some other folder and operate with it. I used /tmp/.

Secondly, we need some related IDs from moz_anno_attributes table. I'm not sure they are the same for all people, so let's get them:

Code: Select all
echo ".mode line

    SELECT id,name
    FROM moz_anno_attributes
    WHERE name
    LIKE '%downloads%';
" |
sqlite3 /tmp/places.sqlite


For me they are 34, 35 and 43. Replace two occurrences of these numbers for your own if differ.

Next comes the main mess. AWK call is used for pretty-printing—just insert a blank line between different entries.

Code: Select all
echo ".mode csv

    SELECT place_id,content,mime_type,dateAdded,lastModified
    FROM moz_annos
    WHERE anno_attribute_id IN (34,35,43);

    SELECT DISTINCT place_id,url,title,last_visit_date
    FROM moz_annos,moz_places
    WHERE (
        moz_annos.anno_attribute_id IN (34,35,43)
        AND moz_annos.place_id=moz_places.id
    );
" |
sqlite3 /tmp/places.sqlite |
sort |
awk '
    BEGIN {FS=","};
    {
    if ($1 != save) {
        print ""
    }
    print $0
    save=$1
}
'


The result isn't very nice, but for me it's enough.
Unfortunately, seems like referrer and MIME type fields are lost completely for these entries (please correct if I'm wrong).

Return to Firefox General


Who is online

Users browsing this forum: No registered users and 2 guests