#!/usr/bin/ruby

# jabberd2ejabberd.rb - Migrate the jabberd2 MySQL database to ejabberd
#
# Warning! This tool has not widely been tested! Use at your own risk
# Things NOT covered with this script
# * Privacy lists will not be migrated
# * A couple tabels in jabberd2 don't have a counterpart in ejabberd like
#   disco-items, motd-*, vacation-settings
# * vcard extval fields are not supported
# Things working
# * roster groups and items are being properly migrated
# * vcard is partially being converted (Only some fields for now)
# * The queue and private storage items are being migrated (stripping
#   them from the route and iq stanzas)
#
# Copyright (C) 2009 by Daniel Willmann <daniel@totalueberwachung.de>
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU Public License as published by
# the Free Software Foundation; version 2 of the license.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU Lesser Public License for more details.

require 'optparse'
require 'mysql'
require 'rexml/document'

class Table
  attr_accessor :map
  def initialize(name)
    @table = name
    @map = Array.new()
  end
  def name
    @table
  end
  def feeddb(result)
    result.each_hash{ |row|
      map = Hash.new()
      row.each_pair{ |key,value|
        map[key] = value
      }
      @map << map
    }
  end
  def to_sql
    def escape(str)
      if (str.class == String)
        "'" + Mysql.quote(str) + "'"
      else
        str
      end
    end

    tmplist = [ ]

    @map.each { |row|
      tmplist << "INSERT INTO `#{@table}` (`#{row.keys.join('`, `')}`) VALUES (#{row.values.collect{|str| escape(str)}.join(', ')})"
    }
    return tmplist
  end
end

def find_private_storage(xml)
  xml.elements.each{ |element|
    if element.attributes["xmlns"] == 'jabber:iq:private'
      return element.elements[1].to_s
    end
    return find_private_storage(element)
  }
end

def find_spool_data(xml)
  xml.elements.each{ |element|
    if element.attributes["xmlns"] == 'http://jabberd.jabberstudio.org/ns/component/1.0'
      return element.elements[1].to_s
    end
    if element.attributes["xmlns:comp"] == 'http://jabberd.jabberstudio.org/ns/component/1.0'
      return element.elements[1].to_s
    end
    return find_private_storage(element)
  }
end

