diff options
Diffstat (limited to 'Bugzilla/DB')
| -rw-r--r-- | Bugzilla/DB/Mysql.pm | 219 | ||||
| -rw-r--r-- | Bugzilla/DB/Schema.pm | 85 |
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'}, ], }, |