How to make NHibernate to support Delete Store Procedure better

So far, the latest NH (1.2.1.GA) still only generates something like DELETE_SP id if   custom delete  sql is being specified in hbm.xml file. In our situation, all the delete stored procedure mandatory validate every column before doing the actual deleting, so called dirty check which is often used by update sp.

I looked into the source code of NH, it’s dynamic-update and optimistic-lock settings can implement the same dirty check by generating the DELETE SQL with all updatable properties, (doesn’t support detached-objects as official doc said)  but this is SQL, not calling the stored  procedure we defined. SP is being skipped.

I have to change NH’s source code and build my own fix version of NH, if we are required not changing SPs.

My modifications:

In NHibernate.Persister.Entity.AbstractEntityPersister:

public void Delete(object id, object version, object obj, ISessionImplementor session)

......

for (int j = span - 1; j >= 0; j--)
{

object[] deleteState = null;

EntityKey key = new EntityKey(id, this);
object entity = session.GetEntity(key);
if (entity != null)
{
EntityEntry entry = session.GetEntry(entity);
deleteState = entry.DeletedState;
}

////////////////////////////////////

Delete(id, deleteState, version, j, obj, deleteStrings[j], session, loadedState);</blockquote>
<blockquote>public void Delete(object id, object[] fields, object version, int j, object obj, SqlCommandInfo sql, ISessionImplementor session,
object[] loadedState)
{
.......

try
{
int index = 0;

//index += expectation.Prepare(statement, factory.ConnectionProvider.Driver);

// Do the key. The key is immutable so we can use the _current_ object state
//IdentifierType.NullSafeSet(statement, id, index, session);
//index += IdentifierColumnSpan;

////////////////////////////////////
/// Frank's fix for delete sp.
///////////////////////////////////
bool[] includeProperty = GetPropertiesToInsert(fields);
index = Dehydrate(id, fields, includeProperty, propertyColumnInsertable, j, statement, session, index);
///////////////////////////////////
.........

protected SqlCommandInfo GenerateDeleteString(int j)
{
SqlDeleteBuilder deleteBuilder = new SqlDeleteBuilder(Factory);

//            deleteBuilder
//                .SetTableName(GetTableName(j))
//                .SetIdentityColumn(GetKeyColumns(j), IdentifierType);
////////////////////////////////////
/// Frank's Fix for delete SP
/// ////////////////////////////////
deleteBuilder
.SetTableName(GetTableName(j));

bool[] includeProperty = PropertyInsertability;
bool hasColumns = false;
for (int i = 0; i &lt; entityMetamodel.PropertySpan; i++)
{
if (includeProperty[i] &amp;&amp; IsPropertyOfTable(i, j))
{
deleteBuilder.AddColumns(GetPropertyColumnNames(i), propertyColumnInsertable[i], PropertyTypes[i]);
hasColumns = hasColumns || GetPropertyColumnSpan(i) &gt; 0;
}
}
// dehydrate() always add the id column as the last param.
deleteBuilder.SetIdentityColumn(GetKeyColumns(j), IdentifierType);

///////////////////////////////////////////

if (j == 0 &amp;&amp; IsVersioned)
{
deleteBuilder.SetVersionColumn(new string[] {VersionColumnName}, VersionType);
}

return deleteBuilder.ToSqlCommandInfo();
}

//In NHibernate.SqlCommand.SqlDeleteBuilder, add those methods similar to SqlInsertBuilder :

////////////////////////////////////////////
/// Frank's fix for delete sp.
///////////////////////////////////////////

private ArrayList columnNames = new ArrayList(); // name of the column
private ArrayList columnValues = new ArrayList(); //string or a Parameter

/// <summary>
/// Add a column with a specific value to the DELETE sql
/// </summary>
/// <param name="columnName>The name of the Column to add.</param>
/// <param name="val">The value to set for the column.</param>;
/// <param name="literalType">The NHibernateType to use to convert the value to a sql string.</param>
/// <returns>The SqlDeleteBuilder.</returns>
public SqlDeleteBuilder AddColumn(string columnName, object val, ILiteralType literalType)
{
return AddColumn(columnName, literalType.ObjectToSQLString(val));
}

/// <summary>
/// Add a column with a specific value to the DELETE sql
/// </summary>
/// <param name="columnName">The name of the Column to add.</param>
/// <param name="val">A valid sql string to set as the value of the column.</param>
/// <returns>The SqlDeleteBuilder.</returns>
public SqlDeleteBuilder AddColumn(string columnName, string val)
{
columnNames.Add(columnName);
columnValues.Add(val);

return this;
}

/// <summary>
/// Adds columns with a specific value to the DELETE sql
///</summary>
/// <param name="columnName">The names of the Column sto add.</param>
/// <param name="val">A valid sql string to set as the value of the column.</param>
/// <returns>The SqlDeleteBuilder</returns>
public SqlDeleteBuilder AddColumns(string[] columnName, string val)
{
for (int i = 0; i &lt; columnName.Length; i++)
{
columnNames.Add(columnName[i]);
columnValues.Add(val);
}

return this;
}

/// <summary>
/// Adds the Property's columns to the UPDATE sql
/// </summary>
/// <param name="columnNames">An array of the column names for the Property</param>
/// <param name="propertyType">The IType of the property.</param>
/// <return>The SqlDeleteBuilder.</returns>
public SqlDeleteBuilder AddColumns(string[] columnNames, IType propertyType)
{
return AddColumns(columnNames, null, propertyType);
}

public SqlDeleteBuilder AddColumns(string[] columnNames, bool[] insertable, IType propertyType)
{
SqlType[] sqlTypes = propertyType.SqlTypes(Mapping);

for (int i = 0; i &lt; columnNames.Length; i++)
{

if (insertable == null || insertable[i])
{

parameterTypes.Add(sqlTypes[i]);

}
}

return this;
}

Compiled and re-build, change the reference to this new NH, now my app’s delete stored procedure can take more parameters than just primary key as before.

Note: Even this is a workable solution, but the best option is still use timstamp field.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s