As nice as SQLite is for tests it is very simple database, and it does not have all the options ‘big’ databases provide (foreign key enforcement!). I don’t think there’s much you can do about this issue, but there’s more.
SQLite does not support all the mappings you can have. I bumped into this issue when working with mapping similar to described in this post. Basically when you try to create schema from this mapping:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="ConsoleApplication1"
assembly="ConsoleApplication1">
<class name="Invoice">
<id name="Id">
<generator class="guid.comb" />
</id>
<property name="InvoiceRtfString" type="StringClob">
<column name ="RtfText" sql-type="nvarchar(MAX)"/>
</property>
<property name="ScannedInvoiceJpg" type="BinaryBlob">
<column name ="JpgData" sql-type="varbinary(max)" />
</property>
</class>
</hibernate-mapping>
You’ll get an error:
SQLite error near “MAX”: syntax error
That’s because SQLite does not know how to handle nvarchar(max) or varbinary(max). It has its own types for that – TEXT and BLOB. So you’re either stuck, or have two sets of mappings – one for tests for SQLite, one for production, for SQL Server. But wait, there’s third option!
In the previous post, you may have noticed, that in DbTestsBase Init method I call:
new Remapper().Remap(configuration);
Remapper is my remedy for that problem. It’s a simple class that traverses the mapping looking for incompatibilities and adjusts it on the fly before creating session factory and schema. This lets you have single mapping (be it XML or FluentNHibernate) while still using SQLite for tests.
The class looks like this:
public class Remapper
{
private IDictionary<string, String> map = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
public Remapper()
{
map.Add("varbinary(max)", "BLOB");
map.Add("nvarchar(max)", "TEXT");
}
public void Remap(Configuration configuration)
{
foreach (var classMapping in configuration.ClassMappings)
{
this.Remap(classMapping.Table);
}
}
private void Remap(Table table)
{
if (table.Name.Contains("."))
{
// found on http://www.nablasoft.com/alkampfer/index.php/2009/07/24/manage-in-memory-nhibernate-test-with-sqlite-and-database-schema/
// this is a table with schema
table.Name = table.Name.Replace(".", "_");
}
foreach (var column in table.ColumnIterator)
{
this.Remap(column);
}
}
private void Remap(Column column)
{
if (string.IsNullOrEmpty(column.SqlType))
{
return;
}
string sqlType;
if (!this.map.TryGetValue(column.SqlType, out sqlType))
{
return;
}
column.SqlType = sqlType;
}
}
It is very simple and basic, because I created it for my very specific needs, but with little effort you could extend it to handle more incompatibilities.