Category: unit testing

Categories
database unit testing

Database Unit Testing: It’s Tricky

I’ve been aware for a while that I really should be doing some database unit-testing along with the main unit-testing I’m already doing of the other code. With databases there’s a bunch of stuff I’d like to test apart from the logic inside any executable code:

  • Relationships – the database model should support certain types of queries, make sure that the more common ones work as needed
  • Query Execution Time – to verify that indices are present on SELECT’s and to monitor the cost of insertions and as a general (and fairly gross) monitor of performance
  • Arbitary Data Values – some data in the database is ‘special’. It’s always like that, it’s data that you don’t get from another source. It’s static data that makes your abstractions work. When it’s there everything is ok, when it’s gone you’re screwed
  • Constraints & Triggers – constraints and triggers occassionally get dropped when maintenance occurs when they don’t get put back things go south
  • Permissions – certain types of activity in a database should be prohibited ensure that these protections are in place for a class of users

There’s probably a lot more I could do too, but this will do to begin with. In the past I’ve spent significant investigative time hunting down problems that originated from some initial assumption being violated. Since I don’t like feeling violated, at least not on a Thursday, it seems like I should unit test what I can to get early warnings before the shit-storm hits.

So I did what any lazy, self-respecting developer would do I went looking for some code that someone else had written that I could steal. T-SQLUnit looked like the best match for my needs so I downloaded it and checked it out. Now, before I upset anyone I should say that T-SQLUnit is ok. But it suffers from a few fairly major drawbacks. There’s nothing wrong with TSQLUnit per-se it’s just that all database unit testing frameworks that are written in SQL are broken. Here’s a few lowlights:

  1. Results of expressions can not be inputs to stored procedures making traditional unit testing Assert statements awkward
  2. Catching all exceptions and reporting automatically that as a failure (ala jUnit) is messy requiring a BEGIN/END TRY around every test block

It’s the first one that makes life hard because all your assertions have to read something like:

IF @MyVar <> @TheirVar
THEN
     EXEC ts_failure 'My variable doesn't equal your variable'
END

When what you really want to write is something like:

EXEC ts_assert @MyVar <> @TheirVariable,  'My variable doesn't equal your variable'

I don’t know, perhaps I’m just not smart enough but I could not see anyway to make something like the above work without ending up with a syntax so verbose and ugly that even Eurocrats would balk a little. So bad that you might as well have just used a bunch of IF statements in the first place. Also, a direct consequence of not being able to have an ‘assert’ stored proc is that you can’t easily count the number of assertions you make to report them later. Now whilst this is just icing on the unit-test cake it’s still a nice feature to have and adds to the warm fuzz after a test run.

If that was hard then testing permissions related activities is next-to impossible. This is because your unit-testing framework is running as a particular user in a particular session. For you to be able to test particular permissions you might need to disconnect and reconnect as a different user. Well, it’s not impossible it’s just … a bit tricky

The obvious thing to do, then, is to completely give up on SQL unit testing frameworks and go back to your programming language of choice. As far as is possible you want to hide all the internals of dealing with the database and leave just the pieces you need to setup, run and teardown your tests. To do this I made a helper class to do all the heavy lifting by: connecting to the database, running any query, timing the execution, processing the results and storing them somewhere. Finally I made my class provide a function based query interface so that I could then write test code using NUnit style assertions against it. Creating this custom class took only a few hours. Once I’d created it I could hand all the testing framework stuff to my old-friend NUnit. This technique worked well for me and integrated nicely with my existing code tests.

Categories
programming unit testing

Too fast to live, too impatient to unit test

James Dean, James Dean, you bought it sight unseen.
You were too fast to live, too young to die, bye bye. — The Eagles

The benefits of unit-testing are enormous. I don’t think anyone can deny that, but to make unit-testing work for you actually have to write tests.

As I see it I have three (no four) main problems with unit-testing which ultimately boil down to the time and hassle it takes to write certain types of tests:

  1. The generally approved technique is to start with the test and start by making a test that fails for a specific functionality. You can’t compile the failed test until you’ve written some code to call. Then you find yourself writing something that will take a few arguments. For simple methods that take some arguments and return some values means coding some stub, switching to the unit-test view, write a failing test, switch back and write the functionality. This method probably took 3 or 4 times longer to write than if I hadn’t tried to unit-test it. I know that I will get that payback later but my life contains plenty of context switches already and having to add these extra ones is a pain. I know (but can’t find a link) that there’s a special tool for Ruby that automates this process. You write a test with a well known name, it creates all the stubs and makes it throw exceptions. I need this feature!
  2. Sometimes you will end up with abstract classes in your design. These are awkward to test for a number of reasons, one of which is that you have to create a unit-testable implementation to test it since you can’t instantiate it because it’s abstract. This means creating especially derived inner classes for your abstract class.
  3. Unit testing data sources. Some unit-testing (like Rails for example) include the data sources in the test. But I’ve heard the view that this is a form of integration-testing since you are testing more than just the class when you get a bunch of data from a database. This raises the ugly question of how to test data source access classes without a data source. It’s a similar problem to the abstract class problem in as much as you need a special instance of a class for your unit-test purposes. To serve this purpose you can use a mocking framework like NMock or jMock or even Mockpp! Great though these tools are it’s very easy to create a monster mock object that has all sorts of complex and hard-to-maintain behaviour.
  4. Tests need to be maintained. Like the code you’re testing the tests themselves have to be maintained when refactoring work takes place because they tend to start failing a lot. This maintenance usually comes in two forms: really nasty problem that I’m really glad I found and annoying little deficiency in the test that means it needs tweaking to work. This illustrates that tests themselves can have good and bad design and we can save downstream time (with yet more upfront cost) if we try to make the tests less brittle.

It’s the second and third points that prove to be a tremendous time-sink for me. You want to unit-test the behaviour so you have to create a custom class or mock to do it. Before you know it the mock / custom class requires as much effort as the original code. So my development is half as effective.

I know that all this is an upfront cost that will get repaid many times over later. I even know that the writing of the custom classes will actually help to ensure that there is a clean contract between base classes and their derived classes. And I’ve already mentioned that good test design will save me time later. But all that upfront time adds up and I need to deliver something. Going back and doing it later is just not an option either. Once the code is written the chances that you’ll go back and add that very important unit test are slim. There’s new stuff to deliver.

I don’t think that James Dean would have written software and I’m positive if he had he wouldn’t of written unit-tests. But if I’m to write code that ‘lives’ I need to. It’s just forcing myself to find that time …