Advanced Database Unit Testing Techniques

1. xUnit Style Database Unit Testing ACCU London – 20th January 2011 Chris Oldwood gort@cix.co.uk

2. Presentation Outline • Database Development Process • The xUnit Testing Model • Test First Development • Continuous Integration/Toolchain • Pub

3. Legacy Database Development • Shared development environment • Only integration/system/stress tests • No automated testing • Only real data not test data • Referential Integrity – all or nothing • No automated build & deployment

4. Ideal Development Process • Isolation • Scaffolding • Automation

5. Example Testable Behaviours • Default constraint • Trigger to cascade a delete • Refactoring to a surrogate key

6. NUnit Test Model [TestFixture] public class ThingTests { [Test] public void Thing_DoesStuff_WhenAskedTo() { var input = ...; var expected = ...; var result = ...; Assert.That(result, Is.EqualTo(expected)); } }

7. NUnit Test Runner • Tests packaged into assemblies • Uses reflection to locate tests • In-memory to minimise residual effects • Output to UI/console

8. SQL Test Model create procedure test.Thing_DoesStuff_WhenAskedTo as declare @input varchar(100) set @input = ... declare @expected varchar(100) set @expected = ... declare @result varchar(100) select @result = ... exec test.AssertEqualString @expected, @result go

9. SQL Test Runner • Tests packaged into scripts (batches) • Uses system tables to locate tests • Uses transactions to minimise residual effects • Output to UI/console

10. SQL Asserts • Value comparisons (string, datetime, …) • Table/result set row count • Table/result set contents • Error handling (constraint violations)

11. Setup & Teardown • Per-Fixture (static data) • Per-Test (specific data) • Use helper procedures

12. Default Constraint Test create procedure test.AddingTask_SetsSubmitTime as declare @taskid int declare @submitTime datetime set @taskid = 1 insert into Task values(@taskid, ...) select @submitTime = t.SubmitTime from Task t where t.TaskId = @taskid exec test.AssertDateTimeNotNull @submitTime go

13. Trigger Test create procedure DeletingUser_DeletesUserSettings as ... set @userid = 1 insert into AppUser values(@userid, ...) insert into AppUserSettings values(@userid, ...) delete from AppUser where UserId = @userid select @rows = count(*) from AppUserSettings where UserId = @userid exec test.AssertRowCountEqual @rows, 0 go

14. Unique Key Test create procedure AddingDuplicateCustomer_RaisesError as ... insert into Customer values(‘duplicate’, ...) begin try insert into Customer values(‘duplicate’, ...) end try begin catch set @threw = 1 end catch exec test.ErrorRaised @threw go

15. Automation • Enables easy regression testing • Enables Continuous Integration • Performance can be variable

16. Test First Development • Start with a requirement • Write a failing test • Write production code • Test via the public interface

17. The Public Interface • Stored procedures • Views • Tables?

18. Implementation Details • Primary keys • Foreign keys • Indexes • Triggers • Check constraints • Default constraints

19. Deployment Testing Build version N then patch to N+1 then run unit tests Build version N+1 then run unit tests ==

20. Buy or Build? • Batch file, SQL scripts & SQLCMD • TSQLUnit & PL/Unit • Visual Studio • SQL Server/Oracle Express

21. “The Oldwood Thing”http://chrisoldwood.blogspot.com Chris Oldwood gort@cix.co.uk