bbPress to phpBB 3.2.1

Converting from other board software? Good decision! Need help? Have a question about a convertor? Wish to offer a convertor package? Post here.
Post Reply
mrbrns
Registered User
Posts: 1
Joined: Wed Sep 20, 2017 8:04 pm

bbPress to phpBB 3.2.1

Post by mrbrns » Wed Sep 20, 2017 8:13 pm

I got a lot of help from this topic viewtopic.php?f=65&t=673815 when importing a bbPress forum into phpBB 3.2.1. I had to make some changes to the import queries. I might have missed something so feel free to post updates.

Code: Select all

/* If anything goes wrong, let's not muck up the database */
START TRANSACTION;

/* Clear tables and reset IDs */
TRUNCATE TABLE phpbb_forums;
TRUNCATE TABLE phpbb_topics;
TRUNCATE TABLE phpbb_topics_posted;
TRUNCATE TABLE phpbb_topics_track;
TRUNCATE TABLE 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 becomes 54 etc. */
SET @user_id_jump = 51;

/* Set the style ID. I'm using the Oxygen style but you can change this
to something else */
SET @style_id = 3;

/* Clear all users except anonymous, admins 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);

/* Transfer non-admin users */
INSERT INTO
    phpbb_users (
        user_id,
        user_type,
        group_id,
        username,
        username_clean,
        user_password,
        user_passchg,
        user_email,
        user_lastvisit,
        user_regdate,
        user_timezone,
        user_lang,
        user_dateformat,
        user_style,
        user_options
    )
SELECT
    ID + @user_id_jump /* New ID */,
    0 /* User type ("Normal user") */,
    2 /* Group ("Registered") */,
    user_login /* Username */,
    LOWER(REPLACE(user_login, '.', '_')) /* Sanitised username */,
    user_pass /* Passwords */,
    1 /* Convert passwords */,
    user_email /* Email address */,
    UNIX_TIMESTAMP(user_registered) /* Date of the user's last activity */,
    UNIX_TIMESTAMP(user_registered) /* Date the user signed up */,
    1 /* Default timezone */,
    'en' /* Language */,
    'D, d M Y, G:i' /* Date format */,
    @style_id /* Style (theme, basically) */,
    895 /* Flags */
FROM
    wp_users
WHERE
    ID > 1;

/* Transfer membership to groups */
INSERT INTO
    phpbb_user_group (
        group_id,
        user_id,
        group_leader,
        user_pending
    )
SELECT DISTINCT
    group_id /* Group ID */,
    user_id /* User ID */,
    0 /* Group leader */,
    0 /* User join request is pending */
FROM
    phpbb_users
WHERE
    user_id > @user_id_jump + 1;

/* Transfer forums */
INSERT INTO phpbb_forums (
    forum_id,
    parent_id,
    left_id,
    right_id,
    forum_name,
    forum_desc,
    forum_type,
    forum_posts_approved,
    forum_topics_approved,
    forum_last_post_id,
    forum_last_poster_id,
    forum_last_post_subject,
    forum_last_post_time,
    forum_last_poster_name,
    forum_flags
) SELECT
    f.id /* Forum ID */,
    0 /* Parent ID */,
    0 /* Left ID */,
    0 /* Right ID */,
    f.post_title /* Title */,
    f.post_content /* Description */,
    1 /* Forum type ("post-based") */,
    (
        SELECT
            COUNT(*)
        FROM
            wp_posts AS r
        INNER JOIN
            wp_posts AS t ON r.post_parent = t.id
        WHERE
            t.post_parent = f.id
        AND
            r.post_type = 'reply'
        AND
            t.post_type = 'topic'
    ) /* Number of posts */,
    (
        SELECT
            COUNT(*)
        FROM
            wp_posts AS t
        WHERE
            t.post_parent = f.id
        AND
            t.post_type = 'topic'
    ) /* Number of topics */,
    (
        SELECT
            COUNT(*)
        FROM
            wp_posts AS t
        WHERE
            t.post_parent = f.id
        AND
            t.post_type = 'topic'
    ) /* Last post ID */,
    (
        SELECT
            post_author + @user_id_jump
        FROM
            wp_posts AS p
        WHERE
            p.post_parent = f.id
        AND
            p.post_type = 'topic'
        ORDER BY
            p.post_date DESC LIMIT 1
    ) /* Last poster ID */,
    (
        SELECT
            post_title
        FROM
            wp_posts AS p
        WHERE
            p.post_parent = f.id
        AND
            p.post_type = 'topic'
        ORDER BY
            p.post_date DESC LIMIT 1
    ) /* Last post subject */,
    (
        SELECT
            UNIX_TIMESTAMP(post_date)
        FROM
            wp_posts AS p
        WHERE
            p.post_parent = f.id
        AND
            p.post_type = 'topic'
        ORDER BY
            p.post_date DESC LIMIT 1
    ) /* Last post time */,
    (
        SELECT
            u.user_login
        FROM
            wp_posts AS p
        INNER JOIN
            wp_users AS u ON p.post_author = u.id
        WHERE
            p.post_parent = f.id
        AND
            p.post_type = 'topic'
        ORDER BY
            p.post_date DESC LIMIT 1
    ) /* Last poster name */,
    32 /* Flags */
