Support per-script statement separator configuration for SQL scripts [SPR-8817]

[spring-projects-issues]

Julian Sareyka opened SPR-8817 and commented

I have tried to setup an in-memory db with hsql. In one of my scripts there is a stored procedure. Database setup fails when executing this script.

I found out that this is due to automatic statement splitting in ResourceDatabasePopulator. Here all statements in a script are split on semicolons. So my stored procedure is cut into pieces (after p_date_value DAY;).

CREATE FUNCTION pkg_verweildauer.get_pub_ende (IN p_pub_start TIMESTAMP, IN p_date_unit VARCHAR(255), IN p_date_value NUMERIC) 
RETURNS timestamp
READS SQL DATA
BEGIN ATOMIC

DECLARE v_pub_ende timestamp;
case p_date_unit
	when 'DAYS' then
		SET v_pub_ende = p_pub_start + p_date_value DAY;
	when 'WEEKS' then
		SET v_pub_ende = TIMESTAMPADD(SQL_TSI_WEEK, p_date_value, p_pub_start);
	when 'MONTHS' then
		SET v_pub_ende = TIMESTAMPADD(SQL_TSI_MONTH, p_date_value, p_pub_start);
	when 'YEARS' then
		SET v_pub_ende = TIMESTAMPADD(SQL_TSI_YEAR, p_date_value, p_pub_start);
	else
		SET v_pub_ende = p_pub_start + 365 DAY;
end case;
RETURN v_pub_ende;

END;

A simple solution would be to add a parameter to the <jdbc:script> tag to disable splitting on file basis. Unfortunately ResourceDatabasePopulator cannot be exchanged in the EmbeddedDatabaseBuilder.

I had to write a post processing bean, to add the stored function.

---

Affects: 3.0.5

Issue Links:

• Error when parsing PMS stored procedure script. [SPR-8884] #13525 Error when parsing PMS stored procedure script. ("is duplicated by")
• Add configurable SQL statement delimiter to ResourceDatabasePopulator and JDBC XML namespace. [SPR-7700] #12356 Add configurable SQL statement delimiter to ResourceDatabasePopulator and JDBC XML namespace. ("is duplicated by")
• Support EOF as statement separator in SQL scripts [SPR-11687] #16310 Support EOF as statement separator in 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

3 votes, 2 watchers

[spring-projects-issues]

Chris Beams commented

Hi Julian,

It is certainly possible to make the ResourceDatabasePopulator configurable through an EmbeddedDatabaseBuilder constructor argument.

It is currently possible, as you may have noticed, to configure the statement separator using using ResourceDatabasePopulator#setSeparator

As you mention, there is no way to tell the ResourceDatabasePopulator to consider the entire contents of the file as a single statement. This could be added as a special kind of statement separator -- an "EOF" separator, if you will.

However, even if we were to add this per-file statement separation, I'm not sure that it would be useful in a general purpose way. The reason for this is that there is only one ResourceDatabasePopluator in use for a given EmbeddedDatabaseBuilder, and if the statement separator is set to the special "EOF" value, this setting will apply to all scripts added against the builder. This is probably not what you (or others would want), am I correct?

What it seems that you want is to be able to specify the separator on a per-script basis, which is a less trivial change to introduce.

We could conceivably introduce another method altogether -- 'addStatement(String)'. This would allow a kind of escape hatch for cases like this. You would have to deal with extracting the contents of the script file into a String, but once that is done, you could simply add it to the builder with no concern about statement separators, etc. Any value added there is assumed to be a single statement.

This probably wouldn't be exposed through the jdbc: namespace, but would rather assume the user is programmatically configuring an EmbeddedDatabaseBuilder, e.g. within a @Bean method in a @Configuration class.

Please provide your thoughts on the above, thanks!

[spring-projects-issues]

Julian Sareyka commented

Hi Chris,

"What it seems that you want is to be able to specify the separator on a per-script basis, which is a less trivial change to introduce."

