Igor's Blog

If you've ever worked on a PL/SQL project or any project with a good amount of database related work you probably have had to deal with multiple SQL files and their deployment across systems. This is exactly what I've come across recently. Even in the early stages of my PL/SQL project I had 11 separate SQL files that would need to be deployed to system and performance test environments, then propagated to production, production support, etc. To help with managing all this I decided that building a single 'super SQL' file would work best and I ended up using Maven to do it.
sqldev_ddl.png


Maven isn't exactly suitable for PL/SQL or SQL development, but it is possible to reuse its concepts and apply them in this case anyway. I went about creating the Maven directory structure as per usual approach, my SQL files went into ${project.basedir}/src/main/sql and then I had a pom.xml in the project root directory.

I didn't actually want to do anything too smart with Maven. All I was after was the ability to merge the individual SQL files into one big super SQL file. This file would be the distributable that would get applied across various environments.

After lots of reading about the easiest way to merge files in Maven I hit a bit of a roadblock since most of the focus was on merging ZIP, JAR or Property files, not what I was after. Eventually I found the Maven Merge Plugin which did exactly what I wanted, a simple merge (concatenation actually) of files. So that's what I went with.

I had a look at the source code for the plugin and didn't really like its 'how to run' part so ended up doing something different in my pom.xml file. I simply bound the plugin 'merge' goal to the 'package' phase so to run it all I had to do was...
 Maven
mvn clean package




This is what my pom.xml looked like...
 Maven POM
<?xml version="1.0" encoding="UTF-8" ?>
<project xmlns="http://maven.apache.org/POM/4.0.0">
<modelVersion>4.0.0</modelVersion>
<groupId>net.igorkromin</groupId>
<artifactId>SqlMergeExample</artifactId>
<name>${project.artifactId}</name>
<version>1.0</version>
<description>SqlMergeExample</description>
<packaging>pom</packaging>
<build>
<directory>${project.basedir}/target</directory>
<outputDirectory>${project.build.directory}</outputDirectory>
<plugins>
<plugin>
<groupId>org.zcore.maven</groupId>
<artifactId>merge-maven-plugin</artifactId>
<version>0.0.3</version>
<executions>
<execution>
<id>merge</id>
<phase>package</phase>
<goals>
<goal>merge</goal>
</goals>
<configuration>
<mergers>
<merger>
<target>${project.build.outputDirectory}/super.sql</target>
<sources>
<source>src/main/sql/tbl_mytable1.sql</source>
<source>src/main/sql/pkg_mypackcage1.sql</source>
...
</sources>
</merger>
</mergers>
</configuration>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>


You can see that packaging is set to POM, that is because we are not compiling any Java, etc. The rest is fairly straight forward, there is no dependency section like described in the plugin's readme, that's not required - Maven will figure it out for you.

The main part is really all those lines. The files are concatenated together using the order that they appear in the pom.xml file.

That's all there is to it really, just list all the files, run the package phase and you will have a super SQL file in the usual target directory.

-i

, , ,
About — I'm an enthusiastic software engineer and consultant interested in many fields including J2EE, programming, electronics, 3D printing, video games, wood working and gardening.
See my Resume for more information.
The views expressed in this blog are my own and not those of my employer.
comments powered by Disqus
My other posts you may like...
Programming, DIY, Games, Hacks, Tech and more.
Follow me on...
Current and Past Projects
See my Resume

Subscribe


RSS Feed

My Other Web Sites

Igor and Elise's Travels
Riverside Expressway Cam
StrFunc() Online
300 George St Blogumentary
Guru JSON-RPC Tester
Extrudifier Object Designer

Recent Blog Posts

Binding extra variables with PHP PDO returns no results

New Nintendo 3DS XL, R4i SDHC RTS Lite and DSDoom

Older versions of JD-GUI crashing on macOS Sierra

How does Seeed Fusion stack up as a low budget PCB fab

Where to find the last item (Robbie's lost lyrics) in GravityFalls Lake level

Detecting print requests to print contents of a DIV with JavaScript

Using Google DFP with AdSense on responsive pages

How to change the email subject line in Gmail

Force PHP GD library to load JPEG images that have invalid data

How to uninstall Samsung Smart Switch from your Mac

Recent Galleries

Space Food - Chocolate Ice Cream with Chocolate Chips

Legeod Star Wars AT-DP kit

DIY spare parts computer build with a RAIDMAX Anura case

Fake 'Lepin' brand Lego packaging

Hardwood garden bench with clear resin void filler

Fixing a 3D printer extruder that stopped heating up

Easily increase disk space in a Lenovo Ideapad 100S 14" laptop with an M.2 SSD

Making a multi-piece 3D printed solder spool holder stand

DIY indoor apartment grow light wiring

Good Friday Electronics fun Easter Bunny LED PCB Kit IBEABU-01.0

Top Categories

Blogs I follow

Matt Moores Blog
Georgi's FlatPress Guide
Perplexing Permutations
The Security Sleuth

Friends

RAWS Parts Online
Alpha Dimensions Hosting
Kristensen Photography
Ilia Rogatchevski
Travelling Fairy

Blog Activity

Blog Activity
Follow me on... 
     
...or subscribe for updates!

Don't show this again