X-Git-Url: http://code.vuplus.com/gitweb/?a=blobdiff_plain;f=Websites%2Fbugs.webkit.org%2FBugzilla%2FDB%2FMysql.pm;fp=Websites%2Fbugs.webkit.org%2FBugzilla%2FDB%2FMysql.pm;h=92d1df1a01a1f6adaf7249f5b5176b1f412a593b;hb=186b2535c234ccc42a8c27998f11be6f718f604f;hp=0000000000000000000000000000000000000000;hpb=1c567be6144228b511852e3cab689fc41b052875;p=vuplus_webkit diff --git a/Websites/bugs.webkit.org/Bugzilla/DB/Mysql.pm b/Websites/bugs.webkit.org/Bugzilla/DB/Mysql.pm new file mode 100644 index 0000000..92d1df1 --- /dev/null +++ b/Websites/bugs.webkit.org/Bugzilla/DB/Mysql.pm @@ -0,0 +1,967 @@ +# -*- Mode: perl; indent-tabs-mode: nil -*- +# +# The contents of this file are subject to the Mozilla Public +# License Version 1.1 (the "License"); you may not use this file +# except in compliance with the License. You may obtain a copy of +# the License at http://www.mozilla.org/MPL/ +# +# Software distributed under the License is distributed on an "AS +# IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or +# implied. See the License for the specific language governing +# rights and limitations under the License. +# +# The Original Code is the Bugzilla Bug Tracking System. +# +# The Initial Developer of the Original Code is Netscape Communications +# Corporation. Portions created by Netscape are +# Copyright (C) 1998 Netscape Communications Corporation. All +# Rights Reserved. +# +# Contributor(s): Dave Miller +# Gayathri Swaminath +# Jeroen Ruigrok van der Werven +# Dave Lawrence +# Tomas Kopal +# Max Kanat-Alexander +# Lance Larsh + +=head1 NAME + +Bugzilla::DB::Mysql - Bugzilla database compatibility layer for MySQL + +=head1 DESCRIPTION + +This module overrides methods of the Bugzilla::DB module with MySQL specific +implementation. It is instantiated by the Bugzilla::DB module and should never +be used directly. + +For interface details see L and L. + +=cut + +package Bugzilla::DB::Mysql; + +use strict; + +use Bugzilla::Constants; +use Bugzilla::Install::Util qw(install_string); +use Bugzilla::Util; +use Bugzilla::Error; +use Bugzilla::DB::Schema::Mysql; + +use List::Util qw(max); + +# This is how many comments of MAX_COMMENT_LENGTH we expect on a single bug. +# In reality, you could have a LOT more comments than this, because +# MAX_COMMENT_LENGTH is big. +use constant MAX_COMMENTS => 50; + +# This module extends the DB interface via inheritance +use base qw(Bugzilla::DB); + +sub new { + my ($class, $user, $pass, $host, $dbname, $port, $sock) = @_; + + # construct the DSN from the parameters we got + my $dsn = "DBI:mysql:host=$host;database=$dbname"; + $dsn .= ";port=$port" if $port; + $dsn .= ";mysql_socket=$sock" if $sock; + + my $attrs = { mysql_enable_utf8 => Bugzilla->params->{'utf8'} }; + + my $self = $class->db_new($dsn, $user, $pass, $attrs); + + # This makes sure that if the tables are encoded as UTF-8, we + # return their data correctly. + $self->do("SET NAMES utf8") if Bugzilla->params->{'utf8'}; + + # all class local variables stored in DBI derived class needs to have + # a prefix 'private_'. See DBI documentation. + $self->{private_bz_tables_locked} = ""; + + bless ($self, $class); + + # Bug 321645 - disable MySQL strict mode, if set + my ($var, $sql_mode) = $self->selectrow_array( + "SHOW VARIABLES LIKE 'sql\\_mode'"); + + if ($sql_mode) { + # STRICT_TRANS_TABLE or STRICT_ALL_TABLES enable MySQL strict mode, + # causing bug 321645. TRADITIONAL sets these modes (among others) as + # well, so it has to be stipped as well + my $new_sql_mode = + join(",", grep {$_ !~ /^STRICT_(?:TRANS|ALL)_TABLES|TRADITIONAL$/} + split(/,/, $sql_mode)); + + if ($sql_mode ne $new_sql_mode) { + $self->do("SET SESSION sql_mode = ?", undef, $new_sql_mode); + } + } + + # Allow large GROUP_CONCATs (largely for inserting comments + # into bugs_fulltext). + $self->do('SET SESSION group_concat_max_len = 128000000'); + + return $self; +} + +# when last_insert_id() is supported on MySQL by lowest DBI/DBD version +# required by Bugzilla, this implementation can be removed. +sub bz_last_key { + my ($self) = @_; + + my ($last_insert_id) = $self->selectrow_array('SELECT LAST_INSERT_ID()'); + + return $last_insert_id; +} + +sub sql_group_concat { + my ($self, $column, $separator) = @_; + my $sep_sql; + if ($separator) { + $sep_sql = " SEPARATOR $separator"; + } + return "GROUP_CONCAT($column$sep_sql)"; +} + +sub sql_regexp { + my ($self, $expr, $pattern) = @_; + + return "$expr REGEXP $pattern"; +} + +sub sql_not_regexp { + my ($self, $expr, $pattern) = @_; + + return "$expr NOT REGEXP $pattern"; +} + +sub sql_limit { + my ($self, $limit, $offset) = @_; + + if (defined($offset)) { + return "LIMIT $offset, $limit"; + } else { + return "LIMIT $limit"; + } +} + +sub sql_string_concat { + my ($self, @params) = @_; + + return 'CONCAT(' . join(', ', @params) . ')'; +} + +sub sql_fulltext_search { + my ($self, $column, $text) = @_; + + # Add the boolean mode modifier if the search string contains + # boolean operators. + my $mode = ($text =~ /[+\-<>()~*"]/ ? "IN BOOLEAN MODE" : ""); + + # quote the text for use in the MATCH AGAINST expression + $text = $self->quote($text); + + # untaint the text, since it's safe to use now that we've quoted it + trick_taint($text); + + return "MATCH($column) AGAINST($text $mode)"; +} + +sub sql_istring { + my ($self, $string) = @_; + + return $string; +} + +sub sql_from_days { + my ($self, $days) = @_; + + return "FROM_DAYS($days)"; +} + +sub sql_to_days { + my ($self, $date) = @_; + + return "TO_DAYS($date)"; +} + +sub sql_date_format { + my ($self, $date, $format) = @_; + + $format = "%Y.%m.%d %H:%i:%s" if !$format; + + return "DATE_FORMAT($date, " . $self->quote($format) . ")"; +} + +sub sql_interval { + my ($self, $interval, $units) = @_; + + return "INTERVAL $interval $units"; +} + +sub sql_iposition { + my ($self, $fragment, $text) = @_; + return "INSTR($text, $fragment)"; +} + +sub sql_position { + my ($self, $fragment, $text) = @_; + + return "INSTR(CAST($text AS BINARY), CAST($fragment AS BINARY))"; +} + +sub sql_group_by { + my ($self, $needed_columns, $optional_columns) = @_; + + # MySQL allows you to specify the minimal subset of columns to get + # a unique result. While it does allow specifying all columns as + # ANSI SQL requires, according to MySQL documentation, the fewer + # columns you specify, the faster the query runs. + return "GROUP BY $needed_columns"; +} + + +sub _bz_get_initial_schema { + my ($self) = @_; + return $self->_bz_build_schema_from_disk(); +} + +##################################################################### +# Database Setup +##################################################################### + +sub bz_setup_database { + my ($self) = @_; + + # The "comments" field of the bugs_fulltext table could easily exceed + # MySQL's default max_allowed_packet. Also, MySQL should never have + # a max_allowed_packet smaller than our max_attachment_size. So, we + # warn the user here if max_allowed_packet is too small. + my $min_max_allowed = MAX_COMMENTS * MAX_COMMENT_LENGTH; + my (undef, $current_max_allowed) = $self->selectrow_array( + q{SHOW VARIABLES LIKE 'max\_allowed\_packet'}); + # This parameter is not yet defined when the DB is being built for + # the very first time. The code below still works properly, however, + # because the default maxattachmentsize is smaller than $min_max_allowed. + my $max_attachment = (Bugzilla->params->{'maxattachmentsize'} || 0) * 1024; + my $needed_max_allowed = max($min_max_allowed, $max_attachment); + if ($current_max_allowed < $needed_max_allowed) { + warn install_string('max_allowed_packet', + { current => $current_max_allowed, + needed => $needed_max_allowed }) . "\n"; + } + + # Make sure the installation has InnoDB turned on, or we're going to be + # doing silly things like making foreign keys on MyISAM tables, which is + # hard to fix later. We do this up here because none of the code below + # works if InnoDB is off. (Particularly if we've already converted the + # tables to InnoDB.) + my ($innodb_on) = @{$self->selectcol_arrayref( + q{SHOW VARIABLES LIKE '%have_innodb%'}, {Columns=>[2]})}; + if ($innodb_on ne 'YES') { + print <selectall_arrayref("SHOW TABLE STATUS"); + my @isam_tables; + foreach my $row (@$table_status) { + my ($name, $type) = @$row; + push(@isam_tables, $name) if $type eq "ISAM"; + } + + if(scalar(@isam_tables)) { + print "One or more of the tables in your existing MySQL database are\n" + . "of type ISAM. ISAM tables are deprecated in MySQL 3.23 and\n" + . "don't support more than 16 indexes per table, which \n" + . "Bugzilla needs.\n Converting your ISAM tables to type" + . " MyISAM:\n\n"; + foreach my $table (@isam_tables) { + print "Converting table $table... "; + $self->do("ALTER TABLE $table TYPE = MYISAM"); + print "done.\n"; + } + print "\nISAM->MyISAM table conversion done.\n\n"; + } + + my ($sd_index_deleted, $longdescs_index_deleted); + my @tables = $self->bz_table_list_real(); + # We want to convert tables to InnoDB, but it's possible that they have + # fulltext indexes on them, and conversion will fail unless we remove + # the indexes. + if (grep($_ eq 'bugs', @tables)) { + if ($self->bz_index_info_real('bugs', 'short_desc')) { + $self->bz_drop_index_raw('bugs', 'short_desc'); + } + if ($self->bz_index_info_real('bugs', 'bugs_short_desc_idx')) { + $self->bz_drop_index_raw('bugs', 'bugs_short_desc_idx'); + $sd_index_deleted = 1; # Used for later schema cleanup. + } + } + if (grep($_ eq 'longdescs', @tables)) { + if ($self->bz_index_info_real('longdescs', 'thetext')) { + $self->bz_drop_index_raw('longdescs', 'thetext'); + } + if ($self->bz_index_info_real('longdescs', 'longdescs_thetext_idx')) { + $self->bz_drop_index_raw('longdescs', 'longdescs_thetext_idx'); + $longdescs_index_deleted = 1; # For later schema cleanup. + } + } + + # Upgrade tables from MyISAM to InnoDB + my @myisam_tables; + foreach my $row (@$table_status) { + my ($name, $type) = @$row; + if ($type =~ /^MYISAM$/i + && !grep($_ eq $name, Bugzilla::DB::Schema::Mysql::MYISAM_TABLES)) + { + push(@myisam_tables, $name) ; + } + } + if (scalar @myisam_tables) { + print "Bugzilla now uses the InnoDB storage engine in MySQL for", + " most tables.\nConverting tables to InnoDB:\n"; + foreach my $table (@myisam_tables) { + print "Converting table $table... "; + $self->do("ALTER TABLE $table TYPE = InnoDB"); + print "done.\n"; + } + } + + $self->_after_table_status(\@tables); + + # 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. + if (grep($_ eq 'bugs', @tables) + && ($self->bz_index_info_real('bugs', 'assigned_to') + || $self->bz_index_info_real('flags', 'flags_bidattid_idx')) ) + { + + # This is a check unrelated to the indexes, to see if people are + # upgrading from 2.18 or below, but somehow have a bz_schema table + # already. This only happens if they have done a mysqldump into + # a database without doing a DROP DATABASE first. + # We just do the check here since this check is a reliable way + # of telling that we are upgrading from a version pre-2.20. + if (grep($_ eq 'bz_schema', $self->bz_table_list_real())) { + die("\nYou are upgrading from a version before 2.20, but the" + . " bz_schema\ntable already exists. This means that you" + . " restored a mysqldump into\nthe Bugzilla database without" + . " first dropping the already-existing\nBugzilla database," + . " at some point. Whenever you restore a Bugzilla\ndatabase" + . " backup, you must always drop the entire database first.\n\n" + . "Please drop your Bugzilla database and restore it from a" + . " backup that\ndoes not contain the bz_schema table. If for" + . " some reason you cannot\ndo this, you can connect to your" + . " MySQL database and drop the bz_schema\ntable, as a last" + . " resort.\n"); + } + + 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; + # And 45 minutes for every 15,000 attachments, per some experiments. + my ($attachment_count) = + $self->selectrow_array("SELECT COUNT(*) FROM attachments"); + $rename_time += int(($attachment_count * 45) / 15000); + # 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) unless Bugzilla->installation_answers->{NO_PAUSE}; + } + 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. + # Also, sometimes an old mysqldump would incorrectly rename + # unique indexes to "PRIMARY", so we address that here, also. + my $bad_names = { + # 'when' is a possible leftover from Bugzillas before 2.8 + bugs_activity => ['when', 'bugs_activity_bugid_idx', + 'bugs_activity_bugwhen_idx'], + cc => ['PRIMARY'], + longdescs => ['longdescs_bugid_idx', + 'longdescs_bugwhen_idx'], + flags => ['flags_bidattid_idx'], + flaginclusions => ['flaginclusions_tpcid_idx'], + flagexclusions => ['flagexclusions_tpc_id_idx'], + keywords => ['PRIMARY'], + milestones => ['PRIMARY'], + profiles_activity => ['profiles_activity_when_idx'], + group_control_map => ['group_control_map_gid_idx', 'PRIMARY'], + user_group_map => ['PRIMARY'], + group_group_map => ['PRIMARY'], + email_setting => ['PRIMARY'], + bug_group_map => ['PRIMARY'], + category_group_map => ['PRIMARY'], + watch => ['PRIMARY'], + namedqueries => ['PRIMARY'], + series_data => ['PRIMARY'], + # 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'; + } + $self->bz_drop_index_raw('series', $dropname) if $dropname; + } + + # 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') ) + { + $self->bz_drop_index_raw('email_setting', + 'email_settings_user_id_idx'); + } + + # Go through all the tables. + foreach my $table (@tables) { + # Will contain the names of old indexes as keys, and the + # definition of the new indexes as a value. The values + # include an extra hash key, NAME, with the new name of + # the index. + my %rename_indexes; + # 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. + if (my $index = $self->bz_index_info_real($table, $column)) { + # Fix the name to fit in with the new naming scheme. + $index->{NAME} = $table . "_" . + $index->{FIELDS}->[0] . "_idx"; + print "Renaming index $column to " + . $index->{NAME} . "...\n"; + $rename_indexes{$column} = $index; + } # if + } # foreach column + + my @rename_sql = $self->_bz_schema->get_rename_indexes_ddl( + $table, %rename_indexes); + $self->do($_) foreach (@rename_sql); + + } # foreach table + } # if old-name indexes + + # If there are no tables, but the DB isn't utf8 and it should be, + # then we should alter the database to be utf8. We know it should be + # if the utf8 parameter is true or there are no params at all. + # This kind of situation happens when people create the database + # themselves, and if we don't do this they will get the big + # scary WARNING statement about conversion to UTF8. + if ( !$self->bz_db_is_utf8 && !@tables + && (Bugzilla->params->{'utf8'} || !scalar keys %{Bugzilla->params}) ) + { + $self->_alter_db_charset_to_utf8(); + } + + # And now we create the tables and the Schema object. + $self->SUPER::bz_setup_database(); + + if ($sd_index_deleted) { + $self->_bz_real_schema->delete_index('bugs', 'bugs_short_desc_idx'); + $self->_bz_store_real_schema; + } + if ($longdescs_index_deleted) { + $self->_bz_real_schema->delete_index('longdescs', + 'longdescs_thetext_idx'); + $self->_bz_store_real_schema; + } + + # The old timestamp fields need to be adjusted here instead of in + # checksetup. Otherwise the UPDATE statements inside of bz_add_column + # will cause accidental timestamp updates. + # The code that does this was moved here from checksetup. + + # 2002-08-14 - bbaetz@student.usyd.edu.au - bug 153578 + # attachments creation time needs to be a datetime, not a timestamp + my $attach_creation = + $self->bz_column_info("attachments", "creation_ts"); + if ($attach_creation && $attach_creation->{TYPE} =~ /^TIMESTAMP/i) { + print "Fixing creation time on attachments...\n"; + + my $sth = $self->prepare("SELECT COUNT(attach_id) FROM attachments"); + $sth->execute(); + my ($attach_count) = $sth->fetchrow_array(); + + if ($attach_count > 1000) { + print "This may take a while...\n"; + } + my $i = 0; + + # This isn't just as simple as changing the field type, because + # the creation_ts was previously updated when an attachment was made + # obsolete from the attachment creation screen. So we have to go + # and recreate these times from the comments.. + $sth = $self->prepare("SELECT bug_id, attach_id, submitter_id " . + "FROM attachments"); + $sth->execute(); + + # Restrict this as much as possible in order to avoid false + # positives, and keep the db search time down + my $sth2 = $self->prepare("SELECT bug_when FROM longdescs + WHERE bug_id=? AND who=? + AND thetext LIKE ? + ORDER BY bug_when " . $self->sql_limit(1)); + while (my ($bug_id, $attach_id, $submitter_id) + = $sth->fetchrow_array()) + { + $sth2->execute($bug_id, $submitter_id, + "Created an attachment (id=$attach_id)%"); + my ($when) = $sth2->fetchrow_array(); + if ($when) { + $self->do("UPDATE attachments " . + "SET creation_ts='$when' " . + "WHERE attach_id=$attach_id"); + } else { + print "Warning - could not determine correct creation" + . " time for attachment $attach_id on bug $bug_id\n"; + } + ++$i; + print "Converted $i of $attach_count attachments\n" if !($i % 1000); + } + print "Done - converted $i attachments\n"; + + $self->bz_alter_column("attachments", "creation_ts", + {TYPE => 'DATETIME', NOTNULL => 1}); + } + + # 2004-08-29 - Tomas.Kopal@altap.cz, bug 257303 + # Change logincookies.lastused type from timestamp to datetime + my $login_lastused = $self->bz_column_info("logincookies", "lastused"); + if ($login_lastused && $login_lastused->{TYPE} =~ /^TIMESTAMP/i) { + $self->bz_alter_column('logincookies', 'lastused', + { TYPE => 'DATETIME', NOTNULL => 1}); + } + + # 2005-01-17 - Tomas.Kopal@altap.cz, bug 257315 + # Change bugs.delta_ts type from timestamp to datetime + my $bugs_deltats = $self->bz_column_info("bugs", "delta_ts"); + if ($bugs_deltats && $bugs_deltats->{TYPE} =~ /^TIMESTAMP/i) { + $self->bz_alter_column('bugs', 'delta_ts', + {TYPE => 'DATETIME', NOTNULL => 1}); + } + + # 2005-09-24 - bugreport@peshkin.net, bug 307602 + # Make sure that default 4G table limit is overridden + my $row = $self->selectrow_hashref("SHOW TABLE STATUS LIKE 'attach_data'"); + if ($$row{'Create_options'} !~ /MAX_ROWS/i) { + print "Converting attach_data maximum size to 100G...\n"; + $self->do("ALTER TABLE attach_data + AVG_ROW_LENGTH=1000000, + MAX_ROWS=100000"); + } + + # Convert the database to UTF-8 if the utf8 parameter is on. + # We check if any table isn't utf8, because lots of crazy + # partial-conversion situations can happen, and this handles anything + # that could come up (including having the DB charset be utf8 but not + # the table charsets. + my $utf_table_status = + $self->selectall_arrayref("SHOW TABLE STATUS", {Slice=>{}}); + $self->_after_table_status([map($_->{Name}, @$utf_table_status)]); + my @non_utf8_tables = grep($_->{Collation} !~ /^utf8/, @$utf_table_status); + + if (Bugzilla->params->{'utf8'} && scalar @non_utf8_tables) { + print <installation_answers->{NO_PAUSE}) { + if (Bugzilla->installation_mode == + INSTALLATION_MODE_NON_INTERACTIVE) + { + print <bz_table_list_real) { + my $info_sth = $self->prepare("SHOW FULL COLUMNS FROM $table"); + $info_sth->execute(); + while (my $column = $info_sth->fetchrow_hashref) { + # Our conversion code doesn't work on enum fields, but they + # all go away later in checksetup anyway. + next if $column->{Type} =~ /enum/i; + + # If this particular column isn't stored in utf-8 + if ($column->{Collation} + && $column->{Collation} ne 'NULL' + && $column->{Collation} !~ /utf8/) + { + my $name = $column->{Field}; + + # The code below doesn't work on a field with a FULLTEXT + # index. So we drop it, which we'd do later anyway. + if ($table eq 'longdescs' && $name eq 'thetext') { + $self->bz_drop_index('longdescs', + 'longdescs_thetext_idx'); + } + if ($table eq 'bugs' && $name eq 'short_desc') { + $self->bz_drop_index('bugs', 'bugs_short_desc_idx'); + } + my %ft_indexes; + if ($table eq 'bugs_fulltext') { + %ft_indexes = $self->_bz_real_schema->get_indexes_on_column_abstract( + 'bugs_fulltext', $name); + foreach my $index (keys %ft_indexes) { + $self->bz_drop_index('bugs_fulltext', $index); + } + } + + print "Converting $table.$name to be stored as UTF-8...\n"; + my $col_info = + $self->bz_column_info_real($table, $name); + + # CHANGE COLUMN doesn't take PRIMARY KEY + delete $col_info->{PRIMARYKEY}; + + my $sql_def = $self->_bz_schema->get_type_ddl($col_info); + # We don't want MySQL to actually try to *convert* + # from our current charset to UTF-8, we just want to + # transfer the bytes directly. This is how we do that. + + # The CHARACTER SET part of the definition has to come + # right after the type, which will always come first. + my ($binary, $utf8) = ($sql_def, $sql_def); + my $type = $self->_bz_schema->convert_type($col_info->{TYPE}); + $binary =~ s/(\Q$type\E)/$1 CHARACTER SET binary/; + $utf8 =~ s/(\Q$type\E)/$1 CHARACTER SET utf8/; + $self->do("ALTER TABLE $table CHANGE COLUMN $name $name + $binary"); + $self->do("ALTER TABLE $table CHANGE COLUMN $name $name + $utf8"); + + if ($table eq 'bugs_fulltext') { + foreach my $index (keys %ft_indexes) { + $self->bz_add_index('bugs_fulltext', $index, + $ft_indexes{$index}); + } + } + } + } + + $self->do("ALTER TABLE $table DEFAULT CHARACTER SET utf8"); + } # foreach my $table (@tables) + } + + # Sometimes you can have a situation where all the tables are utf8, + # but the database isn't. (This tends to happen when you've done + # a mysqldump.) So we have this change outside of the above block, + # so that it just happens silently if no actual *table* conversion + # needs to happen. + if (Bugzilla->params->{'utf8'} && !$self->bz_db_is_utf8) { + $self->_alter_db_charset_to_utf8(); + } +} + +# There is a bug in MySQL 4.1.0 - 4.1.15 that makes certain SELECT +# statements fail after a SHOW TABLE STATUS: +# http://bugs.mysql.com/bug.php?id=13535 +# This is a workaround, a dummy SELECT to reset the LAST_INSERT_ID. +sub _after_table_status { + my ($self, $tables) = @_; + if (grep($_ eq 'bugs', @$tables) + && $self->bz_column_info_real("bugs", "bug_id")) + { + $self->do('SELECT 1 FROM bugs WHERE bug_id IS NULL'); + } +} + +sub _alter_db_charset_to_utf8 { + my $self = shift; + my $db_name = Bugzilla->localconfig->{db_name}; + $self->do("ALTER DATABASE $db_name CHARACTER SET utf8"); +} + +sub bz_db_is_utf8 { + my $self = shift; + my $db_collation = $self->selectrow_arrayref( + "SHOW VARIABLES LIKE 'character_set_database'"); + # First column holds the variable name, second column holds the value. + return $db_collation->[1] =~ /utf8/ ? 1 : 0; +} + + +sub bz_enum_initial_values { + my ($self) = @_; + my %enum_values = %{$self->ENUM_DEFAULTS}; + # Get a complete description of the 'bugs' table; with DBD::MySQL + # there isn't a column-by-column way of doing this. Could use + # $dbh->column_info, but it would go slower and we would have to + # use the undocumented mysql_type_name accessor to get the type + # of each row. + my $sth = $self->prepare("DESCRIBE bugs"); + $sth->execute(); + # Look for the particular columns we are interested in. + while (my ($thiscol, $thistype) = $sth->fetchrow_array()) { + if (defined $enum_values{$thiscol}) { + # this is a column of interest. + my @value_list; + if ($thistype and ($thistype =~ /^enum\(/)) { + # it has an enum type; get the set of values. + while ($thistype =~ /'([^']*)'(.*)/) { + push(@value_list, $1); + $thistype = $2; + } + } + if (@value_list) { + # record the enum values found. + $enum_values{$thiscol} = \@value_list; + } + } + } + + return \%enum_values; +} + +##################################################################### +# MySQL-specific Database-Reading Methods +##################################################################### + +=begin private + +=head1 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 + + Description: Returns an abstract column definition for a column + as it actually exists on disk in the database. + Params: $table - The name of the table the column is on. + $column - The name of the column you want info about. + Returns: An abstract column definition. + If the column does not exist, returns undef. + +=cut + +sub bz_column_info_real { + my ($self, $table, $column) = @_; + + # DBD::mysql does not support selecting a specific column, + # so we have to get all the columns on the table and find + # the one we want. + my $info_sth = $self->column_info(undef, undef, $table, '%'); + + # Don't use fetchall_hashref as there's a Win32 DBI bug (292821) + my $col_data; + while ($col_data = $info_sth->fetchrow_hashref) { + last if $col_data->{'COLUMN_NAME'} eq $column; + } + + if (!defined $col_data) { + return undef; + } + return $self->_bz_schema->column_info_to_column($col_data); +} + +=item C + + 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. + # 9 = Null. Sometimes undef, sometimes 'YES'. + # 10 = Index_type. The type of the index. Usually either 'BTREE' or 'FULLTEXT' + # 11 = 'Comment.' Usually undef. + 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->[10] eq 'FULLTEXT' + ? 'FULLTEXT' : $index_type; + } + } + + my $retval; + if (scalar(@fields)) { + $retval = {FIELDS => \@fields, TYPE => $index_type}; + } + return $retval; +} + +=item C + + Description: Returns a list of index names on a table in + the database, as it actually exists on disk. + Params: $table - The name of the table you want info about. + Returns: An array of index names. + +=cut + +sub bz_index_list_real { + my ($self, $table) = @_; + my $sth = $self->prepare("SHOW INDEX FROM $table"); + # Column 3 of a SHOW INDEX statement contains the name of the index. + return @{ $self->selectcol_arrayref($sth, {Columns => [3]}) }; +} + +##################################################################### +# MySQL-Specific "Schema Builder" +##################################################################### + +=back + +=head1 MYSQL-SPECIFIC "SCHEMA BUILDER" + +MySQL needs to be able to read in a legacy database (from before +Schema existed) and create a Schema object out of it. That's what +this code does. + +=end private + +=cut + +# This sub itself is actually written generically, but the subroutines +# that it depends on are database-specific. In particular, the +# bz_column_info_real function would be very difficult to create +# properly for any other DB besides MySQL. +sub _bz_build_schema_from_disk { + my ($self) = @_; + + print "Building Schema object from database...\n"; + + my $schema = $self->_bz_schema->get_empty_schema(); + + my @tables = $self->bz_table_list_real(); + foreach my $table (@tables) { + $schema->add_table($table); + my @columns = $self->bz_table_columns_real($table); + foreach my $column (@columns) { + my $type_info = $self->bz_column_info_real($table, $column); + $schema->set_column($table, $column, $type_info); + } + + my @indexes = $self->bz_index_list_real($table); + foreach my $index (@indexes) { + unless ($index eq 'PRIMARY') { + my $index_info = $self->bz_index_info_real($table, $index); + ($index_info = $index_info->{FIELDS}) + if (!$index_info->{TYPE}); + $schema->set_index($table, $index, $index_info); + } + } + } + + return $schema; +} +1;