Bug 284850: checksetup should rename indexes to conform to the new standard

Patch By Max Kanat-Alexander <mkanat@bugzilla.org> r=jouni, a=myk

2 files changed, 261 insertions, 43 deletions

diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm
index 7b2a7b2c2..1d10838e9 100644
--- a/Bugzilla/DB/Mysql.pm
+++ b/Bugzilla/DB/Mysql.pm
@@ -230,7 +230,226 @@ sub bz_setup_database {
         print "\nISAM->MyISAM table conversion done.\n\n";
     }
 
+    # Versions of Bugzilla before the existence of Bugzilla::DB::Schema did 
+    # not provide explicit names for the table indexes. This means
+    # that our upgrades will not be reliable, because we look for the name
+    # of the index, not what fields it is on, when doing upgrades.
+    # (using the name is much better for cross-database compatibility 
+    # and general reliability). It's also very important that our
+    # Schema object be consistent with what is on the disk.
+    #
+    # While we're at it, we also fix some inconsistent index naming
+    # from the original checkin of Bugzilla::DB::Schema.
+
+    # We check for the existence of a particular "short name" index that
+    # has existed at least since Bugzilla 2.8, and probably earlier.
+    # For fixing the inconsistent naming of Schema indexes,
+    # we also check for one of those inconsistently-named indexes.
+    my @tables = $self->bz_table_list_real();
+    if ( scalar(@tables) && 
+         ($self->bz_index_info_real('bugs', 'assigned_to') ||
+          $self->bz_index_info_real('flags', 'flags_bidattid_idx')) )
+    {
+        my $bug_count = $self->selectrow_array("SELECT COUNT(*) FROM bugs");
+        # We estimate one minute for each 3000 bugs, plus 3 minutes just
+        # to handle basic MySQL stuff.
+        my $rename_time = int($bug_count / 3000) + 3;
+        # If we're going to take longer than 5 minutes, we let the user know
+        # and allow them to abort.
+        if ($rename_time > 5) {
+            print "\nWe are about to rename old indexes.\n"
+                  . "The estimated time to complete renaming is "
+                  . "$rename_time minutes.\n"
+                  . "You cannot interrupt this action once it has begun.\n"
+                  . "If you would like to cancel, press Ctrl-C now..."
+                  . " (Waiting 45 seconds...)\n\n";
+            # Wait 45 seconds for them to respond.
+            sleep(45);
+        }
+        print "Renaming indexes...\n";
+
+        # We can't be interrupted, because of how the "if"
+        # works above.
+        local $SIG{INT}  = 'IGNORE';
+        local $SIG{TERM} = 'IGNORE';
+        local $SIG{PIPE} = 'IGNORE';
+
+        # Certain indexes had names in Schema that did not easily conform
+        # to a standard. We store those names here, so that they
+        # can be properly renamed.
+        my $bad_names = {
+            bugs_activity => ('bugs_activity_bugid_idx',
+                'bugs_activity_bugwhen_idx'),
+            longdescs => ('longdescs_bugid_idx',
+               'longdescs_bugwhen_idx'),
+            flags => ('flags_bidattid_idx'),
+            flaginclusions => ('flaginclusions_tpcid_idx'),
+            flagexclusions => ('flagexclusions_tpc_id_idx'),
+            profiles_activity => ('profiles_activity_when_idx'),
+            group_control_map => ('group_control_map_gid_idx')
+            # series_categories is dealt with below, not here.
+        };
+
+        # The series table is broken and needs to have one index
+        # dropped before we begin the renaming, because it had a
+        # useless index on it that would cause a naming conflict here.
+        if (grep($_ eq 'series', @tables)) {
+            my $dropname;
+            # This is what the bad index was called before Schema.
+            if ($self->bz_index_info_real('series', 'creator_2')) {
+                $dropname = 'creator_2';
+            }
+            # This is what the bad index is called in Schema.
+            elsif ($self->bz_index_info_real('series', 'series_creator_idx')) {
+                    $dropname = 'series_creator_idx';
+            }
+
+            if ($dropname) {
+                print "Removing the useless index $dropname on the"
+                      . " series table...\n";
+                my @drop = $self->_bz_schema->get_drop_index_ddl(
+                    'series', $dropname);
+                foreach my $sql (@drop) {
+                    $self->do($sql);
+                }
+            }
+        }
+
+        # The email_setting table also had the same problem.
+        if( grep($_ eq 'email_setting', @tables) 
+            && $self->bz_index_info_real('email_setting', 
+                                         'email_settings_user_id_idx') ) 
+        {
+            print "Removing the useless index email_settings_user_id_idx\n"
+                  . "  on the email_setting table...\n";
+            my @drop = $self->_bz_schema->get_drop_index_ddl('email_setting',
+                'email_settings_user_id_idx');
+            $self->do($_) foreach (@drop);
+        }
+
+        # Go through all the tables.
+        foreach my $table (@tables) {
+            # And go through all the columns on each table.
+            my @columns = $self->bz_table_columns_real($table);
+
+            # We also want to fix the silly naming of unique indexes
+            # that happened when we first checked-in Bugzilla::DB::Schema.
+            if ($table eq 'series_categories') {
+                # The series_categories index had a nonstandard name.
+                push(@columns, 'series_cats_unique_idx');
+            } 
+            elsif ($table eq 'email_setting') { 
+                # The email_setting table had a similar problem.
+                push(@columns, 'email_settings_unique_idx');
+            }
+            else {
+                push(@columns, "${table}_unique_idx");
+            }
+            # And this is how we fix the other inconsistent Schema naming.
+            push(@columns, $bad_names->{$table})
+                if (exists $bad_names->{$table});
+            foreach my $column (@columns) {
+                # If we have an index named after this column, it's an 
+                # old-style-name index.
+                # This will miss PRIMARY KEY indexes, but that's OK 
+                # because we aren't renaming them.
+                if (my $index = $self->bz_index_info_real($table, $column)) {
+                    # Fix the name to fit in with the new naming scheme.
+                    my $new_name = $table . "_" .
+                                   $index->{FIELDS}->[0] . "_idx";
+                    print "Renaming index $column to to $new_name...\n";
+                    # Unfortunately, MySQL has no way to rename an index. :-(
+                    # So we have to drop and recreate the indexes.
+                    my @drop = $self->_bz_schema->get_drop_index_ddl(
+                        $table, $column);
+                    my @add = $self->_bz_schema->get_add_index_ddl(
+                        $table, $new_name, $index);
+                    $self->do($_) foreach (@drop);
+                    $self->do($_) foreach (@add);
+                } # if
+            } # foreach column
+        } # foreach table
+    } # if old-name indexes
+
     $self->SUPER::bz_setup_database();
 }
 
+
+#####################################################################
+# MySQL-specific Database-Reading Methods
+#####################################################################
+
+=begin private
+
+=head 1 MYSQL-SPECIFIC DATABASE-READING METHODS
+
+These methods read information about the database from the disk,
+instead of from a Schema object. They are only reliable for MySQL 
+(see bug 285111 for the reasons why not all DBs use/have functions
+like this), but that's OK because we only need them for 
+backwards-compatibility anyway, for versions of Bugzilla before 2.20.
+
+=over 4
+
+=item C<bz_index_info_real($table, $index)>
+
+ Description: Returns information about an index on a table in the database.
+ Params:      $table = name of table containing the index
+              $index = name of an index
+ Returns:     An abstract index definition, always in hashref format.
+              If the index does not exist, the function returns undef.
+=cut
+sub bz_index_info_real {
+    my ($self, $table, $index) = @_;
+
+    my $sth = $self->prepare("SHOW INDEX FROM $table");
+    $sth->execute;
+
+    my @fields;
+    my $index_type;
+    # $raw_def will be an arrayref containing the following information:
+    # 0 = name of the table that the index is on
+    # 1 = 0 if unique, 1 if not unique
+    # 2 = name of the index
+    # 3 = seq_in_index (The order of the current field in the index).
+    # 4 = Name of ONE column that the index is on
+    # 5 = 'Collation' of the index. Usually 'A'.
+    # 6 = Cardinality. Either a number or undef.
+    # 7 = sub_part. Usually undef. Sometimes 1.
+    # 8 = "packed". Usually undef.
+    # MySQL 3
+    # -------
+    # 9 = comments. Usually an empty string. Sometimes 'FULLTEXT'.
+    # MySQL 4
+    # -------
+    # 9 = Null. Sometimes undef, sometimes 'YES'.
+    # 10 = Index_type. The type of the index. Usually either 'BTREE' or 'FULLTEXT'
+    # 11 = 'Comment.' Usually undef.
+    my $is_mysql3 = ($self->bz_server_version() =~ /^3/);
+    my $index_type_loc = $is_mysql3 ? 9 : 10;
+    while (my $raw_def = $sth->fetchrow_arrayref) {
+        if ($raw_def->[2] eq $index) {
+            push(@fields, $raw_def->[4]);
+            # No index can be both UNIQUE and FULLTEXT, that's why
+            # this is written this way.
+            $index_type = $raw_def->[1] ? '' : 'UNIQUE';
+            $index_type = $raw_def->[$index_type_loc] eq 'FULLTEXT'
+                ? 'FULLTEXT' : $index_type;
+        }
+    }
+
+    my $retval;
+    if (scalar(@fields)) {
+        $retval = {FIELDS => \@fields, TYPE => $index_type};
+    }
+    return $retval;
+}
+
 1;
+
+__END__
+
+=back
+
+=end private
+
diff --git a/Bugzilla/DB/Schema.pm b/Bugzilla/DB/Schema.pm
index 054080772..3d008a09b 100644
--- a/Bugzilla/DB/Schema.pm
+++ b/Bugzilla/DB/Schema.pm
@@ -191,7 +191,7 @@ use constant ABSTRACT_SCHEMA => {
             alias               => {TYPE => 'varchar(20)'},
         ],
         INDEXES => [
-            bugs_unique_idx           => {FIELDS => ['alias'],
+            bugs_alias_idx            => {FIELDS => ['alias'],
                                           TYPE => 'UNIQUE'},
             bugs_assigned_to_idx      => ['assigned_to'],
             bugs_creation_ts_idx      => ['creation_ts'],
@@ -224,9 +224,9 @@ use constant ABSTRACT_SCHEMA => {
             removed   => {TYPE => 'TINYTEXT'},
         ],
         INDEXES => [
-            bugs_activity_bugid_idx   => ['bug_id'],
+            bugs_activity_bug_id_idx  => ['bug_id'],
             bugs_activity_who_idx     => ['who'],
-            bugs_activity_bugwhen_idx => ['bug_when'],
+            bugs_activity_bug_when_idx => ['bug_when'],
             bugs_activity_fieldid_idx => ['fieldid'],
         ],
     },
@@ -237,7 +237,7 @@ use constant ABSTRACT_SCHEMA => {
             who    => {TYPE => 'INT3', NOTNULL => 1},
         ],
         INDEXES => [
-            cc_unique_idx => {FIELDS => [qw(bug_id who)],
+            cc_bug_id_idx => {FIELDS => [qw(bug_id who)],
                               TYPE => 'UNIQUE'},
             cc_who_idx    => ['who'],
         ],
@@ -257,9 +257,9 @@ use constant ABSTRACT_SCHEMA => {
                                 DEFAULT => 'FALSE'},
         ],
         INDEXES => [
-            longdescs_bugid_idx   => ['bug_id'],
+            longdescs_bug_id_idx   => ['bug_id'],
             longdescs_who_idx     => ['who'],
-            longdescs_bugwhen_idx => ['bug_when'],
+            longdescs_bug_when_idx => ['bug_when'],
             longdescs_thetext_idx => {FIELDS => ['thetext'],
                                       TYPE => 'FULLTEXT'},
         ],
@@ -308,6 +308,7 @@ use constant ABSTRACT_SCHEMA => {
         INDEXES => [
             attachments_bug_id_idx => ['bug_id'],
             attachments_creation_ts_idx => ['creation_ts'],
+            attachments_submitter_id_idx => ['submitter_id'],
         ],
     },
 
@@ -330,7 +331,7 @@ use constant ABSTRACT_SCHEMA => {
             description => {TYPE => 'MEDIUMTEXT'},
         ],
         INDEXES => [
-            keyworddefs_unique_idx => {FIELDS => ['name'],
+            keyworddefs_name_idx   => {FIELDS => ['name'],
                                        TYPE => 'UNIQUE'},
         ],
     },
@@ -341,7 +342,7 @@ use constant ABSTRACT_SCHEMA => {
             keywordid => {TYPE => 'INT2', NOTNULL => 1},
         ],
         INDEXES => [
-            keywords_unique_idx    => {FIELDS => [qw(bug_id keywordid)],
+            keywords_bug_id_idx    => {FIELDS => [qw(bug_id keywordid)],
                                        TYPE => 'UNIQUE'},
             keywords_keywordid_idx => ['keywordid'],
         ],
@@ -367,7 +368,7 @@ use constant ABSTRACT_SCHEMA => {
                                   DEFAULT => 'TRUE'},
         ],
         INDEXES => [
-            flags_bidattid_idx     => [qw(bug_id attach_id)],
+            flags_bug_id_idx       => [qw(bug_id attach_id)],
             flags_setter_id_idx    => ['setter_id'],
             flags_requestee_id_idx => ['requestee_id'],
         ],
@@ -408,7 +409,7 @@ use constant ABSTRACT_SCHEMA => {
             component_id => {TYPE => 'INT2'},
         ],
         INDEXES => [
-            flaginclusions_tpcid_idx =>
+            flaginclusions_type_id_idx =>
                 [qw(type_id product_id component_id)],
         ],
     },
