Ticket #1140: trac-user-name-cleanup.sql

File trac-user-name-cleanup.sql, 9.7 KB (added by sleske, 4 years ago)

Delete unused accounts, create list of renamings.

Line 
1/*
2This will clean up the Trac SIDs (user names) in Navit's Trac.
3Goals:
4* clean up the SIDs consisting of an URI
5* join SIDs based on the same OpenID URI
6
7See http://trac.navit-project.org/ticket/1140 for details.
8
9To use:
10* first run this file (.read trac-user-name-cleanup.sql), to build the
11  renaming table
12* then run rename-users-trac.sql , which performs the rename
13
14*/
15
16-- Preliminary cleanup:
17-- Delete user accounts that never wrote anything (ticket, comment etc.)
18-- and have not logged in since January 2015.
19
20drop table if exists sid_with_data;
21create table sid_with_data (sid varchar primary key);
22
23insert into sid_with_data
24SELECT author from attachment
25UNION SELECT author from attachment
26UNION SELECT owner from component
27UNION SELECT username from permission
28UNION SELECT author from report
29UNION SELECT author from revision
30UNION SELECT reporter from ticket
31UNION SELECT owner from ticket
32UNION SELECT author from ticket_change
33UNION SELECT oldvalue from ticket_change where  field in ('reporter', 'owner')
34UNION SELECT newvalue from ticket_change where  field in ('reporter', 'owner')
35UNION SELECT author from wiki;
36delete from sid_with_data where sid is NULL;
37
38delete from session where authenticated=1 and sid not in
39(select sid from sid_with_data) and last_visit<1420066800;
40delete from session_attribute where authenticated=1 and sid not in
41(select sid from session where authenticated=1);
42
43-- Create list of SIDs that look like a URI, and simplify them.
44-- For the Navit wiki and launchpad.net extract the user name from the URI.
45drop table if exists httpsids;
46PRAGMA case_sensitive_like = TRUE;
47create table httpsids (sid varchar primary key, newsid varchar);
48insert into httpsids (sid, newsid) select sid, replace(sid, 'http://wiki.navit-project.org/index.php/user:', '')
49from session where sid like 'http://wiki.navit-project.org/index.php/user:%' and authenticated=1;
50insert into httpsids (sid, newsid) select sid, replace(sid, 'https://wiki.navit-project.org/index.php/user:', '')
51from session where sid like 'https://wiki.navit-project.org/index.php/user:%' and authenticated=1;
52insert into httpsids (sid, newsid) select sid, replace(sid, 'http://wiki-new.navit-project.org/index.php/user:', '')
53from session where sid like 'http://wiki-new.navit-project.org/index.php/user:%' and authenticated=1;
54insert into httpsids (sid, newsid) select sid, replace(sid, 'https://wiki-new.navit-project.org/index.php/user:', '')
55from session where sid like 'https://wiki-new.navit-project.org/index.php/user:%' and authenticated=1;
56insert into httpsids (sid, newsid) select sid, replace(sid, 'http://wiki.test.navit-project.org/index.php/user:', '')
57from session where sid like 'http://wiki.test.navit-project.org/index.php/user:%' and authenticated=1;
58insert into httpsids (sid, newsid) select sid, replace(sid, 'https://wiki.test.navit-project.org/index.php/user:', '')
59from session where sid like 'https://wiki.test.navit-project.org/index.php/user:%' and authenticated=1;
60
61insert into httpsids (sid, newsid) select sid, replace(sid, 'http://wiki.navit-project.org/index.php/User:', '')
62from session where sid like 'http://wiki.navit-project.org/index.php/User:%' and authenticated=1;
63insert into httpsids (sid, newsid) select sid, replace(sid, 'https://wiki.navit-project.org/index.php/User:', '')
64from session where sid like 'https://wiki.navit-project.org/index.php/User:%' and authenticated=1;
65insert into httpsids (sid, newsid) select sid, replace(sid, 'http://wiki-new.navit-project.org/index.php/User:', '')
66from session where sid like 'http://wiki-new.navit-project.org/index.php/User:%' and authenticated=1;
67insert into httpsids (sid, newsid) select sid, replace(sid, 'https://wiki-new.navit-project.org/index.php/User:', '')
68from session where sid like 'https://wiki-new.navit-project.org/index.php/User:%' and authenticated=1;
69insert into httpsids (sid, newsid) select sid, replace(sid, 'http://wiki.test.navit-project.org/index.php/User:', '')
70from session where sid like 'http://wiki.test.navit-project.org/index.php/User:%' and authenticated=1;
71insert into httpsids (sid, newsid) select sid, replace(sid, 'https://wiki.test.navit-project.org/index.php/User:', '')
72from session where sid like 'https://wiki.test.navit-project.org/index.php/User:%' and authenticated=1;
73
74insert into httpsids (sid, newsid) select sid, replace(sid, 'https://launchpad.net/~', '')
75from session where sid like 'https://launchpad.net/~%' and authenticated=1;
76
77insert into httpsids (sid, newsid) select sid, replace(sid, 'http://', '')
78from session where sid like 'http://%' and authenticated=1
79and sid not in (select sid from httpsids);
80insert into httpsids (sid, newsid) select sid, replace(sid, 'https://', '')
81from session where sid like 'https://%' and authenticated=1
82and sid not in (select sid from httpsids);
83
84PRAGMA case_sensitive_like = FALSE;
85
86update httpsids set newsid=rtrim(newsid, '/');
87
88-- Find accounts with identical or equivalent OpenID URI.
89-- URI is taken from session_attribute or from the SID.
90drop table if exists oidurls;
91create table oidurls (sid varchar primary key, normalurl varchar);
92insert into oidurls (sid, normalurl) select sid, value from session_attribute
93where name='openid_session_identity_url_data' and authenticated=1;
94insert into oidurls (sid, normalurl) select sid, sid from session
95where sid  like 'http%' and sid not in (select sid from oidurls);
96
97-- Normalize URIs, to make equivalent URIs identical.
98update oidurls set normalurl=lower(normalurl);
99update oidurls set normalurl=replace(normalurl,'https','http');
100update oidurls set normalurl=replace(normalurl,'wiki-new.navit-project.org','wiki.navit-project.org');
101update oidurls set normalurl=replace(normalurl,'wiki.test.navit-project.org','wiki.navit-project.org');
102-- ----------------------------
103-- Check for conflicting SIDs because of the simplification.
104-- Idea: For all SIDs which are mapped to the same SID, find the
105-- corresponding OpenID URI (if available).
106-- Result: new SID, OpenID URI for old SID, old SID
107/*
108select httpsids.newsid, coalesce(oidurls.normalurl, '-NO URI-'), httpsids.sid from httpsids
109left join oidurls on httpsids.sid=oidurls.sid where httpsids.sid in  (
110  select sid from httpsids where newsid in (select  newsid from httpsids group by newsid having count(*)>1 )
111);
112*/
113-- -> Result: No conflicts :-).
114
115
116drop table if exists rename_user;
117create table rename_user (oldname varchar(1000), newname varchar(1000), keep_session int, primary key (oldname));
118
119-- Join SIDs with equivalent OpenID URIs.
120-- * group by "normalurl"
121-- * find max(last_visit) for each group
122-- * find SID corresponding to max(last_visit)
123insert into rename_user (oldname, newname, keep_session)
124select o.sid, (select s3.sid from session s3 where s3.last_visit=(
125    select max(last_visit) from oidurls o2 join session s2
126    on o2.sid=s2.sid and s2.authenticated=1 where o2.normalurl=o.normalurl
127    )) as newsid, 0
128from oidurls o;
129-- We keep the SID with latest login date. That is the SID that does not
130-- change, so we don't need to do anything with it.
131delete from rename_user where oldname=newname;
132
133-- Simplify SIDs as described in httpsids
134
135-- For existing rename records change "newname" as appropriate.
136update rename_user set newname=coalesce(
137  (select h.newsid from httpsids h where h.sid=rename_user.newname),
138  rename_user.newname);
139
140-- If there is no rename record yet, insert one.
141insert into rename_user (oldname, newname, keep_session)
142select h.sid, h.newsid,1 from httpsids h
143where h.sid not in (select oldname from rename_user);
144
145-- Fix conflicts with existing SIDs
146
147-- Build table of conflicting SID
148-- "Conflicting" means: SID exists in session table and in rename_user.newname.
149drop table if exists conflict_sid;
150create table conflict_sid (sid varchar primary key, hasurl int);
151insert into conflict_sid
152select s.sid, exists (select * from oidurls o where o.sid=s.sid)
153from session s where s.authenticated=1
154and exists (select * from rename_user ru where ru.newname=s.sid and ru.keep_session=1)
155and not exists (select * from rename_user ru2 where ru2.oldname=s.sid);
156
157-- Delete SIDs without an OpenID URI (those cannot login anyway)
158with nourl as (
159  select cs.sid from conflict_sid cs where cs.hasurl=0
160) insert into rename_user (oldname,newname,keep_session)  select sid,sid,0 from nourl;
161 
162-- If OpenID URI is available (which will then be different for the two conflicting SIDs)
163-- -> rename SID with older last_visit (-> -2-)
164
165-- last_visit older for existing SID -> rename it, thus new record in rename_user
166insert into rename_user (oldname, newname, keep_session)
167select sid, sid||' -2-',1 from (
168  with hasurl as (
169    select cs.sid from conflict_sid cs where cs.hasurl=1
170  ) select h.sid, s.last_visit lv_exist, ru.oldname, s2.last_visit lv_neu from hasurl h
171  join session s on s.sid=h.sid and s.authenticated=1
172  join rename_user ru on ru.newname=h.sid and ru.keep_session=1
173  join session s2 on s2.authenticated=1 and s2.sid=ru.oldname
174) bla where lv_exist<lv_neu;
175
176-- last_visit older for new SID (for rename) -> rename it, thus change record in rename_user
177update rename_user set newname=newname||' -2-' where oldname in (
178  select oldname from (
179    with hasurl as (
180      select cs.sid from conflict_sid cs where cs.hasurl=1
181    ) select h.sid, s.last_visit lv_exist, ru.oldname, s2.last_visit lv_neu from hasurl h
182    join session s on s.sid=h.sid and s.authenticated=1
183    join rename_user ru on ru.newname=h.sid
184    join session s2 on s2.authenticated=1 and s2.sid=ru.oldname
185  ) bla where lv_exist>=lv_neu);
186
187-- Special cases for cp15 and sleske
188insert into rename_user (oldname, newname, keep_session) values
189('sleske', 'sleske', 0), ('sleske (2)', 'sleske', 1);
190update rename_user set newname='cp15', keep_session=0 where oldname in ('cp15','Cp15');
191
192-- Cleanup
193drop table sid_with_data;
194drop table httpsids;
195drop table oidurls;
196drop table conflict_sid;