Converting from drupal forums to phpbb3

Azaxiel · Post by **Azaxiel** » 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:

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;

D¡cky · Post by **D¡cky** » Sun Mar 28, 2010 3:29 am

If you are running that from the command line, you need to select a database.

Code: Select all

mysql USE database_name

Azaxiel · Post by **Azaxiel** » Sun Mar 28, 2010 4:43 pm

I'm using phpmyadmin to submit the query, but I added 'use bb;' above the section where it fails, now I'm getting a new error. Biggest problem is that I don't know the first thing about SQL, lol, so I'm shooting in the dark here. The new error I'm getting is:

Code: Select all

#1054 - Unknown column 'n.nid' in 'on clause'

do I need to have it use both databases? or just phpbb's?

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;

use bb;

#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;

Azaxiel · Post by **Azaxiel** » Sun Mar 28, 2010 9:12 pm

Ok, with the help of a good friend, I was able to make this work. It successfully converted all my users, forums, topics, and posts from drupal to phpbb2. Now, I just have to use the converter for phpbb2 to phpbb3. Here's the SQL code I used, it might have to be adapted to suit your needs, but worked fine for me.

Code: Select all

#Drupal to phpBB import script

#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;

use bb;

#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 n1.nid, f1.tid, n1.title, n1.uid, n1.created, max(c1.cid), min(c1.cid), count(c1.cid) from drupal.drupal_node n1, drupal.drupal_comments c1, drupal.drupal_forum f1 where f1.nid = n1.nid and n1.type='forum' and c1.nid = n1.nid group by n1.nid
union
select n2.nid, f2.tid, n2.title, n2.uid, n2.created, 0, 0, 0 from drupal.drupal_node n2, drupal.drupal_forum f2 where n2.type='forum' and n2.nid = f2.nid and n2.nid not in (select c2.nid from drupal.drupal_comments c2);

#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;

advertisements