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
                Agenda

•   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
  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.
         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);
       }
   }
}
             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#.
              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?
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
                 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
  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.
              Using TSQLUnit


CREATE PROCEDURE ut_testCapitalize AS
BEGIN
  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'
END
                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
demo

 TSQLUnit
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
demo

 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
    tests.
  – 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
demo

 Data Generation & Deploy
                 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
                 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!’
  http://blogs.blackmarble.co.uk/blogs/rfennell


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