I try to post here the steps I did to import from PHPBB to BuddyPress v2.6 alpha.
The concept is the same of what you’ve already read in my previous post but I think the SQL scripts are different.
So first of all I’ve created manually all the missing columns in my PHPBB tables (I was converting from an old version of PHPBB and the converter was expecting some columns that didn’t exist – this could not be your case):
ALTER TABLE forum_forums
ADD forum_posts_approved numeric;
ALTER TABLE forum_forums
ADD forum_topics_approved numeric;
ALTER TABLE forum_topics
ADD topic_posts_approved numeric;
ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_facebook varchar(255);
ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_twitter varchar(255);
ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_occupation varchar(255);
ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_interests varchar(255);
ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_location varchar(255);
ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_youtube varchar(255);
ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_skype varchar(255);
ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_googleplus varchar(255);
ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_website varchar(255);
ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_aol varchar(255);
ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_yahoo varchar(255);
ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_icq varchar(255);
ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_wlm varchar(255);
Then I’ve performed this KEY STEP: insert a dummy row in the WP_USERS table with a very high userid (e.g.: 99999). This should be higher than the highest userid you have in your phpbb installation.
In this way, when the users get converted, the system will assigned IDs starting from 100000 (again, if you have more than 100K users, then you need a higher value).
This step is necessary, so you will always be able to distinguish the old id (PHPBB, < 100000) from the new one assigned after conversion (>= 100000).
After that I’ve started the conversion (it took long).
After converting, the authors of certain posts were wrong.
There is a table that is generated after the conversion that maps the old IDs to the new ones.
So I’ve execute the following scripts to readjust (cannot guarantee this would fix entirely the problem):
/* Get all posts with wrong author and correspondent right author from translator table */
create table TMP_ORPHANS as
select
<YOUR_SCHEMA>.wp_posts.ID,
<YOUR_SCHEMA>.wp_posts.post_author,
<YOUR_SCHEMA>.wp_bbp_converter_translator.value_id real_author,
IFNULL(<YOUR_SCHEMA>.wp_posts.post_date, NULL) post_date,
IFNULL(<YOUR_SCHEMA>.wp_posts.post_date_gmt, NULL) post_date_gmt,
<YOUR_SCHEMA>.wp_posts.post_content,
CASE
WHEN <YOUR_SCHEMA>.wp_posts.post_type = ‘reply’
THEN (SELECT tmposts.post_title from <YOUR_SCHEMA>.wp_posts as tmposts where tmposts.id = <YOUR_SCHEMA>.wp_posts.post_parent)
ELSE <YOUR_SCHEMA>.wp_posts.post_title
END post_title,
<YOUR_SCHEMA>.wp_posts.post_excerpt,
<YOUR_SCHEMA>.wp_posts.post_status,
<YOUR_SCHEMA>.wp_posts.comment_status,
<YOUR_SCHEMA>.wp_posts.ping_status,
<YOUR_SCHEMA>.wp_posts.post_password,
<YOUR_SCHEMA>.wp_posts.post_name,
<YOUR_SCHEMA>.wp_posts.to_ping,
<YOUR_SCHEMA>.wp_posts.pinged,
IFNULL(<YOUR_SCHEMA>.wp_posts.post_modified, NULL) post_modified,
IFNULL(<YOUR_SCHEMA>.wp_posts.post_modified_gmt, NULL) post_modified_gmt,
<YOUR_SCHEMA>.wp_posts.post_content_filtered,
<YOUR_SCHEMA>.wp_posts.post_parent,
<YOUR_SCHEMA>.wp_posts.guid,
<YOUR_SCHEMA>.wp_posts.menu_order,
<YOUR_SCHEMA>.wp_posts.post_type,
<YOUR_SCHEMA>.wp_posts.post_mime_type,
<YOUR_SCHEMA>.wp_posts.comment_count,
( select <YOUR_SCHEMA>.wp_postmeta.meta_value
from <YOUR_SCHEMA>.wp_postmeta
where
<YOUR_SCHEMA>.wp_postmeta.meta_key = ‘_bbp_author_ip’ and
<YOUR_SCHEMA>.wp_postmeta.post_id = <YOUR_SCHEMA>.wp_posts.id
) user_ip
from <YOUR_SCHEMA>.wp_posts
left join <YOUR_SCHEMA>.wp_users on <YOUR_SCHEMA>.wp_posts.post_author = <YOUR_SCHEMA>.wp_users.id
left join <YOUR_SCHEMA>.wp_bbp_converter_translator on <YOUR_SCHEMA>.wp_posts.post_author = <YOUR_SCHEMA>.wp_bbp_converter_translator.meta_value
where
<YOUR_SCHEMA>.wp_posts.post_type in (‘forum’, ‘reply’, ‘topic’) and
(<YOUR_SCHEMA>.wp_users.id is null or
<YOUR_SCHEMA>.wp_users.id = 1) and
<YOUR_SCHEMA>.wp_bbp_converter_translator.value_type = ‘user’ and
<YOUR_SCHEMA>.wp_bbp_converter_translator.meta_key = ‘_bbp_old_user_id’;
/* Do this to be able to create indexes later – some may not work */
ALTER TABLE TMP_ORPHANS modify column post_date datetime default NULL;
ALTER TABLE TMP_ORPHANS modify column post_date_gmt datetime default NULL;
ALTER TABLE TMP_ORPHANS modify column post_modified datetime default NULL;
ALTER TABLE TMP_ORPHANS modify column post_modified_gmt datetime default NULL;
ALTER TABLE TMP_ORPHANS modify column user_ip varchar(40);
ALTER TABLE TMP_ORPHANS modify column post_title varchar(255);
/* we create indexes to speed up the update process */
ALTER TABLE TMP_ORPHANS ADD INDEX(post_date);
ALTER TABLE TMP_ORPHANS ADD INDEX(post_author);
ALTER TABLE TMP_ORPHANS ADD INDEX(real_author);
ALTER TABLE TMP_ORPHANS ADD INDEX(user_ip);
ALTER TABLE TMP_ORPHANS ADD INDEX(post_title);
/* Revert bad authors to good authors */
UPDATE
<YOUR_SCHEMA>.wp_postsINNER JOIN <YOUR_SCHEMA>.TMP_ORPHANS
ON <YOUR_SCHEMA>.wp_posts.id = <YOUR_SCHEMA>.TMP_ORPHANS.id
set <YOUR_SCHEMA>.wp_posts.post_author = <YOUR_SCHEMA>.TMP_ORPHANS.real_author
where <YOUR_SCHEMA>.wp_posts.post_type in (‘reply’, ‘topic’) ;
DROP TABLE <YOUR_SCHEMA>.TMP_ORPHANS;
I hope this helps.
Gio
