lib/SQL/Translator/Producer/MySQL.pm

   1 package SQL::Translator::Producer::MySQL;
   2
   3 # -------------------------------------------------------------------
   4 # $Id: MySQL.pm,v 1.53 2007-10-24 10:55:44 schiffbruechige 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.53 $ =~ /(\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 $c_name = $c->name;
 488         $counter->{$c->table} ||= {};
 489         my $def = join(' ',
 490                        map { $_ || () }
 491                          'CONSTRAINT',
 492                          $qt . join('_', next_unused_name($c_name)
 493                                    ) . $qt,
 494                          'FOREIGN KEY'
 495                       );
 496
 497
 498         $def .= ' ('.$qf . join( "$qf, $qf", @fields ) . $qf . ')';
 499
 500         $def .= ' REFERENCES ' . $qt . $c->reference_table . $qt;
 501
 502         my @rfields = map { $_ || () } $c->reference_fields;
 503         unless ( @rfields ) {
 504             my $rtable_name = $c->reference_table;
 505             if ( my $ref_table = $c->table->schema->get_table( $rtable_name ) ) {
 506                 push @rfields, $ref_table->primary_key;
 507             }
 508             else {
 509                 warn "Can't find reference table '$rtable_name' " .
 510                     "in schema\n" if $options->{show_warnings};
 511             }
 512         }
 513
 514         if ( @rfields ) {
 515             $def .= ' (' . $qf . join( "$qf, $qf", @rfields ) . $qf . ')';
 516         }
 517         else {
 518             warn "FK constraint on " . $c->table->name . '.' .
 519                 join('', @fields) . " has no reference fields\n"
 520                 if $options->{show_warnings};
 521         }
 522
 523         if ( $c->match_type ) {
 524             $def .= ' MATCH ' .
 525                 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
 526         }
 527
 528         if ( $c->on_delete ) {
 529             $def .= ' ON DELETE '.join( ' ', $c->on_delete );
 530         }
 531
 532         if ( $c->on_update ) {
 533             $def .= ' ON UPDATE '.join( ' ', $c->on_update );
 534         }
 535         return $def;
 536     }
 537
 538     return undef;
 539 }
 540
 541 sub alter_table
 542 {
 543     my ($to_table, $options) = @_;
 544
 545     my $qt = $options->{quote_table_name} || '';
 546
 547     my $table_options = generate_table_options($to_table) || '';
 548     my $out = sprintf('ALTER TABLE %s%s',
 549                       $qt . $to_table->name . $qt,
 550                       $table_options);
 551
 552     return $out;
 553 }
 554
 555 sub alter_field
 556 {
 557     my ($from_field, $to_field, $options) = @_;
 558
 559     my $qf = $options->{quote_field_name} || '';
 560     my $qt = $options->{quote_table_name} || '';
 561
 562     my $out = sprintf('ALTER TABLE %s CHANGE COLUMN %s %s',
 563                       $qt . $to_field->table->name . $qt,
 564                       $qf . $to_field->name . $qf,
 565                       create_field($to_field, $options));
 566
 567     return $out;
 568 }
 569
 570 sub add_field
 571 {
 572     my ($new_field, $options) = @_;
 573
 574     my $qt = $options->{quote_table_name} || '';
 575
 576     my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
 577                       $qt . $new_field->table->name . $qt,
 578                       create_field($new_field, $options));
 579
 580     return $out;
 581
 582 }
 583
 584 sub drop_field
 585 {
 586     my ($old_field, $options) = @_;
 587
 588     my $qf = $options->{quote_field_name} || '';
 589     my $qt = $options->{quote_table_name} || '';
 590
 591     my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
 592                       $qt . $old_field->table->name . $qt,
 593                       $qf . $old_field->name . $qf);
 594
 595     return $out;
 596
 597 }
 598
 599 sub next_unused_name {
 600   my $name       = shift || '';
 601   if ( !defined($used_names{$name}) ) {
 602     $used_names{$name} = $name;
 603     return $name;
 604   }
 605
 606   my $i = 1;
 607   while ( defined($used_names{$name . '_' . $i}) ) {
 608     ++$i;
 609   }
 610   $name .= '_' . $i;
 611   $used_names{$name} = $name;
 612   return $name;
 613 }
 614
 615 1;
 616
 617 # -------------------------------------------------------------------
 618
 619 =pod
 620
 621 =head1 SEE ALSO
 622
 623 SQL::Translator, http://www.mysql.com/.
 624
 625 =head1 AUTHORS
 626
 627 darren chamberlain E<lt>darren@cpan.orgE<gt>,
 628 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
 629
 630 =cut