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