Firefox 3のplaces.sqliteデータベースからリンク元の記録を探してみる

今日はFirefox 3の話。

先日、なにかを検索している途中で、偶然こんなアート関係のブログを発見した。

MICHELANGELO PISTOLETTO - TRANS BORDER TRANS LIMIT TRANS GRESSION | LOOK INTO MY OWL

こんな絵が載っている。

(by Michelangelo Pistoletto)

この絵自体には、それほど興味をひかれなかったんだけど (といっても、最近小耳に挟んだある学者の理論によると、美術作品の大きさにはなにか重要な意味があるらしく、ひょっとすると、この絵も、そういった理論と関係した絵で、パソコンの小さな画像で見ても意味が無い絵なのかもしれない、という気もするけど、それはともかくとして) この絵よりも、ページ右手のほうの「LINKS」のコーナーが気になった。

なんか面白そうなブログがあるかもしれないぞ、毎日、三つぐらいずつリンクを順にチェックしていこう、と思って、デスクトップにこのページへのショートカットを置いておいた。

で、この「LINKS」の三番目に載ってる「Arrested Motion」というブログを試しに開いてみてみると、これがなかなか面白くて、このブログは早速フィードリーダーに登録することにした。

……そんなある日、 デスクトップのショートカットを間違えて削除してしまった。 「LINKS」のコーナーを一時的に見たかっただけなので、サイト名も全く覚えてないし、このページはどこにもブクマしてない。どうしよう!

整理すると、要するに「Arrested Motion」というブログを最初に見たときの「リンク元」が知りたい、ということ。

俺が使ってるFirefoxにはPlacesというデータベースが入っていて、ページ閲覧の記録が保存されている。ここから情報を取り出すことができないだろうか? ……と思って色々やってみたらできたので以下にメモっておくことにする。


まず、このデータベースは以下の場所にある。(Windows XPの場合)

「C:\Documents and Settings\ユーザ名\Application Data\Mozilla\Firefox\Profiles\ランダムのフォルダ名\places.sqlite

(Firefox 3のPlacesにSQLite Database Browserでアクセスしてみる - builder by ZDNet Japanより)

このデータベースにアクセスするためのFirefoxのアドオンなどもあるらしいが、アドオンを入れるのは嫌だったので、SQLiteのコマンドラインツール「sqlite3.exe」をもらってきて、それを使ってアクセスすることとした。

今回の用事に関係のあるテーブルは

  • moz_places
  • moz_historyvisits

であった。

参考

色々試行錯誤したのだが、結論から書くと、

select h.from_visit from moz_historyvisits h join moz_places p 
on h.place_id=p.id where p.url like 'http://arrestedm%' 
group by h.from_visit order by h.visit_date;

こんな風に書くことで、

from_visit
                  • -
115959 153530 (中略) 163643 164391 0 164399 164400 164401

こんなふうに数字が出てくる。この数字が、リンク元ページのデータを格納した「moz_historyvisits」テーブルのidを指している。

したがって、(SQLは不慣れなので頭がこんがらからないよう) まず以下のようにVIEWを作成し、

create view arrested1 as select h.from_visit from moz_historyvisits h 
join moz_places p on h.place_id=p.id where p.url 
like 'http://arrestedm%' group by h.from_visit 
order by h.visit_date;

以下のようにjoinしてみたところ、

select p.url from moz_historyvisits h join moz_places p 
on h.place_id=p.id join arrested1 d on d.from_visit=h.id;
url
                                                  • -
http://blogsearch.google.com/blogsearch?hl=en..... http://lookintomyowl.com/michelangelo-pistole..... http://arrestedmotion.com/ http://arrestedmotion.com/2009/03/openings-lu.....

といった具合にURLがずらずらと出てきて、そのなかから、削除してしまったショートカットのブログのURLを見つけ出すことができた。

以上のまとめとして、例えば、

sqlite> .mode line

とした上で、以下のようにすると、

select fp.url, p.url from moz_places p join moz_historyvisits h on h.place_id=p.id 
join moz_historyvisits f on f.from_visit=h.id 
join moz_places fp on f.place_id=fp.id 
where fp.url like 'http://d.hatena.ne.jp%' 
and p.url not like 'http://d.hatena%' 
group by f.from_visit;

よそのサイトを経由して、はてなダイアリーのエントリーを閲覧した履歴を以下のように見ることができる。

(閲覧したページ) url = http://d.hatena.ne.jp/mereco/20090112/p1
(そのリンク元) url = http://b.hatena.ne.jp/

(例: ハテブのトップページで紹介されていた、はてなダイアリーのエントリーをクリックしたケース)

(閲覧したページ) url = http://d.hatena.ne.jp/klov/20081203/1228321767
(そのリンク元) url = http://mixi.jp/view_bbs.pl?id=36322416&comm_id=40241

(例: mixiで紹介されていたケース)

(もっとSQLを短く省略して書くこともできるだろうか??)

しかし、この「リンク元」の情報って、なんの必要があってデータベースに記録されているんだろう? ちょっと不思議。