I use the Chrome history tab when I forget about something I've looked up in the past. I initially thought that the data would be stored in a CSV or XML file and thought I could do some string munging for kicks and giggles. To my delight, when I looked in the "Application Support" directory for Chrome, I found several data-rich sqlite databases ready for mining. With a few Ruby tricks, I found some cool data. All the code this article covers is available on the chrome_spy project.

With chrome_spy, you can answer some of these queries:

what have I searched for recently?

which sites do I visit most frequently?

what urls do I type into the address bar frequently?

what have I downloaded and how big are the files?

And that's only the surface. Let's dive straight into the technical details.

Since the data is stored in sqlite, I decided to use trusty old ActiveRecord to wrap around the tables:

require 'sqlite3' require 'active_record' ActiveRecord::Base.establish_connection({ :adapter => "sqlite3", :database => File.expand_path("~/Library/Application Support/Google/Chrome/Default/History") }) 1 require 'sqlite3' require 'active_record' ActiveRecord :: Base . establish_connection ( { : adapter = > "sqlite3" , : database = > File . expand_path ( "~/Library/Application Support/Google/Chrome/Default/History" ) } )

I've been using Chrome for a while now, so there were multiple archived 'History' databases (e.g. History Index 2010-07), but the most recent database is named 'History'. Rather than inspecting the schema on each of the tables, ActiveRecord has a module SchemaDumper that generates a very readable schema dump:

puts ActiveRecord::SchemaDumper.dump 1 puts ActiveRecord :: SchemaDumper . dump

From here, it's pretty straightforward to map the tables to ActiveRecord models. For each 'create_table' declaration, I declared a new model. For example, to define a model for urls:

class Url < ActiveRecord::Base end 1 class Url < ActiveRecord :: Base end

At this point, you should be able to query for Urls through the models:

> Url.first => #<url id: 1, url: "http:> > Url.where(:title => "Google").count => 21 > Url.first.last_visit_time => 12940646404770210 1 > Url . first = > #<url id: 1, url: "http:> > Url.where(:title => "Google").count => 21 > Url.first.last_visit_time => 12940646404770210

Everything was looking peachy up until the last_visit_time. At first I thought it was an epoch timestamp, or a JS timestamp. After looking at a few other timestamps, I noticed that it's 17 digits long rather than the usual 10 digits. The frustrating part was that some fields used epoch timestamps, but other fields would use this 17-digit timestamp. I wrote a little helper module to clean up the typecast from these columns:

module TimestampAccessors def timestamp_accessors(*attributes) attributes.each do |attr| name = attr.to_s # Some timestamps have 17 digits # Since 10000000000 is year 2286, so I'm assuming that no dates are longer # than 10 digits define_method(name) { raw = read_attribute(name).to_s.slice(0, 10) Time.at(raw.to_i) } define_method(name+'=') { |t| write_attribute(name, t.to_i) } end end end ActiveRecord::Base.extend(TimestampAccessors) 1 module TimestampAccessors def timestamp_accessors ( * attributes ) attributes . each do | attr | name = attr . to_s # Some timestamps have 17 digits # Since 10000000000 is year 2286, so I'm assuming that no dates are longer # than 10 digits define_method(name) { raw = read_attribute(name).to_s.slice(0, 10) Time.at(raw.to_i) } define_method(name+'=') { |t| write_attribute(name, t.to_i) } end end end ActiveRecord::Base.extend(TimestampAccessors)

Then for every table that has timestamp columns, I can declare them in the model. For example:

class Url < ActiveRecord::Base timestamp_accessors :last_visit_time end 1 class Url < ActiveRecord :: Base timestamp_accessors : last_visit_time end

Let's retry that same query from before:

> Url.first.last_visit_time => Mon Jan 03 06:24:00 -0800 2011 1 > Url . first . last_visit_time = > Mon Jan 03 06 : 24 : 00 - 0800 2011

That's much better.

Some table names and column names don't follow Rails conventions, so there's a little extra work to specify associations and some tables. For example, the SegmentUsage model is backed by the 'segment_usage' table rather than 'segment_usages':

class SegmentUsage < ActiveRecord::Base set_table_name "segment_usage" end 1 class SegmentUsage < ActiveRecord :: Base set_table _ name "segment_usage" end

Another example is when Visit uses 'url' as a foreign key to Url rather than 'url_id':

class Visit < ActiveRecord::Base belongs_to :url, :foreign_key => 'url' end 1 class Visit < ActiveRecord :: Base belongs_to : url , : foreign_key = > 'url' end

With just these thin wrappers, we can easily come up with queries to answer the questions at the beginning of this article. To find the most recent searches, we can do:

> ChromeSpy.recent_searches => ["intridea", "thegist", "dimsum icons", ...] 1 > ChromeSpy . recent_searches = > [ "intridea" , "thegist" , "dimsum icons" , . . . ]

The definition for this method is just a simple ActiveRecord query:

def recent_searches KeywordSearchTerm.includes('url').order('urls.last_visit_time desc').map(&:term) end 1 def recent_searches KeywordSearchTerm . includes ( 'url' ) . order ( 'urls.last_visit_time desc' ) . map ( & : term ) end

At this point, you should take a break and share your search history with a friend. Context-free search terms can be hilarious and embarrassing. A few my girlfriend could not stop laughing at:

super meat boy

exercise mix tapes

huntsville bed intruder

factory girl

haml (she thought I repeatedly misspelled 'ham')

To answer the other questions at the beginning of the article:

Which sites do I visit most frequently?

ChromeSpy.most_frequent_sites 1 ChromeSpy . most_frequent _ sites

What urls do I type into the address bar frequently?

ChromeSpy.most_frequently_typed_addresses 1 ChromeSpy . most_frequently_typed _ addresses

What have I downloaded and how big are the files?

ChromeSpy.recent_downloads 1 ChromeSpy . recent _ downloads

While ChromeSpy may not be the most useful example, it shows how ActiveRecord can be applied outside of Rails. A similar thought process can be reused for other problems where quick data manipulations or reporting is needed. Whether those reports are useful, or just plain silly is entirely up to you. Now go forth and find some funny recent searches you've done!