blob: 86b209ab243d87aaea597c3531cd49cca107286e [file] [log] [blame]
ddkilzer@apple.com8040bb02017-03-21 16:27:49 +00001#!/usr/bin/perl -T
2# This Source Code Form is subject to the terms of the Mozilla Public
3# License, v. 2.0. If a copy of the MPL was not distributed with this
4# file, You can obtain one at http://mozilla.org/MPL/2.0/.
ddkilzer@apple.comf3615fc2009-07-03 02:13:41 +00005#
ddkilzer@apple.com8040bb02017-03-21 16:27:49 +00006# This Source Code Form is "Incompatible With Secondary Licenses", as
7# defined by the Mozilla Public License, v. 2.0.
ddkilzer@apple.comf3615fc2009-07-03 02:13:41 +00008
ddkilzer@apple.com8040bb02017-03-21 16:27:49 +00009use 5.10.1;
ddkilzer@apple.comf3615fc2009-07-03 02:13:41 +000010use strict;
ddkilzer@apple.com8040bb02017-03-21 16:27:49 +000011use warnings;
ddkilzer@apple.comf3615fc2009-07-03 02:13:41 +000012
13=head1 NAME
14
15merge-users.pl - Merge two user accounts.
16
17=head1 SYNOPSIS
18
19 This script moves activity from one user account to another.
20 Specify the two accounts on the command line, e.g.:
21
22 ./merge-users.pl old_account@foo.com new_account@bar.com
23 or:
24 ./merge-users.pl id:old_userid id:new_userid
25 or:
26 ./merge-users.pl id:old_userid new_account@bar.com
27
28 Notes: - the new account must already exist.
29 - the id:old_userid syntax permits you to migrate
30 activity from a deleted account to an existing one.
31
32=cut
33
ddkilzer@apple.com097da082009-07-03 02:14:25 +000034use lib qw(. lib);
ddkilzer@apple.comf3615fc2009-07-03 02:13:41 +000035
36use Bugzilla;
37use Bugzilla::Constants;
38use Bugzilla::Util;
ddkilzer@apple.com097da082009-07-03 02:14:25 +000039use Bugzilla::User;
ddkilzer@apple.comf3615fc2009-07-03 02:13:41 +000040
41use Getopt::Long;
42use Pod::Usage;
43
44my $dbh = Bugzilla->dbh;
45
46# Display the help if called with --help or -?.
47my $help = 0;
48my $result = GetOptions("help|?" => \$help);
49pod2usage(0) if $help;
50
51
52# Make sure accounts were specified on the command line and exist.
53my $old = $ARGV[0] || die "You must specify an old user account.\n";
54my $old_id;
55if ($old =~ /^id:(\d+)$/) {
56 # As the old user account may be a deleted one, we don't
57 # check whether this user ID is valid or not.
58 # If it never existed, no damage will be done.
59 $old_id = $1;
60}
61else {
62 trick_taint($old);
63 $old_id = $dbh->selectrow_array('SELECT userid FROM profiles
64 WHERE login_name = ?',
65 undef, $old);
66}
67if ($old_id) {
68 print "OK, old user account $old found; user ID: $old_id.\n";
69}
70else {
71 die "The old user account $old does not exist.\n";
72}
73
74my $new = $ARGV[1] || die "You must specify a new user account.\n";
75my $new_id;
76if ($new =~ /^id:(\d+)$/) {
77 $new_id = $1;
78 # Make sure this user ID exists.
79 $new_id = $dbh->selectrow_array('SELECT userid FROM profiles
80 WHERE userid = ?',
81 undef, $new_id);
82}
83else {
84 trick_taint($new);
85 $new_id = $dbh->selectrow_array('SELECT userid FROM profiles
86 WHERE login_name = ?',
87 undef, $new);
88}
89if ($new_id) {
90 print "OK, new user account $new found; user ID: $new_id.\n";
91}
92else {
93 die "The new user account $new does not exist.\n";
94}
95
96# Make sure the old and new accounts are different.
97if ($old_id == $new_id) {
98 die "\nBoth accounts are identical. There is nothing to migrate.\n";
99}
100
101
102# A list of tables and columns to be changed:
103# - keys of the hash are table names to be locked/altered;
104# - values of the hash contain column names to be updated
105# as well as the columns they depend on:
106# = each array is of the form:
107# ['foo1 bar11 bar12 bar13', 'foo2 bar21 bar22', 'foo3 bar31 bar32']
108# where fooN is the column to update, and barN1, barN2, ... are
109# the columns to take into account to avoid duplicated entries.
110# Note that the barNM columns are optional.
ddkilzer@apple.com57772842014-10-16 16:00:58 +0000111#
112# We set the tables that require custom stuff (multiple columns to check)
113# here, but the simple stuff is all handled below by bz_get_related_fks.
114my %changes = (
ddkilzer@apple.comf3615fc2009-07-03 02:13:41 +0000115 cc => ['who bug_id'],
ddkilzer@apple.comf3615fc2009-07-03 02:13:41 +0000116 # Tables affecting global behavior / other users.
ddkilzer@apple.comf3615fc2009-07-03 02:13:41 +0000117 component_cc => ['user_id component_id'],
ddkilzer@apple.comf3615fc2009-07-03 02:13:41 +0000118 watch => ['watcher watched', 'watched watcher'],
119 # Tables affecting the user directly.
120 namedqueries => ['userid name'],
121 namedqueries_link_in_footer => ['user_id namedquery_id'],
122 user_group_map => ['user_id group_id isbless grant_type'],
123 email_setting => ['user_id relationship event'],
124 profile_setting => ['user_id setting_name'],
ddkilzer@apple.comf3615fc2009-07-03 02:13:41 +0000125
126 # Only do it if mailto_type = 0, i.e is pointing to a user account!
127 # This requires to be done separately due to this condition.
128 whine_schedules => [], # ['mailto'],
ddkilzer@apple.com57772842014-10-16 16:00:58 +0000129);
ddkilzer@apple.comf3615fc2009-07-03 02:13:41 +0000130
ddkilzer@apple.com57772842014-10-16 16:00:58 +0000131my $userid_fks = $dbh->bz_get_related_fks('profiles', 'userid');
132foreach my $item (@$userid_fks) {
133 my ($table, $column) = @$item;
134 $changes{$table} ||= [];
135 push(@{ $changes{$table} }, $column);
136}
137
138# Delete all old records for these tables; no migration.
139foreach my $table (qw(logincookies tokens profiles)) {
140 $changes{$table} = [];
141}
ddkilzer@apple.comf3615fc2009-07-03 02:13:41 +0000142
ddkilzer@apple.com097da082009-07-03 02:14:25 +0000143# Start the transaction
144$dbh->bz_start_transaction();
ddkilzer@apple.comf3615fc2009-07-03 02:13:41 +0000145
146# Delete old records from logincookies and tokens tables.
147$dbh->do('DELETE FROM logincookies WHERE userid = ?', undef, $old_id);
148$dbh->do('DELETE FROM tokens WHERE userid = ?', undef, $old_id);
149
ddkilzer@apple.com8040bb02017-03-21 16:27:49 +0000150# Special care needs to be done with bug_user_last_visit table as the
151# source user and destination user may have visited the same bug id at one time.
152# In this case we remove the one with the oldest timestamp.
153my $dupe_ids = $dbh->selectcol_arrayref("
154 SELECT earlier.id
155 FROM bug_user_last_visit as earlier
156 INNER JOIN bug_user_last_visit as later
157 ON (earlier.user_id != later.user_id
158 AND earlier.last_visit_ts < later.last_visit_ts
159 AND earlier.bug_id = later.bug_id)
160 WHERE (earlier.user_id = ? OR earlier.user_id = ?)
161 AND (later.user_id = ? OR later.user_id = ?)",
162 undef, $old_id, $new_id, $old_id, $new_id);
163
164if (@$dupe_ids) {
165 $dbh->do("DELETE FROM bug_user_last_visit WHERE " .
166 $dbh->sql_in('id', $dupe_ids));
167}
168
ddkilzer@apple.comf3615fc2009-07-03 02:13:41 +0000169# Migrate records from old user to new user.
ddkilzer@apple.com57772842014-10-16 16:00:58 +0000170foreach my $table (keys %changes) {
171 foreach my $column_list (@{ $changes{$table} }) {
ddkilzer@apple.comf3615fc2009-07-03 02:13:41 +0000172 # Get all columns to consider. There is always at least
173 # one column given: the one to update.
174 my @columns = split(/[\s]+/, $column_list);
175 my $cols_to_check = join(' AND ', map {"$_ = ?"} @columns);
176 # The first column of the list is the one to update.
177 my $col_to_update = shift @columns;
178
179 # Will be used to migrate the old user account to the new one.
180 my $sth_update = $dbh->prepare("UPDATE $table
181 SET $col_to_update = ?
182 WHERE $cols_to_check");
183
184 # Do we have additional columns to take care of?
185 if (scalar(@columns)) {
186 my $cols_to_query = join(', ', @columns);
187
188 # Get existing entries for the old user account.
189 my $old_entries =
190 $dbh->selectall_arrayref("SELECT $cols_to_query
191 FROM $table
192 WHERE $col_to_update = ?",
193 undef, $old_id);
194
195 # Will be used to check whether the same entry exists
196 # for the new user account.
197 my $sth_select = $dbh->prepare("SELECT COUNT(*)
198 FROM $table
199 WHERE $cols_to_check");
200
201 # Will be used to delete duplicated entries.
202 my $sth_delete = $dbh->prepare("DELETE FROM $table
203 WHERE $cols_to_check");
204
205 foreach my $entry (@$old_entries) {
206 my $exists = $dbh->selectrow_array($sth_select, undef,
207 ($new_id, @$entry));
208
209 if ($exists) {
210 $sth_delete->execute($old_id, @$entry);
211 }
212 else {
213 $sth_update->execute($new_id, $old_id, @$entry);
214 }
215 }
216 }
217 # No check required. Update the column directly.
218 else {
219 $sth_update->execute($new_id, $old_id);
220 }
221 print "OK, records in the '$col_to_update' column of the '$table' table\n" .
222 "have been migrated to the new user account.\n";
223 }
224}
225
226# Only update 'whine_schedules' if mailto_type = 0.
227# (i.e. is pointing to a user ID).
228$dbh->do('UPDATE whine_schedules SET mailto = ?
229 WHERE mailto = ? AND mailto_type = ?',
230 undef, ($new_id, $old_id, 0));
231print "OK, records in the 'mailto' column of the 'whine_schedules' table\n" .
232 "have been migrated to the new user account.\n";
233
234# Delete the old record from the profiles table.
235$dbh->do('DELETE FROM profiles WHERE userid = ?', undef, $old_id);
236
ddkilzer@apple.com097da082009-07-03 02:14:25 +0000237# rederive regexp-based group memberships, because we merged all memberships
238# from all of the accounts, and since the email address isn't the same on
239# them, some of them may no longer match the regexps.
240my $user = new Bugzilla::User($new_id);
241$user->derive_regexp_groups();
242
243# Commit the transaction
244$dbh->bz_commit_transaction();
ddkilzer@apple.comf3615fc2009-07-03 02:13:41 +0000245
ddkilzer@apple.com8040bb02017-03-21 16:27:49 +0000246# It's complex to determine which items now need to be flushed from memcached.
247# As user merge is expected to be a rare event, we just flush the entire cache
248# when users are merged.
249Bugzilla->memcached->clear_all();
250
ddkilzer@apple.comf3615fc2009-07-03 02:13:41 +0000251print "Done.\n";