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!

Post Reply

Return to “[3.2.x] Convertors”

Who is online

Users browsing this forum: No registered users and 3 guests