SQLLocalDB Fast Unit Tests

Summary

One of my previous posts described how to setup a MS SQL Lite server called LocalDB.  My little demo project worked really good except for one detail.  It was slow.  The primary problem was that I was starting the instance, creating the database in the unit test initialize (called [TestInitialize]) and then I was dropping the database and stopping the instance in the test cleanup method.  That caused a database create and destroy for each unit test.  Now I’m going to show how to setup your unit tests to create the database once and destroy it once.  I’m also going to describe how to clean out your data for the next test.


The Truncate Table Command

The truncate table command has a couple of nice features: It can delete data fast, and it resets the seed value used for the identity column.  So I setup a list of tables to be truncated and a method that will perform the actual truncation.  Here’s how I implemented the list (which should be contained in your unit test module):

public static class SampleDatabase
{
    public static string[] TableList
    {
        get
        {
            // make sure these are in the right order.
            return new string[]
                {
                “product“,”producttype“,”store
                };
        }
    }

}

The code that handles the truncation looks like this:

public static void TruncateData(string databaseName, string[] tableList)
{
    SqlConnection db = new SqlConnection(
        “server=(localdb)\” + databaseName + “testinstance;” +
        “Trusted_Connection=yes;” +
        “database=” + databaseName + “; ” +
        “Integrated Security=true; ” +
        “connection timeout=30“);

    db.Open();

    foreach (var item in tableList)
    {
        SqlCommand myCommand = new SqlCommand(
           @”TRUNCATE TABLE ” + databaseName + “..” + item, db);

        myCommand.ExecuteNonQuery();
    }
    db.Close();

}

You can look at the sample code to see the details of how this fits together.  Basically, I call my TruncateData method inside the TestCleanup method to truncate the tables after every unit test.  When you create your list of tables to truncate, be aware that if you have constraints on your test database, you’ll have to truncate your tables in the right order.

Now, there is an AssemblyInitialize and an AssemblyCleanup attribute that can be used once inside the unit test project.  These are used with static methods and are called before your unit tests start and after your unit tests end.  I created a cs file named “AssemblyUnitTestShared.cs” that contains this code:

[TestClass]
public sealed class AssemblyUnitTestShared
{
    [AssemblyInitialize]
    public static void ClassStartInitialize(TestContext 
       testContext)
    {
        UnitTestStartAndEnd.Start(“sampledata“);
    }

    [AssemblyCleanup]
    public static void ClassEndCleanup()
    {
        UnitTestStartAndEnd.End(“sampledata“);
    }

}

At this point your unit tests would look something like this:

[TestMethod]
public void test_method_2()
{
    using (ISession unitTestSession = 
           SampleDataTestContext.OpenSession())
    {
        Product product = new Product
        {
            Name = “P 1“,
            ProductId = 1,
            store = 1
        };
        unitTestSession.Save(product);
        unitTestSession.Flush();

        ProductObject productObject = new 
              ProductObject(unitTestSession);

        var productList = productObject.ReadAll();

        Assert.AreEqual(“P 1“, productList[0].Name);
    }

}

When executing these unit tests there will be an initial delay while the database is being created, then all the tests will run in a few seconds time, then there is a delay while the database is being torn down.  Adding more tests will not significantly increase your total unit test execution time.

Here’s the source code, which I would recommend downloading and getting it to work.  This can serve as a model for your project unit tests.

SQLLocalDBBlogPost2.zip


Other Considerations

At this point, I have shown how you can use LocalDB to create a temporary database and run unit tests against methods that contain database operations.  There are still some issues here that should be resolved.

First, what about integrity constraints.  One method is to apply all the constraints to the database after the tables are created (which currently occurs on the first call to the nhibernate context).  The problem with this method is that each unit test would need to incorporate all the required seed data that constrains any tables that are needed for the actual test.  This is a lot of unnecessary work.

Another method is to only generate the constraints at the unit test level and destroy all the constraints at the end of the unit tests.  I have not tried this yet, but there will be some sort of performance penalty.  If the number of constraints is restricted to just the tables that are necessary, then the performance loss will be minimized and the unit tests can be contained within the scope of what is being tested.  

So now we would need to come up with code to generate and destroy these constraints.  One of my older methods of doing this is to create a list of all the constraints in the database and write a method that receives a list of tables.  Any foreign constraints would be added only if both tables were present in the list.  Unique constraints added if the table with the constraint was in the list and so on.  Then the tear-down can be accommodated by scanning the master database for foreign and unique constraints.  This code can all be encapsulated in a common project that you would use with your unit tests (except the table list which you have here and the list of actual constraints, because they are database dependent).  

