Friday, 6 August 2010

Using FDO Schema Overrides

One of the more esoteric features of the FDO API is the ability to apply schema overrides via an XML configuration file.

Unfortunately this feature is not really well documented, so this blog post will hopefully make it much easier to understand how to use this feature.

What are Schema Overrides?

Schema Overrides allow you to define or override the following aspects of a FDO data source or MapGuide Feature Source:
  • The spatial contexts for this data source
  • The logical schema (schema/class/properties) of this data source
  • The mapping of this logical schema to the underlying physical storage (class -> table, property -> column)
This information is specified in a XML-based configuration file

How are Schema Overrides used?

For FDO providers like ODBC, there is no actual concept of:
  • Geometry properties
  • Spatial Contexts
In this case, schema overrides can be used to "declare" point geometry properties (based on X/Y/Z columns) and what spatial context these points represent. The ODBC feature source table editor in MapGuide Studio/Maestro is a specialized editor of this kind of schema overrides.

Another use, which I think is not quite known is to override how FDO interprets the tables and views in your database, especially views.

A practical example

One of the common needs in any presentation of data is the ability to have one set of data linked to another set of data. In MapGuide, Feature Joins can be used to achieve this effect.

But the performance of Feature Joins can range anywhere between fair and excruciatingly slow (especially when themes are involved), and as such I generally do any of the following:
  1. Pre-join the related bits of data before loading it into MapGuide
  2. If both sets of data come from the same database, do the join at the database level and encapsulate the result in a view.
Our example covers point #2.

Consider the following SQL Server database:

























Suppose we want to have a view (called VParcels) that represents the Parcel tables and any related records from the ParcelData table. You would normally do a join similar to this:




















Now if we look at this database through FDO Toolbox, you'll now see that our view is represented as a feature class:

















But we have a problem, if we make a layer off of this we can't select any objects on it. Look at the ID property:




















It isn't an identity property. If we create a layer from this feature class we won't be able to select objects on it because identity properties are the mechanism by which MapGuide knows which particular objects have been selected. In fact, 90%-95% of most problems where layers aren't selectable can be boiled down to a violation of this one basic rule:

No identity properties = unselectable layers

So now we know the problem, how can we fix it? We could index this view, by applying a unique clustered index (the only valid index that can be applied to a SQL server view, other DBMSes may be different), FDO (for SQL Server) will interpret the index as the identity properties.

But a unique clustered index requires that the view only use inner joins which may be a problem especially if you want to show parcels that have no related records (ie. a left outer join).

Enter FDO Schema Overrides to the rescue.

FDO Toolbox 0.9.6 introduced a new command that allows you to dump the default schema mapping to an XML file. What we will do is dump a default XML configuration, and then do the following:
  • Edit this configuration file
  • Test this configuration in FDO Toolbox
  • Loading this configuration file to MapGuide
  • Testing this configuration in MapGuide
So it goes without saying, you'll need FDO Toolbox v0.9.6 or later to do this.

If you right click our SQL Server connection, you will see a new Dump Schema Mapping command on the context menu. This command will create an XML configuration file containing the following information:
  • All the spatial contexts in this data store
  • The entire FDO logical schema
  • The logical to physical schema mapping
If you open this file it will look something like this (outline view):











All XML configuration files have the same structure:
  • They all start and end with the fdo:DataStore element
  • All spatial contexts are indicated with the gml:DerivedCRS element
  • The xs:schema elements indicate the FDO logical schema
  • Finally the SchemaMapping elements indicates how the logical schema maps to the physical schema. These elements are provider specific
Look at the logical schema section and you'll notice a pattern:
  • Feature Classes are denoted by the xs:complexType elements
  • Identity Properties of the Feature Class are denoted by the xs:element element declared directly above the matching xs:complexType element.





















If you look closely there is no xs:element declaration for the xs:complexType of our view:

So let's override this default logical schema by declaring an identity property for our view. The xml fragment follows this pattern:

<xs:element name="CLASS_NAME" type="CLASS_NAMEType" abstract="false" substitutiongroup="gml:_Feature">
<xs:key name="CLASS_NAMEKey">
<xs:selector xpath=".//CLASS_NAME" />
<xs:field xpath="IDENTITY_PROPERTY_NAME" />
</xs:key>
</xs:element>

Where CLASS_NAME is the FDO feature class name and IDENTITY_PROPERTY_NAME is the name of the identity property in the FDO feature class. We'll add one such fragment for VParcels:



















Now let's save it and load up another SQL Server connection to the same database. You'll notice that the express dialog has a field allowing you to specify an XML configuration file. We'll use our saved configuration file.














Now connect and take a look at VParcels from the configured connection:























