SkypeのチャットのログをGmailに送って検索しやすくする

仕事でSkypeのチャットを使いまくっている。

別にチャットならSkypeじゃなく他のでもいいんだけど、Windows/Mac/Linux/iPhone/iPad/AndroidなどOSを問わずクライアントがあって、ファイルの送受信もできて、グループチャットができて、グループに名前を付けられるってことでSkypeを使ってる。音声やビデオはそんなに重視してない。

ただ問題があって、過去ログの検索が使いにくい。検索機能は一応あるんだけど、キーワードを入れて該当の発言を見ることができるだけで、前後の話の流れは見れない。だったら全部のログをロードしてから検索すればいいけど、それは重すぎて使い物にならない。あと、iPhoneから見たりできない。

そこで、ログをGmailにため込んで検索するというのを考えて、作ってみた。たいしたやつじゃないし、あんまりキレイに書いてないけどとりあえず。

skype2mail

Rubyで作った。Lionに最初からはいってるRuby(たぶん1.8.7)で動作した。

#! /usr/bin/ruby
# $ sudo gem install sqlite3-ruby

maindb = File.expand_path('~/Library/Application Support/Skype/(Skypeアカウント)/main.db')
last_filename = File.expand_path('~/.skype2mail.last')
max_days = 10
max_entries = 200

mail_config = {
  :server => 'smtp.example.com',
  :port => 25,
  :from => 'mail@example.com',
  :to => 'your_gmail_account@gmail.com',
}

require 'rubygems'
require 'sqlite3'
require 'net/smtp'
require 'nkf'

include SQLite3

# load last timestamp
from = 0
if File.exist?(last_filename)
  f = open(last_filename)
  from = f.read.to_i
  f.close
end

# pre query
to = nil
sql = "select timestamp from Messages where ? < timestamp order by timestamp limit 1"
db = Database.new(maindb)
db.execute(sql, from) do |row|
  to_time = Time.at(row[0])
  to = Time.local(to_time.year, to_time.month, to_time.day).to_i + (60 * 60 * 24 * max_days)
end
today = Time.local(Time.now.year, Time.now.month, Time.now.day).to_i
to = today if to == nil || today < to

# main query
sql = <<SQL
  select
    cht.topic,
    con.fullname,
    con.skypename,
    msg.timestamp,
    msg.body_xml
  from
    Messages msg
      inner join Chats cht on msg.chatname = cht.name
      inner join Contacts con on msg.author = con.skypename
  where
        ? < msg.timestamp
    and msg.timestamp < ?
  order by
    msg.timestamp asc
SQL

puts "searching... #{Time.at(from)} - #{Time.at(to)}"
last = from
data = {}
keys = []
db = Database.new(maindb)
db.execute(sql, from, to) do |row|
  chat = "#{row[0]}"
  name = "#{row[1] || row[2]}"
  time = row[3]
  body = "#{row[4]}"
  date = Time.at(time).strftime("%Y-%m-%d")
  keys << date
  data[date] = {} unless data.has_key?(date)
  data[date][chat] = [] unless data[date].has_key?(chat)
  data[date][chat] << [] if data[date][chat].empty? || data[date][chat].last.length == max_entries
  data[date][chat].last << { :name => name, :time => Time.at(time), :body => body }
  last = time
end
db.close

# mail
keys.uniq.sort.each do |date|
  data[date].each do |chat, list|
    list.each_index do |index|
      item = list[index]
      subject = "[skype] #{chat} #{date}" + (2 <= list.length ? " (#{index + 1}/#{list.length})" : "")
      subject_encoded = NKF.nkf('-M', subject)
      body =
        "<h1 style=\"margin: 0 0 1em 0; padding: 0.5em; background: #00aff1; color: #ffffff; font-size: 150%;\">" +
        subject +
        "</h1>\r\n"
      item.each do |msg|
        body +=
          "<h2 style=\"margin: 0.5em 0 0.5em 0; font-size: 100%;\">" +
          msg[:time].strftime("%H:%M") + " " +
          msg[:name] + "</h2>\r\n" +
          msg[:body].gsub(/\r\n|\r|\n/, "<br>") +
          "\r\n<hr>\r\n"
      end
      body_encoded = [NKF.nkf('-w8', body)].pack('m')

      mail = <<-MAIL
From: #{mail_config[:from]}
To: #{mail_config[:to]}
Subject: #{subject_encoded}
Date: #{Time::now.strftime("%a, %d %b %Y %X")}
Mime-Version: 1.0
Content-Type: text/html; charaset=utf-8
Content-Transfer-Encoding: base64

#{body_encoded}
      MAIL

      puts "sending... #{subject} : #{item.count} entries."
      Net::SMTP.start(mail_config[:server], mail_config[:port]) do |smtp|
        smtp.send_mail mail, mail_config[:from], mail_config[:to]
      end
      sleep 1
    end
  end
end

# save last timestamp
f = File.open(last_filename, 'w')
f.puts last
f.close

こんな感じ。コードの冒頭にも書いてあるけど、gemでsqlite3-rubyを事前にインストールしておいてね。

$ sudo gem install sqlite3-ruby

