[odb-users] Prepared statement feature

[Boris Kolpackov]

Hi Paul,

Stath, Paul <pstath at axxcelera.com> writes:

> My thinking was that the prepared_query<> class would work like a
> factory class, creating the prepared query statement in each database
> connection as necessary.  Thus hiding the connection/multi-threading
> aspects of the connection, much like odb::<db>::connection_factory
> hides how connection objects are obtained.

I agree, that would be nice. However:


> The prepared_query<> class would hold the query condition, and when
> the execute method is called, would look up the prepared statement
> in the current database connection, and if not present would prepare
> and cache it in the connection, and finally execute it.
> The next time that connection is used to execute the prepared query,
> the prepared statement would already be in the cache, and the query
> could be executed, skipping the prepare/cache steps.

The problem are the by-reference parameters. We either have to create
a separate set for each connection or there will have to be access
synchronization. The latter I believe is a bad idea since it will
constrain parallelism. And if we go with the former, then there
cannot be a shared query condition since it is bound to a specific
set of by-reference parameters. In other words, we will need to
create a separate odb::query object that is bound to a separate
set of by-reference parameters. And given the current architecture,
I don't see an easy way to clone odb::query but give it a separate
set of by-ref parameters.

I also thought about the factory idea in the following context: if
the same prepared query is used in multiple different transactions,
then things can get unmaintainable because now the query condition
(i.e., query::age < query::_ref (age)) has to be duplicated in
each of them (because they all need to be prepared to handle the
case where the statement hasn't yet been prepared and cached; see
the complex scenario in my previous email). So I thought that it
would be a good idea to allow the specification of a factory that
will be called to prepare the statement if the call to lookup()
didn't find anything. Here is an example (using C++11 lambda but
normal function/functor can also be used):

struct query_params
{
  unsigned short age;
};

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

db->prepared_query_factory (
  "age-query",
  [] (const char* name, connection& conn)
  {
    auto_ptr<query_params> p (new query_params); // Or unique_ptr.

    prepared_query<person> q (
      conn.prepare_query<person> (
        name, query::age < query::_ref (p->age)));

    conn.cache (q, p); // Assumes ownership of p.
  });

transaction t (db->begin ());

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

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

...

t.commit ();


> It seems to me that there is no need to expose the lookup and cache
> methods to the user, instead hide them inside the execute method of
> the prepared query.

One nice thing about my approach is that it is incremental: one can
just create a prepared query on the stack, execute it a couple of times,
and that's it. No caching involved. The next level is a query that one
would like to reuse in the same (single) transaction (code wise, not
repetition-wise). Here we can make do with caching in this transaction,
keeping everything local. And the final level is when we need to reuse
the same query in multiple different transactions (say a query that
returns the next sequence number). Here we can use the factory approach
to keep things maintainable.


> In the end, either method will work, and will allow the prepared
> query statement to be cached, eliminating the expensive parse of
> the query statement for every query.

Ok, I've started working on this and should have something for you
to try in a few days.

Boris