NHibernate SchemaValidator reimplemented.

Some time ago a new utility class was introduced to NHibernate. It’s called SchemaValidator and it allows you to detect inconsistencies between mappings and database schema. Here you can find a short sample code showing how it works.

The class has got only one useful method called Validate(). It analyzes and compares database metadata with mappings. It can detect missing tables, missing columns, improper types in mappings, etc. It throws HibernateException immediately after it encounters such a problem. However, this is a serious disadvantage (at least for me), because I would like to know all issues related to my mappings and I don’t want to be surprised by a sudden NH exception telling me that I have forgotten one column in my .HBM file. That is why I have decided to reimplement this class so that it would not throw HibernateException, but rather return a list of possible issues.

The following code is based on original NHibernate.Tool.hbm2ddl.SchemaValidator class. Instead of throwing HibernateException it returns a list of strings. The code was tested with Sql Server 2005.

using System;
using System.Collections.Generic;
using System.Data.Common;
using NHibernate;
using NHibernate.Cfg;
using NHibernate.Dialect;
using NHibernate.Dialect.Schema;
using NHibernate.Engine;
using NHibernate.Id;
using NHibernate.Mapping;
using NHibernate.Tool.hbm2ddl;
using NHibernate.Util;

namespace MyApplication.Server.DAO.SchemaValidation 
{ 
    class SchemaValidator
    {
        private readonly Configuration configuration;
        private readonly IConnectionHelper connectionHelper;
        private readonly Dialect dialect;

        public SchemaValidator(Configuration cfg) : this(cfg, cfg.Properties) { }

        public SchemaValidator(Configuration cfg, IDictionary<string, string> connectionProperties)
        {
            configuration = cfg;
            dialect = Dialect.GetDialect(connectionProperties);
            IDictionary<string, string> props = new Dictionary<string, string>(dialect.DefaultProperties);
            foreach (var prop in connectionProperties)
            {
                props[prop.Key] = prop.Value;
            }
            connectionHelper = new ManagedProviderConnectionHelper(props);
        }

        public SchemaValidator(Configuration cfg, Settings settings)
        {
            configuration = cfg;
            dialect = settings.Dialect;
            connectionHelper = new SuppliedConnectionProviderConnectionHelper(settings.ConnectionProvider);
        }

        public IList<string> Validate()
        {
            try
            {
                DatabaseMetadata meta;
                try
                {
                    connectionHelper.Prepare();
                    DbConnection connection = connectionHelper.Connection;
                    meta = new DatabaseMetadata(connection, dialect, false);
                }
                catch (Exception sqle)
                {
                    throw;
                }

                return ValidateSchema(dialect, meta);
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                try
                {
                    connectionHelper.Release();
                }
                catch (Exception e)
                {
                	throw;
                }
            }
        }
        
        private IList<string> ValidateSchema(
            Dialect dialect, DatabaseMetadata databaseMetadata)
        {
            IList<string> problems = new List<string>();

            string defaultCatalog = PropertiesHelper.GetString(NHibernate.Cfg.Environment.DefaultCatalog, 
                configuration.Properties, null);
            string defaultSchema = PropertiesHelper.GetString(NHibernate.Cfg.Environment.DefaultSchema, 
                configuration.Properties, null);

            IMapping mapping = configuration.BuildMapping();
            ICollection<PersistentClass> list = configuration.ClassMappings;
            foreach (PersistentClass pc in list)
            {
                try
                {
                    var table = pc.Table;
                    if (table.IsPhysicalTable)
                    {
                        ITableMetadata tableInfo = databaseMetadata.GetTableMetadata(
                            table.Name,
                            table.Schema ?? defaultSchema,
                            table.Catalog ?? defaultCatalog,
                            table.IsQuoted);
                        if (tableInfo == null)
                            problems.Add(string.Format("Missing table: {0}", table.Name));
                        else
                            ValidateColumns(problems, table, dialect, mapping, tableInfo);
                    }
                }
                catch (HibernateException ex)
                {
                    problems.Add(ex.Message);
                }
            }

            var persistenceIdentifierGenerators = IterateGenerators(dialect);
            foreach (var generator in persistenceIdentifierGenerators)
            {
                string key = generator.GeneratorKey();
                if (!databaseMetadata.IsSequence(key) && !databaseMetadata.IsTable(key))
                {
                    problems.Add(string.Format("Missing sequence or table: {0}", key));
                }
            }
            return problems;
        }

