x Unit Style Database Unit Testing ACCU London

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

Presentation Outline • • • Database Development Process The x. Unit 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 [Test. Fixture] public class Thing. Tests { [Test] public void Thing_Does. Stuff_When. Asked. To() { var input =. . . ; var expected =. . . ; var result =. . . ; Assert. That(result, Is. Equal. To(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_Does. Stuff_When. Asked. To as declare @input varchar(100) set @input =. . . declare @expected varchar(100) set @expected =. . . declare @result varchar(100) select @result =. . . exec test. Assert. Equal. String @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. Adding. Task_Sets. Submit. Time as declare @taskid int declare @submit. Time datetime set @taskid = 1 insert into Task values(@taskid, . . . ) select from where @submit. Time = t. Submit. Time Task t t. Task. Id = @taskid exec test. Assert. Date. Time. Not. Null @submit. Time go

Trigger Test create procedure Deleting. User_Deletes. User. Settings as. . . set @userid = 1 insert into App. User values(@userid, . . . ) insert into App. User. Settings values(@userid, . . . ) delete from App. User where User. Id = @userid select @rows = count(*) from App. User. Settings where User. Id = @userid exec test. Assert. Row. Count. Equal @rows, 0 go

Unique Key Test create procedure Adding. Duplicate. Customer_Raises. Error as. . . insert into Customer values(‘duplicate’, . . . ) begin try insert into Customer values(‘duplicate’, . . . ) end try begin catch set @threw = 1 end catch exec test. Error. Raised @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 gort@cix. co. uk