Logo

NHibernate

The object-relational mapper for .NET

How to

This page is converted from the old nhforge.org Wiki. First published by: felicepollano on 02-25-2010, Last revision by: supersi on 06-04-2010

How to use Db2hbm

Db2hbm is a reverse engineering tool able to generate hbm files from a database schema and some information contained in a configuration file. Unlike other commercial and open source tools serving the same purpose, db2hbm is not template based. The hbm output comes from a serialization based on the NHibernate mapping schema classes. Extendibility is provided through  strategies the user can implement or extend. db2hbm does not generate code. In order to generate the classes or other artifacts please consider the use of hbm2net.

Db2hbm is a command line tool. You can download it here. Information on how to connect to the database and various generation options are contained in a separate configuration file you have to provide in order to make the tool work. A schema for the configuration file is provided in the distributed zip:nhibernate-codegen.xsd (you can download it here). You will have intellisense editing the configuration file in Visual Studio by copying that file under the %Program Files%\Microsoft Visual Studio 9.0\Xml\Schemas folder. Below there is a configuration file skeleton and a description for each section is provided.

 

<?xml version="1.0" encoding="utf-8" ?>

<db2hbm-conf xmlns="urn:nhibernate-codegen-2.2"> 

  <metadata-strategies>

    <strategy class="NHibernate.Tool.Db2hbm.FirstPassEntityCollector, NHibernate.Tool.Db2hbm"/>

    <strategy class="NHibernate.Tool.Db2hbm.PrimaryKeyStrategy, NHibernate.Tool.Db2hbm"/>

    <strategy class="NHibernate.Tool.Db2hbm.ManyToOneStrategy, NHibernate.Tool.Db2hbm"/>

    <strategy class="NHibernate.Tool.Db2hbm.SetStrategy, NHibernate.Tool.Db2hbm"/>

    <strategy class="NHibernate.Tool.Db2hbm.ManyToManyStrategy, NHibernate.Tool.Db2hbm"/>

    <strategy class="NHibernate.Tool.Db2hbm.ApplyEntityExceptionsStrategy, NHibernate.Tool.Db2hbm"/>

    <!--strategy class="NHibernate.Tool.Db2hbm.SetToMapStrategy, NHibernate.Tool.Db2hbm"/-->

  </metadata-strategies>

  <foreign-key-crawlers>

    <factory>NHibernate.Tool.Db2hbm.ForeignKeyCrawlers.MSSQLForeignKeyCrawlerFactory, NHibernate.Tool.Db2hbm</factory>

  </foreign-key-crawlers>

  <type-mapping>

    <sql-type dbtype="xml" nhtype="String"/> <!--user type instead-->

    <sql-type dbtype="bit" nhtype="Boolean"/>

    <sql-type dbtype="varbinary" nhtype="Array"/>

    <sql-type dbtype="varchar" nhtype="String"/>

    <sql-type dbtype="nvarchar" nhtype="String"/>

    <sql-type dbtype="nchar" nhtype="String"/>

    <sql-type dbtype="char" nhtype="String"/>

    <sql-type dbtype="numeric" nhtype="Decimal"/>

    <sql-type dbtype="decimal" nhtype="Decimal"/>

    <sql-type dbtype="tinyint" nhtype="Byte"/>

    <sql-type dbtype="smallint" nhtype="Int16"/>

    <sql-type dbtype="int" nhtype="Int32"/>

    <sql-type dbtype="bigint" nhtype="Int64"/>

    <sql-type dbtype="datetime" nhtype="DateTime"/>

    <sql-type dbtype="uniqueidentifier" nhtype="Guid"/>

    <sql-type dbtype="money" nhtype="Decimal"/>

    <sql-type dbtype="smallmoney" nhtype="Decimal"/>

  </type-mapping>

  <naming-strategy class="NHibernate.Tool.Db2hbm.DefaultNamingStrategy, NHibernate.Tool.Db2hbm">

    <property name="Language">English</property>

  </naming-strategy>

  <connection-info>

    <dialect>NHibernate.Dialect.MsSql2005Dialect, NHibernate</dialect>

    <connection-string>Server=localhost\SQLEXPRESS;initial catalog=AdventureWorks;Integrated Security=True</connection-string>

    <connection-driver>NHibernate.Driver.SqlClientDriver, NHibernate</connection-driver>

  </connection-info>

  <table-filter>

    <include   /> <!--Include all-->

  </table-filter>

  <tables>

  </tables>

  <entity-exceptions>

    <entity match=".*Addres.*|UnitMeasure|CreditCard"  >

      <member-tag match="set|bag|idbag|list" exclude="true"/>

      <alter>

        <set name="lazy" value="false"/>

        <meta-add attribute="comment">this is a sample meta</meta-add>

      </alter>

    </entity>

    <entity match=".*">

      <member-tag match="set|bag|idbag|list">

        <alter>

          <set name="access" value="field.camelcase-underscore"/>

          <meta-add attribute="test">Value of test</meta-add>

        </alter>

      </member-tag>

      <member-tag match="many-to-one">

        <alter>

          <set name="fetch" value="join"/>

        </alter>

      </member-tag>

   </entity>

  </entity-exceptions>

  <entities-namespace>AdventureWorks.Entities</entities-namespace>

  <entities-assembly>AdventureWorks.Entities</entities-assembly>

