X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FTranslator%2FProducer%2FMySQL.pm;h=a7ebb05b923d09d5f0230307c9da4ae16b96b15e;hb=11ad2df91bcc0674faa8fb5b6bab52c9e4a73762;hp=e3cbd3f2c838a37b410eb3b3ab99fab195358386;hpb=2c6be67ad8870e978c1976c1debc6a0c4596d911;p=dbsrgits%2FSQL-Translator.git diff --git a/lib/SQL/Translator/Producer/MySQL.pm b/lib/SQL/Translator/Producer/MySQL.pm index e3cbd3f..a7ebb05 100644 --- a/lib/SQL/Translator/Producer/MySQL.pm +++ b/lib/SQL/Translator/Producer/MySQL.pm @@ -1,9 +1,7 @@ package SQL::Translator::Producer::MySQL; # ------------------------------------------------------------------- -# $Id: MySQL.pm,v 1.54 2007-11-10 03:36:43 mwz444 Exp $ -# ------------------------------------------------------------------- -# Copyright (C) 2002-4 SQLFairy Authors +# Copyright (C) 2002-2009 SQLFairy Authors # # This program is free software; you can redistribute it and/or # modify it under the terms of the GNU General Public License as @@ -40,6 +38,15 @@ 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.). +=head1 ARGUMENTS + +This producer takes a single optional producer_arg C, which +provides the desired version for the target database. By default MySQL v3 is +assumed, and statements pertaining to any features introduced in later versions +(e.g. CREATE VIEW) are not produced. + +Valid version specifiers for C are listed L + =head2 Table Types Normally the tables will be created without any explicit table type given and @@ -93,12 +100,18 @@ Set the fields charater set and collation order. use strict; use warnings; use vars qw[ $VERSION $DEBUG %used_names ]; -$VERSION = sprintf "%d.%02d", q$Revision: 1.54 $ =~ /(\d+)\.(\d+)/; +$VERSION = '1.59'; $DEBUG = 0 unless defined $DEBUG; +# Maximum length for most identifiers is 64, according to: +# http://dev.mysql.com/doc/refman/4.1/en/identifiers.html +# http://dev.mysql.com/doc/refman/5.0/en/identifiers.html +my $DEFAULT_MAX_ID_LENGTH = 64; + use Data::Dumper; use SQL::Translator::Schema::Constants; -use SQL::Translator::Utils qw(debug header_comment); +use SQL::Translator::Utils qw(debug header_comment + truncate_id_uniquely parse_mysql_version); # # Use only lowercase for the keys (e.g. "long" and not "LONG") @@ -126,8 +139,20 @@ my %translate = ( 'long integer' => 'integer', 'text' => 'text', 'datetime' => 'datetime', + + # + # PostgreSQL types + # + bytea => 'BLOB', ); +# +# Column types that do not support lenth attribute +# +my @no_length_attr = qw/ + date time timestamp datetime year + /; + sub preprocess_schema { my ($schema) = @_; @@ -247,7 +272,8 @@ sub produce { my $schema = $translator->schema; my $show_warnings = $translator->show_warnings || 0; my $producer_args = $translator->producer_args; - my $mysql_version = $producer_args->{mysql_version} || 0; + my $mysql_version = parse_mysql_version ($producer_args->{mysql_version}, 'perl') || 0; + my $max_id_length = $producer_args->{mysql_max_id_length} || $DEFAULT_MAX_ID_LENGTH; my ($qt, $qf, $qc) = ('','', ''); $qt = '`' if $translator->quote_table_names; @@ -255,10 +281,10 @@ sub produce { debug("PKG: Beginning production\n"); %used_names = (); - my $create; + my $create = ''; $create .= header_comment unless ($no_comments); # \todo Don't set if MySQL 3.x is set on command line - $create .= "SET foreign_key_checks=0;\n\n"; + my @create = "SET foreign_key_checks=0"; preprocess_schema($schema); @@ -275,14 +301,73 @@ sub produce { no_comments => $no_comments, quote_table_names => $qt, quote_field_names => $qf, + max_id_length => $max_id_length, mysql_version => $mysql_version }); } + if ($mysql_version >= 5.000001) { + for my $view ( $schema->get_views ) { + push @table_defs, create_view($view, + { add_replace_view => $add_drop_table, + show_warnings => $show_warnings, + no_comments => $no_comments, + quote_table_names => $qt, + quote_field_names => $qf, + max_id_length => $max_id_length, + mysql_version => $mysql_version + }); + } + } + + # print "@table_defs\n"; - push @table_defs, "SET foreign_key_checks=1;\n\n"; + push @table_defs, "SET foreign_key_checks=1"; + + return wantarray ? ($create ? $create : (), @create, @table_defs) : ($create . join('', map { $_ ? "$_;\n\n" : () } (@create, @table_defs))); +} + +sub create_view { + my ($view, $options) = @_; + my $qt = $options->{quote_table_names} || ''; + my $qf = $options->{quote_field_names} || ''; + + my $view_name = $view->name; + debug("PKG: Looking at view '${view_name}'\n"); - return wantarray ? ($create, @table_defs) : $create . join ('', @table_defs); + # Header. Should this look like what mysqldump produces? + my $create = ''; + $create .= "--\n-- View: ${qt}${view_name}${qt}\n--\n" unless $options->{no_comments}; + $create .= 'CREATE'; + $create .= ' OR REPLACE' if $options->{add_replace_view}; + $create .= "\n"; + + my $extra = $view->extra; + # ALGORITHM + if( exists($extra->{mysql_algorithm}) && defined(my $algorithm = $extra->{mysql_algorithm}) ){ + $create .= " ALGORITHM = ${algorithm}\n" if $algorithm =~ /(?:UNDEFINED|MERGE|TEMPTABLE)/i; + } + # DEFINER + if( exists($extra->{mysql_definer}) && defined(my $user = $extra->{mysql_definer}) ){ + $create .= " DEFINER = ${user}\n"; + } + # SECURITY + if( exists($extra->{mysql_security}) && defined(my $security = $extra->{mysql_security}) ){ + $create .= " SQL SECURITY ${security}\n" if $security =~ /(?:DEFINER|INVOKER)/i; + } + + #Header, cont. + $create .= " VIEW ${qt}${view_name}${qt}"; + + if( my @fields = $view->fields ){ + my $list = join ', ', map { "${qf}${_}${qf}"} @fields; + $create .= " ( ${list} )"; + } + if( my $sql = $view->sql ){ + $create .= " AS (\n ${sql}\n )"; + } +# $create .= ""; + return $create; } sub create_table @@ -292,7 +377,7 @@ sub create_table my $qt = $options->{quote_table_names} || ''; my $qf = $options->{quote_field_names} || ''; - my $table_name = $table->name; + my $table_name = quote_table_name($table->name, $qt); debug("PKG: Looking at table '$table_name'\n"); # @@ -300,9 +385,9 @@ sub create_table # my $create = ''; my $drop; - $create .= "--\n-- Table: $qt$table_name$qt\n--\n" unless $options->{no_comments}; - $drop = qq[DROP TABLE IF EXISTS $qt$table_name$qt;\n] if $options->{add_drop_table}; - $create .= "CREATE TABLE $qt$table_name$qt (\n"; + $create .= "--\n-- Table: $table_name\n--\n" unless $options->{no_comments}; + $drop = qq[DROP TABLE IF EXISTS $table_name] if $options->{add_drop_table}; + $create .= "CREATE TABLE $table_name (\n"; # # Fields @@ -345,20 +430,30 @@ sub create_table # Footer # $create .= "\n)"; - $create .= generate_table_options($table) || ''; - $create .= ";\n\n"; + $create .= generate_table_options($table, $options) || ''; +# $create .= ";\n\n"; return $drop ? ($drop,$create) : $create; } +sub quote_table_name { + my ($table_name, $qt) = @_; + + $table_name =~ s/\./$qt.$qt/g; + + return "$qt$table_name$qt"; +} + sub generate_table_options { - my ($table) = @_; + my ($table, $options) = @_; my $create; my $table_type_defined = 0; + my $qf = $options->{quote_field_names} ||= ''; my $charset = $table->extra('mysql_charset'); my $collate = $table->extra('mysql_collate'); + my $union = undef; for my $t1_option_ref ( $table->options ) { my($key, $value) = %{$t1_option_ref}; $table_type_defined = 1 @@ -369,6 +464,9 @@ sub generate_table_options } elsif (uc $key eq 'COLLATE') { $collate = $value; next; + } elsif (uc $key eq 'UNION') { + $union = "($qf". join("$qf, $qf", @$value) ."$qf)"; + next; } $create .= " $key=$value"; } @@ -380,6 +478,7 @@ sub generate_table_options $create .= " DEFAULT CHARACTER SET $charset" if $charset; $create .= " COLLATE $collate" if $collate; + $create .= " UNION=$union" if $union; $create .= qq[ comment='$comments'] if $comments; return $create; } @@ -404,6 +503,7 @@ sub create_field my $charset = $extra{'mysql_charset'}; my $collate = $extra{'mysql_collate'}; + my $mysql_version = $options->{mysql_version} || 0; # # Oracle "number" type -- figure best MySQL type # @@ -424,13 +524,15 @@ sub create_field } # # Convert a large Oracle varchar to "text" + # (not necessary as of 5.0.3 http://dev.mysql.com/doc/refman/5.0/en/char.html) # elsif ( $data_type =~ /char/i && $size[0] > 255 ) { - $data_type = 'text'; - @size = (); + unless ($size[0] <= 65535 && $mysql_version >= 5.000003 ) { + $data_type = 'text'; + @size = (); + } } elsif ( $data_type =~ /boolean/i ) { - my $mysql_version = $options->{mysql_version} || 0; if ($mysql_version >= 4) { $data_type = 'boolean'; } else { @@ -450,10 +552,14 @@ sub create_field $field_def .= " $data_type"; - if ( lc $data_type eq 'enum' ) { + if ( lc($data_type) eq 'enum' || lc($data_type) eq 'set') { $field_def .= '(' . $commalist . ')'; - } - elsif ( defined $size[0] && $size[0] > 0 ) { + } + elsif ( + defined $size[0] && $size[0] > 0 + && + ! grep lc($data_type) eq $_, @no_length_attr + ) { $field_def .= '(' . join( ', ', @size ) . ')'; } @@ -477,11 +583,13 @@ sub create_field # Default? XXX Need better quoting! my $default = $field->default_value; if ( defined $default ) { - if ( uc $default eq 'NULL') { - $field_def .= ' DEFAULT NULL'; - } else { - $field_def .= " DEFAULT '$default'"; - } + SQL::Translator::Producer->_apply_default_value( + \$field_def, + $default, + [ + 'NULL' => \'NULL', + ], + ); } if ( my $comments = $field->comments ) { @@ -511,16 +619,24 @@ sub alter_create_index sub create_index { - my ($index, $options) = @_; + my ( $index, $options ) = @_; my $qf = $options->{quote_field_names} || ''; - return join( ' ', - lc $index->type eq 'normal' ? 'INDEX' : $index->type . ' INDEX', - $index->name, - '(' . $qf . join( "$qf, $qf", $index->fields ) . $qf . ')' - ); - + return join( + ' ', + map { $_ || () } + lc $index->type eq 'normal' ? 'INDEX' : $index->type . ' INDEX', + $index->name + ? ( + truncate_id_uniquely( + $index->name, + $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH + ) + ) + : '', + '(' . $qf . join( "$qf, $qf", $index->fields ) . $qf . ')' + ); } sub alter_drop_index @@ -583,7 +699,7 @@ sub create_constraint elsif ( $c->type eq UNIQUE ) { return 'UNIQUE '. - (defined $c->name ? $qf.$c->name.$qf.' ' : ''). + (defined $c->name ? $qf.truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH ).$qf.' ' : ''). '(' . $qf . join("$qf, $qf", @fields). $qf . ')'; } elsif ( $c->type eq FOREIGN_KEY ) { @@ -592,7 +708,7 @@ sub create_constraint # my $table = $c->table; - my $c_name = $c->name; + my $c_name = truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH ); my $def = join(' ', map { $_ || () } @@ -651,7 +767,7 @@ sub alter_table my $qt = $options->{quote_table_names} || ''; - my $table_options = generate_table_options($to_table) || ''; + my $table_options = generate_table_options($to_table, $options) || ''; my $out = sprintf('ALTER TABLE %s%s', $qt . $to_table->name . $qt, $table_options); @@ -726,14 +842,13 @@ sub batch_alter_table { } else { ( ) } } @{$diff_hash->{alter_create_constraint} }; - my $drop_stmt = ''; + my @drop_stmt; if (scalar keys %fks_to_alter) { $diff_hash->{alter_drop_constraint} = [ grep { !$fks_to_alter{$_->name} } @{ $diff_hash->{alter_drop_constraint} } ]; - $drop_stmt = batch_alter_table($table, { alter_drop_constraint => [ values %fks_to_alter ] }, $options) - . "\n"; + @drop_stmt = batch_alter_table($table, { alter_drop_constraint => [ values %fks_to_alter ] }, $options); } @@ -760,7 +875,7 @@ sub batch_alter_table { return unless @stmts; # Just zero or one stmts. return now - return "$drop_stmt@stmts;" unless @stmts > 1; + return (@drop_stmt,@stmts) unless @stmts > 1; # Now strip off the 'ALTER TABLE xyz' of all but the first one @@ -777,7 +892,7 @@ sub batch_alter_table { my $padd = " " x length($alter_table); - return $drop_stmt . join( ",\n", $first, map { s/$re//; $padd . $_ } @stmts) . ';'; + return @drop_stmt, join( ",\n", $first, map { s/$re//; $padd . $_ } @stmts); } @@ -789,7 +904,8 @@ sub drop_table { # Drop (foreign key) constraints so table drops cleanly my @sql = batch_alter_table($table, { alter_drop_constraint => [ grep { $_->type eq 'FOREIGN KEY' } $table->get_constraints ] }, $options); - return join("\n", @sql, "DROP TABLE $qt$table$qt;"); + return (@sql, "DROP TABLE $qt$table$qt"); +# return join("\n", @sql, "DROP TABLE $qt$table$qt"); } @@ -830,6 +946,6 @@ SQL::Translator, http://www.mysql.com/. =head1 AUTHORS darren chamberlain Edarren@cpan.orgE, -Ken Y. Clark Ekclark@cpan.orgE. +Ken Youens-Clark Ekclark@cpan.orgE. =cut