PC上のSkypeログをPHPで抽出してみた

Skype のログは PC 上に main.db というファイルで格納されていて、sqlite で処理できると聞き、PHP の SQLite3*1拡張で発言ログを抽出してみました。

$ strings main.db | head -1
SQLite format 3

僕の main.db は 636MB ありますが、それでも処理出来たので大抵行けると思います。このサイズだと Skyperious *2では処理できず固まったので自作した次第です。

ツール

PHP + SQLite3 拡張が必要です。SQLite3 は PHP5.3 以降はデフォで入ってます。

  • 使い方
$ php skyperead.php
Usage: php skyperead.php <main.db>
$ php skyperead.php main.db
$
  • skypelog フォルダが出来てて、その中にファイルが生成されます。
$ ls skypelog/ | wc
    515     542   15911
  • １つチャットでないファイルがあって、トピック一覧が入ってます。
$ wc skypelog/topicmap.txt
  256   427 18614 skypelog/topicmap.txt

仕様

  • 発言をチャットグループで分別してファイルに保存
    • グループチャット(3名以上のチャット)はトピックをファイル名に
    • プライベートチャット(2名のチャット)は相手の名前をファイル名に
  • トピック一覧を topicmap.txt に保存。

sqlite

$ sqlite3 main.db
SQLite version 3.7.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
Accounts        ChatMembers     Conversations   Participants    Voicemails
Alerts          Chats           DbMeta          SMSes
CallMembers     ContactGroups   LegacyMessages  Transfers
Calls           Contacts        Messages        Videos
  • 自分のアカウント
$ sqlite3  main.db
sqlite> SELECT skypename FROM Accounts ;
yoya(略)
  • 発言の抽出
$ sqlite3 main.db
sqlite> SELECT timestamp,chatname,from_dispname,body_xml FROM Messages LIMIT 100;
  • トピックの抽出
$ sqlite3 main.db
sqlite> SELECT name,topic FROM Chats LIMIT 10;

考慮点

  • チャットグループのIDになる文字列はある

#<作成者>/$<初めに誘った人>;<ハッシュっぽい16進文字列x16文字>の形式

    • 但しグループチャットもプライベートチャットも区別しない。ただメンバー数が違うだけ
  • グループチャットは ID が固定と考えてよい。
  • でも、プライベートチャットはコンタクトから選んで話す場合、ID が新規に割り当たる。つまりコロコロ変わる。

処理の流れ

  • Accounts から自分のアカウント確認
    • 後でプライベートチャットから相手の skype名を調べる時に、自分以外の発言という条件で区別したい為。
  • Chats からマッピング作成
    • activemembers が3名以上のチャットは name(チャットグループID相当) とトピック文字列
      • 同じチャットで複数のトピックがある場合は、一番長く設定されていたトピックを記録する。
    • 2名のみのチャットでは name と friendlyname
  • Messages から発言抽出
    • トピック文字列のマップがある場合は、Chats の topic でファイル名を作成して発言を記録
    • でない場合は、from_dispname でファイル名作成。
    • from_dispname が分からない時は (Chats の) friendlyname で。
  • 尚、ファイル名に ":/ " の三文字は嫌なので、_ に置換してます。(: があると smb でマウントすると化けて見える)

課題

  • プライベートチャットの名寄せ的な処理がうまく行ってないのを、要改善
  • アカウントを買えたり PC をリプレースした場合は、複数の main.db をマージしなきゃなので、その処理を考える。Chats には guid なるものがあるので、もしかしたらそれで行ける？
  • 最後のトピック名が反映されない不具合がありそう。
  • SELECT でカラム指定すれば、速くなると思う。
  • モジュール化して色々やろうかと一瞬思ったけど、会社で Skype 使うの禁止されたのでモチベーション無し。

最後に感想

仕事のやり取りで Skype を使っていたのですが、思ってた以上に自分仕事してました。何もかも懐かしい。。

この程度のツールを作るのに５時間かかるとかショックでした。慣れてた頃は、１時間ちょっとで作れましたが。DB スキーマの理解力が落ちてる、PHP の関数とか忘れてる。やはり普段からプログラミングし続けないとダメです。

*1:SQLite v2 では処理出来ないので、PHP5.2 以前にデフォで入ってる sqlite 拡張(sqlite3 じゃない方)では駄目です。

*2:32bit版と64bit版を試して、どちらもすぐ固まりました