Unit testing bad

More on unit testing and TDD – it’s good and it’s got momentum. As Steven Feuerstein commented on a previous post of mine, “deficient testing is generally an ongoing worldwide crisis in the software industry”. And no more so than in the database, I’d warrant.

However, whatever tier we’re testing, with whatever technology, I’m uncomfortable with unit testing dictating which methods/procedures/etc are public and which are private within a class/package/etc. Taking it to the extreme, you’re almost saying that unit testing is more important than security and code access control.

I believe that one possible approach in Java is to circumvent the access control using Reflection. Not something I know much about.

But there is no such mechanism in the database.

One approach might be to use conditional compilation. However, in general, my feelings are that conditional compilation solves a nonexistent problem. I don’t like the idea of testing a piece of code that might have conditional compilation clause changing what and how it does something between Dev, Test and Prod environments or between different versions.

In general, if you can refactor a routine into a packaged function or procedure then that makes sense and it makes sense to expose it as a testable unit.

But all this does not make private procedures and functions redundant. No way.

Unit testing good

I’ve written before about unit testing in the database. And I was very pleased to get some comments on that post from Steven Feuerstein.

In years gone by, what little unit testing I have done has involved hand crafting my own tests in PL/SQL or whatever language was involved, with the obvious problem of errors in the tests, writing tests based on code I’ve already written.

At my current client, I have been evaluating Qute Quest Code Tester for Oracle. They had been using utPLSQL and Ounit but one look at the amount of test code that you had to write left me distinctly unimpressed.

BUT I have been very pleasantly surprised with Quest Code Tester for Oracle. I say surprised, scrap that, I mean why would you be with a man like Steven Feuerstein at the helm? What I mean is that it has been far easier and far less painful than I had imagined. Sure, there are a few featurettes to be ironed out still, but on the whole I am very impressed. So far, very little code to write, point and click, set up some custom (reusable) data sets, run some tests and get a barrage of green smiley faces telling you everything’s ok.

If you’re not using it, I urge you to have a look. But in the flesh on a pilot project. The trial download might be ideal for a short sprint.

Database testing – Data Quality

As I was saying…

One overlooked area is probably data quality testing.

In fact, from some of the schema designs I’ve seen over the years, I’m sure it is.
Notably in missing primary/foreign/unique keys and other constraints.

To what extent can or should we be testing whether the implementation of a data model enforces the rules, relationships and constraints that it should?

Can any “is”/”has” statements be validated against it?

How should we best go about this?

And are there any tools that can help us do this?

This is definitely needed. And it’s not just one off testing either, because data models evolve constantly and we want to check that a release does not degrade the quality of our data – that
a dropped unique key/index is OK, that a relaxed constraint does not compromise our data.

Aptly, when doing a bit of searching just earlier, I noted that Scott Ambler was
writing about testing the very same.

Database testing – Load Testing

As I was saying…

I’ve mentioned load testing frameworks previously and there’s not much to add apart from I have singularly failed to either fully investigate or implement either of Hammerora or Simora. Yet.

Both work by replaying trace files to generate accurate activities and workload. Simple ingenuity.

Other options include Swingbench and ORION.

From the initial blurb, Orion seems more of an IO thing. But Swingbench looks good too. Dominic Giles has another utility, Datagenerator, which I imagine might tie in nicely with DBUnit if you needed some volumes of data to do certain unit tests, maybe.

Database testing – Unit Testing

As I was saying…

One of my unresolved concerns about unit testing is that there is a gulf between where unit testing has come from and the best way to write performant database code. How well does the tradition unit testing and TDD approach sit with set-based operations in code.

What I am going on about?

Well, it seems to be that unit testing sits very nicely with refactored code, where little routines can be packaged into a function with a defined responsibility that can be reused by many other components.

Where this gives me a problem, is that this encourages you to write SQL with lots of row-by-row functional lookups which are tried and tested, trusted and known to work. But this can cripple your SQL performance.

The flipside can require you to repeatedly write the same lookups inline in various bits of SQL. Better performing but you’re having to test many of the same things for each piece of SQL. Needs some thought that one.

For now, for me, performance wins every time.

Anyway.

There are a number of tools out there to help database testing of code modules, many of which have benefited from Steven Feuerstein’s involvement. Some of the options are GUI led, some require knowledge of Java or some other language.

The core technology is utPLSQL – “developed by Steven Feuerstein … it allows the automated testing of packages, functions and procedures.”

Extending utPLSQL, we have Ounit. “Ounit offers a basic graphical interface to utPLSQL”. Ounit was designed by Steven Feuerstein who has gone on to move the concept on further with what is now known as Quest Code Tester for Oracle, which is now a commercial product from Quest but which also offers trial and freeware versions.

Another tool out there is DBunit- ” JUnit extension targeted for database-driven projects that, among other things, puts your database into a known state between test runs”.

Also, another option that has been highlighted to me recently is DBFit, a version 0.1 utility.

In these early stages of my understanding, I’m not convinced that any one of these options ticks all the boxes. It sounds like utPLSQL is going to be involved. A GUI front end might be involved but it needs to be integrated with other unit testing and build results – so it might be as simple as collating the results and sending them in some format to a web page somewhere.

Definitely, a significant feature of any solution should be some sort of integration with build technologies such as Ant, Maven and Cruise Control – we want to automate testing as much as possible, after all. But if the underlying technology for the tests is PLSQL then calling that is not going to be much of a problem.

Also, not having played with these yet, I have questions over whether these tend to best suited to just testing procedures which do calculations, etc on inputs rather than results sets from complex SQL which may or may not be joining the right tables and returning the right values. And how well can we adapt them to test batch jobs.

I expect that we can adapt some of them pretty well to do most things that we want. I’m going to have a decent play with all of them and I wouldn’t be surprised if out of the big mix came a little bit of utPLSQL along with some DBUNIT to put up and tear down the data required.

Database testing

For too long, I have put off tackling the under developed area of database testing – that is, my knowledge of the tools out there and my usage thereof. Which at the moment, is pretty much non-existent.

That’s not to say that I don’t test, I do. But like most places, I bet, that testing is frankly inadequate, particularly when compared to the testing that is done in other tiers of the application.

What’s the problem with what is often done now?

Well, depending on the application, the testing is deferred to the DAO layer of the application.
In other words, often we don’t test a lot of the database code in isolation, we rely on the tests done in the application. In some cases, maybe this isn’t inappropriate, but it feels like a cop out.

And with more complicated database only code in batch jobs and alike, well, the trouble is that it’s ad-hoc. If there’s anything, then there’s some bespoke scripts – test code written by me to test code written by me. And one of the biggest issues, apart from being hard to follow and the tests themselves not being bulletproof, is that the passing or otherwise of these tests relies on visual inspection of the results.

I know I’m not alone in this.

What sort of isolated database testing do we need?

• Unit testing.
• Load testing.
• Schema design / Data quality testing.

Put all these together in a toolkit which also includes a central trace schema that includes a DBMS_PROFILER installation, some public synonyms for a single plan table, runstats_pkg, tables and procs for configurable session and module logging and some non-extra-license-requiring ASH-like session level wait event capturing you’ve got something that many Oracle installations can benefit from.