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

Subject: Re: [boost] sqlpp11: SQL for C++
From: Matthijs Möhlmann (matthijs_at_[hidden])
Date: 2013-11-12 11:53:08

On 11/12/13, 2:17 PM, Roland Bock wrote:
> On 2013-11-12 10:00, Matthijs Möhlmann wrote:
>> On 11/12/13, 4:26 AM, Tim Keitt wrote:
>>> On Sat, Nov 9, 2013 at 4:03 PM, Roland Bock <rbock_at_[hidden]> wrote:
>>>
>>>> Hi,
>>>>
>>>> over the last four or five years I developed several SQL libraries for
>>>> C++. With C++11 I was finally able to create an SQL library that meets
>>>> my own expectations and requirements. It is being used in production, I
>>>> recently put it on github, and I would really like to hear from you
>>>> guys
>>>> whether this is something that could be interesting for you personally
>>>> or for boost?
>>>>
>>>> https://github.com/rbock/sqlpp11
>>>> https://github.com/rbock/sqlpp11/wiki
>>>>
>>>> sqlpp11 is a template library representing an embedded domain specific
>>>> language (EDSL) that allows you to
>>>>
>>>>     * define types representing tables and columns,
>>>>     * construct type safe queries checked at compile time for syntax
>>>>       errors, type errors, name errors and even some semantic errors,
>>>>     * interpret results by iterating over query-specific structs with
>>>>       appropriately named and typed members.
>>>>
>>>> This results in several benefits, e.g.
>>>>
>>>>     * the library user operates comfortably on structs and functions,
>>>>     * the compiler reports many kinds of errors long before the code
>>>>       enters unit testing or production,
>>>>     * the library hides the gory details of string construction for
>>>>       queries and interpreting string based results returned by select
>>>>       calls. I.e. you don't need to use strings in query construction
>>>>       where you wouldn't use them in SQL and there is no need to use
>>>>       positional arguments or to parse strings when obtaining fields
>>>> from
>>>>       a result row (the latter being true unless you do not know which
>>>>       columns to select at compile time).
>>>>
>>>> The library supports both static and dynamic queries. The former offers
>>>> greater benefit in terms of type and consistency checking. The latter
>>>> makes it easier to construct queries on the flight.
>>>>
>>>> Specific traits of databases (e.g. unsupported or non-standard
>>>> features)
>>>> are known at compile time as well. This way, the compiler can tell the
>>>> developer at compile time if a query is not accepted by the database
>>>> (e.g. if a feature is missing). And the library can form the query in
>>>> the correct manner, for instance if the engine uses concat instead of
>>>> operator|| to concatenate strings.
>>>>
>>>> Two Examples:
>>>> =============
>>>> Static Select:
>>>> --------------
>>>> // selecting zero or more results, iterating over the results
>>>> for (const auto& row : db.run(
>>>>                           select(foo.name, foo.hasFun)
>>>>                           .from(foo)
>>>>                           .where(foo.id > 17 and
>>>> foo.name.like("%bar%"))))
>>>> {
>>>>     if (row.name.is_null())
>>>>       std::cerr << "name will convert to empty string" << std::endl;
>>>>     std::string name = row.name;  // text fields are implicitly
>>>>                                      convertible to string
>>>>     bool hasFun = hasFun;         // bool fields are implicitly
>>>>                                      convertible to bool
>>>> }
>>>>
>>>> Dynamic Select:
>>>> ----------------
>>>> auto s = dynamic_select(db, tab.foo).dynamic_columns().from(tab);
>>>> if (userWantsBar)
>>>>     s.add_column(tab.bar);
>>>> for(const auto& row : run(s))
>>>> {
>>>>     std::cerr << "row.foo: " << row.foo;
>>>>     if (userWantsBar)
>>>>       std::cerr << "row.bar" << row.at("bar");
>>>>     std::cerr << std::endl;
>>>> };
>>>>
>>>>
>>>> Please let me know your questions/thoughts/suggestions/rants.
>>>> Contributions welcome, of course :-)
>>>>
>>> This is an interesting thread and I thought I'd comment.
>>>
>>> I am a pretty heavy user of postgresql/postgis (spatial extension) in my
>>> work. I wrote the first R package to access postgresql and
>>> contributed to
>>> the current R DBI package. I did a proof-of-concept (= not very
>>> pretty ;-)
>>> embedding of the Boost Graph Library in postgresql replacing local
>>> storage
>>> with prepared queries called on demand.
>>>
>>> I have to say when I look at this, I don't really want to learn another
>>> SQL. I am perfectly happy to send query strings to the database and
>>> let it
>>> parse them. I can debug these separate from my C++ code. I think for
>>> complex queries (recursive with anyone?) it would be quite difficult
>>> to get
>>> the C++ right.
>>>
>>> What I would really like is a mapping of binary cursors to iterator
>>> concepts + easy type-safe endian-aware customizable data conversion. But
>>> that's my bias. I've always liked mapping on-demand data to common
>>> interfaces.
>>>
>>> But your use cases are probably different and I can see how this
>>> would be
>>> very useful to some.
>>>
>>> THK
>>>
>> Hi,
>>
>> Isn't libpqxx then not what you are looking for? I know its
>> specifically written
>> for PostgreSQL.
>>
>> http://pqxx.org/development/libpqxx/
>>
>> Regards, Matthijs
>>
> It also string based, isn't it? For those who'd like to experiment with
> sqlpp11 and postgreSQL: I just received a mail that there is a first
> connector library for postgreSQL available:
>
> https://github.com/pbondo/sqlpp11-connector-postgresql
>
> Regards,
>
> Roland
>
It is string based yes.
Nice, I'll experiment with that.

Regards, Matthijs