NHibernate mapping for Oracle INTERVAL DAY TO SECOND datatype

Question

Not so much a question as I challenge a colleague of mine was presented yesterday by a DBA. We have a TimeSpan property on one of our objects which HAS to be persisted. Yes, you could just infer the value from the Start and End DateTime properties on the object but the DBA is adamant that this value is saved on the database table.

So the Oracle data type chosen by the DBA to hold the value is INTERVAL DAY(2) TO SECOND(6).

The corresponding type in Oracle.DataAccess is OracleDbType.InvervalDS but I've not been able to find anything relating to how to map that with NHibernate.

We ended up with this solution

    public class SomeTimeSpanTestClass
    {
        public virtual string TimeSpanTest { get; protected set; }
        public virtual TimeSpan ActualTimeSpan
        {
            get 
            {
                // Need to do some formatting of TimeSpanTest before it can be parsed
                return TimeSpan.Parse(TimeSpanTest);
            }
            set 
            {
                TimeSpanTest = string.Format("{0}{1} {2}:{3}:{4}.{5}",
                    value.ToString().Contains('-') ? "-" : "+",
                    value.Days.ToString().Contains('-') ? value.Days.ToString().Substring(1).PadLeft(2, '0') : value.Days.ToString().PadLeft(2, '0'),
                    value.Hours.ToString().Contains('-') ? value.Hours.ToString().Substring(1).PadLeft(2, '0') : value.Hours.ToString().PadLeft(2, '0'),
                    value.Minutes.ToString().Contains('-') ? value.Minutes.ToString().Substring(1).PadLeft(2, '0') : value.Minutes.ToString().PadLeft(2, '0'),
                    value.Seconds.ToString().Contains('-') ? value.Seconds.ToString().Substring(1).PadLeft(2, '0') : value.Seconds.ToString().PadLeft(2, '0'),
                    value.Milliseconds.ToString().Contains('-') ? value.Milliseconds.ToString().Substring(1).PadLeft(6, '0') : value.Milliseconds.ToString().PadLeft(6, '0')
                );
            }
        }
    }

With the mapping as

    <property name="TimeSpanTest" column="TIMESPAN_TEST"/>

A very noddy test as

    class Program
    {
        static void Main(string[] args)
        {
            SomeTimeSpanTestClass spanClass = new SomeTimeSpanTestClass();

            DateTime start = DateTime.Now;
            DateTime end = DateTime.Now.AddMinutes(75);
            spanClass.ActualTimeSpan = end.Subtract(start);

            Console.WriteLine(spanClass.TimeSpanTest);        

        }
    }

Obviously this code isn't refactored in any way, but for the purpose of this test it's trivial anyway.

The value in the database basically has to look like this "+00 01:15:03.000874". A - sign is also valid at the start of the string if the value is negative. An important point to note here is: when the value is negative, each part of the TimeSpan object is negative when looked at in isolation, hence the not so pretty "value.Days.ToString().Contains('-')" in each section of the Format() method.

Ours tests pass, we are able to save and retrieve a TimeSpan value into a database column defined as INTERVAL DAY(2) TO SECOND(6) via NHibernate.

If anyone has done this before a better way I'd be very interested to know how.

Apologies for not linking the Oracle types, it's my first post so I'm not allowed...

Answer 1

public virtual TimeSpan ActualTimeSpan { get; set; }



class TimeSpanUserType : ImmutableUserType
{
    public override object NullSafeGet(IDataReader rs, string[] names, object owner)
    {
        // Need to do some formatting of TimeSpanTest before it can be parsed
        return TimeSpan.Parse((string)rs[names[0]]);
    }

    public override void NullSafeSet(IDbCommand cmd, object value, int index)
    {
        var timespan = (TimeSpan)value;
        var duration = timespan.Duration();
        var timeSpanstring = string.Format("{0}{1} {2}:{3}:{4}.{5}",
            (timespan.Ticks < 0) ? "-" : "+",
            duration.Days.ToString().PadLeft(2, '0'),
            duration.Hours.ToString().PadLeft(2, '0'),
            duration.Minutes.ToString().PadLeft(2, '0'),
            duration.Seconds.ToString().PadLeft(2, '0'),
            duration.Milliseconds.ToString().PadLeft(3, '0').PadRight(6, '0'));

        NHibernateUtil.String.NullSafeSet(cmd, timeSpanstring, index);
    }

    public override Type ReturnedType
    {
        get { return typeof(TimeSpan); }
    }

    public override SqlType[] SqlTypes
    {
        get { return new[] { SqlTypeFactory.GetString(8) }; }
    }
}

<property name="ActualTimeSpan" column="TIMESPAN_TEST" type="TimeSpanUserType"/>

Edit: added immutableUserType

public abstract class ImmutableUserType : IUserType
{
    public new virtual bool Equals(object x, object y)
    {
        return object.Equals(x, y);
    }

    public virtual int GetHashCode(object x)
    {
        return (x == null) ? 0 : x.GetHashCode();
    }

    public override bool IsMutable
    {
        get { return false; }
    }

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

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

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

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

    public abstract object NullSafeGet(System.Data.IDataReader rs, string[] names, object owner);

    public abstract void NullSafeSet(System.Data.IDbCommand cmd, object value, int index);
    public abstract Type ReturnedType { get; }

    public abstract SqlType[] SqlTypes { get; }
}