Tuesday, October 18, 2005

How To Do DB Testing (Part II, Bulk Analyze)

So with all of the SQL expressions contained in query manager subclasses, the next step was to collect and run all of the queries at once. This had two big advantages.

First, all of the SQL could be exercised. Probably the biggest headache in writing SQL is that there's no real way to "compile" it, even to get a syntax sanity check, (without going into stored procedures and other DB-specific stuff). Our way, we have a hierarchy of classes that contain all of the SQL strings, and a TestAllQueries JUnit class that calls each method on each query manager class. Running one test runs all of the queries in the system. (It ignores the results; that's for other tests.)

Second, with a tiny tweak in how the methods are called, instead of running the query we can 'EXPLAIN ANALYZE' it, and run the output into a text file for further examination. One run, and I can see what queries are doing table scans, and what index that one query is using. Quite handy.

And with all of the application code not knowing anything about JDBC, but rather using an Iterator, we can put together a mock database fairly easily, just by instantiating and throwing HashMaps into a Vector, and returning the iterator() from it. Acquiring the database from a factory means that each db-using class is now easily testable without having to setup a separate database and worry about the attending headaches.


Post a Comment

<< Home