• Yes, I thought that would be the easiest way. But with a closer look, it is not. It would be necessary to add the information of splitting(yes/no) to the Resource that encapsulates the script. The Resource-Interface does not provide the possibility to add any kind of metadata.

The EOF-Separator is something that mysql uses. When creating a function, you have to specify the delimiter that ends the statement:

delimiter //
create function...
do something;
end//

When adding this kind of logic to the ResourceDatabasePopluator, one has to be careful that it does not conflict with the proprietary logic of database engines (like mysql).
To be able to add the information of splitting(yes/no) to the Resource would be more transparent to use than a EOF-Separator.

To introduce the 'addStatement(String)' method would not really add value. I already use the datasource and a JdbcTemplate to achieve the same goal.

[spring-projects-issues]

Julian Sareyka commented

Hi Chris,

I found another solution which is probably easier. A tool called dbdeploy uses a DelimiterType for splitting.
With DelimiterType.normal the statement is split when the semicolon is at the line ending.
With DelimiterType.row the statement is split when the semicolon is in a line itself. The above example script would look like this:

create function...
do something;
end
;

Have a look: http://www.google.com/codesearch#SfNcgPiEUEg/trunk/dbdeploy-core/src/main/java/com/dbdeploy/database/QueryStatementSplitter.java&type=cs&q=delimiter%20package:http://dbdeploy%5C.googlecode%5C.com

[spring-projects-issues]

Chris Beams commented

Julian,

The concept of a configurable delimiter type makes sense. This would need to be introduced on a script-by-script basis, e.g. signatures of addScript methods on EmbeddedDatabaseBuilder and ResourceDatabasePopulator would have to be introduced something like the following:

• addScript(Resource) (existing signature)
• addScript(Resource, DelimiterType)

where DelimiterType is possibly an enum indicating end-of-line or end-of-file. The default delimiter type would be end of line.

This is just a sketch, but already would require fairly significant changes to the way that script resources are tracked and processed, and it's not yet clear whether such an addition would benefit enough users to go ahead with it. In the meantime, and as you mention above, one can always use a JdbcTemplate or other means to add the statement, so there is a reasonable workaround here.

For these reasons, I've moved this issue to the General Backlog and labeled it as 'under-consideration'. This gives it some time to catch additional attention, get votes, etc. We'll certainly return to this if it proves popular, and in the meantime, it's a good candidate for a patch.

[spring-projects-issues]

Julian Sareyka commented

Chris,

the delimiter type could be added as a general setting in the ResourceDatabasePopulator (as it is in dbdeploy). This would make the implementation much easier.

[spring-projects-issues]

Chris Beams commented

Julian,

This could be a setting on ResourceDatabasePopulator, but this assumes that all scripts added against that populator would have one delimiter type vs. another. If I understand your own use case correctly, you have a mix, where one file has end-of-line and another has end-of-file. Even if this is not your use case, one can imagine that it would be common (assuming this is a common need at all). I am imagining a situation in which users have typical schema creation and data population scripts (end-of-line delimited), and the occasional sproc definition or other multi-line script necessitating end-of-file delimiter. Do you agree?

[spring-projects-issues]

Sam Brannen commented

This issue is related to #17970.

[spring-projects-issues]

Sam Brannen commented

FYI: an EOF_STATEMENT_SEPARATOR was introduced in ScriptUtils in Spring Framework 4.0.4 (see #16310).

ScriptUtils.EOF_STATEMENT_SEPARATOR can naturally be supplied as the separator (i.e., statement separator/delimiter) in:

• ScriptUtils.executeSqlScript(Connection, EncodedResource, boolean, boolean, String, String, String, String)
• ResourceDatabasePopulator.setSeparator(String)
• EmbeddedDatabaseBuilder.setSeparator(String)

[spring-projects-issues]

Sam Brannen commented

Due to lack of interest from the community and the existing support for an EOF_STATEMENT_SEPARATOR that was introduced in ScriptUtils in Spring Framework 4.0.4 (see #16310), I am closing this issue as Won't Fix.