Wednesday, October 24, 2012

NHibernate Spatial (Part 5 - NHibernate 3.3.1+)

NHibernate Spatial is sort-of moribund, which is rather unfortunate because it's a really great project with lots of potential. Anyway, not conformed with it I decided to put the sucker onto Github to create an unofficial working version of it.

Fortunately some lads had the same idea avoiding me the extra-work. Unfortunately their projects don't have that much activity and most don't seem to really work. Anyway, in the spirit of GitHub and collaboration I decided to fork one of these to try my luck. Also, if time permits, I'll try to maintain it for future NHibernate releases.

I've forked the following project: The author already set it up nicely, making the following changes to the broken nhcontrib version:
  • Fixed compilation errors
  • Used NuGet for some references
  • Upgraded the projects from VS2008 to VS2010
So, props for suryapratap for providing a much nicer starting point. My own fork is at: where I'm going to update this library.

I've started by:
  • Fixing some references. Some were directed to the "lib" folder, which no longer exists. I've directed the missing references to NuGet versions, except the "NetTopologySuite.TestRunner" stuff, which I included as a project (downloaded from NTS homepage).
  • Renaming the projects, removing the Visual Studio version sufix
  • Beginning to fix the Oracle version (still doesn't compile, and for now is removed from the main solution)
  • Updating some code/configuration to reflect newer NHibernate versions, like removing "NHibernate.ByteCode.Castle" from the equation, as NHibernate (since 3.2) now includes its own embedded proxy generator.
  • Try to get it working :)
So, now I'm going to run the unit-tests (for Sql 2008) to see how it fares. Thus I need to:
  • Create an empty database named nhsp_test -> check
  • Fix the connection strings in the test projects -> check
  • Run NUnit -> check

Well, I was actually surprised that it ran correctly at first try. Anyway, lots of errors and fails. Some are specific to Sql 2008, mainly because the NHibernate Spatial incorrectly thinks that SQL Server 2008 uses spatial metadata columns (like PostGis does). A quick fix on MsSql2008SpatialDialect.cs and now some of the tests are automatically ignored for Sql Server 2008.

Also, some tests simply can't be used for both Sql Server 2008 and other Databases. For example, there is a test that asserts that the following polygons as invalid:
Shell self-touches at vertex
Shell self-touches at non-vertex

The "problem" is that Sql Server 2008/2012 handles these polygons without any fuss. So, because of these and other differences I've created special overrides for the tests that behave differently in Sql Server. I've also removed some incorrect tests that seemed like experiments that were committed by mistake.

Running NUnit now gives this result:

Three tests are failing, but this represents indeed a bug in NHibernate.Spatial. I'm pretty sure this test passed prior to NHibernate version 3.2, so I guess some internals in NH were modified, causing the issue.

Basically the query is issued to Sql Server like this:
SELECT this_.oid as oid0_0_, as name0_0_, this_.the_geom as the3_0_0_ FROM linestringtest this_ WHERE this_.the_geom.Filter(@p0) = 1 
But the @p0 parameter is not passed.

I've added the NHibernate project to the solution to step into the implementation and try to understand the problem. After lots of debugging I finally found the code where the problem resides:

Inside the "NHibernate.Param.ParametersBackTrackExtensions" class, the method "GetEffectiveParameterLocations" was not able to backtrack the properties:
public static IEnumerable<int> GetEffectiveParameterLocations(
        this IList<Parameter> sqlParameters, string backTrackId)
    for (int i = 0; i < sqlParameters.Count; i++)
        if (backTrackId.Equals(sqlParameters[i].BackTrack))
            yield return i; // ---> NEVER RETURNED ANY VALUE HERE
After a while I was finally able to fix this bug. I'm putting it all up on my Github fork at:

Running the tests now shows no errors:

Now that I've got the project set up on Github this should be my last post regarding NHibernate Spatial on this blog. There's obviously a lot to be said, but I'll use the Github project wiki for that.

Anyway, this project is still far from complete. Although I've fixed a few bugs, some features were never implemented in the official project, and still throw a "NotImplementedException". I'm planning on implementing them and having an up-to-date wiki page that shows the working/non-working feature set of the library. The page is already setup but the content is not yet updated:

I'll also try to keep the issues and wiki pages updated as much as possible.
    I'm including binaries downloads for each NHibernate version. Currently I just have for NHibernate, but I'll update this as new versions get released.

    I'll also add a whole new set of unit-tests to the solution. They'll be separated by spatial operation type (aggregation, analysis, relation, validation) and will include versions for each query API (Criteria, HQL, LINQ, QueryOver). Obviously some tests will be redundant with existing ones, but in this case more is better.


    1. Great work Pedro!

      But I got an error using QueryOver:

      queryOver.WhereSpatialRestrictionOn(x => x.Geo).Within(extent);

      The error I got is the same you described : "Must declare the scalar variable \"@p1\"."

      It works great with older version (NH 3.1)

      Any idea?


    2. Thanks for the feedback Paul. I'll try to fix it tonight ;)

    3. Hi,
      I have the same error when trying with this sample code,

      Country country = session.CreateCriteria(typeof(Country))
      .Add(SpatialExpression.Contains("Boundaries", new Point(-70.40, -33.24)))
      .UniqueResult() as Country;

      InnerException: Must declare the scalar variable "@p0".

    4. Thanks Pedro. Will also take a look at that. The problem is that I thought the unit tests covered the NHibernate features pretty well. Doesn't seem to be the case though...

    5. Guys,

      Both problems have been fixed and I've committed the changes to Github.

      Also, I've added new unit-tests for "within" and "contains" in class

      1. Pedro,
        It works fine now.
        Thank you very much for your good job and your quick response.
        Before I found your blog, I was in this other I have tested his code too and throws the same errors.
        De nuevo obrigado.

      2. Worked great Pedro!
        Thank you!

    6. Pedro,
      you are GREAT!
      Thank you very much for your good job.


    7. Hello Pedro...

      Is it working with the NHibernate 3.3.3?