Converting from UBB to vBulletin - PMs, SQL, ruby, PHP, and me... and an SQL question

[Tawnos]

The vBulletin importer script did not properly handle or import the PMs from our UBB Classic 6.5 board. Finding no definitive answers online, I decided to "roll my own" solution in Ruby, giving me something to figure out in a language I haven't used much (beyond a bit of rails).

Being new to Ruby, I decided to edge in slowly. I needed some way to parse usernames and associated numbers, both on the old and new board. To that end, I did the following:

File.open("vb_ubb_fixedids.txt", "w") do |outfile|
  
  File.open("vb_ubb_userid_importuserid.txt", "r") do |infile|
    while $. != 3427
      line = infile.gets.split('|')
      outfile.puts line[0].chop + "|" + line[0]
    end
  end
  
end

File.open("vb_sorted.txt", "w") do |outfile|  
  File.open("vb_ubb_fixedids.txt", "r") do |infile|
    array = infile.readlines.sort
    array.each {|out| outfile.puts out}
  end
end

Not the prettiest, two separate programs to do what could be done in one, a line or two that are extraneous... but hey, learning, right?

Now, I had to get all import userids and associate them with a name. To that end, I ran the following in the members directory. Afterwards, I just did a find and replace to get rid of the leading 0's (which, I learned later, could have been done with a simple .to_i)

File.open("ubb_usernames_numbers.txt", "w") do |outfile|

  Dir["*.cgi"].each do |infilename|
    File.open(infilename, "r") do |infile|
      outfile.puts infilename.slice(0,8) + "|" + infile.readlines[15]
    end
  end

end

Then, the meat and bones of my application, parsing PMs from UBB into something which can be inserted into the vBulletin tables.
Some of it's quite ugly, but it's producing insert statements that work as intended, so I'm okay with that:

   def read_segment(totalpms,segname,infile)
  retArray = Array.new(totalpms)
  until (line = infile.gets).strip.eql?(");")
    qpairs = line.split(/(=>)/)
    next if qpairs[0].chomp.eql?(segname) || qpairs[0].strip.eql?("")
    left = qpairs[0].split(/q+?(\W+?)(.*)(\1)/).at(2).to_s
    right = qpairs[2].split(/q+?(\W+?)(.*)(\1)/).at(2).to_s
    retArray[left.to_i] = right
  end
  retArray
end

numbers = Array.new(5235)
names = Array.new(5235)
totalpms = -1

File.open("/home/tawnos/workspace/pm_importer/vb_ubb_fixedids.txt","r") do |infile|
  infile.each do |line|
    linearray = line.split("|")
    numbers[linearray[0].to_i] = linearray[1].to_i
  end
end

File.open("/home/tawnos/workspace/pm_importer/ubb_usernames_numbers.txt","r") do |infile|
  infile.each do |line|
    linearray = line.split("|")
    names[linearray[0].to_i] = linearray[1] 
  end
