Transcript Unit Testing in SQL

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
Schema
Import
Deploy
Manage
Schema
Run tests
Compare
with
external
DB
Associated
changes
with work
items
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 posttest 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 – [email protected]
WebSite – www.blackmarble.co.uk