In memory SQLite database for PersistenceSpecification testing.

Hi,

I've encountered a problem while trying to use SQLite test my mappings. First I have two pretty standard mappings:

public interface IEntity
{

long Id { get; }

}

public class Model : IEntity
{

protected Model()
    {
    }

    public Model(Family family)
    {
        Throw
            .An<ArgumentNullException>("family")
            .If(() => family == null);

        Family = family;
    }

public virtual long Id { get; set; }
    public virtual string Name { get; set; }
    public virtual Family Family { get; set; }

}

public class Family : IEntity
{

    public virtual long Id { get; set; }
    public virtual string Name { get; set; }
    public virtual string Description { get; set; }

}

public sealed class ModelMap : ClassMap
{

public ModelMap()
{
    Id( x => x.Id );
    Map( x => x.Name )
        .Length( 50 )
        .Not.Nullable();
    References( x => x.Family )
        .Column( "FamilyId" )
        .Cascade.None()
        .Not.Nullable();
}

}

public sealed class FamilyMap : ClassMap
{

public FamilyMap()
{
    Id( x => x.Id );
    Map( x => x.Name )
        .Length( 50 )
        .Not.Nullable();
    Map( x => x.Description )
        .Length( 256 );
}

}

In order to test these mappings I need to insert a family into the database before testing the model mapping. This is ok because the family is Cascade.None() which is the behavior I want. However using an in memory SQLite database I get the following error message:

Test method UnitTests.ModelMapTests.CanMapModel threw exception: NHibernate.Exceptions.GenericADOException: could not insert: [Core.Family][SQL: INSERT INTO "Family" (Name, Description) VALUES (?, ?); select last_insert_rowid()] ---> System.Data.SQLite.SQLiteException: SQLite error
no such table: Family.

The schema export produced this output:

drop table if exists "Model"
drop table if exists "Family"
create table "Model" (Id integer, Name TEXT not null, FamilyId INTEGER not null, primary key (Id))
create table "Family" (Id integer, Name TEXT not null, Description TEXT, primary key (Id))

Im not a SQLite expert, but this seems ok to me... I've changed the name of the table in the mapping, but this did not help.

I also switched the test to use my Sql2008 configuration and it works perfectly. Is this a SQLite issue? or something strange with Fluent Nhibernate? Any suggestions? I really dont want to have to do all of my persistence specification testing against a real database... well not until I do an intergration / acceptance test anyhow...

The code for the test:

[TestMethod] public void CanMapModel()
{

using( var session = Setup.InMemoryDatabase().OpenSession() )
{
    var family = new Family
                     {
                         Name = "FamilyName", 
                               Description = "FamilyDescription", 
                     };

        // Works fine in Sql2008 configuration, fails in SQLite configuration.
        session.SaveOrUpdate(family);

        new PersistenceSpecification<Model>(session, new EqualityComparer()).
                CheckProperty(c => c.Id, 1L). // The L is required otherwise CheckProperty will fail because it expects the Id to be in Int32, not Int64...
                CheckProperty(c => c.Name, "Name").
                CheckProperty(c => c.Description, "Description").
                CheckProperty(c => c.Family, family).
                VerifyTheMappings();
}

}

Cheers,

Robert