mysql.pm

bugzilla

                exit;
            }
            else {
                print "         Press Enter to continue or Ctrl-C to exit...";
                getc;
            }
        }

        print "Converting table storage format to UTF-8. This may take a",
              " while.\n";
        foreach my $table ($self->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. The upgrade code will re-create
                    # it later.
                    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');
                    }

                    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");
                }
            }

            $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<bz_column_info_real($table, $column)>

 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<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.
    # 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<bz_index_list_real($table)>

 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;