Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement