Optimistic Locking with Version/Timestamp Columns and Handling the Prefix of Tables in NHibernate

• Download demo project - 835.47 KB
• Download source - 24.33 KB

Contents

• Introduction
• Set up Demo
• Define New User Type to Check Optimistic Locking with Timestamp Data Type
• Handle the Prefix of Tables and Database Owner in NHibernate Query
• Points of Interest
• History

According to the NHibernate documentation, version 1.2.0, NHibernate recommends that timestamp data type should be used for optimistic locking:

"Version numbers may be of type Int64, Int32, Int16, Ticks, Timestamp, or TimeSpan (or their nullable counterparts in .NET 2.0)".

But it seems there is a small issue when casting the type Byte[] column of SQL Server to the type TimestampType column of NHibernate on mapping file. Some questions related to this problem have already been posted on NHibernate forum like this one.

Another issue I have already faced is that the new application will be deployed on some states and the abbreviation of each state will be the prefix of tables. For example, if it is deployed at Indiana, Users table will become In_Users, and Tx_Users for Texas, Fl_Users for Florida respectively etc. Definitely I am not encouraged to open all XML mapping files, changing the table names when it is deployed on a different state.

In order to go through these issues, I just tried to implement interface of NHibernate classes based on the real requirements.

In this article, I would like to share my hands-on experience about these issues. Hopefully, you found it useful and can adapt it to meet your specific needs.

My demo is implemented with Visual Studio 2005, NHibernate version 1.2.0. If you are interested in this demo, it needs to run the NHibernateUsers.sql script included on the package to create NHibernateUsers database.

For the purposes of this article, let's assume that NHibernateUsers database is set up at Indiana so the prefix of tables must be IN_ and it is created by web_sa user. Rowversion column of IN_Users table is designed for checking optimistic locking. Here is a Users mapping file of IN_Users table (Users.hbm.xml):

<?xml version="1.0" encoding="utf-8">
    <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-access="property"/>
    <class name="OverwriteInterfaceNHibernate.Users, OverwriteInterfaceNHibernate"
            table="Users" dynamic-update="true"
          optimistic-lock="version" mutable="true" polymorphism="implicit"
            dynamic-insert="false" select-before-update="false">
        <id name="UserID" column="userID"
            access="nosetter.camelcase" unsaved-value="0"/>
            <generator class="identity">
        </id>
        <version name="RowVersion" column="RowVersion"
            type="OverwriteInterfaceNHibernate.UserTypeTimestamp,
            OverwriteInterfaceNHibernate" generated="always" unsaved-value="null">
        <property name="UserName" column="UserName"
            type = "String(30)" not-null="true"/>
        <property name="Password" column="Password"
            type = "String(30)" not-null="true"/>
     </class>
</hibernate-mapping> 

Glancing at the mapping file, if you pay attention to the mapping file shown, there are two points. I think they should be following questions:

1. Table is named the Users (table="Users") even if it is the IN_Users in database.
   This session is going to be discussed in the section Handle the prefix of tables and database owner in NHibernate query.
