PlanetJava
Custom Search

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

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

Subject: Re: sql-maven-plugin: sql files need different delimiter configurations for packages and stored proceduees
Date: Sun, 20 Jun 2010 09:58:18 +0100
Thank you for the reply (even if its not what I wanted to hear!) I
will give it a try.
On 19 June 2010 22:00, Robert Scholte <[email protected]> wrote:
> Hi Tim,
>
> You like already said, with delimiter and delimiterType you can do the stuff
> you want.
> And yes, that requires multiple execution-blocks. So it looks like you've
> configured that part of the pom as it should.
>
> Now the what-you-call annoying part:
> Maven does not support "running by executionId" by design. It's idea is to
> bind plugin goals to a specific phase.
>
> The solution is to use a profile, where you can put your sql-m-p. I believe
> it would even work if you define a defaultGoal in the build-section.
> By running 'mvn -PprofileId' you will explicitly run this profile. And by
> default the sql-m-p can be skipped.
>
> Depending on your profileId the number of chars can be even less than both
> 'mvn sql:execute' and 'mvn validate' ;)
>
> -Robert
>
>> Date: Sat, 19 Jun 2010 00:38:03 +0100
>> From: [email protected]
>> To: [email protected]
>> Subject: [mojo-user] sql-maven-plugin: sql files need different delimiter
>> configurations for packages and stored proceduees
>>
>> 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
>>
>>
>
> ________________________________
> Express yourself instantly with MSN Messenger! MSN Messenger
-- 
-o-
---------------------------------------------------------------------
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