Using Sqlite InMemory DB for unittesting MSSQL-DB

Question

Hallo,

i am trying to implent this solution NHibernate-20-SQLite-and-In-Memory-Databases

the only problem is that we have hbm's like this

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

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

i found also this posting on the rhino-tools-dev group where they talk about just removing the schema from the mapping:
link to discussion

but on NH2 there doesn't seem to be a

classMapping.Schema

there is a

classMapping.Table.Schema

but it seems to be read-only.

e.g.

 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 the "dbo."
  (i tried "attach datbase :memory: as dbo" but this didn't seem to help)
  or
  
• can i programmatically remove it from the classmappings ?
  (unfortunately changing the hbm's is not possible right now)

Answer 1

We are using Sqlite to run unit tests with NH 2.0.1. Actually, I didn't run into this problem. I just didn't specify dbo, I think it is default on SqlServer.

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

Answer 2

We had too many problems with SQLite which eventually pushed us to switch to SQL Express. Problems I remember:

1. SQLite, when used in-memory, discards the database when Session is closed
2. SQLite does not support bunch of SQL constructs such basic ones as ISNULL, but also more advanced like common table expressions and others added in SQL 2005 and 2008. This becomes important when you start writing complex named queries.
3. SQLite's datetime has bigger range of possible values than SQL Server's
4. The API NHibernate uses for SQLite behaves differently than ADO.NET for MS SQL Server when used in scope of transaction. One example is the hbm-to-ddl tool whose Execute method does not work inside transaction with SQL Server but works fine with SQLite.

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

Answer 3

After looking through the source of NH and some experimenting i think i found a simple workaround -

    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 while an empty string threw an exception ...

thanks for the answers !