Safari? SQL? Seriously?

So let’s say you your friend has a wordpress blog and you weren’t he wasn’t smart enough to back up the files in the theme and then working late after a long day to tweak some details, as the grand finale of a comedy of errors involving lots of open tabs and multiple browsers, the css file that actually lays out the whole theme was truncated in a manner which is most approproriately described as ‘obliteratisticated’.

First your friend comes to the realization that while WordPress stores revisions of posts and pages, editing themes is without a safety net. As this sinks in, he realizes that while the site has been reloaded on Firefox, Safari is sitting there with the page loaded with the old css. Hallelujiah, he can just dig that out of the cache… there is a cache right?

This was rocking Safari 3.1.2 on OS X 10.5.5.

I’m writing this because at point there was a lot of google and a bit of thrashing, uhm, for my friend.

You might find a ~/Library/Caches/com.apple.Safari/Cache.db and think there is something in there, and waste a bunch of time but this will only lead to swearing and teeth gnashing.  At some point Safari decided that was not the place to put the Cache.  If you have a newer version of Safari this is not your cache, this is an artifact from some Safari of days gone by.

The Cache.db file you want is in /private/var/
/private/var/folders/sn/SomeLongRidiculousHashedUpBS/-Caches-/com.apple.Safari/Cache.db to be exact.

That big blob of a file is a little sql db and you can search through it with sqlite.

sqlite2 Cache.db

Now that is is open you can get the lay of the land.

sqlite> .schema
CREATE TABLE cfurl_cache_blob_data
     (entry_ID INTEGER PRIMARY KEY,
      response_object BLOB,
      request_object BLOB,
      receiver_data BLOB, 
      proto_props BLOB,
      user_info BLOB);
CREATE TABLE cfurl_cache_response
    (entry_ID INTEGER 
                   PRIMARY KEY 
                   AUTOINCREMENT
                   UNIQUE,
     version INTEGER,
     hash_value INTEGER,
     storage_policy INTEGER,
     request_key TEXT UNIQUE,
     time_stamp NOT NULL
                        DEFAULT
                        CURRENT_TIMESTAMP);
CREATE TABLE cfurl_cache_schema_version
    (schema_version INTEGER);
CREATE INDEX proto_props_index 
    ON cfurl_cache_blob_data(entry_ID);
CREATE INDEX request_key_index
    ON cfurl_cache_response(request_key);
CREATE INDEX time_stamp_index
    ON cfurl_cache_response(time_stamp);

The data you want is in the cfurl_cache_blob_data table in the receiver_data column.

If you know something about the contents of what you are looking for then it is going to be easy:


select receiver_date 
          from cfurl_cache_blob_data 
          where receiver_data
          like '%What I Know%'

If you know the file name you should be able to use where request_object like '%filename%'

This version of Safari for whatever reason, and maybe it is my settings, doesn’t seem to be caching images, which is pretty awesome…

Hey Apple… WTF?


2 responses to “Safari? SQL? Seriously?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: