1 / 21

xUnit Style Database Unit Testing

xUnit Style Database Unit Testing. ACCU London – 20 th January 2011 Chris Oldwood gort@cix.co.uk. Presentation Outline. Database Development Process The xUnit Testing Model Test First Development Continuous Integration/Toolchain Pub. Legacy Database Development.

Download Presentation

xUnit Style Database Unit Testing

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related