Re: [boost] sqlpp11: SQL for C++

Subject: Re: [boost] sqlpp11: SQL for C++
From: Gavin Lambert (gavinl_at_[hidden])
Date: 2013-11-13 01:28:25

On 13/11/2013 18:24, Quoth Roland Bock:
> On 2013-11-13 00:48, Gavin Lambert wrote:
>>
>> Provided that you do value insertion via actual parameters (either
>> named or positional, depending on the target SQL dialect) and not via
>> directly inserting constants into the SQL query itself, it should be
>> fairly straightforward to extend to a dialect that requires explicit
>> preparing; you'd just need to provide a syntax that allows you to
>> cache a query object in a variable and then execute it multiple times
>> with different actual values.  (Using parameters is also preferred
>> over direct insertion because it lessens the risk of script injection.)
>>
> That should be relatively easy to do (and if you encapsulate the query
> generation in a parametrized function it is already there).

I'm not sure I'm reading that last part right, but what I am talking 
about is very different from having a C++ function that accepts 
parameters and internally generates and runs the query.

I haven't looked at your code enough to formulate a sensible example 
using its syntax, but if I can offer an analogy using Boost.Format's 
syntax (don't take it too literally):

boost::format GenerateQuery()
{
     return boost::format("select * from T where a=%1% and b=%2%");
     // not intended to be fully safe SQL, it's just for show
     // also I'm not implying you should use strings to construct
}

std::string IndirectCall(const boost::format& query, int a, const 
std::string& b)
{
     return (query % a % b).str(); // imagine this actually runs SQL
}

std::string DirectCall(int a, const std::string& b)
{
     IndirectCall(GenerateQuery(), a, b);
}

The GenerateQuery method returns a "query object" that defines the 
action to be done but not the specific parameter values on which it 
acts.  It is expensive to create (because this is where all the parsing 
happens) but is (fairly) cheap to copy, and reusable without copying. 
This is analogous to preparing an SQL query.

Outside code could run GenerateQuery once (perhaps at program startup), 
cache the result somewhere, and then use it repeatedly via IndirectCall, 
thereby only paying the parsing cost once.  (Boost.Format allows the 
same object to be reused once str() is invoked on it, or if explicitly 
cleared; or you can copy it prior to adding actual values to the copy.) 
  This is analogous to running a prepared query with different actual 
parameters.  (The db connector backend usually has specific separation 
between the SQL statement and the parameters -- you wouldn't actually 
insert them into the string the way that this example implies.  This 
also typically means you don't have to escape values to prevent 
injection, as they're already known to be data.)

The DirectCall method is the least performant case; it explicitly 
regenerates the query each time.  This is analogous to running an 
unprepared query *or* actually inserting the real values directly into 
the SQL instead of using the db-connector's parameter mechanism.  (But 
this is the most common way that SQL gets used, because it's "easier". 
I haven't looked too closely but I get the impression that this is the 
only way that sqlpp11 works at present too.)

The key point being that for prepared statements to work (whether 
explicit as in Oracle or automatic as in SQL Server), you need to be 
able to define the SQL statement itself using only constants and 
placeholders where variables go, but not provide the actual values of 
the variables.  Then when you actually go to execute the query you pass 
the constant SQL statement along with the collection of actual values to 
fill in the placeholders for this particular invocation.  Different 
database backends support different syntaxes for placeholders; most 
support positional placeholders and a few offer named placeholders.

(In your case I would recommend using only named placeholders on the 
user side, because it will be very unobvious to the user what the "real" 
positional order in the underlying SQL query will be.  When interfacing 
to a connector for a DB that only supports positional placeholders you 
would have to convert them appropriately.)