Converting a decade-old Snitz forum to modern software

*sigh* Looks like I haven’t written anything for a long time. The cause of this may be the fact that I took ownership of a pretty cool website in the middle of December. Apparently their admin didn’t want to do the work of maintaining it anymore:

http://www.i-war2.com.

 

This thing was reasonably huge:

• hundreds of files (HTML, ZIP & more)
• various subpages that had been coded in ASP
• a forum with “Snitz” software, which hasn’t been maintained since 2009 (ASP, too)
• a forums database for Microsoft Access (.mdb)

How do you transfer such a sluggish, old beast to a Linux machine without major downtimes?

First of all, I wanted to get at least the static websites and all the files. The tool “WebHTTrack” was a very big help in this part. I was able to crawl the complete website and get all ZIP files, HTML pages and even some of the ASP sites, that were only used to deliver some static content.

The heart of this website is entirely different, though. It’s a forum with posts from which some were written over a decade ago. This one gave me a headache.

Step 1 – getting rid of Access

What was I supposed to do with an ugly .mdb file? That thing had to be gone and converted to MariaDB. I tried converting it with a linux tool, mdb-tools. This resulted in a segmentation fault. Great! Not much luck here, I guess.

My second approach was booting up a virtual XP machine and using Bullzip Access to SQL – that tool converted the database flawlessly, within mere seconds. I had a database which could be used as a starting point, now.

Step 2 – converting the database to phpBB3

I have a lot of experience with Joomla and one of it’s popular forum systems – Kunena (previously named Joomlaboard or Fireboard) – it was crystal-clear for me that this should be the target system for the new website.

Jumping directly from Snitz to Kunena was impossible, though. There wasn’t a tool for that purpose available and I didn’t have the time to fix one up just now.

So I had to do it the rough and hard way: Getting the database to work in phpBB2. I’ve found a converter for this purpose here. Surely a cool thing – but it needs phpBB 2.0.6. And that one runs only on PHP4. To be able to get that one, you need LAMPP 1.6.8a – and this has to be installed into /opt/lampp, otherwise it doesn’t run properly. (Additionally, all of the php.ini limits within LAMPP should be raised to the absolute maximum to get the conversion process started.)

After setting up and configuring that big bunch of software I had to remove all the sweat from my face. I really wanted to run the converter and be done with it. Of course – it broke down with an error message in forums.php. Apparently it couldn’t insert all columns into the new database, because there weren’t enough of them. After a lot of brainstorming, I commented out the lines 54-55 and it ran through. Great Scott!

Finally! I had a database file for phpBB2!

I continued with migrating from phpBB2 to phpBB3. Nothing extraordinary had to be done here. There is an official migration tool – I started it up, it did it’s job and I was done with it. Awesome!

Step 3 – there’s Kunena at the end of the tunnel

 

I was very happy and full of enthusiasm. I’ve managed to get around all of the obstacles and obtain three databases! Snitz SQL, phpBB2 and phpBB3 – they were all complete, too. A dream! 🙂

My happiness vanished very quickly, though, when I read that there wasn’t an official migration tool anymore and therefore no official way to migrate directly to Kunena. Back in the days when Fireboard was the hot sh*t such a script had been maintained. Nowadays, nobody really seems to care anymore.

I discovered an SQL-Skript on github that had been written for a very specific usecase. As I already suspected that thing didn’t work completely, because some columns in the phpBB3 database weren’t there, but they should’ve been. It looks like these columns don’t get created if a user migrates his forum instead of completely creating it from scratch.

However, these missing columns were indeed available in the phpBB2 database, so I could copy together those two databases with a wild mix of commands and the strange thing that came out at the end of it all was – my Kunena database. I finally had it after uncountable hours of brainstorming. It worked, too. I browsed through my new forums and felt relieved.

Step 4 – Let’s clean up!

A beginner would tell me now: Fine Ronny, you did well. Keep it as it is.

But I wasn’t quite done yet. 🙁 All the posts looked like shit. I had to continously improve the old data with these steps:

• 1. Convert HTML to bbCode

This task was relatively easy. A little SQL command like this did it:

UPDATE kunena_messages_text SET message = REPLACE(message, '

‘, ‘[center]’) WHERE `message` LIKE ‘%%’

• 2. Make garbled bbCodes pretty

Apparently some strange phpBB3 developers think that creating these bbCodes is awesome:

[img:3gso40ßf]

Of course, no other forum software is parsing that junk. MariaDB’s replace command isn’t able to deal with regular expressions, too. The only way for me to deal with this was exporting my SQL database and running sed over it to parse out that stuff:

sed -i 's/[img:[^]]{8}]/[img]/g' db.sql

• 3. Restore old URLs

The website is almost 15 years old. No wonder that in all of the time thousands of backlinks had been created by other users. And all of them should still work, because I want them to. Back in the days the URLs looked like this:

http://www.i-war2.com/forum/topic.asp?TOPIC_ID=666

If you click on this link you’ll notice that you get redirected to a prettier one:

http://www.i-war2.com/forum/general-gameplay/666-act-3-can-i-go-between-clusters

This possible because of some mod_rewrite rules in Apache2:

RewriteCond %{QUERY_STRING} ^TOPIC_ID=([^&]+) [NC]
RewriteRule ^forum/topic.asp$ /forum/missions/%1? [NC,L,R=302]
RewriteCond %{QUERY_STRING} ^TOPIC_ID=([^&]+) [NC]
RewriteRule ^forum/pop_printer_friendly.asp$ /forum/missions/%1? [NC,L,R=302]
RewriteCond %{QUERY_STRING} ^TOPIC_ID=([^&]+) [NC]
RewriteRule ^index.php$ /forum/missions/%1? [NC,L,R=302]
RewriteCond %{QUERY_STRING} ^FORUM_ID=([^&]+) [NC]
RewriteRule ^forum/forum.asp$ /forum/%1? [NC,L,R=302]
RewriteCond %{QUERY_STRING} ^CAT_ID=([^&]+) [NC]
RewriteRule ^forum/default.asp$ /forum/%1? [NC,L,R=302]
RedirectMatch 301 /forum/(.*).asp /forum

A visitor will be redirected to the subforum “missions” with the post id attached to the end of it. Kunena is changing that URL dynamically and making it pretty. It’s home time!

And the moral of this story: Don’t let your software rot, people, please! 😉