• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Using Maven to run nested SQL scripts (PostgreSQL)?

 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
etc

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?
 
Rancher
Posts: 989
9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
chris webster
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic