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 like this:

<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 posting on the rhino-tools-dev group where they talk about just removing the schema from the mapping, 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. For example, this doesn't 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 the [dbo] (I tried attach database :memory: as dbo, but this didn't seem to help)?
• Alternatively, can I programmatically remove it from the classmappings (unfortunately changing the hbms is not possible right now)?

Lucent

Answer 1

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.

Commingle

Of course we would run the tests on Mssql on the Buildserver, on the Dev-pcs we would like to run with sqlite for perfomance-reasons. That it discards the DB is a bonus. We work mostly through nhibernate so the lack of complex operators would only affect us if we want complex named queries, or ? On another note : How is the performance of mssql-express ? can i create easily throw-away dbs ? – Lucent

SqlLite does not discard the database when the session is closed, but when the connection is closed. You need to share one single connection over all sessions of a test. We can switch tests from sqlite to sqlserver by switching the build configuration. You could also have conditional tests that only run on sqlserver to cover the last 1%. – Volar

with <br> properties.Add("connection.release_mode", "on_close"); <br> NH closes the connection only when the session is closed from the first article : " The fix ended up being quite simple though, I just needed to set my "connection.release_mode" setting to "on_close" which according to the nHibernate docs is "left for backward compatibility, but its use is highly discouraged". Well, I hope that they don't remove this in a future version because it will cause a few issues with those of us who are using in-memory databases for testing purposes." Do you have another solution ? – Lucent

Have a look inside rhino tools or fluent nhibernate for strategies to deal with the single connection problem in sqlite. The most common technique is to use a static var to hold on to the connection. – Raye

Nenad, Neal, that is the least of my worries. My biggest problem is incompatibilities and other differences that undermine credibility of unit-testing altogether. – Commingle

Answer 2

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.

Volar

Is there a way to add the default schema through code or can i just do this through the xml-file ? – Lucent

You can set all configurations in the code, before you create the session factory. Configuration.SetProperty("default_catalog", value); – Volar

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 !

Lucent