X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FTranslator%2FProducer%2FMySQL.pm;h=2dc106d090171d239c8a58e86a3cc875aa5e1138;hb=821a0fde221f5accf93e3f65efa77b5a6733cb5e;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..2dc106d 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.54 2007-11-10 03:36:43 mwz444 Exp $ +# $Id$ # ------------------------------------------------------------------- # Copyright (C) 2002-4 SQLFairy Authors # @@ -40,6 +40,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 +102,17 @@ 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 = sprintf "%d.%02d", q$Revision$ =~ /(\d+)\.(\d+)/; $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") @@ -247,7 +261,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 +270,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 +290,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, @table_defs) : $create . join ('', @table_defs); + 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"); + + # 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 @@ -301,7 +375,7 @@ 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}; + $drop = qq[DROP TABLE IF EXISTS $qt$table_name$qt] if $options->{add_drop_table}; $create .= "CREATE TABLE $qt$table_name$qt (\n"; # @@ -345,20 +419,22 @@ 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 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 +445,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 +459,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 +484,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 +505,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,9 +533,9 @@ 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 ) { $field_def .= '(' . join( ', ', @size ) . ')'; } @@ -477,11 +560,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 ) { @@ -517,7 +602,7 @@ sub create_index return join( ' ', 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 . ')' ); @@ -583,7 +668,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 +677,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 +736,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 +811,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 +844,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 +861,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 +873,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"); }