class Convert
  def Convert.private(table, vhost)
    tmp = Table.new("private_storage")
    map = Array.new()
    table.map.each{ |row|
      user, host = row["collection-owner"].split("@")
      if vhost and vhost != host
        next
      end
      nrow = Hash.new()
      nrow["username"] = user
      nrow["namespace"] = row["ns"]
      private_data = REXML::Document.new(row["xml"])
      nrow["data"] = find_private_storage(private_data)
      map << nrow
    }
    tmp.map = map
    return tmp
  end
  def Convert.authreg(table, vhost)
    tmp = Table.new("users")
    map = Array.new()
    table.map.each{ |row|
      if vhost and vhost != row["realm"]
        next
      end
      nrow = Hash.new()
      nrow["username"] = row["username"]
      nrow["password"] = row["password"]
      map << nrow
    }
    tmp.map = map
    return tmp
  end
  def Convert.roster_items(table, vhost)
    tmp = Table.new("rosterusers")
    map = Array.new()
    table.map.each{ |row|
      user, host = row["collection-owner"].split("@")
      if vhost and vhost != host
        next
      end
      nrow = Hash.new()
      nrow["username"] = user
      nrow["jid"] = row["jid"]
      nrow["nick"] = row["name"]
      if not nrow["nick"]
        nrow["nick"] = ""
      end
      nrow["ask"] = row["ask"]=="1"?"Y":"N"
      nrow["subscription"] = "N"
      if row["to"] == "1" and row["from"] == "1"
        nrow["subscription"] = "B"
      elsif row["to"] == "1"
        nrow["subscription"] = "T"
      elsif row["from"] == "1"
        nrow["subscription"] = "F"
      end

      # TODO: Find out what these mean
      nrow["type"] = "item"
      nrow["server"] = "N"
      map << nrow
    }
    tmp.map = map
    return tmp
  end
  def Convert.roster_groups(table, vhost)
    tmp = Table.new("rostergroups")
    map = Array.new()
    table.map.each{ |row|
      user, host = row["collection-owner"].split("@")
      if vhost and vhost != host
        next
      end
      nrow = Hash.new()
      nrow["username"] = user
      nrow["jid"] = row["jid"]
      nrow["grp"] = row["group"]
      map << nrow
    }
    tmp.map = map
    return tmp
  end
  def Convert.status(table, vhost)
    tmp = Table.new("last")
    map = Array.new()
    table.map.each{ |row|
      user, host = row["collection-owner"].split("@")
      if vhost and vhost != host
        next
      end
      nrow = Hash.new()
      nrow["username"] = user
      nrow["seconds"] = row["last-login"]
      map << nrow
    }
    tmp.map = map
    return tmp
  end
  def Convert.queue(table, vhost)
    tmp = Table.new("spool")
    map = Array.new()
    table.map.each{ |row|
      user, host = row["collection-owner"].split("@")
      if vhost and vhost != host
        next
      end
      nrow = Hash.new()
      nrow["username"] = user
      xml = REXML::Document.new(row["xml"])
      nrow["xml"] = find_spool_data(xml)
      nrow["seq"] = row["object-sequence"]
      map << nrow
    }
    tmp.map = map
    return tmp
  end
  def Convert.vcard(table, vhost)
    tmp = Table.new("vcard")
    tmp2 = Table.new("vcard_search")
    map = Array.new()
    map2 = Array.new()
    table.map.each{ |row|
      user, host = row["collection-owner"].split("@")
      if vhost and vhost != host
        next
      end
      nrow = Hash.new()
      nrow2 = Hash.new()
      nrow["username"] = user
      nrow2["username"] = user
      vcard = REXML::Document.new()
      vcard = vcard.add_element("vCard", {"xmlns" => "vcard-temp", "version" => "2.0", "prodid" => "-//HandGen//NONSGML vGen v1.0//EN"})
      if row["fn"]
        vcard.add_element("FN")
	vcard.elements["FN"].text = row["fn"]
        nrow2["fn"] = row["fn"]
      end
      if row["n-family"] or row["n-given"] or row["n-middle"]
        n = vcard.add_element("N")
        if row["n-family"]
	  n.add_element("FAMILY")
	  n.elements["FAMILY"].text = row["n-family"]
          nrow2["family"] = row["n-family"]
	end
        if row["n-given"]
	  n.add_element("GIVEN")
	  n.elements["GIVEN"].text = row["n-given"]
          nrow2["given"] = row["n-given"]
	end
        if row["n-middle"]
	  n.add_element("MIDDLE")
	  n.elements["MIDDLE"].text = row["n-middle"]
          nrow2["middle"] = row["n-middle"]
	end
        if row["n-prefix"]
	  n.add_element("PREFIX")
	  n.elements["PREFIX"].text = row["n-prefix"]
	end
        if row["n-suffix"]
	  n.add_element("SUFFIX")
	  n.elements["SUFFIX"].text = row["n-suffix"]
	end
      end
      if row["nickname"]
        vcard.add_element("NICKNAME")
	vcard.elements["NICKNAME"].text = row["nickname"]
        nrow2["nickname"] = row["nickname"]
      end
      if row["bday"]
        vcard.add_element("BDAY")
	vcard.elements["BDAY"].text = row["bday"]
        nrow2["bday"] = row["bday"]
      end
      if row["title"]
        vcard.add_element("TITLE")
	vcard.elements["TITLE"].text = row["title"]
      end
      if row["role"]
        vcard.add_element("ROLE")
	vcard.elements["ROLE"].text = row["role"]
      end
      if row["tel"]
        email = vcard.add_element("TEL")
	email.add_element("HOME")
	email.add_element("VOICE")
	email.add_element("NUMBER")
	email.elements["NUMBER"].text = row["tel"]
      end
      if row["adr-street"] or row["adr-extadd"]
        adr = vcard.add_element("ADR")
	if row["adr-street"]
	  adr.add_element("STREET")
	  adr.elements["STREET"].text = row["adr-street"]
	end
	if row["adr-extadd"]
	  adr.add_element("EXTADD")
	  adr.elements["EXTADD"].text = row["adr-extadd"]
	end
	if row["adr-pobox"]
	  adr.add_element("POBOX")
	  adr.elements["POBOX"].text = row["adr-pobox"]
	end
	if row["adr-locality"]
	  adr.add_element("LOCALITY")
	  adr.elements["LOCALITY"].text = row["adr-locality"]
          nrow2["locality"] = row["adr-locality"]
	end
	if row["adr-region"]
	  adr.add_element("REGION")
	  adr.elements["REGION"].text = row["adr-region"]
	end
	if row["adr-pcode"]
	  adr.add_element("PCODE")
	  adr.elements["PCODE"].text = row["adr-pcode"]
	end
	if row["adr-country"]
	  adr.add_element("CTRY")
	  adr.elements["CTRY"].text = row["adr-country"]
          nrow2["ctry"] = row["adr-country"]
	end
      end
      if row["email"]
        email = vcard.add_element("EMAIL")
	email.add_element("INTERNET")
	email.add_element("USERID")
	email.elements["USERID"].text = row["email"]
        nrow2["email"] = row["email"]
      end
      if row["url"]
        vcard.add_element("URL")
	vcard.elements["URL"].text = row["url"]
      end
      if row["org-orgname"] or row["org-orgunit"]
        org = vcard.add_element("ORG")
	if row["org-orgname"]
	  org.add_element("ORGNAME")
	  org.elements["ORGNAME"].text = row["org-orgname"]
	  nrow2["orgname"] = row["org-orgname"]
	end
	if row["org-orgunit"]
	  org.add_element("ORGUNIT")
	  org.elements["ORGUNIT"].text = row["org-orgunit"]
	  nrow2["orgunit"] = row["org-orgunit"]
	end
      end
      if row["tz"]
        vcard.add_element("TZ").text = row["tz"]
      end
      if row["geo-lat"] or row["geo-lon"]
        org = vcard.add_element("GEO")
	if row["geo-lat"]
	  org.add_element("LAT").text = row["geo-lat"]
	end
	if row["geo-lon"]
	  org.add_element("LON").text = row["geo-lon"]
	end
      end
      if row["sort-string"]
        vcard.add_element("SORT-STRING").text = row["sort-string"]
      end
      if row["photo-type"]
        photo = vcard.add_element("PHOTO")
	photo.add_element("TYPE")
	photo.elements["TYPE"].text = row["photo-type"]
	photo.add_element("BINVAL")
	photo.elements["BINVAL"].text = row["photo-binval"]
      end
      if row["logo-type"]
        photo = vcard.add_element("LOGO")
	photo.add_element("TYPE")
	photo.elements["TYPE"].text = row["logo-type"]
	photo.add_element("BINVAL")
	photo.elements["BINVAL"].text = row["logo-binval"]
      end
      if row["sound-phonetic"]
        photo = vcard.add_element("SOUND")
	photo.add_element("PHONETIC").text = row["sound-phonetic"]
	photo.add_element("BINVAL").text = row["sound-binval"]
      end
      if row["desc"]
        vcard.add_element("DESC")
	vcard.elements["DESC"].text = row["desc"]
      end
      if row["note"]
        vcard.add_element("NOTE").text = row["note"]
      end
      if row["key-type"] or row["key-cred"]
        org = vcard.add_element("KEY")
	if row["key-type"]
	  org.add_element("TYPE").text = row["key-type"]
	end
	if row["key-cred"]
	  org.add_element("CRED").text = row["key-cred"]
	end
      end
      if row["rev"]
        vcard.add_element("REV").text = row["rev"]
      end
      nrow["vcard"] = vcard.to_s

      htmp = Hash.new()
      nrow2.each_pair{ |key, value|
        if value
	  htmp["l"+key] = value.downcase
	end
      }

      map << nrow
      map2 << nrow2.merge(htmp)
    }
    tmp.map = map
    tmp2.map = map2
    return [tmp, tmp2]
  end
