I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL University: Database testing and refactoring tools and examples

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#.

T-SQL Code C# test code
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
image
-- 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
image 
-- 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
image 
-- 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
image
-- DoesViewReturnCorrectColumns test will SUCCEED
-- DoesViewReturnCorrectData test will    SUCCEED

-- clean up
DROP VIEW vPhoneNumbers
DROP TABLE PhoneNumbers
[Test]
public void DoesViewReturnCorrectColumns()
{
// 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!

Print | posted on Friday, June 04, 2010 2:53 PM | Filed Under [ SQL Server Back to Basics ]

Feedback

Gravatar

# re: SQL University: Database testing and refactoring tools and examples

Mladen, a set of really interesting blogs. The post on why we need to test databases especially resonated.

I agree, there is little emphasis around testing in general and even less for databases. Even where dev teams have fully embraced test driven development - database development is very rarely included.

I have created a database testing framework that might be of interest. It provides an API, templates and helper scripts that tries to remove some of the complexity around database testing. MBunit is used by default - but NUnit could be used.

More details can be found at http://michaelbaylon.wordpress.com/database-testing-framework/
6/4/2010 4:42 PM | Michael Baylon
Gravatar

# re: SQL University: Database testing and refactoring tools and examples

Great job Mladen! Everyone, if you read this series of blogs and found it useful please leave Mladen and SQL University feedback at: http://bit.ly/SQLUfb
6/5/2010 6:33 PM | Jorge Segarra
Gravatar

# re: SQL University: Database testing and refactoring tools and examples

I am fresher to software world and i am testing team my team lead asked me about data base testing?
My questions about this are what is the need database testing & how we test database?
Can u provide any material or test book or sites to learn more about this topic
8/30/2010 2:39 PM | kamal
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET