A Religious Discussion

Have you ever heard a DBA prattle on incessantly about the importance of the database and their apotheotic role in maintaining the space-time continuum? Me too. And while databases (and occasionally DBAs) have their place in software, I would contend it is not at the same level as the code or UI.

So I am throwing down the gauntlet to all of you database gurus. I challenge you to construct a lucid, well thought out, logical argument that disproves any or all of these statements:

• A database is for storing and retrieving data period.
• Business logic does not belong in the database period.
• SQL server is not a software development platform period.

Let the conversation begin.

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