#!/usr/bin/perl -w
#
# Convert a Openfire user export file to a ejabberd mysql user database
#
# See http://www.igniterealtime.org/projects/openfire/plugins/userimportexport/readme.html
# for spec of XML User export format
#
# Licensed in the same terms as perl
#
# Author: Pedro Melo <melo@simplicidade.org>
# Date: 2007/09/20
# Version: 1.0
#

use strict;
use DBI;
use XML::LibXML;
use XML::LibXML::XPathContext;
use Getopt::Long;

# FIXME: not all state are covered yet
my %states = (
  '3 -1 -1' => [ 'B', 'N', 'N' ],
  '2 0 -1' => [ 'F', 'O', 'N' ],
  '2 -1 -1' => [ 'F', 'N', 'N' ],
  '1 -1 -1' => [ 'T', 'N', 'N' ],
  '0 -1 -1' => [ 'N', 'N', 'N' ],
  '0 0 -1' => [ 'N', 'O', 'N' ],
  '0 -1 2' => [ 'N', 'I', 'N' ],
);

my $db;
my $host = 'localhost';
my $user;
my $pass;

my $ok = GetOptions(
  "database=s" => \$db,
  "host=s"     => \$host,
  "user=s"     => \$user,
  "password=s" => \$pass,
);
my $user_file = $ARGV[0];

if (!$ok || !$user_file) {
  print STDERR "Usage: x-convert-openfire-to-ejabberd OPTIONS FILE\n\n";
  print STDERR "Use the OPTIONS to select the destination MySQL database,\n";
  print STDERR "and use your Openfire user export file as the final parameter.\n\n";
  print STDERR "Valid options are:\n";
  print STDERR "    --host: hostname of the MySQL server (default: localhost)\n";
  print STDERR "    --database: database to use in the MySQL server\n";
  print STDERR "    --user: authenticate as\n";
  print STDERR "    --password: authenticate with\n";
  print STDERR "\n";
  exit(1);
}

# Connect to database
my $dbh = DBI->connect(
  "dbi:mysql:database=$db;host=$host",
  $user,
  $pass,
  {
    AutoCommit => 0,
    RaiseError => 1,
    PrintError => 1,
  }
);
if (!$dbh) {
  print STDERR "FATAL: could not connect to the database: $DBI::errstr\n";
  exit(1);
}
$dbh->do(q{SET NAMES 'utf8'});

# Parse the XML file
my $parser = XML::LibXML->new;
my $doc = $parser->parse_file($user_file);
my $xp = XML::LibXML::XPathContext->new($doc);

# Scan for users
foreach my $user ($xp->findnodes('/Openfire/User')) {
  # fetch login and password
  my $login = $xp->findvalue('Username', $user);
  my $passw = $xp->findvalue('Password', $user);
  $dbh->do(q{
    INSERT INTO users (username, password) VALUES (?, ?)
  }, undef, $login, $passw);
  
  # fetch some vcard fields: email, and name
  my $email = exml($xp->findvalue('Email', $user) || '');
  my $name  = exml($xp->findvalue('Name', $user)  || '');
  my $nick  = exml($login);
  my $vcard = q{<vCard xmlns='vcard-temp' version='2.0' prodid='-//HandGen//NONSGML vGen v1.0//EN'>};
  $vcard .= qq{<FN>$name</FN>} if $name;
  $vcard .= qq{<EMAIL><INTERNET/><USERID>$email</USERID></EMAIL>} if $email;
  $vcard .= qq{<NICKNAME>$nick</NICKNAME>};
  $vcard .= q{</vCard>};
  $dbh->do(q{
    INSERT INTO vcard (username, vcard) VALUES (?, ?)
  }, undef, $login, $vcard);
  
  # fetch and insert roster items, including groups
  foreach my $item ($xp->findnodes('Roster/Item', $user)) {
    my $r_jid  = $item->getAttribute('jid');
    my $r_name = $item->getAttribute('name') || '';
    my $r_ask  = $item->getAttribute('askstatus');
    my $r_recv = $item->getAttribute('recvstatus');
    my $r_sub  = $item->getAttribute('substatus');

    my $key = "$r_sub $r_ask $r_recv";
    if (!exists $states{$key}) {
      die "State combination '$key' not found in valid state table\n";
      exit(1);
    }

    my ($sub, $ask, $server) = @{$states{$key}};
    
    $dbh->do(q{
      INSERT INTO rosterusers (username, jid, nick, subscription, ask, server, type,   askmessage, subscribe   )
                       VALUES (?,        ?,   ?,    ?,            ?,   ?,      'item', '',         '' )
    }, undef, $login, $r_jid, $r_name, $sub, $ask, $server);
    
    # deal with groups also
    foreach my $group ($xp->findnodes('Group', $item)) {
      $group = $group->textContent;
      next unless $group;

      $dbh->do(q{
        INSERT INTO rostergroups ( username, jid, grp )
                          VALUES ( ?,        ?,   ?   )
      }, undef, $login, $r_jid, $group);
    }
  }
  
  $dbh->commit;
 # print "User: $login   Passw: $passw   Name: $name  Email: $email\n";
}

$dbh->disconnect;


sub exml {
  # fast path for the commmon case:
  return $_[0] unless $_[0] =~ /[&\"\'<>]/;
  
  my $x = shift;
  for ($x) {
    s/\&/&amp;/g;
    s/\"/&quot;/g;
    s/\'/&apos;/g;
    s/</&lt;/g;
    s/>/&gt;/g;
  }
  return $x;
}


<!-- Localized -->