Re: [gnome-db] Optimal way to copy registers

["Vivien Malerba" <vmalerba gmail com>]

On 9/26/06, Juan Pablo <jpdborgna yahoo com ar> wrote:
> Hi list.
> I came here today for consulting :)
>
> Im working on a program wich under certain conditions has to copy n
> registers from one table to another (two different databases, providers,
> servers).
> First i didt simple, but im wondering if there is a better way to do it.
> What i did now is SELECT all the needed registers from source and
> iterate into the recordset with INSERT. Im using g_strdup_printf for
> making the insert sentence, so im having a value stringify for each
> field.
> What i think it could be done better is the insert part.

Funny because I've been thinking myself about the same kind of
problem, which is in a general way about moving data from one database
to another or from one table to the other, etc.

Basically, the solution seems right about iterating through a
GdaDataModel obtained by a SELECT statement and running an INSERT
query with it for each row. However to make things more portable, here
is what I suggest:

1-> create a GdaDataModelQuery object (dest_model) with a SELECT
statement as "SELECT col1, col2, FROM dest_table" and add of course
all the columns you need to set data for in it.

2-> use gda_data_model_query_set_modification_query(dest_model) with
the INSERT query which must handle parameters: it would look like:
"INSERT INTO dest_table (col1, col2) VALUES (## [:name="ACol1name"
:type="integer"], ##[:name="ACol1name" :type="gchararray"])" -- see
the gda_data_model_query_set_modification_query()'s documentation for
more information

3-> call gda_data_model_send_hint(dest_model,
GDA_DATA_MODEL_HINT_START_BATCH_UPDATE, NULL);

4-> use gda_data_model_import_from_model (source_model, dest_model);
where source_model is the GdaDataModel to copy data from.

5-> call gda_data_model_send_hint(dest_model,
GDA_DATA_MODEL_HINT_END_BATCH_UPDATE, NULL);

the calls to gda_data_model_send_hint() are to tell the dest_model
that a lot of modifications will be done in a quick time to enable it
to optimize things (in this case the model won't try to run the SELECT
query you gave it in step 1 each time a INSERT query is performed).

If there aren't too many bugs in libgda, this should work and you
won't have to hand code the queries yourself and data type handling
will be done correctly.

Tell me how it goes,

Vivien