end
Dir["*.cgi"].each do |filename|
  
  File.open(filename,"r") do |infile|
    
    until (line = infile.gets).strip.eql?(");")
      qpairs = line.split(/(=>)/)
      next if qpairs[0].chomp.eql?("%pm_topic_data = (") || qpairs[0].strip.eql?("")
  
      left = qpairs[0].split(/q+?(\W+?)(.*)(\1)/).at(2).to_s
      right = qpairs[2].split(/q+?(\W+?)(.*)(\1)/).at(2).to_s
  
      if left.eql?("from")
        puts numbers[right.to_i]
        puts names[right.to_i]
      end
      if left.eql?("to")
        puts numbers[right.to_i]
        puts names[right.to_i]
      end
      if left.to_s.eql?("replies")
        totalpms = right.to_i + 1
      end
      if left.to_s.eql?("subject")
        subject = right
      end
    end # until
  
    puts totalpms
    pm_date = read_segment(totalpms,"%pm_date = (",infile)
    pm_time = read_segment(totalpms,"%pm_time = (",infile)
    pm_post = read_segment(totalpms,"%pm_post = (",infile)
    pm_to = read_segment(totalpms,"%pm_to = (",infile)
    pm_from = read_segment(totalpms,"%pm_from = (",infile)
    pm_icon = read_segment(totalpms,"%pm_icon = (",infile)
    pm_signature = read_segment(totalpms,"%pm_signature = (",infile)
    
    
    File.open("testinsert.txt","a") do |outfile|
        outfile.puts "INSERT INTO pmtext(fromuserid,fromusername,title,"
        outfile.puts "message,touserarray,iconid,dateline,showsignature,allowsmilie,importpmid) "
        outfile.puts "VALUES"
     totalpms.times do |i|
        pm_date[i].scan(/(\w+)-(\w+)-(\w+)/) {|month,day,year| pm_date[i] = year << "-" << month << "-" << day }

        if pm_time[i].split(" ")[1].strip.eql?("PM") && !pm_time[i].split(" ")[0].split(":")[0].strip.eql?("12")
          pm_time[i] = pm_time[i].split(" ")[0]
          puts pm_time[i].split(":")[0].to_i + 12
          pm_time[i] = (pm_time[i].split(":")[0].to_i + 12).to_s << ":" << pm_time[i].split(":")[1].to_i.to_s
        end
        
        time = Time.parse(pm_date[i].to_s << " " << pm_time[i]).to_i
        
        outfile.puts "("
        # fromuserid
        outfile.puts "'" << numbers[pm_from[i].to_i].to_s.chomp << "',"
        #fromusername
        outfile.puts "'" << names[pm_from[i].to_i].to_s.chomp << "',"
        #title
        outfile.puts "'" << subject.gsub(/[']/,'\\\\\'').chomp << "',"
        #message
        outfile.puts "'" << pm_post[i].to_s.gsub(/[']/,'\\\\\'').chomp << "',"
        #touserarray
        outfile.puts pm_to[i].to_s.chomp << ","
        #iconid
        outfile.puts pm_icon[i].to_s.chomp << ","
        #dateline
        outfile.puts time.to_s << ","
        #showsignature
        if pm_signature[i].to_s.chomp.eql?("yes")
          outfile.puts "1,"
        else
          outfile.puts "0,"
        end
        #allowsmilie
        outfile.puts "1,"
        #importpmid
        outfile.puts filename.slice(0,8).to_s.to_i + i
        
        if i == totalpms-1
          outfile.puts ")"
        else
          outfile.puts "),"
        end
      end
        outfile.puts ";"
    end
  
  end

end

Now, I know the following is *EXTREMELY* insecure, as ANYTHING can be executed versus the server... but I need something from which I can copy the text of the created files and run server side (no ssh access, and the vbulletin text field won't accept multiple thousand lines of post. This code simply connects to the database and executes what I paste into it (Thanks to for the idea/help:

// If the form has been submitted
<?
if(isset($_POST['query']))
{
// Set Mysql Variables
$host = 'localhost';
$user = 'root';
$pass = '';
$db = 'ex';
$table = 'comment';

// Set global variables to easier names
$command = $_GET['command'];

// Connect to Mysql, select the correct database, and run teh query which adds the data gathered from the form into the database
mysql_connect($host,$user,$pass) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
mysql_query($command) or die(mysql_error());
}
else
{

// If the form has not been submitted, display it!
?>
<form method='POST' action='<? echo'$PHP_SELF'; ?>'>
Name : <input type='text' name='uname'><br><br>
Comment : <input type='text' name='command'><br><br>
<input type='hidden' name='query' value='set'>
<input type='submit' value='Post your comment'>
</form>
<?
}
?>

Hope that works, will do a couple tests before running the big queries...never used PHP before.

Finally, my SQL question:
I have all these PMs in the pmtext database, will this SQL query work to add them to the "pm" database:

INSERT INTO pm (pmtextid,userid,importpmid) SELECT pmtextid,touserarray,importpmid FROM pmtext WHERE importpmid > 0

Thanks for reading this far, hope this helps anyone else needing to make the move from UBB to vBull where the PM importer doesn't work, and thanks to those who answer my question .

 

[Tawnos]

Well, nobody responded, but I did figure it all out. Putting this out here so anyone in the future who needs PMs when impex fails can use it.

Two files need to be created:

The first has a list of userids and importuserids from the database. It can be generated using the admin control panel, under maintenance > execute SQL query: "SELECT userid,importuserid FROM user WHERE importuserid != 0". Copy this into a textfile (the script below expects it to be named "vb_ubb_userid_importuserid.txt"). Replace the spaces in between the numbers with a "|". If I recall correctly, I used a regular expression to do this.. something like (\d+)(\s+)(\d+) and replace with \1\|\2 (first match, |, second match)

The second file correlates old userids with usernames. This was done using a simple (now fixed so it can be directly used) ruby script inside the copied members directory. The copied member's directory should have only the ########.cgi files in it.
username_number_ubb.rb

File.open("ubb_usernames_numbers.txt", "w") do |outfile|

  Dir["*.cgi"].each do |infilename|
    File.open(infilename, "r") do |infile|
      outfile.puts infilename.slice(0,8).to_i.to_s << "|" << infile.readlines[15]
    end
  end

end

Finally, in the base pm_topics directory, I created a simple shell script to handle running my script inside each directory, then concatenating the results into a .sql file. Keep in mind, my program was not written with superb efficiency in mind. For the 18,000 or so pms contained in the 6,656 files, the script takes about 25 seconds.
generatesql.sh

#!/bin/sh

for X in $(find -maxdepth 1 -mindepth 1 -type d); do
  cd $X
  ruby /home/tawnos/workspace/pm_importer/translate_pm.rb
  cd ..
done

find ./ -name "testinsert.txt" | xargs cat >> fullinsert.sql

Obviously, change the /home/tawnos... to wherever the file is on your system.

Finally, the completed translation ruby code:
translate_pm.rb

def read_segment(totalpms,segname,infile)
  retArray = Array.new(totalpms)
  until (line = infile.gets).strip.eql?(");")
    qpairs = line.split(/(=>)/)
    next if qpairs[0].chomp.eql?(segname) || qpairs[0].strip.eql?("")
    left = qpairs[0].split(/q+?(\W+?)(.*)(\1)/).at(2).to_s
    right = qpairs[2].split(/q+?(\W+?)(.*)(\1)/).at(2).to_s
    retArray[left.to_i] = right
  end
  retArray
end

numbers = Array.new(5235)
names = Array.new(5235)
totalpms = -1

File.open("/home/tawnos/workspace/pm_importer/vb_ubb_fixedids.txt","r") do |infile|
  infile.each do |line|
    linearray = line.split("|")
    numbers[linearray[0].to_i] = linearray[1].to_i
  end
end

File.open("/home/tawnos/workspace/pm_importer/ubb_usernames_numbers.txt","r") do |infile|
  infile.each do |line|
    linearray = line.split("|")
    names[linearray[0].to_i] = linearray[1] 
  end
end
Dir["*.cgi"].each do |filename|
  
  File.open(filename,"r") do |infile|
    
    until (line = infile.gets).strip.eql?(");")
      qpairs = line.split(/(=>)/)
      next if qpairs[0].chomp.eql?("%pm_topic_data = (") || qpairs[0].strip.eql?("")
  
      left = qpairs[0].split(/q+?(\W+?)(.*)(\1)/).at(2).to_s
      right = qpairs[2].split(/q+?(\W+?)(.*)(\1)/).at(2).to_s
  
      if left.to_s.eql?("replies")
        totalpms = right.to_i + 1
      end
      if left.to_s.eql?("subject")
        subject = right
      end
    end # until
  
    #puts totalpms
    pm_date = read_segment(totalpms,"%pm_date = (",infile)
    pm_time = read_segment(totalpms,"%pm_time = (",infile)
    pm_post = read_segment(totalpms,"%pm_post = (",infile)
    pm_to = read_segment(totalpms,"%pm_to = (",infile)
    pm_from = read_segment(totalpms,"%pm_from = (",infile)
    pm_icon = read_segment(totalpms,"%pm_icon = (",infile)
    pm_signature = read_segment(totalpms,"%pm_signature = (",infile)
    
    
    File.open("testinsert.txt","a") do |outfile|
        outfile.print "INSERT INTO pmtext(fromuserid,fromusername,title,"
        outfile.print "message,touserarray,iconid,dateline,showsignature,allowsmilie,importpmid) "
        outfile.print "VALUES"
     totalpms.times do |i|
        pm_date[i].scan(/(\w+)-(\w+)-(\w+)/) {|month,day,year| pm_date[i] = year << "-" << month << "-" << day }

        if pm_time[i].split(" ")[1].strip.eql?("PM") && !pm_time[i].split(" ")[0].split(":")[0].strip.eql?("12")
          pm_time[i] = pm_time[i].split(" ")[0]
          #puts pm_time[i].split(":")[0].to_i + 12
          pm_time[i] = (pm_time[i].split(":")[0].to_i + 12).to_s << ":" << pm_time[i].split(":")[1].to_i.to_s
        end
        
        time = Time.parse(pm_date[i].to_s << " " << pm_time[i]).to_i
        
        outfile.print "("
        # fromuserid
        outfile.print "'" << numbers[pm_from[i].to_i].to_s.chomp << "',"
        #fromusername
        outfile.print "'" << names[pm_from[i].to_i].to_s.chomp << "',"
        #title
        outfile.print "'" << subject.gsub(/[']/,'\\\\\'').chomp << "',"
        #message
        outfile.print "'" << pm_post[i].to_s.gsub(/[']/,'\\\\\'').chomp << "',"
        #touserarray
        outfile.print numbers[pm_to[i].to_i].to_s.chomp << ","
        #iconid
        outfile.print pm_icon[i].to_s.chomp << ","
        #dateline
        outfile.print time.to_s << ","
        #showsignature
        if pm_signature[i].to_s.chomp.eql?("yes")
          outfile.print "1,"
        else
          outfile.print "0,"
        end
        #allowsmilie
        outfile.print "1,"
        #importpmid
        outfile.print filename.slice(0,8).to_s.to_i + i
        
        if i == totalpms-1
          outfile.print ")"
        else
          outfile.print "),"
        end
      end
        outfile.puts ";"
    end
  
  end

end

Note, the array lengths are both number of users + 1 (so you can just do direct user matching, that is, get username for user 4 by doing names[4].to_s).

Putting that all together, there's a SQL file generated, one insert per line. Mine was about 9 megs. Now, if you have access to the server via shell, great, copy it over and just pipe the data into sql. However, I don't have shell access, so I putzed around (the above php didn't work) to get a working php solution.

However, that's on my laptop, and that's elsewhere in the house. Should this be of use to anyone, or should anyone find the need to import their Pms.. just ask .