NHibernate blog

DataBase; The Eliot Ness of IT ?

Fabio Maulo — Fri, 26 Jun 2009 13:23:00 GMT

How many times we heard :

“I need to map this situation…bla…bla… Note: I can’t touch the DataBase schema.”

“I don’t like composite PK but I can’t change the DB schema.”

“I need to map this situation…bla…bla… Note: legacy DB.”

You can see phrases similar to those above in various NHibernate forums in various languages. The fact that questions are sent to a NH’s forum mean that somebody are developing a new .NET2.0 or .NET3.5 application using NHibernate with an existing DataBase (I’m hoping this is the only possible situation). If a company spent time and money to rewrite an obsolete application for sure is because it is needed.

Let me show another situation: In a new application the first base module was deployed in production. The team now has an existing application with an existing DB and is developing another module. In the second module we realize that some change is needed to the persistence we actually have in production. What the team should do ? Well…something normal for us is create a “migration step” for the DB. The same happen again and again in each sprint.

How much different is the situation when the existing DB is five, or more, years old ? (well… it is different but I’m not so sure that the difference is so big).

“I can’t change the DB because there are other applications using it”… my friend in this case the first step should be write a good service layer to serve “externals” applications.

Why, write a new .NET3.5 application, shouldn’t mean re-think the DB ? The DB is not a part of the old application ?

We are working in software, is the relational DB a piece of granite technology of the past century and nothing more ?

One of my preferred Mentor, illuminate me the way of ORM in these past years, is Scott W. Ambler. If you want help your DBA to come in the XXI century gives him some of Scott’s books. Waiting Christmas point him to this article and invite him to follow each singular link; perhaps you will win a friend.

Dear DBA we are not your enemies, we are both in the same ship.

Auto Quote Table/Column names

Fabio Maulo — Wed, 24 Jun 2009 03:25:00 GMT

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:

Trough configuration
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.

NHibernate Cache Providers 2.1.0Beta1

Fabio Maulo — Sun, 21 Jun 2009 15:52:00 GMT

NHibernate Caches 2.1.0Beta1 was released today.

Download NHibernate Caches 2.1.0Beta1.

Enjoy it together with NHibernate2.1.0.

NHibernate 2.1.0Beta2

Fabio Maulo — Sun, 21 Jun 2009 13:05:00 GMT

NHibernate 2.1.0 Beta2 is on the road.

Download NHibernate 2.1.0.Beta2

After download it please take a look to releasenotes.txt file.

Note:

For those are using sources, there is a new branch named : 2.1.x

All next releases of NH2.1 will be released from the branch.

The trunk, stating from few minutes, will be NH3.0.0 and its target is .NET3.5 (“good bye” to .NET2.0 compatibility).

From where start to implements IDataBaseSchema

Fabio Maulo — Fri, 12 Jun 2009 17:30:00 GMT

The auto-quote and auto import KeyWords features are now available in NHibernate but only for those dialects are providing an implementation of IDataBaseSchema.

If the NHibernate’s dialect, for your favorite RDBMS, does not provide an implementation of IDataBaseSchema, what can you do ?

Start point

First of all you need an easy way to know some “internals” of your DataProvider/RDBMS. The code to extract all information you are needing to implement a IDataBaseSchema is:

internal class Program
{
 private static void Main(string[] args)
 {
  
     // Extract metadata for Oracle
     CreateMetadataXml("System.Data.OracleClient", "User Id=NH; Password=nh");

     // Extract metadata for MsSQL
     CreateMetadataXml("System.Data.SqlClient", @"Data Source=localhost\SQLEXPRESS;Initial Catalog=NHTEST;Integrated Security=True");
  
     Console.WriteLine("Work done!");
     Console.ReadLine();
 }

 private static void CreateMetadataXml(string providerName, string connectionString)
 {
     DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);

