Have you ever found out that your application believed that an entity property was stored in DB as a different type than it actually was (varchar instead of nvarchar, int instead of tinyint etc.)? Maybe you haven't, but would like to check before learning it in production the hard way? Most of today's ORM mapping mechanisms allow to generate the DB schema automatically, but there is a number of reasons to create or update it manually. One of them is that you gain better flexibility and could achieve some designs not possible otherwise. On the other hand the developer overtake the responsibility for the consistency between application model and the DB schema. And as we (developers) all know - even though we are almost perfect :), if something is not automatically checked, some tiny error will eventually find a way to make our life harder.

Divergence between configuration and the actual database schema could lead to serious issues i.e. Implicit conversion. What if we could prevent it with just several lines of a simple test code? Read on to learn how to do it for NHibernate.

Fast answer

I hate scrolling through long introductions just to get to the code I'm looking for. So in short version - just use SchemaValidator class built-in the NHibernate itself in an integration test like this:

[Test]
public void Schema_Should_Be_Consistent_With_Actual_DB()
{
    // arrange
    InitializeIntegrationTest();
    var configuration = GetNHMappingConfiguration();
    var sv = new NHibernate.Tool.hbm2ddl.SchemaValidator(configuration);

    // act && assert
    sv.Validate();
}

private void InitializeIntegrationTest()
{
    // you should configure NHibernate with your model and connetion to a working DB here    
}

private NHibernate.Cfg.Configuration GetNHMappingConfiguration()
{
    //provide configuration of your model here    
}

If there are any divergent columns in your DB running this test will result with an exception. The exception will show the first of the present issues.

Sample result (table and column names are changed):

Hibernate.HibernateException : Wrong column type in TestedDatabaseName.dbo.SampleTable123 for column AAStateEnm. Found: smallint, Expected TINYINT
   at NHibernate.Mapping.Table.ValidateColumns(Dialect dialect, IMapping mapping, ITableMetadata tableInfo)
   at NHibernate.Cfg.Configuration.ValidateSchema(Dialect dialect, DatabaseMetadata databaseMetadata)
   at NHibernate.Tool.hbm2ddl.SchemaValidator.Validate()

We get to know that there exists at least one problem with our mapping. The column AAStateEnm of the table SampleTable123 is smallint whereas nhiberante will expect tinyint (accordingly to the mapping configuration). This might be the only issue or there might be more of them. You'll get to know when you fix the first one. This might be sufficient for some usages and enough for some readers. If you want to know how to get the NHibernate configuration in GetNHMappingConfiguration method or learn about some modifications allowing to achieve some more goals, please read on.

The main goals of the test

It should simply fail when NHibernate mapping is not consistent with the reality. The test should analyze the model and raise an error if any property of any entity is mapped to a different DB type than the actual one in the DB. This requires querying an actual existing DB. Hence the test will be an integration one.

Additional goals

I wanted the test to help to asses and remove divergences both from new and existing legacy projects. That resulted in several requirements:

  1. The test need to be compatible with NH 3.3 and 4.x
  2. It should allow to assess how many and what kind of issues we are dealing with (in a single test run)
  3. It should be fast to introduce both in legacy and new projects
  4. One of the legacy projects is configured to quote all sql tables names (like this [Transaction]), which causes the original NH SchemaValidator to show false errors (not existing tables). Test should be immune to this problem.

The actual implementation

This is how the actual final test looks like:

[Test]
public void Schema_Should_Be_Consistent_With_Actual_Db()
{            
    // arrange
    var sv = new TestInfrastructure.SchemaValidator(Fixture.PersistanceConfiguration);

    // act
    var issues = sv.Validate();

    // assert            
    Assert.That(issues.Count, Is.EqualTo(0), "Found issues: " + String.Join("\n", issues));
}

Some explanation: There is no NH configuration preparation in the test because in our integration tests we use fixture that is responsible for that - the setup method is automatically called before each test. At the end of the article I will show how it is used to provide the configuration to the test and everything I implement in the fixture could be also done just as a part of the test setup (if you prefer it this way).

Bellow sample result of the new test (table and column names are changed):