We now have identity properties for our view! Assuming we have an existing SQL Server feature source (without a configuration). Let's run a validation test on it, you'll get something like this:


















Notice that MapGuide also can't get the identity properties for VParcels (because it too uses FDO). Now let's edit the configuration of this feature source. In the feature source editor for Maestro, click Edit Configuration Document and we'll get an XML editor, paste the contents of our XML configuration file in here and save it.




















Now let's try validating again:


















That problem is gone. We have now successfully applied a schema override! MapGuide can now read the identity properties from our view. Just to doubly make sure, let's see if we can make a selectable layer now. I'll make one themed by whether there are related records (black = has related record, red = no related record). Remember that our view is based off of a left outer join so there may be parcels that have no related records.






















Now let's house it in a map (switch on selectability) and preview it.



















We now have a selectable view-based layer! Now having selectable views comes with a data modeling caveat:

We are only declaring these properties/columns to be unique to MapGuide. You have to ensure at the DBMS level (via constraints or other means) that the actual columns declared will be unique, otherwise selecting a feature may not return the correct set of attributes that you were expecting!

Hopefully this better explains how you can use schema overrides to change how FDO/MapGuide looks at your data store. I'll eventually have some kind of visual editor for FDO Toolbox so you don't have to do any painstaking XML editing by hand.

On a side note, as I've demonstrated with this post FDO Toolbox is a very useful tool for diagnosing problems with MapGuide Feature Sources and other problems that may not be readily apparent through looking at error logs. Almost every time I do a (windows) deployment of MapGuide (Open or Enterprise) I'll always install a copy of FDO Toolbox (and Maestro) as well, because having something on hand to be able to look at your data is very useful.

19 comments:

Ivan said...

Hello Jackie,

can we do the same with "Manage Schema mappings" option?

I was trying to solve my problem following your steps but it seems that XML document is pretty much different (the structure of XML) than yours.

Jackie Ng said...

The SchemaMapping part of the XML document varies from provider to provider. The *.Overrides.dll contain the logic to read/write these specific fragments.

Also the "Manage Schema mappings" option is broken atm. It will be merged into the existing schema editor, which itself will be tweaked before the final release

Ivan said...

Yes, I have noticed that MSM is broken. It's weird, I'm using the same provider as you, MS SQL 2008 Spatial..

Check this out zipped xml file.

I have view vObjectVer where is objectId "unique" and should be an ID. I tried everything, followed your steps, declaring "objectId" as identityPropertyName in complex type tag - but unsuccessful.

If you can take a look I would appreciate it.

Thank you.

Ivan said...

Jackie,

I've just downloaded 0.9.6 version and duped schema mapping is pretty much different than schema from 1.0 beta.

I will try with 0.9.6.

Ivan said...

Oh Jackie, I'm so sorry :-)

Stupid Visual Studio did not formatted well XML and I saw incomplete file :/

Everything is absolutely perfect! Thank you!

kcheng007 said...

Thx Jackie....

kalmy said...

I tried to override schema with new 1.0.0.1200 Toolbox but when I try to load in new Maestro 3.0 I got a validation error .
"Object reference not set an instance of an object" and can't save . I use MGE 2012,sql server 2008 R2.

erictsn said...

I downloaded the latest version FDO Toolbox 1.0.0.1227. I can't to find the Dump Schema Mapping command on the context menu. Where is the command? Thanks.

Jackie Ng said...

Since 1.0 beta 2, the "Dump Schema Mappings" command is renamed to "Export Data Store Configuration to XML" to reflect its proper function

Rizzo said...

Hi Jackie,

I'm having a little trouble with this. I created a view, but none of my views are listed in FDO Toolbox. In fact, the only tables listed are the feature class tables. Any idea what I'm doing wrong?

Jon

Rizzo said...

Hi Jackie,

I'm having a little trouble with this. I created a view, but none of my views (or other tables) are listed as feature classes in FDO Toolbox. I can see my other feature classes, just not my custom tables & views. Any idea what I'm doing wrong?

Jon

Jackie Ng said...

Does this database have FDO metadata tables (ie. tables that start with f_)?

If so, this technique will not work for you.

Rizzo said...

Yes, it is an FDO database. I didn't think there was any other way to store polygons in SQL Server using FDO. I was able to get it to work (sort of) by manually creating the FDO metadata for my view. Not as elegant as I would like, but it seems to work well enough for what I need.

FirecassidyBlogler said...

Hi Jackie,

Tried to get a dump of a SQL Server 2008 schema using the latest FDO Toolbox (tried both x64 & x86). It tosses an error -
Exception Occurred: System.Collections.Generic.KeyNotFoundException. The given key was not in the dictionary. This is against MGOS 2.5. This is when using the "Export Data Store Configuration" command

