Support EOF as statement separator in SQL scripts [SPR-11687]

[spring-projects-issues]

Sam Brannen opened SPR-11687 and commented

Status Quo

Prior to Spring Framework 4.0.3, it was possible to supply a bogus statement separator (i.e., a separator string that does not exist in the configured SQL scripts) to ResourceDatabasePopulator with the effect that the entire contents of each script file would be interpreted as a single SQL statement. This undocumented feature was never intentional; however, some developers came to rely on it.

Changes made in conjunction with #14165 and #16184 cause such scenarios to no longer work. Thus, it would be beneficial to provide first-class support for treating the end of file (EOF) as a valid statement separator in SQL scripts that contain a single SQL statement spread across multiple lines without a trailing statement separator.

---

Deliverables

1. Introduce explicit support for the EOF marker as a statement separator when executing SQL scripts.

---

Affects: 4.0.3

Issue Links:

• SQL Scripts with newline separators no longer work [SPR-11560] #16184 SQL Scripts with newline separators no longer work ("depends on")
• Support multi-line SQL comments in ResourceDatabasePopulator and JdbcTestUtils [SPR-9531] #14165 Support multi-line SQL comments in ResourceDatabasePopulator and JdbcTestUtils ("depends on")
• H2 aliases are not supported in SQL ScriptUtils [SPR-15896] #20450 H2 aliases are not supported in SQL ScriptUtils
• Support per-script statement separator configuration for SQL scripts [SPR-8817] #13459 Support per-script statement separator configuration for SQL scripts
• Support triggers & stored procedures alongside standard statements in SQL scripts [SPR-13389] #17970 Support triggers & stored procedures alongside standard statements in SQL scripts

Referenced from: commits 9230b38, 1753f5d, 03ae8ee, cc0ae3a

[spring-projects-issues]

Sam Brannen commented

Completed for Spring Framework 4.1 as described in the comments for...

GitHub commit cc0ae3a:

Support EOF as statement separator in SQL scripts

Prior to Spring Framework 4.0.3, it was possible to supply a bogus
statement separator (i.e., a separator string that does not exist in
the configured SQL scripts) to ResourceDatabasePopulator with the side
effect that the entire contents of a script file would be interpreted
as a single SQL statement.

This undocumented feature was never intentional; however, some
developers came to rely on it. Unfortunately, changes made in
conjunction with #14165 and #16184 caused such scenarios to no
longer work.

This commit introduces first-class support for executing scripts which
contain a single statement that spans multiple lines but is not
followed by an explicit statement separator.

Specifically, ScriptUtils.EOF_STATEMENT_SEPARATOR may now be specified
as a 'virtual' statement separator to denote that a script contains a
single statement and no actual separator.

And GitHub commit 1753f5d:

Make EOF_STATEMENT_SEPARATOR XML friendly

Changed the value of ScriptUtils.EOF_STATEMENT_SEPARATOR from
"<<< END OF SCRIPT >>>" to "^^^ END OF SCRIPT ^^^" so that the angle
brackets do not have to be escaped in XML configuration files.

[spring-projects-issues]

Sam Brannen commented

Backported to Spring Framework 4.0.4 in the following GitHub commits:

• 9230b38
• 03ae8ee