迁移旧用户¶

当你从Trac迁移时，你会得到一些陈旧的旧用户。

当你从一种认证系统切换到另一种系统时，比如从手工创建的用户到LDAP时，也会发生这种情况。

此方法是为在MySQL上运行的Redmine 1.3.3编写的。

旧用户表¶

使用此脚本创建此表，并用旧的和新的用户登录名填充它。新用户应该已经存在，因此请手动创建它们或使用类似User CSV导入插件v0.0.1的东西

/* Fill this table with the old login name and the new login name */
CREATE TABLE `old_users` (
  `old_login` varchar(30) NOT NULL,
  `new_login` varchar(30) NOT NULL,
  UNIQUE KEY `old_login_UNIQUE` (`old_login`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

用户迁移流程¶

DROP PROCEDURE IF EXISTS fixup_oldusers;
DELIMITER //
CREATE PROCEDURE fixup_oldusers()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE oldid, newid INT;

    DECLARE oldcur CURSOR FOR 
        SELECT u1.id as old_id, u2.id as new_id FROM old_users
            JOIN users u1 on old_login = u1.login
            JOIN users u2 on new_login = u2.login;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN oldcur;

    read_loop: LOOP
        FETCH oldcur into oldid, newid;

        IF done THEN
            LEAVE read_loop;
        END IF;

        IF oldid != newid THEN

            UPDATE journals SET user_id = newid WHERE user_id = oldid;
            UPDATE attachments SET author_id = newid WHERE author_id = oldid;
            UPDATE wiki_contents SET author_id = newid WHERE author_id = oldid;
            UPDATE wiki_content_versions SET author_id = newid WHERE author_id = oldid;
            UPDATE time_entries SET user_id = newid WHERE user_id = oldid;
            UPDATE news SET author_id = newid WHERE author_id = oldid;
            UPDATE issue_categories SET assigned_to_id = newid WHERE assigned_to_id = oldid;
            UPDATE comments SET author_id = newid WHERE author_id = oldid;
            UPDATE changesets SET user_id = newid WHERE user_id = oldid;
            UPDATE queries SET user_id = newid WHERE user_id = oldid;
            UPDATE messages SET author_id = newid WHERE author_id = oldid;
            UPDATE messages SET last_reply_id = newid WHERE last_reply_id = oldid;
            UPDATE issues SET assigned_to_id = newid WHERE assigned_to_id = oldid;
            UPDATE issues SET author_id = newid WHERE author_id = oldid;

            UPDATE journal_details SET old_value = newid WHERE prop_key = 'assigned_to_id' AND old_value = oldid;
            UPDATE journal_details SET value = newid WHERE prop_key = 'assigned_to_id' AND value = oldid;

            /* Delete old user data */
            DELETE FROM member_roles WHERE member_id = oldid;
            DELETE FROM members WHERE user_id = oldid;
            DELETE FROM user_preferences WHERE user_id = oldid;
            DELETE FROM users WHERE id = oldid;

        END IF;
    END LOOP;

    CLOSE oldcur;

END//
DELIMITER ;

执行流程（从MySQL Workbench）¶

SET SQL_SAFE_UPDATES = 0;
CALL fixup_oldusers;
SET SQL_SAFE_UPDATES = 1;