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:
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?
The configs for the sql-maven-plugin here http://mojo.codehaus.org/sql-maven-plugin/usage.html 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.
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.