X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FTranslator%2FProducer%2FMySQL.pm;h=bfa1da67788eb8564e61d94d595f88283b6dbea0;hb=44659089c28216f1984873bc4aa8641e2e0e3410;hp=a9b8301e8bf96c4ac621f1aacc32399aa80186c7;hpb=5d666b31769e40325f3513299a58cdac631ced41;p=dbsrgits%2FSQL-Translator.git diff --git a/lib/SQL/Translator/Producer/MySQL.pm b/lib/SQL/Translator/Producer/MySQL.pm index a9b8301..bfa1da6 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 @@ -47,7 +45,7 @@ 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 +Valid version specifiers for C are listed L =head2 Table Types @@ -83,9 +81,9 @@ 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">. -Please note that the C option is the prefered method of specifying +Please note that the C option is the preferred method of specifying the MySQL storage engine to use, but this method still works for backwards -compatability. +compatibility. =item B, B @@ -102,7 +100,7 @@ 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: @@ -112,7 +110,8 @@ my $DEFAULT_MAX_ID_LENGTH = 64; use Data::Dumper; use SQL::Translator::Schema::Constants; -use SQL::Translator::Utils qw(debug header_comment truncate_id_uniquely parse_mysql_version); +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") @@ -140,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) = @_; @@ -270,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); @@ -295,7 +306,7 @@ sub produce { }); } - if ($mysql_version > 5.0) { + if ($mysql_version >= 5.000001) { for my $view ( $schema->get_views ) { push @table_defs, create_view($view, { add_replace_view => $add_drop_table, @@ -309,11 +320,61 @@ sub produce { } } + if ($mysql_version >= 5.000002) { + for my $trigger ( $schema->get_triggers ) { + push @table_defs, create_trigger($trigger, + { add_drop_trigger => $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_trigger { + my ($trigger, $options) = @_; + my $qt = $options->{quote_table_names} || ''; + my $qf = $options->{quote_field_names} || ''; + + my $trigger_name = $trigger->name; + debug("PKG: Looking at trigger '${trigger_name}'\n"); + + my @statements; + + my $events = $trigger->database_events; + for my $event ( @$events ) { + my $name = $trigger_name; + if (@$events > 1) { + $name .= "_$event"; - return wantarray ? ($create, @table_defs) : $create . join ('', @table_defs); + warn "Multiple database events supplied for trigger '${trigger_name}', ", + "creating trigger '${name}' for the '${event}' event\n" + if $options->{show_warnings}; + } + + my $action = $trigger->action; + $action .= ";" unless $action =~ /;\s*\z/; + + push @statements, "DROP TRIGGER IF EXISTS ${qt}${name}${qt}" if $options->{add_drop_trigger}; + push @statements, sprintf( + "CREATE TRIGGER ${qt}%s${qt} %s %s ON ${qt}%s${qt}\n FOR EACH ROW BEGIN %s END", + $name, $trigger->perform_action_when, $event, $trigger->on_table, $action, + ); + + } + # Tack the comment onto the first statement + $statements[0] = "--\n-- Trigger ${qt}${trigger_name}${qt}\n--\n" . $statements[0] unless $options->{no_comments}; + return @statements; } sub create_view { @@ -353,9 +414,11 @@ sub create_view { $create .= " ( ${list} )"; } if( my $sql = $view->sql ){ - $create .= " AS (\n ${sql}\n )"; + # do not wrap parenthesis around the selector, mysql doesn't like this + # http://bugs.mysql.com/bug.php?id=9198 + $create .= " AS\n ${sql}\n"; } - $create .= ";\n\n"; +# $create .= ""; return $create; } @@ -366,7 +429,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"); # @@ -374,9 +437,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 @@ -419,20 +482,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 @@ -443,6 +516,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"; } @@ -454,6 +530,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; } @@ -530,7 +607,11 @@ sub create_field 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 ) . ')'; } @@ -551,15 +632,14 @@ sub create_field # Null? $field_def .= ' NOT NULL' unless $field->is_nullable; - # 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'"; - } - } + # Default? + SQL::Translator::Producer->_apply_default_value( + $field, + \$field_def, + [ + 'NULL' => \'NULL', + ], + ); if ( my $comments = $field->comments ) { $field_def .= qq[ comment '$comments']; @@ -588,16 +668,22 @@ 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', - truncate_id_uniquely( $index->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH ), - '(' . $qf . join( "$qf, $qf", $index->fields ) . $qf . ')' - ); - + return join( + ' ', + map { $_ || () } + lc $index->type eq 'normal' ? 'INDEX' : $index->type . ' INDEX', + $index->name + ? $qf . truncate_id_uniquely( + $index->name, + $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH + ) . $qf + : '', + '(' . $qf . join( "$qf, $qf", $index->fields ) . $qf . ')' + ); } sub alter_drop_index @@ -728,7 +814,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); @@ -803,14 +889,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); } @@ -837,7 +922,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 @@ -854,7 +939,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); } @@ -866,7 +951,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"); } @@ -907,6 +993,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