Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Convert newer-style bbPress forums to phpBB (3.0). Based on code by primehifi (http://mark.tl/1l9HyRR)
/* 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_pass_convert,
user_email,
user_website,
user_lastvisit,
user_regdate,
user_timezone,
user_dst,
user_lang,
user_dateformat,
user_style,
user_options,
user_permissions,
user_sig,
user_occ,
user_interests
)
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 */,
user_url /* URL */,
UNIX_TIMESTAMP(user_registered) /* Date of the user's last activity */,
UNIX_TIMESTAMP(user_registered) /* Date the user signed up */,
1 /* Default timezone */,
1 /* Overbse aylight saving time */,
'en' /* Language */,
'D, d M Y, G:i' /* Date format */,
@style_id /* Style (theme, basically) */,
895 /* Flags */,
'' /* User Permissions */,
'' /* User sig */,
'' /* User occ */,
'' /* User interensts */
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,
forum_rules,
parent_id,
forum_parents,
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.id /* Forum ID */,
'' /* Forum rules */,
0 /* Parent ID */,
0 /* Forum parents */,
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'
) /* Number of topics */,
(
IFNULL
(
(
SELECT
id
FROM
wp_posts AS p
WHERE
p.post_parent = f.id
AND
p.post_type = 'topic'
ORDER BY
p.post_date DESC LIMIT 1
),
0
)
) /* Last post ID */,
(
IFNULL
(
(
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
),
0
)
) /* Last poster ID */,
(
IFNULL
(
(
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
),
0
)
) /* Last post subject */,
(
IFNULL
(
(
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
),
(
SELECT UNIX_TIMESTAMP(NOW())
)
)
) /* Last post time */,
(
IFNULL
(
(
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
),
0
)
) /* 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_replies,
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 */,
(
IFNULL
(
(
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
),
t.id
)
) /* First reply ID */,
(
IFNULL
(
(
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
),
t.id
)
) /* 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 original topic content */
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 */,
p.id /* Topic ID */,
t.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_openproducts_com_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_openproducts_com_posts AS p
INNER JOIN
wp_openproducts_com_users AS u ON p.post_author = u.id
INNER JOIN
wp_openproducts_com_posts AS t ON p.post_parent = t.id
INNER JOIN
wp_openproducts_com_posts AS f ON t.post_parent = f.id
WHERE
(p.post_type = 'topic' AND t.post_type = 'forum' );
/* 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
INNER JOIN
wp_posts AS f ON t.post_parent = f.id
WHERE
(p.post_type = 'reply' AND t.post_type = 'topic' );
/* 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 */
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.