Re: updateable recordsets

[Ace Thompson <acet interzon com>]

Hi all,

For my 2 cents, I support keeping the implentation of the updates/etc at 
the provider
level.  As someone else stated, that abstraction is already there and I 
would argue
exists for purposes like this.  For another thing, I believe some db client 
libraries
support client-side updateable cursors (I could be optimistically 
hallucinating), and it
would seem to make sense to implement a recordset which will allow updates
using these functions.  For those that don't, the provider could implement
functionality with meta-data information and/or unique row ids.  Either 
way, it
doesn't seem right to enforce any particular method on every provider 
(especially
considering that each provider, and the creators of each provider, may have 
different
usage needs/performance considerations).

I would also mention that this problem has been dealt with many many times 
before,
perhaps it is worth looking at what other people do, just as a base. I know 
that
Microsoft MFC libraries attempt to use the ::SQLSetPos ODBC API function
if using ODBC, otherwise (I believe) they attempt to simply do an UPDATE
with the WHERE clause using all of the columns in the recordset (and the
corresponding values for the row to update).  An exception is thrown if more
than one record will be updated in that manner.  Perl's DBIx::Recordset uses
metadata information (set by the client application), i.e. you specify the 
primary
key for a table when you get your information from it.  I believe a warning
is logged if you update more than one row when updating a recordset row.
Does anyone know of any other approaches used?

I agree strongly with David Bath that updateable rows are a smaller part of
a larger problem.  Most database libraries don't have the "intelligence" 
that we
have an opportunity to give to gnome-db.  David's example of "hot" foreign
keys is an example of the type of tool that makes an application developers
life nice and will draw more users to gnome-db. For providers which don't
have meta-data information, as David said (if I interpret him correctly)
application- or user- specified information could be used.

It appears that the structure is already layed out for db meta-data
(gda_connection_open_schema(), etc); could we not use this to find
the primary key for a given table when a recordset is opened on it? (of course
for providers which support schema).  When an update is requested on
the table, the recordset could then use those primary key columns to
uniquely identify the row (if a primary key column isn't included in the
select list the recordset could silently decide to select the ones left out).
This would seem to me to involve less work than the row-id method
proposed (assuming that the provider already has open_schema()), as
well as be more robust.  We could also go from here and allow updates
on recordsets which involve joins for certain cases.

At any rate, this to me would seem logical:

- let the provider implement the code to do the updating
- if the provider has some kind of updateable cursor mechanism in
it's client libraries, use that.
- otherwise, use the metadata "Primary key determination" method.
- OR (user/application/programmer/provider/etc specified):
- use unique row ids.

Criticism? Please :)?

Sorry for the long-windedness :)

Ace Thompson
acet@interzon.com