Improve trigger 'scope' attribute support (RT#119997)

[dbsrgits/SQL-Translator.git] / lib / SQL / Translator / Producer / MySQL.pm

diff --git a/lib/SQL/Translator/Producer/MySQL.pm b/lib/SQL/Translator/Producer/MySQL.pm
index 57d09dc..ff75606 100644 (file)
--- a/lib/SQL/Translator/Producer/MySQL.pm
+++ b/lib/SQL/Translator/Producer/MySQL.pm
@@ -90,6 +90,7 @@ $DEBUG   = 0 unless defined $DEBUG;
 #   http://dev.mysql.com/doc/refman/5.0/en/identifiers.html
 my $DEFAULT_MAX_ID_LENGTH = 64;
 
+use base qw(SQL::Translator::Producer);
 use Data::Dumper;
 use SQL::Translator::Schema::Constants;
 use SQL::Translator::Generator::DDL::MySQL;
@@ -355,11 +356,14 @@ sub create_trigger {
         }
 
         my $action = $trigger->action;
-        $action .= ";" unless $action =~ /;\s*\z/;
+        if($action !~ /^ \s* BEGIN [\s\;] .*? [\s\;] END [\s\;]* $/six) {
+            $action .= ";" unless $action =~ /;\s*\z/;
+            $action = "BEGIN $action END";
+        }
 
         push @statements, "DROP TRIGGER IF EXISTS " . $generator->quote($name) if $options->{add_drop_trigger};
         push @statements, sprintf(
-            "CREATE TRIGGER %s %s %s ON %s\n  FOR EACH ROW BEGIN %s END",
+            "CREATE TRIGGER %s %s %s ON %s\n  FOR EACH ROW %s",
             $generator->quote($name), $trigger->perform_action_when, $event,
             $generator->quote($trigger->on_table), $action,
         );
@@ -534,8 +538,7 @@ sub create_field
     my @size      = $field->size;
     my %extra     = $field->extra;
     my $list      = $extra{'list'} || [];
-    # \todo deal with embedded quotes
-    my $commalist = join( ', ', map { qq['$_'] } @$list );
+    my $commalist = join( ', ', map { __PACKAGE__->_quote_string($_) } @$list );
     my $charset = $extra{'mysql_charset'};
     my $collate = $extra{'mysql_collate'};
 
@@ -627,7 +630,7 @@ sub create_field
     }
 
     # Default?
-    SQL::Translator::Producer->_apply_default_value(
+    __PACKAGE__->_apply_default_value(
       $field,
       \$field_def,
       [
@@ -636,7 +639,8 @@ sub create_field
     );
 
     if ( my $comments = $field->comments ) {
-        $field_def .= qq[ comment '$comments'];
+        $comments = __PACKAGE__->_quote_string($comments);
+        $field_def .= qq[ comment $comments];
     }
 
     # auto_increment?
@@ -645,6 +649,13 @@ sub create_field
     return $field_def;
 }
 
+sub _quote_string {
+    my ($self, $string) = @_;
+
+    $string =~ s/([\\'])/$1$1/g;
+    return qq{'$string'};
+}
+
 sub alter_create_index
 {
     my ($index, $options) = @_;
@@ -930,12 +941,13 @@ sub batch_alter_table {
 sub drop_table {
   my ($table, $options) = @_;
 
-  # 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);
-
-  my $table_name = _generator($options)->quote($table);
-  return (@sql, "DROP TABLE $table");
-
+  return (
+    # Drop (foreign key) constraints so table drops cleanly
+    batch_alter_table(
+      $table, { alter_drop_constraint => [ grep { $_->type eq 'FOREIGN KEY' } $table->get_constraints ] }, $options
+    ),
+    'DROP TABLE ' . _generator($options)->quote($table),
+  );
 }
 
 sub rename_table {