[odb-users] Atomic and batch update in ODB

[Zhao Xiang]

Dear ODB Developers:


I've been using ODB and MySQL for quite some time, It's been very useful for my projects, and I think it would be much more helpful if the following features could be included in odb:


a) Atomic Update


Considering the following code from http://www.codesynthesis.com/products/odb/doc/manual.xhtml#3.5:


void
update_age (database& db, person& p)
{
  try
  {
    transaction t (db.begin ());


    p.age (p.age () + 1);
    db.update (p);


    t.commit ();
  }
  catch (...)
  {
    transaction t (db.begin ());
    db.load (p.id (), p);
    t.commit ();


    throw;
  }
}
  

If this function is called in two threads at the same time, it could result in only one age in added.


Is there any plan for feature that equivalent to the following SQL: update `person` set `age` = `age` + 1 where `id` = xxx; ?


b) Batch Update


For now I can do a query and update the results in a for-loop, but it would be much slower than a single update statement like: update `person` set `age` = `age` + 1 where `first_name` = 'Joe';


Or: update `person` set `age`= case `id` when 1 then 10 when 2 then 20 else `id` where id in (1, 2);


c) Batch Insert


Similar the previous one, it would be helpful to have something like this: insert into `person` (`id`, `age`) values (1, 10), (2, 20);


d) Insert and duplicate


This feature can be implemented with find() and insert() now, however it would be atomic and faster with something like this: insert into `person` (`id`, `age`) values (1, 10) on duplicate key ignore;


Or: insert into `person` (`id`, `age`) values (1, 10) on duplicate key update set `age` = values(`age`)


Or in some case: insert into `person` (`id`, `age`) values (1, 10) on duplicate key update set `age` += values(`age`)




I hope my suggestions could be taken into consideration in your further development plan.


Thanks
Zhao Xiang