Convert BBpress SQL to PHPbb SQL?

[fbemo]

Hi,

 

I've been looking around and still couldn't find the solutions to my problems, does anyone know how to convert BBpress SQL to PHPbb SQL? Or BBpress SQL to SMF SQL. Is this the right thing to do?

 

In my situation, I have a BBpress forum integrated with Wordpress, I don't feel like using is since it's hard for me to understand so I thought of convert it to PHPbb or SMF forum.

 

Thanks in advance!

[Philip]

Take a look at the forum you're wanting to switch to's forums... like for instance (it's an older topic, but first on google list)

http://www.simplemachines.org/community/index.php?topic=227796.0

[fbemo]

Take a look at the forum you're wanting to switch to's forums... like for instance (it's an older topic, but first on google list)

http://www.simplemachines.org/community/index.php?topic=227796.0

 

Thanks for this link, by the way actually I prefer phpbb.

 

Ok, I found this one:

 

    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;

 

But where to use this code?

[Philip]

You'd throw that into mysql's CLI or PhpMyAdmin. Make sure to make a backup of all your data first!