Database integration tests with SQLite in-memory databases

As described in the Unit testing with Genome article, testing applications that use Genome for data access can be done either with unit tests or with database integration tests (in a unit test form).

One of the main problems with writing database integration tests is that the execution of these tests is relatively slow. From version 4.2, Genome provides integration with SQLite databases, and since SQLite supports in-memory databases, we have investigated how this could be used to increase the performance of the database integration tests, even if the application being tested does not use SQLite database to store its data.

Just like with normal unit tests, database integration tests have to be independent and isolated. However, with database integration tests it is much harder to achieve this, as tests might change or even corrupt the database during their execution. Also, usually the applications require some base-line data to run at all (e.g. configuration table entries, lookup tables or well-known objects in the database). These data has to be ensured for all tests to run successfully. To solve these problems, usually database integration tests

·    Reset the database (clean up all tables, or destroy/re-create database)

·    Insert base-line data

·    Perform the operation that is being tested

As it can be seen from the task to be performed, executing a database integration test might require significant database activity, which can slow down the execution. There are possibilities to optimize this process (e.g. do not reset the database after read-only tests) but these require extra care and they can decrease the stability of the tests.

The DbIntegrationTest.SQLite sample ({Genome installation folder}\Samples\CSharp\DbIntegrationTest.SQLite) shows how database integration tests can be executed in different ways. The sample contains a simple database integration test that is executed 100 times with different execution options. Using this as a benchmark, the following performance can be measured on an average (Intel Core2, 1.86 MHz, 4GB RAM) developer machine:

Solution

Database platform

Execution time (seconds)

Recreate tables and insert base-line data

MS SQL Server

33

SQLite (file-based)

35

SQLite (in-memory)

7

Restore base-line database from backup

MS SQL Server

143

SQLite

14

As you can see from this summary, using SQLite in-memory database the performance can be increased dramatically (almost 5 times).

Speed up the test with in-memory databases

As mentioned above, Genome can be used with SQLite in-memory databases. Though SQLite is a simple database engine that is mainly used by desktop applications, it has rich querying capabilities and supports almost everything that works with Microsoft SQL Server or Oracle database servers. (Of course SQLite is absolutely not comparable with these database engines when discussing parallel access or scalability, but to execute a database integration test, this is usually not important at all.)

Genome provides an abstract interface for enabling data access. The operations one can perform with Genome are usually database-independent. This means that the application logic that was implemented for SQL Server or Oracle, most probably will also work with SQLite as well. For the most of the cases, you don’t even have to change anything in the mapping files or in the code. Genome encapsulated the data access implementation in the DataDomain schema assembly that is produced by the DataDomain schema project during compilation. The schema also encapsulates the specific details of the database platform, so it is platform dependent. On the other hand, the applications does not depend statically on the schema, but they load the schema assembly dynamically using the DataDomainSchema.LoadFrom() method.

Although the Visual Studio integration of the Genome projects allows you to select only a single database provider (e.g. SQL Server 2005), the MsBuild project file can be configured to compile a DataDomain schema for multiple database platforms. To execute the database integration tests with SQLite, the schema project has to be configured to compile a schema for the main database platform (e.g. SQL Server 2005) and also to SQLite. In most of the cases, the same mapping should compile for both platforms. If the mapping contains database-specific mapping features (e.g. stored procedures), this can be mapped in an alternative way for SQLite using the <PlatformSpecifc> element. It is recommended to name the schema assemblies for the different platforms also differently, e.g. GenomeShop.Schema.dll and GenomeShop.Schema.SQLite.dll.

You can read more about how the SQLite in-memory database support works with Genome here.

Setting up a solution to run the integration tests with SQLite

To setup the solution to compile an additional DataDomain schema for SQLite, you have to modify two project files in the solution.

Changes in the schema project

1. You have to add a reference to the TechTalk.Genome.SQLiteProvider assembly located in the Bin folder of the Genome installation. Although this reference is required only for the SQLite compilation, it does not have any negative side-effects for the normal compilation.

2. The project file of the DataDomain schema assembly has to be modified with a text editor tool (e.g. notepad). You have to add a conditional property group that contains the settings for the SQLite schema compilation. This property group can be placed anywhere in the file, e.g. after the other conditional (Debug, Release, etc.) property groups. The property group should look like the following (the assembly name (GenomeShop.Schema.SQLite) should be customized for your own project):

  <PropertyGroup Condition=" '$(SQLiteConfig)' == 'true' ">

    <AssemblyName>GenomeShop.Schema.SQLite</AssemblyName>

    <OutputPath>bin\SQLite\</OutputPath>

    <IntermediateOutputPath>obj\SQLite\</IntermediateOutputPath>

    <BuiltInSchemaFile>SQLite3.xml</BuiltInSchemaFile>

  </PropertyGroup>

Changes in the database integration test project

In our scenario, we have a unit test library (GenomeShop.Tests.DbIntegration) that contains the database integration tests. To provide optimized development performance, we have deferred the compilation of the SQLite schema until the point when the test project is compiled. With this optimization, you don’t have to wait for the compilation of both schemas when you develop the entity mappings or when you want to check for possible mapping errors. So in our case, we have triggered the SQLite schema compilation from the pre-build section of the test project.

If you have multiple test projects, you can apply these steps to all of the projects. If you compile the entire solution, only the first test project will compile the SQLite schema, the subsequent ones will skip the compilation as it is already up-to-date.

3. You have to add a reference to the System.Data.SQLite assembly, which is the .NET provider for SQLite 3. This assembly is not part of the Genome installation, but you can download it from http://sqlite.phxsoftware.com/. Genome was compiled and tested with version 1.0.65.0.

4. You have to add a reference to the TechTalk.Genome.SQLiteProvider assembly located in the Bin folder of the Genome installation.

5. The test project file has to be modified with a text editor tool (e.g. notepad). You have to add the following target to the project file, e.g. to the end of the file. The schema assembly name and the path of the schema project file have to be customized for your own project.

  <Target Name="BeforeBuild">

    <MSBuild Projects="$(SolutionDir)GenomeShop.Schema\GenomeShop.Schema.ddsproj" Properties="Configuration=Debug;SQLiteConfig=true" />

    <ItemGroup>

      <SQLiteSchemaFiles Include="$(SolutionDir)GenomeShop.Schema\bin\SQLite\GenomeShop.Schema.SQLite.*" />

    </ItemGroup>

    <Copy SourceFiles="@(SQLiteSchemaFiles)" DestinationFolder="$(TargetDir)" />

  </Target>

Loading the different schema projects dynamically

As mentioned above, Genome loads the DataDomain schema dynamically using the DataDomainSchema.Load() method. To load a different schema you just have to pass the assembly name of the SQLite schema to this method. It is also important to use a different connection string when testing with SQLite, as the connection strings of SQL Server and Oracle are not compatible with SQLite.

In the sample, we use the EnsureDataDomainInitialized() method, that takes the schema assembly name and the connection string as parameter. This way the tests and the real implementation can initialize the DataDomain differently.

public static void EnsureDataDomainInitialized(string schemaAssembly, string dataDomainRole, string connectionString)

{

    if (dataDomain != null)

        return;

 

    lock (dataDomainSynchRoot)

    {

        if (dataDomain != null)

            return;

 

        DataDomainConfiguration ddConfig = new DataDomainConfiguration();

        ddConfig.Schema = DataDomainSchema.LoadFrom(schemaAssembly);

        ddConfig.ConnectionString = connectionString;

        ddConfig.Role = dataDomainRole;

        ddConfig.LockServers.Add(typeof(object), PersistentOptimisticLockServer.Value);

 

        ddConfig.Namespaces.Add("TechTalk.Genome");

        ddConfig.Namespaces.Add("GenomeShop.Entities");

 

        DataDomain newDataDomain = new DataDomain(ddConfig);

        transformationProvider = new StaticTransformationProvider(newDataDomain);

 

        System.Threading.Thread.MemoryBarrier();

        dataDomain = newDataDomain;

    }

}

Initializing it for the real code:

string dataDomainRole = WebConfigurationManager.AppSettings["DataDomainRole"];

var connectionString = WebConfigurationManager.ConnectionStrings[dataDomainRole].ConnectionString;

EnsureDataDomainInitialized(SCHEMA_ASSEMBLY, dataDomainRole, connectionString);

And for testing with SQLite:

string dataDomainRole = ConfigurationManager.AppSettings["DataDomainRole"];

var connectionString = ConfigurationManager.ConnectionStrings[dataDomainRole + ".SQLiteInMemory"].ConnectionString;

Helper.EnsureDataDomainInitialized("GenomeShop.Schema.SQLite", dataDomainRole, connectionString);

Based on these configuration ideas, you can also build up your own solution if the mentioned configuration does not fit entirely to your needs.