[gdal-dev] OGR : OCI driver improvement

[Nicolas Simon]

Frank, Peter and others,
	
> -----Message d'origine-----
> De : Peter J Halls [mailto:P.Halls at york.ac.uk]
> Envoyé : lundi 4 juillet 2011 15:19
> À : Nicolas Simon
> Cc : gdal-dev at lists.osgeo.org
> Objet : Re: [gdal-dev] OGR : OCI driver improvement
> 
> 
> 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.
> 
It's not tricky to manage speed, integrity and compatibility
Well the driver (on client side) could initialize itself with the maxFID and generate a new 'default' FID for Feature. As done presently.
If no trigger/sequence where in place, the 'default' FID will be used. Nice: full compatibility.
If a trigger/sequence are in place, the 'default' FID will be overwritten by DB trigger.

A trigger/sequence could be setup with a special sql command ( ie. like DELLAYER:<table_name> ) or with a -lco when creating the layer.
 
A subsisting problem is how to handle CreateFeature with preset FID ? It could corrupt the FID sequence ( the 'default' one in the driver and the sequence in the DB). So to preserve integrity, I think that the preset FID should be ignored and overwritten by the 'default' FID or 'DB' FID -> We gain in integrity but lose in compatibility. What's the best chose ?
> > 
> > 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.
> 
It'is ok for me, it tries update and if no row is updated produce a CreateFeature ... but in this last case with the FID provided by the driver or the DB (see previous comment) 
  
> > 
> > 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?
> 

If StartTransaction() is not called, the driver will operate as presently.
If StartTransaction() is called, the driver will operate as exposed, in a SQL sense. this is better than the undefined way it does presently. (In fact it does nothing, the present driver should answers FALSE to Transaction support in TestCapability) 

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