bbPress to phpBB3 - SQL converter

Converting from other board software? Good decision! Need help? Have a question about a convertor? Wish to offer a convertor package? Post here.
Scam Warning
_aleck
Registered User
Posts: 6
Joined: Tue Jan 01, 2008 10:59 pm
Location: Belgrade, Serbia
Contact:

bbPress to phpBB3 - SQL converter

Post by _aleck » Sat Jan 05, 2008 2:48 am

I have a small forum running on bbPress 0.8.1 and just finished first successful run of the SQL converter script.

I tried to write a proper converter, but lost more time trying to figure out how phpBB3 converter framework works than actually building this set of SQL queries.

Script (as written) requires that bbPress and phpBB3 tables exists in the same database. It's far from perfect and does not transfer every bit on information - I just worked until my forum was transfered ok.

Script assumes that you are working with fresh phpBB3 install. Any data (forum / topic / user) that may have been added after that will be lost in the process. Always backup.

Update: I missed the datetime-to-timestamp conversion. Fixed now.

Code: Select all

SET NAMES utf8;
SET SQL_MODE='';
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';



-- 0. BACKUP!
-- really, *backup* your default instalation of phpBB3
-- if something goes wrong, you can get it back to what it was and start over


-- 1. clear tables
DELETE FROM phpbb_forums;
DELETE FROM phpbb_topics;
DELETE FROM phpbb_topics_posted;
DELETE FROM phpbb_topics_track;
DELETE FROM phpbb_posts;
DELETE FROM phpbb_acl_groups WHERE forum_id > 0;
-- there is a difference in user_id.
-- in bbPress, your admin was 1, in phpBB3 is 2 (Anonymous is 1).
-- phpBB3 also has 50 bots preinstalled, thus member user_ids from bbPress must be pushed up:
-- 	2 becomes 53, 3 -> 54 etc.
DECLARE @user_id_jump int;
SET @user_id_jump = 51;
-- now clear all users except Anon, Admin and Bots
DELETE FROM phpbb_user_group WHERE user_id > (@user_id_jump+1);
DELETE FROM phpbb_users WHERE user_id > (@user_id_jump+1);
DELETE FROM phpbb_acl_users WHERE user_id > (@user_id_jump+1);



-- 2. initial users transfer - assumes all users except 1st (which is admin) are normal users. admin is not transfered.
-- group_id: 2 == REGISTERED
-- user_type: 0 == USER_NORMAL
-- user_style: 2 == my newly installed style. 1 = prosilver, 2 = my style
-- user_permissions: ?! == now is this calculated?
-- user_email_hash: ?! == now is this calculated?
INSERT INTO phpbb_users (
	user_id, user_type, group_id,
	username, username_clean, user_password, user_pass_convert, user_email, user_website, 
	user_lastvisit, user_regdate, 
	user_timezone, user_dst, user_lang, user_dateformat, user_style,
	user_options
)
SELECT ID+@user_id_jump as user_id, 0, 2,
	user_login, lower(replace(user_login, '.', '')), user_pass, 1, user_email, user_url,
	unix_timestamp(user_registered), unix_timestamp(user_registered),
	1, 1, 'sr', 'D, d M Y, G:i', 2,
	895
FROM bb_users
WHERE ID > 1;

-- 2a. update user_group table
INSERT INTO phpbb_user_group (
	group_id, user_id, group_leader, user_pending
)
SELECT DISTINCT group_id, user_id, 0, 0
FROM phpbb_users
WHERE user_id > (@user_id_jump+1);



-- 3. transfer forums
-- assumes that all forums are equal, no parents, no sub-forums
-- forum_type: 1 == POST-based forum
-- forum_parents: ?! == how it is calculated?
INSERT INTO phpbb_forums (
	forum_id, parent_id, left_id, right_id,
	forum_name, forum_desc, forum_type,
	forum_posts, forum_topics, forum_topics_real,
	forum_last_post_id, forum_last_poster_id, forum_last_post_subject, forum_last_post_time, forum_last_poster_name,
	forum_flags
)
SELECT f.forum_id, 0, 0, 0,
	f.forum_name, f.forum_desc, 1,
	f.posts, f.topics, f.topics,
	(SELECT MAX(post_id) FROM bb_posts WHERE forum_id = f.forum_id) as last_post_id, 0, '', 0, '',
	32
FROM bb_forums f;

-- 3a. update last post* data
UPDATE phpbb_forums f
SET forum_last_poster_id = (SELECT CASE poster_id WHEN 1 THEN poster_id+1 ELSE poster_id+@user_id_jump END as poster_id FROM bb_posts WHERE forum_id = f.forum_id AND post_id = f.forum_last_post_id),
    forum_last_poster_name = (SELECT u.user_login FROM bb_users u, bb_posts p WHERE p.forum_id = f.forum_id AND p.post_id = f.forum_last_post_id AND u.ID = p.poster_id),
    forum_last_post_time = (SELECT unix_timestamp(post_time) FROM bb_posts WHERE forum_id = f.forum_id AND post_id = f.forum_last_post_id);

-- 3b. update left_id and right_id
-- see: http://www.sitepoint.com/article/hierarchical-data-database/2
-- this continues the assumption that all forums are equal, with no parents and no sub-forums
UPDATE phpbb_forums f
SET left_id = (forum_id-1)*2 + 1,
    right_id = (forum_id-1)*2 + 2;

