The History database, found in '/home/
$ sqlite3 History .tables
downloads presentation urls
keyword_search_terms segment_usage visit_source
meta segments visits
The downloads table is the focus of this post. It is structured as follows:
$ sqlite3 History .schema | grep downloads CREATE TABLE downloads (id INTEGER PRIMARY KEY,full_path LONGVARCHAR NOT NULL,url LONGVARCHAR NOT NULL,start_time INTEGER NOT NULL,received_bytes INTEGER NOT NULL,total_bytes INTEGER NOT NULL,state INTEGER NOT NULL);
For the uninitiated, the table schema tells us that each record contains 'id', 'full_path', 'url', 'start_time', 'received_bytes', 'total_bytes', and 'state' fields. We can extract the data in csv format, as follows:
sqlite -header -csv History "SELECT * FROM downloads"
That's nice, but the dates are unixepoch and we don't know what the 'state' integer means, though it can be deduced from the 'received_bytes' and 'total_bytes' fields and studying a known source of data. I determined that state 1 and 2 mean "complete" and "incomplete" download respectively.
To convert unixepoch to local time, we use the SQLite datetime function. We tell datetime to take the start_time integer, interpret it as unixepoch time, and translate to localtime. Exerpted from the rest of the query, it looks like this:
For clarity in the output, it would be nice to convert the state integer to its text equivalent. We can do this with the case command. The case command is the if/then statement of SQLite. In its simplest application it takes the form 'CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END' where x is the field data, w is a comparison value, and r is the result that is returned. It is easiest to understand when we apply it to our current circumstance:
CASE WHEN state=1 THEN 'complete' WHEN state=2 THEN 'incomplete' ELSE 'unknown' END
Putting it all together, we can obtain nicely formatted output from the downloads table of the Chrome History database with the following command:
sqlite3 -header -csv History "SELECT id,full_path, url, datetime(start_time,'unixepoch','localtime') AS date, received_bytes, total_bytes, CASE WHEN state=1 THEN 'complete' WHEN state=2 THEN 'incomplete' ELSE 'unknown' END AS state FROM downloads"
The caps in the commands are not required, but are designed to make reading the SQLite operators easier. You may have noted the "AS" operators after the datetime function and case expressions. It serves to replace the expressions as the header for that column in your csv output.
Your output should look like:
id,full_path,url,date,received_bytes,total_bytes,state 1,/home/slosleuth/Downloads/keepnote_0.7.3-1_all.deb,http://keepnote.org/keepnote/download/keepnote_0.7.3-1_all.deb,"2011-06-23 12:04:46",500022,500022,complete 2,/home/slosleuth/Downloads/iso/nbcaine2.0.dd.gz,http://www.caine-live.net/Downloads/nbcaine2.0.dd.gz,"2011-06-24 13:25:42",85962197,0,incomplete 3,/home/slosleuth/Downloads/iso/nbcaine2.0gz.md5.txt,http://www.caine-live.net/Downloads/nbcaine2.0gz.md5.txt,"2011-06-24 13:27:23",51,51,complete
I hope this helps you with SQLite queries in general and specifically to parse the data in the Google Chrome downloads table of the History SQLite database.