xunit style database unit testing
Post on 31-Dec-2015
24 Views
Preview:
DESCRIPTION
TRANSCRIPT
xUnit Style Database Unit Testing
ACCU London – 20th 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
• 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
Ideal Development Process
• Isolation
• Scaffolding
• Automation
Example Testable Behaviours
• Default constraint
• Trigger to cascade a delete
• Refactoring to a surrogate key
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)); }}
NUnit Test Runner
• Tests packaged into assemblies
• Uses reflection to locate tests
• In-memory to minimise residual effects
• Output to UI/console
SQL Test Modelcreate procedure test.Thing_DoesStuff_WhenAskedToas declare @input varchar(100) set @input = ...
declare @expected varchar(100) set @expected = ...
declare @result varchar(100) select @result = ...
exec test.AssertEqualString @expected, @result go
SQL Test Runner
• Tests packaged into scripts (batches)
• Uses system tables to locate tests
• Uses transactions to minimise residual effects
• Output to UI/console
SQL Asserts
• Value comparisons (string, datetime, …)
• Table/result set row count
• Table/result set contents
• Error handling (constraint violations)
Setup & Teardown
• Per-Fixture (static data)
• Per-Test (specific data)
• Use helper procedures
Default Constraint Test
create procedure test.AddingTask_SetsSubmitTimeas 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
Trigger Test
create procedure DeletingUser_DeletesUserSettingsas ... 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
Unique Key Test
create procedure AddingDuplicateCustomer_RaisesErroras ... insert into Customer values(‘duplicate’, ...)
begin try insert into Customer values(‘duplicate’, ...) end try begin catch set @threw = 1 end catch
exec test.ErrorRaised @threwgo
Automation
• Enables easy regression testing
• Enables Continuous Integration
• Performance can be variable
Test First Development
• Start with a requirement
• Write a failing test
• Write production code
• Test via the public interface
The Public Interface
• Stored procedures
• Views
• Tables?
Implementation Details
• Primary keys
• Foreign keys
• Indexes
• Triggers
• Check constraints
• Default constraints
Deployment Testing
Build version N+1then run unit tests
Build version Nthen patch to N+1then run unit tests
==
Buy or Build?
• Batch file, SQL scripts & SQLCMD
• TSQLUnit & PL/Unit
• Visual Studio
• SQL Server/Oracle Express
“The Oldwood Thing”http://chrisoldwood.blogspot.com
Chris Oldwood
gort@cix.co.uk
top related