= FDO RFC 33 - SQL Pass-Through Feature Reader Support =

This page contains an change request (RFC) for the FDO Open Source project.  
More FDO RFCs can be found on the [wiki:FDORfcs RFCs] page.


== Status == 
 
||RFC Template Version||(1.0)|| 
||Submission Date|| April 9, 2009 ||
||Last Modified|| Greg Boone [[Timestamp]]||
||Author||Greg Boone||
||RFC Status||Not Ready|| 
||Implementation Status||Pending||
||Proposed Milestone||3.5.0.0||
||Assigned PSC guide(s)||Greg Boone||
||'''Voting History'''||(vote date)||
||+1|| ||
||+0|| ||
||-0|| ||
||-1|| ||

== Motivation ==

Enable the FDO API to support SQL pass-through commands that return an FDO feature reader, referencing a proper FDO schema, not simply an FDO data reader. The feature reader will contain proper geometry properties, relations and associations. 

TODO:

-- Discuss code that used FR can use data coming back from Select or SQL !PassThrough. Cover more application cases.

== Overview ==

SQL pass-through deals with physical schema. There is no parsing and what is identified are physical schema tables, columns, functions, commands, etc. SQL can be any SQL command, not just select, but any DML (select, insert, update, delete) or any DDL (create, drop, alter, …). However, FDO feature reader deals with FDO logical schema, which is at a different conceptual level. It’s mixing these levels that causes some difficulty for applications using the FDO API.  

In general, the FDO feature reader (to a certain degree) assumes an FDO class definition related to the properties being returned, but with physical schema SQL (let’s say just Select command), there isn’t necessarily any FDO class definition that applies. That’s why currently it just returns a data reader, which handles any generic returned data (actually the FDO select aggregates command also doesn’t return a feature reader since it’s returning computed data).

We can break down the SQL pass through selects into a couple of categories. One category would be selecting data that really isn’t related to any of the FDO schema classes, e.g. selecting from system tables or views (e.g. select from Oracle’s mdsys.cs_srs table, select stuff from dual or SQL Server’s sys.indexes, etc. Spatial predicates are quite a bit different.).

Even if we consider cases where the select is from a table that contains feature or feature related data, what does it mean to return a class definition for that data? Do we reverse engineer the select results and try to find the FDO class definition that seems to match it, that is one of the class definitions that you would get back from FDO’s describe schema? Do we just make up a class definition that may not even exist in FDO’s Describe Schema? If the select is from a single table, we possibly could find the class definition that is based on that table and use it. However, then we have to match up the columns being selected with properties in the logical schema, which may be slightly different – then we’re mixing physical schema and logical schema together (e.g. class names may not be the same as table names, property names may not be the same as column names, there may be additional computed columns, the user may not have selected the pkey columns – should we add them automatically?, etc.). Granted that in many cases, these would be virtually identical, but we can’t guarantee it and have to be able to handle the general case. Maybe we just reverse engineer a class definition from the selected data and not try to match up any existing FDO class? We would have to handle this anyway since a select may not match up at all with an existing class, such as selecting from a table with an owner that is different from the connected data store (e.g. connect to data store called Denver and select from Boulder.Roads – the schema may or may not be similar to Denver.Roads. Even worse, what happens if the user turns around and tries to update one of the returned objects – they’d have to get back to Boulder.Roads and not just try to update the FDO Roads feature class. So, this would have to be a different feature class.) It sounds like we’d manufacture a new class definition unless we could determine with certainty that the select is on a table that matches one of the already defined fdo classes. (An interesting case is “select r.* from roads r where r.length > 50”.)

TODO:

-- Reword away from questions. 

Therefore, in cases where the resulting columns come from an existing FDO feature table, a provider can return the !FdoClassDefinition corresponding to that table. In cases where the columns come from an unknown table, a class definition can be constructed on the fly. By definition, the FDO class definition returned by an FdoIFeatureReader does not necessarily correspond exactly to an existing FDO class even in existing providers. This is due to the fact that it may only contains the properties that were asked for in the Select command, plus additional computed properties. It is perfectly legal to return a constructed class definition, which is only valid for the select that was executed, and not usable for further updates or inserts.

