I decided after an interesting chat with Loren Feldman from 1938 Media earlier this year that this site should move from a blog to a forum. He had recently swapped from WordPress to vBulletin.
I’m a data hoarder, so I had to figure out how to migrate the data. It’s not simple, because although vBulletin has a WordPress importer, it’s for an old version of WordPress and it’s designed to work with the vBulletin Blog system (not for importing into a forum).
So, here are the steps I went through to get it to work.
Install Drupal 5.
Download and install WordPress Import and Trackback modules for Drupal 5. Enable these, along with the Blog and Forum modules.
Import the WXR file from your WordPress install.
Update all imported story or blog types to forum types by running:
update node set type="forum";
Create a container using the Forums administration panel. Create a forum within that container. Note the forum ID.
Run MySQL queries FOR EVERY NODE to insert term to node and forum to node links. You can use Excel to make a column with IDs and then write a simple formula to create the SQL queries below. Replace 78 with the forum ID created above and have a formula in Excel to replace 60 with the node ID. For example, =”insert into term_node (nid, tid) values (“&A1&”, 78);” and =”insert into forum (nid, vid, tid) values (“&A1&”, “&A1&”, 78);”
insert into term_node (nid, tid) values (60, 78);
insert into forum (nid, vid, tid) values (60, 60, 78);
(If you want to import your tags / categories into vBulletin tags, you may want to dump the term_data and term_node tables from Drupal and somehow import them into tag and tagthread respectively in vBulletin. I’ll give some tips later.)
Delete the imported WordPress category and tag vocabularies.
Install ImpEx on vBulletin and use the Drupal importer. It should “just work”.
If you want to maintain some commenter name on comments, you will also need to mysqldump the comments table from your Drupal database, and import it into your vBulletin database. (I had to do this as my databases were on different servers.)
mysqldump -u drupaluser -p drupaldb comments > comments.sql
mysql -u vbuser -p vbdb < comments.sql
update post, comments set post.username=comments.name where post.importpostid=comments.cid;
update post, comments set post.ipaddress=comments.hostname where post.importpostid=comments.cid;
to set the commenter name to a name instead of “Guest” (and optionally to preserve the IP address history).
If you wanted to import tags / categories, here are the queries once you’ve imported the term_data and term_node tables.
INSERT INTO tag ( tagid, tagtext, dateline ) SELECT tid, name, UNIX_TIMESTAMP( ) FROM term_data;
INSERT INTO tagthread ( tagid, threadid, userid, dateline ) SELECT term_node.tid, thread.threadid, thread.postuserid, UNIX_TIMESTAMP( ) FROM term_node, thread WHERE thread.importthreadid = term_node.nid;
You may want to write some query to update the thread table with the taglist, using the following query as inspiration:
SELECT threadid, GROUP_CONCAT(tagtext SEPARATOR ', ') FROM tag, tagthread WHERE tag.tagid = tagthread.tagid GROUP BY tagthread.threadid;
I just used an INSERT statement based on the above SELECT to populate a new table of IDs and text strings, and then used that to update the taglist column in the thread table.