[mapguide-users] SQL Server 2008 views and fdo again

[Brent Robinson]

Hi,

Tickets 469 and 470 have been fixed in FDO 3.4 but after 3.4.1. Therefore, these fixes will be present in FDO 3.5. There might be a future 3.4.2 release, which would contain the fixes, but there is no definite plan yet. The ticket 470 fix is required for the clustered index fix to work. Without it, the SQLServerSpatial provider ignores any indexes on views. It sounds like you have a workaround for the short term but if you'd like to try out these fixes before 3.4.2 or 3.5, we could arrange to get you a patched provider.

The config doc alternative is a lot more complicated, but fortunately, you won't need a feature source for each layer, since the config doc can specify multiple classes. One approach, which would minimize the work would be to create 2 feature sources for your datastore.

	- one feature source would have a config doc that defines classes only for the views whose identity 
	properties need to be specified via the config document. This feature source would only be used when 
	creating a layer for one of these views. When the underlying schema changes, the changes must also be 
	reflected in the config document. Therefore, limiting the number of classes it contains, limits the 
	amount of required changes to the config document going forward.

	- a second feature source with no config doc. This would be used for accessing all other views and 
	tables in the datastore.

One question about the examples below. Does tablea have a primary key or unique index? For the first view:

	create view v1 as select col1, col2, geog from tablea;

any index on the view would be ignored (in FDO 3.4.1 and before), but if the table has a primary key or unique index then it will be used to determine the view's identity. This will happen as long as the view is based on a single table. For the other view:

	create view v1 as select col1 as Column1, col2 as Column2, geog as geog from
	tablea;

the provider does a sanity check to ensure that the primary key or unique index columns are present in the view. The check is done by name so, if col1 or col2 are in the key, the column aliasing fools it into thinking they are not in the view, and the key is not used to determine the view's identity. The SQL Server data dictionary tracks view->table dependencies but I'm not sure if there's something similar for dependencies between individual columns. For this reason, the provider falls back to relying on names. For FDO 3.4.2 and after, the clustered index should work for this view.

Brent.

-----Original Message-----
From: mapguide-users-bounces at lists.osgeo.org [mailto:mapguide-users-bounces at lists.osgeo.org] On Behalf Of carc
Sent: Tuesday, January 12, 2010 6:42 PM
To: mapguide-users at lists.osgeo.org
Subject: Re: [mapguide-users] SQL Server 2008 views and fdo again


Just a quick update. Owing to time constraints we have shelved using views
with joins and gone with a called stored procedure, populate temp table
approach (shudder...it is 2010!) Even once we decided to go with this
approach we hit some surprising road blocks that took us awhile to track
down. In the end the culprit turned out to be column aliases in the view
definition.

So this works:

create view v1 as select col1, col2, geog from tablea;

Assuming you also create a unique clustered key on the view.

This does not work:

create view v1 as select col1 as Column1, col2 as Column2, geog as geog from
tablea;

Even with the clustered unique index on the view this does not work.

As surprising as it seems that is what we concluded. If we have more time in
the future we may dig a little more into using the config document to solve
the problem even though I am not that hyped on the idea of creating a
feature source for every layer.

Thanks for the tips Jason.

CA
-- 
View this message in context: http://n2.nabble.com/SQL-Server-2008-views-and-fdo-again-tp4273589p4295305.html
Sent from the MapGuide Users mailing list archive at Nabble.com.
_______________________________________________
mapguide-users mailing list
mapguide-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapguide-users