fix bug in splitSqlScript

[inponomarev]

Fix #3316
Fix #4441

Problem

Current version of ScriptUtils.splitSqlScript tries to take into account BEGIN...END blocks. However, it treats any END as an end of the block, even if it's END IF or END LOOP, so that the following one-statement script

BEGIN
     rec_loop: LOOP
         FETCH blah;
         IF something_wrong THEN LEAVE rec_loop; END IF;
         do_something_else;
     END LOOP;
END;

Is incorrectly split into following "statements":

BEGIN
    rec_loop: LOOP
        FETCH blah;
        IF something_wrong THEN LEAVE rec_loop; END IF
-----------------------------------------------------------------
       do_something_else
-----------------------------------------------------------------
   END LOOP
-----------------------------------------------------------------
END

This is also broken for the case when a special statement separator is used (like GO in MSSQL tradition, / in Oracle tradition or @ in DB2).

The following statement is not split over @ sign as when it comes to the separator, the algorithm counted more ENDs than BEGINs and and since the balance is not zero, it is not splitting the code:

BEGIN
    rec_loop: LOOP
        FETCH blah;
        IF something_wrong THEN LEAVE rec_loop; END IF;
        do_something_else;
    END LOOP;
END;
@
CALL something();
@

Solution

Apparently, the "true end of block" must be END immediately followed by a separator (with probably whitespace and/or comments preceding the separator).

The current implementation of splitSqlScript is poorly written and it's difficult to extend "syntax rules". So I have completely rewritten it in a style of a standard parser:

1 "Lexical analysis" is perfromed in ScriptScanner class. This class retrieves next id, next comment, next string literal etc.
2 "Syntactic rules" are implemented in ScriptSplitter whis is just a recursive procedural parser.

[kiview]

Hey @inponomarev, thanks for your PR, but unfortunately we will have to close it.

ScriptUtils has been mostly taken from the Spring-JDBC project as a convenient feature and we agree that it can't support all use cases and all SQL syntax variants. We also agree with your assessment regarding the difficulty to extend and maintain it. But that's precisely the reason why we don't want to invest further in this feature or refactoring it extensively.

Instead, we recommend to use the mechanisms provided by the vendor specific images, which mostly means using withCopyFileToContainer() and a specific path for init scripts. I am pretty sure it should also work for the use case you have in mind.

In the future, we'd suggest to reach out to us first via Slack or GH discussions, to avoid unnecessary work on features that won't get merged.

[kiview]

Re-opened after private discussion in Slack.

[kiview]

Can you PTAL @eddumelendez?

[eddumelendez]

Thanks for your contribution, @inponomarev !