-
Notifications
You must be signed in to change notification settings - Fork 1
/
renameusers_3.13.5.sql
132 lines (101 loc) · 8.47 KB
/
renameusers_3.13.5.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
-- PostgreSQL (PL/pgSQL) function that renames usernames in a JIRA 3.13.5 database.
--
-- The function assumes the existence of a 'renameusers' table, containing a list of usernames to rename. Eg. to rename 'john.smith' to 'jsmith':
--
-- jira_strutsmerge_3135=> \i renameusers.sql
-- jira_strutsmerge_3135=> CREATE TABLE renameusers (oldusername VARCHAR, newusername VARCHAR);
-- CREATE TABLE
-- jira_strutsmerge_3135=> INSERT INTO renameusers VALUES ('john.smith', 'jsmith');
-- INSERT 0 1
-- jira_strutsmerge_3135=> SELECT renameusers();
--
-- The renameusers function is idempotent.
--
-- Based on prior work by Srini Ramaswamy, Peter Wik and others on JRA-1549:
-- http://jira.atlassian.com/browse/JRA-1549?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel&focusedCommentId=181507&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_181507
--
-- Jeff Turner <[email protected]>, 2010-02-19
create or replace function renameusers() returns void as $$
DECLARE
rec RECORD;
BEGIN
update changegroup a set AUTHOR = (select b.newusername from renameusers b where b.oldusername = a.AUTHOR)
where a.AUTHOR in (select c.oldusername from renameusers c);
update columnlayout a set username = (select b.newusername from renameusers b where b.oldusername = a.username)
where a.username in (select c.oldusername from renameusers c);
update component a set lead = (select b.newusername from renameusers b where b.oldusername = a.lead)
where a.lead in (select c.oldusername from renameusers c);
update FAVOURITEASSOCIATIONS a set USERNAME = (select b.newusername from renameusers b where b.oldusername = a.USERNAME)
where a.USERNAME in (select c.oldusername from renameusers c);
update fileattachment a set author = (select b.newusername from renameusers b where b.oldusername = a.author)
where a.author in (select c.oldusername from renameusers c);
update filtersubscription a set username = (select b.newusername from renameusers b where b.oldusername = a.USERNAME)
where a.USERNAME in (select c.oldusername from renameusers c);
update jiraaction a set author = (select b.newusername from renameusers b where b.oldusername = a.author)
where a.author in (select c.oldusername from renameusers c);
update jiraaction a set updateauthor = (select b.newusername from renameusers b where b.oldusername = a.updateauthor)
where a.updateauthor in (select c.oldusername from renameusers c);
update jiraissue a set reporter = (select b.newusername from renameusers b where b.oldusername = a.reporter)
where a.reporter in (select c.oldusername from renameusers c);
update jiraissue a set assignee = (select b.newusername from renameusers b where b.oldusername = a.assignee)
where a.assignee in (select c.oldusername from renameusers c);
update jiraworkflows a set creatorname=(select b.newusername from renameusers b where b.oldusername = a.creatorname)
where a.creatorname in (select c.oldusername from renameusers c);
update mailserver a set mailusername = (select b.newusername from renameusers b where b.oldusername = a.MAILUSERNAME)
where a.mailusername in (select c.oldusername from renameusers c);
update membershipbase a set user_name = (select b.newusername from renameusers b where b.oldusername = a.user_name)
where a.user_name in (select c.oldusername from renameusers c);
update os_currentstep a set owner = (select b.newusername from renameusers b where b.oldusername = a.owner)
where a.owner in (select c.oldusername from renameusers c);
update os_currentstep a set caller = (select b.newusername from renameusers b where b.oldusername = a.caller)
where a.caller in (select c.oldusername from renameusers c);
update os_historystep a set owner = (select b.newusername from renameusers b where b.oldusername = a.owner)
where a.owner in (select c.oldusername from renameusers c);
update os_historystep a set caller = (select b.newusername from renameusers b where b.oldusername = a.caller)
where a.caller in (select c.oldusername from renameusers c);
update portalpage a set username = (select b.newusername from renameusers b where b.oldusername = a.username)
where a.username in (select c.oldusername from renameusers c);
update project a set lead = (select b.newusername from renameusers b where b.oldusername = a.lead)
where a.lead in (select c.oldusername from renameusers c);
update PROJECTROLEACTOR a set ROLETYPEPARAMETER = (select b.newusername from renameusers b where b.oldusername = a.ROLETYPEPARAMETER)
where a.ROLETYPEPARAMETER in (select c.oldusername from renameusers c);
update SCHEMEPERMISSIONS a set PERM_PARAMETER = (select b.newusername from renameusers b where b.oldusername = a.PERM_PARAMETER)
where a.PERM_PARAMETER in (select c.oldusername from renameusers c);
update searchrequest a set authorname = (select b.newusername from renameusers b where b.oldusername = a.authorname)
where a.authorname in (select c.oldusername from renameusers c);
update searchrequest a set username = (select b.newusername from renameusers b where b.oldusername = a.username)
where a.username in (select c.oldusername from renameusers c);
update userassociation a set source_name = (select b.newusername from renameusers b where b.oldusername = a.source_name) where a.source_name in (select c.oldusername from renameusers c);
-- At one stage the above userassociation update was failing, and I had to replace it with this snippet to see which username was duplicated:
-- for rec in select * from renameusers LOOP
-- RAISE NOTICE 'Renaming % to %', rec.oldusername,rec.newusername;
-- BEGIN
-- update userassociation a set source_name = rec.newusername where source_name=rec.oldusername;
-- EXCEPTION WHEN unique_violation THEN
-- RAISE NOTICE 'Caught error; continuing';
-- end;
-- end loop;
--
update userbase a set username = (select b.newusername from renameusers b where b.oldusername = a.username) where a.username in (select c.oldusername from renameusers c);
update WORKLOG a set AUTHOR = (select b.newusername from renameusers b where b.oldusername = a.AUTHOR)
where a.AUTHOR in (select c.oldusername from renameusers c);
update WORKLOG a set UPDATEAUTHOR = (select b.newusername from renameusers b where b.oldusername = a.UPDATEAUTHOR)
where a.UPDATEAUTHOR in (select c.oldusername from renameusers c);
-- FIXME: We should restrict replacements to custom field types that are known to store usernames.
update CUSTOMFIELDVALUE a set STRINGVALUE=(select b.newusername from renameusers b where b.oldusername = a.STRINGVALUE)
where a.STRINGVALUE in (select c.oldusername from renameusers c);
update changeitem set OLDVALUE = (select newusername from renameusers where oldusername = OLDVALUE) where OLDVALUE in (select oldusername from renameusers) and field~*'assignee';
update changeitem set NEWVALUE = (select newusername from renameusers where oldusername = NEWVALUE) where NEWVALUE in (select oldusername from renameusers) and field~*'assignee';
update changeitem set OLDSTRING = (select newusername from renameusers where oldusername = OLDSTRING) where OLDSTRING in (select oldusername from renameusers) and field~*'assignee';
update changeitem set NEWSTRING = (select newusername from renameusers where oldusername = NEWSTRING) where NEWSTRING in (select oldusername from renameusers) and field~*'assignee';
update changeitem set OLDVALUE = (select newusername from renameusers where oldusername = OLDVALUE) where OLDVALUE in (select oldusername from renameusers) and field~*'reporter';
update changeitem set NEWVALUE = (select newusername from renameusers where oldusername = NEWVALUE) where NEWVALUE in (select oldusername from renameusers) and field~*'reporter';
update changeitem set OLDSTRING = (select newusername from renameusers where oldusername = OLDSTRING) where OLDSTRING in (select oldusername from renameusers) and field~*'reporter';
update changeitem set NEWSTRING = (select newusername from renameusers where oldusername = NEWSTRING) where NEWSTRING in (select oldusername from renameusers) and field~*'reporter';
-- Delete the duplicate userbase entries and group ownerships the above queries create
delete from userbase u where exists (select * from userbase where username=u.username and id>u.id) ;
delete from membershipbase u where exists (select * from membershipbase where user_name=u.user_name and group_name=u.group_name and id>u.id) ;
-- Note: if both users had a custom dashboard, there will be two dashboards called 'Dashboard' with the same sequence, which JIRA might not like.
-- Similarly there may be duplicate favouriteassociations
END;
$$ language plpgsql;