Tester Jobs Requirements (VIDEO GAME)

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"

Thats nice, but the dates are unixepoch and we dont 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:

datetime(start_time,unixepoch,localtime)

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.