diff options
| -rw-r--r-- | Bugzilla/DB.pm | 38 | ||||
| -rw-r--r-- | Bugzilla/DB/Mysql.pm | 219 | ||||
| -rw-r--r-- | Bugzilla/DB/Schema.pm | 85 | ||||
| -rwxr-xr-x | checksetup.pl | 72 |
4 files changed, 337 insertions, 77 deletions
diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm index 28eb9eed9..b0106314e 100644 --- a/Bugzilla/DB.pm +++ b/Bugzilla/DB.pm @@ -305,10 +305,7 @@ sub bz_setup_database { my ($self) = @_; # Get a list of the existing tables (if any) in the database - my $table_sth = $self->table_info(undef, undef, undef, "TABLE"); - my @current_tables = - @{$self->selectcol_arrayref($table_sth, { Columns => [3] })}; - + my @current_tables = $self->bz_table_list_real(); my @desired_tables = $self->_bz_schema->get_table_list(); foreach my $table_name (@desired_tables) { @@ -650,6 +647,39 @@ sub bz_table_exists ($) { } ##################################################################### +# Protected "Real Database" Schema Information Methods +##################################################################### + +# Only Bugzilla::DB and subclasses should use these methods. +# If you need a method that does the same thing as one of these +# methods, use the version without _real on the end. + +# bz_table_columns_real($table) +# +# Description: Returns a list of columns on a given table +# as the table actually is, on the disk. +# Params: $table - Name of the table. +# Returns: An array of column names. +# +sub bz_table_columns_real { + my ($self, $table) = @_; + my $sth = $self->column_info(undef, undef, $table, '%'); + return @{ $self->selectcol_arrayref($sth, {Columns => [4]}) }; +} + +# bz_table_list_real() +# +# Description: Gets a list of tables in the current +# database, directly from the disk. +# Params: none +# Returns: An array containing table names. +sub bz_table_list_real { + my ($self) = @_; + my $table_sth = $self->table_info(undef, undef, undef, "TABLE"); + return @{$self->selectcol_arrayref($table_sth, { Columns => [3] })}; +} + +##################################################################### # Transaction Methods ##################################################################### 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'}, ], }, diff --git a/checksetup.pl b/checksetup.pl index 0e2af1608..8decb119f 100755 --- a/checksetup.pl +++ b/checksetup.pl @@ -1905,7 +1905,10 @@ $dbh->bz_rename_field('bugs_activity', 'when', 'bug_when'); # a new table format which will allow 32 indices.) $dbh->bz_drop_field('bugs', 'area'); -$dbh->bz_add_field('bugs', 'votes', 'mediumint not null, add index (votes)'); +if (!$dbh->bz_get_field_def('bugs', 'votes')) { + $dbh->bz_add_field('bugs', 'votes', 'mediumint not null'); + $dbh->do('CREATE INDEX bugs_votes_idx ON bugs(votes)'); +} $dbh->bz_add_field('products', 'votesperuser', 'mediumint not null'); @@ -2114,8 +2117,9 @@ if ($dbh->bz_get_field_def('bugs', 'long_desc')) { # now has a pointer into that table instead of recording the name directly. if ($dbh->bz_get_field_def('bugs_activity', 'field')) { - $dbh->bz_add_field('bugs_activity', 'fieldid', - 'mediumint not null, ADD INDEX (fieldid)'); + $dbh->bz_add_field('bugs_activity', 'fieldid', 'mediumint not null'); + $dbh->do('CREATE INDEX bugs_activity_fieldid_idx + ON bugs_activity(fieldid)'); print "Populating new fieldid field ...\n"; $dbh->bz_lock_tables('bugs_activity WRITE', 'fielddefs WRITE'); @@ -2200,8 +2204,9 @@ if ($dbh->bz_get_index_def('profiles', 'login_name')->[1]) { } print "OK, changing index type to prevent duplicates in the future ...\n"; - $dbh->do("ALTER TABLE profiles DROP INDEX login_name"); - $dbh->do("ALTER TABLE profiles ADD UNIQUE (login_name)"); + $dbh->do("ALTER TABLE profiles DROP INDEX profiles_login_name_idx"); + $dbh->do("CREATE UNIQUE INDEX profiles_login_name_idx" + . " ON profiles(login_name)"); } @@ -2397,8 +2402,8 @@ if ( $dbh->bz_get_index_count('cc') != 3 ) { # print "Recreating indexes on cc table.\n"; $dbh->bz_drop_table_indexes('cc'); - $dbh->do("ALTER TABLE cc ADD UNIQUE (bug_id,who)"); - $dbh->do("ALTER TABLE cc ADD INDEX (who)"); + $dbh->do("CREATE UNIQUE INDEX cc_bug_id_idx ON cc(bug_id,who)"); + $dbh->do("CREATE INDEX cc_who_idx ON cc(who)"); } if ( $dbh->bz_get_index_count('keywords') != 3 ) { @@ -2407,8 +2412,9 @@ if ( $dbh->bz_get_index_count('keywords') != 3 ) { # print "Recreating indexes on keywords table.\n"; $dbh->bz_drop_table_indexes('keywords'); - $dbh->do("ALTER TABLE keywords ADD INDEX (keywordid)"); - $dbh->do("ALTER TABLE keywords ADD UNIQUE (bug_id,keywordid)"); + $dbh->do("CREATE UNIQUE INDEX keywords_bug_id_idx" + . " ON keywords(bug_id,keywordid)"); + $dbh->do("CREATE INDEX keywords_keywordid_idx ON keywords(keywordid)"); } @@ -2555,7 +2561,7 @@ ENDTEXT # http://bugzilla.mozilla.org/show_bug.cgi?id=57350 if (!defined $dbh->bz_get_index_def('longdescs','who')) { print "Adding index for who column in longdescs table...\n"; - $dbh->do('ALTER TABLE longdescs ADD INDEX (who)'); + $dbh->do('CREATE INDEX longdescs_who_idx ON longdescs(who)'); } # 2001-06-15 kiko@async.com.br - Change bug:version size to avoid @@ -2720,7 +2726,7 @@ $dbh->bz_add_field('attachments', 'isprivate', 'tinyint not null default 0'); # in addition to ID. if (!$dbh->bz_get_field_def("bugs", "alias")) { $dbh->bz_add_field("bugs", "alias", "VARCHAR(20)"); - $dbh->do("ALTER TABLE bugs ADD UNIQUE (alias)"); + $dbh->do("CREATE UNIQUE INDEX bugs_alias_idx ON bugs(alias)"); } # 2002-07-15 davef@tetsubo.com - bug 67950 @@ -2818,11 +2824,12 @@ if ($dbh->bz_get_field_def("products", "product")) { # Drop any indexes that may exist on the milestones table. $dbh->bz_drop_table_indexes('milestones'); - $dbh->do("ALTER TABLE milestones ADD UNIQUE (product_id, value)"); - $dbh->do("ALTER TABLE bugs DROP INDEX product"); - $dbh->do("ALTER TABLE bugs ADD INDEX (product_id)"); - $dbh->do("ALTER TABLE bugs DROP INDEX component"); - $dbh->do("ALTER TABLE bugs ADD INDEX (component_id)"); + $dbh->do("CREATE UNIQUE INDEX milestones_product_id_idx" + . " ON milestones(product_id, value)"); + $dbh->do("ALTER TABLE bugs DROP INDEX bugs_product_idx"); + $dbh->do("CREATE INDEX bugs_product_id_idx ON bugs(product_id)"); + $dbh->do("ALTER TABLE bugs DROP INDEX bugs_component_idx"); + $dbh->do("CREATE INDEX bugs_component_id_idx ON bugs(component_id)"); print "Removing, renaming, and retyping old product and component fields.\n"; $dbh->bz_drop_field("components", "program"); @@ -2837,9 +2844,10 @@ if ($dbh->bz_get_field_def("products", "product")) { $dbh->bz_change_field_type("components", "name", "varchar(64) not null"); print "Adding indexes for products and components tables.\n"; - $dbh->do("ALTER TABLE products ADD UNIQUE (name)"); - $dbh->do("ALTER TABLE components ADD UNIQUE (product_id, name)"); - $dbh->do("ALTER TABLE components ADD INDEX (name)"); + $dbh->do("CREATE UNIQUE INDEX products_name_idx ON products(name)"); + $dbh->do("CREATE UNIQUE INDEX component_product_id_idx" + . " ON components(product_id, name)"); + $dbh->do("CREATE INDEX components_name_idx ON components(name)"); } # 2002-08-14 - bbaetz@student.usyd.edu.au - bug 153578 @@ -2932,11 +2940,13 @@ if ($dbh->bz_get_field_def("profiles", "groupset")) { $dbh->bz_add_field('groups', 'last_changed', 'datetime not null'); # Some mysql versions will promote any unique key to primary key # so all unique keys are removed first and then added back in - $dbh->do("ALTER TABLE groups DROP INDEX bit") if $dbh->bz_get_index_def("groups","bit"); - $dbh->do("ALTER TABLE groups DROP INDEX name") if $dbh->bz_get_index_def("groups","name"); + $dbh->do("ALTER TABLE groups DROP INDEX groups_bit_idx") + if $dbh->bz_get_index_def("groups","bit"); + $dbh->do("ALTER TABLE groups DROP INDEX groups_name_idx") + if $dbh->bz_get_index_def("groups","name"); $dbh->do("ALTER TABLE groups DROP PRIMARY KEY"); $dbh->bz_add_field('groups', 'id', 'mediumint not null auto_increment primary key'); - $dbh->do("ALTER TABLE groups ADD UNIQUE (name)"); + $dbh->do("CREATE UNIQUE INDEX groups_name_idx ON groups(name)"); $dbh->bz_add_field('profiles', 'refreshed_when', 'datetime not null'); # Convert all existing groupset records to map entries before removing @@ -3547,8 +3557,8 @@ if ($dbh->bz_get_field_def("user_group_map", "isderived")) { WHERE isbless = 0 AND grant_type != " . GRANT_DIRECT); $dbh->bz_drop_field("user_group_map", "isderived"); $dbh->bz_drop_table_indexes("user_group_map"); - $dbh->do("ALTER TABLE user_group_map - ADD UNIQUE (user_id, group_id, grant_type, isbless)"); + $dbh->do("CREATE UNIQUE INDEX user_group_map_user_id_idx + ON user_group_map(user_id, group_id, grant_type, isbless)"); # Evaluate regexp-based group memberships my $sth = $dbh->prepare("SELECT profiles.userid, profiles.login_name, groups.id, groups.userregexp @@ -3582,8 +3592,8 @@ if ($dbh->bz_get_field_def("group_group_map", "isbless")) { GROUP_MEMBERSHIP . ")"); $dbh->bz_drop_table_indexes("group_group_map"); $dbh->bz_drop_field("group_group_map", "isbless"); - $dbh->do("ALTER TABLE group_group_map - ADD UNIQUE (member_id, grantor_id, grant_type)"); + $dbh->do("CREATE UNIQUE INDEX group_group_map_member_id_idx + ON group_group_map(member_id, grantor_id, grant_type)"); } # Allow profiles to optionally be linked to a unique identifier in an outside @@ -3653,11 +3663,12 @@ if (!$dbh->bz_get_field_def('fielddefs', 'obsolete')) { # Add fulltext indexes for bug summaries and descriptions/comments. if (!defined $dbh->bz_get_index_def('bugs', 'short_desc')) { print "Adding full-text index for short_desc column in bugs table...\n"; - $dbh->do('ALTER TABLE bugs ADD FULLTEXT (short_desc)'); + $dbh->do('CREATE FULLTEXT INDEX bugs_short_desc_idx ON bugs(short_desc)'); } if (!defined $dbh->bz_get_index_def('longdescs', 'thetext')) { print "Adding full-text index for thetext column in longdescs table...\n"; - $dbh->do('ALTER TABLE longdescs ADD FULLTEXT (thetext)'); + $dbh->do('CREATE FULLTEXT INDEX longdescs_thetext_idx + ON longdescs(thetext)'); } # 2002 November, myk@mozilla.org, bug 178841: @@ -3775,11 +3786,12 @@ if (($fielddef = $dbh->bz_get_field_def("bugs", "delta_ts")) && # 2005-02-12 bugreport@peshkin.net, bug 281787 if (!defined $dbh->bz_get_index_def('attachments','submitter_id')) { print "Adding index for submitter_id column in attachments table...\n"; - $dbh->do('ALTER TABLE attachments ADD INDEX (submitter_id)'); + $dbh->do('CREATE INDEX attachments_submitter_id_idx' + . ' ON attachments(submitter_id)'); } if (!defined $dbh->bz_get_index_def('bugs_activity','who')) { print "Adding index for who column in bugs_activity table...\n"; - $dbh->do('ALTER TABLE bugs_activity ADD INDEX (who)'); + $dbh->do('CREATE INDEX bugs_activity_who_idx ON bugs_activity(who)'); } # This lastdiffed change and these default changes are unrelated, |