TODO:

-- Add !IsComputed reference
-- Discuss schema/class name generation

So following up on the above discussion, providers that return feature readers from SQLCommands will need to come up with the appropriate class definition that the feature reader could expose. 

Here are a few use cases:

    - Select is against a table that has an existing class definition and the select returns the same information as defined by the class definition (e.g. select * from roads).
    - Select is against a table that has an existing class definition, but the select returns other information such as a subset of properties or additional computed properties. The FDO select command handles this type of case as well. The class definition returned by the feature reader includes the specific properties for that select.
    - Select is against a table that doesn’t have an existing class definition. Providers will be required to generate a temporary class definition.
    - Select is against a table that has an existing class definition, but the select is an aggregation that doesn’t return actual objects (e.g. select count(*), max(length) from roads). In this case it doesn’t make sense for the provider to return the same class name as for the underlying table since it is not actually returning road objects at all. This is basically another temporary class definition.

TODO: 

-- Figure out how providers can determine what are input/output paramaters
-- Procedure calls, input/output parameters, etc....
-- Examples

== Requirements ==

=== FDO API ===

In order to support the SQL pass-through objective outlined above, the FDO API will need to be modified to provide new functions to state that this support is enabled, and return an explicit FDO Feature Reader. It is preferential to add these new methods, as opposed to editing existing functionality, so that currently designed and running applications can continue to work as designed and implemented.

==== Command Capabilities ====

An objective of this proposal is allow Provider the option to support SQL pass-through commands that return an FDO feature reader. If a provider decides to support this functionality, it can do so using the SupportsFeatureReaderFromSQLCommand method on the FdoICommandCapabilities interface. 

{{{
/// \brief
/// The FdoICommandCapabilities interface declares the feature 
/// provider's level of support for Commands.
class FdoICommandCapabilities : public FdoIDisposable
{
public:
...
...
...

    /// \brief
    /// Determines if the provider supports the ExecuteFeatureReader 
    /// function on the SQL Command. 
    /// 
    /// \return
    /// Returns true if the provider supports ExecuteFeatureReader.
    FDO_API virtual bool SupportsFeatureReaderFromSQLCommand();
};
}}}


==== SQL Command Interface ====

In order to allow Providers to return an FDO feature reader from the FDO SQL command, a new !ExecuteFeatureReader method will be added to the FdoISQLCommand interface. Providers that implement this level of functionality will need to implement this command and provide support for the use cases (which are not yet a definative list) listed above.

{{{
/// \brief
/// The FdoISQLCommand interface defines the SQL command, which 
/// supports the execution of a SQL statement against an underlying 
/// RDBMS. Three execute methods are provided to distinguish between 
/// statements that return data versus those that execute 
/// non query type operations.
class FdoISQLCommand : public FdoICommand
{
public:
...
...
...

    /// \brief
    /// Executes the SQL statement against the connection object and returns
    /// a Feature Reader exposing the physical data returned by the reader 
    /// as FDO features. The Feature Reader may return a computed class that 
    /// maps the physical schema elements, referenced by the execute, to a logical
    /// FDO schema. 
    /// 
    /// \return
    /// Returns an FDO feature reader referncing a computed class that 
    /// maps the physical schema elements, referenced by the execute, to a logical
    /// FDO schema.
    /// 
    FDO_API virtual FdoIFeatureReader* ExecuteFeatureReader();
};
}}}

== Provider Implementation ==

The question concerning which providers will need to be modified to support the SQL pass through enhancement is an open one and will depend on available development resources. I am fairly confident SQLite will be enahced to add the additional SQL Pass Through support. Adding support to SQL Server Spatial should also be high on the priority list. However, depending on resourcing, this may not be achievable in FDO 3.5.0. Support for ODBC and MySQL are considered nice to have and will be implemented if resourcing and funding can be obtained.

== Test Plan ==

Existing FDO Core unit tests will be expanded to test the proposed enhancements defined above. 

Provider specific unit tests will be added to test the proposed enhancements defined above. 

== Funding/Resources ==

Autodesk to provide resources / funding