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

12 Nov 2013


      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  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