lib/SQL/Translator/Producer/MySQL.pm

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