The next issue surrounds the fact that your database will be updated at some time in the future and you’ll need to be aware that any constraints changed in the database have to be changed in your test package.  There is no simple solution for this problem.  In the past I have created software that reads the tables, fields, constraints, indexes, etc from an excel file.  This excel file was the master definition of everything.  Any database changes would be made to this file and a tool I developed would synch changes with the actual database (when the developer was ready to apply changes).  This excel file was then used by my unit tests and out of synch issues were minimized in this fashion.  

Another issue surrounds the fact that you need to seed your data for a unit test, and this can get very tedious in a hurry.  So I have written classes and methods to read data from an xml file (embedded in the unit test project) to pre-populate the data in a unit test before it runs.  Then I name my xml file with the unit test source name and the method name that calls it.  I use individual files for each method because data can be altered in the future (which could mess up multiple tests if they share one xml file).  Copy and paste is my friend.  The layout of such a file would look something like this:

<data>
  <tablename>
    <field1>data in field 1</field1>
    <field2>data in field 2</field2>
    <field3>data in field 3</field3>
  </tablename>
</data>

One last thing to be aware of.  It seems that LocalDB does not use the FILEGROWTH parameter.  I have not dug too deep on this issue (I just increased my starting database size to 8k), but you might run into an insufficient size problem if you insert too much data into your unit test database.  You can either bump up the size or determine if there is a mechanism to auto expand.

If your looking for the SQLLocalDB package to install:

x64/SQLLocalDB.MSI for 64 bit systems (33.0MB)
x86/SQLLocalDB.MSI for 32 bit systems (27.7 MB)







 

Fluent NHibernate “Not Supported By” Exception

Summary

So my company has successfully switched to using Fluent NHibernate for our production systems.  So far the results are astounding.  One thing our developers have learned is that there is no “perfect” ORM.  Not yet anyway.  Each seems to have a flaw or two.  NHibernate has a couple of issues that can be worked around.  One issue, I mentioned in an earlier post regarding the 1200 parameter limit.  While unexpected, it was easy to see why it was an issue.  The issue I want to talk about today came up when one of our developers was working with a group by query involving other query operations like order by and take, etc. So I’m going to talk about one issue and show a solution.


The Setup

I’m going to use my standard sampledata database in MS SQL server with 3 tables: Product, ProductType and Store.  Here’s the ERD:



I put some seed data into these tables, it doesn’t matter what you put in the tables.  There is a SQL script in the project (named “CreateTables.txt”), you can copy the text inside this file and execute it inside a query window in MS SQL Server Management Studio.


The Ugly

Here’s the offending LINQ that I setup in my console app:

var query =
    (from p in session.Query<Product>()
     join pt in session.Query<ProductType>() 
       on p.ProductType equals pt.Id
     join s in session.Query<Store>() on p.store equals s.id
     group new { p, pt } 
     by new { pt.Id, pt.Description, p.Name } into temp
     select new
     {
      name = temp.Key.Name,
      id = temp.Key.Id,
      description = temp.Key.Description,
      count = temp.Count()
     }).OrderBy(u => u.name).ToList();

If you attempt to execute this query, you’ll get an error like this:


This error only occurred when I added the “OrderBy” onto the query.  So it appears that a combination of query parameters can cause this error to occur, or possibly there is an issue with the anonymous types generated in the “select new” part of the query.  


The Fix

I dug around for an answer to this problem and found this Stack Overflow issue:

Linq query nhibernate; not supported exception

That’s where I discovered a solution to this query.  I added the “AsEnumerable()” before the “OrderBy” to fix this problem:

var query =
    (from in session.Query<Product>()
     join pt in session.Query<ProductType>() 
       on p.ProductType equals pt.Id
     join s in session.Query<Store>() on p.store equals s.id
     group new { p, pt } 
     by new { pt.Id, pt.Description, p.Name } into temp
     select new
     {
      name = temp.Key.Name,
      id = temp.Key.Id,
      description = temp.Key.Description,
      count = temp.Count()
     }).AsEnumerable().OrderBy(u => u.name).ToList();


Now the query executes and produces the expected results.

You can download the sample project here:
FluentNHibernateGroupBlogPost.zip

 

Unit Testing Fluent NHibernate

Summary

I’ve covered a little unit testing of NHibernate in the past.  I showed how to use SQLite, which is the most common method of unit testing (technically, integration testing) an NHibernate application.  If you’re using MS SQL server for your application, SQLite has some serious limitations that could hinder your unit tests.  So I’m going to show you how to use Microsoft’s LocalDB.


The Problems with SQLite

One of the first issues I discovered with SQLite is that it doesn’t do multiple databases.  Simulating a multi-database join in a unit test is not workable with SQLite.  The second and most annoying “feature” of SQLite is that it doesn’t behave well with a command line MSTest.  There are work-arounds, including the fact that you have to create a test settings file and point your script to that file to make MSTest work with SQLite unit tests.  Other issues surround the 32bit/64bit dll’s and the difficulty in making it all work on Teamcity.


Microsoft SQL Express and LocalDB

