lib/SQL/Translator/Producer/MySQL.pm

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