Database unit testing patterns

2011/02/27

Each time I create a new SQL object, whether it’s a new table, view, sprocs etc, I find that I follow a similar pattern.

It can be outlined as follows:

1. Write a test to ensure the object exists.

2. Write a test to check the SQL objects properties eg columns in a table, parameters in a sproc.

3. Write a test to check the functionality of the object eg a view returns the expected data.

4. Write a test to check the expected permissions on the object.

After each ‘test step’ – code/script is written so that the tests/requirements are met. The steps have been written in a linear fashion – but usually there is a large degree of iteration between them – especially 2 + 3.

I find that thinking about these 4 steps acts as a really good ‘aide memoire’ and helps ensure a more systematic approach is taken when carrying out database unit testing.

Over the next few weeks I am going to write a series of blogs on how to take a TDD approach to database development – and I will refer back to the steps in this database unit testing pattern.

What do you think?

Do you follow a similar approach to database unit testing or do you do something different?

Advertisements

Version – 0.4.0.428 – DBTestUnit released

2011/02/25

Version 0.4.0.428 of DBTestUnit has been released and can be downloaded from SourceForge.

This release implements the name change from ‘Database testing framework’ to ‘DBTestUnit’.

So what has changed?

There has been no change in overall functionality.

Basically, a number of components and namespaces have been changed to reflect the new name.

These include:

  • DatabaseTesting.dll renamed to DBTestUnit.dll.
    The test dll is now found in: …\Projects\DBTemplate\libs\DBTestUnit\
  • DatabaseTesting.ExportDBDataAsXML.exe renamed to DBTestUnit.ExportDBDataAsXML.exe.
    The exe is found in: …\DBTemplate\tools\ExportDBDataAsXML\
  • All sample tests have been updated to reference DBTestUnit.dll rather than DatabaseTesting.dll
  • All namespaces have been updated
  • eg

    using DatabaseTesting.UnitTestBaseClass.MSSQL;
    

    to d

    using DBTestUnit.UnitTestBaseClass.MSSQL;
    

    How does this effect using the framework?

    If you are a new user – none. Just download and start using.

    If you are using a previous version – there are a number of relatively minor steps that you will need to carry out if you want to start using the new ‘renamed’ version.

    1. Download the latest version – eg 0.4.0.428_DBTestUnit.zip

    2. In your database testing solution remove all references to the ‘old’ DatabaseTest.dll.

    3. Add a reference to the new test dll – DBTestUnit.dll – found in ….\Projects\DBTemplate\libs\DBTestUnit\.

    4. Update any existing namespaces to reflect the new name ie do a ‘find and replace’ changing ‘DatabaseTest’ to ‘DBTestUnit’.

    eg

    using DatabaseTesting.InfoSchema;
    using DatabaseTesting.UnitTestBaseClass.MSSQL;
    

    to

    using DBTestUnit.InfoSchema;
    using DBTestUnit.UnitTestBaseClass.MSSQL;
    

    5. Next you will need to change the test dll config file.

    In the sample project provided – which uses AdventureWorks database as an example – then the change would be applied to following config file:

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

    The following change would be made to reflect the changes in the internal namespaces of the testing framework.

    <!--************************************-->
    <add key="AssemblyName" value="DatabaseTesting"></add>
    <add key="DaoFactoryNamespace" value="DatabaseTesting.InfoSchema.DataAccess.MSSQL"></add>
    

    to

    <!--************************************-->
    <add key="AssemblyName" value="DBTestUnit"></add>
    <add key="DaoFactoryNamespace" value="DBTestUnit.InfoSchema.DataAccess.MSSQL"></add>
    

    6. The final part is if you use the XML export tool found in:

    …\DBTemplate\tools\ExportDBDataAsXML\

    For this, it is probably easier to just take a copy of the latest version of this from the download.

    Make sure that you take a back up of your existing config files as you will need to incorporate them into the ‘vanilla’ config files from the new download.

    And that’s it.

    If you have any problems ‘upgrading’ feel free to contact me.


Database testing framework renamed to DBTestUnit

2011/02/24

As of Feb 2011 – ‘Database testing framework’ has been renamed to ‘DBTestUnit’.

All existing database testing content has been copied from – http://michaelbaylon.wordpress.com – to this site.

Most of this content has been updated to reflect the name change – from ‘Database testing framework’ to DBTestUnit – but apologies in advance where I have missed some.

The database testing framework – DBTestUnit can be downloaded via this new link.