-- 3c. set forum ACL -- this sets required permissions for the forums, based on groups
-- 	set READONLY for GUESTS group
INSERT INTO phpbb_acl_groups (
	group_id, forum_id, auth_role_id
) SELECT 1, forum_id, 17
FROM phpbb_forums;
-- 	set FORUMSTANDARD for REGISTERED group
INSERT INTO phpbb_acl_groups (
	group_id, forum_id, auth_role_id
) SELECT 2, forum_id, 15
FROM phpbb_forums;
-- 	set FORUMSTANDARD for REGISTERED COPPA group
INSERT INTO phpbb_acl_groups (
	group_id, forum_id, auth_role_id
) SELECT 3, forum_id, 15
FROM phpbb_forums;
-- 	set FORUMPOLLS for GLOBAL_MODS group
INSERT INTO phpbb_acl_groups (
	group_id, forum_id, auth_role_id
) SELECT 4, forum_id, 21
FROM phpbb_forums;
-- 	set FORUMFULL for ADMINS group
INSERT INTO phpbb_acl_groups (
	group_id, forum_id, auth_role_id
) SELECT 5, forum_id, 14
FROM phpbb_forums;
-- 	set BOTS for BOTS group
INSERT INTO phpbb_acl_groups (
	group_id, forum_id, auth_role_id
) SELECT 6, forum_id, 19
FROM phpbb_forums;
-- if I had categories, I would set role 17 for groups 1,2,3,6



-- 4. transfer topics
INSERT INTO phpbb_topics (
	topic_id, topic_title, topic_time, topic_last_post_time,
	topic_poster, topic_first_poster_name, topic_last_poster_id, topic_last_poster_name,
	forum_id, topic_replies,
	topic_first_post_id, topic_last_post_id
)
SELECT t.topic_id, t.topic_title, unix_timestamp(t.topic_start_time), unix_timestamp(t.topic_time),
	CASE t.topic_poster WHEN 1 THEN t.topic_poster+1 ELSE t.topic_poster+@user_id_jump END as topic_poster_id, t.topic_poster_name, 
	CASE t.topic_last_poster WHEN 1 THEN t.topic_last_poster+1 ELSE t.topic_last_poster+@user_id_jump END, t.topic_last_poster_name,
	t.forum_id,
	(SELECT count(*) FROM bb_posts WHERE topic_id = t.topic_id)-1 as topic_replies,
	(SELECT min(post_id) FROM bb_posts WHERE topic_id = t.topic_id) as topic_first_post_id,
	(SELECT max(post_id) FROM bb_posts WHERE topic_id = t.topic_id) as topic_last_post_id
FROM bb_topics t;

-- 4a. update poster-related data
-- by members
UPDATE phpbb_topics t
SET topic_first_poster_name = (SELECT u.user_login FROM bb_users u WHERE u.ID = t.topic_poster-@user_id_jump),
    topic_last_poster_name = (SELECT u.user_login FROM bb_users u WHERE u.ID = t.topic_last_poster_id-@user_id_jump),
    topic_replies_real = topic_replies
WHERE t.topic_poster > (@user_id_jump+1);
-- and admin
UPDATE phpbb_topics t
SET topic_first_poster_name = (SELECT u.user_login FROM bb_users u WHERE u.ID = t.topic_poster-1),
    topic_last_poster_name = (SELECT u.user_login FROM bb_users u WHERE u.ID = t.topic_last_poster_id-1),
    topic_replies_real = topic_replies
WHERE t.topic_poster = 2;




-- 5. transfer posts
INSERT INTO phpbb_posts (
	post_id, topic_id, forum_id, poster_id, poster_ip, post_time,
	post_username,
	post_subject, post_text
)
SELECT p.post_id, p.topic_id, p.forum_id, CASE p.poster_id WHEN 1 THEN p.poster_id+1 ELSE p.poster_id+@user_id_jump END as poster_id, 
	p.poster_ip, unix_timestamp(p.post_time),
	(SELECT user_login FROM bb_users WHERE ID = p.poster_id) as username,
	'', p.post_text
FROM bb_posts p;




-- 6. update connection between users and topics
INSERT INTO phpbb_topics_posted (
	user_id, topic_id, topic_posted
)
SELECT DISTINCT user_id, topic_id, 1
FROM phpbb_topics;



-- 7. re-sync and re-count forums, topics and posts in phpBB3 admin, on General page



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

Greenleaves
Registered User
Posts: 6
Joined: Sat Jul 19, 2008 2:04 pm

Re: bbPress to phpBB3 - SQL converter

Post by Greenleaves » Tue Jul 22, 2008 2:26 pm

Would it also be possible to export bbpress to xml and then import the xml into phpbb?

Greenleaves
Registered User
Posts: 6
Joined: Sat Jul 19, 2008 2:04 pm

Re: bbPress to phpBB3 - SQL converter

Post by Greenleaves » Wed Jul 23, 2008 8:02 am

I really do need a bbpress to phpBB3 converter. I got through a couple of months of active posting before deciding to make the switch to phpBB because it will allow me multiple languages.

I don't think I am qualified to create my own converter, and the author of this SQL query scriptset seems to be saying that things might not work well. Anyone who can help?

PrevUK
Registered User
Posts: 1
Joined: Thu Jul 24, 2008 11:49 pm

Re: bbPress to phpBB3 - SQL converter

Post by PrevUK » Thu Jul 24, 2008 11:51 pm

