-
-
Save NicolasEsteves/ca481a6a4aaa63855a2b to your computer and use it in GitHub Desktop.
Migrate a Confluence users activity to another user
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- WARNING | |
| -- WARNING THIS IS PROBABLY INCOMPLETE AND MAY BREAK CONFLUENCE. USE AT YOUR OWN RISK | |
| -- WARNING | |
| -- Migrate a Confluence users activity to another user | |
| SELECT * FROM user_mapping WHERE lower_username='old_username' OR lower_username='new_username'; | |
| +----------------------------------+------------------+----------------+ | |
| | user_key | username | lower_username | | |
| +----------------------------------+------------------+----------------+ | |
| | f2848ad85089663701508e701848000e | old_username | old_username | | |
| | ff8080814a032d84014a032da2db0022 | new_username | new_username | | |
| +----------------------------------+------------------+----------------+ | |
| -- Content | |
| UPDATE CONTENT SET CREATOR = 'ff8080814a032d84014a032da2db0022' WHERE CREATOR = 'f2848ad85089663701508e701848000e'; | |
| UPDATE CONTENT SET USERNAME = 'ff8080814a032d84014a032da2db0022' WHERE USERNAME = 'f2848ad85089663701508e701848000e'; | |
| UPDATE CONTENT_LABEL SET OWNER = 'ff8080814a032d84014a032da2db0022' WHERE OWNER = 'f2848ad85089663701508e701848000e'; | |
| UPDATE LABEL SET OWNER = 'ff8080814a032d84014a032da2db0022' WHERE OWNER = 'f2848ad85089663701508e701848000e'; | |
| -- Permisssions | |
| UPDATE CONTENT_PERM SET CREATOR = 'ff8080814a032d84014a032da2db0022' WHERE CREATOR = 'f2848ad85089663701508e701848000e'; | |
| UPDATE SPACEPERMISSIONS SET CREATOR = 'ff8080814a032d84014a032da2db0022' WHERE CREATOR = 'f2848ad85089663701508e701848000e'; | |
| UPDATE SPACEPERMISSIONS SET LASTMODIFIER = 'ff8080814a032d84014a032da2db0022' WHERE LASTMODIFIER = 'f2848ad85089663701508e701848000e'; | |
| UPDATE SPACEPERMISSIONS SET PERMUSERNAME = 'ff8080814a032d84014a032da2db0022' WHERE PERMUSERNAME = 'f2848ad85089663701508e701848000e'; | |
| UPDATE CONTENT_PERM SET USERNAME = 'ff8080814a032d84014a032da2db0022' WHERE USERNAME = 'f2848ad85089663701508e701848000e'; | |
| UPDATE CONTENT_PERM SET CREATOR = 'ff8080814a032d84014a032da2db0022' WHERE CREATOR = 'f2848ad85089663701508e701848000e'; | |
| UPDATE CONTENT_PERM SET LASTMODIFIER = 'ff8080814a032d84014a032da2db0022' WHERE LASTMODIFIER = 'f2848ad85089663701508e701848000e'; | |
| -- Last edited | |
| UPDATE CONTENT SET LASTMODIFIER = 'ff8080814a032d84014a032da2db0022' WHERE LASTMODIFIER = 'f2848ad85089663701508e701848000e'; | |
| -- Likes | |
| UPDATE LIKES SET USERNAME = 'ff8080814a032d84014a032da2db0022' WHERE USERNAME = 'f2848ad85089663701508e701848000e'; | |
| -- Mentions | |
| UPDATE BODYCONTENT SET BODY = REPLACE(BODY, 'f2848ad85089663701508e701848000e', 'ff8080814a032d84014a032da2db0022') WHERE BODY LIKE '%f2848ad85089663701508e701848000e%'; | |
| -- Space owners | |
| UPDATE SPACES SET CREATOR = 'ff8080814a032d84014a032da2db0022' WHERE CREATOR = 'f2848ad85089663701508e701848000e'; | |
| UPDATE SPACES SET LASTMODIFIER = 'ff8080814a032d84014a032da2db0022' WHERE LASTMODIFIER = 'f2848ad85089663701508e701848000e'; | |
| -- Follows | |
| UPDATE FOLLOW_CONNECTIONS SET FOLLOWEE = 'ff8080814a032d84014a032da2db0022' WHERE FOLLOWEE = 'f2848ad85089663701508e701848000e'; | |
| UPDATE FOLLOW_CONNECTIONS SET FOLLOWER = 'ff8080814a032d84014a032da2db0022' WHERE FOLLOWER = 'f2848ad85089663701508e701848000e'; | |
| -- Links | |
| UPDATE LINKS SET CREATOR = 'ff8080814a032d84014a032da2db0022' WHERE CREATOR = 'f2848ad85089663701508e701848000e'; | |
| UPDATE LINKS SET LASTMODIFIER = 'ff8080814a032d84014a032da2db0022' WHERE LASTMODIFIER = 'f2848ad85089663701508e701848000e'; | |
| -- Remove login attempts for user to delete | |
| DELETE FROM logininfo WHERE USERNAME = 'f2848ad85089663701508e701848000e'; | |
| -- Notifications | |
| UPDATE NOTIFICATIONS SET CREATOR = 'ff8080814a032d84014a032da2db0022' WHERE CREATOR = 'f2848ad85089663701508e701848000e'; | |
| UPDATE NOTIFICATIONS SET LASTMODIFIER = 'ff8080814a032d84014a032da2db0022' WHERE LASTMODIFIER = 'f2848ad85089663701508e701848000e'; | |
| UPDATE NOTIFICATIONS SET USERNAME = 'ff8080814a032d84014a032da2db0022' WHERE USERNAME = 'f2848ad85089663701508e701848000e'; | |
| -- Page templates | |
| UPDATE PAGETEMPLATES SET CREATOR = 'ff8080814a032d84014a032da2db0022' WHERE CREATOR = 'f2848ad85089663701508e701848000e'; | |
| UPDATE PAGETEMPLATES SET LASTMODIFIER = 'ff8080814a032d84014a032da2db0022' WHERE LASTMODIFIER = 'f2848ad85089663701508e701848000e'; | |
| -- Old user | |
| DELETE FROM cwd_user_attribute WHERE user_id in (SELECT cwd_user.id FROM user_mapping, cwd_user WHERE user_mapping.username = cwd_user.lower_user_name AND user_mapping.user_key = "f2848ad85089663701508e701848000e"); | |
| DELETE FROM cwd_membership WHERE child_user_id in (SELECT cwd_user.id FROM user_mapping, cwd_user WHERE user_mapping.username = cwd_user.lower_user_name AND user_mapping.user_key = "f2848ad85089663701508e701848000e"); | |
| DELETE FROM cwd_user WHERE id in (SELECT * FROM (SELECT cwd_user.id FROM user_mapping, cwd_user WHERE user_mapping.username = cwd_user.lower_user_name AND user_mapping.user_key = "f2848ad85089663701508e701848000e") as tmp); | |
| DELETE FROM user_mapping WHERE user_key = 'f2848ad85089663701508e701848000e'; | |
| -- Cleaning residual duplicates | |
| DELETE FROM CONTENT WHERE contentid IN | |
| (SELECT * FROM | |
| (SELECT DISTINCT c1.contentid | |
| FROM CONTENT c1 | |
| JOIN CONTENT c2 ON c1.username = c2.username | |
| WHERE c1.contenttype = 'USERINFO' | |
| AND c2.contenttype = 'USERINFO' | |
| AND c1.prevver is null | |
| AND c2.prevver is null | |
| AND c1.contentid > c2.contentid) | |
| as tmp2); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment