JasonDentler.com

This is a follow-up to my post here about SQLiteDatabaseScope, a small class to control the lifecycle of SQLite in-memory databases. It allows you to run NHibernate tests against SQLite’s fast in-memory database. Since each test can have its own database in memory, you can easily run tests in parallel without conflict.

Warning: While SQLite is thread-safe, SQLiteDatabaseScope itself is not. It is intended to be used to manage one or more SQLite in-memory databases per thread, not shared across threads.

Since I originally wrote SQLiteDatabaseScope, I’ve learned a few things about SQLite and NHibernate, and updated the code to match.

First, building an NHibernate configuration and session factory is heavy. It’s a huge waste of time to rebuild those objects for each new instance of the database. Instead, you supply your own configuration and session factory.

Second, it’s possible to create an in-memory database with initial data. A few people I follow on Twitter mentioned giving up on SQLite because they couldn’t quickly and easily fill the in-memory database with necessary initial test data. I’ve found a solution. We attach a SQLite file database containing our data, perform an “INSERT INTO tbl SELECT * FROM attached.tbl” command for each table, then detach our file database. The import runs fast, and the file database can be safely accessed by multiple threads simultaneously, so we can still run our tests in parallel. Better yet, since SQLite doesn’t support foreign keys, we don’t have to be careful about the table order when we import.

Here’s the code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;

namespace System.Data.SQLite
{
    public class SQLiteDatabaseScope : IDisposable 
    {

        private const string CONNECTION_STRING = "Data Source=:memory:;Version=3;New=True;";

        private static readonly log4net.ILog Log = log4net.LogManager.GetLogger(typeof(SQLiteDatabaseScope));

        private object sync = new object();
        private NHibernate.Cfg.Configuration config;
        private NHibernate.ISessionFactory sessionFactory;
        private string initialDataFilename;
        private SQLiteConnection connection;

        public SQLiteDatabaseScope(NHibernate.Cfg.Configuration Configuration,
            NHibernate.ISessionFactory SessionFactory)
        {
            Log.Info("Creating database scope");
            config = Configuration;
            sessionFactory = SessionFactory;
        }

        public SQLiteDatabaseScope(NHibernate.Cfg.Configuration Configuration,
            NHibernate.ISessionFactory SessionFactory,
            string InitialDataFilename)
            : this(Configuration, SessionFactory)
        {
            initialDataFilename = InitialDataFilename;
        }

        public NHibernate.ISession OpenSession()
        {
            return sessionFactory.OpenSession(GetConnection());
        }

        public NHibernate.ISession OpenSession(NHibernate.IInterceptor Interceptor)
        {
            return sessionFactory.OpenSession(GetConnection(), Interceptor);
        }

        public NHibernate.IStatelessSession OpenStatelessSession()
        {
            return sessionFactory.OpenStatelessSession(GetConnection());
        }

        private SQLiteConnection GetConnection()
        {
            if (null == connection)
                BuildConnection();
            return connection;
        }

        private void BuildConnection()
        {
            Log.Info("Building SQLite database connection");
            connection = new SQLiteConnection(CONNECTION_STRING);
            connection.Open();
            BuildSchema();
            if (!string.IsNullOrEmpty(initialDataFilename))
                new SQLiteDataLoader(connection, initialDataFilename).ImportData();
        }

        private void BuildSchema()
        {
            Log.Debug("Creating schema");
            NHibernate.Tool.hbm2ddl.SchemaExport se;
            se = new NHibernate.Tool.hbm2ddl.SchemaExport(config);
            se.Execute(false, true, false, connection, null);
        }

        private bool disposedValue = false;

        protected void Dispose(bool disposing)
        {
            if (!this.disposedValue)
            {
                if (disposing)
                {
                    Log.Info("Disposing database scope.");
                    if (null != connection)
                    {
                        connection.Dispose();
                    }
                }
            }
            this.disposedValue=true;
        }

        #region IDisposable Members

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        #endregion
    }
}

using System;
using System.Linq;
using System.Data;
using System.Data.SQLite;

namespace System.Data.SQLite
{
    public class SQLiteDataLoader
    {

        private static log4net.ILog Log = log4net.LogManager.GetLogger(typeof(SQLiteDataLoader));

        private const string ATTACHED_DB = "zxcvbnmInitialData";

        private SQLiteConnection connection;
        private string initialDataFilename;

        public SQLiteDataLoader(SQLiteConnection Connection, 
            string InitialDataFilename)
        {
            connection = Connection;
            initialDataFilename = InitialDataFilename;
        }

        public void ImportData()
        {
            DataTable dt = connection.GetSchema(SQLiteMetaDataCollectionNames.Tables);
            var tableNames = (from DataRow R in dt.Rows
                             select (string)R["TABLE_NAME"]).ToArray();
            AttachDatabase();
            foreach (string tableName in tableNames)
            {
                CopyTableData(tableName);
            }
            DetachDatabase();
        }

        private void AttachDatabase()
        {
            SQLiteCommand cmd = new SQLiteCommand(connection);
            cmd.CommandText = String.Format("ATTACH '{0}' AS {1}", initialDataFilename, ATTACHED_DB);
            Log.Debug(cmd.CommandText);
            cmd.ExecuteNonQuery();
        }

        private void DetachDatabase()
        {
            SQLiteCommand cmd = new SQLiteCommand(connection);
            cmd.CommandText = string.Format("DETACH {0}", ATTACHED_DB);
            Log.Debug(cmd.CommandText);
            cmd.ExecuteNonQuery();
        }

        private void CopyTableData(string TableName)
        {
            int rowsAffected;
            SQLiteCommand cmd = new SQLiteCommand(connection);
            cmd.CommandText = string.Format("INSERT INTO {0} SELECT * FROM {1}.{0}", TableName, ATTACHED_DB );
            Log.Debug(cmd.CommandText);
            rowsAffected = cmd.ExecuteNonQuery();
            Log.InfoFormat("{0} {1} rows loaded", rowsAffected, TableName);
        }

    }
}

Jason

- We now return you to your regularly scheduled program.