</db2hbm-conf>

 

<metadata-strategies>

This section configure the strategy used to infer the mapping from the schema or from what is inferred from the previous strategies. The execution order is preserved and reflect the order on the configuration file. The current version has the following strategy embedded:

  • FirstPassEntityCollector – Collect each table in the schema and provide an entity for each table with a property for each column.
  • PrimaryKeyStrategy – Find the primary keys on the tables and modify the previously collected entities to reflect the proper <id> or <composite-id> declared in the schema.
  • ManyToOneStrategy – Find the defined foreign keys, and if they point to mapped tables, creates the <many-to-one> associations
  • SetStrategy – Make collections at the other end points of a <many-to-one> association.
  • ManyToManyStrategy – Try to infer if there is a link table between two entities. If so the associative entity is dropped and a pair of collection of type <bag> or <idbag> is created to represent the many-to-many association.
  • ApplyEntityExceptionsStrategy – Applies code generation exceptions to the entities according to the <entity-exceptions> configuration section( see below )

Each strategy element, if required, can contain one of more element <property> in the followinf form:

<property name="X">yyyyyyy</property>

This means: set the value of the property named “X” to the value yyyyyy. Internally the value is converted to the target type of the property if it is not a string.

 

<foreign-key-crawlers>

The core strategies used in reverse engineering requires to know how the foreign key are declared on the tables. Since the ADO.NET schema provider model does not fit this requirement, we have to provide a custom strategy for each DB dialect we are using. Currently only MSSQL server is supported, so if you want a proper code generation for another database you should provide your own foreign-key.-crawler, or alternatively configure each FK definition by hand, as shown in the <tables> configuration section ( you would probably prefer not to ).

To provide your own key crawler, you have to create a factory for it, implementing the interface IForeignKeyCrawlerFactory, declared as shown below:

public interface IForeignKeyCrawlerFactory
{
       IForeignKeyCrawler Create();
       void Register();
}

 

The register method is called by the infrastructure, and allow you to bind your factory with one or more NHIbernate Dialect. Let’s have an example on how the MSSQLForeignKeyCrawlerFactory implement this function:

public void Register()
{
     var instance = new MSSQLForeignKeyCrawlerFactory();
     ForeignKeyCrawlersRegistar.Register(instance, typeof(MsSql2000Dialect));
     ForeignKeyCrawlersRegistar.Register(instance, typeof(MsSql2005Dialect));
     ForeignKeyCrawlersRegistar.Register(instance, typeof(MsSql2008Dialect));
}

The  code above register an instance of MSSQLForeignKeyCrawlerFactory bound to the MsSql200x dialects.

The other method, Create, are supposed to return the IForeignCrawler instance able to deal with the specific database foreign key schema info. This object must implement the following interface:

public interface IForeignKeyCrawler {
IForeignKeyColumnInfo[] GetForeignKeyColumns(DbConnection dbConnection
                                , string constraintName
                                , string catalog
                                , string schema
                                );
}

To proper implement this you should be able to return an array of IForeignKeyColumnInfo from the constraint name, the catalog and the schema containing it.

The IForeignKeyColumnInfo interface is defined as follow:

public interface IForeignKeyColumnInfo
{
       string PrimaryKeyColumnName { get; }
       string PrimaryKeyTableName { get; }
       string PrimaryKeyTableSchema { get;  }
       string PrimaryKeyTableCatalog { get;  }

       string ForeignKeyColumnName { get; }
       string ForeignKeyTableName { get; }
       string ForeignKeyTableSchema { get;  }
       string ForeignKeyTableCatalog { get;  }

}

PrimaryKey/(Table/Catalog/Column)Name must return the key column information as defined in the table containing the primary key referenced by the constraint.

ForeignKey/(Table/Catalog/Column)Name must return the key column information as defined in the table containing the foreign key referenced by the constraint.

 

<type-mapping>

Each element in this section maps a database type to an NHIbernate type. You can force a type with respect to column length/precision too. So you can have:

<sql-type dbtype="varchar" length="1" nhtype="Char"/>
<sql-type dbtype="varchar" length="2-*" nhtype="String"/>

in order to distinguish between char and strings. The most restrictive satisfied condition is used.

 

<naming-strategy>

The naming strategy is a component driving the name generation for the entities, properties, collections and component key class generated by the tool. A default naming strategy is provided: NHibernate.Tool.Db2hbm.DefaultNamingStrategy. This strategy leverages the unNHAddins inflector to singularize the name of the tables, or pluralize the collections. The unNHaddins inflector is provided for many languages, the DefaultNamingStrategy infer the concrete class to use from the property language:

<property name="Language">English</property>

