1# This Source Code Form is subject to the terms of the Mozilla Public
2# License, v. 2.0. If a copy of the MPL was not distributed with this
3# file, You can obtain one at http://mozilla.org/MPL/2.0/.
4#
5# This Source Code Form is "Incompatible With Secondary Licenses", as
6# defined by the Mozilla Public License, v. 2.0.
7
8package Bugzilla::DB::Schema::Mysql;
9
10###############################################################################
11#
12# DB::Schema implementation for MySQL
13#
14###############################################################################
15
16use strict;
17use Bugzilla::Error;
18
19use base qw(Bugzilla::DB::Schema);
20
21# This is for column_info_to_column, to know when a tinyint is a
22# boolean and when it's really a tinyint. This only has to be accurate
23# up to and through 2.19.3, because that's the only time we need
24# column_info_to_column.
25#
26# This is basically a hash of tables/columns, with one entry for each column
27# that should be interpreted as a BOOLEAN instead of as an INT1 when
28# reading in the Schema from the disk. The values are discarded; I just
29# used "1" for simplicity.
30#
31# THIS CONSTANT IS ONLY USED FOR UPGRADES FROM 2.18 OR EARLIER. DON'T
32# UPDATE IT TO MODERN COLUMN NAMES OR DEFINITIONS.
33use constant BOOLEAN_MAP => {
34    bugs           => {everconfirmed => 1, reporter_accessible => 1,
35                       cclist_accessible => 1, qacontact_accessible => 1,
36                       assignee_accessible => 1},
37    longdescs      => {isprivate => 1, already_wrapped => 1},
38    attachments    => {ispatch => 1, isobsolete => 1, isprivate => 1},
39    flags          => {is_active => 1},
40    flagtypes      => {is_active => 1, is_requestable => 1,
41                       is_requesteeble => 1, is_multiplicable => 1},
42    fielddefs      => {mailhead => 1, obsolete => 1},
43    bug_status     => {isactive => 1},
44    resolution     => {isactive => 1},
45    bug_severity   => {isactive => 1},
46    priority       => {isactive => 1},
47    rep_platform   => {isactive => 1},
48    op_sys         => {isactive => 1},
49    profiles       => {mybugslink => 1, newemailtech => 1},
50    namedqueries   => {linkinfooter => 1, watchfordiffs => 1},
51    groups         => {isbuggroup => 1, isactive => 1},
52    group_control_map => {entry => 1, membercontrol => 1, othercontrol => 1,
53                          canedit => 1},
54    group_group_map => {isbless => 1},
55    user_group_map => {isbless => 1, isderived => 1},
56    products       => {disallownew => 1},
57    series         => {public => 1},
58    whine_queries  => {onemailperbug => 1},
59    quips          => {approved => 1},
60    setting        => {is_enabled => 1}
61};
62
63# Maps the db_specific hash backwards, for use in column_info_to_column.
64use constant REVERSE_MAPPING => {
65    # Boolean and the SERIAL fields are handled in column_info_to_column,
66    # and so don't have an entry here.
67    TINYINT   => 'INT1',
68    SMALLINT  => 'INT2',
69    MEDIUMINT => 'INT3',
70    INTEGER   => 'INT4',
71
72    # All the other types have the same name in their abstract version
73    # as in their db-specific version, so no reverse mapping is needed.
74};
75
76use constant MYISAM_TABLES => qw(bugs_fulltext);
77
78#------------------------------------------------------------------------------
79sub _initialize {
80
81    my $self = shift;
82
83    $self = $self->SUPER::_initialize(@_);
84
85    $self->{db_specific} = {
86
87        BOOLEAN =>      'tinyint',
88        FALSE =>        '0',
89        TRUE =>         '1',
90
91        INT1 =>         'tinyint',
92        INT2 =>         'smallint',
93        INT3 =>         'mediumint',
94        INT4 =>         'integer',
95
96        SMALLSERIAL =>  'smallint auto_increment',
97        MEDIUMSERIAL => 'mediumint auto_increment',
98        INTSERIAL =>    'integer auto_increment',
99
100        TINYTEXT =>     'tinytext',
101        MEDIUMTEXT =>   'mediumtext',
102        LONGTEXT =>     'mediumtext',
103
104        LONGBLOB =>     'longblob',
105
106        DATETIME =>     'datetime',
107
108    };
109
110    $self->_adjust_schema;
111
112    return $self;
113
114} #eosub--_initialize
115#------------------------------------------------------------------------------
116sub _get_create_table_ddl {
117    # Extend superclass method to specify the MYISAM storage engine.
118    # Returns a "create table" SQL statement.
119
120    my($self, $table) = @_;
121
122    my $charset = Bugzilla->dbh->bz_db_is_utf8 ? "CHARACTER SET utf8" : '';
123    my $type    = grep($_ eq $table, MYISAM_TABLES) ? 'MYISAM' : 'InnoDB';
124    return($self->SUPER::_get_create_table_ddl($table)
125           . " ENGINE = $type $charset");
126
127} #eosub--_get_create_table_ddl
128#------------------------------------------------------------------------------
129sub _get_create_index_ddl {
130    # Extend superclass method to create FULLTEXT indexes on text fields.
131    # Returns a "create index" SQL statement.
132
133    my($self, $table_name, $index_name, $index_fields, $index_type) = @_;
134
135    my $sql = "CREATE ";
136    $sql .= "$index_type " if ($index_type eq 'UNIQUE'
137                               || $index_type eq 'FULLTEXT');
138    $sql .= "INDEX \`$index_name\` ON $table_name \(" .
139      join(", ", @$index_fields) . "\)";
140
141    return($sql);
142
143} #eosub--_get_create_index_ddl
144#--------------------------------------------------------------------
145
146sub get_create_database_sql {
147    my ($self, $name) = @_;
148    # We only create as utf8 if we have no params (meaning we're doing
149    # a new installation) or if the utf8 param is on.
150    my $create_utf8 = Bugzilla->params->{'utf8'}
151                      || !defined Bugzilla->params->{'utf8'};
152    my $charset = $create_utf8 ? "CHARACTER SET utf8" : '';
153    return ("CREATE DATABASE $name $charset");
154}
155
156# MySQL has a simpler ALTER TABLE syntax than ANSI.
157sub get_alter_column_ddl {
158    my ($self, $table, $column, $new_def, $set_nulls_to) = @_;
159    my $old_def = $self->get_column($table, $column);
160    my %new_def_copy = %$new_def;
161    if ($old_def->{PRIMARYKEY} && $new_def->{PRIMARYKEY}) {
162        # If a column stays a primary key do NOT specify PRIMARY KEY in the
163        # ALTER TABLE statement. This avoids a MySQL error that two primary
164        # keys are not allowed.
165        delete $new_def_copy{PRIMARYKEY};
166    }
167
168    my @statements;
169
170    push(@statements, "UPDATE $table SET $column = $set_nulls_to
171                        WHERE $column IS NULL") if defined $set_nulls_to;
172
173    # Calling SET DEFAULT or DROP DEFAULT is *way* faster than calling
174    # CHANGE COLUMN, so just do that if we're just changing the default.
175    my %old_defaultless = %$old_def;
176    my %new_defaultless = %$new_def;
177    delete $old_defaultless{DEFAULT};
178    delete $new_defaultless{DEFAULT};
179    if (!$self->columns_equal($old_def, $new_def)
180        && $self->columns_equal(\%new_defaultless, \%old_defaultless))
181    {
182        if (!defined $new_def->{DEFAULT}) {
183            push(@statements,
184                 "ALTER TABLE $table ALTER COLUMN $column DROP DEFAULT");
185        }
186        else {
187            push(@statements, "ALTER TABLE $table ALTER COLUMN $column
188                               SET DEFAULT " . $new_def->{DEFAULT});
189        }
190    }
191    else {
192        my $new_ddl = $self->get_type_ddl(\%new_def_copy);
193        push(@statements, "ALTER TABLE $table CHANGE COLUMN
194                       $column $column $new_ddl");
195    }
196
197    if ($old_def->{PRIMARYKEY} && !$new_def->{PRIMARYKEY}) {
198        # Dropping a PRIMARY KEY needs an explicit DROP PRIMARY KEY
199        push(@statements, "ALTER TABLE $table DROP PRIMARY KEY");
200    }
201
202    return @statements;
203}
204
205sub get_drop_fk_sql {
206    my ($self, $table, $column, $references) = @_;
207    my $fk_name = $self->_get_fk_name($table, $column, $references);
208    my @sql = ("ALTER TABLE $table DROP FOREIGN KEY $fk_name");
209    my $dbh = Bugzilla->dbh;
210
211    # MySQL requires, and will create, an index on any column with
212    # an FK. It will name it after the fk, which we never do.
213    # So if there's an index named after the fk, we also have to delete it.
214    if ($dbh->bz_index_info_real($table, $fk_name)) {
215        push(@sql, $self->get_drop_index_ddl($table, $fk_name));
216    }
217
218    return @sql;
219}
220
221sub get_drop_index_ddl {
222    my ($self, $table, $name) = @_;
223    return ("DROP INDEX \`$name\` ON $table");
224}
225
226# A special function for MySQL, for renaming a lot of indexes.
227# Index renames is a hash, where the key is a string - the
228# old names of the index, and the value is a hash - the index
229# definition that we're renaming to, with an extra key of "NAME"
230# that contains the new index name.
231# The indexes in %indexes must be in hashref format.
232sub get_rename_indexes_ddl {
233    my ($self, $table, %indexes) = @_;
234    my @keys = keys %indexes or return ();
235
236    my $sql = "ALTER TABLE $table ";
237
238    foreach my $old_name (@keys) {
239        my $name = $indexes{$old_name}->{NAME};
240        my $type = $indexes{$old_name}->{TYPE};
241        $type ||= 'INDEX';
242        my $fields = join(',', @{$indexes{$old_name}->{FIELDS}});
243        # $old_name needs to be escaped, sometimes, because it was
244        # a reserved word.
245        $old_name = '`' . $old_name . '`';
246        $sql .= " ADD $type $name ($fields), DROP INDEX $old_name,";
247    }
248    # Remove the last comma.
249    chop($sql);
250    return ($sql);
251}
252
253sub get_set_serial_sql {
254    my ($self, $table, $column, $value) = @_;
255    return ("ALTER TABLE $table AUTO_INCREMENT = $value");
256}
257
258# Converts a DBI column_info output to an abstract column definition.
259# Expects to only be called by Bugzila::DB::Mysql::_bz_build_schema_from_disk,
260# although there's a chance that it will also work properly if called
261# elsewhere.
262sub column_info_to_column {
263    my ($self, $column_info) = @_;
264
265    # Unfortunately, we have to break Schema's normal "no database"
266    # barrier a few times in this function.
267    my $dbh = Bugzilla->dbh;
268
269    my $table = $column_info->{TABLE_NAME};
270    my $col_name = $column_info->{COLUMN_NAME};
271
272    my $column = {};
273
274    ($column->{NOTNULL} = 1) if $column_info->{NULLABLE} == 0;
275
276    if ($column_info->{mysql_is_pri_key}) {
277        # In MySQL, if a table has no PK, but it has a UNIQUE index,
278        # that index will show up as the PK. So we have to eliminate
279        # that possibility.
280        # Unfortunately, the only way to definitely solve this is
281        # to break Schema's standard of not touching the live database
282        # and check if the index called PRIMARY is on that field.
283        my $pri_index = $dbh->bz_index_info_real($table, 'PRIMARY');
284        if ( $pri_index && grep($_ eq $col_name, @{$pri_index->{FIELDS}}) ) {
285            $column->{PRIMARYKEY} = 1;
286        }
287    }
288
289    # MySQL frequently defines a default for a field even when we
290    # didn't explicitly set one. So we have to have some special
291    # hacks to determine whether or not we should actually put
292    # a default in the abstract schema for this field.
293    if (defined $column_info->{COLUMN_DEF}) {
294        # The defaults that MySQL inputs automatically are usually
295        # something that would be considered "false" by perl, either
296        # a 0 or an empty string. (Except for datetime and decimal
297        # fields, which have their own special auto-defaults.)
298        #
299        # Here's how we handle this: If it exists in the schema
300        # without a default, then we don't use the default. If it
301        # doesn't exist in the schema, then we're either going to
302        # be dropping it soon, or it's a custom end-user column, in which
303        # case having a bogus default won't harm anything.
304        my $schema_column = $self->get_column($table, $col_name);
305        unless ( (!$column_info->{COLUMN_DEF}
306                  || $column_info->{COLUMN_DEF} eq '0000-00-00 00:00:00'
307                  || $column_info->{COLUMN_DEF} eq '0.00')
308                && $schema_column
309                && !exists $schema_column->{DEFAULT}) {
310
311            my $default = $column_info->{COLUMN_DEF};
312            # Schema uses '0' for the defaults for decimal fields.
313            $default = 0 if $default =~ /^0\.0+$/;
314            # If we're not a number, we're a string and need to be
315            # quoted.
316            $default = $dbh->quote($default) if !($default =~ /^(-)?(\d+)(.\d+)?$/);
317            $column->{DEFAULT} = $default;
318        }
319    }
320
321    my $type = $column_info->{TYPE_NAME};
322
323    # Certain types of columns need the size/precision appended.
324    if ($type =~ /CHAR$/ || $type eq 'DECIMAL') {
325        # This is nicely lowercase and has the size/precision appended.
326        $type = $column_info->{mysql_type_name};
327    }
328
329    # If we're a tinyint, we could be either a BOOLEAN or an INT1.
330    # Only the BOOLEAN_MAP knows the difference.
331    elsif ($type eq 'TINYINT' && exists BOOLEAN_MAP->{$table}
332           && exists BOOLEAN_MAP->{$table}->{$col_name}) {
333        $type = 'BOOLEAN';
334        if (exists $column->{DEFAULT}) {
335            $column->{DEFAULT} = $column->{DEFAULT} ? 'TRUE' : 'FALSE';
336        }
337    }
338
339    # We also need to check if we're an auto_increment field.
340    elsif ($type =~ /INT/) {
341        # Unfortunately, the only way to do this in DBI is to query the
342        # database, so we have to break the rule here that Schema normally
343        # doesn't touch the live DB.
344        my $ref_sth = $dbh->prepare(
345            "SELECT $col_name FROM $table LIMIT 1");
346        $ref_sth->execute;
347        if ($ref_sth->{mysql_is_auto_increment}->[0]) {
348            if ($type eq 'MEDIUMINT') {
349                $type = 'MEDIUMSERIAL';
350            }
351            elsif ($type eq 'SMALLINT') {
352                $type = 'SMALLSERIAL';
353            }
354            else {
355                $type = 'INTSERIAL';
356            }
357        }
358        $ref_sth->finish;
359
360    }
361
362    # For all other db-specific types, check if they exist in
363    # REVERSE_MAPPING and use the type found there.
364    if (exists REVERSE_MAPPING->{$type}) {
365        $type = REVERSE_MAPPING->{$type};
366    }
367
368    $column->{TYPE} = $type;
369
370    #print "$table.$col_name: " . Data::Dumper->Dump([$column]) . "\n";
371
372    return $column;
373}
374
375sub get_rename_column_ddl {
376    my ($self, $table, $old_name, $new_name) = @_;
377    my $def = $self->get_type_ddl($self->get_column($table, $old_name));
378    # MySQL doesn't like having the PRIMARY KEY statement in a rename.
379    $def =~ s/PRIMARY KEY//i;
380    return ("ALTER TABLE $table CHANGE COLUMN $old_name $new_name $def");
381}
382
3831;
384