Apparently, Microsoft came up with a solution to SQLite, called LocalDB.  It’s a version of SQL Express that is lightweight and fully-featured.  The 64-bit install is only 33MB in size and there is no special driver for NHibernate.  If you are going to develop for MS SQL server, then the driver is the same for LocalDB.

Here’s the first document I stumbled across when I was looking for this product: Introducing LocalDB, an improved SQL Express.  In order to get things rolling quick, you can go to this article: Getting Started with SQL Server 2012 Express LocalDB.


The Sample Project

So I put together a project based on my previous example with a Student, Teacher and Class table (I renamed “Class” into “SchoolClass” to prevent a clash with the keyword “Class”).  I added a test project onto the solution and created a special context for the purpose of my unit tests.  Here’s how the code looks:

public class TestSessionFactory
{
    private static ISessionFactory SessionFactory
    {
        get
        {
            return Fluently.Configure()
            .Database(MsSqlConfiguration.MsSql2005
            .ConnectionString(“Server=(localdb)\testinstance;Integrated Security=True”))
            .Mappings(m => m.FluentMappings.Add<SchoolClassMap>())
            .Mappings(m => m.FluentMappings.Add<TeacherMap>())
            .Mappings(m => m.FluentMappings.Add<StudentMap>())
            .ExposeConfiguration(cfg => new SchemaExport(cfg).Create(true, true))
            .BuildSessionFactory();
        }
    }
    public static ISession OpenSession()
    {
        return SessionFactory.OpenSession();
    }

}


The first thing you’ll notice is that I was able to use the “Add” for the fluent mappings.  SQLite doesn’t allow this.  This can be handy if you have duplicate table names in separate databases, but create your class and mappings in one project.  You have fine-grain control over which tables will appear in your context.

The second thing to note is that you can tell the session factory to generate a schema when it starts.  If you were to use this session factory in your unit tests as-is, you’ll get nothing.  That’s because Fluent NHibernate will not create the initial databases.  For that, I wrote another method:

public static void Create(string databaseName, string instanceConnection)
{
    string databaseDirectory = Directory.GetCurrentDirectory();

    SqlConnection db;
    db = new SqlConnection(“server=” + instanceConnection + “;” +
                            “Trusted_Connection=yes;” +
                            “database=master; ” +
                            “Integrated Security=true; ” +
                            “connection timeout=30“);

    db.Open();

    SqlCommand myCommand = new SqlCommand(@”CREATE DATABASE [” + databaseName + @”]
        CONTAINMENT = NONE
        ON  PRIMARY 
        ( NAME = N’” + databaseName + @”‘, FILENAME = N’” + databaseDirectory + @”” + databaseName +
                                                @”.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB )
        LOG ON 
        ( NAME = N’” + databaseName + @”_log’, FILENAME = N’” + databaseDirectory + @”” + databaseName +
                                                @”_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)
        “, db);

    myCommand.ExecuteNonQuery();
    db.Close();

}

This method is contained in the “TestDatabase” class along with a “Remove” method to undo the database creation.  I generated the database create command from the SQL Server management console.  I made the application execute directory (bin directory) the default location for the database files to be created, using the Directory.GetCurrentDirectory() method.


The Unit Tests

The unit tests have an initialize and a cleanup method.  The Initialize will setup the LocalDB instance and create a database.  The cleanup reverses the operation by dropping the database first, then shutting down the instance and deleting it.  

The ms test initialize method looks like this:

[TestInitialize]
public void Initialize()
{
    // … removed code not discussed

    // create a new localdb sql server instance
    startInfo = new ProcessStartInfo
        {
            WindowStyle = ProcessWindowStyle.Hidden,
            FileName = “cmd.exe“,
            Arguments = “/c sqllocaldb create “testinstance” -s
        };

    process = new Process { StartInfo = startInfo };
    process.Start();
    process.WaitForExit();

    TestDatabase.Create(“facultydata“, “(localdb)\testinstance“);
    TestDatabase.Create(“studentdata“, “(localdb)\testinstance“);

}


I used the Process class to execute a command line prompt to kick-off the LocalDB instance.  I named this instance “testinstance”.  That is used inside the context when Fluent NHibernate connects to the database.  It can also be used by the SQL Server management console to connect and troubleshoot problems.  Use the “(localdb)testinstance” syntax to connect to the database.


The unit tests look like this:

[TestMethod]
public void ThirdTest()
{
    using (ISession db = TestSessionFactory.OpenSession())
    {
        Teacher teacher = new Teacher
        {
            Name = “test teacher
        };

        db.Save(teacher);

        teacher = new Teacher
        {
            Name = “test teacher2
        };

        db.Save(teacher);

        var query = (from t in db.Query<Teacher>() select t).ToList();

        Assert.AreEqual(2, query.Count);
    }

}


You can download the sample code here:

SQLServerLocalDBTest.zip