Converting from drupal forums to phpbb3
Posted: Sun Mar 28, 2010 1:24 am
A while back I started a thread here to convert my drupal forums to phpbb3. I was able to convert my users, but could never find a way to convert the topics/posts. Recently, I found a converter to convert drupals forums to phpbb2, and figured I could use that then convert phpbb2 to phpbb3 rather easily. However, this other converter is written in SQL, and I'm at a loss for whats going wrong...it converts the users, and the topics successfully, but then fails during the posts conversion with the SQL error 'No database selected'. I'm hoping someone here might be able to point me in the right direction with this. Thanks in advance.
Drupal to phpBB2 converter:
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;