Automating Unit Tests for PL/SQL Using Gradle

Gradle strikes again:


Yes, it really was that easy!

The Oracle database
This post assumes that you have an Oracle database available. I used an Oracle 10g XE instance running on localhost to develop this post. XE is free to download and is sufficient for development purposes.

I had a SCOTT schema with the password "oracle" in the database and also installed the utPLSQL schema, which is a necessary step to following this example. To replicate what I done I recommend downloading the Oracle Developer Days VM.

NOTE: If you install the utPLSQL schema into an Oracle 10g XE database, make sure that you have granted access on UTL_FILE to public as explained here (remember to connect as sysdba when doing this otherwise it won’t work).

The structure I used for this example is:

.
|-build
|---build.gradle
|---ut_run.sql
|-src
|---main
|-----sql
|-------run.sql
|---test
|-----sql
|-------run.sql

Running the JGitFlow library from within Gradle

I recently seen this blog post and thought it would be really cool if I could integrate git flow into my build scripts. Whats great about this is that because this is a java library it was extremely painless to integrate.

With a little effort this could easily be converted into a plugin for Gradle, but here's my example:


This is only a snippet, so not all of the library is shown, but it works very well as a proof of concept.

Oracle Linux 5.5 with Guest Additions - VirtualBox

I wanted a sandbox VM, but for it to replicate the production servers it needed to be based on Linux 5.5. As I was running this on my local machine in VirtualBox I also wanted Guest Additions installed.

VirtualBox 3.1: Beginner's Guide is a good book to start with if you're new to VirtualBox and want to know how to create virtual machines on your desktop.

So here's how to get Guest Additions installed:

1. Download and Install Oracle Linux
2. Download and copy the appropriate yum configuration file in place, by running the following command as root:

# cd /etc/yum.repos.d
# wget http://public-yum.oracle.com/public-yum-el5.repo

Guest Additions requires the following in order to install successfully:

kmod-ovmapi-uek
libovmapi
libovmapi-devel
ovmd
python-simplejson
xenstoreprovider
ovm-template-config
ovm-template-config-authentication
ovm-template-config-datetime
ovm-template-config-firewall
ovm-template-config-network
ovm-template-config-selinux
ovm-template-config-ssh
ovm-template-config-system
ovm-template-config-user

These can be downloaded by running the following command once the yum configuration is in place:

# yum install libovmapi xenstoreprovider ovmd python-simplejson xenstoreprovider

Gradle/Groovy - get Git build meta information

When creating a release package its nice to maintain a copy of all the metadata. I like to do this using the power of Groovy from within my Gradle build scripts.

 import groovy.xml.MarkupBuilder  
 task xmlGen << {  
      def tag = "git describe --abbrev=0 --tags".execute().text  
      def sha = "git rev-parse HEAD".execute().text  
      def fw = new FileWriter("build_info.xml" )  
      def xml = new groovy.xml.MarkupBuilder(fw)  
      xml.build(id:tag){  
           ProjectName("ORCA")  
           SHA1(sha)  
           Date(new Date())  
           Components("component1, component2, component3")  
      }  
 }  

And the output is:

 <build id="v1.3.0">  
  <ProjectName>MyProject</ProjectName>   
  <SHA1>1f661e69797dc23281d2955d6ca2dda3cdd81dc0</SHA1>   
  <Date>Thu May 02 16:14:33 BST 2013</Date>   
  <Components>component1, component2, component3</Components>   
 </build>  

Removing plain text passwords in Gradle

To ensure no plain text passwords are stored on the servers the following piece of code can be used encrypt and decrypt passwords.

 import java.security.*  
 import javax.crypto.*  
 import javax.crypto.spec.*  
 class DESCodec {  
   static encode = { String target ->  
     def cipher = getCipher(Cipher.ENCRYPT_MODE)  
     return cipher.doFinal(target.bytes).encodeBase64()  
   }  
   static decode = { String target ->  
     def cipher = getCipher(Cipher.DECRYPT_MODE)  
     return new String(cipher.doFinal(target.decodeBase64()))  
   }  
   private static getCipher(mode) {  
     def keySpec = new DESKeySpec(getPassword())  
     def cipher = Cipher.getInstance("DES")  
     def keyFactory = SecretKeyFactory.getInstance("DES")  
     cipher.init(mode, keyFactory.generateSecret(keySpec))  
     return cipher  
   }  
   private static getPassword() { "secret12".getBytes("UTF-8") }  
 }  

Things to note in this script are: encode - this method takes a string, encodes the string against a key and returns an encoded string decode - this method takes the encoded string and key and decodes to the plain text password key - this is the string that is set to "secret12" in the code above. This should be changed and owned on a per environment basis. To further improve ease of use this should be parameterised. Usage: task setPassword << { println new DESCodec().encode("password") } task getPassword << { println new DESCodec().decode("VGf1XPEzkT7g6D2EhjMlrg==") } How you use this in your gradle script is entirely up to you. One suggestion I have would be to pass in the Key as a parameter to your script.

Jenkins Slave Environment Variables

Spent quite some time hitting my head against the wall with this issue. Turns out environment variables need to be set in Jenkins because no end of changing variables on the slave will make a blind bit of difference.

So, I suggest if you have environment variables that need setting up, such as your VCS, or build tool you go via Jenkins initially, then if you get no joy from that use the Shell/Batch build step.

Git for all ages

At my company there's a wide range of technologies being used; some as old as the Mainframe, and newer technologies such as web services. This means that we also have a wide range of age groups - some from the days of COBOL, and other newbies such as myself from the C++ and Java days.

Something I've come to learn is that the old timers are not open to change, and if they have to change they want it to be easy. I'm sure you can imagine the looks on their faces when I started showing them how to use Git bash - anyone would think I just punched a small child.

TortoiseGit is a very useful tool that nicely hides the implementation of Git from the user. I found the old timers were far more receptive to Tortoise than they were of Git bash. I still finished the Git training in bash, but I also offered a small section on Tortoise at the end. The reason for this is that I wanted them to truly understand what Git was doing before hiding that behind a nice UI.