Running your first tests

2009/11/18

Last modified: 2011-02-28

This blog is the second in a two part series.

It will look at:

  • running the some simple tests against the database
  • using the provided SQL helper scripts to help get the properties for an existing database.

It assumes that you have read the first blog in the series – initial set up and configuration.

Example test to be run

The DBTestUnit download comes with a number of sample test files including:

DIR\src\AdventureWorksDatabaseTest\SQLServer.cs

(Note ‘DIR’ is used as a token for ‘C:\Projects\AdventureWorks\’ – the example project set up in initial set up and configuration).

This allows tests to be run against the SQL server that hosts the database – to ensure it has the expected properties.

An example is shown below:

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

namespace DBTemplate.DatabaseTest.Tests
{
    [TestFixture]
    public class SQLServer : SQLServerTestBase
    {
        public SQLServer()
        {
            dbInstance = "AdventureWorks";
            sqlServer = new  DBTest.SQLServer(dbInstance);
            expectedServerCollation = "Latin1_General_CI_AS";
            expectedServerVersion = "10.50.1600.1";
            expectedServerProductLevel = "RTM";
            expectedServerEdition = "Developer Edition";
        }
    }
}

A number of things are worth noting:

  • This is for MS SQL server. The test inherits from SQLServerTestBase part of the DBTestUnit.UnitTestBaseClass.MSSQL namespace. This would need to be changed if testing Oracle or MySQL.
  • The expected values eg expectedServerVersion – will need to be changed appropriately.

This will be used as an example as it is a relatively straight forward set of tests.

How to run the tests
So assuming that everything has been set up and configured correctly how do we run the tests?

1. Open up the VS solution.

If you haven’t already done so open the solution and build/compile the database test project.

The output of this will be a test dll – for example:

DIR\src\AdventureWorksDatabaseTest\bin\Debug
\AdventureWorks.DatabaseTest.dll

2. Open the MBUnit UI

If you need to install either get the latest version from the MBUnit web site or install if from the DBTestUnit download

DIR\tools\mbunit2.41.232.0

In the UI go to the menu ‘Assemblies’ – ‘Add assemblies’

Navigate to and attach the db test dll created in step 1.

Run the SQLServer tests.

If the expected properties in the tests are correct then the tests will pass.

3. What do you do if some of the test fail
This will mean that the SQL server that hosts the AdventureWorks – does not have the properties outlined in test code above.

Therefore the sample test code provided needs to be updated with the correct SQL server properties.

(Note this blog is using the scenario where you are starting to carry out database testing for an existing/legacy database.)

The DBTestUnit download provides a number of SQL helper files that will provide information for an existing database.

These can be found in:

DIR\misc\AdventureWorksDatabase\SQL\DatabaseTestingHelperScripts\

4. Using the SQL helper scripts

The script 001_SQLServerSettings.sql is used in this case.

A part of the script is shown below:

PRINT '********************************'
PRINT '********************************'
PRINT 'SERVER PROPERTIES'
PRINT 'ProductVersion: ' + convert(varchar,SERVERPROPERTY ('ProductVersion'))
PRINT 'ProductLevel: ' + convert(varchar,SERVERPROPERTY ('ProductLevel'))
PRINT 'Edition: ' + convert(varchar,SERVERPROPERTY ('Edition'))
PRINT 'ServerCollation: ' + convert(varchar,SERVERPROPERTY ('Collation'))

The values returned when this script is run can be placed in the C# test shown above.

It can then be recompiled and the tests run again in MBUnit.

If the values are correct the tests will all pass.

Round up
And that’s it.

The first blog showed how to configure/set up DBTestUnit, this blog has taking this on and shown how to run some initial tests.

Advertisements

Initial set up and configuration of DBTestUnit

2009/11/17

Last modified: 2011-06-08

Overview
This is the first in a two part series on how to set up the Visual Studio (VS) template – that is included in the DBTestUnit download.

The second will show how to run tests.

If you already have an existing solution/project and want to start using DBTestUnit – a section is included at the end to cover this scenario.

Screencast
The following short screencast can be used with the notes below to help set up and configure.

(Screencast was created in Aug 2010)

Steps
1. Download the latest version of the DBTestUnit

2. Extract files to where you want to place your database testing solution/project.

In the example below it has been extracted to C:\Projects\ :

DBTemplate default directory structure

DBTemplate default directory structure

3. Rename dirs and files with your database name.

As you can see in the image above by default ‘DBTemplate’ is used as a prefix for many dirs and files.

These should be replaced with the name of the database to be tested.

For this I am going to use the MS sample database AdventureWorks as an example.

