ddkilzer@apple.com | 8040bb0 | 2017-03-21 16:27:49 +0000 | [diff] [blame] | 1 | #!/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.com | f3615fc | 2009-07-03 02:13:41 +0000 | [diff] [blame] | 5 | # |
ddkilzer@apple.com | 8040bb0 | 2017-03-21 16:27:49 +0000 | [diff] [blame] | 6 | # This Source Code Form is "Incompatible With Secondary Licenses", as |
| 7 | # defined by the Mozilla Public License, v. 2.0. |
ddkilzer@apple.com | f3615fc | 2009-07-03 02:13:41 +0000 | [diff] [blame] | 8 | |
ddkilzer@apple.com | 8040bb0 | 2017-03-21 16:27:49 +0000 | [diff] [blame] | 9 | use 5.10.1; |
ddkilzer@apple.com | f3615fc | 2009-07-03 02:13:41 +0000 | [diff] [blame] | 10 | use strict; |
ddkilzer@apple.com | 8040bb0 | 2017-03-21 16:27:49 +0000 | [diff] [blame] | 11 | use warnings; |
ddkilzer@apple.com | f3615fc | 2009-07-03 02:13:41 +0000 | [diff] [blame] | 12 | |
| 13 | =head1 NAME |
| 14 | |
| 15 | merge-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.com | 097da08 | 2009-07-03 02:14:25 +0000 | [diff] [blame] | 34 | use lib qw(. lib); |
ddkilzer@apple.com | f3615fc | 2009-07-03 02:13:41 +0000 | [diff] [blame] | 35 | |
| 36 | use Bugzilla; |
| 37 | use Bugzilla::Constants; |
| 38 | use Bugzilla::Util; |
ddkilzer@apple.com | 097da08 | 2009-07-03 02:14:25 +0000 | [diff] [blame] | 39 | use Bugzilla::User; |
ddkilzer@apple.com | f3615fc | 2009-07-03 02:13:41 +0000 | [diff] [blame] | 40 | |
| 41 | use Getopt::Long; |
| 42 | use Pod::Usage; |
| 43 | |
| 44 | my $dbh = Bugzilla->dbh; |
| 45 | |
| 46 | # Display the help if called with --help or -?. |
| 47 | my $help = 0; |
| 48 | my $result = GetOptions("help|?" => \$help); |
| 49 | pod2usage(0) if $help; |
| 50 | |
| 51 | |
| 52 | # Make sure accounts were specified on the command line and exist. |
| 53 | my $old = $ARGV[0] || die "You must specify an old user account.\n"; |
| 54 | my $old_id; |
| 55 | if ($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 | } |
| 61 | else { |
| 62 | trick_taint($old); |
| 63 | $old_id = $dbh->selectrow_array('SELECT userid FROM profiles |
| 64 | WHERE login_name = ?', |
| 65 | undef, $old); |
| 66 | } |
| 67 | if ($old_id) { |
| 68 | print "OK, old user account $old found; user ID: $old_id.\n"; |
| 69 | } |
| 70 | else { |
| 71 | die "The old user account $old does not exist.\n"; |
| 72 | } |
| 73 | |
| 74 | my $new = $ARGV[1] || die "You must specify a new user account.\n"; |
| 75 | my $new_id; |
| 76 | if ($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 | } |
| 83 | else { |
| 84 | trick_taint($new); |
| 85 | $new_id = $dbh->selectrow_array('SELECT userid FROM profiles |
| 86 | WHERE login_name = ?', |
| 87 | undef, $new); |
| 88 | } |
| 89 | if ($new_id) { |
| 90 | print "OK, new user account $new found; user ID: $new_id.\n"; |
| 91 | } |
| 92 | else { |
| 93 | die "The new user account $new does not exist.\n"; |
| 94 | } |
| 95 | |
| 96 | # Make sure the old and new accounts are different. |
| 97 | if ($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.com | 5777284 | 2014-10-16 16:00:58 +0000 | [diff] [blame] | 111 | # |
| 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. |
| 114 | my %changes = ( |
ddkilzer@apple.com | f3615fc | 2009-07-03 02:13:41 +0000 | [diff] [blame] | 115 | cc => ['who bug_id'], |
ddkilzer@apple.com | f3615fc | 2009-07-03 02:13:41 +0000 | [diff] [blame] | 116 | # Tables affecting global behavior / other users. |
ddkilzer@apple.com | f3615fc | 2009-07-03 02:13:41 +0000 | [diff] [blame] | 117 | component_cc => ['user_id component_id'], |
ddkilzer@apple.com | f3615fc | 2009-07-03 02:13:41 +0000 | [diff] [blame] | 118 | 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.com | f3615fc | 2009-07-03 02:13:41 +0000 | [diff] [blame] | 125 | |
| 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.com | 5777284 | 2014-10-16 16:00:58 +0000 | [diff] [blame] | 129 | ); |
ddkilzer@apple.com | f3615fc | 2009-07-03 02:13:41 +0000 | [diff] [blame] | 130 | |
ddkilzer@apple.com | 5777284 | 2014-10-16 16:00:58 +0000 | [diff] [blame] | 131 | my $userid_fks = $dbh->bz_get_related_fks('profiles', 'userid'); |
| 132 | foreach 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. |
| 139 | foreach my $table (qw(logincookies tokens profiles)) { |
| 140 | $changes{$table} = []; |
| 141 | } |
ddkilzer@apple.com | f3615fc | 2009-07-03 02:13:41 +0000 | [diff] [blame] | 142 | |
ddkilzer@apple.com | 097da08 | 2009-07-03 02:14:25 +0000 | [diff] [blame] | 143 | # Start the transaction |
| 144 | $dbh->bz_start_transaction(); |
ddkilzer@apple.com | f3615fc | 2009-07-03 02:13:41 +0000 | [diff] [blame] | 145 | |
| 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.com | 8040bb0 | 2017-03-21 16:27:49 +0000 | [diff] [blame] | 150 | # 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. |
| 153 | my $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 | |
| 164 | if (@$dupe_ids) { |
| 165 | $dbh->do("DELETE FROM bug_user_last_visit WHERE " . |
| 166 | $dbh->sql_in('id', $dupe_ids)); |
| 167 | } |
| 168 | |
ddkilzer@apple.com | f3615fc | 2009-07-03 02:13:41 +0000 | [diff] [blame] | 169 | # Migrate records from old user to new user. |
ddkilzer@apple.com | 5777284 | 2014-10-16 16:00:58 +0000 | [diff] [blame] | 170 | foreach my $table (keys %changes) { |
| 171 | foreach my $column_list (@{ $changes{$table} }) { |
ddkilzer@apple.com | f3615fc | 2009-07-03 02:13:41 +0000 | [diff] [blame] | 172 | # 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)); |
| 231 | print "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.com | 097da08 | 2009-07-03 02:14:25 +0000 | [diff] [blame] | 237 | # 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. |
| 240 | my $user = new Bugzilla::User($new_id); |
| 241 | $user->derive_regexp_groups(); |
| 242 | |
| 243 | # Commit the transaction |
| 244 | $dbh->bz_commit_transaction(); |
ddkilzer@apple.com | f3615fc | 2009-07-03 02:13:41 +0000 | [diff] [blame] | 245 | |
ddkilzer@apple.com | 8040bb0 | 2017-03-21 16:27:49 +0000 | [diff] [blame] | 246 | # 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. |
| 249 | Bugzilla->memcached->clear_all(); |
| 250 | |
ddkilzer@apple.com | f3615fc | 2009-07-03 02:13:41 +0000 | [diff] [blame] | 251 | print "Done.\n"; |