Sql + RegEx = Awesome

NOTE: This is not a post about regex (if you want more info check out http://www.regular-expressions.info/ or http://www.zytrax.com/tech/web/regex.htm and http://gskinner.com/RegExr/ is a great tool for testing your regular expressions).

One of my clients called and asked me to create an auto-complete list of employee names – no problem. I cracked open the table and started looking at the data only to realize that there were names that were “not valid”, i.e., they contained invalid characters, numbers, periods, etc. When I questioned them about it, the response was “just filter those out”. Now I was ready to get to work.

Step 1, filter out all the names that begin with a number:

SELECT
    FirstName, LastName
FROM
    dbo.Employee
WHERE
    FirstName NOT LIKE '1%'
    AND
    FirstName NOT LIKE '2%'
    AND
    FirstName NOT LIKE '3%' 
    ...
    AND
    FirstName NOT LIKE '9%' 

Great, now on to step 2, filter out names that begin with a dash, period, comma, or backslash:

SELECT
    FirstName, LastName
FROM
    dbo.Employee
WHERE
    FirstName NOT LIKE '1%'
    AND
    FirstName NOT LIKE '2%'
    AND
    FirstName NOT LIKE '3%' 
    ...
    AND
    FirstName NOT LIKE '9%'  
    AND
    FirstName NOT LIKE '-%'
    AND
    FirstName NOT LIKE '.%'
    AND
    FirstName NOT LIKE ',%'  
    AND
    FirstName NOT LIKE '\%'  

Awesome, step 3, abandon all of that and find a better way. In reality, step 2 never happened and step 1 only lasted past the first where condition. I knew there had to be a better way and it is inline RegEx, and it is good.
Let’s rewrite the above query so you can see what it looks like using RegEx:

SELECT
    FirstName, LastName
FROM
    dbo.Employee
WHERE
    FirstName NOT LIKE '[0-9-\.,\\]%'

It’s that simple!

In my case, it was the best solution for filtering out data, but you could just as easily use this technique to create complex select statements too. Give it a try and enjoy!

Structuring Unit Tests

Phil Haack wrote an article entitled "Structuring Unit Tests" (I highly recommend reading it) and it started us talking about how we could adopt this method of test structuring in our projects. The basic structure is a test class that contains test classes for each system under test.

We are a test-first shop and use MSTest as our testing tool of choice. So, step 1, write some tests that follow the structure (I am just going to reproduce Phil’s example using MSTest):

[TestClass]
public class TitleizerTests
{
[TestClass]
public class TheTitleizerMethod
{
 [TestMethod]
 public void ReturnsDefaultTitleForNullName()
 {
     //test code
 }

 [TestMethod]
 public void AppendsTitleToName()
 {
     //test code
 }
}

[TestClass]
public class TheKnightifyMethod
{
 [TestMethod]
 public void ReturnsDefaultTitleForNullName()
 {
     //test code
 }

 [TestMethod]
 public void AppendsSirToMaleNames()
 {
     //test code
 }

 [TestMethod]
 public void AppendsDameToFemaleNames()
 {
     //test code
 }
}
}

Right away we noticed two problems. When you use the keyboard shortcut Ctrl+R, C all tests in the class are run. This works great using this structure because it runs all tests in the SUT class allowing you to isolate the scope of tests to run. The problem is when you want to run all tests in the top level class. If you try Ctrl+R, C at the top level, all tests in the namespace get run. This was easily solved by creating a unique namespace for the test class to reside in:

namespace TestStructure.UnitTests.TitleizerTestContainer
{
[TestClass]
public class TitleizerTests
{
[TestClass]
public class TheTitleizerMethod
{
[TestMethod]
public void ReturnsDefaultTitleForNullName()
{
//test code
}

Now we can run all tests in the class without issue.

The second problem is code duplication. If some setup is required for my class before I can perform my tests, I have to repeat it for each class because nested classes do not have access to their parent members. Our solution to this is to make all of the children inherit from the parent test class like this:

[TestClass]
public class TitleizerTests
{
protected Titleizer target;

[TestInitialize]
public void Init()
{
 target = new Titleizer();
}

[TestClass]
public class TheTitleizerMethod : TitleizerTests
{
 [TestMethod]
 public void ReturnsDefaultTitleForNullName()
 {
     //act
     string result = target.Titleize(null);

     //assert
     Assert.AreEqual(result, "Your name is now Phil the Foolish");
 }

 [TestMethod]
 public void AppendsTitleToName()
 {
     //act
     string result = target.Titleize("Brian");

     //assert
     Assert.AreEqual(result, "Brian the awesome hearted");
 }
}

This is obviously a contrived case where no actual setup is required, but now the TestInitialize method will run before each test in my child classes.

Plus the test are much easier to read:

We are just stating to experiment with this test structure, but so far it looks very promising.

Decisions, decisions

As developers, we are constantly making decisions about how to implement a feature, a business rule, a data schema, etc. Sometimes we even find ourselves questioning the decisions of others. For example, have you ever been visiting a website, or using a mobile application and asked yourself “why did the developer choose to do it that way?”

Last night, I stumbled upon an article that describes how some of the decisions made at the language level affect us as developers, and their cost impact on businesses. The choice of the C language to use NULL terminated strings may be the most expensive mistake in the history of programming!

Enjoy.

Warning: this really is for the geekiest among you.

http://queue.acm.org/detail.cfm?id=2010365

Upcoming Speaking Engagement

Come join the largest free tech conference in Louisiana – there are already over 500 registrations! Come by and get your day started off with my Zen Coding session.

Saturday August 5th, 2011
Zen Coding
SQL Saturday Baton Rouge
8:30-9:30pm
More Info

Parking Lot Programmer

In this industry, we are all familiar with the term "duct tape developer". Those who throw caution to the wind, who turn their noses up at such things as design, test coverage, and the acquisition of new knowledge. Those who scoff at the notion of principles, patterns, and discipline. Those who ride their proverbial Ford F-150 full speed ahead, blindfolded, with a can of on sale malted beverage in one hand and honking the horn with the other. They leave behind a cloud of burnt fossil fuels, a mighty “YEEHAW” ringing in the minds of those within earshot, the indelible afterimage of a “git ‘er done” bumper sticker, and skid marks in the codebase. Well today I am going to simplify that image and take it out of the pickup and move it to the parking lot.

A parking lot programmer is like a buddy who gets drunk and loses his keys in the parking lot. You go out to check on him and he's looking for the keys under a streetlight even though he knows he lost them somewhere else. So you ask him "why are you looking here?" and he looks up and says confidently "because there's more light here".

Upcoming Speaking Engagements

Saturday January 30th, 2011
Zen Coding
SQL Saturday Houston
11:00-12:00pm
More Info

Tuesday February 1st, 2011
Tales from the Code
11:00-12:00pm
Fayard Hall Room 218
Southeastern University
More Info

TDD – But What do I Test?

I am a huge proponent of Test Driven Development (TDD) and have written blogs and given presentations on the subject. The one question that is asked most frequently is “what do I test?”. As this profession is rife with the ubiquitous and inescapable use of acronyms, I too shall add to the growing lexicon with two that help to answer the “what do I test” question - FAST and BEER.

FAST FAST is not actually an answer to the question, but a setup. FAST describes what makes a good test or suite of tests.

Fast – yes, the word associated with the first letter is the same and the acronym itself. Even a unit test project with thousands of tests should take no longer than a few seconds to run.

Atomic – unit tests should test one and only one piece of functionality or behavior. Atomic also means the test is not dependent on outside systems such as a web server or service, the file system, or a database. Those should all be represented by abstractions to support the F in FAST. We should be able to write and test our domain without a UI or data store.

Static – once a test is written, it should not change. This does not mean tests can never be refactored, but if you are practicing TDD, then you are setting up expectations of how the system should behave before writing any code. The implementation of that expectation may and probably will change, but the underlying test for the behavior should remain unchanged.

Thorough – unit tests should test as many conditions as the system under test will face during its lifetime. Testing just a single passing case does not really exercise the code fully or completely. It may not be possible for us to test every edge case, but we can put the code through its paces and ensure its behavior, stability, effectiveness, and graceful failing.

BEER Now on to what needs to be tested. BEER describes a set of cases or circumstances that require reflection and attention when writing tests and designing our APIs. The B in BEER does not stands for behavior - that is just a little too vague to be instructive.

Boundary Conditions – a value that exceeds the maximum or minimum allowed value. If we are testing a validation rule for a string that is required and cannot be more than fifty characters, we need to write a series of tests that address each a series of conditions - a null string, a zero-length string, a string with one character, a string with a length greater than one and less than fifty, a string with fifty characters, and a string with more than fifty characters. The same would apply to numbers or dates. This follows the T in FAST.

Existence – Not Cartesian existence, but nulls and state. We often see this when testing repositories and services. Unit testing persistence requires inserting an object into the data store (represented by an abstractions such as a repository - remember the A in FAST) and retrieving it to make sure it was correctly saved. The opposite, removing an object from the data store, requires a test to assert that it was correctly deleted. This also applies to an object’s state. We want to write tests for state transitions and verify that our objects behave appropriately in each case.

Exceptions – although we may not be able to foresee all possible conditions, we can test for certain exception conditions and ensure they are handled gracefully. Does an object in an invalid state throw an exception? Does the exception give the user an out? Does the exception provide useful information to either the user or other developer for resolving the issue?

Range Conditions – This would include things like a start date occurring before an end date or an invalid enumerator or case in a switch statement. Testing a sorting algorithm where the first element is less than the proceeding one would fall under range conditions.

Conclusion Unit testing is not just about one case or single event. It is about proving our code works in different environments and conditions. Is is also about falsifiability. When we are writing tests, focus on the negative cases and taking a defensive coding posture. What can go wrong and how will my code react? When outside forces assert pressure on the system how will it handle them?

Following these guidelines will help you achieve not just higher test coverage, but higher quality test coverage.