Overview of what DBTestUnit does and how it works


Last modified: 2011-01-08

What it does
1. Test that all expected SQL objects are present
eg the number of tables, views, sprocs, functions, triggers, synonyms etc.

2. Test each SQL object has its expected properties
eg tables have correct columns, defaults, check constraints, unique keys, foreign keys etc.
eg sprocs have expected input/output parameters.

3. Test that SQL objects have expected functional behaviour
eg tables, views and sprocs return their expected data.

4. Test the security/permissions on SQL objects
eg test that the correct users have exec permissions on a sproc.

How it works
When I first started unit testing databases a number of years ago I found that I was writing a lot of boiler plate test code.

For example to connect to the database, to query INFORMATION_SCHEMA/system tables to test the schema, to test the data returned by a table/sproc.

What I wanted to do was to:

  • minimise the amount of boiler plate code required.
  • to make the tests as declarative as possible – writing database tests that focused on asserting my expectations/requirements rather than how to carry out the test.
  • use similar testing tools/frameworks to that of general software development.

Therefore I created a C# library – DBTestUnit.dll – that does this.

The dll abstracts away from the developer of knowing how to carry out the test.

So that, for example, a developer can make simple assertions of what they expect eg that a table A has a number of columns.

The dll has the responsibility of knowing how to test these assertions. For example, by running the appropriate query on INFORMATION_SCHEMA.COLUMNS in MS SQL or DBA_TAB_COLUMNS in Oracle.

A number of examples are shown below that give a brief overview of the types of tests that can be written.

Later blogs will give more detailed examples of how DBTestUnit can be used to carry out the functionality outlined in the first section.

Testing the schema of a SQL table
The sample code below shows part of a C# unit test for the AdventureWorks.Sales.SalesOrderHeader table.

using System;
using MbUnit.Framework;
using DBTestUnit.InfoSchema;
using DBTestUnit.UnitTestBaseClass.MSSQL;

namespace DBTemplate.DatabaseTest.Tests.Tables.Schema
{
    [TestFixture]
    public class SalesOrderHeader : TableTestBase
    {
        public SalesOrderHeader()
        {
            dbInstance = "AdventureWorks";
            schema = "Sales";
            tableName = "SalesOrderHeader";
            table = new Table(dbInstance, schema, tableName);
            expectedColumnCount = 27;
            expectedColumnList = "SalesOrderID,RevisionNumber,OrderDate,DueDate,ShipDate,Status,OnlineOrderFlag,SalesOrderNumber,PurchaseOrderNumber,AccountNumber,CustomerID,ContactID,SalesPersonID,TerritoryID,BillToAddressID,ShipToAddressID,ShipMethodID,CreditCardID,CreditCardApprovalCode,CurrencyRateID,SubTotal,TaxAmt,Freight,TotalDue,Comment,rowguid,ModifiedDate";
            expectedTriggerCount = 1;
            expectedTriggerList = "uSalesOrderHeader";
            expectedPKName = "PK_SalesOrderHeader_SalesOrderID";
            expectedPKColumnCount = 1;
            expectedPKColumnList = "SalesOrderID";
            expectedUniqueIndexCount = 3;
            expectedUniqueIndexList = "AK_SalesOrderHeader_rowguid,AK_SalesOrderHeader_SalesOrderNumber,PK_SalesOrderHeader_SalesOrderID";
            expectedNonUniqueIndexCount = 2;
            expectedNonUniqueIndexList = "IX_SalesOrderHeader_CustomerID,IX_SalesOrderHeader_SalesPersonID";
            expectedClusteredIndexCount = 1;
            expectedClusteredIndexName = "PK_SalesOrderHeader_SalesOrderID";
            expectedNonClusteredIndexCount = 4;
            expectedNonClusteredIndexList = "AK_SalesOrderHeader_rowguid,AK_SalesOrderHeader_SalesOrderNumber,IX_SalesOrderHeader_CustomerID,IX_SalesOrderHeader_SalesPersonID";
            expectedDefaultColumnCount = 9;
        }

In the code above the expected properties of a table are set declaratively in variables.

For example, expectedColumnCount = 27 asserts that the table should have 27 columns.

This combined with the variable expectedColumnList can test that the table has the correct columns.

When the test is run, DBTestUnit reads these ‘expectations’ and tests that the database schema is as expected.

If a column is added/deleted/renamed without updating this tests – then it will fail when run.

A similar approach can be taken for other SQL objects – views, sprocs, functions etc.

If you have an existing database – a SQL script is provided that can help to automatically create these tests . to give you a ‘baseline’ starting point.

The following screencast gives a quick overview on how to test SQL tables.

Testing the data returned by a SQL table
A key part of database unit testing is ensuring that SQL objects return the expected data.

For example, when selecting data from a table or view, or executing a sproc.

The sample code below shows part of a C# unit test for testing that the table – AdventureWorks.Sales.SalesOrderHeader – does this.

using System;
using MbUnit.Framework;
using DBTestUnit.Util;

namespace DBTemplate.DatabaseTest.Tests.Tables.Functional
{
    [TestFixture]
    public class SalesOrderHeader
    {
        string dbInstance = "AdventureWorks";
        string tableTestFileDir = AppSettings.DirTableExpectedResults();

        public SalesOrderHeader() { }

        [RowTest]
        [Row("testData_SalesOrderHeader", "SELECT SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, Status, OnlineOrderFlag, SalesOrderNumber, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, TotalDue, Comment, rowguid, ModifiedDate FROM Sales.SalesOrderHeader WHERE SalesOrderID BETWEEN 43659 and 43678 ORDER BY SalesOrderID")]
        public void T01_CheckRowsFromSelect(string fileName, string sqlText)
        {
            bool areTheSame = DataSetComparer.Compare(tableTestFileDir, fileName, dbInstance, sqlText);
            Assert.IsTrue(areTheSame, "The dataset returned from the database is not the same as that from the XML file.");
        }
    }
}

How does this work?

At design time ‘SELECT’ or ‘EXEC’ statements are run against a known set of test data.

The tool – ExportDBDataAsXML – is used to output this data and it is stored as XML/XSD files.

Tests – as above – are then written that compare the actual output of the SQL object to the ‘expected’ XML file.

If they differ – then the test will fail.

So how does this work for the code above?

DBTestUnit has a method DataSetComparer.Compare – that can compare and xml file to the output of database (select or exec statement).

bool areTheSame = DataSetComparer.Compare(tableTestFileDir, fileName, dbInstance, sqlText);

The ‘expected’ XML data created at design time is stored in a file ‘testData_SalesOrderHeader’ – see the variable fileName above.

This is compared to actual data return from the SQL table by running the select statement – see the variable sqlText above.

DBTestUnit compares them and returns true if they are the same:

If the data being returned from the table changes for any reason – eg a data type of a column is changed or the actual row values changes – then the test will fail.

See Testing data outputs from SQL objects for more detail on how DBTestUnit can be used to test this type of functionality.

Round up
This blog has given a very quick overview of some of the features of DBTestUnit and how it can be used to carry out database unit testing.

Other blog will give more detail on how to set up tests for different types of SQL objects.

If you are interested in using DBTestUnit and have any questions then feel free to contact me.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: