Sunday, September 26, 2010


I have been a big fan of both automated system regression test and unit regression test for many decades.  (It's a bad sign of how long you have been doing something that you can start to say something about your experience and say, "many decades.") 

My last consulting job involved writing many hundreds of unit tests in Visual Studio/C# for a point of sale product.  At my current job, the back end part of the software in written in Java and SQL.  While there were some unit tests in the code, they had not been run or maintained for a very long time--perhaps for a number of years. 

While waiting for others to make changes were blocking me from fixing stuff, I started looking at the existing unit test facilities.  It uses JUnit--and My Eclipse has a very nice interface to it, almost as nice as the unit test facilities built into Visual Studio.  In no time at all, I found the permissions problem that prevented the existing unit tests from running (most of which are now years of date), and fixed it.

In addition, one of the classes that I expect to change as soon as the DBA finishes his work needed a unit test.  It also needed a more elegant and robust interface to the SQL.  Let me explain, for those of you who have not had occasion to write code that interfaces to SQL.  In both C# and Java, there are libraries that interface to SQL stored procedures.  You call a method that executes a SQL command or stored procedure, and get back a series of returned rows.  There are two different method categories for retrieving individual columns from a row, so that you can store them into a class instance.  One category, which much of the current code that I am maintaining uses, retrieves each column by column number:

lastname = data.getString(2);
firstname = data.getString(3);
custNbr = data.getInt(4);

However, this is not a very robust technique.  What is someone changes the stored procedure, so that the last name column is no longer the second column returned, but the third or the fourth column? 

Instead, it is safer to retrieve based on the column name, not the number:

lastname = data.getString("last_name");
firstname = data.getString("first_name");
custNbr = data.getInt("cust_number");

The column name that the stored procedure assigns is what the various get methods use to retrieve the data for this column from this row.

Now, if the stored procedure does not assign a name to a particular column, retrieving by column number may be the only way to get the data--and some people who write stored procedures do not bother to assign a name to a column, even though it is dead simple to do so.  I am starting to change such stored procedures to assign names, for this obvious reason.  However, I wanted to make sure that my changes did not break anything, so I wrote a series of tests to verify that the data retrieval methods in this class returned the right data.  This way, when we start changing the table structure, and when I check in the changes to the stored procedure, I can painlessly verify that these data retrieval methods actually work the way that they used to work.

In addition, while writing these method tests, I found a couple of exception cases that did not return the correct data--so one more set of things to fix, and make sure that they will not come and bite us in the future.

Days like Friday are the days that I actually enjoy my job.

No comments: