Unit Testing in SQL

Document Sample
Unit Testing in SQL Powered By Docstoc
					                 Unit Testing in SQL

Richard Fennell
Engineering Director

SqlBits 6th October 2007

•   Unit testing 101
•   Why test DBs
•   Testing with TSQLUnit
•   Testing in DataDude
             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
• 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.
         Unit Testing 101
namespace bank
    using NUnit.Framework;
    public class AccountTest
       public void TransferFunds()
           Account source = new Account();
           Account destination = new Account();
           source.TransferFunds(destination, 100.00F);
           Assert.AreEqual(250.00F, destination.Balance);
           Assert.AreEqual(100.00F, source.Balance);
             Unit Testing 101
• Unit tests should be quick to run and run
• 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#.
              Why Test DBs?
• Mission-critical business functionality in DB
• Support for evolutionary development
• Current approaches aren't sufficient (you miss
• Why not use a mock object?
Where can we test DBs?
    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.
 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
Testing with 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/
              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
• Code a test, then call tsu_failure if the test
• Execute tsu_runTests, it runs the test you have
  made and shows the result.
              Using TSQLUnit

CREATE PROCEDURE ut_testCapitalize AS
  DECLARE @outStr VARCHAR(500)
  EXEC capitalize 'a string', @outStr OUT
  IF (ASCII(LEFT(@outStr,1)) <> ASCII('A')) OR @outStr IS NULL
         EXEC tsu_failure 'Capitalize should make the first
                                        character uppercase'
                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
  – To explicitly clean up you can make a stored procedure
    called ut_capitalizeTests_teardown

Testing in Visual Studio for Database
      Professionals ‘DataDude’
     Visual Studio Team System
Application Life Cycle Management (ALM) Solution
DataDude support the full
   Database Lifecycle
     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

 Visual Studio Testing
      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
  – Second, you must ensure that the database has
    the appropriate state between each test in the
    test run.
       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
    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

 Data Generation & Deploy
• 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
                 Good Resources
• Microsoft Visual Studio Team System Virtual Labs
  – http://msdn2.microsoft.com/en-us/virtuallabs/aa740411.aspx

• Cameron Skinner (Product Unit Manager
  Visual Studio Team Edition for Database)
  – http://blogs.msdn.com/camerons
• Roy Osherove (Blog on TFS, Agile and Testing)
  – http://weblogs.asp.net/rosherove
          For Further Information
• My random thoughts ‘But it works on my PC!’

• You can also get in touch via:
   Email – richard@blackmarble.co.uk
   WebSite – www.blackmarble.co.uk