@@ -420,7 +421,7 @@ use constant ABSTRACT_SCHEMA => {
             component_id => {TYPE => 'INT2'},
         ],
         INDEXES => [
-            flagexclusions_tpc_id_idx =>
+            flagexclusions_type_id_idx =>
                 [qw(type_id product_id component_id)],
         ],
     },
@@ -441,7 +442,7 @@ use constant ABSTRACT_SCHEMA => {
                             DEFAULT => 'FALSE'},
         ],
         INDEXES => [
-            fielddefs_unique_idx  => {FIELDS => ['name'],
+            fielddefs_name_idx    => {FIELDS => ['name'],
                                       TYPE => 'UNIQUE'},
             fielddefs_sortkey_idx => ['sortkey'],
         ],
@@ -464,8 +465,8 @@ use constant ABSTRACT_SCHEMA => {
             sortkey    => {TYPE => 'INT2', NOTNULL => 1},
         ],
         INDEXES => [
-            milestones_unique_idx => {FIELDS => [qw(product_id value)],
-                                      TYPE => 'UNIQUE'},
+            milestones_product_id_idx => {FIELDS => [qw(product_id value)],
+                                          TYPE => 'UNIQUE'},
         ],
     },
 
@@ -482,7 +483,7 @@ use constant ABSTRACT_SCHEMA => {
                          DEFAULT => 'TRUE'},
         ],
         INDEXES => [
-            bug_status_unique_idx  => {FIELDS => ['value'],
+            bug_status_value_idx  => {FIELDS => ['value'],
                                        TYPE => 'UNIQUE'},
             bug_status_sortkey_idx => ['sortkey', 'value'],
         ],
@@ -498,7 +499,7 @@ use constant ABSTRACT_SCHEMA => {
                          DEFAULT => 'TRUE'},
         ],
         INDEXES => [
-            resolution_unique_idx  => {FIELDS => ['value'],
+            resolution_value_idx   => {FIELDS => ['value'],
                                        TYPE => 'UNIQUE'},
             resolution_sortkey_idx => ['sortkey', 'value'],
         ],
@@ -514,7 +515,7 @@ use constant ABSTRACT_SCHEMA => {
                          DEFAULT => 'TRUE'},
         ],
         INDEXES => [
-            bug_severity_unique_idx  => {FIELDS => ['value'],
+            bug_severity_value_idx   => {FIELDS => ['value'],
                                          TYPE => 'UNIQUE'},
             bug_severity_sortkey_idx => ['sortkey', 'value'],
         ],
@@ -530,7 +531,7 @@ use constant ABSTRACT_SCHEMA => {
                          DEFAULT => 'TRUE'},
         ],
         INDEXES => [
-            priority_unique_idx  => {FIELDS => ['value'],
+            priority_value_idx   => {FIELDS => ['value'],
                                      TYPE => 'UNIQUE'},
             priority_sortkey_idx => ['sortkey', 'value'],
         ],
@@ -546,7 +547,7 @@ use constant ABSTRACT_SCHEMA => {
                          DEFAULT => 'TRUE'},
         ],
         INDEXES => [
-            rep_platform_unique_idx  => {FIELDS => ['value'],
+            rep_platform_value_idx   => {FIELDS => ['value'],
                                          TYPE => 'UNIQUE'},
             rep_platform_sortkey_idx => ['sortkey', 'value'],
         ],
@@ -562,7 +563,7 @@ use constant ABSTRACT_SCHEMA => {
                          DEFAULT => 'TRUE'},
         ],
         INDEXES => [
-            op_sys_unique_idx  => {FIELDS => ['value'],
+            op_sys_value_idx   => {FIELDS => ['value'],
                                    TYPE => 'UNIQUE'},
             op_sys_sortkey_idx => ['sortkey', 'value'],
         ],
@@ -588,8 +589,8 @@ use constant ABSTRACT_SCHEMA => {
             extern_id      => {TYPE => 'varchar(64)'},
         ],
         INDEXES => [
-            profiles_unique_idx => {FIELDS => ['login_name'],
-                                    TYPE => 'UNIQUE'},
+            profiles_login_name_idx => {FIELDS => ['login_name'],
+                                        TYPE => 'UNIQUE'},
         ],
     },
 
