summaryrefslogtreecommitdiffstats
path: root/contrib/merge-users.pl
blob: 8e13b093495bbc99cf46edb7f6bdf71989e3cb9a (plain)
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
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
#!/usr/bin/perl -T
# This Source Code Form is subject to the terms of the Mozilla Public
# License, v. 2.0. If a copy of the MPL was not distributed with this
# file, You can obtain one at http://mozilla.org/MPL/2.0/.
#
# This Source Code Form is "Incompatible With Secondary Licenses", as
# defined by the Mozilla Public License, v. 2.0.

use 5.10.1;
use strict;
use warnings;

=head1 NAME

merge-users.pl - Merge two user accounts.

=head1 SYNOPSIS

 This script moves activity from one user account to another.
 Specify the two accounts on the command line, e.g.:

 ./merge-users.pl old_account@foo.com new_account@bar.com
 or:
 ./merge-users.pl id:old_userid id:new_userid
 or:
 ./merge-users.pl id:old_userid new_account@bar.com

 Notes: - the new account must already exist.
        - the id:old_userid syntax permits you to migrate
          activity from a deleted account to an existing one.

=cut

use lib qw(. lib local/lib/perl5);

use Bugzilla;
use Bugzilla::Constants;
use Bugzilla::Util;
use Bugzilla::User;

use Getopt::Long;
use Pod::Usage;

my $dbh = Bugzilla->dbh;

# Display the help if called with --help or -?.
my $help  = 0;
my $result = GetOptions("help|?" => \$help);
pod2usage(0) if $help;


# Make sure accounts were specified on the command line and exist.
my $old = $ARGV[0] || die "You must specify an old user account.\n";
my $old_id;
if ($old =~ /^id:(\d+)$/) {
    # As the old user account may be a deleted one, we don't
    # check whether this user ID is valid or not.
    # If it never existed, no damage will be done.
    $old_id = $1;
}
else {
    trick_taint($old);
    $old_id = $dbh->selectrow_array('SELECT userid FROM profiles
                                      WHERE login_name = ?',
                                      undef, $old);
}
if ($old_id) {
    print "OK, old user account $old found; user ID: $old_id.\n";
}
else {
    die "The old user account $old does not exist.\n";
}

my $new = $ARGV[1] || die "You must specify a new user account.\n";
my $new_id;
if ($new =~ /^id:(\d+)$/) {
    $new_id = $1;
    # Make sure this user ID exists.
    $new_id = $dbh->selectrow_array('SELECT userid FROM profiles
                                      WHERE userid = ?',
                                      undef, $new_id);
}
else {
    trick_taint($new);
    $new_id = $dbh->selectrow_array('SELECT userid FROM profiles
                                      WHERE login_name = ?',
                                      undef, $new);
}
if ($new_id) {
    print "OK, new user account $new found; user ID: $new_id.\n";
}
else {
    die "The new user account $new does not exist.\n";
}

# Make sure the old and new accounts are different.
if ($old_id == $new_id) {
    die "\nBoth accounts are identical. There is nothing to migrate.\n";
}


# A list of tables and columns to be changed:
# - keys of the hash are table names to be locked/altered;
# - values of the hash contain column names to be updated
#   as well as the columns they depend on:
#   = each array is of the form:
#     ['foo1 bar11 bar12 bar13', 'foo2 bar21 bar22', 'foo3 bar31 bar32']
#     where fooN is the column to update, and barN1, barN2, ... are
#     the columns to take into account to avoid duplicated entries.
#     Note that the barNM columns are optional.
#
# We set the tables that require custom stuff (multiple columns to check)
# here, but the simple stuff is all handled below by bz_get_related_fks.
my %changes = (
    cc              => ['who bug_id'],
    # Tables affecting global behavior / other users.
    component_cc    => ['user_id component_id'],
    watch           => ['watcher watched', 'watched watcher'],
    # Tables affecting the user directly.
    namedqueries    => ['userid name'],
    namedqueries_link_in_footer => ['user_id namedquery_id'],
    user_group_map  => ['user_id group_id isbless grant_type'],
    email_setting   => ['user_id relationship event'],
    profile_setting => ['user_id setting_name'],

    # Only do it if mailto_type = 0, i.e is pointing to a user account!
    # This requires to be done separately due to this condition.
    whine_schedules => [], # ['mailto'],
);

