This is the third article in the series, Close These Loopholes in Your Database Testing. It is worth reading them in sequence.
Close These Loopholes in Your Database Testing
Close these Loopholes – Testing Stored Procedures
In this article we shall demonstrate a simple way to unit test successful modifications. Similarly to the previous article, the expected result of a successful modification – the difference between the original data and the modified one – will be saved in an XML file when setting up the test. The actual differences will be compared against the pre-saved XML file. Unit testing how your system handles unsuccessful modifications will be described in the next article.
Why do you want to compare differences?
In the previous article we demonstrated how to compare a result set against a previously saved XML file. Isn’t it easy to just reuse the same approach for testing modifications? Why can’t you just apply the modifications, select all the rows from your modified table, and compared the result set in the same way? Although this approach is very simple, it might not be the best thing to do in the long term in all the cases. Let me elaborate. Suppose you need to write a unit test for the following simple stored procedure:
CREATE SCHEMA data
GO
CREATE SCHEMA Writers
GO
CREATE TABLE data.Employees(
EmployeeID INT NOT NULL,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
Status VARCHAR(6) NOT NULL CONSTRAINT CHK_Employees_Status CHECK(Status IN(‘Active’, ‘Quit’)),
HireDate DATETIME NOT NULL
)
GO
CREATE PROCEDURE Writers.UpdateEmployee
@EmployeeID INT,
@FirstName VARCHAR(20),
@LastName VARCHAR(20),
@Status VARCHAR(6),
@HireDate DATETIME
AS
UPDATE data.Employees SET
FirstName = @FirstName,
LastName = @LastName,
Status = @Status,
HireDate = @HireDate
WHERE EmployeeID = @EmployeeID
RETURN @@ERROR
GO
You need to both test that one row has been modified as expected and verify that all the other rows have not been changed. To me this clearly sounds like a description of a difference between the original state of data and the modified one. Because I think in terms of my expected result as a difference, I want this difference to be clearly described in my XML file. No matter how many other rows are there in the table, the difference will be the same if the row that matches the provided @EmployeeID exists – later on I can open up the XML file and clearly see my expected result stored in a succinct way.
There is one more practical reason to deal with differences – long term maintenance of your test harness. Suppose for the next release you need to add more test data. For instance, suppose you have several new reports displaying employees hired more than 5 years ago, and suppose your test fixture set up does not provide such data at this time. Suppose you have modified your test fixture set up, so that now it adds some employees hired more than five years ago. If you went for a seemingly easy “select all the rows from your modified table” approach, now all your tests for modifications do not work any more. You need to either re-generate your XML files, or to add additional test data in test set up step for some tests, or just to create another test fixture. Either way, you will add complexity to your test harness and your test harness will become more difficult to maintain.
If, on the other hand, the expected results of your modifications are stored as differences, they stand a really good chance to survive most changes in the text fixture setup intact. In my experience it is better to have as few test fixtures as feasible – that allows me to efficiently reuse code in my unit tests. This is why the additional effort to develop a class comparing differences and to utilize this class is justified – it makes long term maintenance of your unit test harness significantly simpler.
A sample unit test fixture.
The following unit test fixture populates test data, creates a database snapshot, attempts to modify data.Employees table, and compares the actual modifications against the pre-saved ones. After the comparison it tears down simply by rolling back a transaction. This fixture is as simple as possible: it has only one unit test, just for brevity. Typically we reuse a test fixture as much as possible, and we have a lot of unit tests in one fixture. Also the unit test verifies modifications in only one table, and it excludes only one column from comparison. In real life we frequently need to compare more than one table in one unit test. Also we exclude from comparison columns of timestamp type, columns such as LastModifiedDate. Note that if you in one statement insert multiple rows into a table with identity column, you cannot guarantee which rows gets which identity. This is why in some cases we also exclude identities from our comparisons.
Note:
- The database snapshot will be created after all the test data has been populated in the test fixture setup phase. It will be utilized when determining the difference between the original state of the database and the modified one. Creating and dropping a snapshot takes some time, but it significantly simplifies the comparisons.
- The easiest way to tear down after a unit test is to begin a transaction in the test setup phase and roll it back in the test teardown phase. Alternatively, you can manually restore modified data to its original state, but this approach is prone to errors, especially when new data is added in the test harness setup phase. Because you already have a snapshot, you also have the option of simply restoring from it. Note, however, that restoring from a snapshot typically takes more time than rolling back a transaction.
- If your tables have IDENTITY columns, you need to reset IDENTITY seeds after inserts, because rolling back a transaction does not reset it. Also it is convenient to set IDENTITY_INSERT ON in test fixture set up phase, insert rows with negative identities, and use DBCC CHECKIDENT to set identity seed to 1 at the end of your test fixture set up phase.
- if you change database and/or server settings in your unit test, make sure to restore them when you tear down, otherwise the changed settings may affect other unit tests.
Anyway, the sample unit test harness is quite simple and easy, as follows:
using System.Data.SqlClient;
using NUnit.Framework;
namespace DatabaseUnitTesting.Tests
{
[TestFixture]
public class DatabaseUnitTesterTests
{
private SqlConnection connection;
private SqlTransaction transaction;
private DatabaseUnitTester unitTests;
private const bool bSetupMode = false;
[TestFixtureSetUp]
public void TFSetup()
{
//populating test data is skipped
string connectionString = “Application id=”UnitTests;” Trusted_Connection=true; Server=(local); Database=Payroll”;
connection = new SqlConnection(connectionString);
connection.Open();
//a database snapshot is created under the hood in this constructor
unitTests = new DatabaseUnitTester(connection, “Payroll”, “PayrollSnapshot”);
}
[TestFixtureTearDown]
public void TFTearDown()
{
//a database snapshot is dropped under the hood in this method
unitTests.Dispose();
// deleting test data is skipped
connection.Close();
}
[SetUp]
public void SetUp()
{
transaction = unitTests.BeginTestTransaction();
}
[TearDown]
public void TearDown()
{
unitTests.EndTestTransaction();
}
[Test]
public void UpdateEmployeesTest()
{
string filename = “../../TestResults/UpdateEmployees.xml”;
// only data in Employees table will be compared
unitTests.AddObjectComparison(“data”, “Employees”);
// But the EmployeeID column is excluded from comparison,
// just to demonstrate how we do it.
unitTests.AddColumnToIgnore(“data”, “Employees”, “EmployeeID”);
SqlCommand command = connection.CreateCommand();
command.Transaction = transaction;
command.CommandText = “Writers.UpdateEmployees”;
command.Parameters.AddWithValue(“@EmployeeID”, 10);
command.Parameters.AddWithValue(“@FirstName”, “John”);
command.Parameters.AddWithValue(“@LastName”, “Smith”);
command.Parameters.AddWithValue(“@status”, “Quit”);
command.Parameters.AddWithValue(“@HireDate”, “20060601”);
command.ExecuteNonQuery();
if(bSetupMode)
unitTests.WriteDiffsToXml(filename);
else
Assert.IsTrue(unitTests.CompareDiffsToXml(filename));
}
}
}
AS will explain the implementation of methods WriteDiffsToXml and CompareDiffsToXml in the next chapters.
The Database Modification Tester
Again we included the more powerful version of the testing library in the attached archive, but for the purpose of this article, we will use the simpler code as an example.
Again enabling the creation of simple unit tests is a more complicated set of classes behind the scene. These classes create the snapshot, manage the transactions, determine the differences between the database and snapshot, and save and compare the differences to an XML file.
The first class, DatabaseModificationTester, is the interface you will use for your unit tests. It is constructed with a connection to a server, the name of a database to test, and the name to use for the snapshot. The tester will create a snapshot to use for comparison for the entire scope of the test fixture, and it will supply a transaction to use for each individual test. The transaction is rolled back at the end of each, reverting any modifications. This ensures the database is not ultimately modified by your unit testing and creates an identical initial state for each test.
At the beginning of each test, you will tell the tester what tables or views in the database should be checked for modifications. You can then specify which columns in those objects should be ignored during comparison. Next, you can execute the stored procedure you want to test in the transaction supplied by the tester. Finally, you can either save the results to an XML file or compare the results to a previously saved XML file.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace UnitTestingTools
{
public class DatabaseModificationTester : IDisposable
{
private readonly DataComparer _dataComparer;
private readonly List<ObjectToCompare> _objectsToCompare = new List<ObjectToCompare>();
private readonly SqlConnection _connection;
private readonly DataSet _results = new DataSet();
private SqlTransaction _transaction;
public DatabaseModificationTester(SqlConnection connection, string databaseName, string snapshotName)
{
_connection = connection;
_dataComparer = new DataComparer(connection, databaseName, snapshotName);
}
public SqlTransaction BeginTestTransaction()
{
if(_transaction != null)
EndTestTransaction();
return (_transaction = _connection.BeginTransaction());
}
public void EndTestTransaction()
{
if(_transaction == null)
throw new InvalidOperationException(“Transaction does not exist”);
_objectsToCompare.Clear();
_transaction.Rollback();
_transaction.Dispose();
_transaction = null;
}
public bool AreEqual()
{
RunComparison();
foreach(DataTable table in _results.Tables)
if(table.Rows.Count > 0)
return false;
return true;
}
public bool CompareToFile(string filename)
{
RunComparison();
DataSet other = new DataSet();
other.ReadXml(filename);
other.AcceptChanges();
return DataSetComparer.Compare(_results, other);
}
public void SaveToFile(string filename)
{
RunComparison();
_results.WriteXml(filename, XmlWriteMode.WriteSchema);
}
public void AddObjectComparison(string schemaName, string objectName)
{
_objectsToCompare.Add(new ObjectToCompare(schemaName, objectName));
}
public void AddColumnToIgnore(string schemaName, string objectName, string columnName)
{
_objectsToCompare.Find(
delegate(ObjectToCompare item) { return item.SchemaName == schemaName && item.ObjectName == objectName; }).AddColumnToIgnore(
columnName);
}
public void Dispose()
{
if(_transaction != null)
EndTestTransaction();
_dataComparer.Dispose();
}
private void RunComparison()
{
_results.Reset();
foreach(ObjectToCompare objectToCompare in _objectsToCompare)
_dataComparer.Compare(objectToCompare, _transaction, _results);
_results.AcceptChanges();
}
}
}
The Database Comparer
Behind the interface class lays the DatabaseComparer class that actually manages the snapshot and compares the database to the snapshot. This class should require no modification to get you started, although you may want to edit the location that the temporary snapshots are stored in.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace UnitTestingTools
{
internal class DatabaseComparer : IDisposable
{
private readonly string databaseName;
private readonly string snapshotName;
private readonly SqlConnection connection;
internal DatabaseComparer(SqlConnection connection, string databaseName, string snapshotName)
{
this.databaseName = databaseName;
this.snapshotName = snapshotName;
this.connection = connection;
CreateSnapshot();
}
private void CreateSnapshot()
{
SqlCommand command = connection.CreateCommand();
command.CommandText = “CREATE DATABASE ” + snapshotName + ” ON ( NAME = ” + databaseName +
“, FILENAME = ‘C:\\Temp\\” + snapshotName + “.snap’) AS SNAPSHOT OF ” +
databaseName;
command.ExecuteNonQuery();
}
internal void Compare(ObjectToCompare objectToCompare, SqlTransaction transaction, DataSet results)
{
DataSet tempResult = new DataSet();
string columns = GetAllColumns(objectToCompare, transaction);
string select = “SELECT ” + columns + “, ROW_NUMBER() OVER(PARTITION BY ” + columns +
” ORDER BY @@SPID) AS ‘TempRowNumber’ FROM “;
string select1 = select + databaseName + “.” + objectToCompare.Qualified;
string select2 = select + snapshotName + “.” + objectToCompare.Qualified;
SqlCommand command = connection.CreateCommand();
command.Transaction = transaction;
command.CommandText = select1 + ” EXCEPT ” + select2 + “\n” + select2 + ” EXCEPT ” + select1;
using(SqlDataAdapter da = new SqlDataAdapter(command))
{
da.Fill(tempResult);
}
tempResult.Tables[0].TableName = objectToCompare.Qualified + ” in 1″;
tempResult.Tables[1].TableName = objectToCompare.Qualified + ” in 2″;
results.Tables.Add(tempResult.Tables[0].Copy());
results.Tables.Add(tempResult.Tables[1].Copy());
}
private string GetAllColumns(ObjectToCompare objects, SqlTransaction transaction)
{
SqlCommand command = connection.CreateCommand();
command.CommandText =
“SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = @table AND” +
” table_schema = @schema AND table_catalog = @database”;
command.Transaction = transaction;
command.Parameters.AddWithValue(“@table”, objects.ObjectName);
command.Parameters.AddWithValue(“@schema”, objects.SchemaName);
command.Parameters.AddWithValue(“@database”, databaseName);
List<string> allColumns = new List<string>();
using(SqlDataReader result = command.ExecuteReader())
{
while(result.Read())
allColumns.Add(result.GetString(0));
}
foreach(string victim in objects.ColumnsToIgnore)
{
if(!allColumns.Remove(victim))
throw new ArgumentException(“Specified column ” + victim + ” was not in table ” +
objects.Qualified);
if(allColumns.Count == 0)
throw new ArgumentException(“User cannot ignore all columns table ” + objects.Qualified);
}
return “[” + String.Join(“],[“, allColumns.ToArray())+”]”;
}
public void Dispose()
{
SqlCommand command = connection.CreateCommand();
command.CommandText = “DROP DATABASE ” + snapshotName;
command.ExecuteNonQuery();
}
}
}
The DataSet Comparer
A class we see returning from the previous article is the DataSetComparer. This compares the differences currently generated with those saved in an XML file. Again, this can be customized to fit your needs.
using System;
using System.Data;
namespace UnitTestingTools
{
internal static class DataSetComparer
{
internal static bool Compare(DataSet one, DataSet two)
{
if(one.Tables.Count != two.Tables.Count)
return false;
for(int i = 0; i < one.Tables.Count; i++)
if(!CompareTables(one.Tables[i], two.Tables[i]))
return false;
return true;
}
private static bool CompareTables(DataTable one, DataTable two)
{
if(one.Rows.Count != two.Rows.Count)
return false;
for(int i = 0; i < one.Rows.Count; i++)
if(!CompareRows(one.Rows[i], two.Rows[i]))
return false;
return true;
}
private static bool CompareRows(DataRow one, DataRow two)
{
if(one.ItemArray.Length != two.ItemArray.Length)
return false;
for(int i = 0; i < one.ItemArray.Length; i++)
if(!CompareItems(one.ItemArray[i], two.ItemArray[i]))
return false;
return true;
}
private static bool CompareItems(object value1, object value2)
{
if(value1.GetType() != value2.GetType())
return false;
if(value1 is DBNull)
return true;
if(value1 is DateTime)
return ((DateTime) value1).CompareTo((DateTime) value2)
== 0;
if(value1 is byte[])
{
if(((byte[]) value1).Length != ((byte[]) value2).Length)
return false;
for(int i = 0; i < ((byte[]) value1).Length; i++)
if(((byte[]) value1)[i] != ((byte[]) value2)[i])
return false;
return true;
}
return value1.ToString().Equals(value2.ToString());
}
}
The Object ToCompare Class
Another class used by the tester is the ObjectToCompare class. This class simply represents a table or view in a database. It stores the schema and name of the object as well as a list of columns to be ignored during comparison.
using System.Collections.Generic;
namespace UnitTestingTools
{
internal class ObjectToCompare
{
private readonly string _schemaName;
private readonly string _objectName;
private readonly List<string> _columnsToIgnore = new List<string>();
public ObjectToCompare(string schemaName, string objectName)
{
_schemaName = schemaName;
_objectName = objectName;
}
public IEnumerable<string> ColumnsToIgnore
{
get { return _columnsToIgnore; }
}
public string ObjectName
{
get { return _objectName; }
}
public string SchemaName
{
get { return _schemaName; }
}
public string Qualified
{
get { return _schemaName + “.” + _objectName; }
}
public void AddColumnToIgnore(string columnName)
{
_columnsToIgnore.Add(columnName);
}
}
}
Conclusion
In this article we have demonstrated how to test successful database modifications.
Because a robust application must gracefully handle errors, you also need to make your modifications fail, and to test how your application handles these failures.
I will describe that in my next article.
Load comments