FROM
    wp_posts AS f
WHERE
    f.post_type = 'forum';

-- Update Left and Right IDs for forums
UPDATE
    phpbb_forums
SET
    left_id = (forum_id - 1) * 2 + 1,
    right_id = (forum_id - 1) * 2 + 2;

/* Set read-only permissions for the Guests group */
INSERT INTO
    phpbb_acl_groups (
        group_id,
        forum_id,
        auth_role_id
    )
SELECT
    1,
    forum_id,
    17
FROM
    phpbb_forums;

/* Set standard permissions for the Registered Users group */
INSERT INTO
    phpbb_acl_groups (
        group_id,
        forum_id,
        auth_role_id
    )
SELECT
    2,
    forum_id,
    15
FROM
    phpbb_forums;

/* Set standard permissions for the COPPA group */
INSERT INTO
    phpbb_acl_groups (
        group_id,
        forum_id,
        auth_role_id
    )
SELECT
    3,
    forum_id,
    15
FROM
    phpbb_forums;

/* Set poll permissions for mods */
INSERT INTO
    phpbb_acl_groups (
        group_id,
        forum_id,
        auth_role_id
    )
SELECT
    4,
    forum_id,
    21
FROM
    phpbb_forums;

/* Set full permissions for adsmin */
INSERT INTO
    phpbb_acl_groups (
        group_id,
        forum_id,
        auth_role_id
    )
SELECT
    5,
    forum_id,
    14
FROM
    phpbb_forums;

/* Add other permissions for bots */
INSERT INTO
    phpbb_acl_groups (
        group_id,
        forum_id,
        auth_role_id
    )
SELECT
    6,
    forum_id,
    19
FROM
    phpbb_forums;

/* Transfer the 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_posts_approved,
        topic_first_post_id,
        topic_last_post_id
    )
SELECT
    t.id /* Topic ID */,
    t.post_title /* Topic title */,
    UNIX_TIMESTAMP(t.post_date) /* Date it was posted */,
    UNIX_TIMESTAMP(t.post_date) /* Date it was updated */,
    CASE t.post_author
        WHEN 1 THEN
            t.post_author + 1
        ELSE
            t.post_author + @user_id_jump
    END /* Set the poster ID, remembering to up the user ID if it wasn't posted by an admin */,
    IFNULL(
        (
            SELECT
                lu.user_login
            FROM
                wp_posts AS l
            INNER JOIN
                wp_users AS lu ON l.post_author = lu.id
            WHERE
                l.post_parent = t.id
            AND
                l.post_type = 'reply'
            ORDER BY
                l.post_date
            LIMIT 1
        ),
        u.user_login
    ) /* Username of the first poster (or topic creator if none exists) */,
    IFNULL(
        (
            SELECT
                CASE l.post_author
                    WHEN 1 THEN
                        l.post_author + 1
                    ELSE
                        l.post_author + @user_id_jump
                    END
            FROM
                wp_posts AS l
            WHERE
                l.post_parent = t.id
            AND
                l.post_type = 'reply'
            ORDER BY
                l.post_date DESC
            LIMIT 1
        ),
        CASE t.post_author
            WHEN 1 THEN
                t.post_author + 1
            ELSE
                t.post_author + @user_id_jump
            END
    ) /* Set the last poster (or topic creator, if non exists) ID, remembering to up the user ID if it wasn't an admin */,
    (
        IFNULL(
            (
                SELECT
                    lu.user_login
                FROM
                    wp_posts AS l
                INNER JOIN
                    wp_users AS lu ON l.post_author = lu.id
                WHERE
                    l.post_parent = t.id
                AND
                    l.post_type = 'reply'
                ORDER BY
                    l.post_date DESC
                LIMIT 1
            ),
            u.user_login
        )
    ) /* Username of the last poster (or topic creator if none exists) */,
    t.post_parent /* Forum ID */,
    (
        SELECT
            COUNT(*)
        FROM
            wp_posts AS r
        WHERE
            r.post_parent = t.id
        AND
            r.post_type = 'reply'
    ) /* Number of replies */,
    (
        SELECT
            r.id
        FROM
            wp_posts AS r
        WHERE
            r.post_parent = t.id
        AND
            r.post_type = 'reply'
        ORDER BY
            r.post_date
        LIMIT 1
    ) /* First reply ID */,
    (
        SELECT
            r.id
        FROM
            wp_posts AS r
        WHERE
            r.post_parent = t.id
        AND
            r.post_type = 'reply'
        ORDER BY
            r.post_date DESC
        LIMIT 1
    ) /* Last reply ID */
