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

Subject: Re: [boost] sqlpp11: SQL for C++
From: Roland Bock (rbock_at_[hidden])
Date: 2013-11-12 08:17:57

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