        private IEnumerable<IPersistentIdentifierGenerator> IterateGenerators(Dialect dialect)
        {
            var generators = new Dictionary<string, IPersistentIdentifierGenerator>();
            string defaultCatalog = PropertiesHelper.GetString(NHibernate.Cfg.Environment.DefaultCatalog,
                configuration.Properties, null);
            string defaultSchema = PropertiesHelper.GetString(NHibernate.Cfg.Environment.DefaultSchema,
                configuration.Properties, null);

            foreach (var pc in configuration.ClassMappings)
            {
                if (!pc.IsInherited)
                {
                    var ig =
                        pc.Identifier.CreateIdentifierGenerator(dialect, defaultCatalog, defaultSchema, (RootClass)pc) as
                        IPersistentIdentifierGenerator;

                    if (ig != null)
                    {
                        generators[ig.GeneratorKey()] = ig;
                    }
                }
            }

            foreach (var collection in configuration.CollectionMappings)
            {
                if (collection.IsIdentified)
                {
                    var ig =
                        ((IdentifierCollection)collection).Identifier.CreateIdentifierGenerator(dialect, defaultCatalog, defaultSchema,
                                                                                                 null) as IPersistentIdentifierGenerator;

                    if (ig != null)
                    {
                        generators[ig.GeneratorKey()] = ig;
                    }
                }
            }

            return generators.Values;
        }


        private void ValidateColumns(
            IList<string> problems, 
            Table table,
            Dialect dialect,
            IMapping mapping,
            ITableMetadata tableInfo)
        {
            IEnumerable<Column> iter = table.ColumnIterator;
            foreach (Column column in iter)
            {
                IColumnMetadata columnInfo = tableInfo.GetColumnMetadata(column.Name);

                if (columnInfo == null)
                {
                    problems.Add(string.Format("Missing column: {0} in {1}", column.Name,
                        NHibernate.Mapping.Table.Qualify(tableInfo.Catalog, tableInfo.Schema, tableInfo.Name)));
                }
                else
                {
                    bool typesMatch = column.GetSqlType(dialect, mapping).ToLower().StartsWith(columnInfo.TypeName.ToLower());
                    if (!typesMatch)
                    {
                        problems.Add(string.Format("Wrong column type in {0} for column {1}. Found: {2}, Expected {3}",
                                                                   NHibernate.Mapping.Table.Qualify(tableInfo.Catalog, tableInfo.Schema, tableInfo.Name),
                                                                   column.Name, columnInfo.TypeName.ToLower(),
                                                                   column.GetSqlType(dialect, mapping)));
                    }
                }
            }
        }

    }
}

NHibernate IUserType for fake database booleans

In the db schema I’m currently working on, boolean values are represented by columns whose type is tinyint nullable (it’s SQL Server 2005). If such a value equals 1, then it is supposed to be True, 0 means False and if it is null then it is False, too.
This time I would like to show how to use NHibernate IUserType interface to enable automatic conversion from a database field to System.Boolean value
NHibernate.UserTypes.IUserType is a very useful interface when you are to handle some strange db schemas in your data access layer. As you can guess, the thing that we need is a class that implements this interface. You can see it below. The most important methods are NullSafeGet(…) and NullSafeSet(…) as they are responsible for conversion ‘logic’.

namespace MyNamespace
{
  public class ByteAsBool : IUserType
  {
      #region IUserType Members

      public object Assemble(object cached, object owner)
      {
          return cached;
      }

      public object DeepCopy(object value)
      {
          return value;
      }

      public object Disassemble(object value)
      {
          return value;
      }

      public int GetHashCode(object x)
      {
          if (x == null)
              return 0;
          return x.GetHashCode();
      }
      public bool IsMutable
      {
         get { return false; }
      }

      // represents conversion on load-from-db operations:
      public object NullSafeGet(System.Data.IDataReader rs, 
             string[] names, object owner)
      {
          var obj = NHibernateUtil.String.
                 NullSafeGet(rs, names[0]);
          if (obj == null)
              return false;
           byte b = 0;
          try
          {
              if (obj is string)
                  b = byte.Parse(obj as string);
              else
                  b = (byte)obj;
          }
          catch (Exception)
          {
              return false;
          }
          return b == 1;
      }

