Skip to content

Instantly share code, notes, and snippets.

@stwalkerster
Last active Aug 29, 2015
Embed
What would you like to do?
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment