[gdal-dev] OGR : OCI driver improvement

[Peter J Halls]

Nicolas,

    I had a play with some of this a few years back, using GDAl 6.3, I think. 
I've back out of most of it ... It is good to see another person interested in 
improving the OCI driver.

Nicolas Simon wrote:
> Hi all, 
> 
> I'd like to propose some OCI driver improvement.
>  
> 1) Enable insert from different client. This could be done by managing the generation of new FID on DB side instead of client side.
> Technically this could be done with an oracle sequence and a trigger to fill the FID. 
> This proposition modify the way FID are handled ( I propose to do as in PostGIS driver). This means that the FID of a feature (i.e. usually the value of the OGC_FID column for the feature) inserted into a table with CreateFeature() will be retrieved from the database and can be obtained with GetFID() even if an non-null FID was provided. 
> This could be easily implemented in UnboundCreateFeature with a 'returning' clause.
> But I don't known if it's feasible in BoundCreateFeature since truth FIDs will be know later when OGROCITableLayer::FlushPendingFeatures() is called. 

I tried this, but it seemed to slow inserts down dramatically - as does setting 
Primary Key, etc.  I was seeking a means of incrementally building a very large 
spatial database - at least several hundred million entries (a road network).  I 
would be happy if the table were opened for exclusive writing but the FID read 
from the table and used to seed the value used in OGR.  Maybe I got the code wrong.

> 
> 2) By providing true update operation instead of delete + insert operations (cf SetFeture).
> This should provide better performance and enable to develop trigger on update if needed for other purpose. 
> With this modification, OCI driver will have the following mapping between OGR concepts and Oracle operations:
>     OGRFeature::CreateFeature() <==> INSERT operation
>     OGRFeature::SetFeature() <==> UPDATE operation
>     OGRFeature::DeleteFeature() <==> DELETE operation
> This modify slightly the actual behavior since it disable the possibility of inserting a new record through SetFeature (in case of  they were no record to delete, the subsequent insert did the job). With the proposed implementation it'll be no longer the case.  "Update ... set ...  where FID = provided FID" command doesn't insert new record. 

The concept is great, but doing it this way is not: it will mean that existing 
applications written to be output agnostic will need to be rewritten.  It would 
be better to introduce an UpdateFeature method that detects whether an update 
operation is supported and used delete/insert where update is unavailable.  This 
would not break existing software.

> 
> 3) Add transaction support in the normal SQL sense.
> Since transaction is handled within a session and we have a session per OGROCIDataSource, a transaction will include operation on any layer of that datasource. 
> I propose the following operating mode.
> 
> Outside a transaction, we'll be in 'auto commit' mode. 
>   This commit on success DeleteFeature, SetFeature and CreateFeature (not in MULTI_LOAD mode). 
>   For CreateFeature (in MULTI_LOAD mode) data will be commited 
>   - each time the buffer is full (when OGROCITableLayer::FlushPendingFeatures() is called)
>   - when OGROCITableLayer::SyncToDisk() is call
>   - before a new transaction start.
> 
> Transaction start (through a call OGRLayer::StartTransaction()) for all layer in the DataSource.
> OGRLayer::CommitTransaction() will call OGROCITableLayer::SyncToDisk() for each layer of  the DataSource and issues one commit command for the session, and then return in auto commit mode (cf outside transaction mechanism)
> OGRLayer::RollbackTransaction() will drop PendingFeature (may be  through a private function that reset nWriteCacheUsed to 0) for each layer of the datasource and issues one rollback command for the session, and then return to auto commit mode.
> 

I see the benefit of implementing transactions, although I very rarely use this 
approach in my work.  For many users, this may not be a necessity, so it should 
be available to initiate, rather than as default.  I guess this means an option 
at connection time?

> I would like to know if someone else is interested in these improvements ?  
> Is it the way you want things work ?
> 
> An other information is that I'm ready to work for these improvements. 
> 
> Regards,
> 
> Nicolas

Best wishes,

Peter

--------------------------------------------------------------------------------
Peter J Halls, GIS Advisor & Acting Team Leader Applications Support Group,
                Information Directorate, University of York
Telephone: 01904 323806     Fax: 01904 323740
Snail mail: Harry Fairhurst Building, University of York,
             Heslington, York YO10 5DD
This message has the status of a private and personal communication
--------------------------------------------------------------------------------