FDO RFC48: Easing some of the pain of copying data to SQL Server

The next beta of FDO Toolbox will come bundled with a beta version of FDO 3.6.0

The reason for the move is because FDO 3.6 incorporates RFC48, which provides APIs allowing us to correct the orientation of polygons.

Where is this API most relevant? Copying data to SQL Server 2008.

For those of us who fell for the "Microsoft Technology v1.0" trap (because frankly Microsoft never gets things right on the first attempt!), SQL Server has strict rules regarding geography types (FDO will create geography data types if your FDO Geometric Property uses lat/long coordinates). If you try to insert polygons with invalid orientation into a geography column, SQL Server will automatically reject the input. A post-insert MakeValid is not possible.

This basically meant that copying polygons (with lat/long coordinates) into SQL Server had a high chance of failure, with no way to correct the problem.

Apparently the next version of SQL Server (codenamed "Denali") will be more lenient with regards to geometry correctness (among other things) rendering this point moot. But that is the future, and we are in the present! With the next beta of FDO Toolbox, RFC48 APIs will be used exclusively for copying data to SQL Server 2008, ensuring a more pleasant bulk copy experience.

These new APIs are generic, so I can look at making the geometry correction available to all providers in the future, but for now this is only for SQL Server as it is the most relevant provider requiring such services.

A PostGIS Public Service Announcement

If you use PostGIS, avoid using the OSGeo.PostGIS provider. It hasn't been maintained for quite some time now and there are many un-fixed stability and performance problems that have made this provider unsuitable for production use.

Instead, use the OSGeo.PostgreSQL provider which is included in the 2.2 release of MapGuide Open Source (currently in RC), Autodesk MapGuide Enterprise 2011, AutoCAD Map3D 2011 and recent releases of FDO Toolbox.

The OSGeo.PostgresSQL provider is built on the same robust core as the MySQL, SQL Server and other RDBMS providers, and provides better stability and reliability than the old provider.

The OSGeo.PostGIS provider is not included in the next version of FDO. The next beta of FDO Toolbox will not include this provider, as it is using the next version of FDO.

So for those still using the old provider, take this as a warning to start migrating your PostGIS connections and layers in MapGuide over to the new provider before it's too late!

Introducing: SequentialProcess

Ever tried to chain multiple calls to FdoUtil.exe using a batch file?

I did recently on a client site, and found out that for one reason or another the Task Scheduler feature of Windows Server 2008 decided that the batch file I wrote was not going to run. After trying to find out the reason why (with no success), I tried running the individual calls to FdoUtil.exe with the Task Scheduler. Lo and behold they ran!

So armed with this knowledge, I have introduced a 3rd task type into FDO Toolbox, which will be in the next beta release: The Sequential Process.

A sequential process is simply an XML document that contains a series of calls to FdoUtil.exe, with an option at each invocation to abort the process if the previous FdoUtil.exe invocation returns a non-zero result (FdoUtil.exe returns 0 on success, otherwise it returns one of the predefined values here)

So for example, you can define a sequential process like so:

1. Run a SQL command to delete some rows

2. Execute a bulk copy task file (eg. SDF to SQL Server)

3. Run a SQL command to rebuild indexes

The SequentialProcess document would look like this:

Here's what it looks like visually (yes! This will have design support):

When you run this sequential process. Each step will be run as a separate FdoUtil.exe process, with the root process waiting for a return result from this process before continuing onto the next step.

For reference, FdoUtil.exe currently supports the following commands:

• ApplySchema
• CreateDataStore
• Destroy (data store)
• DumpSchema
• CreateFile (data store)
• RegisterProvider
• UnregisterProvider
• BulkCopy
• RunTask
• ExecuteSql

The sequential process task has opened up the design space for more FdoUtil.exe commands. So this list will definitely grow in size in future releases.

And yes, the RunTask command now supports SequentialProcess documents, so theoretically speaking, you can also nest Sequential Processes and other supported task types.

As I said before this is task scheduler friendly (the main reason for introducing this feature), so you can hook this up via the task scheduler for painless daily data updates or other automated spatial data maintenance.

Using Maestro in native mode with MapGuide Open Source 2.2 / MapGuide Enterprise 2011 assemblies

The release of MapGuide Enterprise 2011 and the pending release of MapGuide Open Source 2.2 has slightly changed the game with regards to using Maestro with the official API

Here's how to do it with this release.

First go to the mapviewernet/bin directory of your MapGuide installation

