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

Subject: Re: [boost] sqlpp11: SQL for C++
From: Roland Bock (rbock_at_[hidden])
Date: 2013-11-11 15:31:56

On 2013-11-11 21:14, Larry Evans wrote:
> On 11/11/13 13:35, Abel Sinkovics wrote:
>> Hi Roland,
>>
>> On 2013-11-09 23:58, Roland Bock wrote:
>>> On 2013-11-09 23:03, Michael Marcin wrote:
>>>> On 11/9/2013 4:03 PM, Roland Bock wrote:
>>>>> Please let me know your questions/thoughts/suggestions/rants.
>>>>> Contributions welcome, of course :-)
>>>>>
>>>> Could you compare your library with SOCI?
>>>>
>>>>
>>> I consider SOCI to be a string and position based approach, meaning
>>> that
>>> you (as a library user) have to use strings to construct your queries
>>> and positions to extract results. Here is one of SOCI's introductory
>>> examples:
>>>
>>> // ------------------------
>>> int id = 17;
>>> string name;
>>> int salary;
>>> sql << "select name, salary from persons where id = " << id,
>>>         into(name), into(salary);
>>> // ------------------------
>>>
>>>
>>> In sqlpp11 this would read something like this
>>>
>>> // ---------------------
>>> auto result = db.run(select(persons.name,
>>> persons.salary).from(persons).where(persons.id == 17));
>>> if (!result.empty())
>>> {
>>>     const auto row = result.front();
>>>     std::string name = row.name;
>>>     int salary = row.salary;
>>> }
>>> // ---------------------
>>>
>>> Notice that there are no strings being used in the sqlpp11 code at all.
>>> The compiler can see and check the syntax of your query. If you mistype
>>> anything, or if you compare apples and oranges (e.g. a text column and
>>> an int), or if you forget the 'from' before the 'where' for
>>> instance, it
>>> will let you know with compile errors.
>>>
>>> Also, it is much harder to mix up stuff in the results. Imagine a
>>> slightly larger list of columns being selected. With SOCI you need to
>>> keep the "select a, b, c, d" in sync with the into(a), into(b),
>>> into(c),
>>> into(d)
>>>
>>> sqlpp11 takes care of this responsibility for you and gives you rows
>>> with appropriately named and typed member variables. It is much harder
>>> to use those the wrong way without the compiler yelling at you.
>> There is a library (Metaparse) which supports creating a string-based
>> interface for libraries which is processed at compile-time. It could be
>> used to provide a string-based interface. For example instead of this:
>>
>> auto result = db.run(select(persons.name,
>> persons.salary).from(persons).where(persons.id == 17));
>>
>> Your library could have the following interface:
>>
>> auto result = db.run(QUERY("select name,salary from persons where
>> id=17"));

Metaparse requires const char[N] arguments, right? That would be a
rather atypical case for using a query interface, I'd say. Personally I
have never used queries without variable parameters except in examples
like the one above.

>>
>> The string could be parsed by a template metaprogram and the right
>> classes could be built out of it. It could provide all the static
>> guarantees you have described above.
>
> Guessing from code here:
>
> https://github.com/rbock/sqlpp11/blob/master/include/sqlpp11/select.h#L574
>
>
> The sql string written to oss would be something like the argument
> to your QUERY function.  IOW, IIUC, there's no need for parsing a
> string to build the right classes.
>
> OTOH, the string passed to the actual database (via the db
> on select.h#L574) would have to be parsed, I assume, by dbms, which
> might issue some error message or return some error code if the
> sql string were not right.  I think Roland's code almost guarantee's
> the sql string would be correct.
>
> Is that about right Roland?

That is correct, Larry, nicely guessed from the code, indeed :-)

The query is constructed via functions and objects to build an
expression which /can/ be evaluated as a string which is then being sent
to the database. This is also the current use case. But there have been
several ideas presented in this thread what else could be done
(evaluating XML or JSON or incoming streams). In those cases, it might
be better to transform the query into another representation.

Regarding the correctness of the string: That's the goal, yes.

Best regards,

Roland