Version – 0.4.0.428 – DBTestUnit released

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

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.

Version – 0.3.3.410 – database testing framework released

Version 0.3.3.410 of the database testing framework has been released and can be downloaded from SourceForge

You can start using the new version by replacing the old version of DatabaseTest.dll with the one.

Extended functionality now makes it easier to test explicit permissions set on SQL objects

This is enabled by a new test class in the framework – DatabaseTesting.InfoSchema.SQLObjectPermissions – and some abstract classes.

These enable writing tests to be declarative as possible and minimise the amount of ‘boiler plate’ test code that needs to be written.

Two examples on how to use this functionality are included below.

These are more detailed examples are included in the latest version of the framework

Unit testing explicit permissions on a sproc

Both examples use SQL objects from the AdventureWorks database.

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

namespace DBTemplate.DatabaseTest.Tests.Sprocs.Permissions
{
    [TestFixture]
    public class uspGetManagerEmployees : SprocPermissionsTestBase
    {
        public uspGetManagerEmployees()
        {
            dbInstance = "AdventureWorks";
            sprocName = "dbo.uspGetManagerEmployees";
            sprocPermissions = new SQLObjectPermissions(dbInstance, sprocName);
            expectedTotalPermCount = 2;
            expectedExecPermCount = 2;
            expectedExecUserList = "User01,User02";
        }

The sample code above shows a permission test for a sproc – dbo.uspGetManagerEmployees. These tests assert that we expect the sproc to have a total of 2 permissions set on it (expectedTotalPermCount = 2)
and that two users – User01, User02 have exec permissions (expectedExecPermCount = 2 and expectedExecUserList = “User01,User02”)

If this is correct then the test will pass. If, for example, another user is granted exec permission this test will fail as the total permission count and exec perm count will now be 3.

When these tests are run a number of queries are run against the database (for MS SQL mainly against sys.database_permissions).

All of this implementation detail is handled by the framework. The user writing the tests does not need to worry about this.

An example of the type of query run is shown below*. It tests that User01 and User02 have exec permission on the sproc.

SELECT COUNT(dp.major_id)
FROM sys.database_permissions AS dp
INNER JOIN sys.objects AS o ON o.object_id = dp.major_id
INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id
INNER JOIN sys.database_principals as u ON u.principal_id = dp.grantee_principal_id
WHERE s.name + '.' + o.name='dbo.uspGetManagerEmployees'
AND dp.permission_name='EXECUTE'
AND dp.state_desc IN ('GRANT','GRANT_WITH_GRANT_OPTION')
AND u.name IN ('User01','User02')

*If you are interested in more implemention detail running SQL Profiler whilst testing will show the queries the framework is using to implement the tests.

Unit testing explicit permissions on a table

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

namespace DBTemplate.DatabaseTest.Tests.Tables.Permissions
{
    [TestFixture]
    public class SalesOrderHeader : SQLEntityPemissionsTestBase
    {
        public SalesOrderHeader()
        {
            dbInstance = "AdventureWorks";
            sqlEntityName = "Sales.SalesOrderHeader";
            sqlEntityPermissions = new SQLObjectPermissions(dbInstance, sqlEntityName);
            expectedTotalPermCount = 8;
            expectedDeletePermCount = 2;
            expectedDeleteUserList = "User01,User02";
            expectedInsertPermCount = 2;
            expectedInsertUserList = "User01,User02";
            expectedSelectPermCount = 2;
            expectedSelectUserList = "User01,User02";
            expectedUpdatePermCount = 2;
            expectedUpdateUserList = "User01,User02";
        }

The sample code above shows a permission test for a base table – ‘Sales.SalesOrderHeader’

These tests assert that we expect the table to have a total of 8 permissions set on it -expectedTotalPermCount = 8 – and that two users – User01,User02 – have explicit delete, insert, select and update permissions.

Like the previous examples if anyone else is given explicit permission to this table then this test will fail.

Version – 0.3.2.333 – database testing framework released

Version 0.3.2.333 of the database testing framework has been released and can be downloaded from SourceForge

It includes:

• A new sproc/function test method – ‘ParameterIsSameAsColumn’. This can be used to test that a sproc/function parameter has the same data type/length of an associated table/view column.
• Some of the default SQL objects that were added in the previous version have had some changes made to them. Also, the sproc dbo.p_ExtendedProperty_Insert has been renamed to dbo.p_ExtendedProperty_UpSert – to more accurately reflect what it does.
• A bat file – …DBTemplate\tools\DBTemplateSetUp.bat – that makes it easier/quicker to carry out the initial configuration/set up for a database testing project using the framework.

How to use the ‘ParameterIsSameAsColumn’

An example stored procedure unit test – that includes the new functionality – is shown below.

It uses the sproc – HumanResources.uspUpdateEmployeeHireInfo – from AdventureWorks2008.

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

namespace DBTemplate.DatabaseTest.Tests.Sprocs
{
    [TestFixture]
    public class uspUpdateEmployeeHireInfo : SprocTestBase
    {
        public uspUpdateEmployeeHireInfo()
        {
            dbInstance = "AdventureWorks2008";
            schema = "HumanResources";
            sprocName = "uspUpdateEmployeeHireInfo";
            sproc = new Sproc(dbInstance, schema, sprocName);
            expectedInputCount = 7;
            expectedInputList = "@BusinessEntityID,@JobTitle,@HireDate,@RateChangeDate,@Rate,@PayFrequency,@CurrentFlag";
            expectedOutputCount = 0;
            expectedOutputList = "";
        }

        [RowTest]
        [Row("@BusinessEntityID", "1,IN,int,N/A")]
        [Row("@JobTitle", "2,IN,nvarchar,50")]
        [Row("@HireDate", "3,IN,datetime,N/A")]
        [Row("@RateChangeDate", "4,IN,datetime,N/A")]
        [Row("@Rate", "5,IN,money,N/A")]
        [Row("@PayFrequency", "6,IN,tinyint,N/A")]
        [Row("@CurrentFlag", "7,IN,bit,N/A")]
        public void T05_ParameterProperties(string paramName, string expectedParameterProperties)
        {
            string parameterProperties = sproc.ParameterProperties(paramName);
            Assert.AreEqual(expectedParameterProperties, parameterProperties, "Param properties are not as expected");
        }

        [RowTest]
        [Row("@BusinessEntityID", "HumanResources.Employee", "BusinessEntityID")]
        [Row("@JobTitle", "HumanResources.Employee", "JobTitle")]
        [Row("@RateChangeDate", "HumanResources.EmployeePayHistory", "RateChangeDate")]
        [Row("@Rate", "HumanResources.EmployeePayHistory", "Rate")]
        [Row("@PayFrequency", "HumanResources.EmployeePayHistory", "PayFrequency")]
        [Row("@CurrentFlag", "HumanResources.Employee", "CurrentFlag")]
        public void T06_ParameterIsTheSameDataTypeAndLength(string paramName, string tableName, string columnName)
        {
            bool areTheSame = sproc.ParameterIsSameAsColumn(paramName, tableName, columnName);
            Assert.IsTrue(areTheSame, "Parameter and column are not the same");
        }
    }
}

Using the second parameter – @JobTitle – as an example.

It is used to update the base table column – HumanResources.Employee.JobTitle – and therefore should have the same type/length.

The test method – sproc.ParameterProperties(paramName) – in ‘T05_ParameterProperties’ – asserts that the parameter is of type nvarchar(50).

The new test method – sproc.ParameterIsSameAsColumn(paramName, tableName, columnName) is used in ‘T06_ParameterIsTheSameDataTypeAndLength’.

For the parameter @JobTitle it takes in a row test set of values of:

[Row(“@JobTitle”, “HumanResources.Employee”, “JobTitle”)]

This compares @JobTitle to the HumanResources.Employee.JobTitle column and if they are the same – it returns true.

This type of test can be useful in a number of ways:

• When designing sprocs. If a parameter is associated with column – e.g. for inserts, updating – it can help ensure that the correct type/length is set.
• If columns are changed/refactored e.g. the HumanResources.Employee.JobTitle column was changed to nvarchar(25) – then these tests will quickly highlight any sprocs/functions that might be effected by the changes.

Version – 0.3.1.282 – database testing framework released

A new version has been released – it can be downloaded from SourceForge -0.3.1.282_DatabaseTestingFramework.zip.

There are no changes to the DatabaseTesting.dll

ExportDBDataAsXML – is now part of the framework and can be found in the following dir:
..\DBTemplate\tools\ExportDBDataAsXML\

A number of new sql scripts – that create default SQL objects that I add to all databases – have been included and can be found in the following dir:
..\DBTemplate\misc\DBTemplateDatabase\SQL\DefaultDBObjectsScripts\

The table below outlines the main ones:

SQL object type	Name	Overview
Table	EventLog	General purpose logging table for both database and if appropriate application events. Sprocs included to log data into this table.
Table	BuildVersion	Used to track database schema changes and can help correlate with application versions.
Trigger	tr_DDL_Event	Captures any DDL events and logs them in dbo.EventLog.
Function	fn_CheckSum_TableConstraint, fn_CheckSum_Routine, fn_CheckSum_TableViewColumn	Returns checksum values based on db schema. These values are then stored in dbo.BuildVersion. Can use these to very quickly check that the schema is as expected.
Sproc	p_ExtendedProperty_Insert	Wrapper sproc around sys.sp_updateextendedproperty. Makes it a little easier to add extended properties whilst creating SQL scripts.

I would be interested in hearing from anyone who also has any ‘default’ SQL objects that they add to all databases.

Last modified: 2010-07-18

Version – 0.3.0.267 – database testing framework released

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.