Frank, Peter and others,
> -----Message d'origine-----
> De : Peter J Halls [mailto:p.ha...@york.ac.uk]
> Envoyé : lundi 4 juillet 2011 15:19
> À : Nicolas Simon
> Cc : gdal-dev@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
> --------------------------------------------------------------
> ------------------
>
>
>
_______________________________________________
gdal-dev mailing list
gdal-dev@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/gdal-dev