The highlighted dlls are the .net assemblies we need to strong name. Because of RFC68, the previous MapGuideDotNetApi.dll is now split into 5 separate assemblies. The MapGuideDotNetApi.dll in this installation is now a compatibility stub that redirects to the 5 assemblies.

To be able to use these assemblies in Maestro, we need to do the following:

• Sign all these assemblies with the Maestro public key
• Setup binding redirection for this new version of MapGuideDotNetApi.dll in web.config/app.config

Unfortunately, the current method using Signer.exe does not work because it doesn't properly handle assemblies with TypeForwardedTo attributes (as is the case with the new MapGuideDotNetApi.dll) nor does it handle updating inter-assembly references to signed equivalents, so to actually sign these assemblies, we need to use the ildasm.exe and ilasm.exe utilities that come with the .net Framework SDK.

So first, copy the aforementioned assemblies into a temporary folder

Rename MapGuideDotNetApi.dll to MapGuideDotNetApi-2.2.dll

Now open up a SDK command prompt into this folder and issue the following commands:

• ildasm /all /out=MapGuideDotNetApi-2.2.il MapGuideDotNetApi-2.2.dll
• ildasm /all /out=OSGeo.MapGuide.Foundation.il OSGeo.MapGuide.Foundation.dll
• ildasm /all /out=OSGeo.MapGuide.Geometry.il OSGeo.MapGuide.Geometry.dll
• ildasm /all /out=OSGeo.MapGuide.MapGuideCommon.il OSGeo.MapGuide.MapGuideCommon.dll
• ildasm /all /out=OSGeo.MapGuide.PlatformBase.il OSGeo.MapGuide.PlatformBase.dll
• ildasm /all /out=OSGeo.MapGuide.Web.il OSGeo.MapGuide.Web.dll

Your file listing should now look like this:

Delete the existing dll files, because ilasm.exe will rebuild them.

Before we continue, let's sidestep for a moment.

Here is a screenshot of a reflector dump of the original MapGuideDotNetApi.dll

Notice the PublicKeyToken=null part of the referenced assembly. The original MapGuideDotNetApi.dll is referencing un-signed assemblies (obviously). So when we rebuild MapGuideDotNetApi.dll with ilasm.exe, we have to make sure that it is referencing the signed versions of these assemblies, otherwise the you will get errors due to failing to locate the strongly-named assembly.

Also note that some OSGeo.MapGuide assemblies reference other OSGeo.MapGuide assemblies, thus we need to ensure that all references need to be updated. For reference, the dependency chain is as follows:

• OSGeo.MapGuide.Foundation references nothing
• OSGeo.MapGuide.Geometry references OSGeo.MapGuide.Foundation
• OSGeo.MapGuide.PlatformBase references OSGeo.MapGuide.Foundation and OSGeo.MapGuide.Geometry
• OSGeo.MapGuide.MapGuideCommon references OSGeo.MapGuide.Foundation, OSGeo.MapGuide.Geometry and OSGeo.MapGuide.PlatformBase
• OSGeo.MapGuide.Web references OSGeo.MapGuide.Foundation, OSGeo.MapGuide.Geometry and OSGeo.MapGuide.PlatformBase

Download a copy of the maestroapi.key into this directory.

Now in the same command prompt, rebuild and sign the OSGeo.MapGuide.Foundation assembly using ilasm.exe:

• ilasm /dll /key=maestroapi.key OSGeo.MapGuide.Foundation.il

Take a look at this assembly in reflector.

Note the public key token, it will be the same for the other signed assemblies that we will sign. We need to update the IL of MapGuideDotNetApi and others to include this public key token in their assembly references.

Open the MapGuideDotNetApi-2.2.il in a text editor.

Locate the relevant section for the IL files of the other assemblies and update them with the same public key token. Once that is done, rebuild and sign them all with ilasm.exe

• ilasm /dll /key=maestroapi.key OSGeo.MapGuide.Geometry.il
• ilasm /dll /key=maestroapi.key OSGeo.MapGuide.MapGuideCommon.il
• ilasm /dll /key=maestroapi.key OSGeo.MapGuide.PlatformBase.il
• ilasm /dll /key=maestroapi.key OSGeo.MapGuide.Web.il
• ilasm /dll /key=maestroapi.key MapGuideDotNetApi-2.2.il

Your assemblies are now properly signed and referencing the signed versions of their needed assemblies.

Now these are ready to be used in Maestro.

Copy all the files from mapviewernet/bin (except for MapGuideDotNetApi.dll) into your Maestro installation.

