Ticket #1140: rename-trac-users.sql

File rename-trac-users.sql, 3.4 KB (added by sleske, 4 years ago)

Rename / join user accounts.

Line 
1-- SQL to rename / merge trac users
2
3--create table rename_user (oldname varchar(1000), newname varchar(1000), keep_session int, primary key (oldname));
4-- Insert the users to be renamed into rename_user.
5-- oldname and newname should be self-explanatory.
6-- About keep_session:
7-- For renaming an account, set keep_session=1.
8-- For merging multiple accounts, use the same newname for all accounts,
9-- and set keep_session=0 for all but one account. The account with
10-- keep_session=1 is the one whose account information (name, email,
11-- OpenID etc.) will be kept for the merged acount.
12
13update attachment set author=(select r.newname from rename_user r where r.oldname=attachment.author)
14where attachment.author in (select r2.oldname from rename_user r2);
15update component set owner=(select r.newname from rename_user r where r.oldname=component.owner)
16where component.owner in (select r2.oldname from rename_user r2);
17update report set author=(select r.newname from rename_user r where r.oldname=report.author)
18where report.author in (select r2.oldname from rename_user r2);
19update revision set author=(select r.newname from rename_user r where r.oldname=revision.author)
20where revision.author in (select r2.oldname from rename_user r2);
21update ticket set reporter=(select r.newname from rename_user r where r.oldname=ticket.reporter)
22where ticket.reporter in (select r2.oldname from rename_user r2);
23update ticket set owner=(select r.newname from rename_user r where r.oldname=ticket.owner)
24where ticket.owner in (select r2.oldname from rename_user r2);
25update ticket_change set author=(select r.newname from rename_user r where r.oldname=ticket_change.author)
26where ticket_change.author in (select r2.oldname from rename_user r2);
27update ticket_change set oldvalue=(select r.newname from rename_user r where r.oldname=ticket_change.oldvalue)
28where ticket_change.oldvalue in (select r2.oldname from rename_user r2) and ticket_change.field in ('reporter', 'owner');
29update ticket_change set newvalue=(select r.newname from rename_user r where r.oldname=ticket_change.newvalue)
30where ticket_change.newvalue in (select r2.oldname from rename_user r2) and ticket_change.field in ('reporter', 'owner');
31update wiki set author=(select r.newname from rename_user r where r.oldname=wiki.author)
32where wiki.author in (select r2.oldname from rename_user r2);
33
34-- ----------------
35-- accounts to be deleted:
36
37delete from session_attribute where authenticated=1 and
38sid in (select r2.oldname from rename_user r2  where keep_session=0);
39delete from session where authenticated=1 and
40sid in (select r2.oldname from rename_user r2  where keep_session=0);
41delete from permission where
42username in (select r2.oldname from rename_user r2  where keep_session=0);
43
44-- ----------------
45
46-- account to be renamed:
47update session_attribute set sid=(select r.newname from rename_user r where r.oldname=session_attribute.sid)
48where session_attribute.sid in (select r2.oldname from rename_user r2 where keep_session=1) and session_attribute.authenticated=1;
49update session set sid=(select r.newname from rename_user r where r.oldname=session.sid)
50where session.sid in (select r2.oldname from rename_user r2  where keep_session=1) and session.authenticated=1;
51update permission set username=(select r.newname from rename_user r where r.oldname=permission.username)
52where permission.username in (select r2.oldname from rename_user r2  where keep_session=1);
53
54drop table rename_user;