I agree that a bbpress convertor would be very useful indeed.

I made a backup of my bbpress db and tried the script above, but it fell over every time on the DECLARE statement.

I am happy to tinker, so if a working version of the code above could be provided (pretty please) I am happy to be a guinea pig ;)

Many thanks :)

Chilifrei64
Registered User
Posts: 7
Joined: Thu Jul 03, 2003 2:42 pm
Contact:

Re: bbPress to phpBB3 - SQL converter

Post by Chilifrei64 » Sat Nov 15, 2008 3:53 am

just wondering if anybody is yet to make any headway on this. I have been fartin around with this for a few days but with no luck.. anybody make a converter(or a sql script that works) for this yet?
-Daniel Frei

Chilifrei64
Registered User
Posts: 7
Joined: Thu Jul 03, 2003 2:42 pm
Contact:

Re: bbPress to phpBB3 - SQL converter

Post by Chilifrei64 » Sat Nov 22, 2008 4:56 am

for those of you wondering... I moved this over and it all went really well.. I went and installed SMF forum 1.1.7 and used their BBpress to SMF converter from their forums. Verified everything went well... then used the SMF to phpbb converter from here.. and viola.. bbpress converted.. So for those of you hoping for something.. there is a way.. just gotta play a lil bit.
-Daniel Frei

Epistates
Registered User
Posts: 1
Joined: Sun Dec 06, 2009 4:00 pm

Re: bbPress to phpBB3 - SQL converter

Post by Epistates » Sun Dec 06, 2009 4:11 pm

I also want to migrate my bbpress to phpbb3. So I used this converter. But MySQL errors:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @user_id_jump INT' at line 1
Anyone familiar with this stuff and knows what to do to get this script working?

User avatar
captainjack
Registered User
Posts: 1
Joined: Mon Dec 14, 2009 12:26 pm
Location: Seattle, WA
Contact:

Re: bbPress to phpBB3 - SQL converter

Post by captainjack » Mon Dec 14, 2009 12:38 pm

I'm handling a very large website and I don't really have the time to tinker with migrating bbpress to phpbb3. Will there ever be a converter for this migration? Maybe a fool proof set of instructions? I can't afford any downtime, for this site is very popular. http://hotforwords.com

Captain Jack
LTA for http://Hotforwords.com/category/ta

nivosh
Registered User
Posts: 15
Joined: Tue Feb 23, 2010 3:15 pm

Re: bbPress to phpBB3 - SQL converter

Post by nivosh » Thu Feb 25, 2010 1:48 pm

I also got the SQL error:

Code: Select all

SQL query:

-- there is a difference in user_id.
-- in bbPress, your admin was 1, in phpBB3 is 2 (Anonymous is 1).
-- phpBB3 also has 50 bots preinstalled, thus member user_ids from bbPress must be pushed up:
-- 2 becomes 53, 3 -> 54 etc.
DECLARE@user_id_jump int;

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @user_id_jump int' at line 1 
it would be great if someone could look it up

illustrationism
Registered User
Posts: 2
Joined: Thu Apr 15, 2010 1:34 pm

Re: bbPress to phpBB3 - SQL converter

Post by illustrationism » Fri Apr 16, 2010 6:29 pm

Here's what worked for me. Though you are definitely going to have to have users reset their passwords...

Code: Select all

-- SET NAMES utf8;
-- SET SQL_MODE='';
-- SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
-- SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';



-- 0. BACKUP!
-- really, *backup* your default instalation of phpBB3
-- if something goes wrong, you can get it back to what it was and start over


-- 1. clear tables
DELETE FROM phpbb_forums;
DELETE FROM phpbb_topics;
DELETE FROM phpbb_topics_posted;
DELETE FROM phpbb_topics_track;
DELETE FROM phpbb_posts;
DELETE FROM phpbb_acl_groups WHERE forum_id > 0;
-- there is a difference in user_id.
-- in bbPress, your admin was 1, in phpBB3 is 2 (Anonymous is 1).
-- phpBB3 also has 50 bots preinstalled, thus member user_ids from bbPress must be pushed up:
--  2 becomes 53, 3 -> 54 etc.

SET @user_id_jump = 51;

-- now clear all users except Anon, Admin and Bots
DELETE FROM phpbb_user_group WHERE user_id > (@user_id_jump+1);
DELETE FROM phpbb_users WHERE user_id > (@user_id_jump+1);
DELETE FROM phpbb_acl_users WHERE user_id > (@user_id_jump+1);



-- 2. initial users transfer - assumes all users except 1st (which is admin) are normal users. admin is not transfered.
-- group_id: 2 == REGISTERED
-- user_type: 0 == USER_NORMAL
-- user_style: 2 == my newly installed style. 1 = prosilver, 2 = my style
-- user_permissions: ?! == now is this calculated?
-- user_email_hash: ?! == now is this calculated?
INSERT INTO phpbb_users 
(user_id, user_type, group_id, username, username_clean, user_password, user_pass_convert, user_email, user_website, user_lastvisit, user_regdate, user_timezone, user_dst, user_lang, user_dateformat, user_style, user_options)
SELECT ID + @user_id_jump AS user_id, 0, 2, user_login, lower(REPLACE(user_login, '.', '_')), user_pass, 1, user_email, user_url, unix_timestamp(user_registered), unix_timestamp(user_registered), 1, 1, 'sr', 'D, d M Y, G:i', 2, 895
FROM wp_users
WHERE ID > 1;


