[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