[fdo-internals] FDO RFC 16 - FDO Provider for SQLite

[Traian Stanev]

Currently I'm thinking of having the spatial index per connection (for connections that need to use it). The provider would be single writer, many readers, so yes, the spatial index on a reader connection may get out of date if someone writes to the file concurrently from another connection. I'm tempted to leave it to the client to manage issues of concurrency, because the client knows a lot better its usage patterns. For example, if the client is doing a batch insert from one connection and at the same time doing reads from another, it can tell the reading connection to refresh its spatial index.

I admit that I haven't really thought through the concurrent usage part, when writing is involved -- I've been thinking more in terms of file format simplicity. Another possibility that I see is to allow for the same FDO connection to be used by multiple threads (by locking the underlying sqlite database instance while writes are happening, but allowing multiple reads to happen simultaneously) -- although I know this spells trouble as well. And yet another possibility is to have the spatial index be a table in the database (Jason would rejoice) -- this way writes would update the spatial index and reads would automatically see the change. This is how SDF currently works, and I don't like the overhead one has when having to update the R-Tree with every insert and I don't like how we have to navigate a B-Tree in order to navigate the R-Tree, effectively giving a bbox query time of log^2(n) rather than log(n).


Traian


> -----Original Message-----
> From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-
> bounces at lists.osgeo.org] On Behalf Of Orest Halustchak
> Sent: Wednesday, March 19, 2008 9:14 AM
> To: FDO Internals Mail List
> Subject: RE: [fdo-internals] FDO RFC 16 - FDO Provider for SQLite
>
> Hi Traian,
>
> Just catching up on this email thread ...
>
> To support transactions, the commit must ensure that everything that
> should be persisted is written to the file (the Durable part of ACID).
> For Rollback, SQLite may do the rollback for you, but if you have
> anything in memory that was modified by the transaction, you'll have to
> back out those changes, e.g. in your in-memory spatial index. So, yes,
> somebody can just call the FDO SQL command, but the provider may need
> to be aware of when commit and rollback are called.
>
> Also, for this provider, how is the db to be shared between users? Is
> it single writer, multiple reader? On multiple reader, how is the
> spatial index to be shared?
>
> Thanks,
> Orest.
>
> -----Original Message-----
> From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-
> bounces at lists.osgeo.org] On Behalf Of Traian Stanev
> Sent: Tuesday, March 18, 2008 4:12 PM
> To: FDO Internals Mail List
> Subject: RE: [fdo-internals] FDO RFC 16 - FDO Provider for SQLite
>
>
> SQLite is ACID (for some value of ACID). I'm not sure what's involved
> in supporting transactions properly, but feature commands
> (insert/update/select) will internally be done using transactions
> anyway. With insert, one pretty much has to batch lots of inserts into
> one transaction, because a write transaction in SQLite involves closing
> and reopening the file -- which is a very slow way to coordinate
> access. Anyway, it really depends on what the most often used access
> pattern would be for such an FDO provider. So I guess the answer is
> "yes, if necessary, we can have explicit transaction support"? Also,
> keep in mind that you can start a transaction at any time using the FDO
> SQL command, then go on and execute some other feature commands, and
> end it by a commit.
>
>
> Traian
>
>
> -----Original Message-----
> From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-
> bounces at lists.osgeo.org] On Behalf Of Jason Birch
> Sent: Tuesday, March 18, 2008 3:55 PM
> To: FDO Internals Mail List
> Subject: RE: [fdo-internals] FDO RFC 16 - FDO Provider for SQLite
>
> Traian,
>
> I think you may have missed addressing Mateusz' question about
> transactions.  Considering that SQLite is (apparently) ACID, wouldn't
> it
> make sense to support these?
>
> Jason
>
> -----Original Message-----
> From: Traian Stanev
> Subject: [fdo-internals] FDO RFC 16 - FDO Provider for SQLite
>
> I think my reply to RFC 16 questions got sent to the bit bucket, so
> here
> it is, again:
> _______________________________________________
> fdo-internals mailing list
> fdo-internals at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/fdo-internals
> _______________________________________________
> fdo-internals mailing list
> fdo-internals at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/fdo-internals
> _______________________________________________
> fdo-internals mailing list
> fdo-internals at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/fdo-internals