Using Sqlite InMemory DB for unittesting MSSQL-DB

Question

I am trying to implement this solution: NHibernate-20-SQLite-and-In-Memory-Databases

The only problem is that we have hbms:

<class name="aTable" table="[dbo].[aTable]" mutable="true" lazy="false"> 

with [dbo] in the table name, because we are working with mssql, and this does not work with Sqlite.

I found this publication in the rhino-tools-dev group, where they talk about just removing the schema from the mapping, but classMapping.Schema does not exist on NH2.

There is classMapping.Table.Schema , but it seems to be read-only. For example, this does not work:

  foreach (PersistentClass cp in configuration.ClassMappings) { // Does not work - throws a //System.IndexOutOfRangeException: Index was outside the bounds of the array. cp.Table.Schema = ""; } 

• Is there a way to tell Sqlite to ignore [dbo] (I tried attach database :memory: as dbo , but that didn't seem to help)?
• Alternatively, can I programmatically remove it from class mappings (unfortunately, changing hbms is not possible now)?

Answer 1

We had too many problems with SQLite, which ultimately prompted us to switch to SQL Express. The problems that I remember:

• SQLite, when used in memory, discards the database when the session is closed.
• SQLite does not support a set of SQL constructs such as ISNULL, but also more advanced ones such as general table expressions and others added in SQL 2005 and 2008. This becomes important when you start writing complex named queries.
• SQLite datetime has a larger range of possible values ​​than SQL Server
• The NHibernate API for SQLite behaves differently than ADO.NET for MS SQL Server when used in a transaction. One example is the hbm-to-ddl tool, whose Execute method does not work inside a transaction with SQL Server, but works fine with SQLite.

To summarize, SQLite-based unit testing is very far from being conclusively representative of the problems you will encounter when using MS SQL Server in PROD, and therefore undermines the credibility of unit testing in general.

Answer 2

We use Sqlite to run unit tests with NH 2.0.1. In fact, I did not encounter this problem. I just did not specify dbo, I think this is the default on SqlServer.

By the way, there is a default_schema parameter in the configuration file. This is actually the name of the database, but you can try placing dbo there, only for SqlServer configuration, of course.

Answer 3

After looking at the NH source and some experiments, I think I found a simple way -

  foreach (PersistentClass cp in configuration.ClassMappings) { // Input : [dbo].[Tablename] Output : Tablename cp.Table.Name = Regex.Replace(cp.Table.Name, @"^\[.*\]\.\[", ""); cp.Table.Name = Regex.Replace(cp.Table.Name, @"\]$", ""); // just to be sure cp.Table.Schema = null; } 

Note that I can set Table.Schema to null, and an empty string is an exception ...

thanks for answers!