Step 1 – testing that a new sproc exists
Intro
This is the first in a series of blogs on how to take a test driven development (TDD) approach when creating stored procedures using DBTestUnit.
It is an approach that I have been using for a number of years and IMHO enables a more agile approach to database development to be taken.
This series will follow the steps outlined in – Database unit testing patterns.
This is the first in the series – so it will look at testing to ensure that the new stored procedure exists.
The complete series
Part 2 – Testing the properties of a sproc
Part 3 – Testing the data outputted by sproc – initial overview
Testing the data outputted by sproc – detailed
Part 4 – Testing permissions on a sproc
What is TDD? A quick overview
Taken from wikipedia.
“first the developer writes a failing automated test case that defines a desired improvement or new function, then produces code to pass that test and finally refactors the new code to acceptable standards.”
Why test that a SQL object exists?
Over the years I have seen many application releases fail due to mistakes when carrying out database deployments. Many development teams have issues in managing database schemas/versioning and knowing what SQL objects should be in a particular build.
Coupled with versioning and source control – this type of test can help prevent these issues from occurring.
Background scenario
For this blog and the rest of the series Microsoft’s AdventureWorks2008R2 sample database will be used.
The requirement is to create a new sproc on an existing database.
For a given department the new sproc should return the details of current employee in that department. Following the naming conventions of the existing database it will be named – HumanResources.uspGetCurrentEmployeesForDepartment.
The expectation to be met could be written as “A stored procedure named HumanResources.uspGetCurrentEmployeesForDepartment should exist in the database”.
Sample C# test templates and SQL helper scripts that are included with DBTestUnit download will be used.
Writing a test that defines the requirement/expectations
So the first thing to do is to write a ‘failing automated test’ to assert that the store procedure exists.
DBTestUnit includes a sample test:
..\DBTemplate\src\DBTemplateDatabaseTest\Tests\DatabaseTest.cs
that can be used as a template to get started.
A copy of this sample code is shown below:
using System;
using MbUnit.Framework;
using DBTestUnit.UnitTestBaseClass.MSSQL;
using DBTestUnit.Util;
using DBTest = DBTestUnit.InfoSchema;
using AdventureWorks.DatabaseTest;
namespace AdventureWorks.DatabaseTest.Tests
{
[TestFixture]
public class SQLDatabase : SQLDatabaseTestBase
{
public SQLDatabase()
{
dbInstance = "AdventureWorks";
sqlDatabase = new DBTest.SQLDatabase(dbInstance);
tables = new DBTest.Tables(dbInstance);
views = new DBTest.Views(dbInstance);
triggers = new DBTest.Triggers(dbInstance);
sprocs = new DBTest.Sprocs(dbInstance);
functions = new DBTest.Functions(dbInstance);
linkedServers = new DBTest.LinkedServers(dbInstance);
synonyms = new DBTest.Synonyms(dbInstance);
expectedDBAnsiNullsEnabled = "true";
expectedDBAnsiNullSetting = "false";
expectedDBCollation = "Latin1_General_CI_AS";
expectedDBConcatNullsYieldsNulls = "true";
expectedTableCount = 71;
expectedTableList = "Person.Address,Person.AddressType,dbo.AWBuildVersion,Production.BillOfMaterials,Person.BusinessEntity,Person.BusinessEntityAddress,Person.BusinessEntityContact,Person.ContactType,Person.CountryRegion,Sales.CountryRegionCurrency,Sales.CreditCard,Production.Culture,Sales.Currency,Sales.CurrencyRate,Sales.Customer,dbo.DatabaseLog,HumanResources.Department,Production.Document,Person.EmailAddress,HumanResources.Employee,HumanResources.EmployeeDepartmentHistory,HumanResources.EmployeePayHistory,dbo.ErrorLog,Production.Illustration,HumanResources.JobCandidate,Production.Location,Person.Password,Person.Person,Sales.PersonCreditCard,Person.PersonPhone,Person.PhoneNumberType,Production.Product,Production.ProductCategory,Production.ProductCostHistory,Production.ProductDescription,Production.ProductDocument,Production.ProductInventory,Production.ProductListPriceHistory,Production.ProductModel,Production.ProductModelIllustration,Production.ProductModelProductDescriptionCulture,Production.ProductPhoto,Production.ProductProductPhoto,Production.ProductReview,Production.ProductSubcategory,Purchasing.ProductVendor,Purchasing.PurchaseOrderDetail,Purchasing.PurchaseOrderHeader,Sales.SalesOrderDetail,Sales.SalesOrderHeader,Sales.SalesOrderHeaderSalesReason,Sales.SalesPerson,Sales.SalesPersonQuotaHistory,Sales.SalesReason,Sales.SalesTaxRate,Sales.SalesTerritory,Sales.SalesTerritoryHistory,Production.ScrapReason,HumanResources.Shift,Purchasing.ShipMethod,Sales.ShoppingCartItem,Sales.SpecialOffer,Sales.SpecialOfferProduct,Person.StateProvince,Sales.Store,Production.TransactionHistory,Production.TransactionHistoryArchive,Production.UnitMeasure,Purchasing.Vendor,Production.WorkOrder,Production.WorkOrderRouting";
expectedViewCount = 20;
expectedViewList = "Person.vAdditionalContactInfo,HumanResources.vEmployee,HumanResources.vEmployeeDepartment,HumanResources.vEmployeeDepartmentHistory,Sales.vIndividualCustomer,HumanResources.vJobCandidate,HumanResources.vJobCandidateEducation,HumanResources.vJobCandidateEmployment,Sales.vPersonDemographics,Production.vProductAndDescription,Production.vProductModelCatalogDescription,Production.vProductModelInstructions,Sales.vSalesPerson,Sales.vSalesPersonSalesByFiscalYears,Person.vStateProvinceCountryRegion,Sales.vStoreWithAddresses,Sales.vStoreWithContacts,Sales.vStoreWithDemographics,Purchasing.vVendorWithAddresses,Purchasing.vVendorWithContacts";
expectedSprocCount = 10;
expectedSprocList = "dbo.uspGetBillOfMaterials,dbo.uspGetEmployeeManagers,dbo.uspGetManagerEmployees,dbo.uspGetWhereUsedProductID,dbo.uspLogError,dbo.uspPrintError,dbo.uspSearchCandidateResumes,HumanResources.uspUpdateEmployeeHireInfo,HumanResources.uspUpdateEmployeeLogin,HumanResources.uspUpdateEmployeePersonalInfo";
expectedFunctionCount = 11;
expectedFunctionList = "dbo.ufnGetAccountingEndDate,dbo.ufnGetAccountingStartDate,dbo.ufnGetContactInformation,dbo.ufnGetDocumentStatusText,dbo.ufnGetProductDealerPrice,dbo.ufnGetProductListPrice,dbo.ufnGetProductStandardCost,dbo.ufnGetPurchaseOrderStatusText,dbo.ufnGetSalesOrderStatusText,dbo.ufnGetStock,dbo.ufnLeadingZeros";
expectedDMLTriggerList = "dEmployee,dVendor,iduSalesOrderDetail,iPurchaseOrderDetail,iuPerson,iWorkOrder,uPurchaseOrderDetail,uPurchaseOrderHeader,uSalesOrderHeader,uWorkOrder";
expectedDMLTriggerCount = 10;
expectedDDLTriggerList = "ddlDatabaseTriggerLog";
expectedDDLTriggerCount = 1;
expectedTriggerCount = 11;
expectedTriggerList = expectedDMLTriggerList + "," + expectedDDLTriggerList;
expectedLinkedServerCount = 0;
expectedLinkedServerList = "";
expectedSynonymCount = 0;
expectedSynonymList = "";
}
This lists all of the SQL objects – tables, views, sprocs etc – that are expected to be in the AdventureWorks database. When these tests are run – if the database being tested does contain the expected SQL objects – then they will all pass. See a previous blog Do you know what’s in your database? for more details on how to do this.
For testing sprocs there are two key variables:
expectedSprocCount = 10;
expectedSprocList = "dbo.uspGetBillOfMaterials,dbo.uspGetEmployeeManagers,dbo.uspGetManagerEmployees,dbo.uspGetWhereUsedProductID,dbo.uspLogError,dbo.uspPrintError,dbo.uspSearchCandidateResumes,HumanResources.uspUpdateEmployeeHireInfo,HumanResources.uspUpdateEmployeeLogin,HumanResources.uspUpdateEmployeePersonalInfo";
If the sproc count returned from the database being tested is not the same as the value in ‘expectedSprocCount’ ie 10 – the test will fail. A test will also fail if the database does not contain the sprocs named in ‘expectedSprocList’. The two tests combined ensure that the database contains all of the expected sprocs.
So, taking a TDD approach on creating a new sproc:
- The sproc count will need to be increased by 1 ie to 11.
- The new sproc name will need to be added to the list.
The code above should be amended as follows:
expectedSprocCount = 11;
expectedSprocList = "HumanResources.uspGetCurrentEmployeesForDepartment,dbo.uspGetBillOfMaterials,dbo.uspGetEmployeeManagers,dbo.uspGetManagerEmployees,dbo.uspGetWhereUsedProductID,dbo.uspLogError,dbo.uspPrintError,dbo.uspSearchCandidateResumes,HumanResources.uspUpdateEmployeeHireInfo,HumanResources.uspUpdateEmployeeLogin,HumanResources.uspUpdateEmployeePersonalInfo";
If the updated tests are run again – the two tests that check for sprocs using the values set in ‘expectedSprocCount’ and ‘expectedSprocList’ will, as expectedfail – as the new sproc has not been created yet.
Making the tests pass
The next step is to write the implementation script/code to make the tests pass. At this point only enough code is written so that the tests pass (later steps will actually implement further functionality.)
So, a very basic SQL script – similar to that shown below – is created and run against the database.
CREATE PROC HumanResources.uspGetCurrentEmployeesForDepartment AS SET NOCOUNT ON GO
When the tests are run again they will now all pass as the sproc has been created and the database now meets requirements/expectations as specified in the unit tests.
If the new sproc is not deployed into an environment – or if someone deletes/renames it by mistake – then these tests can quickly identify this.
How does this work?
One of the objectives of DBTestUnit is to minimise the amount of boiler plate test code ‘testers’ have to write and to make the tests as declarative as possible – see Overview of what DBTestUnit does and how it work for more details.
In the sample test class – ‘SQLDatabase’ – shown above, as we have seen, the requirements are expressed by setting values in variables eg expectedSprocCount = 11. This test class inherits from a DBTestUnit abstract class named ‘SQLDatabaseTestBase’.
As shown in the following code segment:
[TestFixture]
public class SQLDatabase : SQLDatabaseTestBase
‘SQLDatabaseTestBase’ contains the test methods – the ‘boiler plate’ test code – that actually tests the counts of different types of SQL objects in the database.
When the tests are run – these methods are called, which cause DBTestUnit to run a number of queries against the various INFORMATION_SCHEMA views/internal systems tables of the database being tested*.
For example, to test the sproc count the following SQL query is run**:
SELECT COUNT(ROUTINE_NAME) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_NAME NOT LIKE 'dt_%'
The value returned by this query – the actual sproc count – is then compared to the value set in ‘expectedSprocCount’. If different the test fails.
To check that the actually sproc name exists it runs the following query:
SELECT COUNT(ROUTINE_NAME)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'
AND ROUTINE_NAME NOT LIKE 'dt_%'
AND SPECIFIC_SCHEMA + '.' + ROUTINE_NAME IN ('HumanResources.uspGetCurrentEmployeesForDepartment','dbo.uspGetBillOfMaterials','dbo.uspGetEmployeeManagers','dbo.uspGetManagerEmployees','dbo.uspGetWhereUsedProductID','dbo.uspLogError','dbo.uspPrintError','dbo.uspSearchCandidateResumes','HumanResources.uspUpdateEmployeeHireInfo','HumanResources.uspUpdateEmployeeLogin','HumanResources.uspUpdateEmployeePersonalInfo')
In this case the value set in ‘expectedSprocList’ is used by the query. If the count returned is the same as that in ‘expectedSprocCount’ then the test passes.
A similar type of approach is used for other SQL objects such as tables, views and functions.
Having as much ‘boiler plate’ test code in the DBTestUnit abstract class as possible allows the tester to focus on the database requirements – the WHAT – whilst DBTestUnit manages the HOW – to actually carry out the test.
* DBTestUnit has implements different queries depending on the type of DBMS eg MS SQL, MySQL or Oracle.
** To view the actually queries DBTestUnit implements run SQL profiler against your database as the unit tests are run.
Advantages of this approach
- SQL object existence is explicitly tested. If the SQL object has not be deployed, if it is deleted or renamed – then this type of test will quickly identify this.
- Tests can be reused as deployment tests. The tests are built as part of the initial development process but can be reused to ensure different environments have the correct database schema.
- Tests can act as documentation as they list all of the expected SQL objects.
- Can help save time when running automated tests. These can be run first before running the complete suite of automated database tests. If any of the initial tests fail – ie an expected SQL object is not present – then the rest of the more ‘longer running’ tests do not need to be run until the schema issues are resolved.
Disadvantages of approach
More development time will be required to set these tests up.
What next
This blog has shown how to complete the first step in taking a TDD approach when developing a new stored procedure exists using DBTestUnit.
The next blog in this series will look at the second step – taken from Database unit testing patterns – testing the properties/schema of the new stored procedure.
DBTestUnit 