my $userid_fks = $dbh->bz_get_related_fks('profiles', 'userid');
foreach my $item (@$userid_fks) {
    my ($table, $column) = @$item;
    $changes{$table} ||= [];
    push(@{ $changes{$table} }, $column);
}

# Delete all old records for these tables; no migration.
foreach my $table (qw(logincookies tokens profiles)) {
    $changes{$table} = [];
}

# Start the transaction
$dbh->bz_start_transaction();

# Delete old records from logincookies and tokens tables.
$dbh->do('DELETE FROM logincookies WHERE userid = ?', undef, $old_id);
$dbh->do('DELETE FROM tokens WHERE userid = ?', undef, $old_id);

# Special care needs to be done with bug_user_last_visit table as the
# source user and destination user may have visited the same bug id at one time.
# In this case we remove the one with the oldest timestamp.
my $dupe_ids = $dbh->selectcol_arrayref("
    SELECT earlier.id
      FROM bug_user_last_visit as earlier
           INNER JOIN bug_user_last_visit as later
           ON (earlier.user_id != later.user_id
               AND earlier.last_visit_ts < later.last_visit_ts
               AND earlier.bug_id = later.bug_id)
     WHERE (earlier.user_id = ? OR earlier.user_id = ?)
           AND (later.user_id = ? OR later.user_id = ?)",
    undef, $old_id, $new_id, $old_id, $new_id);

if (@$dupe_ids) {
    $dbh->do("DELETE FROM bug_user_last_visit WHERE " .
             $dbh->sql_in('id', $dupe_ids));
}

# Migrate records from old user to new user.
foreach my $table (keys %changes) {
    foreach my $column_list (@{ $changes{$table} }) {
        # Get all columns to consider. There is always at least
        # one column given: the one to update.
        my @columns = split(/[\s]+/, $column_list);
        my $cols_to_check = join(' AND ', map {"$_ = ?"} @columns);
        # The first column of the list is the one to update.
        my $col_to_update = shift @columns;

        # Will be used to migrate the old user account to the new one.
        my $sth_update = $dbh->prepare("UPDATE $table
                                           SET $col_to_update = ?
                                         WHERE $cols_to_check");

        # Do we have additional columns to take care of?
        if (scalar(@columns)) {
            my $cols_to_query = join(', ', @columns);

            # Get existing entries for the old user account.
            my $old_entries = 
                $dbh->selectall_arrayref("SELECT $cols_to_query
                                            FROM $table
                                           WHERE $col_to_update = ?",
                                          undef, $old_id);

            # Will be used to check whether the same entry exists
            # for the new user account.
            my $sth_select = $dbh->prepare("SELECT COUNT(*)
                                              FROM $table
                                             WHERE $cols_to_check");

            # Will be used to delete duplicated entries.
            my $sth_delete = $dbh->prepare("DELETE FROM $table
                                             WHERE $cols_to_check");

            foreach my $entry (@$old_entries) {
                my $exists = $dbh->selectrow_array($sth_select, undef,
                                                   ($new_id, @$entry));

                if ($exists) {
                    $sth_delete->execute($old_id, @$entry);
                }
                else {
                    $sth_update->execute($new_id, $old_id, @$entry);
                }
            }
        }
        # No check required. Update the column directly.
        else {
            $sth_update->execute($new_id, $old_id);
        }
        print "OK, records in the '$col_to_update' column of the '$table' table\n" .
              "have been migrated to the new user account.\n";
    }
}

# Only update 'whine_schedules' if mailto_type = 0.
# (i.e. is pointing to a user ID).
$dbh->do('UPDATE whine_schedules SET mailto = ?
           WHERE mailto = ? AND mailto_type = ?',
          undef, ($new_id, $old_id, 0));
print "OK, records in the 'mailto' column of the 'whine_schedules' table\n" .
      "have been migrated to the new user account.\n";

# Delete the old record from the profiles table.
$dbh->do('DELETE FROM profiles WHERE userid = ?', undef, $old_id);

# rederive regexp-based group memberships, because we merged all memberships
# from all of the accounts, and since the email address isn't the same on
# them, some of them may no longer match the regexps.
my $user = new Bugzilla::User($new_id);
$user->derive_regexp_groups();

# Commit the transaction
$dbh->bz_commit_transaction();

# It's complex to determine which items now need to be flushed from memcached.
# As user merge is expected to be a rare event, we just flush the entire cache
# when users are merged.
Bugzilla->memcached->clear_all();

print "Done.\n";