# -*- 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 <davem00@aol.com>#                 Gayathri Swaminath <gayathrik00@aol.com>#                 Jeroen Ruigrok van der Werven <asmodai@wxs.nl>#                 Dave Lawrence <dkl@redhat.com>#                 Tomas Kopal <Tomas.Kopal@altap.cz>#                 Max Kanat-Alexander <mkanat@bugzilla.org>#                 Lance Larsh <lance.larsh@oracle.com>=head1 NAMEBugzilla::DB::Mysql - Bugzilla database compatibility layer for MySQL=head1 DESCRIPTIONThis module overrides methods of the Bugzilla::DB module with MySQL specificimplementation. It is instantiated by the Bugzilla::DB module and should neverbe used directly.For interface details see L<Bugzilla::DB> and L<DBI>.=cutpackage Bugzilla::DB::Mysql;use strict;use Bugzilla::Constants;use Bugzilla::Util;use Bugzilla::Error;# This module extends the DB interface via inheritanceuse 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 $self = $class->db_new($dsn, $user, $pass);    # 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);        }    }    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_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_position {    my ($self, $fragment, $text) = @_;    # mysql 4.0.1 and lower do not support CAST    # (checksetup has a check for unsupported versions)    my $server_version = $self->bz_server_version;    return "INSTR(CAST($text AS BINARY), CAST($fragment AS BINARY))";}sub sql_group_by {    my ($self, $needed_columns, $optional_columns) = @_;    # MySQL allows to specify all columns as ANSI SQL requires, but also    # allow you to specify just minimal subset to get unique result.    # According to MySQL documentation, the less columns you specify    # the faster the query runs.    return "GROUP BY $needed_columns";}sub bz_lock_tables {    my ($self, @tables) = @_;    my $list = join(', ', @tables);    # Check first if there was no lock before    if ($self->{private_bz_tables_locked}) {        ThrowCodeError("already_locked", { current => $self->{private_bz_tables_locked},                                           new => $list });    } else {        $self->do('LOCK TABLE ' . $list);             $self->{private_bz_tables_locked} = $list;    }}sub bz_unlock_tables {    my ($self, $abort) = @_;        # Check first if there was previous matching lock    if (!$self->{private_bz_tables_locked}) {        # Abort is allowed even without previous lock for error handling        return if $abort;        ThrowCodeError("no_matching_lock");    } else {        $self->do("UNLOCK TABLES");            $self->{private_bz_tables_locked} = "";    }}# As Bugzilla currently runs on MyISAM storage, which does not support# transactions, these functions die when called.# Maybe we should just ignore these calls for now, but as we are not# using transactions in MySQL yet, this just hints the developers.sub bz_start_transaction {    die("Attempt to start transaction on DB without transaction support");}sub bz_commit_transaction {    die("Attempt to commit transaction on DB without transaction support");}sub bz_rollback_transaction {    die("Attempt to rollback transaction on DB without transaction support");}sub _bz_get_initial_schema {    my ($self) = @_;    return $self->_bz_build_schema_from_disk();}###################################################################### Database Setup#####################################################################sub bz_setup_database {    my ($self) = @_;    # Figure out if any existing tables are of type ISAM and convert them    # to type MyISAM if so.  ISAM tables are deprecated in MySQL 3.23,    # which Bugzilla now requires, and they don't support more than 16    # indexes per table, which Bugzilla needs.    my $sth = $self->prepare("SHOW TABLE STATUS");    $sth->execute;    my @isam_tables = ();    while (my ($name, $type) = $sth->fetchrow_array) {        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 @tables = $self->bz_table_list_real();    $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.