NHibernate Forge
The official new home for the NHibernate for .NET community

Auto Quote Table/Column names

Since long time we have a very interesting request on NHibernate JIRA (NH-188).

If you are working in a multi-RDBMS application, you are annoyed, for sure, quoting a table-name or a column-name. As a very good persistent-layer this should be a NHibernate’s work.

I’m happy to announce that the problem is solved (even if, so far, is not done by default).

If you want that NH take the responsibility of properly quote table-name or column-name only where really needed now you can do it in two ways:

  1. Trough configuration
  2. Explicitly by code

Trough configuration

As you probably know NHibernate’s configuration has some property oriented to mapping-to-DLL tasks.

For schema integration you can use

<property name="hbm2ddl.auto">create-drop</property>

Allowed values for hbm2dll are:

  • update : auto execute SchemaUpdate on BuildSessionFactory
  • create : auto execute SchemaExport on BuildSessionFactory
  • create-drop : auto execute SchemaExport on BuildSessionFactory recreating the schema
  • validate : auto execute SchemaValidator on BuildSessionFactory

The new property is:

<property name="hbm2ddl.keywords">auto-quote</property>

Allowed values are:

  • none : disable any operation regarding RDBMS KeyWords
  • keywords : (activated by Default)imports all RDBMS KeyWords where the NH-Dialect can provide the implementation of IDataBaseSchema (so far available for MsSQL, Oracle, Firebird, MsSqlCe, MySQL, SQLite, SybaseAnywhere)
  • auto-quote : imports all RDBMS KeyWords and auto-quote all table-names/column-names on BuildSessionFactory

Explicitly by code

When you have an instance of a configured configuration (just before call BuildSessionFactory) you can execute:

SchemaMetadataUpdater.QuoteTableAndColumns(configuration);

That’s all.

The advantage

Take a look to this mapping:

<class name="Order">
<
id type="int">
<
generator class="native"/>
</
id>
<
property name="Select"/>
<
property name="From"/>
<
property name="And"/>
<
property name="Column"/>
<
property name="Name"/>
</
class>

Well… now it is working fine without explicitly quote.

Enjoy NHibernate’s multi-RDBMS easy support.


Posted jun 24 2009, 12:25 a.m. by Fabio Maulo

Comments

Stefan Steinegger wrote re: Auto Quote Table/Column names
on 06-24-2009 3:41

What's exactly the difference between keywords and auto-quote?

rachel123 wrote re: Auto Quote Table/Column names
on 06-25-2009 7:37

I am very pleased with the thought and don’t feel like adding anything in it. It a perfect answer.

Rachel Kinker

<a href="www.fastrealestate.net/.../" rel="dofollow">loan modification</a>

Fabio Maulo wrote re: Auto Quote Table/Column names
on 06-25-2009 9:52

@Stefan

Keywords is by default: NH use the keywords of the dialect to modify a 'formula' template, for example.

auto-quote : imports all RDBMS KeyWords and auto-quote all table-names/column-names you/NH put in the mapping metadata.

Stefan Steinegger wrote re: Auto Quote Table/Column names
on 06-26-2009 3:51

@Fabio: forgive me my ignorance, I didn't get it. I try to express what I understood. I think I'm quite far.

You say, "keywords" escapes rdbms keywords eg. in formula templates. (I assume a formula template is a text in a "formula" attribute in the mapping file.) For instance, if I write a formula like "index + 2" it turns it to "[index] + 2", is this correct? What happens if I want to use a keyword in the formula? Which other strings does NH change?

"auto-quote" escapes all table names and column names in the mappings. I assume wherever a table name or column name is declared (table and column attributes for instance). Does it also do anything with formulas, filters, native sql (sql-update, sql-insert) and so on? Why does NH need to import database keywords to do this? It just needs to know the column and table names.

Fabio Maulo wrote re: Auto Quote Table/Column names
on 06-26-2009 9:51

No, Stefan

formula="SELECT TOP 1 ..."

without keywords will be translated to

SELECT this.TOP 1....

intvoker wrote re: Auto Quote Table/Column names
on 10-26-2009 11:55

When using PostgreSQL dialect this code:

SchemaMetadataUpdater.QuoteTableAndColumns(configuration);

throws:

System.NotSupportedException: Specified method is not supported.

  at NHibernate.Dialect.Dialect.GetDataBaseSchema(DbConnection connection)

  at NHibernate.Tool.hbm2ddl.SchemaMetadataUpdater.GetReservedWords(Dialect dialect, IConnectionHelper connectionHelper)

  at NHibernate.Tool.hbm2ddl.SchemaMetadataUpdater.GetReservedWords(IDictionary`2 cfgProperties)

  at NHibernate.Tool.hbm2ddl.SchemaMetadataUpdater.QuoteTableAndColumns(Configuration configuration)

Fabio Maulo wrote re: Auto Quote Table/Column names
on 10-26-2009 12:04
Powered by Community Server (Commercial Edition), by Telligent Systems