lib/SQL/Translator/Producer/MySQL.pm

   1 package SQL::Translator::Producer::MySQL;
   2
   3 # -------------------------------------------------------------------
   4 # $Id: MySQL.pm,v 1.46 2005-12-16 05:49:37 grommit 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.46 $ =~ /(\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     for my $table ( $schema->get_tables ) {
 161         my $table_name = $table->name;
 162         debug("PKG: Looking at table '$table_name'\n");
 163
 164         #
 165         # Header.  Should this look like what mysqldump produces?
 166         #
 167         $create .= "--\n-- Table: $table_name\n--\n" unless $no_comments;
 168         $create .= qq[DROP TABLE IF EXISTS $table_name;\n] if $add_drop_table;
 169         $create .= "CREATE TABLE $table_name (\n";
 170
 171         #
 172         # Fields
 173         #
 174         my @field_defs;
 175         for my $field ( $table->get_fields ) {
 176             my $field_name = $field->name;
 177             debug("PKG: Looking at field '$field_name'\n");
 178             my $field_def = $field_name;
 179
 180             # data type and size
 181             my $data_type = $field->data_type;
 182             my @size      = $field->size;
 183             my %extra     = $field->extra;
 184             my $list      = $extra{'list'} || [];
 185             # \todo deal with embedded quotes
 186             my $commalist = join( ', ', map { qq['$_'] } @$list );
 187             my $charset = $extra{'mysql_charset'};
 188             my $collate = $extra{'mysql_collate'};
 189
 190             #
 191             # Oracle "number" type -- figure best MySQL type
 192             #
 193             if ( lc $data_type eq 'number' ) {
 194                 # not an integer
 195                 if ( scalar @size > 1 ) {
 196                     $data_type = 'double';
 197                 }
 198                 elsif ( $size[0] && $size[0] >= 12 ) {
 199                     $data_type = 'bigint';
 200                 }
 201                 elsif ( $size[0] && $size[0] <= 1 ) {
 202                     $data_type = 'tinyint';
 203                 }
 204                 else {
 205                     $data_type = 'int';
 206                 }
 207             }
 208             #
 209             # Convert a large Oracle varchar to "text"
 210             #
 211             elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
 212                 $data_type = 'text';
 213                 @size      = ();
 214             }
 215             elsif ( $data_type =~ /char/i && ! $size[0] ) {
 216                 @size = (255);
 217             }
 218             elsif ( $data_type =~ /boolean/i ) {
 219                 $data_type = 'enum';
 220                 $commalist = "'0','1'";
 221             }
 222             elsif ( exists $translate{ lc $data_type } ) {
 223                 $data_type = $translate{ lc $data_type };
 224             }
 225
 226             @size = () if $data_type =~ /(text|blob)/i;
 227
 228             if ( $data_type =~ /(double|float)/ && scalar @size == 1 ) {
 229                 push @size, '0';
 230             }
 231
 232             $field_def .= " $data_type";
 233
 234             if ( lc $data_type eq 'enum' ) {
 235                 $field_def .= '(' . $commalist . ')';
 236                         }
 237             elsif ( defined $size[0] && $size[0] > 0 ) {
 238                 $field_def .= '(' . join( ', ', @size ) . ')';
 239             }
 240
 241             # char sets
 242             $field_def .= " CHARACTER SET $charset" if $charset;
 243             $field_def .= " COLLATE $collate" if $collate;
 244
 245             # MySQL qualifiers
 246             for my $qual ( qw[ binary unsigned zerofill ] ) {
 247                 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
 248                 $field_def .= " $qual";
 249             }
 250             for my $qual ( 'character set', 'collate', 'on update' ) {
 251                 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
 252                 $field_def .= " $qual $val";
 253             }
 254
 255             # Null?
 256             $field_def .= ' NOT NULL' unless $field->is_nullable;
 257
 258             # Default?  XXX Need better quoting!
 259             my $default = $field->default_value;
 260             if ( defined $default ) {
 261                 if ( uc $default eq 'NULL') {
 262                     $field_def .= ' DEFAULT NULL';
 263                 } else {
 264                     $field_def .= " DEFAULT '$default'";
 265                 }
 266             }
 267
 268             if ( my $comments = $field->comments ) {
 269                 $field_def .= qq[ comment '$comments'];
 270             }
 271
 272             # auto_increment?
 273             $field_def .= " auto_increment" if $field->is_auto_increment;
 274             push @field_defs, $field_def;
 275                 }
 276
 277         #
 278         # Indices
 279         #
 280         my @index_defs;
 281         my %indexed_fields;
 282         for my $index ( $table->get_indices ) {
 283             push @index_defs, join( ' ',
 284                 lc $index->type eq 'normal' ? 'INDEX' : $index->type,
 285                 $index->name,
 286                 '(' . join( ', ', $index->fields ) . ')'
 287             );
 288             $indexed_fields{ $_ } = 1 for $index->fields;
 289         }
 290
 291         #
 292         # Constraints -- need to handle more than just FK. -ky
 293         #
 294         my @constraint_defs;
 295         my @constraints = $table->get_constraints;
 296         for my $c ( @constraints ) {
 297             my @fields = $c->fields or next;
 298
 299             if ( $c->type eq PRIMARY_KEY ) {
 300                 push @constraint_defs,
 301                     'PRIMARY KEY (' . join(', ', @fields). ')';
 302             }
 303             elsif ( $c->type eq UNIQUE ) {
 304                 push @constraint_defs,
 305                     'UNIQUE '.
 306                     (defined $c->name ? $c->name.' ' : '').
 307                     '(' . join(', ', @fields). ')';
 308             }
 309             elsif ( $c->type eq FOREIGN_KEY ) {
 310                 #
 311                 # Make sure FK field is indexed or MySQL complains.
 312                 #
 313                 unless ( $indexed_fields{ $fields[0] } ) {
 314                     push @index_defs, "INDEX ($fields[0])";
 315                     $indexed_fields{ $fields[0] } = 1;
 316                 }
 317
 318                 my $def = join(' ',
 319                     map { $_ || () } 'CONSTRAINT', $c->name, 'FOREIGN KEY'
 320                 );
 321
 322                 $def .= ' (' . join( ', ', @fields ) . ')';
 323
 324                 $def .= ' REFERENCES ' . $c->reference_table;
 325
 326                 my @rfields = map { $_ || () } $c->reference_fields;
 327                 unless ( @rfields ) {
 328                     my $rtable_name = $c->reference_table;
 329                     if ( my $ref_table = $schema->get_table( $rtable_name ) ) {
 330                         push @rfields, $ref_table->primary_key;
 331                     }
 332                     else {
 333                         warn "Can't find reference table '$rtable_name' " .
 334                             "in schema\n" if $show_warnings;
 335                     }
 336                 }
 337
 338                 if ( @rfields ) {
 339                     $def .= ' (' . join( ', ', @rfields ) . ')';
 340                 }
 341                 else {
 342                     warn "FK constraint on " . $table->name . '.' .
 343                         join('', @fields) . " has no reference fields\n"
 344                         if $show_warnings;
 345                 }
 346
 347                 if ( $c->match_type ) {
 348                     $def .= ' MATCH ' .
 349                         ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
 350                 }
 351
 352                 if ( $c->on_delete ) {
 353                     $def .= ' ON DELETE '.join( ' ', $c->on_delete );
 354                 }
 355
 356                 if ( $c->on_update ) {
 357                     $def .= ' ON UPDATE '.join( ' ', $c->on_update );
 358                 }
 359
 360                 push @constraint_defs, $def;
 361             }
 362         }
 363
 364         $create .= join(",\n", map { "  $_" }
 365             @field_defs, @index_defs, @constraint_defs
 366         );
 367
 368         #
 369         # Footer
 370         #
 371         $create .= "\n)";
 372         my $table_type_defined = 0;
 373                 for my $t1_option_ref ( $table->options ) {
 374                         my($key, $value) = %{$t1_option_ref};
 375                         $table_type_defined = 1
 376                                 if uc $key eq 'ENGINE' or uc $key eq 'TYPE';
 377                     $create .= " $key=$value";
 378                 }
 379         my $mysql_table_type = $table->extra('mysql_table_type');
 380         #my $charset          = $table->extra('mysql_character_set');
 381         #my $collate          = $table->extra('mysql_collate');
 382         #$create .= " Type=$mysql_table_type" if $mysql_table_type;
 383         #$create .= " DEFAULT CHARACTER SET $charset" if $charset;
 384         #$create .= " COLLATE $collate" if $collate;
 385         $create .= " Type=$mysql_table_type"
 386                         if $mysql_table_type && !$table_type_defined;
 387         my $charset          = $table->extra('mysql_charset');
 388         my $collate          = $table->extra('mysql_collate');
 389         my $comments         = $table->comments;
 390
 391         $create .= " DEFAULT CHARACTER SET $charset" if $charset;
 392         $create .= " COLLATE $collate" if $collate;
 393         $create .= qq[ comment='$comments'] if $comments;
 394         $create .= ";\n\n";
 395     }
 396
 397     return $create;
 398 }
 399
 400 1;
 401
 402 # -------------------------------------------------------------------
 403
 404 =pod
 405
 406 =head1 SEE ALSO
 407
 408 SQL::Translator, http://www.mysql.com/.
 409
 410 =head1 AUTHORS
 411
 412 darren chamberlain E<lt>darren@cpan.orgE<gt>,
 413 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
 414
 415 =cut