      // represents conversion on save-to-db operations:
      public void NullSafeSet(System.Data.IDbCommand cmd, 
             object value, int index)
      {
          if (value == null)
          {
              ((IDataParameter)cmd.Parameters[index]).Value = 
                    DBNull.Value;
          }
          else
          {
              var boolValue = (bool)value;
              ((IDataParameter)cmd.Parameters[index]).Value = 
                    boolValue ? (byte)1 : (byte)0;
          }
      }
      public object Replace(object original, object target, 
             object owner)
      {
          return original;
      }

      public Type ReturnedType
      {
          get { return typeof(bool); }
      }

      public NHibernate.SqlTypes.SqlType[] SqlTypes
      {
          get { return new[] { SqlTypeFactory.Byte }; }
      }
      #endregion

      bool IUserType.Equals(object x, object y)
      {
          return object.Equals(x, y);
      }
  }
}

The class ByteAsBool can be now used in NHibernate mappings in the following way:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
    assembly="SomeAssembly" namespace="MyNamespace.Types">
  <class name="SomeClass" table="Some_Table" >
  <!-- ... -->
    <property name="active" 
        column="ACTIVE" 
        type="MyNamespace.ByteAsBool, SomeAssembly" access="field" />
  <!-- ... -->
  </class>
</hibernate-mapping>

The mapped class is very simple and looks very nice, as there are no ugly bool? types, just simple System.Boolean:

  public class SomeClass : BaseEntity
  {
     protected bool active;
     // ...
  }

“SqlDateTime overflow…” exception horror.

Currently, I am involved in developing a client-server multi-layered application that uses NHibernate library as its data access layer. It is designed to support some old legacy database schema. Database is SQL Server 2005. All tables in schema have got two fields which are called CREATE_TIME and UPDATE_TIME. They are both of type datetime nullable in DB schema (btw. our schema contains about 200 tables, which is, in my opinion, A LOT). Their purpose was to describe the time when a certain record was created and when it was updated.

To cope with these fields I created a base class for all mapped entities in our project – a class called BaseEntity:

    public abstract class BaseEntity
    {
        protected int id; // always mapped to primary key
        protected DateTime created;
        protected DateTime updated;

        public BaseEntity() { }

        public virtual int Id
        {
            get { return id; }
            set { this.id = value; }
        }
        public virtual DateTime Created
        {
            set { created = value; }
            get { return created; }
        }
        public virtual DateTime Updated
        {
            set { updated = value; }
            get { return updated; }
        }
    }

The three members of this class were mapped in every NHibernate mapping file (the code below shows a mapping for a Foo class whose base class is, of course, BaseEntity):

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Some.Assembly" namespace="ProjectNamespace.Types">
  <class name="Foo" table="Foo_table">
    
  <id name="Id" type="System.Int32" column="ID_FOO_TABLE" unsaved-value="0">
    <generator class="native" />
  </id>

   <!-- BaseEntity -->
  <property name="created" column="CREATE_TIME" access="field" not-null="false" type="System.DateTime"  insert="true" update="true"/>
  <property name="updated" column="UPDATE_TIME" access="field" not-null="false" type="System.DateTime"  insert="true" update="true"/>
  
  <!-- other properties here... -->
  </class>
</hibernate-mapping>

Everything seemed to work just fine until we populated our database with some real data taken from a production environment. I need to mention here that the tables were very very big (a lot of columns and records – besides the schema is an excellent example of a bad design).
Suddenly, it turned out during tests that a strange exception was caught when users tried to save certain entities. The exception looked like this:
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM….
However, this exception didn’t appear too often, which made it difficult to debug. It happened only sometimes on NHibernate operations such us session.Merge() or session.Update().
We spent a lot of time trying to figure out what was going on. NHibernate traces were giving a clue that something is wrong with created/updated fields – Nhibernate was trying to persist ‘0001-01-01 00:00:00’ values, which are incorrect values for SQL Server datetime column type.

The explanation is really simple. The database we were given contained a few records whose CREATE_TIME and UPDATE_TIME fields were null. We actually did not care about them – there were more important things to do than storing last modification time in every entity. During merge or update operation NHibernate converted these fields into DateTime.MinValue (which is actually ‘0001-01-01 00:00:00’) and such a value causes SqlDateTime overflow exception when entity is being saved. So the solution is also very simple. It is enough to change created/updated members type to Nullable<DateTime>:

    public abstract class BaseEntity
    {
        // ...
        protected DateTime? created;
        protected DateTime? updated;
        // ...
     }