PlanetJava
Custom Search

java-maven-plugins-mojo-user
[Top] [All Lists]

sql-maven-plugin: sql files need different delimiter configurations for

Subject: sql-maven-plugin: sql files need different delimiter configurations for packages and stored proceduees
Date: Sat, 19 Jun 2010 00:38:03 +0100
mvn sql:execute was working just fine for all out table definitions
and our insert statements using the default delimiter ";" semicolon.
For oracle, "create table"/"alter table"/"insert into table values"
statements all end in a semicolon. We have a couple of hundred of
these scripts to create database tables and insert data. The plugin
sends the sql to the database every time it sees a semicolon. So far
so good. Works a treat. Our scripts can also be run directly in the
oracle sqlplus tool too which is great.
So, my project just started writing some oracle stored procedures and
functions in separate files. Now stored procedures contain internal
semicolons as well as the usually the final ones and so the sql
execution fails, if the default delimiter is used. So we need to
change the delimiter and or the delimiterType
*just*for*the*stored*procedure*files*. Fine.
I would have thought that this was a common scenario for oracle and
db2 at least, as it keeps the scripts compatible with the databases
default tools (oracle sqlplus for example).
But the only way I have found to make this work is really ugly and I
was hoping there is a simpler way.  It uses "executions" of which I
need three. The middle one installs the stored procedures using a
delimiter of "/". The third one switches back to ; semicolon as it
uses inserts and the occasional stored procedure call.
One other annoyance (apart from the overcomplicated pom.xml) is that I
now have to say "mvn validate" instead of "mvn sql:execute". I
couldn't work out how to get the three executions to execute as part
of the same sql:execute goal, only as part of the same phase.
Is there  a cleaner way of doing this? Should I use an ant task for
this instead (via the plugin)?
(The obvious simple solution to me would be to use a delimiter
attribute within the srcFile tag. <srcFile
delimiter="nondeaultdelimiter">)
Anyway, here is the relevant extract of my pom.xml.
<plugin>
  <groupId>org.codehaus.mojo</groupId>
  <artifactId>sql-maven-plugin</artifactId>
  <executions>
    <execution>
      <id>tables-etc</id>
      <phase>validate</phase>
      <goals>
        <goal>execute</goal>
      </goals>
      <configuration>
        <srcFiles>
          <!-- Tables (must list files in order to satisfy integrity
constraints) -->
          <srcFile>src/main/sql/tables/audit_log.sql</srcFile>
          ...
          <!-- Views -->
          <srcFile>src/main/sql/views/sup_user_authorities.sql</srcFile>
          ...
        </srcFiles>
      </configuration>
    </execution>
    <execution>
      <id>packages-and-sprocs</id>
      <phase>validate</phase>
      <goals>
        <goal>execute</goal>
      </goals>
      <configuration>
        <delimiter>/</delimiter>
        <srcFiles>
          <!-- Packages and sprocs -->
          <srcFile>src/main/sql/packages/tools.sql</srcFile>
          <srcFile>src/main/sql/sprocs/insertauditlog.sql</srcFile>
          ...
        </srcFiles>
      </configuration>
    </execution>
    <execution>
      <id>seed-data</id>
      <phase>validate</phase>
      <goals>
        <goal>execute</goal>
      </goals>
      <configuration>
        <delimiter>;</delimiter>
        <srcFiles>
          <!-- insert initial data   -->
          <srcFile>src/main/sql/seeddata/user.sql</srcFile>
          ...
        </srcFiles>
      </configuration>
    </execution>
  </executions>
</plugin>
Cheers
Tim
---------------------------------------------------------------------
To unsubscribe from this list, please visit:
    http://xircles.codehaus.org/manage_email
msgmiddle
<Prev in Thread] Current Thread [Next in Thread>
Current Sitemap | © 2012 planetjava | Contact | Privacy Policy