Found issues: 
Wrong column type in TestedDatabaseName.dbo.SampleTable123 for column AAStateEnm. Found: smallint, Expected TINYINT
Wrong column type in TestedDatabaseName.dbo.SampleTable2 for column AnotherColumnEnm. Found: int, Expected TINYINT
Wrong column type in TestedDatabaseName.dbo.SampleTable2 for column PCTEnm. Found: int, Expected TINYINT
Wrong column type in TestedDatabaseName.dbo.SampleTable2 for column PErrorEnm. Found: int, Expected TINYINT
Wrong column type in TestedDatabaseName.dbo.SampleTable3211 for column AnotherColumnEnm. Found: int, Expected TINYINT
Wrong column type in TestedDatabaseName.dbo.SampleTable3211 for column PErrorEnm. Found: int, Expected TINYINT
Wrong column type in TestedDatabaseName.dbo.SampleTable3211 for column PCTEnm. Found: int, Expected TINYINT
Wrong column type in TestedDatabaseName.dbo.SampleTable444 for column TStatusEnm. Found: int, Expected TINYINT
Wrong column type in TestedDatabaseName.dbo.SampleTable599999 for column SDOW. Found: smallint, Expected INT
Wrong column type in TestedDatabaseName.dbo.SampleTable599999 for column EDOW. Found: smallint, Expected INT
Wrong column type in TestedDatabaseName.dbo.SampleTable67 for column SDOW. Found: smallint, Expected INT
Wrong column type in TestedDatabaseName.dbo.SampleTable67 for column EDOW. Found: smallint, Expected INT
Wrong column type in TestedDatabaseName.dbo.SampleTable7000 for column PLVersion. Found: smallint, Expected INT
  Expected: 0
  But was:  13

As you could see all existing issues are listed in the test result. This is important for legacy projects as we could estimate what is the effort to fix all of the issues before starting the work. 7 out of 13 issues is actually the same issue of mapping enumeration - this could be probably fixed with a single mapping convention or changing the enums' base type. The rest should also be a piece of cake once you know they exist.

The test is using a new implementation of SchemaValidator. Instead of throwing exception it returns list of found issues and also copes with the table name quoting issue. Unfortunately most of the NH SchemaValidator's methods and fields are private so it is not easy to use inheritance here, so inspired by Lukasz Baran Blog I've copied the implementation from NH and introduced my modifications to create a new class. My implementation differs a bit so I've put it on github. Feel free to use it:

Modified SchemaValidator on github

Last thing to sort out - let's deliver the configuration to the test. NHibernate doesn't expose the configuration in any way during runtime. Moreover AFAIK it is not even stored internally. Configuration in the projects I considered was done in different manners with usage of different class hierarchy, static/non-static methods etc. and I wanted some common way for the test to be able to inject in this process to get the configuration. I decided to create a static event. Bellow an example of how it could be done when using FluentNHibernate.

//The event - anything that wants to use the configuration could subscribe to it
public static event EventHandler<NHibernateCfg.Configuration> ConfigurationCreated;

ISessionFactory BuildSessionFactory(IUnityContainer container, string connectionStringName, string exportPath, TextWriter schemaOutputWriter, Assembly[] assemblies)
{
    NHibernateCfg.Configuration configuration = null;
    var sessionFactory = Fluently.Configure()
        //these calls are just exemplary - the important one is the .ExposeConfiguration(...)
        .Database(GetDatabaseConfiguration(container, connectionStringName))
        .Mappings(m => new TModelPersistenceConfigurator().Configure(m, exportPath, assemblies))
        .CurrentSessionContext<TSessionContext>()
        .ExposeConfiguration(c =>
        {
            configuration = c;
            //some additional configuration could be done here but it is irrelevant to this article
        })
        .BuildSessionFactory();

    //Invoke the event handlers so they can get the configuration
    if (ConfigurationCreated != null)
        ConfigurationCreated(this, configuration);

    return sessionFactory;
}

And the usage in our fixture:

public Configuration PersistanceConfiguration { get; private set; }

private void Configure()
{
    //Store the configuration so it could be used in the test later
    CustomNHibernateConfigurator.ConfigurationCreated += (sender, configuration) => { PersistanceConfiguration = configuration; };
    //here is the place to perform the actual NH configuration
    //...
}

The above could be done directly in the test if you don't use a fixture class.

Summary

Someone could argue that the schema validation could be called during application startup instead of an integration test but I prefer to have a test over slowing down each application launch.

Once You know how, it is much faster to implement the test in your project than it was to write this article (probably it also applies to reading it too :)). It allows to find issues in legacy projects and prevent creating them in new ones leading to better quality and performance of db queries. It has already proven to be extremely helpful for me and I hope that it could help also someone else!

About the author:

Przemysław Polański

Senior developer and team lead in BT Skyrise involved in the company's life since the dawn of its existence. Programming commercially and compulsively since 2007. On the journey through multiple technologies and programming languages he found out that there’s always some space for improvement and sometimes enough time to introduce it.

Next Post Previous Post

blog comments powered by Disqus