Brandon said...

I have the same error as the above post. Trying to export the xml config after selecting a primary key for a postgis view. MG 2.5.1 x64, FDO TB x64, postgis 1.5.5 all on windows.

Brandon said...

I have the same error as the above post. Trying to export the xml config after selecting a primary key for a postgis view. MG 2.5.1 x64, FDO TB 1.2 x64, postgis 1.5.5, all on windows.

Craig Gers said...

I would like to thank you for the informative blog. It is the only article on this subject that I have been able to source on the Internet.

Could you please give me some pointers.

I am using MGOS on Oracle 11.0.2.4. I am getting many layers in Maestro validation that are similar to the message below.

Warning - Warning_MapDefinition_UnselectableLayer: Layer SP_SEWGPIPE_C is marked as selectable but its feature class KingOra:JWATER~SP_SEWGPIPE_C~GEOMETRY in (Library://Jwater/Data/data_conn_jwater.FeatureSource) has no identity properties. This layer will not be selectable.


I believe that this error arises because I do not have identity properties specified.


I have not been able to pull out the xml Schema document you describe? All the data are tables and not spatial views as in your example.

I would appreciate any pointers you may have. The Select Tool on many layers is not working as a result of this problem and I do not know how to fix this problem.


Thank you very much in anticipation.

Regards
Craig

Brandon said...

@Craig

I solved this by deleting the non-spatial tables in FDO toolbox (ie deleting them in the schema, not the database) and exporting the schema without them. Is there a reason you need to see them in Maestro? If you are trying to do joins there, its probably best done on the server side using views. That's what I did and I then assigned primary keys to the spatial views, as detailed in Jackie's post. Works a treat.

Craig Gers said...

I have managed solve the problem in Oracle.....

Here is the solution. Oracle requires that you have a Primary Key Constraint on the spatial / geometry data.

So here is what I have done...

By way of my naming conventions and assumptions:
1) I always have a field named GID which is unique and populated by a trigger.
You can use an existing data field, however there MUST be a unique field AND that field must have a Primary Key constraint.

2.1) The spatial data has metadata created for it.

2.2) The name for the spatial index (which exists) is SP_TableName_$X.

(e.g. SP_SEWCATCHMENT_$X).
-- THE SQL FOR THE CREATE INDEX WILL BE SOMETHING LIKE THIS....
CREATE INDEX GIS.SP_SEWCATCHMENT_$X
ON GIS.SP_SEWCATCHMENT(GEOMETRY)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS('sdo_indx_dims=2, tablespace=GISINDEX, work_tablespace=GISINDEX');

3) The geometry column is ALWAYS ALWAYS named GEOMETRY.

4) All spatial tables in Oracle begins with a table name of 'SP_'


My example will use a spatial layer called.... SP_SEWCATCHMENT.

--1) Check for a GID;

DESC SP_SEWCATCHMENT
Name Null Type
--------- -------- --------------
GEOM_AREA NUMBER
COMMENTS VARCHAR2(150)
TAG_X NUMBER
TAG_Y NUMBER
TAG_VALUE VARCHAR2(40)
TAG_SIZE NUMBER
TAG_ANGLE NUMBER
TAG_JUST CHAR(2)
GEOMETRY SDO_GEOMETRY()
SUB_BASIN VARCHAR2(9)
OUTFALL VARCHAR2(30)

--2) Create the GID
ALTER TABLE SP_SEWCATCHMENT
ADD
(
GID NUMBER(38,0)
);

--3) Pupulate the GID with Values from a sequence, in my case it is called Munseq_GID.

UPDATE SP_SEWCATCHMENT
set GID = Munseq_GID.nextval;
commit;

--4) Build an index on the GID

CREATE UNIQUE INDEX SP_SEWCATCHMENT_GID ON SP_SEWCATCHMENT(GID);


--5) Add the Primary Key constraint, without which you cannot select FEATURES in map guide.
ALTER TABLE SP_SEWCATCHMENTADD CONSTRAINT SP_SEWCATCHMENT_PK PRIMARY KEY (GID) USING INDEX SP_SEWCATCHMENT_GID ;

commit;

--5) You can now add the data to MGOS and it will be selectable and you wont get a warning something like this...

Warning - Warning_MapDefinition_UnselectableLayer: Layer WATER is marked as selectable but its feature class KingOra:GIS~SP_SEWCATCHMENT~GEOMETRY in (Library://Jwater/Data/data_conn_gis.FeatureSource) has no identity properties. This layer will not be selectable.

Hope this helps someone.