Formula Mapping in NHibernate to Functions in SQLi

Question

i found the following article http://ayende.com/Blog/archive/2009/04/28/nhibernate-unit-testing.aspx to help me unit test my NHibernate application. Most of my tests are working fine but a strange error has been thrown. From looking at the SQL generated i believe the following fluent mapping is causing the problems:

Map(x => x.IsValid).Formula("CASE WHEN dbo.NumPosts(UserID) = 5 THEN 1 ELSE 0 END");

You'll notice the call to dbo.NumPosts which is a user defined function within my database. I know there's other ways i could've mapped this property but this is just used as an example. Basically i need to know how to map this using SQLite.

Edit:

After further thoughts would, is it possible to override the mapping for this field in my unit testing project? Here's my current configuration:

private static ISessionFactory CreateSessionFactory() {
    return Fluently.Configure()
        .Database(SQLiteConfiguration.Standard.InMemory().ShowSql())
        .Mappings(m => m.FluentMappings
            .AddFromAssembly(typeof(Role).Assembly)
            .Conventions.AddFromAssemblyOf<EnumConvention>())
        .ExposeConfiguration(c => _configuration = c)
        .BuildSessionFactory();
}

I don't wish to re-define all the mappings as this would take some time and would become un-maintainable.

I'd appreciate the help. Thanks

Answer 1

Problem solved! I was able to say:

[SQLiteFunction(Name = "NumPosts", Arguments = 1, FuncType = FunctionType.Scalar)]
public class NumPosts : SQLiteFunction {
    public override object Invoke(object[] args) {
        ...
    }
}

Now all i had to do was add a setting to add the dbo prefix infront of my functions. This is then set to blank in the test project.

Answer 2

In reference to the answer in your comment, I was able to get a static bool property to work.

Still I didn't like relying on a global bool variable so I decided to dig further. In NHibernate 3.0 they added events to the Configuration object. In particular I was able to take advantage of the new BeforeBind event on the Configuration object.

I rewrote slightly your CreateSessionFactory() method to show how I did it.

NOTE: In my formula's I always write them as dbo.NumPosts, and the event handler will remove the 'dbo.' if we are running within a SQLite Dialect.

private static ISessionFactory CreateSessionFactory()
{
    return Fluently.Configure()
        .Database(SQLiteConfiguration.Standard.InMemory().ShowSql())
        .Mappings(m => m.FluentMappings
                           .AddFromAssembly(typeof (Role).Assembly)
                           .Conventions.AddFromAssemblyOf<EnumConvention>())
        .ExposeConfiguration(c =>
                                 {
                                     c.BeforeBindMapping += BeforeBindMappingHandler;
                                     _configuration = c;
                                 })
        .BuildConfiguration()
        .BuildSessionFactory();
}

private static void BeforeBindMappingHandler(object sender, BindMappingEventArgs e)
{
    if (!(e.Dialect is SQLiteDialect)) return;

    var properties = e.Mapping.RootClasses
        .SelectMany(r => r.Properties)
        .Where(p => p is HbmProperty)
        .Cast<HbmProperty>()
        .Where(p => p.Formulas.Any());

    foreach (var hbmProperty in properties)
        hbmProperty.formula = hbmProperty.formula.ToLower().Replace("dbo.", "");
}