Enable hbm2ddl.keywords=auto-quote in Fluent NHibernate

Question

I have made a tiny software tool that allows me to display or run SQL generated from NHibernate. I made this because hbm2ddl.auto is not recommended for production.

I have one problem: when I generate the SQL I always get the infamous Index column unquoted, because I need .AsList() mappings. This prevents me to run the SQL.

In theory, if I had an XML configuration of NHibernate I could use hbm2ddl.keywords tag, but unfortunately since my tool is designed as a DBA-supporting tool for multiple environments, I must use a programmatic approach.

My approach (redundant) is the following:

private static Configuration BuildNHConfig(string connectionString, DbType dbType, out Dialect requiredDialect)
    {
        IPersistenceConfigurer persistenceConfigurer;

        switch (dbType)
        {
            case DbType.MySQL:
                {
                    persistenceConfigurer =
                        MySQLConfiguration
                        .Standard
                        .Dialect<MySQL5Dialect>()
                        .Driver<MySqlDataDriver>()
                        .FormatSql()
                        .ShowSql()
                        .ConnectionString(connectionString);

                    requiredDialect = new MySQL5Dialect();
                    break;
                }
            case DbType.MsSqlAzure:
                {
                    persistenceConfigurer = MsSqlConfiguration.MsSql2008
                        .Dialect<MsSqlAzure2008Dialect>()
                        .Driver<SqlClientDriver>()
                        .FormatSql()
                        .ShowSql()
                        .ConnectionString(connectionString);

                    requiredDialect = new MsSqlAzure2008Dialect();
                    break;
                }
            default:
                {
                    throw new NotImplementedException();
                }
        }


        FluentConfiguration fc = Fluently.Configure()
            .Database(persistenceConfigurer)
            .ExposeConfiguration(
                cfg => cfg.SetProperty("hbm2ddl.keywords", "keywords")
                            .SetProperty("hbm2ddl.auto", "none"))
            .Mappings(
            m => m.FluentMappings.AddFromAssemblyOf<NHibernateFactory>());
        Configuration ret = fc.BuildConfiguration();
        SchemaMetadataUpdater.QuoteTableAndColumns(ret);
        return ret;


    }

...

public static void GenerateSql(MainWindowViewModel viewModel)
    {
        Dialect requiredDialect;
        Configuration cfg = BuildNHConfig(viewModel.ConnectionString, viewModel.DbType.Value, out requiredDialect);

        StringBuilder sqlBuilder = new StringBuilder();

        foreach (string sqlLine in cfg.GenerateSchemaCreationScript(requiredDialect))
            sqlBuilder.AppendLine(sqlLine);

        viewModel.Sql = sqlBuilder.ToString();
    }

Explanation: when I want to set the ViewModel's SQL to display on a TextBox (yea, this is WPF) I initialize the configuration programmatically with connection string given in ViewModel and choose the dialect/provider accordingly. When I Fluently Configure NHibernate I both set hbm2ddl.keywords (tried both auto-quote and keywords, this being the default) and, following this blog post, I also use the SchemaMetadataUpdater.

The result is that I'm always presented with SQL like

create table `OrderHistoryEvent` (Id BIGINT NOT NULL AUTO_INCREMENT, EventType VARCHAR(255) not null, EventTime DATETIME not null, EntityType VARCHAR(255), Comments VARCHAR(255), Order_id VARCHAR(255), Index INTEGER, primary key (Id))

where the guilty Index column is not quoted.

The question is: given a programmatic and fluent configuration of NHibernate, how do I tell NHibernate to quote any reserved word in the SQL exported by GenerateSchemaCreationScript?

Answer 1

I have found a workaround: when I generate the update script (the one that runs with hbm2ddl.auto=update) the script is correctly quoted.

The infamous Index column has been already discussed and from my findings it's hardcoded in FNH (ToManyBase.cs, method public T AsList()).

Since the update script is a perfectly working creational script on an empty database, changing the code to generate an update script on an empty DB should equal generating a creational script.

This happens only because I want to generate the script on my own. There is probably a bug in NHibernate that only activates when you call GenerateSchemaCreationScript and not when you let your SessionFactory build the DB for you