@@ -604,7 +605,7 @@ use constant ABSTRACT_SCHEMA => {
         ],
         INDEXES => [
             profiles_activity_userid_idx  => ['userid'],
-            profiles_activity_when_idx    => ['profiles_when'],
+            profiles_activity_profiles_when_idx => ['profiles_when'],
             profiles_activity_fieldid_idx => ['fieldid'],
         ],
     },
@@ -616,8 +617,7 @@ use constant ABSTRACT_SCHEMA => {
             event        => {TYPE => 'INT1', NOTNULL => 1},
         ],
         INDEXES => [
-            email_settings_user_id_idx => ['user_id'],
-            email_settings_unique_idx  =>
+            email_setting_user_id_idx  =>
                                     {FIELDS => [qw(user_id relationship event)],
                                      TYPE => 'UNIQUE'},
         ],
@@ -629,7 +629,7 @@ use constant ABSTRACT_SCHEMA => {
             watched => {TYPE => 'INT3', NOTNULL => 1},
         ],
         INDEXES => [
-            watch_unique_idx  => {FIELDS => [qw(watcher watched)],
+            watch_watcher_idx => {FIELDS => [qw(watcher watched)],
                                   TYPE => 'UNIQUE'},
             watch_watched_idx => ['watched'],
         ],
@@ -643,7 +643,7 @@ use constant ABSTRACT_SCHEMA => {
             query        => {TYPE => 'MEDIUMTEXT', NOTNULL => 1},
         ],
         INDEXES => [
-            namedqueries_unique_idx => {FIELDS => [qw(userid name)],
+            namedqueries_userid_idx => {FIELDS => [qw(userid name)],
                                         TYPE => 'UNIQUE'},
         ],
     },
@@ -697,7 +697,7 @@ use constant ABSTRACT_SCHEMA => {
                              DEFAULT => 'TRUE'},
         ],
         INDEXES => [
-            groups_unique_idx => {FIELDS => ['name'], TYPE => 'UNIQUE'},
+            groups_name_idx => {FIELDS => ['name'], TYPE => 'UNIQUE'},
         ],
     },
 
@@ -711,9 +711,9 @@ use constant ABSTRACT_SCHEMA => {
             canedit       => {TYPE => 'BOOLEAN', NOTNULL => 1},
         ],
         INDEXES => [
-            group_control_map_unique_idx =>
+            group_control_map_product_id_idx =>
             {FIELDS => [qw(product_id group_id)], TYPE => 'UNIQUE'},
-            group_control_map_gid_idx    => ['group_id'],
+            group_control_map_group_id_idx    => ['group_id'],
         ],
     },
 
