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.

About these ads