Automating Unit Tests for PL/SQL Using Gradle

Gradle strikes again:

	ext{
	dbun = "SCOTT"
	dbpw = "oracle"
	dbinst = "orcl"
	}
	task deployUnitTests << {
	def sqlplusCmd = "sqlplus -s $dbun/$dbpw @../src/test/sql/run.sql".execute()
	sqlplusCmd.in.eachLine { line -> println line }
	}
	task runUnitTests << {
	def sqlplusCmd = "sqlplus -s $dbun/$dbpw @ut_run.sql".execute()
	sqlplusCmd.in.eachLine { line -> println line }
	}
	task deployDBUpdate << {
	def sqlplusCmd = "sqlplus -s $dbun/$dbpw @../src/main/sql/run.sql".execute()
	sqlplusCmd.in.eachLine { line -> println line }
	}

view raw utplsql-build.gradle hosted with ❤ by GitHub

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:

	buildscript{
	repositories{
	mavenCentral()
	}
	dependencies{
	classpath 'com.atlassian.jgitflow:jgit-flow-core:0.9'
	}
	}
	import com.atlassian.jgitflow.core.*
	import com.atlassian.jgitflow.core.exception.*
	task initFlow << {
	//your local working copy is now on the develop branch
	JGitFlow flow = JGitFlow.getOrInit(new File("."))
	}
	task releaseStart << {
	JGitFlow flow = JGitFlow.getOrInit(new File("."))
	//try to start our release
	try
	{
	flow.releaseStart("1.0").call()
	}
	catch (ReleaseBranchExistsException e)
	{
	//the release branch already exists, just check it out
	try
	{
	flow.git().checkout().setName(flow.getReleaseBranchPrefix() + "1.0").call()
	}
	catch (JGitFlowGitAPIException e1)
	{
	logger.error("Something went wrong checking out the release branch")
	}
	}
	catch (DirtyWorkingTreeException e)
	{
	logger.error("you have un-committed changes!")
	}
	catch (BranchOutOfDateException e)
	{
	logger.error("your local develop branch is behind the remote, please do a git pull")
	}
	//Note: your local working copy is now on release/1.0
	}
	task releaseFinish << {
	JGitFlow flow = JGitFlow.getOrInit(new File("."))
	//finish the release and push to the origin
	try
	{
	flow.releaseFinish("1.0").setPush(true).call();
	}
	catch (DirtyWorkingTreeException e)
	{
	logger.error("you have un-committed changes!")
	}
	}

view raw jgitflow.gradle hosted with ❤ by GitHub

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>