= !MapGuide RFC 78 - Enhance transaction capability to Feature Service =

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


== Status ==

||RFC Template Version||(1.0)||
||Submission Date||(Date/Time submitted)||
||Last Modified||(Klain Qin) (Thu Jul 9 22:18:19 2009)||
||Author||(Klain Qin)||
||RFC Status||(ready for review)||
||Implementation Status||(pending)||
||Proposed Milestone||(2.2)||
||Assigned PSC guide(s)||(Tom Fukushima)||
||'''Voting History'''||(vote date)||
||+1||||
||+0||||
||-0||||
||-1||||
||no vote|| ||

== Overview ==

This proposal is to enhance the transaction capability of MapGuide Feature Service to support executing not only a sequence of standard commands(delete/update/insert) but also a sequence of sql statements within a single transaction. 

== Motivation ==

Currently MapGuide Feature Service supports executing a sequence of standard commands within a single transaction through the API illustrated below. The standard commands are delete/update/insert. If you pass in true for useTransaction, the API will start a transaction and commit(or rollback) it at the end. Thus all command execution will reside inside a single transaction. 
{{{
virtual MgPropertyCollection* UpdateFeatures( MgResourceIdentifier* resource,
                                              MgFeatureCommandCollection* commands,
                                              bool useTransaction ) = 0;
}}}
However, MapGuide Feature Service also provides another two APIs to execute sql statements as illustrated below, where a database transaction will internally be started and committed before and after the sql statement execution. Here the capability of executing a sequence of sql statements within a single transaction is missing. 
{{{
virtual MgSqlDataReader* ExecuteSqlQuery(MgResourceIdentifier* resource,
                                         CREFSTRING sqlStatemen) = 0;
virtual INT32 ExecuteSqlNonQuery( MgResourceIdentifier* resource,
                                  CREFSTRING sqlNonSelectStatement ) = 0;
}}}
This proposal is to expose a seperate transaction control from Feature Service to enable executing a sequence of sql statements within a single transaction. 

== Proposed Solution ==

A new class of !MgTransaction will be added to represent a transaction to be performed in a data store. 
{{{
/// \brief
/// MgTransaction represents a transaction to be performed in a DataStore.
/// If the transaction is time out, commit or rollback a transaction will
/// result in one exception MgFeatureServiceException thrown.
class MgTransaction : public MgGuardDisposable
{
PUBLISHED_API:
    /// \brief
    /// Commit the transaction.
    ///
    virtual void Commit() = 0;

    /// \brief
    /// Rollback the transaction.
    ///
    virtual void Rollback() = 0;

    /// \brief
    /// Get the identifier of feature source associated with the transaction.
    ///
    /// \return
    /// Returns the identifier of feature source associated with the transaction.
    ///
    virtual MgResourceIdentifier* GetFeatureSource() = 0;
};
}}}

Changes to Feature Service

!ExecuteSqlQuery() and !ExecuteSqlNonQuery() must use the same underlying connection on which the transaction started. So !MgTransaction instance is passed in as parameters for both of these two methods. The !MgTransaction implementation can return the undelying connection. 

The existing !UpdateFeatures function will execute the standard commands in some other connection from the connection pool. If passing in true for useTransaction, it will use a different transaction other than the one started by !BeginTransaction(). 

So in order to be consistent with !UpdateFeatures function, an overloaded version of !UpdateFeatures function will be added taking a transaction object as well. It executes the standard commands within the same transaction explicityly started by !BeginTransaction(). However this needs to be documented clearly to the users that if they are using the new APIs with the transaction object, they should stick to that and not mix in the old !UpdateFeatures() API. 

