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