FROM
    wp_posts AS t
INNER JOIN
    wp_users AS u ON t.post_author = u.id
WHERE
    t.post_type = 'topic';

/* Transfer replies */
INSERT INTO
    phpbb_posts (
        post_id,
        topic_id,
        forum_id,
        poster_id,
        poster_ip,
        post_time,
        post_username,
        post_subject,
        post_text
    )
SELECT
    p.id /* Post ID */,
    t.id /* Topic ID */,
    f.id /* Forum ID */,
    CASE
        p.post_author
    WHEN 1 THEN
        p.post_author + 1
    ELSE
        p.post_author + @user_id_jump
    END /* The ID of the poster, upping the number for non-admins */,
    (
        SELECT
            m.meta_value
        FROM
            wp_postmeta AS m
        WHERE
            m.post_id = p.id
        AND
            meta_key = '_bbp_author_ip'
    ) /* Poster IP address */,
    UNIX_TIMESTAMP(p.post_date) /* Date of the post */,
    u.user_login /* Username of the poster */,
    '' /* Empty subject */,
    p.post_content /* Post text */
FROM
    wp_posts AS p
INNER JOIN
    wp_users AS u ON p.post_author = u.id
INNER JOIN
    wp_posts AS t ON p.post_parent = t.id OR p.id = t.id
INNER JOIN
    wp_posts AS f ON t.post_parent = f.id
WHERE
    p.post_type in ('reply','topic')
AND
    t.post_type = 'topic'
AND
    f.post_type = 'forum';

/* Update connections 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;

/* Rehash email addresses */
UPDATE
    phpbb_users
SET
    user_email_hash = CONCAT(CRC32(LOWER(user_email)), LENGTH(user_email));

COMMIT;
/* If we're happy, let's save our changes */
Please note that your posts will not be set as approved after the import. Use the following queries to approve posts and topics:

Code: Select all

UPDATE phpbb_posts SET post_visibility=1;
UPDATE phpbb_topics SET topic_visibility=1;
Enjoy!

paule3d
Registered User
Posts: 1
Joined: Thu Nov 16, 2017 10:26 am

Re: bbPress to phpBB 3.2.1

Post by paule3d » Thu Nov 16, 2017 10:34 am

I've been looking for bbPress to phpBB converter since I first made the mistake of setting up a bbPress forum before realising it was lacking some basic features. I found topics relating to this from many year ago and nothing for recent versions of bbPress. The older topics perhaps aimed at standalone bbPress.

Has this been used on the more recent bbPress (e.g., 2.5) from a Wordpress integration?

Any idea if it would be suitable for me? I would like to integrate phpBB with my Wordpress website and users, but replace the forum with a more functional and industry standard one such as phpBB.

Thank you.

Post Reply

Return to “[3.2.x] Convertors”

Who is online

Users browsing this forum: No registered users and 2 guests

cron