ツール‎ > ‎

Skype (frozen)

(とりあえず)Mac OS X で、Skype の履歴を SQLite3 のデータベースから抜いてくるための SQL。

部屋ごと:

skype_id="knaka123"
(
  echo '<html><head><meta http-equiv="content-type" content="text/html; charset=UTF-8"></head><body><ul>'
  echo "SELECT '<li>[', coalesce(c.displayname, m.from_dispname) AS roomname, '] ', m.from_dispname, ' (', strftime('%Y/%m/%d %H:%M', m.timestamp, 'unixepoch', 'localtime'), '): ', coalesce(m.identities, m.dialog_partner, replace(m.body_xml, x'0A', '<br />')) AS message FROM messages m LEFT OUTER JOIN conversations c ON c.identity = m.chatname WHERE roomname <> '' AND message IS NOT NULL ORDER BY roomname, m.timestamp;" | sqlite3 -separator "" "$HOME/Library/Application Support/Skype/$skype_id/main.db"
  echo '</ul></body></html>'
) > skype.html

時系列:

skype_id="knaka123"
(
  echo '<html><head><meta http-equiv="content-type" content="text/html; charset=UTF-8"></head><body><ul>'
  echo "SELECT '<li>[', coalesce(c.displayname, m.from_dispname) AS roomname, '] ', m.from_dispname, ' (', strftime('%Y/%m/%d %H:%M', m.timestamp, 'unixepoch', 'localtime'), '): ', coalesce(m.identities, m.dialog_partner, replace(m.body_xml, x'0A', '<br />')) AS message FROM messages m LEFT OUTER JOIN conversations c ON c.identity = m.chatname WHERE roomname <> '' AND message IS NOT NULL ORDER BY m.timestamp;" | sqlite3 -separator "" "$HOME/Library/Application Support/Skype/$skype_id/main.db"
  echo '</ul></body></html>'
) > skype.html
Comments