As usual, a first post was followed by weeks of silence. This time I do have a good excuse though. Since that first post, the ensuing few weeks have been crazy at work and almost consumed all my brain power. Not quite all though…
During the intervening weeks, I was able to spend some quality time working with Python and PyTest and, I’m happy to report, I was able to complete my first automated PL/SQL unit-testing piece – more on that later.
Before delving into the specifics of that work I think it might be useful to begin by examining what is meant by ‘automated unit-testing’; what features and capabilities are included in – and excluded from – that term.
A little history
From what I’ve been able to gather so far, it appears that the development of automated unit-testing went hand-in-hand with automated application building; with the development and use of build tools like Maven, for example. By application in this context, I mean a collection of executable files that reside and can be executed on a host computer. This would include, for example, applications which are written in C, C++, Java, C#, Python, etc. I would categorize such applications as distinct from database applications which reside and are executed within a database instance.
These tools provide a framework for defining and resolving build dependencies and outputs. As such, they are also perfectly suited for defining and resolving automated unit test dependencies and outputs. Such test suites can thus be simply integrated into the project definition files and executed during the build process.
In Java projects, for example, a set of classes are created in the project code base to perform unit-testing. When performing a build, these classes are compiled along with the application classes and then executed to perform the unit tests. Generally, the test classes create and subsequently destroy any data or other resources the test cases require.
Things have traditionally been done a bit differently in the database world, at least in my experience. For most of the time I worked in this field there was no equivalent to performing a build – since there was no end product similar to an application executable. Typically, database developers would create and or modify database objects, test them by hand and then deploy to the database as individual objects.
Release management was handled by creating patch scripts associated with a release and executing them during a release deployment – all very manual.
So, a consequence of these different situations was that automated unit-testing kind of passed us by in the database application world. I think we all knew it went on out in the main application development world but it simply didn’t apply to us database folks – we were different.
However, time is always marching on and nothing ever stays static or true forever. The business of software development gets ever more complex and schedules and delivery dates are always pressing in on us. Now, more than ever, perhaps, is a good time to bring automation to database application development and unit-testing is a great place to begin!
My point above regarding the different nature of database application development would still be true today; however, one of the newer additions to my development cycle at work is a very clever tool named Liquibase. My team has been feverishly migrating our deployment processes onto Liquibase. Our primary application is very large and the work necessary to integrate all of its deployment into Liquibase was considerable. There are significant benefits to be gained though; hands-free deployment by non-developers to QA and other non-production environments being a major one. This blog is not the place to go into the Liquibase migration in great detail; however, the use of this tool has now enabled our database application development to be managed much more like a non-database application; we now use Maven to build it and so now have the opportunity to also incorporate automated unit-testing.
What is it?
Now we need to figure out exactly what to do and how to do it. There are several options for unit-testing PL/SQL development. Here’s a list of utilities that I found after a little research:
- Quest Code Tester for Oracle
- SQL Developer Unit Testing
- PL/SQL Commons
To this list, I would now add the Python/PyTest combination.
This list of utilities contains many different options and approaches – where to begin? As I continued reading about Python and PyTest, I decided to dig deeper into these tools first. This combination would, I felt, give me a quick introduction to the world of unit test automation as it is done in the non-database development world. I felt it was important to gain this perspective before launching into an evaluation of the dedicated PL/SQL tools listed above.
In my next post, I’ll review what was learned from this exercise and how it can be applied to database development.