Fluent NHibernate - PersistenceSpecification of HiLo scheme

Question

Not sure if I'm asking the right question so please bear with me! Bit of an NHibernate noob.

We're using Fluent NH and have the following id generation scheme for all tables

public class IdGenerationConvention : IIdConvention
{
    public void Apply(IIdentityInstance instance)
    {
        var where = string.Format("TableKey = '{0}'", instance.EntityType.Name);
        instance.GeneratedBy.HiLo("HiloPrimaryKeys", "NextHighValue", "1000", x => x.AddParam("where", where));
    }
}

We have an SQL script that generates the HiloPrimaryKeys table and seeds it with data which gets run during deployment. This is working fine.

I'm now trying to write unit tests to verify our persistence layer, ideally using SQLite in memory configuration for speed. This is how I configure NH for the tests:

[SetUp]
public void SetupContext()
{
    config = new SQLiteConfiguration()
            .InMemory()
            .ShowSql()
            .Raw("hibernate.generate_statistics", "true");

    var nhConfig = Fluently.Configure()
            .Database(PersistenceConfigurer)
            .Mappings(mappings =>
                 mappings.FluentMappings.AddFromAssemblyOf<DocumentMap>()
            .Conventions.AddFromAssemblyOf<IdGenerationConvention>());

    SessionSource = new SessionSource(nhConfig);
    Session = SessionSource.CreateSession();
    SessionSource.BuildSchema(Session);
}

The problem is I don't know how to tell NHibernate about our deployment script so that it generates the correct schema and seed data during tests.

The specific problem I get is when running the following PersistenceSpecification test:

[Test]
public void ShouldAddDocumentToDatabaseWithSimpleValues()
{
    new PersistenceSpecification<Document>(Session)
            .CheckProperty(x => x.CreatedBy, "anonymous")
            .CheckProperty(x => x.CreatedOn, new DateTime(1954, 12, 23))
            .CheckProperty(x => x.Reference, "anonymous")
            .CheckProperty(x => x.IsMigrated, true)
            .CheckReference(x => x.DocumentType, documentType)
            .VerifyTheMappings();
}

Which throws the following exception:

TestCase ... failed: 
Execute
NHibernate.Exceptions.GenericADOException: 
        could not get or update next value[SQL: ] 
        ---> System.Data.SQLite.SQLiteException: SQLite error
        no such column: TableKey

So my deduction is that it hasn't run the deployment script when checking the persistence spec.

Is there an existing solution to this situation? My Google-fu seems to have deserted me on this one.

Answer 1

As Brian said, you can run the deployment script after the schema is built. This code works well for me:

var config = new SQLiteConfiguration()
        .InMemory()
        .ShowSql()
        .Raw("hibernate.generate_statistics", "true");

var nhConfig = Fluently.Configure()
        .Database(config)
        .Mappings(mappings =>
             mappings.FluentMappings.AddFromAssemblyOf<DocumentMap>()
        .Conventions.AddFromAssemblyOf<IdGenerationConvention>());

var SessionSource = new SessionSource(nhConfig);
var Session = SessionSource.CreateSession();
SessionSource.BuildSchema(Session);

// run the deployment script
var deploymentScriptQuery = Session.CreateSQLQuery("ALTER TABLE HiloPrimaryKeys ADD COLUMN TableKey VARCHAR(255); INSERT INTO HiloPrimaryKeys (TableKey, NextHighValue) values ('Document', 1);");
deploymentScriptQuery.ExecuteUpdate();

The deployment script could be loaded from file etc...

Building FNH configuration and database schema is time consuming action. Execution of test suit will take unacceptable amount of time if the count of tests that are using the schema grows and the schema and the configuration are built by each test class. Both configuration and schema should be shared between all tests. Here is how to achieve that without loosing test isolation.

EDIT: If more than one session instance is required in test then connection pooling should be turned on, or both sessions should be created via the same connection. Details here...