Tuesday, August 28, 2012

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.

Wednesday, February 22, 2012

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!

Thursday, January 26, 2012

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:

image

image

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