If an unknown language is specified, DefaultNamingStrategy uses “English “ by default. In order to implement your own naming strategy, you have to derive from the default naming strategy, or implement your own from scratch by implementing the INamingStrategy interface. Please note that despites the name, this interface is not the one contained in NHibernate.

The interface is defined as follow:

public interface INamingStrategy
{
        string GetEntityNameFromTableName(string tableName);
        string GetPropertyNameFromColumnName(string columnName);
        string GetIdPropertyNameFromColumnName(string columnName);
        string GetClassNameForComponentKey(string entityName);
        string GetNameForComponentKey(string entityName,string componentClass);
        string GetNameForManyToOne(string referredEntity, string[] columnNames);
        string GetNameForCollection(string collectingClass, int progressive);
        string GetClassNameForCollectionComponent(string collectionTableName);
}

The meaning of the first three functions should be clear by the name. For the other ones:

  • GetClassNameForComponentKey – The entity has a composite key, and it is reverse engineering with a component, implementor has to return a name for the class representing that component.
  • GetNameForManyToOne – The entity has a reference to another entity, you have to return the name you want to assign to the corresponding property. Implementor will receive the referred entity name and the column(s) names defining the reference.
  • GetNameForCollection – the entity has a collection of other entity instances. The implementor will receive the name of the collected entity, plus a progressive ( you may have more collection of the same type of entity in a class ). implementor has to return the name to assign to the property representhing the collection.
  • GetClassNameForCollectionComponent – There is a map of component. implementor has to return the name of the class representing the component.

 

<connection-info>

Db2hbm needs to connect to the database to inquire into schema information. In this section user will provide the essential parameters to achieve the connection.

  • <dialect> – the NHibernate dialect class.
  • <connection-string> – the DB connection string
  • <connection-driver> – the NHibernate connection driver

 

<table-filter>

User can specify which table to include/exclude from the reverse engineering properties by adding keys to this section.

More than one include/exclude element can be specified. Each element has three attributes:

<include catalog=".*" schema=".*" table=".*"/>

<exclude table="UselessTable"/>

you can specify a regular expression to specify the pattern for the catalog, the schema and the table. Tables matching this pattern will be excluded/included.

 

<tables>

If there is not enougth information in the schema, or if there is not a proper foreign key crawler available for the database in use, it is possible to specify manually the foreign key/primary key layout. These information will merge with the ones already present in the schema, if any.

Here below an example:

<tables>
    <table schema="dbo" catalog="db2hbm" name="Simple">
      <primary-key >
        <generator class="sequence" >
          <param name="sequence">id_seq</param>
        </generator>
      </primary-key>
      <foreign-key constraint-name="myconstraint" foreign-catalog="adatabase" foreign-schema="dbo" foreign-table="ReferredTable">
        <column-ref foreign-column="id" local-column="referred-id"/>
      </foreign-key>
    </table>
  </tables>

 

<entity-exceptions>

In this section the user can finely drive the produced hbm artifacts by altering the default tool behavior on a per-name o per-tag based strategy. Let’s have an example:

<entity-exceptions>
   <entity match=".*Addres.*|UnitMeasure|CreditCard"  >
     <member-tag match="set|bag|idbag|list" exclude="true"/>
     <alter>
       <set name="lazy" value="false"/>
       <meta-add attribute="comment">a comment…</meta-add>
     </alter>
   </entity>
   <entity match=".*">
     <member-tag match="set|bag|idbag|list">
       <alter>
         <set name="access" value="field.camelcase-underscore"/>
         <meta-add attribute="test">Value of test</meta-add>
       </alter>
     </member-tag>
     <member-tag match="many-to-one">
       <alter>
         <set name="fetch" value="join"/>
       </alter>
     </member-tag>
   </entity>
</entity-exceptions>

 

The tag <entity match=".*Addres.*|UnitMeasure|CreditCard"> is a selctor based on the entity name. The selector match attribute is a .NET Regex. All entities satisfied this Regex will be interested in the alter process.

The <member-tag> is a selector, the match is done against the tag name in the generated hbm. The exclude attribute means, in this case, to remove all the collections from the entity. If a name based selection is required, <member-name> has to be used, with the same semantic.

The alter tag drive the modification, and works both on a per-entity or a per-member base. In an alter tag the user can specify <set name=”something” value=”val”/> to set ( or change ) an attribute on the generated hbm.  To remove an attribute the <remove name=”toremove”/> is required, and for add an additional meta information on the hbm the <meta-add attribute=”metaattribute”>the attribute content</meta-attribute> has to be used.

 

<entities-namespace>

Specify the namespace of the generated entities.

 

<entities-assembly>

Specify the assembly containing the generated entities. ( db2hbm does not compile any code, but this is a parameter in the hbm file).

 

Using the command line tool

Using the command line tool is very easy: just launch

db2hbm –config:configfilename –output:outputdir

The output directory is optional. If not specified, hbm artifacts will be generated in the current folder.

© NHibernate Community 2016