Saturday, April 30, 2011

Postgresql pain point on ORMs

Is it a breaking changes if Postgresql will make their table and field names case-insensitive? Postgresql table and field cases automatically folded to lowercase hurts badly on ORMs

Use this for the meantime:

public static string ToLowercaseNamingConvention(this string s, bool toLowercase)
{
    if (toLowercase)
    {
        var r = new Regex(@"
        (?<=[A-Z])(?=[A-Z][a-z]) |
         (?<=[^A-Z])(?=[A-Z]) |
         (?<=[A-Za-z])(?=[^A-Za-z])", RegexOptions.IgnorePatternWhitespace);

        return r.Replace(s, "_").ToLower();
    }
    else
        return s;
}


Sample here: http://code.google.com/p/fluent-nhibernate-lowercase-system/source/checkout

2 comments:

  1. I'm not familiar with NHibernate, but why does it not quote identifiers if it is case sensitive?

    ReplyDelete
  2. IEnableMuch said...
    Unfortunately, NHibernate doesn't quote tables and columns by default. Perhaps they are of the thinking that the majority of Postgresql users are naming their tables and columns in lowercase, so constructing query would not be a tedious chore. e.g. SELECT * FROM Person WHERE Lastname = 'Hejlsberg' and Firstname = 'Anders', select * from lastname = 'hejlsberg' and firstname = 'anders' will do too; whereas if those Postgresql users make their DDL like this CREATE TABLE "Person"("Lastname" text, "Firstname" text), querying could induce RSI, i.e. SELECT * FROM "Person" WHERE "Person"."Lastname" = 'Hejlsberg' AND "Person"."Firstname" = 'Anders'. Unquoting them in ORMs will let Postgresql fold the table and column names to lowercase.

    The ToLowercaseNamingConvention function, aside from making names lowercase, it also put an undescore on compound words, e.g. AddressCity to address_city, PostComment to post_comment

    However, there's a solution with FNH(via INamingStrategy) if you are using uppercase in your table and column names: http://manfredlange.blogspot.com/2011/04/fluent-nhibernate-postgresql-and.html

    ReplyDelete