end

options = {:user => 'jabberd2', :host => 'localhost', :j2db => 'jabberd2',
	:ejdb => 'ejabberd', :apply => false, :vhost => nil}

opts = OptionParser.new do |opts|
  opts.banner = "Usage: #{$0} [options] <bdbdir>"
  opts.on("-a", "--apply", "Modify the mysql database") do |v|
    options[:apply] = v
  end
  opts.on("-u", "--user <username>", "Mysql username") do |v|
    options[:user] = v
  end
  opts.on("-p", "--password <password>", "Mysql password") do |v|
    options[:password] = v
  end
  opts.on("-h", "--host <hostname>", "Host the mysql server runs on") do |v|
    options[:host] = v
  end
  opts.on("-d", "--jabberd2db <database>", "Which database to use for jabberd2") do |v|
    options[:j2db] = v
  end
  opts.on("-e", "--ejabberddb <database>", "Which database to use for ejabberd") do |v|
    options[:ejdb] = v
  end
  opts.on("-v", "--vhost <vhost>", "VHost to convert") do |v|
    options[:vhost] = v
  end
end

opts.parse!

#if (ARGV.length == 0)
#  puts opts.help
#  exit 1
#end

intables = [ ]

jabberd2 = ["private", "authreg", "roster-items", "roster-groups", "status", "queue", "vcard"]

mydb = Mysql.real_connect(options[:host], options[:user], options[:password], options[:j2db])

jabberd2.each { |name|
  puts "Found Table #{name}"
  table = Table.new(name)
  result = mydb.query("SELECT * from `#{name}`")
  table.feeddb(result)
  intables << table
}

outtables = [ ]

intables.each{ |table|
  puts "Converting table #{table.name}"
  outtables << Convert.send(table.name.tr('-','_').to_sym, table, options[:vhost])
}

outtables.flatten!

if (options[:apply])
  mydb = Mysql.real_connect(options[:host], options[:user], options[:password], options[:ejdb])
end

outtables.each { |tbl|
  if (!options[:apply])
    puts "Table #{tbl.name}"
    tbl.to_sql.each {|statement|
      puts statement
    }
  else
    puts "Adding statements for table #{tbl.name}"
    tbl.to_sql.each {|statement|
      begin
        mydb.query(statement)
      rescue Mysql::Error => error
        puts "Error while querying '#{statement}'"
        puts error
      end
    }
  end
}

if (options[:apply])
  puts "Database updated"
else
  puts "Not modified the database"
end