21/02/2012

Why we’re abandoning M$ SQL Server for PostgreSQL

Yes, we’re actually working on this epical migration – and our database is huge and complex, won’t be a picnic; anyway, we have reasons, and had reasoned.
Sure, there is a big difference in licensing costs – some thousand of euros per year versus nothing, but this one alone isn’t enough – at least if you have money to ~~burn~~ invest.
Sure, T-SQL lacks of many useful features, like the ability to use composite types to declare variables, arrays, and syntactic sugar for cursor loops, but we can overcome with a good-old-fashioned bloated code.
Yes, if we talk about triggers, we have no option to implement them from a per-row perspective, that usually is more comprehensible and mantainable, nor we have support for before-triggers, nor we can define an execution order during declaration – just afterwards by calling a system stored procedure.
And no, we can’t have multiple cascade paths, but we can implement them on our own with a bunch of instead-of triggers.
And no, no boolean type, but we can use the bit one; not quite the same since we can’t combine bits with logical operators, but it does the job. We just had to wait ’till 2008 to have the ability to store dates and times separately, spending previous years by forcing one or the other part to 0 to be sure to obtain meaningful data when SELECTing, so maybe we’ll see booleans before 2020.
We still have many limitations inside FUNCTIONs, but many can be overcomed with some tricky workaround ¹.

What we can’t workaround is the impossibility of having DDL statements in concurrent isolated transactions. As you may know, you have to manually enable snapshot isolation in your SQL Server databases, or anything you can achieve is a read committed isolation level, the default for this DBMS, and deal with tons of table-locks and dead-locks; actually you’ll be able to provide a good service just if every database interaction will be almost instantaneous.
So, let’s switch the snapshot isolation on: here comes the fun.

It happens that our system relies upon materialization of hierarchical survey structures, and you just can’t execute DDL statements in isolated transactions, nor you can execute them concurrently with other isolated transactions; if you try, as a butterfly effect, every concurrent transaction, even if manipulating datas not interested by the current materialization process, will be forcibly terminated.

So now, after a month of rewriting, our procedural code in PostgreSQL has dropped in size to about a fifth of its correspondent in SQL Server, we have full snapshot-isolated interaction thanx to its awesome MVCC implementation, and we’re ready to set up the test environment on some O.S. with the X of POSIX inside and the antivirus service outside.

All’s well that ends well

T-SQL: NEWID inside a FUNCTION [↩]

16/02/2012

C#: a smarter dependency container for type hierarchies

I’m going to discuss a problem that can arise when using widely diffused Dependency Containers in a huge code bases with complex type hierarchies, and a different perspective on how to deal with it that doesn’t involve complex, verbose and error-prone dependency configurations.

