Drupal -> phpbb conversion?

This is an archive of the phpBB 2.0.x convertors forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
Locked
nullbert
Registered User
Posts: 12
Joined: Sat Dec 23, 2006 6:25 am

Drupal -> phpbb conversion?

Post by nullbert »

I'm hosting a Drupal based site and would like to convert the users and forums to PHPBB.

Does such a converter exists? What's my best route for accomplishing this?

thank you!
nullbert
Registered User
Posts: 12
Joined: Sat Dec 23, 2006 6:25 am

Post by nullbert »

bump - so nobody has gone this route? Seems that I'll be using the universal converter to do it manually.
nullbert
Registered User
Posts: 12
Joined: Sat Dec 23, 2006 6:25 am

Post by nullbert »

Still hoping someone will magically have a drupal->phpBB migration script. :)

In the meantime: I have (so far) one burning question: Regarding converting users from drupal->phpBB- does anyone know if the the two systems use the same password encryption scheme- ie. would I be able to drop the password values right into the phpBB table?
User avatar
D¡cky
Former Team Member
Posts: 11812
Joined: Tue Jan 25, 2005 8:38 pm
Location: New Hampshire, USA
Name: Richard Foote
Contact:

Post by D¡cky »

I don't know what encryption Drupal uses. phpBB uses a md5 encryption. You could md5 encrypt your password and see if it matches what is in the Drupal users table. You are welcome to use this MD5 Encryption Tool.
Have you hugged someone today?
nullbert
Registered User
Posts: 12
Joined: Sat Dec 23, 2006 6:25 am

Post by nullbert »

w00t! That's it- the passwords are encrypted the same.

Drupal.org has a phpBB->Drupal migration guide- the best I can do right now is reverse-engineer that guide to get the forum posts into phpBB.

Luckily I only need to convert the users, forum structure and posts. All of the other CMS content will be uploaded to a wiki.

I'll likely make the conversion as a set of SQL scripts- however, all of the 'links' inside of the posted messages might need some sort of post-conversion process to match the URL pattern of phpBB.

.. or perhaps use ModRewrite? Haven't used that yet, need to research it some more.

thanks so far!

Brent
nullbert
Registered User
Posts: 12
Joined: Sat Dec 23, 2006 6:25 am

Post by nullbert »

Here's my script so far. Can someone with more experience eyeball it for me?

After executing the script I don't see any posts in the forums I've created! I did perform the resync on all of the forums in administration. I believe I have all of the keys matching with the categories/forums/posts.. Any ideas?

Do I need to refresh some cached values?

Thanks,
brent

use bb;
#bb = phpbb database
#drupal = drupal database


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

select @forumVID:=vid from drupal.vocabulary v where v.module = 'forum';

set @catOrder=0;
insert into bb.bb_categories (cat_id, cat_title, cat_order)
select td.tid, td.name, @catOrder:=@catOrder+1 from drupal.term_data td, drupal.term_hierarchy th, drupal.vocabulary_node_types vnt where td.tid = th.tid and th.parent = 0 and vnt.type= 'forum' and vnt.vid = td.vid;

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.term_data td, drupal.term_hierarchy th, bb.bb_categories bbc where td.tid = th.tid and bbc.cat_id = th.parent;

insert into bb_topics(topic_id, forum_id, topic_title, topic_poster, topic_time)
select n.nid, df.tid, n.title, n.uid, n.created from drupal.node n, drupal.forum df where n.type='forum' and df.nid = n.nid;

insert into bb_posts(post_id, topic_id, forum_id, poster_id, post_time, enable_bbcode, enable_html, enable_smilies, enable_sig)
select c.cid, c.nid, t.forum_id, c.uid, c.timestamp, 1, 0, 1, 1 from 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.comments c, bb.bb_posts p where c.cid = p.post_id;
User avatar
D¡cky
Former Team Member
Posts: 11812
Joined: Tue Jan 25, 2005 8:38 pm
Location: New Hampshire, USA
Name: Richard Foote
Contact:

Post by D¡cky »

Check the bb_topics table for first_post_id and last_post_id. I believe that a resync will not fix those.
Have you hugged someone today?
nullbert
Registered User
Posts: 12
Joined: Sat Dec 23, 2006 6:25 am

Updated conversion script

Post by nullbert »

following this post is the updated converstion script. It seems to work correctly for my installation. If there are any obvious mistakes, please advise!

I'm so happy I bit the bullet and did this conversion; the collaborative / community experience for my users will be so much better. Hopefully this script will help others who want to make this switch as well!

Thanks all for your help,
Brent
nullbert
Registered User
Posts: 12
Joined: Sat Dec 23, 2006 6:25 am

Post by nullbert »

#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.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.term_data td, drupal.term_hierarchy th, 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.term_data td, 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.node n, drupal.comments c, 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.node n, drupal.forum f left join 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.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.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.node_revisions nr, 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.node_revisions nr, bb.bb_topics t, drupal.node n where n.type='forum' and nr.nid = n.nid and n.nid = t.topic_id;
nullbert
Registered User
Posts: 12
Joined: Sat Dec 23, 2006 6:25 am

Post by nullbert »

I've noticed search isn't working. I couldn't find a function in the control panel to re-seed the search keywords table. how can I do this?

Brent
User avatar
D¡cky
Former Team Member
Posts: 11812
Joined: Tue Jan 25, 2005 8:38 pm
Location: New Hampshire, USA
Name: Richard Foote
Contact:

Post by D¡cky »

Have you hugged someone today?
nullbert
Registered User
Posts: 12
Joined: Sat Dec 23, 2006 6:25 am

Post by nullbert »

Right- thanks! I found this as well:
http://www.phpbb.com/phpBB/viewtopic.php?t=329629
robataka
Registered User
Posts: 1
Joined: Mon Jun 18, 2007 9:34 pm

Re:

Post by robataka »

nullbert wrote:
#!!!!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
#!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I don't understand this. Well, I understand the english.....

I am not sure how to convernt the administrator UserID from UID2 to UID1......
rockymaxsource
Registered User
Posts: 1
Joined: Tue Oct 02, 2007 8:51 am

Re: Drupal -> phpbb conversion?

Post by rockymaxsource »

Hey nullbert,

Can you post website's URL where the Forum you converted is installed and in use at this thread please?

Blessings,
Rocky
Locked

Return to “[2.0.x] Convertors”