Now copy all the signed assemblies from our temp directory into your Maestro installation, overwrite the existing assemblies with our signed ones.

Now edit your app.config/web.config to redirect the currently referenced MapGuideDotNetApi.dll to our signed version:

And Maestro in Native mode will correctly use the assemblies we just signed.

Note that for MapGuide Enterprise 2011, the assembly version number is different but the process is the same.

Upon the proper release of MapGuide Open Source 2.2, I'll post the signed assemblies here so you won't have to follow this long process :D

The expressive power of MapGuide Tooltips redux

Over a year ago, I wrote about how you can have really powerful tooltips in MapGuide with some knowledge of HTML and the FDO expression language, with one major caveat:

You can't have interactive content in the AJAX viewer.

Well I've finally tackled this problem and implemented "sticky" tooltips for the AJAX viewer, so now you can have your Google Charts or Youtube videos in your AJAX viewer tooltips and be able to interact with them

I'm hoping to get this in for the 2.2 release of MapGuide Open Source. If you can't wait, and know how to apply diffs, you can grab the patch here

Using FDO XML configuration to reduce the attack surface of your data store (and improve performance)

Consider the following SQL Server database:

The screenshot isn't big enough to show it, but there are 250 feature classes in this schema. Sometimes for security purposes, you don't exactly want to make all 250 feature classes visible to any FDO client application (eg. MapGuide). This is commonly known as reducing the attack surface.

With FDO XML configuration and the Unified Data Store editor, you can override the default logical schema (250 classes) with something that is more stripped down (like say: 10 classes)

To do this, we once again use the unified data store editor

Now select the feature classes you don't want visible and remove them (for ease of use, you can remove a feature class by hitting the Delete key as long as the class node is selected)

Now for the important bit: Do not apply the changes! Doing so will actually delete these feature classes from the underlying data store which we do not want! We just want to hide them.

Instead, we export this current state of the logical schema to an XML configuration file

Now if we make another connection with this XML configuration file

We see that the connection takes on the logical schema as defined in our XML configuration file.

Another benefit of a reduced attack surface is performance. Here's the schema walk of the 250 class connection (LargeDb) and the configured one (LargeDb_compacted):

Although SQL Server already implements FDO RFC23, and thus schema walking is already efficient, and the times in question (as you can see from the screenshot) are already well under 10ms, you can objectively see that having a stripped down logical schema via XML configuration does indeed improve performance, which makes sense (less classes = faster loading). Now a really good test case would've been the ArcSDE provider and the Utah SGID dataset, but unfortunately the ArcSDE provider does not support XML configuration :( so that's a case of "what if?".

Another wonderful use of FDO XML configuration brought to you by the Toolbox.

Using FDO Schema Overrides Redux

When I announced beta 2 of FDO Toolbox 1.0, I briefly mentioned that the new Unified Data Store editor doubles as a visual FDO Schema Override editor.

In this post I'll illustrate how we can create FDO XML configuration documents without needing to modify a single line of XML in a text editor, using our Unified Data Store editor.

We will use the same sample SQL Server database from our previous post

Creating a connection to this via FDO Toolbox gives us the following logical schema view:

Notice once again, how the FeatId property is not identity. Now where the previous post used a text editor to tweak the logical schema, we will do the whole process visually using the unified data store editor.

We'll right click the connection and use the new Edit Data Store option

This brings up the Unified Data Store editor.

Now what we simply do, is modify the logical schema so that it matches what we want it to be. In this case, we want FeatId to be an Identity Property of VParcels. So we select the VParcels feature class, this will bring up the class definition editor. We check the FeatId property under the Identity Properties to make FeatId an Identity Property

Now comes the important bit: Do not apply the changes!

Instead we export this current state out to an XML configuration document via Export - XML Configuration Document

Now let's create another connection to the same database, but this time we'll use the XML configuration document we have just saved.

If we look at the VParcels feature class in this configured connection, you'll notice it takes on our overridden logical schema.

From here, the process is just like the previous post. In Maestro's feature source editor, you would edit the configuration document of that feature source, paste in the contents of this XML file and save it. Layers based off of the VParcels feature class will then be selectable.

As I mentioned in the beta 2 announcement this editor is not fully complete. For one thing, there is no user interface to edit the physical configuration of any Class Definitions and Property Definitions. Whatever mappings gets exported out to XML are the default mappings generated by the provider.

But for overriding the default logical schema (which I'd guess is the common scenario), the unified data store editor makes this process much simpler than the older method of editing XML files by hand.