lib/SQL/Translator/Producer/MySQL.pm

   1 package SQL::Translator::Producer::MySQL;
   2
   3 # -------------------------------------------------------------------
   4 # $Id: MySQL.pm,v 1.50 2006-08-09 13:06:20 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 ];
  96 $VERSION = sprintf "%d.%02d", q$Revision: 1.50 $ =~ /(\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     my $no_comments    = $translator->no_comments;
 135     my $add_drop_table = $translator->add_drop_table;
 136     my $schema         = $translator->schema;
 137     my $show_warnings  = $translator->show_warnings || 0;
 138
 139     my ($qt, $qf) = ('','');
 140     $qt = '`' if $translator->quote_table_names;
 141     $qf = '`' if $translator->quote_field_names;
 142
 143     debug("PKG: Beginning production\n");
 144
 145     my $create;
 146     $create .= header_comment unless ($no_comments);
 147     # \todo Don't set if MySQL 3.x is set on command line
 148     $create .= "SET foreign_key_checks=0;\n\n";
 149
 150     #
 151     # Work out which tables need to be InnoDB to support foreign key
 152     # constraints. We do this first as we need InnoDB at both ends.
 153     #
 154     foreach ( map { $_->get_constraints } $schema->get_tables ) {
 155         next unless $_->type eq FOREIGN_KEY;
 156         foreach my $meth (qw/table reference_table/) {
 157             my $table = $schema->get_table($_->$meth) || next;
 158             next if $table->extra('mysql_table_type');
 159             $table->extra( 'mysql_table_type' => 'InnoDB');
 160         }
 161     }
 162
 163     #
 164     # Generate sql
 165     #
 166     my @table_defs =();
 167     for my $table ( $schema->get_tables ) {
 168 #        print $table->name, "\n";
 169         push @table_defs, create_table($table,
 170                                        { add_drop_table    => $add_drop_table,
 171                                          show_warnings     => $show_warnings,
 172                                          no_comments       => $no_comments,
 173                                          quote_table_names => $qt,
 174                                          quote_field_names => $qf
 175                                          });
 176     }
 177
 178 #    print "@table_defs\n";
 179
 180     return wantarray ? ($create, @table_defs, 'SET foreign_key_checks=1;') : $create . join ('', @table_defs, "SET foreign_key_checks=1;\n\n");
 181 }
 182
 183 sub create_table
 184 {
 185     my ($table, $options) = @_;
 186
 187     my $qt = $options->{quote_table_names} || '';
 188     my $qf = $options->{quote_field_names} || '';
 189
 190     my $table_name = $table->name;
 191     debug("PKG: Looking at table '$table_name'\n");
 192
 193     #
 194     # Header.  Should this look like what mysqldump produces?
 195     #
 196     my $create = '';
 197     $create .= "--\n-- Table: $qt$table_name$qt\n--\n" unless $options->{no_comments};
 198     $create .= qq[DROP TABLE IF EXISTS $qt$table_name$qt;\n] if $options->{add_drop_table};
 199     $create .= "CREATE TABLE $qt$table_name$qt (\n";
 200
 201     #
 202     # Fields
 203     #
 204     my @field_defs;
 205     for my $field ( $table->get_fields ) {
 206         push @field_defs, create_field($field, $options);
 207     }
 208
 209     #
 210     # Indices
 211     #
 212     my @index_defs;
 213     my %indexed_fields;
 214     for my $index ( $table->get_indices ) {
 215         push @index_defs, create_index($index, $options);
 216         $indexed_fields{ $_ } = 1 for $index->fields;
 217     }
 218
 219     #
 220     # Constraints -- need to handle more than just FK. -ky
 221     #
 222     my @constraint_defs;
 223     my @constraints = $table->get_constraints;
 224     for my $c ( @constraints ) {
 225         my $constr = create_constraint($c, $options);
 226         push @constraint_defs, $constr if($constr);
 227
 228         unless ( $indexed_fields{ ($c->fields())[0] } ) {
 229             push @index_defs, "INDEX ($qf" . ($c->fields())[0] . "$qf)";
 230             $indexed_fields{ ($c->fields())[0] } = 1;
 231         }
 232     }
 233
 234     $create .= join(",\n", map { "  $_" }
 235                     @field_defs, @index_defs, @constraint_defs
 236                     );
 237
 238     #
 239     # Footer
 240     #
 241     $create .= "\n)";
 242     my $table_type_defined = 0;
 243     for my $t1_option_ref ( $table->options ) {
 244         my($key, $value) = %{$t1_option_ref};
 245         $table_type_defined = 1
 246             if uc $key eq 'ENGINE' or uc $key eq 'TYPE';
 247         $create .= " $key=$value";
 248     }
 249     my $mysql_table_type = $table->extra('mysql_table_type');
 250     #my $charset          = $table->extra('mysql_character_set');
 251     #my $collate          = $table->extra('mysql_collate');
 252     #$create .= " Type=$mysql_table_type" if $mysql_table_type;
 253     #$create .= " DEFAULT CHARACTER SET $charset" if $charset;
 254     #$create .= " COLLATE $collate" if $collate;
 255     $create .= " Type=$mysql_table_type"
 256         if $mysql_table_type && !$table_type_defined;
 257     my $charset          = $table->extra('mysql_charset');
 258     my $collate          = $table->extra('mysql_collate');
 259     my $comments         = $table->comments;
 260
 261     $create .= " DEFAULT CHARACTER SET $charset" if $charset;
 262     $create .= " COLLATE $collate" if $collate;
 263     $create .= qq[ comment='$comments'] if $comments;
 264     $create .= ";\n\n";
 265
 266     return $create;
 267 }
 268
 269 sub create_field
 270 {
 271     my ($field, $options) = @_;
 272
 273     my $qf = $options->{quote_field_names} ||= '';
 274
 275     my $field_name = $field->name;
 276     debug("PKG: Looking at field '$field_name'\n");
 277     my $field_def = "$qf$field_name$qf";
 278
 279     # data type and size
 280     my $data_type = $field->data_type;
 281     my @size      = $field->size;
 282     my %extra     = $field->extra;
 283     my $list      = $extra{'list'} || [];
 284     # \todo deal with embedded quotes
 285     my $commalist = join( ', ', map { qq['$_'] } @$list );
 286     my $charset = $extra{'mysql_charset'};
 287     my $collate = $extra{'mysql_collate'};
 288
 289     #
 290     # Oracle "number" type -- figure best MySQL type
 291     #
 292     if ( lc $data_type eq 'number' ) {
 293         # not an integer
 294         if ( scalar @size > 1 ) {
 295             $data_type = 'double';
 296         }
 297         elsif ( $size[0] && $size[0] >= 12 ) {
 298             $data_type = 'bigint';
 299         }
 300         elsif ( $size[0] && $size[0] <= 1 ) {
 301             $data_type = 'tinyint';
 302         }
 303         else {
 304             $data_type = 'int';
 305         }
 306     }
 307     #
 308     # Convert a large Oracle varchar to "text"
 309     #
 310     elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
 311         $data_type = 'text';
 312         @size      = ();
 313     }
 314     elsif ( $data_type =~ /char/i && ! $size[0] ) {
 315         @size = (255);
 316     }
 317     elsif ( $data_type =~ /boolean/i ) {
 318         $data_type = 'enum';
 319         $commalist = "'0','1'";
 320     }
 321     elsif ( exists $translate{ lc $data_type } ) {
 322         $data_type = $translate{ lc $data_type };
 323     }
 324
 325     @size = () if $data_type =~ /(text|blob)/i;
 326
 327     if ( $data_type =~ /(double|float)/ && scalar @size == 1 ) {
 328         push @size, '0';
 329     }
 330
 331     $field_def .= " $data_type";
 332
 333     if ( lc $data_type eq 'enum' ) {
 334         $field_def .= '(' . $commalist . ')';
 335     }
 336     elsif ( defined $size[0] && $size[0] > 0 ) {
 337         $field_def .= '(' . join( ', ', @size ) . ')';
 338     }
 339
 340     # char sets
 341     $field_def .= " CHARACTER SET $charset" if $charset;
 342     $field_def .= " COLLATE $collate" if $collate;
 343
 344     # MySQL qualifiers
 345     for my $qual ( qw[ binary unsigned zerofill ] ) {
 346         my $val = $extra{ $qual } || $extra{ uc $qual } or next;
 347         $field_def .= " $qual";
 348     }
 349     for my $qual ( 'character set', 'collate', 'on update' ) {
 350         my $val = $extra{ $qual } || $extra{ uc $qual } or next;
 351         $field_def .= " $qual $val";
 352     }
 353
 354     # Null?
 355     $field_def .= ' NOT NULL' unless $field->is_nullable;
 356
 357     # Default?  XXX Need better quoting!
 358     my $default = $field->default_value;
 359     if ( defined $default ) {
 360         if ( uc $default eq 'NULL') {
 361             $field_def .= ' DEFAULT NULL';
 362         } else {
 363             $field_def .= " DEFAULT '$default'";
 364         }
 365     }
 366
 367     if ( my $comments = $field->comments ) {
 368         $field_def .= qq[ comment '$comments'];
 369     }
 370
 371     # auto_increment?
 372     $field_def .= " auto_increment" if $field->is_auto_increment;
 373
 374     return $field_def;
 375 }
 376
 377 sub create_index
 378 {
 379     my ($index, $options) = @_;
 380
 381     my $qf = $options->{quote_field_names} || '';
 382
 383     return join( ' ',
 384                  lc $index->type eq 'normal' ? 'INDEX' : $index->type,
 385                  $index->name,
 386                  '(' . $qf . join( "$qf, $qf", $index->fields ) . $qf . ')'
 387                  );
 388
 389 }
 390
 391 sub create_constraint
 392 {
 393     my ($c, $options) = @_;
 394
 395     my $qf = $options->{quote_field_names} || '';
 396     my $qt = $options->{quote_table_names} || '';
 397
 398     my @fields = $c->fields or next;
 399
 400     if ( $c->type eq PRIMARY_KEY ) {
 401         return 'PRIMARY KEY (' . $qf . join("$qf, $qf", @fields). $qf . ')';
 402     }
 403     elsif ( $c->type eq UNIQUE ) {
 404         return
 405         'UNIQUE '.
 406             (defined $c->name ? $qf.$c->name.$qf.' ' : '').
 407             '(' . $qf . join("$qf, $qf", @fields). $qf . ')';
 408     }
 409     elsif ( $c->type eq FOREIGN_KEY ) {
 410         #
 411         # Make sure FK field is indexed or MySQL complains.
 412         #
 413
 414         my $def = join(' ',
 415                        map { $_ || () } 'CONSTRAINT', $qt . $c->table . '_' . $c->name . $qt, 'FOREIGN KEY'
 416                        );
 417
 418         $def .= ' ('.$qf . join( "$qf, $qf", @fields ) . $qf . ')';
 419
 420         $def .= ' REFERENCES ' . $qt . $c->reference_table . $qt;
 421
 422         my @rfields = map { $_ || () } $c->reference_fields;
 423         unless ( @rfields ) {
 424             my $rtable_name = $c->reference_table;
 425             if ( my $ref_table = $c->table->schema->get_table( $rtable_name ) ) {
 426                 push @rfields, $ref_table->primary_key;
 427             }
 428             else {
 429                 warn "Can't find reference table '$rtable_name' " .
 430                     "in schema\n" if $options->{show_warnings};
 431             }
 432         }
 433
 434         if ( @rfields ) {
 435             $def .= ' (' . $qf . join( "$qf, $qf", @rfields ) . $qf . ')';
 436         }
 437         else {
 438             warn "FK constraint on " . $c->table->name . '.' .
 439                 join('', @fields) . " has no reference fields\n"
 440                 if $options->{show_warnings};
 441         }
 442
 443         if ( $c->match_type ) {
 444             $def .= ' MATCH ' .
 445                 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
 446         }
 447
 448         if ( $c->on_delete ) {
 449             $def .= ' ON DELETE '.join( ' ', $c->on_delete );
 450         }
 451
 452         if ( $c->on_update ) {
 453             $def .= ' ON UPDATE '.join( ' ', $c->on_update );
 454         }
 455         return $def;
 456     }
 457
 458     return undef;
 459 }
 460
 461 sub alter_field
 462 {
 463     my ($from_field, $to_field, $options) = @_;
 464
 465     my $qf = $options->{quote_field_name} || '';
 466     my $qt = $options->{quote_table_name} || '';
 467
 468     my $out = sprintf('ALTER TABLE %s CHANGE COLUMN %s %s',
 469                       $qt . $to_field->table->name . $qt,
 470                       $qf . $to_field->name . $qf,
 471                       create_field($to_field, $options));
 472
 473     return $out;
 474 }
 475
 476 sub add_field
 477 {
 478     my ($new_field, $options) = @_;
 479
 480     my $qt = $options->{quote_table_name} || '';
 481
 482     my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
 483                       $qt . $new_field->table->name . $qt,
 484                       create_field($new_field, $options));
 485
 486     return $out;
 487
 488 }
 489
 490 sub drop_field
 491 {
 492     my ($old_field, $options) = @_;
 493
 494     my $qf = $options->{quote_field_name} || '';
 495     my $qt = $options->{quote_table_name} || '';
 496
 497     my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
 498                       $qt . $old_field->table->name . $qt,
 499                       $qf . $old_field->name . $qf);
 500
 501     return $out;
 502
 503 }
 504
 505 1;
 506
 507 # -------------------------------------------------------------------
 508
 509 =pod
 510
 511 =head1 SEE ALSO
 512
 513 SQL::Translator, http://www.mysql.com/.
 514
 515 =head1 AUTHORS
 516
 517 darren chamberlain E<lt>darren@cpan.orgE<gt>,
 518 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
 519
 520 =cut