We're using the "table per subclass, using a discriminator" inheritance pattern in our database. NHibernate's lack of support for this strategy has left me looking for workarounds because the performance impact of using joined-subclass with our number of subclass tables is too great.
We've got a client-side (Firebird embedded) and a server-side database (MS SQL 2005), using a replication pattern to get offline data back and forth. The replication pattern will require a close match between client and server database schemas.
On the server side (MSSQL) we were able to work around the issue by using the table per class hierarchy with a discriminator and flattening the table structure through the use of custom queries which called stored procedures. After using SET NOCOUNT ON to hide the second insert/update from NHibernate everything worked well.
While implementing the client-side mapping I ran into an interesting issue with Firebird; it does not report the number of rows affected with stored procedures. Although the stored procedure successfully insert/updates/deletes the record NHibernate throws an error and rolls back the transaction because the ExecuteNonQuery returns 0. Even if it did return the correct number of rows, without a comprable "SET NOCOUNT ON" statement, it would likely return 2 rows affected and NHibernate would throw an error.
At this point I'm looking for suggestions (and pros/cons) on how to proceed on the client.
I've come up with a few:
* Port the Hibernate code for "table per subclass, using a discriminator" (<join/>) to NHibernate.
* Submit an NHibernate patch to allow RowsAffected to be returned via an output parameter when using custom insert/update/delete(s). It could be declared in the mapping file as <sql-insert rowsAffectedParameter=":rowCount">/*custom sql here*/</sql-insert>
* Add "BaseTable" columns to all Subclass tables and use a trigger to replicate the changes to the "BaseTable." Base columns on the subclass will be nullable to allow replication to work and named queries for selects will join to the base table.
Code:
/* simplified table structure for illustration */
BaseTable
(
KeyValue INT
CommonValue VARCHAR(32),
SubclassType INT
)
Subclass1Table -- SubclassType = 1
(
KeyValue INT
Subclass1Value VARCHAR(32)
)
Subclass2Table -- SubclassType = 2
(
KeyValue INT
Subclass2Value VARCHAR(32)
)
/* simplified class code */
public abstract class BaseClass
{
public string KeyValue;
public string CommonValue;
}
public class Subclass1 : BaseClass
{
public string Subclass1Value;
}
public class Subclass2 : BaseClass
{
public string Subclass2Value;
}