Description
I am working as a new member on a Spring Boot Project that utilizes 2 different properties files for 2 different configurations related to DB settings:
- production mode: postgres SQL DB
- development mode: h2 in memory DB
Since I'm trying to minimize differences in the two scripts, I have started writing functions that will take case of differences related to date/time handling.
One example is adding hours since postgres uses intervals and h2 uses the oracle similar date_add function.
Unfortunately I am getting an exception for function creation statements that work in the console.
Existing Files
Config/Properties
spring.profiles.active=pre-prod
spring.datasource.url=jdbc:postgresql://localhost:5432/db
spring.datasource.username=postgres
spring.datasource.password=root
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.data=classpath:db/migration/postgres/db_functions.sql,classpath:db/migration/postgres/data.sql
spring.jpa.hibernate.ddl-auto=create
db_functions.sql
--Adds a cast to date to the specific statement
--for h2 simply make a wrapper
CREATE OR REPLACE FUNCTION db_pgres_cast_varchar_to_date(d VARCHAR ) RETURNS date AS $$
BEGIN
RETURN d::date;
END;
$$ LANGUAGE plpgsql;
--Common function for h2 and vct to add hours
--References: http://stackoverflow.com/questions/9376350/postgresql-how-to-concat-interval-value-2-days
CREATE OR REPLACE FUNCTION db_add_hours(d timestamp, hours int) RETURNS timestamp AS $$
BEGIN
RETURN d + (hours || ' hours')::interval;
END;
$$ LANGUAGE plpgsql;
Exception
NFO] org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor Shutting down ExecutorService 'createTaskExecutor'
Exception in thread "main" org.springframework.jdbc.datasource.init.UncategorizedScriptException: Failed to execute database script from resource [class path resource [db/migration/postgres/vct_functions.sql]]; nested exception is java.lang.ArrayIndexOutOfBoundsException
at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:509)
Update
I started debugging and see that instead of the split/parser code creating 2 statements, my scripts are being interpreted as 6 SQL commands:
Update 2
The problem seems to be with the implementation of splitSqlScript, definition:
public static void splitSqlScript(EncodedResource resource, String script, String separator, String commentPrefix,
String blockCommentStartDelimiter, String blockCommentEndDelimiter, List<String> statements)
throws ScriptException
Update 3
Since Spring Utils of Spring seems to be the problem I am trying a different syntax to create my functions:
--Adds a cast to date to the specific statement
--for h2 simply a wrapper
CREATE OR REPLACE FUNCTION vct_pgres_cast_varchar_to_date(VARCHAR ) RETURNS date
AS 'select $1::date;'
LANGUAGE SQL
RETURNS NULL ON NULL INPUT;
--Common function for h2 and vct to add hours
--References: http://stackoverflow.com/questions/9376350/postgresql-how-to-concat-interval-value-2-days
CREATE OR REPLACE FUNCTION vct_add_hours(timestamp, integer) RETURNS timestamp
AS 'select $1 + ($2 || '' hours'')::interval'
LANGUAGE SQL
RETURNS NULL ON NULL INPUT;
