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") })
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
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
At this point, you should be able to query for Urls through the models:
> Url.first => # > 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)
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
Let's retry that same query from before:
> 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
Another example is when Visit uses 'url' as a foreign key to Url rather than 'url_id':
class Visit '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", ...]
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
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
What urls do I type into the address bar frequently?
ChromeSpy.most_frequently_typed_addresses
What have I downloaded and how big are the files?
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!
For the last three years, Presently users have been taking advantage of our integration with SharePoint. Today we're excited to offer SharePoint 2010 integration. If you're using older versions of SharePoint, don't worry; we will be keeping our old webpart in place so that you can continue to use Presently with older versions. 
