_run_sql_array / _split_sql_chunk split too agressively, breaking triggers

[Leeft]

The first thing _run_sql_array in DBIx::Class::DeploymentHandler::DeployMethod::SQL::Translator does during the deploy stage:

$sql = [ _split_sql_chunk( @$sql ) ];

And _split_sql_chunk executes:

my @sql = map { split /;\n/, $_ } @_;

This naive splitter which strips all semicolons doesn't work well with slightly more complex SQL, e.g. a MySQL trigger example:

sub sqlt_deploy_hook {
   my ( $self, $sqlt_table ) = @_;
   $sqlt_table->schema->add_trigger({
      name                => 'foo',
      perform_action_when => 'BEFORE',
      database_events     => [qw(INSERT)],
      fields              => [qw(name)],
      on_table            => $sqlt_table->name,
      schema              => $sqlt_table->schema,
      scope               => 'row',
      action              => q#
         IF ( 1 = 1 ) THEN
            SET NEW.name = 'foo';
         END IF;
      #,
   });
};

While deploying, the SQL is split just before the END IF, the semicolon is removed from the SQL (that first part is then fed to $dbh->do) and MySQL correctly returns a syntax error on the statement.

I don't see an obvious way yet to solve it properly with a patch for DH; in my subclassed ::DeployMethod::SQL::Translator class I've overridden _run_sql_array to skip calling _split_sql_chunk entirely (which isn't a method and thus not overridable), and this works around the problem for me. I haven't seen any adverse effects yet from leaving out the SQL splitting, though no doubt it'll have some effect somewhere.

[frioux]

Yeah this is a big bummer see #11 as that is the real fix for this.