lib/SQL/Translator/Producer/MySQL.pm

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