Unit Testing in SQL

1
Unit Testing in SQL

• Richard FennellEngineering Director
• SqlBits 6th October 2007

2
Agenda

• Unit testing 101
• Why test DBs
• Testing with TSQLUnit
• Testing in DataDude

3
Unit Testing 101

• Unit testing is a procedure used to validate that
  individual units of source code are working
  properly
• Ideally, each test case is independent from the
  others
• Mock objects and test harnesses can be used to
  assist testing a module in isolation.
• Unit testing is typically done by developers and
  not by end-users.

4
Unit Testing 101
namespace bank using NUnit.Framework
TestFixture public class
AccountTest Test
public void TransferFunds()
Account source new Account()
source.Deposit(200.00F)
Account destination new
Account()
destination.Deposit(150.00F)
source.TransferFunds(destination, 100.00F)
Assert.AreEqual(250.00F,
destination.Balance)
Assert.AreEqual(100.00F, source.Balance)

5
Unit Testing 101

• Unit tests should be quick to run and run often.
• Unit testing can be the basis of automated
  testing such as night builds
• They are normally applied to programming
  languages such as Java and C.

6
Why Test DBs?

• Mission-critical business functionality in DB
• Support for evolutionary development
• Current approaches aren't sufficient (you miss
  bugs)
• Why not use a mock object?

7
Where can we test DBs?
8
Common DB Testing Methods

• Combination of PRINT statements and ad-hoc tests
  with the SQL Query Analyzer when developing
  stored procedures.
• T-SQL debugger to inspect the values of
  variables.
• In all cases, human judgment is required to
  analyze the results.
• As test are ad-hoc, the tests can not easily be
  repeated again.

9
Possible Types of Database Unit Test

• Feature Test
• E.g. Testing Stored Procedures
• Schema Tests
• E.g. Returns the columns values you expect
• Security Tests
• E.g. Test who can see what
• Stock-data Tests
• E.g. Check all seed data is present

10
Testing with TSQLUnit
11
TSQLUnit

• A framework to write tests for applications
  written in Transact-SQL
• In the tradition of the "xUnit" framework
  developed by Henrik Ekelund.
• Open Source, licensed under the LGPL license
• http//tsqlunit.sourceforge.net/

12
Using TSQLUnit

• Run the install SQL script on a DB
• Create a stored procedure with a name that starts
  with ut and underscore, such as ut_testSomething
• Code a test, then call tsu_failure if the test
  fails
• Execute tsu_runTests, it runs the test you have
  made and shows the result.

13
Using TSQLUnit
CREATE PROCEDURE ut_testCapitalize AS BEGIN
DECLARE _at_outStr VARCHAR(500) EXEC
capitalize 'a string', _at_outStr OUT IF
(ASCII(LEFT(_at_outStr,1)) ltgt ASCII('A')) OR _at_outStr
IS NULL EXEC tsu_failure 'Capitalize should
make the first
character
uppercase' END
14
Other features

• TestSuites 
• Groups similar tests together
• tsu_runTests 'capitalizeTests'
• Fixtures
• Many tests needs to have quite a lot of prepared
  data as a background.
• To add a fixture create a stored procedure called
  ut_capitalizeTests_setup
• To explicitly clean up you can make a stored
  procedure called ut_capitalizeTests_teardown

15
demo
TSQLUnit
16
Testing in Visual Studio for Database
Professionals DataDude
17
Visual Studio Team SystemApplication Life Cycle
Management (ALM) Solution
18
DataDude support the fullDatabase Lifecycle
19
Testing in Visual Studio 2008

• Firstly remember that can test any CLR code
  before loading it into SQL Server
• DataDude adds database tests that can be used to
  test any stored procedure, function, trigger or
  DB object
• A single test project can contain a variety of
  test types
• Can be used to auto generate test stubs

20
demo
Visual Studio Testing
21
Managing Database State

• How do I guarantee that the data in my database
  is what I expect it to be when I run my tests?
• First, you must ensure that the database has the
  expected state, before you run a collection of
  tests.
• Second, you must ensure that the database has the
  appropriate state between each test in the test
  run.

22
Managing Database State

• Use a data-generation tool to set the database
  state, before you run your collection of unit
  tests
• Restore a database from backup, or attach an
  existing database
• Have your tests assume no state and, as part of
  each pre-test, set up the appropriate state
• Manually cleaning up state changes in each
  post-test script
• Use Transaction Rollback

23
Test Data Generation Options

• Use production data for testing purposes
• Come up with test data from scratch
• Configurable data generators
• Smart default assignment of generators
• Data generation is repeatable
• Enforcement of table ratios

24
demo
Data Generation Deploy
25
Other cool features of DataDude

• Refactoring
• DB /deployment
• Source control

26
Summary

• There is no excuse for NOT testing DB objects
  like any other piece of code.
• Tools exist to help generate and run tests
• Tools exist to help generate and manage test data
  and deployment
• Make use of them to improve your system quality

27
Good Resources

• Microsoft Visual Studio Team System Virtual Labs
• http//msdn2.microsoft.com/en-us/virtuallabs/aa740
  411.aspx
• Cameron Skinner (Product Unit ManagerVisual
  Studio Team Edition for Database)
• http//blogs.msdn.com/camerons
• Roy Osherove (Blog on TFS, Agile and Testing)
• http//weblogs.asp.net/rosherove

28
For Further Information

• My random thoughts But it works on my PC!
• http//blogs.blackmarble.co.uk/blogs/rfennell
• You can also get in touch via
• Email richard_at_blackmarble.co.uk
• WebSite www.blackmarble.co.uk