FDO RFC 33 - Enhanced SQL Command Support

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

Status

RFC Template Version	(1.0)
Submission Date	April 9, 2009
Last Modified	Greg Boone Timestamp
Author	Greg Boone, Orest Halustchak
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

The FDO API currently defines support for a SQL command that allows for pass-through execution of SQL statements either through NonQuery execution of the SQL statement directly in the underlying Data Store, or through a query mechanism that returns a simple data reader listing the properties returned as a result of the SQL execution. The definition of the SQL command has remained fairly static over the last number of releases as primary development efforts have focused on extending other aspects of the FDO API, implementing new providers, etc. However, requirements for change to the SQL command have accumulated as RDBMS providers have implemented SQL support and clients have attempted to integrate use of the command into their applications in a seamless manner.

One key request has been the desire to have the FDO API 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. This enhancement is also intended to allow client applications that use FDO Feature Readers to code their applications in a generic manner so that data coming back from Select or SQL Pass Through statements can be processed in a uniform manner, thus reducing complexity, costs and time to implement.

Other more SQL specific requirements have also arisen, including:

• A need to allow a client to set the size of the fetch array an FDO provider will use when executing SQL statements
• A need to specify an array of data values on which the command will execute
• A need to specify the type of SQL command being executed; either straight text or stored procedure
• A need to specify the direction in which parameters to the SQL command are to be used, specifically, Input, Output, Input/Output or Return parameters

This RFC will outline changes that are required in the FDO API to accommodate these requests.

Overview

In general, the intent of SQL pass-through is to deal with physical schemas. There is no parsing of the SQL statements, and what is identified are physical schema tables, columns, functions, commands, etc. The SQL statement can be any SQL command, not just select, but any DML (select, insert, update, delete) or any DDL (create, drop, alter, …). However, FDO feature readers deal with FDO logical schemas, which is at a different conceptual level. It’s mixing these levels that causes difficulty for applications using the FDO API. Applications are required to use different code pathways to handle select statement as opposed to direct SQL execution. If clients could use the result of both these types of operations in a generic routine or component, such as a Data Table or Symbolization packages, applications would be provided a much more seamless and dynamic mechanism on which they can build and extend their applications.

To a certain degree, the current FDO feature reader implementation assumes an FDO class definition is directly related to the properties being returned. With physical schema SQL (let’s say just Select command), there isn’t necessarily any FDO class definition that applies. This is why currently the SQL command's Execute method returns a data reader, which handles any generically returned data. Note that the FDO select aggregates command doesn’t return a feature reader either, since it’s returning computed data.

One response to this issue has been to suggest that providers reverse engineer the select results and attempt to find the FDO class definition (coming from a describe schema request) that matches it. Other suggestions have been to construct a class definition on the fly, one that would not result from a call to describe schema. If the select is from a single table, providers would find the class definition that is based on that table and use it. However, then matching up the columns being selected with the properties in the logical schema, there may be some slight differences. This would result in the mixing of physical schema and logical schema elements together. For example, class names that are not the same as table names, property names that are not the same as column names, the use of additional computed columns, inclusion of pkey columns, etc. Granted that in many cases, the logical and physical views would be virtually identical. However, since that cannot be guaranteed, the design that is adopted will have to be able to handle the general case.

To account for these scenarios, providers will need to be modified to reverse engineer class definitions from the selected data and not attempt to match the select request to an existing FDO class. IN reality, FDO Providers will be required to handle this in any case since a generic SQL select may not match up at all with an existing FDO class. An example of this can be found when selecting from a table with an owner that is different from the connected data store. For example, Connect to data store called Denver and select from Boulder.Roads – the schema may or may not be similar to Denver.Roads. In these types of circumstances, providers should manufacture a new class definition unless they could determine with certainty that the select is on a table that matches one of the already defined FDO classes.

In cases where the resulting columns come from an existing FDO feature table, a provider can return the class definition 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 a feature reader does not necessarily correspond exactly to an existing FDO class definition. Existing class definitions may contain 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.

In the circumstance that a computed class is generated, the FDO class definition’s IsComputed property will return true. In that manner, applications are able to distinguish the content of the feature reader responses coming from the providers and tailor their implementation accordingly. In such a situation, some care will also need to be given to the name of the generated schema and classes. At this point no standards exist for naming auto-generated or comuted schema, class and property names. It would be beneficial if, as a result of this RFC, some uniform conventions could be adopted.

Providers that do return feature readers from SQL commands will need to come up with the appropriate class definition that the feature reader could expose. Here are a few general use cases that can be used to guide their implementations:

• 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.

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();
};

Parameter Direction Enumeration

A new enumeration, FdoParameterDirection, will be added to the FDO API for use within FDO Commands that utilize parameters. FdoParameterDirection defines the direction in which the command will utilize the parameter upon execution. Parameter direction values are typically Input, Output, InputOutput and Return, with the default value is typically Input.

/// \brief
/// The FdoParameterDirection enumeration defines the direction in which a
/// Parameter value will be used within the context of an FDO Command. Parameter 
/// Direction values are typically Input, Output, InputOutput and Return. 
/// The default value is typically Input.
enum FdoParameterDirection 
{
    FdoParameterDirection_Input,
    FdoParameterDirection_Ouput,
    FdoParameterDirection_InputOutput,
    FdoParameterDirection_Return
};

Parameter Value Interface

The FdoParameterValue class will be updated to add an additional property Direction, which will indicate the direction in which the parameter will be used within the context of command using the parameters within the context of their execution.

/// \brief
/// The FdoParameterValue class specifies a value for a particular parameter.
/// Instances of this class are used to specify a value to bind to a parameter
/// when a command is executed.
class FdoParameterValue : public FdoIDisposable
{
public:
...
...
...
    /// \brief
    /// Sets the function direction of the parameter value.
    /// Values can be Input, Output, InputOutput and Return.
    /// 
    /// \param value 
    /// Input the direction enumeration value
    /// 
    /// \return
    /// Returns nothing
    /// 
    FDO_API void SetDirection(FdoParameterDirection value);

    /// \brief
    /// Gets the function direction of the the command parameter.
    /// Values can be Input, Output, InputOutput and Return.
    /// 
    /// \return
    /// Returns the direction enumeration value
    /// 
    FDO_API FdoParameterDirection GetDirection();
};

SQL Command Type Enumeration

A new enumeration, FdoSQLCommandType, will be added to the FDO API for use in the SQL Command. FdoSQLCommandType represents the type of SQL statement being sent tot the provider for execution. The acceptable values of the enumeration are either Text or Stored Procedure. Stored Procedure are often executed as PL/SQL blocks.

/// \brief
/// The FdoSQLCommandType enumeration defines the type of SQL
/// statement that will be executed. Statements can either be 
/// simple Text or a represent a StoredProcedure.
/// The default value is typically Text.
enum FdoSQLCommandType 
{
    FdoSQLCommandType_Text,
    FdoSQLCommandType_StoredProcedure
};

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
    /// Gets the fetch size of the data set used by
    /// the SQL command when executing statements against the 
    /// underlying Data Store. This parameter is typically set 
    /// in situations where large amout of data are expected 
    /// when the SQL command is executed and providers need 
    /// to minimize the number of Data Store round trips
    /// For example, fetch 10,000 rows in one execution step. 
    /// 
    /// \return
    /// Returns the SQL commands batch fetch size.
    /// 
    FDO_API virtual FdoInt32 GetFetchSize() = 0;

    /// \brief
    /// Sets the fetch size of the data set used by
    /// the SQL command when executing statements against the 
    /// underlying Data Store. This parameter is typically set 
    /// in situations where large amout of data are expected 
    /// when the SQL command is executed and providers need 
    /// to minimize the number of Data Store round trips
    /// For example, fetch 10,000 rows in one execution step. 
    /// 
    /// \param value 
    /// Input the SQL commands batch fetch size.
    /// 
    /// \return
    /// Returns nothing
    /// 
    FDO_API virtual void SetFetchSize(FdoInt32 fetchSize) = 0;

    /// \brief
    /// Gets the Batch ParameterValue Collection that will be used for optimized
    /// batch execution of multiple statements with a single command. Batch
    /// execution can be performed by using parameters for each of the property
    /// values, then adding collections of parameter values to the Batch
    /// ParameterValue Collection. Each Collection in the ParameterValue Collection
    /// should contain one FdoParameterValue for each of the parameters specified 
    /// for property values.
    /// 
    /// \return
    /// Returns FdoBatchParameterValueCollection
    /// 
    FDO_API virtual FdoBatchParameterValueCollection* GetBatchParameterValues() = 0;

    /// \brief
    /// Gets the SQL statement type that is to be executed.
    /// 
    /// \return
    /// Returns the SQL statement type.
    /// 
    FDO_API virtual FdoSQLCommandType GetCommandType() = 0;

    /// \brief
    /// Sets the SQL statement type that is to be executed.
    /// 
    /// \param value 
    /// Input the SQL statement type.
    /// 
    /// \return
    /// Returns nothing
    /// 
    FDO_API virtual void SetCommandType(FdoSQLCommandType value) = 0;

    /// \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