-- 2a. update user_group table
INSERT INTO phpbb_user_group (
 group_id, user_id, group_leader, user_pending
)
SELECT DISTINCT group_id, user_id, 0, 0
FROM phpbb_users
WHERE user_id > (@user_id_jump+1);



-- 3. transfer forums
-- assumes that all forums are equal, no parents, no sub-forums
-- forum_type: 1 == POST-based forum
-- forum_parents: ?! == how it is calculated?
INSERT INTO phpbb_forums (
 forum_id, parent_id, left_id, right_id,
 forum_name, forum_desc, forum_type,
 forum_posts, forum_topics, forum_topics_real,
 forum_last_post_id, forum_last_poster_id, forum_last_post_subject, forum_last_post_time, forum_last_poster_name,
 forum_flags
)
SELECT f.forum_id, 0, 0, 0,
 f.forum_name, f.forum_desc, 1,
 f.posts, f.topics, f.topics,
 (SELECT MAX(post_id) FROM bb_posts WHERE forum_id = f.forum_id) AS last_post_id, 0, '', 0, '',
 32
FROM bb_forums f;

-- 3a. update last post* data
UPDATE phpbb_forums f
SET forum_last_poster_id = (SELECT CASE poster_id WHEN 1 THEN poster_id+1 ELSE poster_id+@user_id_jump END AS poster_id FROM bb_posts WHERE forum_id = f.forum_id AND post_id = f.forum_last_post_id),
  forum_last_poster_name = (SELECT u.user_login FROM wp_users u, bb_posts p WHERE p.forum_id = f.forum_id AND p.post_id = f.forum_last_post_id AND u.ID = p.poster_id),
  forum_last_post_time = (SELECT unix_timestamp(post_time) FROM bb_posts WHERE forum_id = f.forum_id AND post_id = f.forum_last_post_id);

-- 3b. update left_id and right_id
-- see: http://www.sitepoint.com/article/hierarchical-data-database/2
-- this continues the assumption that all forums are equal, with no parents and no sub-forums
UPDATE phpbb_forums f
SET left_id = (forum_id-1)*2 + 1,
  right_id = (forum_id-1)*2 + 2;

-- 3c. set forum ACL -- this sets required permissions for the forums, based on groups
--  set READONLY for GUESTS group
INSERT INTO phpbb_acl_groups (
 group_id, forum_id, auth_role_id
) SELECT 1, forum_id, 17
FROM phpbb_forums;
--  set FORUMSTANDARD for REGISTERED group
INSERT INTO phpbb_acl_groups (
 group_id, forum_id, auth_role_id
) SELECT 2, forum_id, 15
FROM phpbb_forums;
--  set FORUMSTANDARD for REGISTERED COPPA group
INSERT INTO phpbb_acl_groups (
 group_id, forum_id, auth_role_id
) SELECT 3, forum_id, 15
FROM phpbb_forums;
--  set FORUMPOLLS for GLOBAL_MODS group
INSERT INTO phpbb_acl_groups (
 group_id, forum_id, auth_role_id
) SELECT 4, forum_id, 21
FROM phpbb_forums;
--  set FORUMFULL for ADMINS group
INSERT INTO phpbb_acl_groups (
 group_id, forum_id, auth_role_id
) SELECT 5, forum_id, 14
FROM phpbb_forums;
--  set BOTS for BOTS group
INSERT INTO phpbb_acl_groups (
 group_id, forum_id, auth_role_id
) SELECT 6, forum_id, 19
FROM phpbb_forums;
-- if I had categories, I would set role 17 for groups 1,2,3,6



-- 4. transfer topics
INSERT INTO phpbb_topics (
 topic_id, topic_title, topic_time, topic_last_post_time,
 topic_poster, topic_first_poster_name, topic_last_poster_id, topic_last_poster_name,
 forum_id, topic_replies,
 topic_first_post_id, topic_last_post_id
)
SELECT t.topic_id, t.topic_title, unix_timestamp(t.topic_start_time), unix_timestamp(t.topic_time),
 CASE t.topic_poster WHEN 1 THEN t.topic_poster+1 ELSE t.topic_poster+@user_id_jump END AS topic_poster_id, t.topic_poster_name, 
 CASE t.topic_last_poster WHEN 1 THEN t.topic_last_poster+1 ELSE t.topic_last_poster+@user_id_jump END, t.topic_last_poster_name,
 t.forum_id,
 (SELECT count(*) FROM bb_posts WHERE topic_id = t.topic_id)-1 AS topic_replies,
 (SELECT min(post_id) FROM bb_posts WHERE topic_id = t.topic_id) AS topic_first_post_id,
 (SELECT max(post_id) FROM bb_posts WHERE topic_id = t.topic_id) AS topic_last_post_id
FROM bb_topics t;

-- 4a. update poster-related data
-- by members
UPDATE phpbb_topics t
SET topic_first_poster_name = (SELECT u.user_login FROM wp_users u WHERE u.ID = t.topic_poster-@user_id_jump),
  topic_last_poster_name = (SELECT u.user_login FROM wp_users u WHERE u.ID = t.topic_last_poster_id-@user_id_jump),
  topic_replies_real = topic_replies