{{{
/// \brief
/// Provides an abstraction layer for the storage and retrieval
/// of feature data in a technology-independent way. The API
/// lets you determine what storage technologies are available
/// and what capabilities they have. Access to the storage
/// technology is modeled as a connection. For example, you can
/// connect to a file and do simple insertions or connect to a
/// relational database and do transaction-based operations.
class MgFeatureService : public MgService 
{
PUBLISHED_API:
    /// \brief
    /// Begins a transaction and return it.
    ///
    /// \param resource (MgResourceIdentifier)
    /// A resource identifier referring to a feature source.
    ///
    /// \return
    /// Returns an MgTransaction instance.
    ///
virtual MgTransaction* BeginTransaction(MgResourceIdentifier* resource) = 0;

virtual MgSqlDataReader* ExecuteSqlQuery(MgResourceIdentifier* resource,
                                         CREFSTRING sqlStatemen
                                         MgTransaction* transaction) = 0;

virtual INT32 ExecuteSqlNonQuery( MgResourceIdentifier* resource,
                                  CREFSTRING sqlNonSelectStatement,
                                  MgTransaction* transaction) = 0;

MgPropertyCollection* MgServerFeatureService::UpdateFeatures( MgResourceIdentifier* resource,
                                                              MgFeatureCommandCollection* commands,
                                                              MgTransaction* tran );

};
}}}

One example using the new API
{{{
MgTransaction tran = nullptr;
try
{
    tran = MgFeatureService.StartTransaction(resourceId);
    MgFeatureService.ExecuteSql(resourceId, sql statement1, tran); 
    MgFeatureService.ExecuteSql(resourceId, sql statement2, tran); 
    MgFeatureService.UpdateFeatures(resourceId, MgFeatureCommandCollection, tran);
    tran.Commit();
}
catch(...)
{
    if(nullptr != tran)
        tran.Rollback();
}
}}}

Notes: 
 * If any exception is thrown within the sql statement execution, the new API won't rollback the transaction internally. Instead, it depends on the client application to rollback the transaction explicitly as illustrated in above codes.
 * If a provider doesn't support transaction, !BeginTransaction() will throw an !MgInvalidOperationException saying transaction is not supported. 
 * If passing in NULL for the parameter, !ExecuteSqlQuery and !ExecuteSqlNonQuery will work the same as before taking no transaction is started. For new version of !UpdateFeatures function, it will work the same as you pass in false for useTransaction. 

Two additional APIs are added INTERNAL_API as to facilitate committing and rollbacking the transaction. They are not exposed with managed APIs but can only be accessed through C++. Every transaction object on the server side will have a unique identifier accordingly. The transaction id will be serialized and deserialized when passing between proxy feature service and the server-side feature service.
{{{
class MgFeatureService : public MgService 
{
INTERNAL_API:

    // Commit the transaction specified by the transaction id.
    virtual bool CommitTransaction(CREFSTRING transactionId) = 0;

    // Rollback the transaction specified by the transaction id. 
    virtual bool RollbackTransaction(CREFSTRING transactionId) = 0;
};
}}}

Timeout configuration for !MgTransaction

As the transaction is a critical resource for database applications, a server-side transaction can be left open while the application is doing other things in between sql or even worse the application may fail or hang and the server would not know. So it needs to set a timeout for all transactions. If a transaction hasn't been committed or rollbacked within the timeout, the server will automatically rollback the transaction. If you call Commit() or Rollback() on the !MgTransaction instance after timeout, an exception will be thrown reminding that the transaction has been timeout and reverted. 

Two items will be added into serverconfig.ini for transaction timeout setting. If the transaction hasn't been committed or rollbacked within 6 minutes, it will be automatically rollbacked. 
{{{
DataTransactionTimeout             = 360
DataTransactionTimerInterval       = 60
}}}
Bind !MgTransaction to FDO connection

The transaction has to be bound with the underlying FDO connection where it starts during its life circle. The underlying FDO connection will be kept used by the transaction until the call of either Commit() or Rollback(). The implementation of !MgTransaction should be able to return the underlying connection. 

== Implications ==

This is a new API, so API documentation need to be updated. However, it will not affect the existing application if you don't use it at all. 

This new API is designed for sql statement execution. You have to use the API with !ExecuteSqlQuery() or !ExecuteSqlNonQuery() within the transaction. 

If the user uses the new !UpdateFeatures function taking the transaction object, they should stick to that and not mix in the old !UpdateFeatures() API. 

== Test Plan ==

A new unit test similar to the above example will be added into the existing Feature Service unit tests. 

== Funding/Resources ==

Supplied by Autodesk.