Confluence user renaming PostgreSQL stored procedure.

confluence-rename-users.sql

DECLARE vdir integer;

BEGIN

-- BEGIN;
-- ^not needed for stored procedure

CREATE TABLE IF NOT EXISTS usermigration
(
  oldusername character varying(255) NOT NULL,
  newusername character varying(255) NOT NULL,
  CONSTRAINT pk_oldusername PRIMARY KEY (oldusername)
)
WITH (
  OIDS=FALSE
);

update attachments 
	set creator = newusername from usermigration u 
	where creator = u.oldusername;
 
update attachments
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;
 
update content
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update content
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;
 
update content
	set username = newusername from usermigration u
	where username = u.oldusername;
 
update content_label
	set owner = newusername from usermigration u
	where owner = u.oldusername;
 
update content_perm
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update content_perm
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;
 
update content_perm
	set username = newusername from usermigration u
	where username = u.oldusername;
 
update contentlock
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update contentlock
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;
 
update cwd_user
	set lower_user_name = lower(newusername) from usermigration u
	where lower_user_name = lower(u.oldusername);
 
update cwd_user
	set user_name = newusername from usermigration u
	where user_name = u.oldusername;
 
update extrnlnks
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update extrnlnks
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;
 
update follow_connections
	set followee = newusername from usermigration u
	where followee = u.oldusername;
 
update follow_connections
	set follower = newusername from usermigration u
	where follower = u.oldusername;
 
update label
	set owner = newusername from usermigration u
	where owner = u.oldusername;
 
update links
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update links
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;
 
update notifications
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update notifications
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;
 
update notifications
	set username = newusername from usermigration u
	where username = u.oldusername;
 
update pagetemplates
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update pagetemplates
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;
 
update remembermetoken
	set username = newusername from usermigration u
	where username = u.oldusername;
 
update spacegroups
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update spacegroups
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;
 
update spacepermissions
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update spacepermissions
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;
 
update spacepermissions
	set permusername = newusername from usermigration u
	where permusername = u.oldusername;
 
-- just in case the newuser namaged to create a new space before we did the migration
-- we will rename this one: ~username => ~~username
update spaces
	set spacename = '~~' || spacename from usermigration u
	where spacename = '~' || u.newusername;

-- rename the old space to the new one (now it does to exist)
update spaces
	set spacename = '~' || newusername from usermigration u
	where spacename  = '~' || u.oldusername;

update spaces
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update spaces
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;

 
update trackbacklinks
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update trackbacklinks
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;

-- if new user already exists we remove these entries
delete from os_propertyentry where entity_name in (select 'CWD_' || newusername from usermigration);

update os_propertyentry
	set entity_name = 'CWD_' || newusername from usermigration u
	where entity_name = 'CWD_' || u.oldusername;

update spaces
	set spacekey = '~' || newusername from usermigration u
	where spacekey = '~' || u.oldusername;
  
update bandana
	set bandanacontext = '~' || newusername from usermigration u
	where bandanacontext = '~' || u.oldusername;

update bandana
	set bandanavalue = replace(bandanavalue, '~' || oldusername, '~' || newusername)
	from usermigration u;

-- that's risky but looks necessary, letg just hope nobody has a too short username
update bodycontent 
	set body = replace(body, '~' || oldusername, '~' || newusername)
	from usermigration u;
	
-- now we delete migrated users, we do not want to risk running the migration twice because it will remove os_propertyentry records.

delete from usermigration;

-- COMIT;
-- ^not needed for stored procedure

RETURN 1;

END;