Transcript xUnit Style Database Unit Testing

xUnit Style Database Unit Testing
ACCU London – 20th January 2011
Chris Oldwood
[email protected]
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 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
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_SetsSubmitTime
as
declare @taskid
int
declare @submitTime datetime
set @taskid = 1
insert into Task values(@taskid, ...)
select
from
where
@submitTime = t.SubmitTime
Task t
t.TaskId = @taskid
exec test.AssertDateTimeNotNull @submitTime
go
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
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
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+1
then run unit tests
==
Build version N
then patch to N+1
then 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
[email protected]