WHERE t.topic_poster > (@user_id_jump+1);
-- and admin
UPDATE phpbb_topics t
SET topic_first_poster_name = (SELECT u.user_login FROM wp_users u WHERE u.ID = t.topic_poster-1),
  topic_last_poster_name = (SELECT u.user_login FROM wp_users u WHERE u.ID = t.topic_last_poster_id-1),
  topic_replies_real = topic_replies
WHERE t.topic_poster = 2;




-- 5. transfer posts
INSERT INTO phpbb_posts (
 post_id, topic_id, forum_id, poster_id, poster_ip, post_time,
 post_username,
 post_subject, post_text
)
SELECT p.post_id, p.topic_id, p.forum_id, CASE p.poster_id WHEN 1 THEN p.poster_id+1 ELSE p.poster_id+@user_id_jump END AS poster_id, 
 p.poster_ip, unix_timestamp(p.post_time),
 (SELECT user_login FROM wp_users WHERE ID = p.poster_id) AS username,
 '', p.post_text
FROM bb_posts p;




-- 6. update connection between users and topics
INSERT INTO phpbb_topics_posted (
 user_id, topic_id, topic_posted
)
SELECT DISTINCT topic_poster, topic_id, 1
FROM phpbb_topics;



-- 7. re-sync and re-count forums, topics and posts in phpBB3 admin, on General page



-- SET SQL_MODE=@OLD_SQL_MODE;
-- SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

cricketrevolt
Registered User
Posts: 6
Joined: Sun Oct 24, 2010 5:13 am

Re: bbPress to phpBB3 - SQL converter

Post by cricketrevolt » Sun Nov 28, 2010 12:32 pm

Guys is there a Simplepress FOrum to phpBB 3 converter

Is it similar to this one ?

asaadfazal
Registered User
Posts: 1
Joined: Sat Jan 01, 2011 8:35 am

Re: bbPress to phpBB3 - SQL converter

Post by asaadfazal » Sat Jan 01, 2011 8:40 am

illustrationism wrote:Here's what worked for me. Though you are definitely going to have to have users reset their passwords...

Code: Select all

-- SET NAMES utf8;
-- SET SQL_MODE='';
-- SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
-- SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';



-- 0. BACKUP!
-- really, *backup* your default instalation of phpBB3
-- if something goes wrong, you can get it back to what it was and start over


-- 1. clear tables
DELETE FROM phpbb_forums;
DELETE FROM phpbb_topics;
DELETE FROM phpbb_topics_posted;
DELETE FROM phpbb_topics_track;
DELETE FROM phpbb_posts;
DELETE FROM phpbb_acl_groups WHERE forum_id > 0;
-- there is a difference in user_id.
-- in bbPress, your admin was 1, in phpBB3 is 2 (Anonymous is 1).
-- phpBB3 also has 50 bots preinstalled, thus member user_ids from bbPress must be pushed up:
--  2 becomes 53, 3 -> 54 etc.

SET @user_id_jump = 51;

-- now clear all users except Anon, Admin and Bots
DELETE FROM phpbb_user_group WHERE user_id > (@user_id_jump+1);
DELETE FROM phpbb_users WHERE user_id > (@user_id_jump+1);
DELETE FROM phpbb_acl_users WHERE user_id > (@user_id_jump+1);



-- 2. initial users transfer - assumes all users except 1st (which is admin) are normal users. admin is not transfered.
-- group_id: 2 == REGISTERED
-- user_type: 0 == USER_NORMAL
-- user_style: 2 == my newly installed style. 1 = prosilver, 2 = my style
-- user_permissions: ?! == now is this calculated?
-- user_email_hash: ?! == now is this calculated?
INSERT INTO phpbb_users 
(user_id, user_type, group_id, username, username_clean, user_password, user_pass_convert, user_email, user_website, user_lastvisit, user_regdate, user_timezone, user_dst, user_lang, user_dateformat, user_style, user_options)
SELECT ID + @user_id_jump AS user_id, 0, 2, user_login, lower(REPLACE(user_login, '.', '_')), user_pass, 1, user_email, user_url, unix_timestamp(user_registered), unix_timestamp(user_registered), 1, 1, 'sr', 'D, d M Y, G:i', 2, 895
FROM wp_users
WHERE ID > 1;


-- 2a. update user_group table
INSERT INTO phpbb_user_group (
 group_id, user_id, group_leader, user_pending
)
SELECT DISTINCT group_id, user_id, 0, 0
FROM phpbb_users
WHERE user_id > (@user_id_jump+1);



-- 3. transfer forums
-- assumes that all forums are equal, no parents, no sub-forums
-- forum_type: 1 == POST-based forum
-- forum_parents: ?! == how it is calculated?
INSERT INTO phpbb_forums (
 forum_id, parent_id, left_id, right_id,
 forum_name, forum_desc, forum_type,
 forum_posts, forum_topics, forum_topics_real,
 forum_last_post_id, forum_last_poster_id, forum_last_post_subject, forum_last_post_time, forum_last_poster_name,
 forum_flags
)
SELECT f.forum_id, 0, 0, 0,
 f.forum_name, f.forum_desc, 1,
 f.posts, f.topics, f.topics,
 (SELECT MAX(post_id) FROM bb_posts WHERE forum_id = f.forum_id) AS last_post_id, 0, '', 0, '',
 32
FROM bb_forums f;

