In this post I will look at what is probably a less common situation, but it boasts slightly more complex SQL formulas and a more entwined domain model.

Here is the situation we have:

We want to merge the classes Pump and PumpDefinition into one. The old database looks like this:

We should have no trouble maintaining the Pumps list in Network; nothing changed there. So we just need to retrieve the Pump properties DefinitionName and Capacity from another table: pump_definition.

Let's start with the basics migrating HBM, which is almost the same as the new HBM:

Migrating HBM
  <class name="Network">
    <id name="Id"> <generator class="guid" /> </id>
    <bag name="Pumps" cascade="all-delete-orphan">
      <key column="PumpId"/>
      <one-to-many class="Pump"/>
    </bag>
  </class>

  <class name="Pump">
    <id name="Id">
      <generator class="guid" />
    </id>
    <property name="Name" />
    <property name="DefinitionName" ???? />
    <property name="Capacity" ???? />
  </class>

It's not finished yet, we need to do something about DefinitionName and Capacity. Let's start with the DefinitionName. Using the formula field and simple SQL we can retrieve it from the pump_definition table, we just need to match the Ids:

SELECT def.Name FROM pump_definition def WHERE def.Id = Definition

Note that 'Definition' here is the column (foreign key) in the old Pump table.

For Capacity we can do something quite similar, so the HBM needs to be adjusted to the following:

Actual legacy formulas
    <property name="DefinitionName" formula="( SELECT def.Name FROM pump_definition def WHERE def.Id = Definition)"/>
    <property name="Capacity" formula="( SELECT def.Capacity FROM pump_definition def WHERE def.Id = Definition)"/>

The resulting cleaned-up SQL looks like this:

SELECT pump.NetworkId, pump.Id, pump.Id, pump.Name, 
   ( SELECT def.Name FROM pump_definition def WHERE def.Id = pump.Definition), 
   ( SELECT def.Capacity FROM pump_definition def WHERE def.Id = pump.Definition) 
FROM Pump pump 
WHERE pump.NetworkId=...

This should have concluded the series on using hbms for backward compatibility, however I will do at least one more post, regarding class inheritance. For source code see first post in series.

  • No labels