X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FTranslator%2FProducer%2FMySQL.pm;h=498e8d6d0fe3faeb390400888957e6721de9ea34;hb=31f10179f8660d0482fe85b49f9b3ca9bcf542cd;hp=446b176b3bc2866b7546befaa7783d601d7781f5;hpb=aa0a4c42ac100f66f411ad9e422d34fe54af9679;p=dbsrgits%2FSQL-Translator.git diff --git a/lib/SQL/Translator/Producer/MySQL.pm b/lib/SQL/Translator/Producer/MySQL.pm index 446b176..498e8d6 100644 --- a/lib/SQL/Translator/Producer/MySQL.pm +++ b/lib/SQL/Translator/Producer/MySQL.pm @@ -1,7 +1,7 @@ package SQL::Translator::Producer::MySQL; # ------------------------------------------------------------------- -# $Id: MySQL.pm,v 1.40 2004-09-20 20:32:18 kycl4rk Exp $ +# $Id: MySQL.pm,v 1.45 2005-06-27 20:41:13 duality72 Exp $ # ------------------------------------------------------------------- # Copyright (C) 2002-4 SQLFairy Authors # @@ -40,11 +40,50 @@ There are still some issues to be worked out with syntax differences between MySQL versions 3 and 4 ("SET foreign_key_checks," character sets for fields, etc.). +=head2 Table Types + +Normally the tables will be created without any explicit table type given and +so will use the MySQL default. + +Any tables involved in foreign key constraints automatically get a table type +of InnoDB, unless this is overridden by setting the C extra +attribute explicitly on the table. + +=head2 Extra attributes. + +The producer recognises the following extra attributes on the Schema objects. + +=over 4 + +=item field.list + +Set the list of allowed values for Enum fields. + +=item field.binary field.unsigned field.zerofill + +Set the MySQL field options of the same name. + +=item table.mysql_table_type + +Set the type of the table e.g. 'InnoDB', 'MyISAM'. This will be +automatically set for tables involved in foreign key constraints if it is +not already set explicitly. See L<"Table Types">. + +=item table.mysql_charset table.mysql_collate + +Set the tables default charater set and collation order. + +=item field.mysql_charset field.mysql_collate + +Set the fields charater set and collation order. + +=back + =cut use strict; use vars qw[ $VERSION $DEBUG ]; -$VERSION = sprintf "%d.%02d", q$Revision: 1.40 $ =~ /(\d+)\.(\d+)/; +$VERSION = sprintf "%d.%02d", q$Revision: 1.45 $ =~ /(\d+)\.(\d+)/; $DEBUG = 0 unless defined $DEBUG; use Data::Dumper; @@ -94,6 +133,21 @@ sub produce { # \todo Don't set if MySQL 3.x is set on command line $create .= "SET foreign_key_checks=0;\n\n"; + # + # Work out which tables need to be InnoDB to support foreign key + # constraints. We do this first as we need InnoDB at both ends. + # + foreach ( map { $_->get_constraints } $schema->get_tables ) { + foreach my $meth (qw/table reference_table/) { + my $table = $schema->get_table($_->$meth) || next; + next if $table->extra('mysql_table_type'); + $table->extra( 'mysql_table_type' => 'InnoDB'); + } + } + + # + # Generate sql + # for my $table ( $schema->get_tables ) { my $table_name = $table->name; debug("PKG: Looking at table '$table_name'\n"); @@ -121,6 +175,8 @@ sub produce { my $list = $extra{'list'} || []; # \todo deal with embedded quotes my $commalist = join( ', ', map { qq['$_'] } @$list ); + my $charset = $extra{'mysql_charset'}; + my $collate = $extra{'mysql_collate'}; # # Oracle "number" type -- figure best MySQL type @@ -130,10 +186,10 @@ sub produce { if ( scalar @size > 1 ) { $data_type = 'double'; } - elsif ( $size[0] >= 12 ) { + elsif ( $size[0] && $size[0] >= 12 ) { $data_type = 'bigint'; } - elsif ( $size[0] <= 1 ) { + elsif ( $size[0] && $size[0] <= 1 ) { $data_type = 'tinyint'; } else { @@ -165,7 +221,7 @@ sub produce { } $field_def .= " $data_type"; - + if ( lc $data_type eq 'enum' ) { $field_def .= '(' . $commalist . ')'; } @@ -173,11 +229,19 @@ sub produce { $field_def .= '(' . join( ', ', @size ) . ')'; } + # char sets + $field_def .= " CHARACTER SET $charset" if $charset; + $field_def .= " COLLATE $collate" if $collate; + # MySQL qualifiers for my $qual ( qw[ binary unsigned zerofill ] ) { - my $val = $extra{ $qual || uc $qual } or next; + my $val = $extra{ $qual } || $extra{ uc $qual } or next; $field_def .= " $qual"; } + for my $qual ( 'character set', 'collate', 'on update' ) { + my $val = $extra{ $qual } || $extra{ uc $qual } or next; + $field_def .= " $qual $val"; + } # Null? $field_def .= ' NOT NULL' unless $field->is_nullable; @@ -192,6 +256,10 @@ sub produce { } } + if ( my $comments = $field->comments ) { + $field_def .= qq[ comment '$comments']; + } + # auto_increment? $field_def .= " auto_increment" if $field->is_auto_increment; push @field_defs, $field_def; @@ -215,7 +283,6 @@ sub produce { # Constraints -- need to handle more than just FK. -ky # my @constraint_defs; - my $has_fk; my @constraints = $table->get_constraints; for my $c ( @constraints ) { my @fields = $c->fields or next; @@ -226,11 +293,11 @@ sub produce { } elsif ( $c->type eq UNIQUE ) { push @constraint_defs, - 'UNIQUE (' . join(', ', @fields). ')'; + 'UNIQUE '. + (defined $c->name ? $c->name.' ' : ''). + '(' . join(', ', @fields). ')'; } elsif ( $c->type eq FOREIGN_KEY ) { - $has_fk = 1; - # # Make sure FK field is indexed or MySQL complains. # @@ -240,7 +307,7 @@ sub produce { } my $def = join(' ', - map { $_ || () } 'FOREIGN KEY', $c->name + map { $_ || () } 'CONSTRAINT', $c->name, 'FOREIGN KEY' ); $def .= ' (' . join( ', ', @fields ) . ')'; @@ -293,9 +360,23 @@ sub produce { # Footer # $create .= "\n)"; - if ( $has_fk ) { - $create .= " Type=InnoDB"; - } + my $table_type_defined = 0; + for my $t1_option_ref ( $table->options ) { + my($key, $value) = %{$t1_option_ref}; + $table_type_defined = 1 + if uc $key eq 'ENGINE' or uc $key eq 'TYPE'; + $create .= " $key=$value"; + } + my $mysql_table_type = $table->extra('mysql_table_type'); + $create .= " Type=$mysql_table_type" + if $mysql_table_type && !$table_type_defined; + my $charset = $table->extra('mysql_charset'); + my $collate = $table->extra('mysql_collate'); + my $comments = $table->comments; + + $create .= " DEFAULT CHARACTER SET $charset" if $charset; + $create .= " COLLATE $collate" if $collate; + $create .= qq[ comment='$comments'] if $comments; $create .= ";\n\n"; }