-- 3a. update last post* data
UPDATE phpbb_forums f
SET forum_last_poster_id = (SELECT CASE poster_id WHEN 1 THEN poster_id+1 ELSE poster_id+@user_id_jump END AS poster_id FROM bb_posts WHERE forum_id = f.forum_id AND post_id = f.forum_last_post_id),
  forum_last_poster_name = (SELECT u.user_login FROM wp_users u, bb_posts p WHERE p.forum_id = f.forum_id AND p.post_id = f.forum_last_post_id AND u.ID = p.poster_id),
  forum_last_post_time = (SELECT unix_timestamp(post_time) FROM bb_posts WHERE forum_id = f.forum_id AND post_id = f.forum_last_post_id);

-- 3b. update left_id and right_id
-- see: http://www.sitepoint.com/article/hierarchical-data-database/2
-- this continues the assumption that all forums are equal, with no parents and no sub-forums
UPDATE phpbb_forums f
SET left_id = (forum_id-1)*2 + 1,
  right_id = (forum_id-1)*2 + 2;

-- 3c. set forum ACL -- this sets required permissions for the forums, based on groups
--  set READONLY for GUESTS group
INSERT INTO phpbb_acl_groups (
 group_id, forum_id, auth_role_id
) SELECT 1, forum_id, 17
FROM phpbb_forums;
--  set FORUMSTANDARD for REGISTERED group
INSERT INTO phpbb_acl_groups (
 group_id, forum_id, auth_role_id
) SELECT 2, forum_id, 15
FROM phpbb_forums;
--  set FORUMSTANDARD for REGISTERED COPPA group
INSERT INTO phpbb_acl_groups (
 group_id, forum_id, auth_role_id
) SELECT 3, forum_id, 15
FROM phpbb_forums;
--  set FORUMPOLLS for GLOBAL_MODS group
INSERT INTO phpbb_acl_groups (
 group_id, forum_id, auth_role_id
) SELECT 4, forum_id, 21
FROM phpbb_forums;
--  set FORUMFULL for ADMINS group
INSERT INTO phpbb_acl_groups (
 group_id, forum_id, auth_role_id
) SELECT 5, forum_id, 14
FROM phpbb_forums;
--  set BOTS for BOTS group
INSERT INTO phpbb_acl_groups (
 group_id, forum_id, auth_role_id
) SELECT 6, forum_id, 19
FROM phpbb_forums;
-- if I had categories, I would set role 17 for groups 1,2,3,6



-- 4. transfer topics
INSERT INTO phpbb_topics (
 topic_id, topic_title, topic_time, topic_last_post_time,
 topic_poster, topic_first_poster_name, topic_last_poster_id, topic_last_poster_name,
 forum_id, topic_replies,
 topic_first_post_id, topic_last_post_id
)
SELECT t.topic_id, t.topic_title, unix_timestamp(t.topic_start_time), unix_timestamp(t.topic_time),
 CASE t.topic_poster WHEN 1 THEN t.topic_poster+1 ELSE t.topic_poster+@user_id_jump END AS topic_poster_id, t.topic_poster_name, 
 CASE t.topic_last_poster WHEN 1 THEN t.topic_last_poster+1 ELSE t.topic_last_poster+@user_id_jump END, t.topic_last_poster_name,
 t.forum_id,
 (SELECT count(*) FROM bb_posts WHERE topic_id = t.topic_id)-1 AS topic_replies,
 (SELECT min(post_id) FROM bb_posts WHERE topic_id = t.topic_id) AS topic_first_post_id,
 (SELECT max(post_id) FROM bb_posts WHERE topic_id = t.topic_id) AS topic_last_post_id
FROM bb_topics t;

-- 4a. update poster-related data
-- by members
UPDATE phpbb_topics t
SET topic_first_poster_name = (SELECT u.user_login FROM wp_users u WHERE u.ID = t.topic_poster-@user_id_jump),
  topic_last_poster_name = (SELECT u.user_login FROM wp_users u WHERE u.ID = t.topic_last_poster_id-@user_id_jump),
  topic_replies_real = topic_replies
WHERE t.topic_poster > (@user_id_jump+1);
-- and admin
UPDATE phpbb_topics t
SET topic_first_poster_name = (SELECT u.user_login FROM wp_users u WHERE u.ID = t.topic_poster-1),
  topic_last_poster_name = (SELECT u.user_login FROM wp_users u WHERE u.ID = t.topic_last_poster_id-1),
  topic_replies_real = topic_replies
WHERE t.topic_poster = 2;




-- 5. transfer posts
INSERT INTO phpbb_posts (
 post_id, topic_id, forum_id, poster_id, poster_ip, post_time,
 post_username,
 post_subject, post_text
)
SELECT p.post_id, p.topic_id, p.forum_id, CASE p.poster_id WHEN 1 THEN p.poster_id+1 ELSE p.poster_id+@user_id_jump END AS poster_id, 
 p.poster_ip, unix_timestamp(p.post_time),
 (SELECT user_login FROM wp_users WHERE ID = p.poster_id) AS username,
 '', p.post_text
FROM bb_posts p;




-- 6. update connection between users and topics
INSERT INTO phpbb_topics_posted (
 user_id, topic_id, topic_posted
)
SELECT DISTINCT topic_poster, topic_id, 1
FROM phpbb_topics;



-- 7. re-sync and re-count forums, topics and posts in phpBB3 admin, on General page



-- SET SQL_MODE=@OLD_SQL_MODE;
-- SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
thanks it worked for me all right

pesh
Registered User
Posts: 1
Joined: Tue Apr 05, 2011 5:23 am

Re: bbPress to phpBB3 - SQL converter

