Using schemas with SQLite for in-memory NHibernate tests

We have been using NHibernate and using SQLite for in-memory tests which has worked very well for us but hit an issue when using schemas.

We wanted to separate out the tables used by our services through the use of schemas as schemas provides a very nice clean logical separation. However, although NHibernate supports schemas through the schema attribute on your class mappings, our SQLite tests fail as SQLite doesn’t support schemas.

We looked into ways around this. Our first port of call was to look at whether their is a way of managing this through our own NHibernate dialect class but our investigations led us to a dead end – although we plan on revisiting this later.

So we switched to looking at the SQLite ADO.NET provider. We figured that if we wrapped this then we could intercept the calls to it and override the “schema.table” requests into “schema_table”.

To achieve this we provide our own implementations of the following:

• IDbCommand
• IDbConnection
• DbParameter
• DbParameterCollection
• IDbTransaction

Within each of these implementations we wrap the underlying System.Data.SQLite versions of these to delegate down to. Within the command itself we parse the provided CommandText and detect if a schema is used, overriding this when necessary.

The final piece of the puzzle is to provide our own NHibernate driver which will tell NHibenate to use our implementation of IDbCommand. We couldn’t just inherit from the SQLite20Driver class so we had to copy its implementation as provided below:

using NHibernate.Driver;

namespace TheAgileWorkshop.SQLiteWithSchemaSupport
{
    public class SQLiteDriver : ReflectionBasedDriver
    {
        public SQLiteDriver()
            : base(
              "TheAgileWorkshop.SQLiteWithSchemaSupport",
              "TheAgileWorkshop.SQLiteWithSchemaSupport.SQLiteWithSchemaConnection",
              "TheAgileWorkshop.SQLiteWithSchemaSupport.SQLiteWithSchemaCommand")
        {
        }

        public override bool UseNamedPrefixInSql
        {
            get { return true; }
        }

        public override bool UseNamedPrefixInParameter
        {
            get { return true; }
        }

        public override string NamedPrefix
        {
            get { return "@"; }
        }

        public override bool SupportsMultipleOpenReaders
        {
            get { return false; }
        }

        public override bool SupportsMultipleQueries
        {
            get { return true; }
        }
    }
}

 

Now to use schemas with NHibernate and keep your in-memory SQLite tests passing, reference the TheAgileWorkshop.SQLiteWithSchemaSupport.dll and specify this driver class (TheAgileWorkshop.SQLiteWithSchemaSupport.SQLiteDriver) in place of the SQLite20Driver class that exists at the moment.

To use this, you can get the source or grab a copy of a release build of the dll directly.

I hope this helps others. If there are any issues then please don’t hesitate to let me know.

UPDATE: We have updated the release build dll link with a version that supports ‘.’ in the parameter values (such as a decimal property) based on MacX’s comments.