#!/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/\&/&/g;
s/\"/"/g;
s/\'/'/g;
s/</</g;
s/>/>/g;
}
return $x;
}