[SOLVED] Post author sync problem

Converting from other board software? Good decision! Need help? Have a question about a convertor? Wish to offer a convertor package? Post here.
Ideas Centre
Locked
sebbat
Registered User
Posts: 8
Joined: Wed Apr 22, 2009 3:49 pm

[SOLVED] Post author sync problem

Post by sebbat » Fri Feb 13, 2015 11:50 am

Hi,

I used a SQL request to migrate from bbPress to phpBB 3.0. All went well, except that i have a sync problem between the author displayed in the topic list and the real topic author.

Example, look at the topic named "Bonne année 2015"

Topic list : http://www.sudretroelectro.org/forum/vi ... php?f=7389
The topic : http://www.sudretroelectro.org/forum/vi ... 89&t=14082

Topic author is NYR88 (id 252) but in topic the name displayed as author is Sebbat (id 2), but the link on the author name point to the good id.

Is there a request i can use to "resync" displayed author names ?

Here is the request i used.

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_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 /* 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 */
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,
    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 */,
    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'
    ) /* Number of topics */,
    (
        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
    ) /* 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_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 */,
    (
        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 */
Last edited by sebbat on Fri Feb 13, 2015 3:43 pm, edited 1 time in total.

sebbat
Registered User
Posts: 8
Joined: Wed Apr 22, 2009 3:49 pm

Re: Post author sync problem

Post by sebbat » Fri Feb 13, 2015 1:40 pm

I have another problem : answer count for every topic is set to 0 ! So page 2 is never displayed in long topics.

sebbat
Registered User
Posts: 8
Joined: Wed Apr 22, 2009 3:49 pm

Re: Post author sync problem

Post by sebbat » Fri Feb 13, 2015 3:43 pm

Wasn't really bad after all. Manual resync of each forum solved the problem, but i was not aware of such functions.

Locked

Return to “[3.0.x] Convertors”