[JDEV] jabberd 1.4 to 2.0 migration script
Trejkaz Xaoza
trejkaz at xaoza.net
Wed Jan 21 06:34:58 CST 2004
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Attached is a Ruby script for migrating jabberd1.4 spool directories to
jabberd2 mysql. Unlike the script provided with jabberd2 itself, this one
migrates all the data, or at least all the data possible under the jabberd2
database schema.
It migrates offline messages, which was the primary reason for writing it.
Warning: this script is in beta state. I cannot guarantee it will work on all
spool directories, and I claim no responsibilities for what damage it might
do to your system. The last time I ran it, a tiger jumped in my window,
swear to god. 'Buyer beware', YMMV, and so forth.
Requirements:
- Ruby (tested on 1.8.1 but I don't think I used any new features so it
might work on earlier. If it does work on earlier, let me know and I'll
update this!)
- rexml
- mysql-ruby
To use:
- cd to your spool directory.
- type: /path/to/migrate.rb jabber.example.com > jabber-data.sql
It will sit and whir for a while, telling you every user it's processing as it
goes through its work.
At present it uses the mysql-ruby library *only* for the string escape method.
I know this is not ideal, however I'm working in a situation where I can't
run this script on the server so I have to move all the data back to home,
then back to the server. It could, however, be trivially modified to connect
to Mysql directly. I chose Ruby because Perl's XML::DOM didn't feel like it
had enough support for namespaces, though I'm probably wrong. Either way it
took about the same amount of time to write.
A lot of the things it generates aren't 100% efficient. Particularly, the
VCard migration is really heavy on the number of SQL statements it generates.
I could have done it better, but the most important thing was to get it to
work right, fast. I know also that the VCard conversion isn't 100%. There
are several VCard fields which are permitted in the VCard multiple times and
as far as I know the database for jabberd2 only allows one of each (that is,
the ADR and EMAIL elements.)
It seems to work for me, with a bit of hacking of the script values I was able
to set this up on a test server with a different hostname, and my account was
set up correctly.
Give me feedback, let me know if it works for you, or if it breaks. If it
breaks, send me a stack trace to me directly (I have TMDA running on my mail
account so you will have to go through a confirmation process, no big deal
but I'll warn you anyway.)
Have fun, chaps.
TX
- --
'Every sufficiently advanced technology is indistinguishable from magic' -
Arthur C Clarke
'Every sufficiently advanced magic is indistinguishable from technology' - Tom
Graves
Email: Trejkaz Xaoza <trejkaz at xaoza.net>
Web site: http://xaoza.net/trejkaz/
Jabber ID: trejkaz at jabber.xaoza.net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
iD8DBQFADnH0mifzwBMPxxoRAqrdAJ0fFIW5NblNyctOSfmqlneQCixWJQCfVuwn
84mk3mfSBF33rn4Ins/qIic=
=jx3I
-----END PGP SIGNATURE-----
-------------- next part --------------
#!/usr/bin/ruby
require "rexml/document"
require "mysql"
if not ARGV[0] then
$stderr.puts("ERROR: You must specify the realm (server id) to use.")
Kernel.exit(1)
end
realm = ARGV[0]
if not File.stat(realm).directory? then
$stderr.puts("ERROR: You must be in the spool directory.")
Kernel.exit(1)
end
Dir.foreach(realm) do |filename|
username = filename.clone;
username.gsub!(/\.xml$/, '')
owner = username + "@" + realm
filename = realm + "/" + filename
if not File.stat(filename).directory? then
$stderr.puts "Processing #{owner}..."
puts "INSERT INTO authreg (username, realm) VALUES ('" + Mysql.escape_string(username) + "', '" + filename + "');"
file = File.new(filename)
doc = REXML::Document.new(file)
doc.root.elements.each do |element|
qname = element.name
if element.namespace != nil then
qname = element.namespace + " " + qname
end
case qname
when "jabber:iq:auth:0k zerok"
puts "UPDATE authreg SET hash = '#{element.elements["hash"].text}', token = '#{element.elements["token"].text}', " +
"sequence = #{element.elements["sequence"].text} WHERE username = '#{Mysql.escape_string(username)}' AND realm = '#{realm}';"
when "jabber:iq:auth password"
puts "UPDATE authreg SET `password` = '#{Mysql.escape_string(element.text)}' " +
"WHERE username = '#{Mysql.escape_string(username)}' AND realm = '#{realm}';"
when "jabber:iq:last query"
puts "INSERT INTO logout (`collection-owner`, time) VALUES ('#{Mysql.escape_string(owner)}', #{element.attributes["last"]});"
when "jabber:iq:roster query"
element.elements.each("item") do |item|
item_subscription = item.attributes["subscription"]
if (item_subscription == "to" || item_subscription == "both") then
item_to = 1
else
item_to = 0
end
if (item_subscription == "from" || item_subscription == "both") then
item_from = 1
else
item_from = 0
end
if (item.attributes["ask"] == "subscribe") then
item_ask = 1
else
# Note: item_ask = 2 isn't possible since jabberd 1.4 doesn't store pending unsubscribe state.
item_ask = 0
end
item_jid = item.attributes["jid"]
item_name = item.attributes["name"]
if item_name then
puts "INSERT INTO `roster-items` (`collection-owner`, jid, name, `to`, `from`, ask) " +
"VALUES ('#{Mysql.escape_string(owner)}', '#{Mysql.escape_string(item_jid)}', '#{Mysql.escape_string(item_name)}', " +
"#{item_to.to_s}, #{item_from.to_s}, #{item_ask.to_s});"
else
puts "INSERT INTO `roster-items` (`collection-owner`, jid, `to`, `from`, ask) " +
"VALUES ('#{Mysql.escape_string(owner)}', '#{Mysql.escape_string(item_jid)}', " +
"#{item_to.to_s}, #{item_from.to_s}, #{item_ask.to_s});"
end
item.elements.each("group") do |group|
if (group.text) then
puts "INSERT INTO `roster-groups` (`collection-owner`, jid, `group`) " +
"VALUES ('#{Mysql.escape_string(owner)}', '#{Mysql.escape_string(item_jid)}', '#{Mysql.escape_string(group.text)}');";
end
end
end
when "jabber:x:offline foo"
element.elements.each("message") do |message|
puts "INSERT INTO queue (`collection-owner`, `xml`) VALUES ('#{Mysql.escape_string(owner)}', '#{Mysql.escape_string(message.to_s)}');"
end
when "vcard-temp vCard", "vcard-temp vcard" # typo
puts "INSERT INTO vcard (`collection-owner`) VALUES ('#{Mysql.escape_string(owner)}');"
def vcard_iter
yield "fn", "FN"
yield "nickname", "NICKNAME"
yield "url", "URL"
yield "tel", "TEL/NUMBER"
yield "email", "EMAIL[USERID]/USERID"
yield "title", "TITLE"
yield "role", "ROLE"
yield "bday", "BDAY"
yield "desc", "DESC"
yield "n-given", "N/GIVEN"
yield "n-family", "N/FAMILY"
yield "adr-street", "ADR/STREET"
yield "adr-extadd", "ADR/EXTADD"
yield "adr-locality", "ADR/LOCALITY"
yield "adr-region", "ADR/REGION"
yield "adr-pcode", "ADR/PCODE"
yield "adr-country", "ADR/COUNTRY"
yield "org-orgname", "ORG/ORGNAME"
yield "org-orgunit", "ORG/ORGUNIT"
end
vcard_iter { |vcard_table_field, vcard_xpath|
vcard_field = element.elements[vcard_xpath]
if (vcard_field and vcard_field.text) then
puts "UPDATE vcard SET `#{vcard_table_field}` = '#{Mysql.escape_string(vcard_field.text)}' " +
"WHERE `collection-owner` = '#{Mysql.escape_string(owner)}';"
end
}
else
if element.attributes["j_private_flag"] == "1" then
puts "INSERT INTO private (`collection-owner`, ns, xml) VALUES ('#{Mysql.escape_string(owner)}', '#{element.namespace}', '#{Mysql.escape_string(element.to_s)}');"
else
# We ignore these because non-private arbitrary storage is out of the question.
end
end
end
end
end
$stderr.puts "Completed processing for #{realm}!"
More information about the JDev
mailing list