Using MbUnit3's [Rollback] for unit testing NHibernate interactions with SQLite

Question

Background:

My team is dedicated to ensuring that straight from checkout, our code compiles and unit tests run successfully. To facilitate this and test some of our NHibernate mappings, we've added a SQLite DB to our repository which is a mirror of our production SQL Server 2005 database. We're using the latest versions of: MbUnit3 (part of Gallio), System.Data.SQLite and NHibernate.

Problem:

I've discovered that the following unit test does not work with SQLite, despite executing without trouble against SQL Server 2005.

    [Test]
    [Rollback]
    public void CompleteCanPersistNode()
    {
        // returns a Configuration for either SQLite or SQL Server 2005 depending on how the project is configured.
        Configuration config = GetDbConfig(); 

        ISessionFactory sessionFactory = config.BuildSessionFactory();
        ISession session = sessionFactory.OpenSession();

        Node node = new Node();
        node.Name = "Test Node";
        node.PhysicalNodeType = session.Get<NodeType>(1);

        // SQLite fails with the exception below after the next line called.
        node.NodeLocation = session.Get<NodeLocation>(2);

        session.Save(node);
        session.Flush();

        Assert.AreNotEqual(-1, node.NodeID);
        Assert.IsNotNull(session.Get<Node>(node.NodeID));
    }

The exception I'm getting (ONLY when working with SQLite) follows:

NHibernate.ADOException: cannot open connection --->
System.Data.SQLite.SQLiteException:
  The database file is locked database is locked
    at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
    at System.Data.SQLite.SQLiteDataReader.NextResult()
    at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
    at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
    at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
    at System.Data.SQLite.SQLiteTransaction..ctor(SQLiteConnection connection, Boolean deferredLock)
    at System.Data.SQLite.SQLiteConnection.BeginDbTransaction(IsolationLevel isolationLevel)
    at System.Data.SQLite.SQLiteConnection.BeginTransaction()
    at System.Data.SQLite.SQLiteEnlistment..ctor(SQLiteConnection cnn, Transaction scope)
    at System.Data.SQLite.SQLiteConnection.EnlistTransaction(Transaction transaction)
    at System.Data.SQLite.SQLiteConnection.Open()
    at NHibernate.Connection.DriverConnectionProvider.GetConnection()
    at NHibernate.Impl.SessionFactoryImpl.OpenConnection()
    --- End of inner exception stack trace ---
    at NHibernate.Impl.SessionFactoryImpl.OpenConnection()
    at NHibernate.AdoNet.ConnectionManager.GetConnection()
    at NHibernate.AdoNet.AbstractBatcher.Prepare(IDbCommand cmd)
    at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)
    at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session)
    at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
    at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
    at NHibernate.Loader.Loader.LoadEntity(ISessionImplementor session, Object id, IType identifierType, Object optionalObject, String optionalEntityName, Object optionalIdentifier, IEntityPersister persister)
    at NHibernate.Loader.Entity.AbstractEntityLoader.Load(ISessionImplementor session, Object id, Object optionalObject, Object optionalId)
    at NHibernate.Loader.Entity.AbstractEntityLoader.Load(Object id, Object optionalObject, ISessionImplementor session)
    at NHibernate.Persister.Entity.AbstractEntityPersister.Load(Object id, Object optionalObject, LockMode lockMode, ISessionImplementor session)
    at NHibernate.Event.Default.DefaultLoadEventListener.LoadFromDatasource(LoadEvent event, IEntityPersister persister, EntityKey keyToLoad, LoadType options)
    at NHibernate.Event.Default.DefaultLoadEventListener.DoLoad(LoadEvent event, IEntityPersister persister, EntityKey keyToLoad, LoadType options)
    at NHibernate.Event.Default.DefaultLoadEventListener.Load(LoadEvent event, IEntityPersister persister, EntityKey keyToLoad, LoadType options)
    at NHibernate.Event.Default.DefaultLoadEventListener.ProxyOrLoad(LoadEvent event, IEntityPersister persister, EntityKey keyToLoad, LoadType options)
    at NHibernate.Event.Default.DefaultLoadEventListener.OnLoad(LoadEvent event, LoadType loadType)
    at NHibernate.Impl.SessionImpl.FireLoad(LoadEvent event, LoadType loadType)
    at NHibernate.Impl.SessionImpl.Get(String entityName, Object id)
    at NHibernate.Impl.SessionImpl.Get(Type entityClass, Object id)
    at NHibernate.Impl.SessionImpl.Get[T](Object id)
D:\dev\598\Code\test\unit\DataAccess.Test\NHibernatePersistenceTests.cs

When SQLite is used and the [Rollback] attribute is NOT specified, the test also completes successfully.

Question:

Is this an issue with System.Data.SQLite's implementation of TransactionScope which MbUnit3 uses for [Rollback] or a limitation of the SQLite engine?

Is there some way to write this unit test, working against SQLite, that will rollback so as to avoid affecting the database each time the test is run?

Answer 1

Ditch [Rollback] and use NDbUnit. I use this myself for this exact scenario and it has been working great.

Answer 2

This is not a real answer to you question, but probably a solution to solve the problem.

I use an in-memory implementation of sql lite for my integration tests. I build up the schema and fill the database before each test. The schema creation and initial data filling happens really fast (less then 0.01 seconds per test) because it's an in-memory database.

Why do you use a physical database?

Edit: response to answer about question above:

1.) Because I migrated my schema and data directly from SQL Server 2005 and I want it to persist in source control.

• I recommend to store a file with the database schema in and a file or script that creates the sample data in source control. You can generate the file using sql server studion management express, you can generate it from your NHibernate mappings or you can use a tool like sql compare and you can probably find other solutions for this when you need it. Plain text files are stored easier in version control systems then complete binary database files.

2.) Does something about the in-memory SQLite engine differ such that it would resolve this difficulty?

• It might solve your problems because you can recreate your database before each test. Your database under test will be in a the state you expect it to be before each test is executed. A benefit of that is there is no need to roll back your transactions, but I have run similar test with in memory sqllite and it worked as aspected.

Answer 3

Check if you're not missing connection.release_mode=on_close in your SQLite NHibernate configuration. (reference docs)

BTW: always dispose your ISession and ISessionFactory.