File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Ant, Maven and Other Build Tools and the fly likes Using Maven to run nested SQL scripts (PostgreSQL)? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Engineering » Ant, Maven and Other Build Tools
Bookmark "Using Maven to run nested SQL scripts (PostgreSQL)?" Watch "Using Maven to run nested SQL scripts (PostgreSQL)?" New topic

Using Maven to run nested SQL scripts (PostgreSQL)?

chris webster

Joined: Mar 01, 2009
Posts: 2289

I'm working on a Java/PostgreSQL project using Maven for the build process. Currently, we use the SQL plugin for Maven to run various DB scripts for each sprint. This simply fetches the scripts from a known directory and executes them in order. We want to change how we call our DB scripts, so that we can bundle particular pieces of work for each story within the sprint and execute/deliver the stories separately if necessary. So my idea was to have a "main" script for the sprint, which could then call a set of "story" scripts in the appropriate order. Because there may be dependencies between stories, we want to be able to specify the order they should be executed. For example:

Sprint05.sql calls:
--> Story99.sql
---->Story99.sql might also call Story99a.sql, Story99b.sql etc
--> Story33.sql

If we want to run the stories in a different order, we can simply change the order of the calls from Sprint05.sql. This is really easy for the DB developers to maintain, and I've done this many times using Oracle and SQL*Plus without Maven i.e having a "main" SQL script to control the process then various subordinate scripts to run specific steps, but the combination of PostgreSQL and Maven is new to me. As far as I can tell, you can only call one SQL script from another in PostgreSQL if you are running them via the PSQL client. But my impression is that the Maven SQL plugin actually runs the SQL directly in the database (via JDBC? not sure), so how do I persuade Maven to let me run one SQL script that calls another? Alternatively, is there a way just to provide the Maven SQL plugin with a simple list of the scripts to run in a specified order, without having to get into yet more XML config files? Or any ideas for alternative appraoches that would be easy to implement without downloading extra plugins (we can't do that in my workplace) or messing around with XML every time we add a story to the sprint scripts?

No more Blub for me, thank you, Vicar.
E Armitage

Joined: Mar 17, 2012
Posts: 989
The configs for the sql-maven-plugin here suggests that you can specify multiple fileSets and srcFiles (not sure what the difference is). Maybe you can use maven profiles or some filtering to decide which ones to include if your file order is not dynamically determined by the sql scripts themselves.
chris webster

Joined: Mar 01, 2009
Posts: 2289

Thanks for the tip.

But we've decided to forget about this idea for now. It was meant to be a simple convenient mechanism to make our DB build process easier, but it seems like more trouble than it's worth to persuade Maven to play along.
I agree. Here's the link:
subject: Using Maven to run nested SQL scripts (PostgreSQL)?
It's not a secret anymore!