Drupal to phpBB2 converter:
Code: Select all
#Drupal to phpBB import script
#Drupal version 4.7.x
#phpBB version 2.0.22
#save this script as drupal-phpbb-import.sql
#execute by:
#mysql -u <user> -p<password> < drupal-phpbb-import.sql
#see important notes below before running this script!
#in this script:
#phpBB database is called bb
#drupal database is called drupal
#both databases are on the same instance of mysql
#written for 4.0.x mysql compatibility
#Only handles a simple drupal forum layout- on this conversion there was only one main drupal forum
#multiple forums were not tested with this script, but may convert OK
#!!!!SUPER IMPORTANT!!!!!!!!!!!!!
#Assumes a fresh phpBB database with the administrator UserID converted from ID 2 (apparently the default) to ID 1 to match
#the Drupal admin user. This ensures everything imports smoothly with userId's aligned correctly and without Id Collisions
#!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
#insert the users and create the default groups
insert into bb.bb_users(user_id, user_active, username, user_password, user_regdate, user_level, user_posts, user_email, user_sig)
select uid, 1, name, pass, created, 0, 0, mail, signature from drupal.drupal_users u where u.uid > 1;
select @lastGroupId:=max(group_id) from bb.bb_groups;
set @groupId=@lastGroupId;
insert into bb.bb_user_group(group_id, user_id, user_pending)
select @groupId:=@groupId+1, u.user_id, 0 from bb.bb_users u where u.user_id > 1;
insert into bb.bb_groups(group_id, group_type, group_name, group_description, group_moderator, group_single_user)
select group_id, 1, '', 'Personal User', 0, 1 from bb.bb_user_group ug where ug.user_id > 1;
#insert the 1st level forum terms from drupal as phpBB 'forums'
set @catOrder=0;
insert into bb.bb_categories (cat_id, cat_title, cat_order)
select td.tid, td.name, @catOrder:=@catOrder+1 from drupal.drupal_term_data td, drupal.drupal_term_hierarchy th, drupal.drupal_vocabulary_node_types vnt where td.tid = th.tid and th.parent = 0 and vnt.type= 'forum' and vnt.vid = td.vid;
#insert the 2nd level terms from drupal- these correspond to 'forums' in phpBB
set @forumOrder=10;
insert into bb.bb_forums(forum_id, cat_id, forum_name, forum_desc, forum_status, forum_order, auth_post, auth_reply, auth_edit, auth_delete, auth_sticky, auth_announce, auth_vote, auth_pollcreate, auth_attachments)
select td.tid, th.parent, td.name,'',0,@forumOrder:=@forumOrder+10, 1,1,1,1,3,3,1,1,0 from drupal.drupal_term_data td, drupal.drupal_term_hierarchy th, bb.bb_categories bbc where td.tid = th.tid and bbc.cat_id = th.parent;
#insert the topics- the first part of the union gets the nodes that have replies, so that the first, last post id's are set
#the second union are for topics with no replies
insert into bb_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, n.created, max(c.cid), min(c.cid), count(c.cid) from drupal.drupal_node n, drupal.drupal_comments c, drupal.drupal_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, n.created, 0, 0, 0 from drupal.drupal_node n, drupal.drupal_forum f left join drupal.drupal_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 bb_posts(post_id, topic_id, forum_id, poster_id, poster_ip, post_username, post_time, enable_bbcode, enable_html, enable_smilies, enable_sig)
select c.cid, c.nid, t.forum_id, c.uid, '7f000001', '', c.timestamp, 1, 0, 1, 1 from drupal.drupal_comments c, bb.bb_topics t where t.topic_id = c.nid;
insert into bb_posts_text(post_id, post_subject, post_text)
select cid, subject, comment from drupal.drupal_comments c, bb.bb_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 bb.bb_posts;
set @postId=@lastPostId;
insert into bb_posts(post_id,topic_id, forum_id, poster_id, poster_ip, post_username, post_time, enable_bbcode, enable_html, enable_smilies,enable_sig)
select @postId:=@postId+1, nr.nid, t.forum_id, nr.uid, '7f000001', '', nr.timestamp, 1,0,1,1 from drupal.drupal_node_revisions nr, drupal.drupal_node n, bb.bb_topics t where n.type='forum' and nr.nid = n.nid and n.nid = t.topic_id;
set @postId=@lastPostId;
insert into bb_posts_text(post_id, post_subject, post_text)
select @postId:=@postId+1, nr.title, nr.body from drupal.drupal_node_revisions nr, bb.bb_topics t, drupal.drupal_node n where n.type='forum' and nr.nid = n.nid and n.nid = t.topic_id;