Seeing is believing

Some times it really pays off to verify things with your own eyes.

I am a big proponent of automatic testing, and think that people should use it as much as possible, but I'm also aware of the limitations of automatic testing, and I've come across a great example of why fully automation is not enough.

Currently I'm working on an application which is very much reliant on stored procedures, and to ensure that these works as they are expected, we've added a lot of integration tests, where we run the code executing the stored procedures, and ensures that the data are as expected afterward.

One of these test was quite simple - it took the user id and a user role, and updated the user role of the given user identified with the user id.

The automatic tests we made ran as the should, executed successfully, and the role of the user was successfully updated. Or so it seemed. Unfortunately there was one minor thing we hadn't thought of, and it had to do with the nature of the application code, calling the stored procedures.

Given the limited options for user roles, they are implemented as an enumeration in the code. In C#, an enumeration (enum for short) is a set of numbers with an associated value (e.g. 1 - "Administrator"), so if you set the value of the enum to 1, it can be translated (with the ToString() method) into "Administrator".

As I said earlier, the stored procedure took two parameters - an user id and an user role. In the code calling the stored procedure, we did pass these two parameters along, but we forgot to "translate" the role from the integer to the actual human readable value. This meant that in the example given above, we would pass the value 1 down to the database, rather than the value "Administrator".

This was not detected since the code could correctly understand the value when it was fetched again, and thus it appeared that the database value was correct. Unfortunately this wasn't the case, since the association between the integer value and the text only exists in the code, and not in the database.

The only reason this error was found, before it caused problems elsewhere, was because I was looking at the database data directly in the database for other reasons. Had I not done so, this error would most likely not be found until other systems started using the database as well.

So, today's lesson is: while automatic testing is definitely important, it's also important to verify the results with your own eyes once in a while.