(Describing what Dependency Injection is and what is a Dependency Container is beyond the scope of this article; we can just address the unaware reader to some external resources (¹ and ²)

Before going deep in the topic, I have to admit my strong background of static type checked languages, C++ particularly, and my general adversion to runtime exception for failed assertions, like failed runtime type casts: if I use a compiler and I need some particular interface in a method or class, I like to provide it objects of the least type requested, not a vague abstract interface to be down-casted at runtime; even more I feel this a kind of necessity in huge projects with a constant evolution and a really long life time, during which my memory can fail, whereas compiler will not.

After that mandatory introduction, let’s describe the scenario.
The main picture is a MVC 3 application using a custom dependency resolver, specifically StructureMap ³.
The very object of the failure is a data context, conceptually a database connection, that must exists in a single instance for every HTTP connection.
This data context is the leaf of a stratified hierarchy, composed by a tree of interfaces implemented by a correspondent tree of abstract and concrete classes.
In different parts of the library this context may (and is) referenced by any of its differents forms, either interface or class.

With StructureMap, we are forced to define different mappings for every form our leaf class can appear within our library, HTTP-context-scoped or not, leading to automatic construction of more than one instance (and also forcing us to remember or discover by failure every form of the class needed by our current application).
Obviously we can add overcome this approach by defining specific injection parameters, for each and every needed dependent class constructor, to use the specific instance provided by the container itself – my head aches just picturing that.

But what if the container would have been smarter and able to understand that, without an explicit and specific type mapping, a mapping from the root to the leaf of the hierarchy is suitable for every inner node / intermediate class ?

So we ended up in implementing this kind of logic and a simple Dependency Container, totally suitable for our needs, since we don’t need specific values; this container is so “smart” it is usually configured with two lines of code in our non-trivial applications.

A basic part of the container is a simple algorithm bound to System.Type as extension method, able to tell us if a type Derived is-a type Base, with the property that every type T is-a itself.

static class TypeExtensions
{
    // tells if a type extends another one; a type does extends itself
    public static Boolean Extends(this Type t, Type baseType)
    {
        return (selfOrBaseType != null && selfOrBaseType.IsAssignableFrom(t));
    }
    // tells if an object's type extends another type
    public static Boolean IsA(this Type t, Type selfOrBaseType)
    {
        return (o != null && o.GetType().Extends(selfOrBaseType));
    }
}

static class TypeExtensions

{

// tells if a type extends another one; a type does extends itself

public static Boolean Extends(this Type t, Type baseType)

{

return (selfOrBaseType != null && selfOrBaseType.IsAssignableFrom(t));

}

// tells if an object's type extends another type

public static Boolean IsA(this Type t, Type selfOrBaseType)

{

return (o != null && o.GetType().Extends(selfOrBaseType));

}

Relying on this simple check method, we can define a special type of Dictionary able to find an implied mapping, rather than just a key association. This dictionary contains associations from requested to provided types, the latter associated with factory methods – this adds a degree of freedom to our creation process and is consistent with a cachable fallback to a default factory method. Note that since TryGetValue is not marked as virtual, we can’t refer to this class by one of its ancestor types (no dynamic binding allowed).

/// <summary>
/// Type registry: when asked for a type T, it can answer with a type T'' registered for a type T'
/// such that T is-a T' and T'' is-a T, if not stated otherwise
/// </summary>
/// <example>B extends A implements I; register B bound to I; ask for A; get B</example>
class LambdaRegistry : Dictionary<Type, Tuple<Type, Func<Object>>>
{
    /// <summary>
    /// succeed when a direct or implied bond exists
    /// </summary>
    public bool TryGetValue(Type t, out Func<Object> result)
    {
        // look for a specific binding
        Pair<Type, Func<Object>> rt = null;
        if (base.TryGetValue(t, out rt)) {
            result = rt.Second;
            return true;
        }
        // look for an implied binding
        foreach (var x in this) {
            if (t.Extends(x.Key) && x.Value.Item1.Extends(t)) {
                result = x.Value.Item2;
                return true;
            }
        }
        // fail
        result = null;
        return false;
    }
}

/// <summary>

/// Type registry: when asked for a type T, it can answer with a type T'' registered for a type T'

/// such that T is-a T' and T'' is-a T, if not stated otherwise

/// </summary>

/// <example>B extends A implements I; register B bound to I; ask for A; get B</example>

class LambdaRegistry : Dictionary<Type, Tuple<Type, Func<Object>>>

{

/// <summary>

/// succeed when a direct or implied bond exists

/// </summary>

public bool TryGetValue(Type t, out Func<Object> result)

{

// look for a specific binding

Pair<Type, Func<Object>> rt = null;

if (base.TryGetValue(t, out rt)) {

result = rt.Second;

return true;

}

// look for an implied binding

foreach (var x in this) {

if (t.Extends(x.Key) && x.Value.Item1.Extends(t)) {

result = x.Value.Item2;

return true;

}

// fail

result = null;

return false;

}

At this point, we are ready to realize the backbone of our Dependency Container: in our library it’s obviously a bit more complex since it can deal with singletons, named and scoped instances, various flavours type and factory method registrations, etc.
The basic instantiation algorithm however is realized by a pair of mutually recursive methods; the entry point is GetInstanceOrCreate, that looks up in an instance of the registry defined above to look for a suitable factory method; if no entry is found, it falls back to a default factory method, named BuildInstance that relies on reflection to build an object instance. BuildInstance, in turn, uses GetInstanceOrCreate to obtain instances of parameters for non-empty constructors.
Error handling has completely been omitted for code clarity.

Note that using the factory method as a default fallback allows us to just omit trivial type bindings and safelly use the InstanceBuilder class throughout the code without relying on per-library or any kind of subsequent runtime configurations – this has been our choice.

class InstanceBuilder 
{
    /// <summary>
    /// registry of type mappings: what type to create (values.first) when something is asked for (keys), using which factory (values.second)
    /// </summary>
    private LambdaRegistry _typeLambdaRegistry = new LambdaRegistry();

    /// <summary>
    /// default factory method
    /// </summary>
    private object BuildInstance(Type t)
    {
        foreach (var ctor in t.GetConstructors()) {
            try {
                var args = new List<object>();
                foreach (var p in ctor.GetParameters()) {
                    var pDef = p.RawDefaultValue;
                    if (pDef != DBNull.Value) {
                        args.Add(pDef);
                    } else {
                        args.Add(this.GetInstanceOrCreate(p.ParameterType));
                    }
                }
                return ctor.Invoke(args.ToArray());
            } catch (Exception e) {
                // try with next constructor
                continue;
            }
        }
        // no constructor succeded, fail
        throw new InvalidOperationException("Unable to create an instance of type " + t);
    }

    public object GetInstanceOrCreate(Type type)
    {
        // search for a suitable registration for type
        if (_typeLambdaRegistry.TryGetValue(type, out factoryMethod)) {
            return factoryMethod.Invoke();
        }

        // if type is not registered anywhere, just try to build it directly
        return this.BuildInstance(type);
    }
}

class InstanceBuilder

{

/// <summary>

/// registry of type mappings: what type to create (values.first) when something is asked for (keys), using which factory (values.second)

/// </summary>

private LambdaRegistry _typeLambdaRegistry = new LambdaRegistry();

/// <summary>

/// default factory method

/// </summary>

private object BuildInstance(Type t)

{

foreach (var ctor in t.GetConstructors()) {

try {

var args = new List<object>();

foreach (var p in ctor.GetParameters()) {

var pDef = p.RawDefaultValue;

if (pDef != DBNull.Value) {

args.Add(pDef);

} else {

args.Add(this.GetInstanceOrCreate(p.ParameterType));

}

return ctor.Invoke(args.ToArray());

} catch (Exception e) {

// try with next constructor

continue;

}

// no constructor succeded, fail

throw new InvalidOperationException("Unable to create an instance of type " + t);

}

public object GetInstanceOrCreate(Type type)

{

// search for a suitable registration for type

if (_typeLambdaRegistry.TryGetValue(type, out factoryMethod)) {

return factoryMethod.Invoke();

}

// if type is not registered anywhere, just try to build it directly

return this.BuildInstance(type);

}

16/02/2012

NHibernate: mapping a PostgreSQL composite type

Out there in the web you can find some example about mapping sets of plain columns to a single C# class, but nothing about mapping a database level composite type to a corresponding C# class, so we’re going to fill the hole with a simple example.

For those loving spoilers, the goal is achieved via database literal parsing and using the generic database object type C# side.

So, let’s suppose we defined a simple composite type database side, like the following representing a pair of dates, maybe to be used as a time range:

CREATE TYPE t_date_range AS (
    d_from DATE,
    d_to DATE
);

CREATE TYPE t_date_range AS (

d_from DATE,

d_to DATE

);

A bit of PostgreSQL’ documentation digging and / or a couple of test performed will reveal us that such a type is represented by literals in this form:

-- both fields NOT NULL
'(2012-01-01,2012-12-31)'
-- one field NULL
'(,2012-12-31)'
'(2012-01-01,)'
-- both fields NULL
'(,)'

-- both fields NOT NULL

'(2012-01-01,2012-12-31)'

-- one field NULL

'(,2012-12-31)'

'(2012-01-01,)'

-- both fields NULL

'(,)'

A literal is the notation used to represent fixed values in programming languages; in PostgreSQL, literals for composite types may be resembled to formatted strings, containing a comma-separated list of values for each field of the type enclosed within parentheses; if values contains spaces, they will be enclosed within quotation marks (“); NULL values are simply omitted, thus represented by an empty sequence of characters.

Those literals will emerge from database layer as string values, to be parsed and produced in our NHibernate.UserTypes.IUserType implementation.

First of all, let’s prepare the C# type we’ll map our database type onto: a simple class with two optionals DateTimes and a set of useful constructors.

class DateRange
{
    public DateTime? From;
    public DateTime? To;

    public DateRange(DateTime? from, DateTime? to)
    {
        this.From = from;
        this.To = to;
    }

    public DateRange(DateRange toCopy)
    {
        if (toCopy != null) {
            this.From = toCopy.From;
            this.To = toCopy.To;
        }
    }

    public DateRange()
    {
    }
}

class DateRange

{

public DateTime? From;

public DateTime? To;

public DateRange(DateTime? from, DateTime? to)

{

this.From = from;

this.To = to;

}

public DateRange(DateRange toCopy)

{

if (toCopy != null) {

this.From = toCopy.From;

this.To = toCopy.To;

}

public DateRange()

{

}

Also, let’s define a couple of support functions for string manipulation that will become useful during database literal’s deserialization:

static class StringExtensions
{
    /// <summary>
    /// split a string in two around first occurence of separator
    /// </summary>
    /// <remarks>if separator is not found, first element is the whole string, and second is null</remarks>
    public static Tuple<String, String> Split2(this String s, Char sep)
    {
        var p = s.IndexOf(sep);
        if (p < 0)
            return new Tuple<String, String>(s, null);
        else
            return new Tuple(s.Substring(0, p), s.Substring(p + 1));
    }

    /// <summary>
    /// reduces a string by one character from both sides
    /// </summary>
    public static String Shrink(this String s)
    {
        return s.Substring(1, s.Length - 2);
    }
}

static class StringExtensions

{

/// <summary>

/// split a string in two around first occurence of separator

/// </summary>

/// <remarks>if separator is not found, first element is the whole string, and second is null</remarks>

public static Tuple<String, String> Split2(this String s, Char sep)

{

var p = s.IndexOf(sep);

if (p < 0)

return new Tuple<String, String>(s, null);

else

return new Tuple(s.Substring(0, p), s.Substring(p + 1));

}

/// <summary>

/// reduces a string by one character from both sides

/// </summary>

public static String Shrink(this String s)

{

return s.Substring(1, s.Length - 2);

}

Then, prepare the background for the NHibernate.UserTypes.IUserType implementation; since every similar mapping has common properties, we can put the common behaviour in an abstract class, letting actual implementation just deal with the string processing:

abstract class NHRecordType : NHibernate.UserTypes.IUserType
{
    #region Abstracts
    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 object DeepCopy(object value);
    #endregion

    #region Common
    /// <summary>
    /// cache for returned sql type(s)
    /// </summary>
    private static NHibernate.SqlTypes.SqlType[] _sqlTypes = new NHibernate.SqlTypes.SqlType[] { new NHibernate.SqlTypes.SqlType(DbType.Object) };
    /// <summary>
    /// sql type returned is a generic "object" type
    /// </summary>
    public NHibernate.SqlTypes.SqlType[] SqlTypes
    {
        get { return _sqlTypes; }
    }

    /// <summary>
    /// type is mutable, so return a deep copy
    /// </summary>
    public object Assemble(object cached, object owner)
    {
        return this.DeepCopy(cached);
    }
    /// <summary>
    /// type is mutable, so return a deep copy
    /// </summary>
    public object Disassemble(object value)
    {
        return this.DeepCopy(value);
    }
    /// <summary>
    /// type is mutable
    /// </summary>
    public bool IsMutable
    {
        get { return true; }
    }

    /// <summary>
    /// return a deep copy of original value
    /// </summary>
    public object Replace(object original, object target, object owner)
    {
        return this.DeepCopy(original);
    }

    public new bool Equals(object x, object y)
    {
        if ((x == null) || (y == null))
            return false;
        else
            return x.Equals(y);
    }

    public int GetHashCode(object x)
    {
        if (x == null)
            return 0;
        else
            return x.GetHashCode();
    }
    #endregion
}

abstract class NHRecordType : NHibernate.UserTypes.IUserType

{

#region Abstracts

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 object DeepCopy(object value);

#endregion

#region Common

/// <summary>

/// cache for returned sql type(s)

/// </summary>

private static NHibernate.SqlTypes.SqlType[] _sqlTypes = new NHibernate.SqlTypes.SqlType[] { new NHibernate.SqlTypes.SqlType(DbType.Object) };

/// <summary>

/// sql type returned is a generic "object" type

/// </summary>

public NHibernate.SqlTypes.SqlType[] SqlTypes

{

get { return _sqlTypes; }

}

/// <summary>

/// type is mutable, so return a deep copy

/// </summary>

public object Assemble(object cached, object owner)

{

return this.DeepCopy(cached);

}

/// <summary>

/// type is mutable, so return a deep copy

/// </summary>

public object Disassemble(object value)

{

return this.DeepCopy(value);

}

/// <summary>

/// type is mutable

/// </summary>

public bool IsMutable

{

get { return true; }

}

/// <summary>

/// return a deep copy of original value

/// </summary>

public object Replace(object original, object target, object owner)

{

return this.DeepCopy(original);

}

public new bool Equals(object x, object y)

{

if ((x == null) || (y == null))

return false;

else

return x.Equals(y);

}

public int GetHashCode(object x)

{

if (x == null)

return 0;

else

return x.GetHashCode();

}

#endregion

}

Finally, here’s the serialization and deserialization logic, pretty straightforward after all of the above background preparation:

class NHDateRange : NHRecordType
{
    /// <summary>
    /// date format on database
    /// </summary>
    private static readonly String FMT_DATE_READ = "yyyy-MM-dd";
    /// <summary>
    /// format we use to write data on db, to provide to a String.Format
    /// </summary>
    private static readonly String FMT_DATE_WRITE = "\"{0:yyyy-MM-dd}\"";

    /// <summary>
    /// deep copy by cloning
    /// </summary>
    public override object DeepCopy(object value)
    {
        if (value == null)
            return null;
        else
            return ((DateRange)value).Clone();
    }

    /// <summary>
    /// returned type is BO.Library.DateRange
    /// </summary>
    public override Type ReturnedType
    {
        get { return typeof(DateRange); }
    }

    /// <summary>
    /// database literal deserialization implementation
    /// </summary>
    public override object NullSafeGet(System.Data.IDataReader rs, string[] names, object owner)
    {
        // record type literals are strings, get value using NHibernate method
        object v = NHibernate.NHibernateUtil.String.NullSafeGet(rs, names[0]);
        if (v == null) return null;

        try {
            var s = v.ToString();
            var p = s.Shrink().Split2(',');   //format is (x?,y?); remove parenthesis and split
            var rt = new DateRange();
            if (!String.IsNullOrWhiteSpace(p.Item1))
                rt.From = DateTime.ParseExact(p.Item1, FMT_DATE_READ, CultureInfo.CurrentCulture);
            if (!p.Second.IsNullOrWhiteSpace())
                rt.To = DateTime.ParseExact(p.Item2, FMT_DATE_READ, CultureInfo.CurrentCulture);

            return rt;
        } catch (Exception e) {
            throw new InvalidBusinessDataException(String.Format("Invalid literal {0} for a date range", v), e);
        }
    }

    /// <summary>
    /// utility to generate a field of the whole literal
    /// </summary>
    private static String FormatField(DateTime? d)
    {
        if (d.HasValue) {
            return String.Format(FMT_DATE_WRITE, d.Value);
        } else
            return String.Empty;
    }
    /// <summary>
    /// serialization to database literal
    /// </summary>
    public override void NullSafeSet(System.Data.IDbCommand cmd, object value, int index)
    {
        if (value == null)
            NHibernate.NHibernateUtil.String.NullSafeSet(cmd, value, index);
        else {
            var r = (DateRange)value;
            var s = String.Format("({0},{1})", FormatField(r.From), FormatField(r.To));
            ((IDataParameter)cmd.Parameters[index]).Value = s;
        }
    }
}

class NHDateRange : NHRecordType

{

/// <summary>

/// date format on database

/// </summary>

private static readonly String FMT_DATE_READ = "yyyy-MM-dd";

/// <summary>

/// format we use to write data on db, to provide to a String.Format

/// </summary>

private static readonly String FMT_DATE_WRITE = "\"{0:yyyy-MM-dd}\"";

/// <summary>

/// deep copy by cloning

/// </summary>

public override object DeepCopy(object value)

{

if (value == null)

return null;

else

return ((DateRange)value).Clone();

}

/// <summary>

/// returned type is BO.Library.DateRange

/// </summary>

public override Type ReturnedType

{

get { return typeof(DateRange); }

}

/// <summary>

/// database literal deserialization implementation

/// </summary>

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

{

// record type literals are strings, get value using NHibernate method

object v = NHibernate.NHibernateUtil.String.NullSafeGet(rs, names[0]);

if (v == null) return null;

try {

var s = v.ToString();

var p = s.Shrink().Split2(','); //format is (x?,y?); remove parenthesis and split

var rt = new DateRange();

if (!String.IsNullOrWhiteSpace(p.Item1))

rt.From = DateTime.ParseExact(p.Item1, FMT_DATE_READ, CultureInfo.CurrentCulture);

if (!p.Second.IsNullOrWhiteSpace())

rt.To = DateTime.ParseExact(p.Item2, FMT_DATE_READ, CultureInfo.CurrentCulture);

return rt;

} catch (Exception e) {

throw new InvalidBusinessDataException(String.Format("Invalid literal {0} for a date range", v), e);

}

/// <summary>

/// utility to generate a field of the whole literal

/// </summary>

private static String FormatField(DateTime? d)

{

if (d.HasValue) {

return String.Format(FMT_DATE_WRITE, d.Value);

} else

return String.Empty;

}

/// <summary>

/// serialization to database literal

/// </summary>

public override void NullSafeSet(System.Data.IDbCommand cmd, object value, int index)

{

if (value == null)

NHibernate.NHibernateUtil.String.NullSafeSet(cmd, value, index);

else {

var r = (DateRange)value;

var s = String.Format("({0},{1})", FormatField(r.From), FormatField(r.To));

((IDataParameter)cmd.Parameters[index]).Value = s;

}