Therefore change:

C:\Projects\DBTemplate\ to
C:\Projects\AdventureWorks\

Rather than renaming these manually, a bat file – C:\Project\DBTemplate\tools\DBTemplateSetUp.bat – is provided that can do this.

The first section of this file is shown below.

SET dirRoot=C:\Projects\
SET dirParent=%dirRoot%DBTemplate\
SET dbProjectName=AdventureWorks

Before running the bat file – copy it to another directory eg C:\Projects\ and set appropriate values for ‘dirRoot’ and ‘dbProjectName’.

After renaming the directory structure should be similar to the following:

AdventureWorks directory structure

AdventureWorks directory structure

4. Change the VS solution file

Open – C:\Project\AdventureWorks\src\AdventureWorks.sln

Replace ‘DBTemplate’ with ‘AdventureWorks’

5. Change the the db test project file

Open – C:\Projects\AdventureWorks\src\AdventureWorksDatabaseTest\
AdventureWorks.DatabaseTest.csproj

Replace ‘DBTemplate’ with ‘AdventureWorks’

6. Start VS

Open the solution.

The structure should look similar to the following:

AdventureWorks Visual Studio directory structure

AdventureWorks Visual Studio directory structure

A number of sample unit tests and other files are including the in solution. These will all have the text ‘DBTemplate’ in their namespaces.

Therefore, do a solution wide ‘find and replace’ changing ‘DBTemplate’ to ‘AdventureWorks’

7. Set up the config file to connect to the database to test

If you open up any of the sample tests provided you will see that they all have a variable – dbInstance – which by default has the value ‘AdventureWorks’.

This can be seen at the bottom of the sample code provided below:

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";

This is used to provide connection settings to the database to be tested and should be set in the following app config file:

..\src\AdventureWorksDatabaseTest\bin\Debug\
AdventureWorks.DatabaseTest.dll.config

Open this config file.

Go to the connectionStrings section.

The following should be included by default.

<connectionStrings>
<!--MS SQL dbInstances-->
<add name="AdventureWorks" connectionString="Data Source=serverName;Initial Catalog=AdventureWorks;Integrated Security=True;Application Name=AdventureWorksUnitTesting" 
			 providerName="System.Data.SqlClient"/>

The ‘name=”AdventureWorks”‘ should correlate with the value set in the variable ‘dbInstance’ as show in the test code above.

The connectionString properties need to be changed appropriately eg Data Source=serverName

Once the config file has been updated – that’s it all set up – ready to start database testing.

What do I do if I have an existing project?
If you an existing project and just want to start using the DBTestUnit.dll for testing then it is a bit easier to set up.

Download the latest version of the DBTestUnit as per step 1.

In your existing test project reference the latest version of DBTestUnit.dll. You will also need to reference the MS enterprise libraries provided in the download.

Then amend your existing config file appropriately so that tests can connect to the database.

Round up
This blog shown how to set up DBTestUnit – the second will look at running tests.


Prereqs – what you need to use DBTestUnit

2009/11/14

Last modified: 2011-02-28

See related blog – What’s included in the download

To start using DBTestUnit – you just need to reference DBTestUnit.dll and then start using it in your database tests.

The purpose of the table below is to outline what is required for other ‘components’ of the DBTestUnit download.

Supported SQL dbms See supported feature set
SQL helper file Predominantly for MS SQL2K5/8 – though there are some for Oracle.
Solution/project template Built using Visual Studio 2K10 express edition. The project solution template should work with other versions of VS.

Unit test samples are in C#.

Unit testing framework MBUnit 2.41

This and other required external libraries are included in the DBTemplate download.

Other unit testing frameworks – eg NUnit – could be used.


Overview of what DBTestUnit does and how it works

2009/11/14

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.


What’s included in the DBTestUnit download

2009/11/14

Last modified: 2011-02-28

The DBTestUnit download includes:

  • DBTestUnit.dll. This library is referenced by unit tests and is the API used to test databases.
    It’s primary objective being to abstract away as much of the boiler plate code associated with database testing as possible.
  • ExportDBDataAsXML. An exe that allows data – based on queries/execs set in a config file – to be exported from a db as XML. This is used when testing the data returned from SQL objects.
  • Visual studio solution and database testing project template. To help get developers get started in setting up new database testing projects.
  • A number of samples C# tests – for testing tables, views, sprocs and functions.
  • SQL helper files – gets properties of a database and can be used to help auto generate C# db unit tests for existing/legacy databases.
  • MBUnit – the default unit test framework used – though others such as NUnit could be used.
  • All other support files, libraries, project directory structure.