sqlFile delimitor

General Discussion
1

With liquibase 1.9.5, I could use the element and include a file that might include the following SQL:

CREATE TABLE host (
  Host char(60) COLLATE utf8_bin NOT NULL DEFAULT ‘’,
  PRIMARY KEY (Host)
) ENGINE=MyISAM COMMENT=‘Host privileges;  Merged with database privileges’;

I find that in 2.0.0, even when a delimiter exists in quotes (as the comment in the SQL above contains), liquibase will still use that to demarcate a statement.  When liquibase attempts to execute the above, it misses everything after “Host privileges”, which of course results in an error.

Is this an intended change in behavior?

Since our “baseline” schema for liquibase is generated from a MySQL dump, and that dump does indeed include  semicolons, this change is decidedly incovenient.  Is there a way I can escape the semicolon?

~hf

2

I just fixed some issues around the delimiter a day or two ago.  Try the latest build from http://liquibase.org/ci/latest and let me know if that helps.

Nathan

3

It does not appear to help.

I’ve attached two patches, one for the SQLFileChangeTest and the other to StringUtilsTest  (I think only SQLFileChangeTest is relevant).  Either will demonstrate  the behavior I’m describing.

I’ll take a look at the 1.9 branch to determine how exactly such a statement was parsed correctly.  It’s curious that I did not run into the same issue described in this post when using 1.9.5:  http://liquibase.org/forum/index.php?topic=246.0

~hf

4

I’m not sure why it was working with 1.9 either.  We did made changes to how the delimiter was used, but mainly fixing bugs related to it and making it more robust.

Your tests pass if you don’t pass in the ; delimiter, the default uses “;\s*$” as part of the delimiter which will just match semicolons at the end of a line.  You weren’t using a delimiter more like ;$ before, were you?

Nathan

5

I think I found the difference.

In version 1.9.x, in AbstractSQLChange you invoke in generate statements (line 112):

StringUtils.splitSQL(processedSQL);

Whereas in trunk, you’re supplying the second argument (line 103):

String[] statements = StringUtils.splitSQL(processedSQL, getEndDelimiter());

Correct me if I’m wrong, but it seems to me trunk adheres more to the intent than 1_9. ( In fact, I might’ve been dependent upon on a bug to work with 1.9! :slight_smile: )

If I follow everything correctly, I can specify the delimiter that 1_9 had been using and I’ll be good to go.

~hf

6

Yes, it looks like it was a bug in 1.9.  If you do not specify a delimiter, it will use the default which was what was happening all the time in 1.9 apparently. 

The tests you gave me passed if I do not specify a delimiter at all, since the default delimeter isn’t “;”, it’s a regular expression that tries to be semi-intelligent. “;$” as a passed delimiter should work in your case as well.

Nathan