Drupal-to-phpBB3 forum migration

Drupal's forums are just miserable.  Well, they might be nice if we were still in 1996, but we're not.  We're using a small forum for our FreeFarmGame.net co-op and a couple of months of Drupal's forums (even with Advanced Forum and OG) were enough to make me want to dump the whole thing. 

phpBB3 is GPL, so it's just the price I wanted.  It's also a full-featured forum, so it offers everything users may be adccustomed to seeing in a forum.  The problem, though, is that I didn't want to just start over and lose a few posts that we had accumulated.  I tried out the phpbbforum module, which ostensibly acts as a bridge between Drupal and phpBB.  Unfortunately, it failed.  It crashed when setting the forum location and killed my admin's session.  Not very useful, that.

Finally "gave up" and opted to attempt a data migration of my users and forum posts from Drupal to phpBB.  Found a good starter script here but it was for an older version of each.  Several trial-and-error attempts later and I ended up with the posts transferred, half of the users working correctly, and most of the posts in the new system not correctly attributed to the authors.  Too much time spent on it already, I just locked those posts and created a fresh clean forum for my users.  The broken accounts needed to re-create, too.  Far less ideal than I had hoped, but I was just wasting too much time on such a relatively unimportant project.

Here's the modifed set of queries if anyone happens to want to expand upon it for their own project.  This is a quick-and-dirty hack.  Don't just copy-and-paste to use this.  You will break something.  Make sure you view this along with the original.

#insert the users and create the default groups
insert into phpbb.phpbb_users(user_id, user_type, username, username_clean, user_password, user_regdate, group_id, user_posts, user_email, user_sig, user_allow_massemail, user_form_salt)
select (uid + 100), 0, name, name, pass, created, 2, 0, mail, signature, 0, encrypt(created) from noobfarm.users u where u.uid > 1;

select @lastGroupId:=max(group_id) from phpbb.phpbb_groups;
set @groupId=@lastGroupId;

insert into phpbb.phpbb_user_group(group_id, user_id, user_pending)
select @groupId:=@groupId+1, u.user_id, 0 from phpbb.phpbb_users u where u.user_id > 1;

insert into phpbb.phpbb_groups(group_id, group_type, group_name, group_desc)
select group_id, 1, '', 'Personal User' from phpbb.phpbb_user_group ug where ug.user_id > 100;

#insert the 1st level forum terms from drupal as phpBB 'forums'
#set @catOrder=0;
insert into phpbb.phpbb_forums (forum_id, forum_name /*, cat_order*/)
select td.tid, td.name /*, @catOrder:=@catOrder+1*/ from noobfarm.term_data td, noobfarm.term_hierarchy th, noobfarm.vocabulary_node_types vnt where td.tid = th.tid and th.parent = 0 and vnt.type= 'forum' and vnt.vid = td.vid;

#insert the topics- the first part of the union gets the nodes that have replies, so that the first, last post ids are set
#the second union are for topics with no replies
insert into phpbb.phpbb_topics(topic_id, forum_id, topic_title, topic_poster, topic_time, topic_first_post_id, topic_last_post_id, topic_replies)
select n.nid, f.tid, n.title, (n.uid)+100, n.created, max(c.cid), min(c.cid), count(c.cid) from noobfarm.node n, noobfarm.comments c, noobfarm.forum f where f.nid = n.nid and n.type='forum' and c.nid = n.nid group by n.nid
union
select n.nid, f.tid, n.title, (n.uid)+100, n.created, 0, 0, 0 from (noobfarm.node n, noobfarm.forum f) left join noobfarm.comments c on n.nid = c.nid where c.nid is null and n.type='forum' and n.nid = f.nid;

#insert the posts from the regular drupal comments section
#there s no pure sql way (that I know of) to transform a text IP address to a hex IP address- so we unfortuantely have to cheat.
insert into phpbb.phpbb_posts(post_id, topic_id, forum_id, poster_id, poster_ip, post_username, post_time, enable_bbcode, enable_smilies, enable_sig)
select c.cid, c.nid, t.forum_id, (c.uid)+100, '7f000001', '', c.timestamp, 1, 1, 1 from noobfarm.comments c, phpbb.phpbb_topics t where t.topic_id = c.nid;

#insert into phpbb.phpbb_posts_text(post_id, post_subject, post_text)
insert into phpbb.phpbb_posts(post_subject, post_text)
select subject, comment from noobfarm.comments c, phpbb.phpbb_posts p where c.cid = p.post_id;

#transform the first drupal node post into a phpBB comment- hopefully phpBB pays attention to the timestamp for ordering, not the primary key... it seems to do this correctly
#after testing the imported data in the phpBB UI
#find the last post ID and save it when we add the first node posts
select @lastPostId:=max(post_id) from phpbb.phpbb_posts;

set @postId=@lastPostId;
insert into phpbb.phpbb_posts(post_id,topic_id, forum_id, poster_id, poster_ip, post_username, post_time, enable_bbcode, enable_smilies,enable_sig)
select @postId:=@postId+1, nr.nid, t.forum_id, (nr.uid)+100, '7f000001', '', nr.timestamp, 1,1,1 from noobfarm.node_revisions nr, noobfarm.node n, phpbb.phpbb_topics t where n.type='forum' and nr.nid = n.nid and n.nid = t.topic_id;

set @postId=@lastPostId;
#insert into phpbb.phpbb_posts_text(post_id, post_subject, post_text)
insert into phpbb.phpbb_posts(post_id, post_subject, post_text)
select @postId:=@postId+1, nr.title, nr.body from noobfarm.node_revisions nr, phpbb.phpbb_topics t, noobfarm.node n where n.type='forum' and nr.nid = n.nid and n.nid = t.topic_id;