@@ -734,7 +734,7 @@ use constant ABSTRACT_SCHEMA => {
                            DEFAULT => '0'},
         ],
         INDEXES => [
-            user_group_map_unique_idx =>
+            user_group_map_user_id_idx =>
                 {FIELDS => [qw(user_id group_id grant_type isbless)],
                  TYPE => 'UNIQUE'},
         ],
@@ -755,7 +755,7 @@ use constant ABSTRACT_SCHEMA => {
                            DEFAULT => '0'},
         ],
         INDEXES => [
-            group_group_map_unique_idx =>
+            group_group_map_member_id_idx =>
                 {FIELDS => [qw(member_id grantor_id grant_type)],
                  TYPE => 'UNIQUE'},
         ],
@@ -769,7 +769,7 @@ use constant ABSTRACT_SCHEMA => {
             group_id => {TYPE => 'INT3', NOTNULL => 1},
         ],
         INDEXES => [
-            bug_group_map_unique_idx   =>
+            bug_group_map_bug_id_idx   =>
                 {FIELDS => [qw(bug_id group_id)], TYPE => 'UNIQUE'},
             bug_group_map_group_id_idx => ['group_id'],
         ],
@@ -781,7 +781,7 @@ use constant ABSTRACT_SCHEMA => {
             group_id    => {TYPE => 'INT3', NOTNULL => 1},
         ],
         INDEXES => [
-            category_group_map_unique_idx =>
+            category_group_map_category_id_idx =>
                 {FIELDS => [qw(category_id group_id)], TYPE => 'UNIQUE'},
         ],
     },
@@ -798,7 +798,7 @@ use constant ABSTRACT_SCHEMA => {
             description => {TYPE => 'MEDIUMTEXT'},
         ],
         INDEXES => [
-            classifications_unique_idx => {FIELDS => ['name'],
+            classifications_name_idx => {FIELDS => ['name'],
                                            TYPE => 'UNIQUE'},
         ],
     },
@@ -821,7 +821,7 @@ use constant ABSTRACT_SCHEMA => {
                                   NOTNULL => 1, DEFAULT => "'---'"},
         ],
         INDEXES => [
-            products_unique_idx => {FIELDS => ['name'],
+            products_name_idx   => {FIELDS => ['name'],
                                     TYPE => 'UNIQUE'},
         ],
     },
@@ -837,8 +837,8 @@ use constant ABSTRACT_SCHEMA => {
             description      => {TYPE => 'MEDIUMTEXT', NOTNULL => 1},
         ],
         INDEXES => [
-            components_unique_idx => {FIELDS => [qw(product_id name)],
-                                      TYPE => 'UNIQUE'},
+            components_product_id_idx => {FIELDS => [qw(product_id name)],
+                                          TYPE => 'UNIQUE'},
             components_name_idx   => ['name'],
         ],
     },
@@ -861,10 +861,9 @@ use constant ABSTRACT_SCHEMA => {
                             DEFAULT => 'FALSE'},
         ],
         INDEXES => [
-            series_unique_idx  =>
+            series_creator_idx  =>
                 {FIELDS => [qw(creator category subcategory name)],
                  TYPE => 'UNIQUE'},
-            series_creator_idx => ['creator'],
         ],
     },
 
@@ -875,7 +874,7 @@ use constant ABSTRACT_SCHEMA => {
             series_value => {TYPE => 'INT3', NOTNULL => 1},
         ],
         INDEXES => [
-            series_data_unique_idx =>
+            series_data_series_id_idx =>
                 {FIELDS => [qw(series_id series_date)],
                  TYPE => 'UNIQUE'},
         ],
@@ -888,8 +887,8 @@ use constant ABSTRACT_SCHEMA => {
             name => {TYPE => 'varchar(64)', NOTNULL => 1},
         ],
         INDEXES => [
-            series_cats_unique_idx => {FIELDS => ['name'],
-                                       TYPE => 'UNIQUE'},
+            series_categories_name_idx => {FIELDS => ['name'],
+                                           TYPE => 'UNIQUE'},
         ],
     },