updateable recordsets (was RE: GNOME-DB TODO)

[Reinhard Mueller <rmueller1 vanenburg com>]

> > I notice an important TODO item in 0.0.96:
> > 
> > * allow recordsets to be modified
> > 
> > I am interested in helping with this, because gnome-db
> > appears to be a very useful tool that has a lot of potential.
> > 
> > Are there any pointers you can give or ideas which have already
> > been discussed about the best way to do this, or is there already
> > work going into this?  Either way, I am eager to help in 
> this respect
> > as I have a project which would be perfect for using gnome-db, but
> > I am in need of using the tools to do database updates.  Please
> > let me know how I can help.

Some members of the GNU Enterprise team have already made some
thoughts about that. Maybe you want to join irc.gnue.org #gnue
to discuss shortly what we have in mind.
Unfortunately, our plans are not written down yet, but exist
only in our brains.

> About how to do it, I myself don't have a clear idea on how 
> to do it in
> a portable way, so let's discuss about it. The only thing I 
> can think of
> is to have the providers store a row identifier (ROWID in 
> oracle, oid in
> postgres...), and when a field is updated, to do it through this
> identifier. BUT, some RDBMS don't support this!

We had the discussion to not use ROWID or oid features, but
rather to use seed tables where a 64bit integer value is
stored per table, and every table has a field "COLNR" or the
like where a "counter" is stored. This COLNR would be filled
in automagically by gda when inserting a record using
gda_recordset_insert (of course not when an explicit SQL
INSERT statement is used).

Using seed tables would have some advantages:
* you can use them partably in any database, even in CSV files
* the row identifyer would have the same type in every DB,
  which makes coding much easier
* the row identifyer is available even before the record
  is posted to the backend, which can ease many tasks in
  creating master-detail related records

Anyway, we would probably want to add

gda_recordset_insert
  (creates a new row in memory, gets next COLNR from seed
   table, but does nothing else in the backend)
gda_recordset_delete
  (deletes the current row from the backend by using COLNR
   as row identifyer, i.e. creates a SQL DELETE statement
   on the fly)
gda_recordset_post
  (writes the current row back to the backend by using COLNR
   as row identifyer, i.e. creates a SQL INSERT or SQL
   UPDATE statement on the fly)

to the client api.
We would also need some means to change gda_field values.

As a result of this, you would only have to build the SQL
SELECT statement, not the others.

There have also been thoughts about completely abstracting
gda access from SQL and somehow write a function like

gda_recordset_open_fieldlist (char *table, char **fieldlist);

but you really got us in the midst of our thoughts, so if
you can wait a few more days, I will (probably over the
weekend) prepare some written proposal on this.

But anyway, please give me your feedback about whether
that is what would fit your needs or not

Thanks
Reinhard