Do you know what’s in your database?

2010/06/13

Last modified: 2011-02-28

When you are doing a release to production – do you always know what SQL objects should be in your database?

If someone were to ask what tables, sprocs, views etc should be present – could you easily tell them?

Ever had the experience where your dev team are all working off slightly different versions of a database? And when it comes to release time – no one knows for sure what should be the database?

If so, then the following short screencast, might be of interest.

It will show how DBTestUnit can help to avoid some of these issues.

(Screencast was created in Aug 2010)

Summary of how DBTestUnit can help
DBTestUnit can test that the correct SQL objects are present in the schema.

The sample SQLDatabase.cs shows how this can be done.

If an existing object is deleted/renamed or a new one created and the tests are not updated then they will fail.

If this type of test is also included as part of a database versioning strategy then this will effectively document what objects should be in each version of a database.


Using DBTestUnit to test data outputs from SQL objects

2010/06/08

Last modified: 2011-01-08

This blog will look at how DBTestUnit can be used to test the data returned by SQL objects such as tables, views and sprocs.

Why test data outputs from SQL objects?
Testing the functionality/behaviour of SQL objects is one of the key areas of database unit testing.

For many SQL objects the primary behaviour is in returning data.

This type of testing provides two key advantages:

  • Help when refactoring/changing a schema. For example, if you want to refactor a sproc. You will want to maintain the contract it offers to external applications. This type of test can help ensure that whilst, changing the internals of a sproc, it continues to return the expected dataset.
  • Reference data. Most database contains tables that store reference data eg countries, offices, regions, contact types in AdventureWorks.
    This can help ensure that different environments contain the same set of reference data and to identify if any ‘unplanned’ changes have been introduced.

Overview of how it works

  • A tool – ExportDBDataAsXML – is provided as part of the DBTestUnit download.
  • At design time ‘SELECT’ or ‘EXEC’ statements are run against SQL objects with known sets of data in the database.
  • ExportDBDataAsXML outputs these as XML/XSD and the files saved.
  • These are checked to ensure the data is correct. This are the expected data output.
  • Data output tests are written for the appropriate SQL object.
  • When the tests are run data is returned for the SQL object and compared the XML file.
  • DBTestUnit has a method that can compare an XML file to the dataset returned from a SQL object.

What is the ExportDBDataAsXML.exe?
ExportDBDataAsXML – is provided as part of the DBTestUnit download.

It is in the following dir:

..\DBTemplate\tools\ExportDBDataAsXML\

There are 2 key config files:

DBTestUnit.ExportDBDataAsXML.exe.config – contains database connection strings and references the SQLStatement.config file below:

DBTestUnit.ExportDBDataAsXML.SQLStatement.config – contains the SELECT/EXEC statements that are written at design time.
These are used to output the ‘expected’ XML data files. The directory where these should be outputted to can be set here as well.

The image below shows some example statements for SQL objects in the AdventureWorks database.

Note each type of SQL object – table, view, sproc and function has a separate section.

Config for SELECT and EXEC statements

Click on to enlarge - Config for SELECT and EXEC statements

The image below shows the section to config where to output the XML/XSD files to.

Config for dir to output files to

Click on to enlarge - Config for dir to output files to

Using ExportDBDataAsXML.exe to create the output files
Before creating the files and running the tests it is important to ensure that the database is in a ‘known state’.

Typically whilst developing a database I will create a set of data load scripts that allow me to put the database in the a known state.

To test a table eg AdventureWorks.Person.ContactType – write the select statement (for a sproc it would be an exec statement).

Enter this into the ‘Tables’ section of the DBTestUnit.ExportDBDataAsXML.SQLStatement.config

Run the exe it will output an XML file and XSD similar to the images below.

Example output XML file

Click on to enlarge - Example output XML file

Example output XSD file

Click on to enlarge - Example output XSD file

Visually check both files to ensure they are returning the data you expect.

To save time and not output all files for all statements in one go – you can put a ‘No’ in the ‘Execute’ section against and individual SQL statement.

This will stop it running. In this way you can just output the XML for the individual SQL object you are testing.

Writing the tests

An example for AdventureWorks.Person.ContactType table is shown below.

Example table data compare test

Click on to enlarge - Example table data compare test

It is possible to run multiple outputs tests for the same SQL object.

You might do this if you were testing a sproc ie test a number of different input parameters. MBUnit [RowTest] feature can be used to pass in multiple tests parameters.

Each would need to have a separate ‘expected data output’ file created by ExportDBDataAsXML.exe

Once the test has been created – run it. It will query the database and place the data as ‘in memory’ XML.

It will then compare this output to the ‘expected data output’ XML/XSD files.

If they are different – it will fail.

Organising the data output tests
In general there are three types of database tests – schema, functional (data outputs) and permission tests.

To help manage these I tend to separate them into separate test files.

So for example – the SQL table SalesOrderHeader – has three test files:

SalesOrderHeader_Functional.cs
SalesOrderHeader_Persmissions.cs
SalesOrderHeader_Schema.cs

These are also separated into 3 different namespaces:

DBTemplate.DatabaseTest.Tests.Tables.Functional
DBTemplate.DatabaseTest.Tests.Tables.Permissions
DBTemplate.DatabaseTest.Tests.Tables.Schema

As mentioned above this aid test management and allows flexibility as you can then run different categories of tests based on namespace.


Version – 0.3.0.267 – database testing framework released

2010/06/05

A new version that includes support for testing Oracle databases has been released.

It can be downloaded from SourceForge – 0.3.0.267_DBTemplate.zip.