This is a post for a great idea called SQL University started by Jorge Segarra also famously known as SqlChicken on Twitter. It’s a collection of blog posts on different database related topics contributed by several smart people all over the world. So this week is mine and we’ll be talking about database testing and refactoring. In 3 posts we’ll cover:

SQLU part 1 - What and why of database testing

SQLU part 2 - What and why of database refactoring

SQLU part 3 - Database testing and refactoring tools and examples

This is the third and last part of the series and in it we’ll take a look at tools we can test and refactor with plus some an example of the both.

Tools of the trade

First a few thoughts about how to go about testing a database. I'm firmily against any testing tools that go into the database itself or need an extra database. Unit tests for the database and applications using the database should all be in one place using the same technology. By using database specific frameworks we fragment our tests into many places and increase test system complexity. Let’s take a look at some testing tools.

1. NUnit, xUnit, MbUnit

All three are .Net testing frameworks meant to unit test .Net application. But we can test databases with them just fine. I use NUnit because I’ve always used it for work and personal projects. One day this might change. So the thing to remember is to be flexible if something better comes along. All three are quite similar and you should be able to switch between them without much problem.

2. TSQLUnit

As much as this framework is helpful for the non-C# savvy folks I don’t like it for the reason I stated above. It lives in the database and thus fragments the testing infrastructure. Also it appears that it’s not being actively developed anymore.

3. DbFit

I haven’t had the pleasure of trying this tool just yet but it’s on my to-do list. From what I’ve read and heard Gojko Adzic (@gojkoadzic on Twitter) has done a remarkable job with it.

4. Redgate SQL Refactor and Apex SQL Refactor

Neither of these refactoring tools are free, however if you have hardcore refactoring planned they are worth while looking into. I’ve only used the Red Gate’s Refactor and was quite impressed with it.

5. Reverting the database state

I’ve talked before about ways to revert a database to pre-test state after unit testing. This still holds and I haven’t changed my mind. Also make sure to read the comments as they are quite informative. I especially like the idea of setting up and tearing down the schema for each test group with NHibernate.

Testing and refactoring example

We’ll take a look at the simple schema and data test for a view and refactoring the SELECT * in that view. We’ll use a single table PhoneNumbers with ID and Phone columns. Then we’ll refactor the Phone column into 3 columns Prefix, Number and Suffix. Lastly we’ll remove the original Phone column. Then we’ll check how the view behaves with tests in NUnit. The comments in code explain the problem so be sure to read them. I’m assuming you know NUnit and C#.

USE tempdb
GO
CREATE TABLE PhoneNumbers
(
    ID INT IDENTITY(1,1),
    Phone VARCHAR(20)
)
GO
INSERT INTO PhoneNumbers(Phone)
SELECT '111 222333 444' UNION ALL
SELECT '555 666777 888'
GO
-- notice we don't have WITH SCHEMABINDING
CREATE VIEW vPhoneNumbers
AS
    SELECT  *
    FROM    PhoneNumbers
GO
-- Let's take a look at what the view returns

-- If we add a new columns and rows both tests will fail
SELECT  *
FROM    vPhoneNumbers 
GO

-- DoesViewReturnCorrectColumns test will SUCCEED

-- DoesViewReturnCorrectData test will    SUCCEED

-- refactor to split Phone column into 3 parts
ALTER TABLE PhoneNumbers ADD Prefix VARCHAR(3)
ALTER TABLE PhoneNumbers ADD Number VARCHAR(6)
ALTER TABLE PhoneNumbers ADD Suffix VARCHAR(3)
GO
-- update the new columns
UPDATE  PhoneNumbers 
SET     Prefix = LEFT(Phone, 3),
        Number = SUBSTRING(Phone, 5, 6),
        Suffix = RIGHT(Phone, 3)
GO
-- remove the old column
ALTER TABLE PhoneNumbers DROP COLUMN Phone
GO
-- This returns unexpected results!
-- it returns 2 columns ID and Phone even though 
-- we don't have a Phone column anymore.
-- Notice that the data is from the Prefix column
-- This is a danger of SELECT *
SELECT  *
FROM    vPhoneNumbers

-- DoesViewReturnCorrectColumns test will SUCCEED

-- DoesViewReturnCorrectData test will    FAIL

-- for a fix we have to call sp_refreshview 
-- to refresh the view definition
EXEC sp_refreshview 'vPhoneNumbers'
-- after the refresh the view returns 4 columns
-- this breaks the input/output behavior of the database
-- which refactoring MUST NOT do
SELECT  *
FROM    vPhoneNumbers

-- DoesViewReturnCorrectColumns test will FAIL

-- DoesViewReturnCorrectData test will    FAIL

-- to fix the input/output behavior change problem 
-- we have to concat the 3 columns into one named Phone
ALTER VIEW vPhoneNumbers
AS
SELECT  ID, Prefix + ' ' + Number + ' ' + Suffix AS Phone
FROM    PhoneNumbers
GO
-- now it works as expected
SELECT  *
FROM    vPhoneNumbers

-- DoesViewReturnCorrectColumns test will SUCCEED

-- DoesViewReturnCorrectData test will    SUCCEED

-- clean up
DROP VIEW vPhoneNumbers
DROP TABLE PhoneNumbers

[Test]
public void DoesViewReturnCoorectColumns()
{            
    // conn is a valid SqlConnection to the server's tempdb
    // note the SET FMTONLY ON with which we return only schema and no data
    using (SqlCommand cmd = new SqlCommand("SET FMTONLY ON; 

                                            SELECT * FROM vPhoneNumbers", conn))
    {                 
        DataTable dt = new DataTable();
        dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
        // test returned schema: number of columns, column names and data types
        Assert.AreEqual(dt.Columns.Count, 2);

        Assert.AreEqual(dt.Columns[0].Caption, "ID");
        Assert.AreEqual(dt.Columns[0].DataType, typeof(int));

        Assert.AreEqual(dt.Columns[1].Caption, "Phone");                
        Assert.AreEqual(dt.Columns[1].DataType, typeof(string));
    }
}

[Test]
public void DoesViewReturnCorrectData()
{
    // conn is a valid SqlConnection to the server's tempdb
    using (SqlCommand cmd = new SqlCommand("SELECT * FROM vPhoneNumbers", conn))
    {
        DataTable dt = new DataTable();
        dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
        // test returned data: number of rows and their values
        Assert.AreEqual(dt.Rows.Count, 2);
        
        Assert.AreEqual(dt.Rows[0]["ID"], 1);
        Assert.AreEqual(dt.Rows[0]["Phone"], "111 222333 444");

        Assert.AreEqual(dt.Rows[1]["ID"], 2);
        Assert.AreEqual(dt.Rows[1]["Phone"], "555 666777 888");
    }
}

With this simple example we’ve seen how a very simple schema can cause a lot of problems in the whole application/database system if it doesn’t have tests. Imagine what would happen if some outside process would depend on that view. It would get wrong data and propagate it silently throughout the system. And that is not good. So have tests at least for the crucial parts of your systems.

And with that we conclude the Database Testing and Refactoring week at SQL University. Hope you learned something new and enjoy the learning weeks to come. Have fun!