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 CREATEPROCEDUREut_testCapitalizeAS BEGIN DECLARE @outStrVARCHAR(500) EXEC capitalize 'a string', @outStr OUT IF (ASCII(LEFT(@outStr,1)) <> ASCII('A')) OR @outStrISNULL EXECtsu_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. 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

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

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