Okay, so recently I was working on a new suggestion for my client.  It was a fairly simple request; create a snapshot of data to be used in monthly calculations, with the option to regenerate the snapshot at a later time.  The snapshot was easy.  I thought regenerating the snapshot would be easy too, but somehow I found a way to make it hard.

The first mistake I made was not writing a test first.  Really this had a lot to do with laziness.  The data that I am capturing is only available via a view into a proprietary database.  Somehow, I needed to figure out how to modify the data behind the view.  It seemed really hard, so I skipped it.

That decision came back to bite me.  I submitted the changes to the customer and soon they reported that they weren’t working.  Perhaps there was a little more than laziness here (perhaps arrogance).  I manually tested my changes, but I didn’t cover every aspect.  As it turns out, the snapshot was being updated correctly, but, the monthly calculations weren’t getting updated.

So now that I got a feature returned as a failure, I decided I better write that test.  In fact, following TDD principles, I knew I should write a test that would fail due to the reported defect.  I started on the test and hit a roadblock and was about to give up on it again.  I mean, this testing stuff is hard!

I chatted with a coworker asking his advice on how to test the data change behind the view.  He provided a simple and elegant solution.  We set up a test data script that would create a “test” table that duplicates the structure of the view.  Then we simply redirected the synonym from the view to the test table.  Now we essentially have a fake view.  And since it is a table, we can manipulate the data to our hearts content.

From this point I was able to continue writing my test.  Everything looked good.  I was sure to test all of the aspects of the new requirements that I could think of.  When I was done, I ran the tests and verified the defect.

Finally, I could fix the problem.  I figured out the problem and was able to fix it fairly easily.  Well, not so fast.  My test was still failing!  I spent hours on the issue.  I knew that the fix was correct.  I tried all kinds of debugging attempts.  I even changed to code to force it to be wrong for a different reason.  Everything seemed to be fine, except my test was still failing.  I was beginning to think that there was a bug in the test itself.

As it turns out, there was more than one problem with the original code.  The second issue was that I had an update statement that performed a join to the snapshot data, but was missing a critical condition in the join.  To uniquely identify the snapshot data, I needed to join to two fields and I forgot one.  So, the update was actually executing multiple times and the last time wasn’t the one I was expecting.

Had I persisted in my laziness and simply added the fix and sent it to the customer, I’d be embarrassed yet again.  But this time, the test saved me.  How many times do I have to learn this lesson?

Tags: ,