rename.sql

drop procedure if exists mergeToOld;

delimiter $$

create procedure mergeToOld (
	in oldid int,
    in newid int,
    in processUserId int,
    in logComment varchar(255)
    )
BEGIN
    declare oldname varchar(255);
    declare newname varchar(255);
    declare newemail varchar(255);
    declare newpassword varchar(255);
    declare newstatus varchar(255);
    declare newonwikiname varchar(255);
    declare newsig varchar(4096);
    declare newla datetime;
    declare newident int(1);
    declare newtempl int(11);
    declare newabortpref int(11);
    declare newesig blob;
    declare newort varchar(45);
    declare newors varchar(45);
    declare newoat varchar(45);
    declare newoas varchar(45);
    declare newoic blob;
    
    declare temp int(11);
    
    -- get the new username
    select username
    into oldname
    from user
    where id = oldid
    for update;

	-- grab the information from the new user
    select username, email, password, status, onwikiname, welcome_sig, lastactive, identified, welcome_template, abortpref, emailsig,   oauthrequesttoken, oauthrequestsecret, oauthaccesstoken, oauthaccesssecret, oauthidentitycache
    into   newname,  newemail, newpassword, newstatus, newonwikiname, newsig, newla, newident, newtempl,         newabortpref, newesig, newort, newors, newoat, newoas, newoic
    from user 
    where id = newid
    for update;
    
    -- delete the new user
    delete from user 
    where id = newid 
    limit 1;
    
    select row_count() into temp from dual;
    if temp <> 1 then
		insert into applicationlog (source, message, stack) values ('mergeToOld', CONCAT('Expected to delete new user (', coalesce(newid, '(null)'), '), but affected rows is ', coalesce(temp, '(null)')), '');
		signal sqlstate '45000' set message_text = 'Expected to delete new user';
	end if;
    
    -- update the old user to have the same details as the new user
    update user 
    set 
		username = newname, 
        email = newemail, 
        password = newpassword, 
        status = newstatus, 
        onwikiname = newonwikiname, 
        welcome_sig = newsig,
        lastactive = newla,
        identified = newident,
        welcome_template = newtempl, 
        abortpref = newabortpref, 
        emailsig = newesig,
        oauthrequesttoken = newort, 
        oauthrequestsecret = newors, 
        oauthaccesstoken = newoat, 
        oauthaccesssecret = newoas,
        oauthidentitycache = newoic
	where id = oldid
    limit 1;
	
    select row_count() into temp from dual;
    if temp <> 1 then
		insert into applicationlog (source, message, stack) values ('mergeToOld', CONCAT('Expected to update old user (', coalesce(oldid, '(null)'), '), but affected rows is ', coalesce(temp, '(null)')), '');
		signal sqlstate '45000' set message_text = 'Expected to update old user.';
	end if;
    
    -- move the approval of the new user to the old user
    update log set objectid = oldid where objectid = newid and action = 'Approved' and objecttype = 'User';
    
    -- add a log entry showing the user has been renamed
    insert into log (objectid, objecttype, user, action, timestamp, comment) values (oldid, 'User', processUserId, 'Renamed', current_timestamp(), logComment);
end $$

delimiter ;

set transaction isolation level serializable;
start transaction;

call mergeToOld(684, 1047, 7, 'a:2:{s:3:"old";s:6:"Tanner";s:3:"new";s:5:"frood";}');
select * from applicationlog;

rollback;