各種設定はコードの上の方にあるので、やりたいことに合わせて書いておく。

  • maindb
    • Skypeのデータベースファイルの場所。
  • last_filename
    • 最後に処理したログのタイムスタンプを保存するファイルのパス。
    • 次回の実行時に、このタイムスタンプ以降を処理するようになっている。
    • 初回はファイルが存在しない状態にしておく。
  • max_days
    • 一回の処理で最大何日分を扱うか。安全装置みたいなもの。
    • あんまり大量にするとメールサーバーに拒否されてしばらくメール送れなくなるので注意。
  • max_entries
    • 1通のメールに発言をいくつ含むか。
    • あんまり多いとエラーになる。
  • mail_config
    • 送信するメールに関する情報。
    • 面倒なので認証なしのメールサーバーを使ってる。認証したい人は頑張って直して欲しい。

このスクリプトを、Skypeがインストールしてあるマシン自体で実行する。まあmaindbのところをうまく書けば、別なマシンからでも実行できると思うけど試してない。

ログは、チャット単位で、1日分を1通のメールとして送る。ただし1日分がmax_entriesを超える場合は分割される。という感じ。名前の付いてないチャットはひとつのメールにまとめられると思う(違ったらごめん)。

これでGmail側でキーワード検索すれば、メール単位で読める。つまり前後の会話も見れるということ。

初回は、全ての過去ログの取り込み

まず初回は、一番古いログからすべてを送信しようとする。とはいえ、max_daysの日数分だけ処理したら止まるようになってる。止まったらもう一度実行すれば、続きをまた日数分だけ送る感じ。

なので、メールサーバーに拒否されないように、時間をあけながらちょっとずつ実行して、頑張って過去ログを全部Gmailに取り込む。過去ログは不要なら、last_filename のところにファイルを作って、昨日の日付あたり(のエポック秒)を書いておけばいい。

これは人それぞれなので参考になるかわからないけど、自分の場合は、max_daysが10、max_entriesが200で、cronで1時間おきに1回だけ実行するように仕込んだら、33時間ほどで約11ヶ月分のログ(メール数にして727件)をエラーも出ずに送ることができた。

ちなみに、1日分をまとめて送るという仕様上、スクリプトを実行した当日の分のログは送らないようになってる。前日までを送る。

今後のログを自動的に送る

過去ログの取り込みが終わったら、あとは今後のログを自動的に取り込ませるために、cronに登録しておけばいいと思う。

自分は、毎日00:01にやるようにしている。日付が変わったらすぐに前日分を取り込ませるイメージ。当たり前だけど、このときMacの電源が入ってないとだめ。まあ運悪く電源落ちてたとしても、翌日に2日分取り込まれるだけだし、急ぐなら手動でコマンド実行すればいいので、気にしなくていいかなと思ってる。

どうしても気にするなら1時間おきとかに実行すればいい。やり過ぎても空振りするだけなので安全のはず。

仕組み

Skypeのチャットのログは、Skype社のサーバーではなく、各自の手元の環境に保存されている。Macの場合は上記のコード中のmaindbで設定している場所。main.dbというファイルがそれで、SQLiteのデータベースになってる。

これをsqlite3コマンド(Macに最初から入ってる)で見てみたところ、割とシンプルなテーブル構造だった。

$ sqlite3 ~/Library/Application\ Support/Skype/nacookan/main.db 
SQLite version 3.7.7 2011-06-25 16:35:41
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
  • Chatsテーブルにチャット単位の情報
  • Contactsテーブルにユーザーの情報
  • Messagesテーブルにチャットのログ

こんな感じ。フィールド名もまあまあわかりやすかったので、関連性は推測できた。

あとはRubyからこのSQLiteのデータベースを見て、該当するデータをうまく取ってきて、チャット単位、日付単位でまとめて、メールで送る感じ。せっかくなのでhtmlメールにしている(面倒でbodyタグとか書いてないけど。。。)。

最後に処理したログの日時をファイルに書き出して次回利用したり、一度に処理する日数に制限をかけるために、先頭のログを拾ってそこから日数を計算したりしているけど、それ以外は結構素直なコードだと思う。

参考 : Skypeログ検索アプリもある

Skypeのチャットのログを検索して、前後のログとあわせて見るという、こんな有料のアプリがある(via Skypeのチャット履歴を検索してログをさかのぼれるMacアプリ「SkyChatViewer」がステキ)。これはこれで便利そう。まだ試してないけど、このGmailの取り組みがうまく行かない感じだったら試したいと思ってる。

まあでもやっぱりGmailに置いてあった方が、iPhone/iPadからでも見れたりするし、メールとSkypeをまとめて検索できるということでもあるのでいいかな。

おわり

ちなみに、iPhone/iPadSkypeチャットやるなら、公式よりもimoというアプリがとっても便利で、これについては以前書いた

Gmailにログを蓄積するチャットとしては、Google Talk(Gmail Chat)があるけど、実際試してみたら全然だめだった。Mac用の専用クライアントがなくて、WebブラウザiChatではファイルの送信できないし、グループチャットに名前を付けることもできない。Google的にやる気なくなっちゃったのかな。

このSkypeのログについて、なにか他にもいいアイディアあったら教えてください。