Re: [boost] Boost.SQL?

Subject: Re: [boost] Boost.SQL?
From: Dominique Devienne (ddevienne_at_[hidden])
Date: 2010-09-14 10:12:55

On Tue, Sep 14, 2010 at 8:26 AM, Roland Bock <rbock_at_[hidden]> wrote:
> currently I am working on a set of header files which are supposed to make
> the life of C++ developers easier when in comes to interacting with
> databases (based on SQL). Goals are:
>
> * Stay close to SQL syntax
> * Do as much type checking as possible

Static type checking is always a plus. I tried going that route too,
but found it a bit cumbersome (and my C++ lacking too ;) One of the
problem with trying to generate queries like this is also the
complexity of the SQL language itself, with nested queries, and all
the various extensions and idiosyncrasies of the different SQL
vendors. I've lurked on the Oracle forums for a few months, and it's
open my eyes to the complexity of SQL (the Oracle dialect at least).

> * prevent oversights and typical security holes, e.g. by forgetting to
> escape strings

Using bind values also avoids such issues, AFAIK

> The basic idea is, that given a table definition, it should be possible to
> * offer a lot of checks at compile time for building a query
> * make it hard to misinterpret the results

The few C++ libraries I've seen in this area take a less ambitious
approach, and typically provide instead an easy way to pass typed C++
values as SQL bind values, and get back the records as typed tuples
for examples. The SQL text must still be correct, and will be parsed
and type checked only at runtime by the DB, but in practice, with unit
testing, I find that it works fairly well.

A good and established library already mentioned on this list in this
domain is http://soci.sourceforge.net/, which uses modern C++ and
Boost.

With Steven Watanabe's help from this list, I used a little fusion +
tuple magic to easily bind and fetch records in a similar manner with
SQLite, but that only checks arity of the select clause, and that
enough bind values are provided, and it's not checking the values
provided or gotten are of the right type at compile time. From my
experience though, after having tried the statically type-checked
approach as well, I found such an approach to be flexible and "good
enough".

> Until now, I have been concentrating on table definitions and select statements.

If you go that route, you'll need a way to generate the static C++
definition of the tables from existing Schemas, otherwise maintaining
these to be in sync might be problematic. Then again the same applies
to all the SQL text in more traditional approaches.

> I wonder if there would be interest for such a library in boost.

Getting a library accepted in Boost is a tall order for sure. I'm just
a lurker here, but given the recent threads on a GUI lib and a C++
Parser, I'd say you'd need to commit several months of hard work and
have a pretty thick skin too. That's not even getting into which
backend DB the library would support, the SQL vs NoSQL debate, etc...

My own feeling is that you should explore this subject into your own
public project for while, and try to attract a community of
volunteers. Then again, once you've reached some kind of milestone you
may want to revisit a possible submission to Boost. But I'm certainly
no expert in the matter. Good luck to you, --DD