How to change the column order in a multi-column index using fluent-nhibernate?

Question

How to change the column order in a multi-column index?
I.e:

mapping.References(x => x.SomeReference).SetAttribute("index", "IX_index");
mapping.Map(x => x.SomeField).SetAttribute("index", "IX_index");

Produces the following Schema:

create index IX_index on ApplicantProgramDatas (SomeField, SomeReferenceId)

But I want to get:

create index IX_index on ApplicantProgramDatas (SomeReferenceId, SomeField)

Answer 1

You can define an index in NHibernate using <database-object> or IAuxiliaryDatabaseObject.

In a hbm.xml file:

<hibernate-mapping xmlns="urn:nhiernate-mapping-2.2">
  <database-object>
     <create>VALID SQL</create>
     <drop>VALID SQL</create>
  </database-object>
</hibernate-mapping>

N.B. <database-object> can go before or after a class mapping in the same hbm.xml file allowing you to keep your index definitions, triggers, etc. with the object to which they apply.

The other option is NHibernate.Mapping.IAuxiliaryDatabaseObject:

namespace NHibernate.Mapping {
    public interface IAuxiliaryDatabaseObject : IRelationalModel {
        void AddDialectScope(string dialectName);
        bool AppliesToDialect(Dialect dialect);
        void SetParameterValues(IDictionary<string, string> parameters);
    }
    public interface IRelationalModel {
        string SqlCreateString(Dialect dialect, IMapping p, string defaultCatalog, string defaultSchema);
        string SqlDropString(Dialect dialect, string defaultCatalog, string defaultSchema);
    }
}

Given that you're using Fluent NHibernate, IAuxiliaryDatabaseObject will probably work better for you. Just expose your configuration when building it and then call:

var sqlCreate = "CREATION SCRIPT";
var sqlDrop = "DROP SCRIPT";    
cfg.AddAuxiliaryDatabaseObject(new SimpleAuxiliaryDatabaseObject(sqlCreate, sqlDrop));

N.B. NHibernate.Mapping.SimpleAuxiliaryDatabaseObject is part of NHibernate. You don't have to write it yourself if all you need to do is supply create/drop scripts for a database object.

I took a quick look in the Fluent NHibernate codebase and didn't see any direct support for IAuxiliaryDatabaseObject. So it is a matter of exposing your configuration object and supplying all your IAuxiliaryDatabaseObjects yourself. It wouldn't be too difficult to write some code that scan through your mapping assembly looking for types that implement IAuxiliaryDatabaseObject and then foreach'ing over them to pass to cfg.AddAuxiliaryDatabaseObject(obj).

You can find more information about auxiliary database objects in the NHibernate docs:

http://nhibernate.info/doc/nh/en/index.html#mapping-database-object

Answer 2

Let me suggest to override SchemaExport. There is private field accessor via reflection, it requires full trust mode. If this approach does not suit your needs, consider rewriting SchemaExport (relatively light class)

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Reflection;
using System.Text;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using FluentNHibernate.Mapping;
using NHibernate.Cfg;
using NHibernate.Tool.hbm2ddl;

namespace FluentNHib
{

    public class Master
    {
        public int Id { get; set; }
    }

    public class Child
    {
        public int Id { get; set; }
        [MCIndex("A", 0)]
        public Master Master { get; set; }
        [MCIndex("A", 1)]
        public string Name { get; set; }
    }

    public class MCIndexAttribute : Attribute
    {
        public string indexName;
        public int indexOrder;

        public MCIndexAttribute(string indexName, int i)
        {
            this.indexName = indexName;
            this.indexOrder = i;
        }
    }

    public class MasterMap : ClassMap
    {
        public MasterMap()
        {
            Id(x => x.Id);
        }
    }

    public class ChildMap : ClassMap
    {
        public ChildMap()
        {
            Id(x => x.Id);
            References(x => x.Master).Index("A");
            Map(x => x.Name).Index("A");

        }
    }

    class MySchemaExport : SchemaExport
    {
        internal struct MCIndexField
        {
            internal int index;
            internal string Name;

        }

        internal class MCIndex
        {
            internal string IndexName;
            public readonly IList fields = new List();
            public string Table;

            public void AddField(string name, int indexOrder)
            {
                fields.Add(new MCIndexField {index = indexOrder, Name = name});
            }
        }

        private readonly Dictionary indexes = new Dictionary();

        MCIndex ByName(string name, string table)
        {
            MCIndex result;
            if (!indexes.TryGetValue(name, out result))
            {
                result = new MCIndex
                    {
                        IndexName = name
                    };
                indexes.Add(name, result);
            }
            return result;
        }

        public MySchemaExport(Configuration cfg) : base(cfg)
        {
            foreach (var type in typeof(ChildMap).Assembly.GetTypes())
            {
                foreach (var prop in type.GetProperties())
                {
                    var attr = prop.GetCustomAttributes(typeof (MCIndexAttribute), true);
                    if (attr.Length == 1)
                    {
                        var attribute = (MCIndexAttribute) attr[0];
                        ByName(attribute.indexName, type.Name).AddField(prop.Name, attribute.indexOrder);
                    }
                }
            }


            var createSqlProp = typeof(SchemaExport).GetField("createSQL", BindingFlags.NonPublic | BindingFlags.Instance);
            var wasSql = createSqlProp.GetValue(this);

            var sb = new StringBuilder();
            sb.AppendLine("");
            foreach (var mcIndex in indexes)
            {
                sb.AppendLine(string.Format("create index {0} on {1} ({2})", mcIndex.Value.IndexName, mcIndex.Value.Table, mcIndex.Value.fields));
            }
            createSqlProp.SetValue(this, wasSql + sb.ToString());
        }
    }

    class Program
    {

        private static void BuildSchema(Configuration config)
        {
            new MySchemaExport(config)
                .Create(s =>
                            {
                                Debug.WriteLine(s);
                            }, true);
        }

        const string fileName = "c:\\temp\\temp.fdb";

        private static string GetConnectionString()
        {
            const string userName = "sysdba";
            const string password = "masterkey";
            return String.Format("ServerType=1;User={0};Password={1};Dialect=3;Database={2}", userName, password, fileName);
        }

        private static FluentConfiguration Configurate()
        {
            var fbc = new FirebirdConfiguration();
            return Fluently.Configure()
            .Database(fbc.ShowSql().ConnectionString(GetConnectionString()))
                .Mappings(m => m.FluentMappings
                    .AddFromAssemblyOf()
                )
              .ExposeConfiguration(BuildSchema);
        }

        static void Main(string[] args)
        {
            FluentConfiguration fluentConfiguration = Configurate();

            Configuration cfg = fluentConfiguration.BuildConfiguration();
        }
    }
}

Answer 3

I guess it is impossible. 'FluentNHibernate.MappingModel.MappedMembers.AcceptVisitor()' iterates properties before references:

        foreach (var collection in Collections)
            visitor.Visit(collection);

        foreach (var property in Properties)
            visitor.Visit(property);

        foreach (var reference in References)
            visitor.Visit(reference);

As a result, you will always have properties before references in multi-column index.

BTW none of the ORMs will give you ability to set non-trivial index options like clustered, filtered, etc.