Fluent nHibernate, Hi-Lo table with entity-per-row

Question

Is there a way to specify a table to use for Hi-Lo values, with each entity having a per-row entry, via a convention (while still having nHibernate create the table structure for you)? I would like to replicate what Phil Haydon blogged about here, but without having to manually manage the table. As it stands, migrating his row-per-table code to its own convention will work only if you've already created the appropriate entries for 'TableKey' in the table already.

Alternatively, is this possible via the XML mappings?

And if all else fails, is the only other appropriate option to use a custom generator, a la this post?

Answer 1

For users of Fluent NHibernate, Anthony Dewhirst has posted a nice solution over here: http://www.anthonydewhirst.blogspot.co.uk/2012/02/fluent-nhibernate-solution-to-enable.html

Answer 2

Fabio Maulo talked about this in one of his mapping-by-code posts.

Mapping by code example:

mapper.BeforeMapClass += (mi, type, map) =>
    map.Id(idmap => idmap.Generator(Generators.HighLow,
        gmap => gmap.Params(new
        {
            table = "NextHighValues",
            column = "NextHigh",
            max_lo = 100,
            where = string.Format(
                "EntityName = '{0}'", type.Name.ToLowerInvariant())
        })));

For FluentNHibernate, you could do something like:

public class PrimaryKeyConvention : IIdConvention
{
    public void Apply(IIdentityInstance instance)
    {
        var type = instance.EntityType.Name;
        instance.Column(type + "Id");
        instance.GeneratedBy.HiLo(type, "NextHigh", "100", 
            x => x.AddParam("where", String.Format("EntityName = '{0}'", type));
    }
}

Also, Fabio explained how you could use IAuxiliaryDatabaseObject to create Hi-Lo script.

private static IAuxiliaryDatabaseObject CreateHighLowScript(
    IModelInspector inspector, IEnumerable<Type> entities)
{
    var script = new StringBuilder(3072);
    script.AppendLine("DELETE FROM NextHighValues;");
    script.AppendLine(
        "ALTER TABLE NextHighValues ADD EntityName VARCHAR(128) NOT NULL;");
    script.AppendLine(
        "CREATE NONCLUSTERED INDEX IdxNextHighValuesEntity ON NextHighValues " 
        + "(EntityName ASC);");
    script.AppendLine("GO");

    foreach (var entity in entities.Where(x => inspector.IsRootEntity(x)))
    {
        script.AppendLine(string.Format(
         "INSERT INTO [NextHighValues] (EntityName, NextHigh) VALUES ('{0}',1);",
         entity.Name.ToLowerInvariant()));
    }

    return new SimpleAuxiliaryDatabaseObject(
        script.ToString(), null, new HashedSet<string> {
           typeof(MsSql2005Dialect).FullName, typeof(MsSql2008Dialect).FullName 
        });
}

You would use it like this:

configuration.AddAuxiliaryDatabaseObject(CreateHighLowScript(
    modelInspector, Assembly.GetExecutingAssembly().GetExportedTypes()));

Answer 3

Building off of Anthony Dewhirst's already excellent solution, I ended up with the following, which adds a couple improvements:

• Adds Acceptance Criteria so that it doesn't try to handle non-integral Id types (e.g. Guid) and won't stomp on Id mappings which have a generator explicitly set
• Script generation takes Dialect into consideration

public class HiLoIdGeneratorConvention : IIdConvention, IIdConventionAcceptance
{
    public const string EntityColumnName = "entity";
    public const string MaxLo = "500";

    public void Accept(IAcceptanceCriteria<IIdentityInspector> criteria)
    {
        criteria
            .Expect(x => x.Type == typeof(int) || x.Type == typeof(uint) || x.Type == typeof(long) || x.Type == typeof(ulong)) // HiLo only works with integral types
            .Expect(x => x.Generator.EntityType == null); // Specific generator has not been mapped
    }

    public void Apply(IIdentityInstance instance)
    {
        instance.GeneratedBy.HiLo(TableGenerator.DefaultTableName, TableGenerator.DefaultColumnName, MaxLo,
                                  builder => builder.AddParam(TableGenerator.Where, string.Format("{0} = '{1}'", EntityColumnName, instance.EntityType.FullName)));
    }

    public static void CreateHighLowScript(NHibernate.Cfg.Configuration config)
    {
        var dialect = Activator.CreateInstance(Type.GetType(config.GetProperty(NHibernate.Cfg.Environment.Dialect))) as Dialect;
        var script = new StringBuilder();

        script.AppendFormat("DELETE FROM {0};", TableGenerator.DefaultTableName);
        script.AppendLine();
        script.AppendFormat("ALTER TABLE {0} {1} {2} {3} NOT NULL;", TableGenerator.DefaultTableName, dialect.AddColumnString, EntityColumnName, dialect.GetTypeName(SqlTypeFactory.GetAnsiString(128)));
        script.AppendLine();
        script.AppendFormat("CREATE NONCLUSTERED INDEX IX_{0}_{1} ON {0} ({1} ASC);", TableGenerator.DefaultTableName, EntityColumnName);
        script.AppendLine();
        if (dialect.SupportsSqlBatches)
        {
            script.AppendLine("GO");
            script.AppendLine();
        }
        foreach (var entityName in config.ClassMappings.Select(m => m.EntityName).Distinct())
        {
            script.AppendFormat("INSERT INTO [{0}] ({1}, {2}) VALUES ('{3}',1);", TableGenerator.DefaultTableName, EntityColumnName, TableGenerator.DefaultColumnName, entityName);
            script.AppendLine();
        }
        if (dialect.SupportsSqlBatches)
        {
            script.AppendLine("GO");
            script.AppendLine();
        }

        config.AddAuxiliaryDatabaseObject(new SimpleAuxiliaryDatabaseObject(script.ToString(), null));
    }
}