Post by pesh » Tue Apr 05, 2011 5:25 am

New here... in order to use this converter, what do I need to edit? I see the php files in /install/converters but I am not at all good with php, this looks like all SQL to me... should I run this in mysql or is there a php file that I need to edit?

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:

Re: bbPress to phpBB3 - SQL converter

Post by D¡cky » Tue Apr 05, 2011 3:00 pm

It is all done in MySQL.
Have you hugged someone today?

primehifi
Registered User
Posts: 1
Joined: Thu Feb 09, 2012 9:23 pm

Re: bbPress to phpBB3 - SQL converter

Post by primehifi » Thu Feb 09, 2012 9:35 pm

Code: Select all

    -- SET NAMES utf8;
    -- SET SQL_MODE='';
    -- SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
    -- SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';



    -- 0. BACKUP!
    -- really, *backup* your default instalation of phpBB3
    -- if something goes wrong, you can get it back to what it was and start over


    -- 1. clear tables
    DELETE FROM phpbb_forums;
    DELETE FROM phpbb_topics;
    DELETE FROM phpbb_topics_posted;
    DELETE FROM phpbb_topics_track;
    DELETE FROM phpbb_posts;
    DELETE FROM phpbb_acl_groups WHERE forum_id > 0;
    -- there is a difference in user_id.
    -- in bbPress, your admin was 1, in phpBB3 is 2 (Anonymous is 1).
    -- phpBB3 also has 50 bots preinstalled, thus member user_ids from bbPress must be pushed up:
    --  2 becomes 53, 3 -> 54 etc.

    SET @user_id_jump = 51;

    -- now clear all users except Anon, Admin and Bots
    DELETE FROM phpbb_user_group WHERE user_id > (@user_id_jump+1);
    DELETE FROM phpbb_users WHERE user_id > (@user_id_jump+1);
    DELETE FROM phpbb_acl_users WHERE user_id > (@user_id_jump+1);



    -- 2. initial users transfer - assumes all users except 1st (which is admin) are normal users. admin is not transfered.
    -- group_id: 2 == REGISTERED
    -- user_type: 0 == USER_NORMAL
    -- user_style: 2 == my newly installed style. 1 = prosilver, 2 = my style
    -- user_permissions: ?! == now is this calculated?
    -- user_email_hash: ?! == now is this calculated?
    INSERT INTO phpbb_users
    (user_id, user_type, group_id, username, username_clean, user_password, user_pass_convert, user_email, user_website, user_lastvisit, user_regdate, user_timezone, user_dst, user_lang, user_dateformat, user_style, user_options)
    SELECT ID + @user_id_jump AS user_id, 0, 2, user_login, lower(REPLACE(user_login, '.', '_')), user_pass, 1, user_email, user_url, unix_timestamp(user_registered), unix_timestamp(user_registered), 1, 1, 'sr', 'D, d M Y, G:i', 2, 895
    FROM wp_users
    WHERE ID > 1;


    -- 2a. update user_group table
    INSERT INTO phpbb_user_group (
     group_id, user_id, group_leader, user_pending
    )
    SELECT DISTINCT group_id, user_id, 0, 0
    FROM phpbb_users
    WHERE user_id > (@user_id_jump+1);



    -- 3. transfer forums
    -- assumes that all forums are equal, no parents, no sub-forums
    -- forum_type: 1 == POST-based forum
    -- forum_parents: ?! == how it is calculated?
    INSERT INTO phpbb_forums (
     forum_id, parent_id, left_id, right_id,
     forum_name, forum_desc, forum_type,
     forum_posts, forum_topics, forum_topics_real,
     forum_last_post_id, forum_last_poster_id, forum_last_post_subject, forum_last_post_time, forum_last_poster_name,
     forum_flags
    )
    SELECT f.forum_id, 0, 0, 0,
     f.forum_name, f.forum_desc, 1,
     f.posts, f.topics, f.topics,
     (SELECT MAX(post_id) FROM bb_posts WHERE forum_id = f.forum_id) AS last_post_id, 0, '', 0, '',
     32
    FROM bb_forums f;

    -- 3a. update last post* data
    UPDATE phpbb_forums f
    SET forum_last_poster_id = (SELECT CASE poster_id WHEN 1 THEN poster_id+1 ELSE poster_id+@user_id_jump END AS poster_id FROM bb_posts WHERE forum_id = f.forum_id AND post_id = f.forum_last_post_id),
      forum_last_poster_name = (SELECT u.user_login FROM wp_users u, bb_posts p WHERE p.forum_id = f.forum_id AND p.post_id = f.forum_last_post_id AND u.ID = p.poster_id),
      forum_last_post_time = (SELECT unix_timestamp(post_time) FROM bb_posts WHERE forum_id = f.forum_id AND post_id = f.forum_last_post_id);

    -- 3b. update left_id and right_id
    -- see: http://www.sitepoint.com/article/hierarchical-data-database/2
    -- this continues the assumption that all forums are equal, with no parents and no sub-forums
    UPDATE phpbb_forums f
    SET left_id = (forum_id-1)*2 + 1,
      right_id = (forum_id-1)*2 + 2;

    -- 3c. set forum ACL -- this sets required permissions for the forums, based on groups
    --  set READONLY for GUESTS group
    INSERT INTO phpbb_acl_groups (
     group_id, forum_id, auth_role_id
    ) SELECT 1, forum_id, 17
    FROM phpbb_forums;
    --  set FORUMSTANDARD for REGISTERED group
    INSERT INTO phpbb_acl_groups (
     group_id, forum_id, auth_role_id
    ) SELECT 2, forum_id, 15
    FROM phpbb_forums;
    --  set FORUMSTANDARD for REGISTERED COPPA group
    INSERT INTO phpbb_acl_groups (
     group_id, forum_id, auth_role_id
    ) SELECT 3, forum_id, 15
    FROM phpbb_forums;
    --  set FORUMPOLLS for GLOBAL_MODS group
    INSERT INTO phpbb_acl_groups (
     group_id, forum_id, auth_role_id
    ) SELECT 4, forum_id, 21
    FROM phpbb_forums;
    --  set FORUMFULL for ADMINS group
    INSERT INTO phpbb_acl_groups (
     group_id, forum_id, auth_role_id
    ) SELECT 5, forum_id, 14
    FROM phpbb_forums;
    --  set BOTS for BOTS group
    INSERT INTO phpbb_acl_groups (
     group_id, forum_id, auth_role_id
    ) SELECT 6, forum_id, 19
    FROM phpbb_forums;
    -- if I had categories, I would set role 17 for groups 1,2,3,6



    -- 4. transfer topics
    INSERT INTO phpbb_topics (
     topic_id, topic_title, topic_time, topic_last_post_time,
     topic_poster, topic_first_poster_name, topic_last_poster_id, topic_last_poster_name,
     forum_id, topic_replies,
     topic_first_post_id, topic_last_post_id
    )
    SELECT t.topic_id, t.topic_title, unix_timestamp(t.topic_start_time), unix_timestamp(t.topic_time),
     CASE t.topic_poster WHEN 1 THEN t.topic_poster+1 ELSE t.topic_poster+@user_id_jump END AS topic_poster_id, t.topic_poster_name,
     CASE t.topic_last_poster WHEN 1 THEN t.topic_last_poster+1 ELSE t.topic_last_poster+@user_id_jump END, t.topic_last_poster_name,
     t.forum_id,
     (SELECT count(*) FROM bb_posts WHERE topic_id = t.topic_id)-1 AS topic_replies,
     (SELECT min(post_id) FROM bb_posts WHERE topic_id = t.topic_id) AS topic_first_post_id,
     (SELECT max(post_id) FROM bb_posts WHERE topic_id = t.topic_id) AS topic_last_post_id
    FROM bb_topics t;

    -- 4a. update poster-related data
    -- by members
    UPDATE phpbb_topics t
    SET topic_first_poster_name = (SELECT u.user_login FROM wp_users u WHERE u.ID = t.topic_poster-@user_id_jump),
      topic_last_poster_name = (SELECT u.user_login FROM wp_users u WHERE u.ID = t.topic_last_poster_id-@user_id_jump),
      topic_replies_real = topic_replies
    WHERE t.topic_poster > (@user_id_jump+1);
    -- and admin
    UPDATE phpbb_topics t
    SET topic_first_poster_name = (SELECT u.user_login FROM wp_users u WHERE u.ID = t.topic_poster-1),
      topic_last_poster_name = (SELECT u.user_login FROM wp_users u WHERE u.ID = t.topic_last_poster_id-1),
      topic_replies_real = topic_replies
    WHERE t.topic_poster = 2;




    -- 5. transfer posts
    INSERT INTO phpbb_posts (
     post_id, topic_id, forum_id, poster_id, poster_ip, post_time,
     post_username,
     post_subject, post_text
    )
    SELECT p.post_id, p.topic_id, p.forum_id, CASE p.poster_id WHEN 1 THEN p.poster_id+1 ELSE p.poster_id+@user_id_jump END AS poster_id,
     p.poster_ip, unix_timestamp(p.post_time),
     (SELECT user_login FROM wp_users WHERE ID = p.poster_id) AS username,
     '', p.post_text
    FROM bb_posts p;




    -- 6. update connection between users and topics
    INSERT INTO phpbb_topics_posted (
     user_id, topic_id, topic_posted
    )
    SELECT DISTINCT topic_poster, topic_id, 1
    FROM phpbb_topics;



    -- 7. re-sync and re-count forums, topics and posts in phpBB3 admin, on General page



    -- SET SQL_MODE=@OLD_SQL_MODE;
    -- SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
In case anyone else stumbles upon this here's some added info:

Using the above code I've just converted a deep integrated Wordpress+BBPress install. Wordpress was version 3.3.1 and BBPress was 1.0.2 (which I believe is the final legit version of BBPress standalone).

The code above worked perfectly fine. The only additional sql section I needed in the db with both BBPress and PHPBB was wp_users as that's where my user db was from bbpress+wp integration. I just exported that out of my Wordpress install and plopped it back into the sql db with both PHPBB+BBPress. Ran the above code and boom, done.

The interesting thing as some people get incorrect is that you're not required to force a password reauth from your members. Wp+BB+PHPBB share the same password scheme but they only vary in the beginning characters.

Wordpress+BB =$P$ and PHPBB is $H$. If you just download the SQL files onto your comp you can do a find a replace in textedit and you're done, no password recovery required for your members. I did this when I switched from phpbb to bbpress about a year+ ago and now I'm doing it the other way to get back to phpbb.

Locked

Return to “[3.0.x] Convertors”

Who is online

Users browsing this forum: No registered users and 4 guests

cron