Last active
August 21, 2018 18:31
-
-
Save tobiasmh/3e5aa4f7a37d9e834647 to your computer and use it in GitHub Desktop.
Migrate a Confluence users activity to another user
This file contains 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 | | |
+----------------------------------+------------------+----------------+ | |
| ff8080814094fe77014094ffd60c0115 | old_username | old_username | | |
| ff8080814094fe77014094ffd516005d | new_username | new_username | | |
+----------------------------------+------------------+----------------+ | |
-- Content | |
UPDATE CONTENT SET CREATOR = 'ff8080814094fe77014094ffd516005d' WHERE CREATOR = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE CONTENT SET USERNAME = 'ff8080814094fe77014094ffd516005d' WHERE USERNAME = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE CONTENT_LABEL SET OWNER = 'ff8080814094fe77014094ffd516005d' WHERE OWNER = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE LABEL SET OWNER = 'ff8080814094fe77014094ffd516005d' WHERE OWNER = 'ff8080814094fe77014094ffd60c0115'; | |
-- Permisssions | |
UPDATE CONTENT_PERM SET CREATOR = 'ff8080814094fe77014094ffd516005d' WHERE CREATOR = 'ff8080814094fe77014094ffd60c0115' | |
UPDATE SPACEPERMISSIONS SET CREATOR = 'ff8080814094fe77014094ffd516005d' WHERE CREATOR = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE SPACEPERMISSIONS SET LASTMODIFIER = 'ff8080814094fe77014094ffd516005d' WHERE LASTMODIFIER = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE SPACEPERMISSIONS SET PERMUSERNAME = 'ff8080814094fe77014094ffd516005d' WHERE PERMUSERNAME = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE CONTENT_PERM SET USERNAME = 'ff8080814094fe77014094ffd516005d' WHERE USERNAME = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE CONTENT_PERM SET CREATOR = 'ff8080814094fe77014094ffd516005d' WHERE CREATOR = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE CONTENT_PERM SET LASTMODIFIER = 'ff8080814094fe77014094ffd516005d' WHERE LASTMODIFIER 'ff8080814094fe77014094ffd60c0115'; | |
-- Last edited | |
UPDATE CONTENT SET LASTMODIFIER = 'ff8080814094fe77014094ffd516005d' WHERE LASTMODIFIER = 'ff8080814094fe77014094ffd60c0115'; | |
-- Likes | |
UPDATE LIKES SET USERNAME = 'ff8080814094fe77014094ffd516005d' WHERE USERNAME = 'ff8080814094fe77014094ffd60c0115'; | |
-- Mentions | |
UPDATE BODYCONTENT SET BODY = REPLACE(BODY, 'ff8080814094fe77014094ffd60c0115', 'ff8080814094fe77014094ffd516005d') WHERE BODY LIKE '%ff8080814094fe77014094ffd60c0115%'; | |
-- Space owners | |
UPDATE SPACES SET CREATOR = 'ff8080814094fe77014094ffd516005d' WHERE CREATOR = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE SPACES SET LASTMODIFIER = 'ff8080814094fe77014094ffd516005d' WHERE LASTMODIFIER = 'ff8080814094fe77014094ffd60c0115'; | |
-- Follows | |
UPDATE FOLLOW_CONNECTIONS SET FOLLOWEE = 'ff8080814094fe77014094ffd516005d' WHERE FOLLOWEE = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE FOLLOW_CONNECTIONS SET FOLLOWER = 'ff8080814094fe77014094ffd516005d' WHERE FOLLOWER = 'ff8080814094fe77014094ffd60c0115'; | |
-- Links | |
UPDATE LINKS SET CREATOR = 'ff8080814094fe77014094ffd516005d' WHERE CREATOR = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE LINKS SET LASTMODIFIER = 'ff8080814094fe77014094ffd516005d' WHERE LASTMODIFIER = 'ff8080814094fe77014094ffd60c0115'; | |
-- Remove login attempts for user to delete | |
DELETE FROM logininfo WHERE USERNAME = 'ff8080814094fe77014094ffd60c0115'; | |
-- Notifications | |
UPDATE NOTIFICATIONS SET CREATOR = 'ff8080814094fe77014094ffd516005d' WHERE CREATOR = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE NOTIFICATIONS SET LASTMODIFIER = 'ff8080814094fe77014094ffd516005d' WHERE LASTMODIFIER = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE NOTIFICATIONS SET USERNAME = 'ff8080814094fe77014094ffd516005d' WHERE USERNAME = 'ff8080814094fe77014094ffd60c0115'; | |
-- Page templates | |
UPDATE PAGETEMPLATES SET CREATOR = 'ff8080814094fe77014094ffd516005d' WHERE CREATOR = 'ff8080814094fe77014094ffd60c0115'; | |
UPDATE PAGETEMPLATES SET LASTMODIFIER = 'ff8080814094fe77014094ffd516005d' WHERE LASTMODIFIER = 'ff8080814094fe77014094ffd60c0115'; | |
-- Remove the user, so they can no longer be mentioned | |
DELETE FROM user_mapping WHERE user_key = 'ff8080814094fe77014094ffd60c0115'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Line 21 -- two questions -- first, isn't it redundant (or rather, isn't Line 26 redundant of 21)? Also, shouldn't there be a semicolon at the end of line 21?