[odb-users] Prepared statement feature

[Boris Kolpackov]

Hi Paul,

Thanks for the suggestions. While I don't like your interface any better
than mine ;-) and there are some implementation issues with it, it 
highlighted some of the aspects that I didn't think about (e.g., the
desire to define age_pquery in the same header as the persistent class,
which would not be possible if it contains references to the odb::query
template -- it can only be used after the -odb.hxx header has been 
included).

So based on your suggestions and some more thinking, I came up with
the following alternative design:

Simple Scenario

The query is prepared, executed within a transaction several times,
and then discarded (i.e., no caching in the connection). This approach
can also work for a single-threaded application which uses a single
connection.

typedef odb::query<person> query;
typedef odb::result<person> result;

transaction t (db->begin ());

unsigned short age;

prepared_query<person> pq (
  db->prepare_query<person> (
    "age-query", query::age < query::_ref (age)));

age = 20;
result r1 (pq->execute ());

age = 30;
result r2 (pq->execute ());

age = 40;
result r3 (pq->execute ());

...

t.commit ();

Complex Scenario

Here we want to cache the prepared query so that it can be executed
later in other transactions.

struct query_params
{
  unsigned short age;
};

typedef odb::query<person> query;
typedef odb::result<person> result;

transaction t (db->begin ());

query_params* params;
prepared_query<person>* pq (db->lookup<person> ("age-query", params));

if (pq == 0)
{
  auto_ptr<query_params> p (new query_params); // Or unique_ptr.

  prepared_query<person> q (
    db->prepare_query<person> (
      "age-query", query::age < query::_ref (p->age)));

  params = p.get ();  
  pq = db->cache (q, p); // Assumes ownership of p.
}

params->age = 20;
result r (pq->execute ());

...

t.commit ();

Now if we don't have any by-reference parameters, then this can be 
simplified to:

typedef odb::query<person> query;
typedef odb::result<person> result;

transaction t (db->begin ());

prepared_query<person>* pq (db->lookup<person> ("age18-query"));

if (pq == 0)
{
  prepared_query<person> q (
    db->prepare_query<person> (
      "age18-query", query::age < 18));

  pq = db->cache (q);
}

result r (pq->execute ());

...

t.commit ();

What do you think?

Boris