skype_exporter

#!/usr/bin/env ruby

require "sqlite3"
require "fileutils"
require "pathname"
require "json"
require "pp"

class MainDb
  def initialize(path)
    @path = path
    @db = SQLite3::Database.new(path)
  end
  def get_data(query)
    columns, *rows = @db.execute2(query)
    rv = []
    rows.each do |row|
      ht = {}
      (0...columns.size).each do |i|
        ht[columns[i]] = row[i] unless row[i].nil?
      end
      rv << ht
    end
    rv
  end
  def get_all_data(table)
    get_data("select * from #{table}")
  end
end

class LogFile
  def initialize
    @msgs = []
  end
  def cleanup_xml(msg)
    # Still need to be converted:
    #   <partlist alt="">
    msg.gsub(%r[<ss type="(?:\S+?)">(\S+)</ss>]){ $1 }
       .gsub(%r[<a href="([^"]+)">\1</a>]){ $1 }
       .gsub(%r[&apos;], "'")
       .gsub(%r[&quot;], '"')
       .gsub(%r[&gt;], ">")
       .gsub(%r[&lt;], "<") # This interferes with XML unescaping
       .gsub(%r[&amp;], "&")
  end

  def add!(tm, from, msg)
    tm = Time.at(tm).utc.strftime("%Y-%m-%d %H:%M:%S")
    @msgs << [tm, from, cleanup_xml(msg)]
  end
  def to_s
    @msgs.sort.map{|tm,from,msg|
      "#{tm} #{from}: #{msg}\n"
    }.join("\n")
  end
end

class SkypeExporter
  def initialize(db_path, output_path)
    @db = MainDb.new(db_path)
    @path = Pathname(output_path)
    @logs = {}
  end
  def log(key)
    @logs[key] ||= LogFile.new
  end
  def save_binary_file!(file_name, data)
    file_path = @path + file_name
    file_path.dirname.mkpath
    file_path.open('wb') do |fh|
      fh.write(data)
    end
    file_path
  end
  def get_all_data(table)
    @db.get_all_data(table)
  end
  def save_profile_attachments!(skype_name, data)
    path_end_idx = 12+data[12..-1].index("\x00")
    path = data[12...path_end_idx]
    image_start_idx = path_end_idx+1+data[path_end_idx+1..-1].index("\x00")
    image = data[image_start_idx+1..-1]
    warn "Attachment isn't called MyAvatar" unless path == "profile://#{skype_name}/MyAvatar"
    warn "Attachment for #{skype_name} doesn't look like JPEG image" unless image[0,4].bytes == "\xFF\xD8\xFF\xE0".bytes
    save_binary_file!("attachments/#{skype_name}.jpg", image)
  end
  def save_avatar!(skype_name, data)
    warn "Avatar for #{skype_name} might be incorrect" unless data[0, 5].bytes == "\x00\xFF\xD8\xFF\xE0".bytes
    save_binary_file!("avatars/#{skype_name}.jpg", data[1..-1])
  end
  def save_text_file!(file_name, converter, data)
    return if data.empty?
    (@path + file_name).open('w') do |fh|
      data.each_with_index do |row, i|
        send("convert_#{converter}", row) do |key, val|
          fh.puts "#{key}: #{val}"
        end
        fh.puts "" unless i == data.size-1
      end
    end
  end
  # accounts and contacts are very similar, so use the same code for both
  def convert_account(row)
    row.each do |key, val|
      case key
      when "capabilities",
           "synced_email",
           "cbl_profile_blob",
           "authorization_certificate",
           "saved_directory_blob",
           "buddyblob"
        # pass
      when "avatar_image"
        yield key, save_avatar!(row["skypename"], val)
      when "profile_attachments"
        yield key, save_profile_attachments!(row["skypename"], val)
      else
        yield key, val.inspect
      end
    end
  end
  def convert_basic(row)
    row.each do |key, val|
      yield key, val.inspect
    end
  end
  def convert_transfer(row)
    row.each do |key, val|
      case key
      when "nodeid",
           "extprop_transfer_alias",
           "chatmsg_guid"
        # pass
      when "starttime", "finishtime"
        yield key, val == 0 ? "-" : Time.at(val)
      else
        yield key, val.inspect
      end
    end
  end
  def convert_message(row)
    a  = row["author"]
    fd = row["from_dispname"]
    b  = row["body_xml"]
    tm = row["timestamp"]
    dp = row["dialog_partner"] || a
    path = "#{dp}.txt"

    log(dp).add! tm, "#{fd} (#{a})", b if b

    row.each do |key, val|
      case key
      when "guid",
           "extprop_chatmsg_ft_index_timestamp",
           "crc"
        # pass
      when "timestamp"
        yield key, val == 0 ? "-" : Time.at(val)
      else
        yield key, val.inspect
      end
    end
  end
  def export!
    @path.rmtree if @path.exist?
    @path.mkpath
    save_text_file! "accounts.txt", :account, get_all_data("Accounts")
    save_text_file! "contacts.txt", :account, get_all_data("Contacts")
    save_text_file! "sms.txt", :basic, get_all_data("SMSes")
    save_text_file! "legacy_messages.txt", :basic, get_all_data("LegacyMessages")
    save_text_file! "alerts.txt", :basic, get_all_data("Alerts")
    save_text_file! "transfers.txt", :transfer, get_all_data("Transfers")
    save_text_file! "voicemails.txt", :basic, get_all_data("Voicemails")
    save_text_file! "call_members.txt", :basic, get_all_data("CallMembers")
    save_text_file! "calls.txt", :basic, get_all_data("Calls")
    save_text_file! "chat_members.txt", :basic, get_all_data("ChatMembers")
    save_text_file! "chats.txt", :basic, get_all_data("Chats")
    save_text_file! "contact_groups.txt", :basic, get_all_data("ContactGroups")
    save_text_file! "conversations.txt", :basic, get_all_data("Conversations")
    save_text_file! "messages.txt", :message, get_all_data("Messages")
    save_text_file! "participants.txt", :basic, get_all_data("Participants")
    save_text_file! "video_messages.txt", :basic, get_all_data("VideoMessages")
    save_text_file! "videos.txt", :basic, get_all_data("Videos")
    @logs.each do |key, log|
      save_binary_file!("logs/#{key}.txt", log.to_s)
    end
  end
end

se = SkypeExporter.new("main.db", "output")
se.export!