lib/SQL/Translator/Producer/MySQL.pm

   1 package SQL::Translator::Producer::MySQL;
   2
   3 # -------------------------------------------------------------------
   4 # $Id: MySQL.pm,v 1.54 2007-11-10 03:36:43 mwz444 Exp $
   5 # -------------------------------------------------------------------
   6 # Copyright (C) 2002-4 SQLFairy Authors
   7 #
   8 # This program is free software; you can redistribute it and/or
   9 # modify it under the terms of the GNU General Public License as
  10 # published by the Free Software Foundation; version 2.
  11 #
  12 # This program is distributed in the hope that it will be useful, but
  13 # WITHOUT ANY WARRANTY; without even the implied warranty of
  14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  15 # General Public License for more details.
  16 #
  17 # You should have received a copy of the GNU General Public License
  18 # along with this program; if not, write to the Free Software
  19 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
  20 # 02111-1307  USA
  21 # -------------------------------------------------------------------
  22
  23 =head1 NAME
  24
  25 SQL::Translator::Producer::MySQL - MySQL-specific producer for SQL::Translator
  26
  27 =head1 SYNOPSIS
  28
  29 Use via SQL::Translator:
  30
  31   use SQL::Translator;
  32
  33   my $t = SQL::Translator->new( parser => '...', producer => 'MySQL', '...' );
  34   $t->translate;
  35
  36 =head1 DESCRIPTION
  37
  38 This module will produce text output of the schema suitable for MySQL.
  39 There are still some issues to be worked out with syntax differences
  40 between MySQL versions 3 and 4 ("SET foreign_key_checks," character sets
  41 for fields, etc.).
  42
  43 =head2 Table Types
  44
  45 Normally the tables will be created without any explicit table type given and
  46 so will use the MySQL default.
  47
  48 Any tables involved in foreign key constraints automatically get a table type
  49 of InnoDB, unless this is overridden by setting the C<mysql_table_type> extra
  50 attribute explicitly on the table.
  51
  52 =head2 Extra attributes.
  53
  54 The producer recognises the following extra attributes on the Schema objects.
  55
  56 =over 4
  57
  58 =item field.list
  59
  60 Set the list of allowed values for Enum fields.
  61
  62 =item field.binary, field.unsigned, field.zerofill
  63
  64 Set the MySQL field options of the same name.
  65
  66 =item table.mysql_table_type
  67
  68 Set the type of the table e.g. 'InnoDB', 'MyISAM'. This will be
  69 automatically set for tables involved in foreign key constraints if it is
  70 not already set explicitly. See L<"Table Types">.
  71
  72 =item mysql_character_set
  73
  74 MySql-4.1+. Set the tables character set.
  75 Run SHOW CHARACTER SET to see list.
  76
  77 =item mysql_collate
  78
  79 MySql-4.1+. Set the tables colation order.
  80
  81 =item table.mysql_charset, table.mysql_collate
  82
  83 Set the tables default charater set and collation order.
  84
  85 =item field.mysql_charset, field.mysql_collate
  86
  87 Set the fields charater set and collation order.
  88
  89 =back
  90
  91 =cut
  92
  93 use strict;
  94 use warnings;
  95 use vars qw[ $VERSION $DEBUG %used_names ];
  96 $VERSION = sprintf "%d.%02d", q$Revision: 1.54 $ =~ /(\d+)\.(\d+)/;
  97 $DEBUG   = 0 unless defined $DEBUG;
  98
  99 use Data::Dumper;
 100 use SQL::Translator::Schema::Constants;
 101 use SQL::Translator::Utils qw(debug header_comment);
 102
 103 #
 104 # Use only lowercase for the keys (e.g. "long" and not "LONG")
 105 #
 106 my %translate  = (
 107     #
 108     # Oracle types
 109     #
 110     varchar2   => 'varchar',
 111     long       => 'text',
 112     clob       => 'longtext',
 113
 114     #
 115     # Sybase types
 116     #
 117     int        => 'integer',
 118     money      => 'float',
 119     real       => 'double',
 120     comment    => 'text',
 121     bit        => 'tinyint',
 122
 123     #
 124     # Access types
 125     #
 126     'long integer' => 'integer',
 127     'text'         => 'text',
 128     'datetime'     => 'datetime',
 129 );
 130
 131 sub produce {
 132     my $translator     = shift;
 133     local $DEBUG       = $translator->debug;
 134     local %used_names;
 135     my $no_comments    = $translator->no_comments;
 136     my $add_drop_table = $translator->add_drop_table;
 137     my $schema         = $translator->schema;
 138     my $show_warnings  = $translator->show_warnings || 0;
 139
 140     my ($qt, $qf) = ('','');
 141     $qt = '`' if $translator->quote_table_names;
 142     $qf = '`' if $translator->quote_field_names;
 143
 144     debug("PKG: Beginning production\n");
 145     %used_names = ();
 146     my $create;
 147     $create .= header_comment unless ($no_comments);
 148     # \todo Don't set if MySQL 3.x is set on command line
 149     $create .= "SET foreign_key_checks=0;\n\n";
 150
 151     #
 152     # Work out which tables need to be InnoDB to support foreign key
 153     # constraints. We do this first as we need InnoDB at both ends.
 154     #
 155     foreach ( map { $_->get_constraints } $schema->get_tables ) {
 156         next unless $_->type eq FOREIGN_KEY;
 157         foreach my $meth (qw/table reference_table/) {
 158             my $table = $schema->get_table($_->$meth) || next;
 159             next if $table->extra('mysql_table_type');
 160             $table->extra( 'mysql_table_type' => 'InnoDB');
 161         }
 162     }
 163
 164     #
 165     # Generate sql
 166     #
 167     my @table_defs =();
 168     for my $table ( $schema->get_tables ) {
 169 #        print $table->name, "\n";
 170         push @table_defs, create_table($table,
 171                                        { add_drop_table    => $add_drop_table,
 172                                          show_warnings     => $show_warnings,
 173                                          no_comments       => $no_comments,
 174                                          quote_table_names => $qt,
 175                                          quote_field_names => $qf
 176                                          });
 177     }
 178
 179 #    print "@table_defs\n";
 180     push @table_defs, "SET foreign_key_checks=1;\n\n";
 181
 182     return wantarray ? ($create, @table_defs) : $create . join ('', @table_defs);
 183 }
 184
 185 sub create_table
 186 {
 187     my ($table, $options) = @_;
 188
 189     my $qt = $options->{quote_table_names} || '';
 190     my $qf = $options->{quote_field_names} || '';
 191
 192     my $table_name = $table->name;
 193     debug("PKG: Looking at table '$table_name'\n");
 194
 195     #
 196     # Header.  Should this look like what mysqldump produces?
 197     #
 198     my $create = '';
 199     my $drop;
 200     $create .= "--\n-- Table: $qt$table_name$qt\n--\n" unless $options->{no_comments};
 201     $drop = qq[DROP TABLE IF EXISTS $qt$table_name$qt;\n] if $options->{add_drop_table};
 202     $create .= "CREATE TABLE $qt$table_name$qt (\n";
 203
 204     #
 205     # Fields
 206     #
 207     my @field_defs;
 208     for my $field ( $table->get_fields ) {
 209         push @field_defs, create_field($field, $options);
 210     }
 211
 212     #
 213     # Indices
 214     #
 215     my @index_defs;
 216     my %indexed_fields;
 217     for my $index ( $table->get_indices ) {
 218         push @index_defs, create_index($index, $options);
 219         $indexed_fields{ $_ } = 1 for $index->fields;
 220     }
 221
 222     #
 223     # Constraints -- need to handle more than just FK. -ky
 224     #
 225     my @constraint_defs;
 226     my @constraints = $table->get_constraints;
 227     for my $c ( @constraints ) {
 228         my $constr = create_constraint($c, $options);
 229         push @constraint_defs, $constr if($constr);
 230
 231          unless ( $indexed_fields{ ($c->fields())[0] } || $c->type ne FOREIGN_KEY ) {
 232              push @index_defs, "INDEX ($qf" . ($c->fields())[0] . "$qf)";
 233              $indexed_fields{ ($c->fields())[0] } = 1;
 234          }
 235     }
 236
 237     $create .= join(",\n", map { "  $_" }
 238                     @field_defs, @index_defs, @constraint_defs
 239                     );
 240
 241     #
 242     # Footer
 243     #
 244     $create .= "\n)";
 245     $create .= generate_table_options($table) || '';
 246     $create .= ";\n\n";
 247
 248     return $drop ? ($drop,$create) : $create;
 249 }
 250
 251 sub generate_table_options
 252 {
 253   my ($table) = @_;
 254   my $create;
 255
 256   my $table_type_defined = 0;
 257   for my $t1_option_ref ( $table->options ) {
 258     my($key, $value) = %{$t1_option_ref};
 259     $table_type_defined = 1
 260       if uc $key eq 'ENGINE' or uc $key eq 'TYPE';
 261     $create .= " $key=$value";
 262   }
 263   my $mysql_table_type = $table->extra('mysql_table_type');
 264   #my $charset          = $table->extra('mysql_character_set');
 265   #my $collate          = $table->extra('mysql_collate');
 266   #$create .= " Type=$mysql_table_type" if $mysql_table_type;
 267   #$create .= " DEFAULT CHARACTER SET $charset" if $charset;
 268   #$create .= " COLLATE $collate" if $collate;
 269   $create .= " Type=$mysql_table_type"
 270     if $mysql_table_type && !$table_type_defined;
 271   my $charset          = $table->extra('mysql_charset');
 272   my $collate          = $table->extra('mysql_collate');
 273   my $comments         = $table->comments;
 274
 275   $create .= " DEFAULT CHARACTER SET $charset" if $charset;
 276   $create .= " COLLATE $collate" if $collate;
 277   $create .= qq[ comment='$comments'] if $comments;
 278   return $create;
 279 }
 280
 281 sub create_field
 282 {
 283     my ($field, $options) = @_;
 284
 285     my $qf = $options->{quote_field_names} ||= '';
 286
 287     my $field_name = $field->name;
 288     debug("PKG: Looking at field '$field_name'\n");
 289     my $field_def = "$qf$field_name$qf";
 290
 291     # data type and size
 292     my $data_type = $field->data_type;
 293     my @size      = $field->size;
 294     my %extra     = $field->extra;
 295     my $list      = $extra{'list'} || [];
 296     # \todo deal with embedded quotes
 297     my $commalist = join( ', ', map { qq['$_'] } @$list );
 298     my $charset = $extra{'mysql_charset'};
 299     my $collate = $extra{'mysql_collate'};
 300
 301     #
 302     # Oracle "number" type -- figure best MySQL type
 303     #
 304     if ( lc $data_type eq 'number' ) {
 305         # not an integer
 306         if ( scalar @size > 1 ) {
 307             $data_type = 'double';
 308         }
 309         elsif ( $size[0] && $size[0] >= 12 ) {
 310             $data_type = 'bigint';
 311         }
 312         elsif ( $size[0] && $size[0] <= 1 ) {
 313             $data_type = 'tinyint';
 314         }
 315         else {
 316             $data_type = 'int';
 317         }
 318     }
 319     #
 320     # Convert a large Oracle varchar to "text"
 321     #
 322     elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
 323         $data_type = 'text';
 324         @size      = ();
 325     }
 326     elsif ( $data_type =~ /char/i && ! $size[0] ) {
 327         @size = (255);
 328     }
 329     elsif ( $data_type =~ /boolean/i ) {
 330         $data_type = 'enum';
 331         $commalist = "'0','1'";
 332     }
 333     elsif ( exists $translate{ lc $data_type } ) {
 334         $data_type = $translate{ lc $data_type };
 335     }
 336
 337     @size = () if $data_type =~ /(text|blob)/i;
 338
 339     if ( $data_type =~ /(double|float)/ && scalar @size == 1 ) {
 340         push @size, '0';
 341     }
 342
 343     $field_def .= " $data_type";
 344
 345     if ( lc $data_type eq 'enum' ) {
 346         $field_def .= '(' . $commalist . ')';
 347     }
 348     elsif ( defined $size[0] && $size[0] > 0 ) {
 349         $field_def .= '(' . join( ', ', @size ) . ')';
 350     }
 351
 352     # char sets
 353     $field_def .= " CHARACTER SET $charset" if $charset;
 354     $field_def .= " COLLATE $collate" if $collate;
 355
 356     # MySQL qualifiers
 357     for my $qual ( qw[ binary unsigned zerofill ] ) {
 358         my $val = $extra{ $qual } || $extra{ uc $qual } or next;
 359         $field_def .= " $qual";
 360     }
 361     for my $qual ( 'character set', 'collate', 'on update' ) {
 362         my $val = $extra{ $qual } || $extra{ uc $qual } or next;
 363         $field_def .= " $qual $val";
 364     }
 365
 366     # Null?
 367     $field_def .= ' NOT NULL' unless $field->is_nullable;
 368
 369     # Default?  XXX Need better quoting!
 370     my $default = $field->default_value;
 371     if ( defined $default ) {
 372         if ( uc $default eq 'NULL') {
 373             $field_def .= ' DEFAULT NULL';
 374         } else {
 375             $field_def .= " DEFAULT '$default'";
 376         }
 377     }
 378
 379     if ( my $comments = $field->comments ) {
 380         $field_def .= qq[ comment '$comments'];
 381     }
 382
 383     # auto_increment?
 384     $field_def .= " auto_increment" if $field->is_auto_increment;
 385
 386     return $field_def;
 387 }
 388
 389 sub alter_create_index
 390 {
 391     my ($index, $options) = @_;
 392
 393     my $qt = $options->{quote_table_names} || '';
 394     my $qf = $options->{quote_field_names} || '';
 395
 396     return join( ' ',
 397                  'ALTER TABLE',
 398                  $qt.$index->table->name.$qt,
 399                  'ADD',
 400                  create_index(@_)
 401                  );
 402 }
 403
 404 sub create_index
 405 {
 406     my ($index, $options) = @_;
 407
 408     my $qf = $options->{quote_field_names} || '';
 409
 410     return join( ' ',
 411                  lc $index->type eq 'normal' ? 'INDEX' : $index->type . ' INDEX',
 412                  $index->name,
 413                  '(' . $qf . join( "$qf, $qf", $index->fields ) . $qf . ')'
 414                  );
 415
 416 }
 417
 418 sub alter_drop_index
 419 {
 420     my ($index, $options) = @_;
 421
 422     my $qt = $options->{quote_table_names} || '';
 423     my $qf = $options->{quote_field_names} || '';
 424
 425     return join( ' ',
 426                  'ALTER TABLE',
 427                  $qt.$index->table->name.$qt,
 428                  'DROP',
 429                  'INDEX',
 430                  $index->name || $index->fields
 431                  );
 432
 433 }
 434
 435 sub alter_drop_constraint
 436 {
 437     my ($c, $options) = @_;
 438
 439     my $qt      = $options->{quote_table_names} || '';
 440     my $qc      = $options->{quote_constraint_names} || '';
 441
 442     my $out = sprintf('ALTER TABLE %s DROP %s %s',
 443                       $c->table->name,
 444                       $c->type,
 445                       $qc . $c->name . $qc );
 446
 447     return $out;
 448 }
 449
 450 sub alter_create_constraint
 451 {
 452     my ($index, $options) = @_;
 453
 454     my $qt = $options->{quote_table_names} || '';
 455     return join( ' ',
 456                  'ALTER TABLE',
 457                  $qt.$index->table->name.$qt,
 458                  'ADD',
 459                  create_constraint(@_) );
 460 }
 461
 462 sub create_constraint
 463 {
 464     my ($c, $options) = @_;
 465
 466     my $qf      = $options->{quote_field_names} || '';
 467     my $qt      = $options->{quote_table_names} || '';
 468     my $leave_name      = $options->{leave_name} || undef;
 469     my $counter = ($options->{fk_name_counter}   ||= {});
 470
 471     my @fields = $c->fields or next;
 472
 473     if ( $c->type eq PRIMARY_KEY ) {
 474         return 'PRIMARY KEY (' . $qf . join("$qf, $qf", @fields). $qf . ')';
 475     }
 476     elsif ( $c->type eq UNIQUE ) {
 477         return
 478         'UNIQUE '.
 479             (defined $c->name ? $qf.$c->name.$qf.' ' : '').
 480             '(' . $qf . join("$qf, $qf", @fields). $qf . ')';
 481     }
 482     elsif ( $c->type eq FOREIGN_KEY ) {
 483         #
 484         # Make sure FK field is indexed or MySQL complains.
 485         #
 486
 487         my $table = $c->table;
 488         my $c_name = $c->name;
 489
 490         # Give the constraint a name if it doesn't have one, so it doens't feel
 491         # left out
 492         unless ( $c_name ){
 493             $c_name   = $table->name . '_fk';
 494         }
 495
 496         $counter->{$table} ||= {};
 497         my $def = join(' ',
 498                        map { $_ || () }
 499                          'CONSTRAINT',
 500                          $qt . join('_', next_unused_name($c_name)
 501                                    ) . $qt,
 502                          'FOREIGN KEY'
 503                       );
 504
 505
 506         $def .= ' ('.$qf . join( "$qf, $qf", @fields ) . $qf . ')';
 507
 508         $def .= ' REFERENCES ' . $qt . $c->reference_table . $qt;
 509
 510         my @rfields = map { $_ || () } $c->reference_fields;
 511         unless ( @rfields ) {
 512             my $rtable_name = $c->reference_table;
 513             if ( my $ref_table = $table->schema->get_table( $rtable_name ) ) {
 514                 push @rfields, $ref_table->primary_key;
 515             }
 516             else {
 517                 warn "Can't find reference table '$rtable_name' " .
 518                     "in schema\n" if $options->{show_warnings};
 519             }
 520         }
 521
 522         if ( @rfields ) {
 523             $def .= ' (' . $qf . join( "$qf, $qf", @rfields ) . $qf . ')';
 524         }
 525         else {
 526             warn "FK constraint on " . $table->name . '.' .
 527                 join('', @fields) . " has no reference fields\n"
 528                 if $options->{show_warnings};
 529         }
 530
 531         if ( $c->match_type ) {
 532             $def .= ' MATCH ' .
 533                 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
 534         }
 535
 536         if ( $c->on_delete ) {
 537             $def .= ' ON DELETE '.join( ' ', $c->on_delete );
 538         }
 539
 540         if ( $c->on_update ) {
 541             $def .= ' ON UPDATE '.join( ' ', $c->on_update );
 542         }
 543         return $def;
 544     }
 545
 546     return undef;
 547 }
 548
 549 sub alter_table
 550 {
 551     my ($to_table, $options) = @_;
 552
 553     my $qt = $options->{quote_table_name} || '';
 554
 555     my $table_options = generate_table_options($to_table) || '';
 556     my $out = sprintf('ALTER TABLE %s%s',
 557                       $qt . $to_table->name . $qt,
 558                       $table_options);
 559
 560     return $out;
 561 }
 562
 563 sub alter_field
 564 {
 565     my ($from_field, $to_field, $options) = @_;
 566
 567     my $qf = $options->{quote_field_name} || '';
 568     my $qt = $options->{quote_table_name} || '';
 569
 570     my $out = sprintf('ALTER TABLE %s CHANGE COLUMN %s %s',
 571                       $qt . $to_field->table->name . $qt,
 572                       $qf . $to_field->name . $qf,
 573                       create_field($to_field, $options));
 574
 575     return $out;
 576 }
 577
 578 sub add_field
 579 {
 580     my ($new_field, $options) = @_;
 581
 582     my $qt = $options->{quote_table_name} || '';
 583
 584     my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
 585                       $qt . $new_field->table->name . $qt,
 586                       create_field($new_field, $options));
 587
 588     return $out;
 589
 590 }
 591
 592 sub drop_field
 593 {
 594     my ($old_field, $options) = @_;
 595
 596     my $qf = $options->{quote_field_name} || '';
 597     my $qt = $options->{quote_table_name} || '';
 598
 599     my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
 600                       $qt . $old_field->table->name . $qt,
 601                       $qf . $old_field->name . $qf);
 602
 603     return $out;
 604
 605 }
 606
 607 sub next_unused_name {
 608   my $name       = shift || '';
 609   if ( !defined($used_names{$name}) ) {
 610     $used_names{$name} = $name;
 611     return $name;
 612   }
 613
 614   my $i = 1;
 615   while ( defined($used_names{$name . '_' . $i}) ) {
 616     ++$i;
 617   }
 618   $name .= '_' . $i;
 619   $used_names{$name} = $name;
 620   return $name;
 621 }
 622
 623 1;
 624
 625 # -------------------------------------------------------------------
 626
 627 =pod
 628
 629 =head1 SEE ALSO
 630
 631 SQL::Translator, http://www.mysql.com/.
 632
 633 =head1 AUTHORS
 634
 635 darren chamberlain E<lt>darren@cpan.orgE<gt>,
 636 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
 637
 638 =cut