     using (DbConnection conn = factory.CreateConnection())
     {
         try
         {
             conn.ConnectionString = connectionString;
             conn.Open();

             //Get MetaDataCollections and write to an XML file.
             //This is equivalent to GetSchema()
             DataTable dtMetadata = conn.GetSchema(DbMetaDataCollectionNames.MetaDataCollections);
             dtMetadata.WriteXml(providerName + "_MetaDataCollections.xml");

             //Get Restrictions and write to an XML file.
             DataTable dtRestrictions = conn.GetSchema(DbMetaDataCollectionNames.Restrictions);
             dtRestrictions.WriteXml(providerName + "_Restrictions.xml");

             //Get DataSourceInformation and write to an XML file.
             DataTable dtDataSrcInfo = conn.GetSchema(DbMetaDataCollectionNames.DataSourceInformation);
             dtDataSrcInfo.WriteXml(providerName + "_DataSourceInformation.xml");

             //data types and write to an XML file.
             DataTable dtDataTypes = conn.GetSchema(DbMetaDataCollectionNames.DataTypes);
             dtDataTypes.WriteXml(providerName + "_DataTypes.xml");

             //Get ReservedWords and write to an XML file.
             DataTable dtReservedWords = conn.GetSchema(DbMetaDataCollectionNames.ReservedWords);
             dtReservedWords.WriteXml(providerName + "_ReservedWords.xml");
         }
         catch (Exception ex)
         {
             Console.WriteLine(ex.Message);
             Console.WriteLine(ex.StackTrace);
         }
     }
 }
}

The code above will create an XML file for each “matter” involved with IDataBaseSchema implementation.

What’s next

I don’t want to deep in details because each RDBMS may have different info so the only things I can tell you are:

There are some base classes that may work, as is, for your RDBMS : AbstractDataBaseSchema, AbstractTableMetadata, AbstractColumnMetaData, AbstractForeignKeyMetadata, AbstractIndexMetadata.
There are some implementations for various RDBMS where you can see which are the difference and where some information, extracted from the code above, was used : FirebirdMetaData.cs, MsSqlCeMetaData.cs, MsSqlMetaData.cs, MySQLMetaData.cs, OracleMetaData.cs, SQLiteMetaData.cs, SybaseAnywhereMetaData.cs.
Tests to pass are contained in NHibernate.Test.Tools.hbm2ddl namespace.

What you should do after implement IDataBaseSchema

Create a new JIRA ticket as:

Project : NHibernate
Issue Type: Improvement
Summary : New DataBase schema provider for “your preferred RDBMS”
Priority : Minor
Component : DataProviders / Dialects
Affects Version : “The last released”
Description : “Few words”
Attachment : this is the most important; send us your implementation!!

Thanks.

LINQ to NHibernate Update

Steve Strong — Thu, 11 Jun 2009 13:50:40 GMT

We're long overdue for another update. Progress as been somewhat slower over the last few weeks, largely due to other commitments and holidays meaning that I've probably only spent a total of around 10 days working on this stuff.

Where we are is that I'm pretty comfortable with what needs to happen, and have a fairly clear route for moving forward from my prototype / exploratory coding phase to something that I can actually release to the wild and let folk start playing with.

There are two options as to how this might happen. The first is by utilizing the concepts within the excellent IQToolkit, which provides a great sample for anyone who wants to explore how to build a Linq to Sql provider. Alas, the code can't be used as it stands, since it's quite closely tied to building and executing SQL and then processing the resulting data rows, which isn't quite what we're trying to achieve here. However, the fundamental concepts and algorithms for handling some of the Linq complexities are certainly valid, and can be ported across to meet our needs relatively simply.

The second option is by building on top of the promising re-linq project, which performs a similar task to the first half of the IQ Toolkit, in that it takes a Linq expression tree and turns it into something a little more manageable. re-linq doesn't attempt to handle the execution side, so it precisely avoids some of the stuff that I'd need to rip out of the IQ Toolkit.

re-linq is currently undergoing some final refactoring (some of it driven by the potential to use it within this project,so many thanks to them for keeping us in mind!). This is due to be completed in the next 3 or so weeks; once they are done, a final decision as to the exact approach can be taken and I'd expect pretty fast progress from that point.

Obviously, my original guesstimate of a June date is looking seriously in doubt, but I don't think it'll be too much longer. Hang in there folks, I know it's been a long wait but it'll be worth it!

NHibernate Cache Providers 2.1.0Alpha1

Fabio Maulo — Sun, 07 Jun 2009 17:03:00 GMT

NHibernate Caches 2.1.0Alpha1 was released today.

Download NHibernate Caches 2.1.0Alpha1.

Enjoy it together with NHibernate2.1.0.

NHibernate 2.1.0Beta1

Fabio Maulo — Sun, 07 Jun 2009 15:28:00 GMT

NHibernate 2.1.0Beta1 was released today.

Download NHibernate 2.1.0.Beta1

After download it please take a look to releasenotes.txt file.