xUnit Style Database Testing

xUnit Style Database Testing

• 1.
  xUnit Style DatabaseUnit Testing ACCU London – 20th January 2011 Chris Oldwood gort@cix.co.uk
• 2.
  Presentation Outline • DatabaseDevelopment 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] publicclass 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 createprocedure 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 • Valuecomparisons (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 createprocedure 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 procedureDeletingUser_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 createprocedure 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 easyregression 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 • Primarykeys • Foreign keys • Indexes • Triggers • Check constraints • Default constraints
• 19.
  Deployment Testing Build versionN+1 then run unit tests Build version N then patch to 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 ChrisOldwood gort@cix.co.uk