[odb-users] Prepared statement feature

[Stath, Paul]

Boris --

I think I see the issue.
(From a previous email in this thread.)
> The by-reference parameters are already "bound" to the query during preparation.
> It is not possible (well, not cheap, to be precisely) to re-bind them for each execution.

In my proposed implementation, the by-reference POD structure was passed as an
argument to the prepared_query.execute() method.  Which is when I assumed
that the by-ref parameters would be bound to the prepared select_statement.

Your previous statement seems to indicate that the by-reference arguments
have already been bound to the select_statement when the prepared_query
class was constructed.  Am I following that?

> Ok, I've started working on this and should have something for you
> to try in a few days.
My schedule is kind of tight for October, but I should be able to dedicate
some time to testing it in November.

-- Paul


> -----Original Message-----
> From: Boris Kolpackov [mailto:boris at codesynthesis.com]
> Sent: Wednesday, October 03, 2012 9:47 AM
> To: Stath, Paul
> Cc: odb-users at codesynthesis.com
> Subject: Re: [odb-users] Prepared statement feature
> 
> 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