2. RowVersion column is used for concurrency control, but the type of this column (UserTypeTimestamp) looks so strange.
   <version> is surely declared to ask NHibernate for completely handling optimistic locking.
   The issue happens when RowVersion column is declared the timestamp data type.
   [NHibernate.ADOException] = {"Could not cast the value in field RowVersion1_ of type Byte[] to the Type TimestampType.
   Please check to make sure that the mapping is correct and that your DataProvider supports this Data Type."}

There is definitely a difference between data type of NHibernate and SQL Server. So it is surely that I cannot use timestamp data type for RowVersion column.

Luckily, Nhibernate gives me the ability to define a new data type. So in this case, my approach is: try to define the new data type that is consistent with type Byte[].

NHibernate allows us to easily define any kind of custom data type called user types. User types can be used for various different purposes. In my case, it is used to convert a datatype, exactly type Byte[]. Instead of using the timestamp data type, the RowVersion column is UserTypeTimestamp. UserTypeTimestamp class is merely implementing Nibernate.IUserType:

using System;
using System.Collections.Generic;
using System.Text;
using NHibernate.Cfg;
using NHibernate;
using NHibernate.Type;
using NHibernate.SqlTypes;

namespace OverwriteInterfaceNHibernate
{
    /// <summary>
    /// UserTypeTimestamp implements the Nhibernate BinaryType
    /// that is used to handle Nhibernate version.
    /// </summary>
    class UserTypeTimestamp : NHibernate.UserTypes.IUserVersionType
    {
        #region IUserVersionType Members

        public object Next(object current, NHibernate.Engine.ISessionImplementor session)
        {
            try
            {
                return current;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public object Seed(NHibernate.Engine.ISessionImplementor session)
        {
            try
            {
                return new byte[8];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region IUserType Members

        public object Assemble(object cached, object owner)
        {
            try
            {
                return DeepCopy(cached);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public object DeepCopy(object value)
        {
            try
            {
                return value;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public object Disassemble(object value)
        {
            try
            {
                return DeepCopy(value);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int GetHashCode(object x)
        {
            try
            {
                return ((byte[])x).GetHashCode();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public bool IsMutable
        {
            get { return false; }
        }

        public object NullSafeGet(System.Data.IDataReader rs, string[] names,
            object owner)
        {
            try
            {
                return ((byte[])rs.GetValue(rs.GetOrdinal(names[0])));
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void NullSafeSet(System.Data.IDbCommand cmd, object value, int index)
        {
            try
            {
                NHibernateUtil.Binary.NullSafeSet(cmd, value, index);
                return;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public object Replace(object original, object target, object owner)
        {
            return original;
        }

        public Type ReturnedType
        {
            get { return typeof(byte[]); }
        }

        public SqlType[] SqlTypes
        {
           get
            {
                try
                {
                  qlType[] types = { new NHibernate.SqlTypes.SqlType
                (System.Data.DbType.Binary) }; return types;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        #endregion

        #region IComparer Members

        public int Compare(object x, object y)
        {
            try
            {
                byte[] xbytes = (byte[])x;
                byte[] ybytes = (byte[])y;
                if (xbytes.Length < ybytes.Length)
                {
                    return -1;
                }
                if (xbytes.Length > ybytes.Length)
                {
                    return 1;
                }
                for (int i = 0; i < xbytes.Length; i++)
                {
                    if (xbytes[i] < ybytes[i])
                    {
                        return -1;
                    }
                    if (xbytes[i] > ybytes[i])
                    {
                        return 1;
                    }
                }
                return 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
       bool NHibernate.UserTypes.IUserType.Equals(object x, object y)
        {
            return (x == y);
        }
        #endregion
    }
}

It seems the implementation is rather straightforward. My conversion class is ready now; everything I need to do is just change the version property mapping definition to use an instance of the conversion class instead of timestamp:

 <version name="RowVersion" column="RowVersion"
    type="OverwriteInterfaceNHibernate.UserTypeTimestamp,
OverwriteInterfaceNHibernate" generated="always" unsaved-value="null">

The next step is to create a class for domain object. I just needed to duplicate table structure in the class and provide a way for linking the Users table. rowversion property was declared with the byte[] data type.

using System;
using System.Collections.Generic;
using System.Text;
namespace ImplementInterfaceNHibernate
{
    [Serializable]
    public class Users
    {
        private int userID;
        private string username;
        private string password;
        private byte[] rowversion;
        public virtual int UserID
        {
            get { return this.userID; }
            set { this.userID = value; }
        }
        public virtual string UserName
        {
            get { return this.username; }
            set { this.username = value; }
        }
        public virtual string Password
        {
            get { return this.password; }
            set { this.password = value; }
        }
        public virtual byte[] RowVersion
        {
            get { return this.rowversion; }
            set { this.rowversion = value; }
        }
    }
}

That's all. In this test, I tried to concurrently update password of "Admin" user. So what's happening ... when "admin 1" password was updated, Nhibernate automatically checked RowVersion value, there was no change, so the process was successful but "admin 2" because RowVersion was already changed by SQL Server when "admin 1" was executed.

As mentioned in the introduction, the prefix of table is also an interesting session to dig. In the demo,IN_ is currently the prefix of Users table. It should flexibly be changed to AL_ or FL_ etc. In some cases, database is created by other users, not dbo, it is a root cause to make an error. Looking at the User.hbm.xml file, I just defined table="Users". It means the mapping file is never modified in spite of the change of prefix. How can NHibernate correctly generate an SQL statement for this case? The easy way I did is to ask NHibernate for adding the prefix when generating SQL statement. In order to do that, I need to implement the INamingStrategy interface. PrefixNamingStrategy class is simply created for this purpose.

using System;
using System.Collections.Generic;
using System.Text;
using NHibernate.Cfg;
namespace ImplementInterfaceNHibernate
{
    /// <summary>
    /// PrefixNamingStrategy class will generate prefixes for tables
    /// in the SQL statements which
    /// are generated by Hibernate
    /// </summary>
    class PrefixNamingStrategy : INamingStrategy
    {
        String tableNamePrefix;
        #region INamingStrategy Members
        public string ClassToTableName(string className)
        {
            try
            {
                return TableName(className);
            }
            catch (Exception ex) { throw ex; }

        }
        public string ColumnName(string columnName)
        {
            try
            {
                return columnName;
            }
            catch (Exception ex) { throw ex; }
        }
        public string PropertyToColumnName(string propertyName)
        {
            try
            {
                return ColumnName(propertyName);
            }
            catch (Exception ex) { throw ex; }
        }
        public string PropertyToTableName(string className, string propertyName)
        {
            try
            {

                return PropertyToColumnName(propertyName);
            }
            catch (Exception ex) { throw ex; }
        }
        public string TableName(string tableName)
        {
            try
            {
                return tableNamePrefix + tableName;
            }
            catch (Exception ex) { throw ex; }
        }
        public void setTableNamePrefix(String s)
        {
            try
            {
                tableNamePrefix = s;
            }
            catch (Exception ex) { throw ex; }
        }
        #endregion
    }
}

In PrefixNamingStrategy class, there is only one more function added to include the owner and the prefix of tables of database: setTableNamePrefix. Before building SessionFactory of NHibernate, the owner and the prefix of tables database should be added to configuration instance:

public UserDataAccess()
  {
            PrefixNamingStrategy namer = new PrefixNamingStrategy();
            namer.setTableNamePrefix("web_sa.IN_");
            nhConfig = new Configuration();

            if (nhConfig == null)
            {
                throw new InvalidOperationException
                      ("NHibernate configuration is null.");
            }
            nhConfig.SetNamingStrategy(namer);
            nhConfig.AddClass(typeof(ImplementInterfaceNHibernate.Users));
            nhFactory = nhConfig.BuildSessionFactory();
            if (nhFactory == null)
            {
                throw new InvalidOperationException
                    ("Call to Configuration.BuildSessionFactory() returned null.");
            }
  }

This is everything I did. Now NHibernate correctly generates SQL statements I expect. It is just my suggestion, owner and the prefix of tables can be put in the configuration file or anywhere you feel comfortable.

In these particular cases, I resolved to pass the issues by implementing NHibernate interface. Adding the implementation NHibernate interface skill to your development techniques will surely ease your experience when developing with NHibernate.