[boost] sqlpp11: SQL for C++

[Roland Bock]

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


Regards,

Roland

_______________________________________________
Unsubscribe & other changes: http://lists.boost.org/mailman/listinfo.cgi/boost

[Michael Marcin]

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?

[Roland Bock]

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.


sqlpp11 assumes that you know your tables at compile time. Thus you can
declare types representing tables and columns with appropriate names and
types. You can then construct SQL queries and analyze the results with
the full armory of syntax and type checking that C++ and template meta
programming have to offer.


Regards,

Roland

[Mateusz Loskot]

On 9 November 2013 22:58, Roland Bock <rb...@eudoxos.de> 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.

Yes, that's correct.

In fact, SQL commands play an important role as part of DBMS access
abstraction. SQL is a part of SOCI interface, by design.

> 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.
>
> sqlpp11 assumes that you know your tables at compile time. Thus you can
> declare types representing tables and columns with appropriate names and
> types. You can then construct SQL queries and analyze the results with
> the full armory of syntax and type checking that C++ and template meta
> programming have to offer.

Thank you for this clarification.

I think sqlpp11 is an extremely interesting exercise.

I have been considering to add non-string layer to SOCI directly based
on your idea, soon after I saw it some time ago in your initial experiments.

Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net

[Roland Bock]

On 2013-11-10 00:12, Mateusz Loskot wrote:
> On 9 November 2013 22:58, Roland Bock <rb...@eudoxos.de> 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.
> Yes, that's correct.
>
> In fact, SQL commands play an important role as part of DBMS access
> abstraction. SQL is a part of SOCI interface, by design.
>
>> 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.
>>
>> sqlpp11 assumes that you know your tables at compile time. Thus you can
>> declare types representing tables and columns with appropriate names and
>> types. You can then construct SQL queries and analyze the results with
>> the full armory of syntax and type checking that C++ and template meta
>> programming have to offer.
> Thank you for this clarification.
>
> I think sqlpp11 is an extremely interesting exercise.

:-)

>
> I have been considering to add non-string layer to SOCI directly based

One option would be to write a sqlpp11 connector library for SOCI,
similar to the existing ones for using MySQL/MariaDb and Sqlite3.

> on your idea, soon after I saw it some time ago in your initial experiments.
>
> Best regards,

You're referring to the discussion in 2010?
http://comments.gmane.org/gmane.comp.lib.boost.devel/208623

Cool, I wasn't aware of the impact :-)



sqlpp11 is much more mature, than what I had back then. Among other things

* auto allows for a much leaner perceived API
* table definitions are also much simpler. I am still using a code
generator for those (DDL->C++, which I'll add to the repository
soon), but the types are quite comprehensible even for a casual
user, I guess
* compile times are better
* sqlpp11 "understands" sub-selects, which were a nightmare in all my
previous attempts. Sub-selects can be used very naturally now, i.e.
as selected values, as input for functions like exists(), as
operands in where-conditions and as pseudo-tables in from(). The
documentation on that is still pretty thin, I guess, but there are
usage hints here and there:
https://github.com/rbock/sqlpp11/wiki/Select#sub-select
https://github.com/rbock/sqlpp11/wiki/Select#aliased-sub-select
* you can add columns to your select at runtime now, if required
* I would not call the library code simple, but it is certainly much
easier to understand and to extend than any of the earlier versions


Cheers,

Roland

[niXman]

Roland Bock 2013-11-10 02:03:
> Hi,

Hi,

Code depends on boost?


--
Regards, niXman
___________________________________________________
Dual-target(32 & 64-bit) MinGW-W64 compilers for 32 and 64-bit Windows:
http://sourceforge.net/projects/mingw-w64/
___________________________________________________
Another online IDE: http://liveworkspace.org/

[Roland Bock]

On 2013-11-10 02:38, niXman wrote:
> Roland Bock 2013-11-10 02:03:
>> Hi,
>
> Hi,
>
> Code depends on boost?
>
>

No, sqlpp11 itself only depends on a decent C++11 compiler and matching
STL. I test with clang-3.2 and gcc-4.8 on Ubuntu as of now. We are using
a version slightly older than the current release with clang-3.1 on FreeBSD.

Connectors could have other dependencies of course. The current
connectors for MySQL/MariaDb and Sqlite3 depend on the respective
C-client libraries. The MySQL connector currently requires the
thread_local feature, so clang3.2 won't work, but I think I'll replace
that with std::call_once.

Regards,

Roland

[Roland Bock]

On 2013-11-10 15:58, niXman wrote:
> Roland Bock 2013-11-10 12:15:

>
>> No, sqlpp11 itself only depends on a decent C++11 compiler and matching
>> STL.
>

> I think I can write a code generator using boost.preprocessor. But I
> need the documentation of DDL, of course.
>

I was talking about SQL's DDL
(http://en.wikipedia.org/wiki/Data_definition_language). Unfortunately
every vendor seems to be using some additions, so that the task is not
too simple in all generality, I guess...

But using the preprocessor to generate the table types could be a very
nice tool in some cases. For instance if you are using in-memory
databases with Sqlite3. As a trivial byproduct it could also produce
the create table string, so that I would not have to maintain that in
two places. Also for such a purpose, it would make sense to restrict the
DDL to a common subset.

With larger tables, I guess I'd stick with my current approach though:
running a separate code-generator as part of the build process. The main
benefit is that I get clearer error message pointing to C++ code that
exists outside the compiler memory in case the code generator produces
incorrect output or I use the generated code in the wrong way, etc.

Another benefit of using external code generators is string handling. In
our company we use CamelCase for classes, camelCase for members and
underscore separated compounds for SQL names. An external code generator
can easily translate, of course, but the preprocessor is not equipped
for string manipulation, afaik.


But again, I can imagine a nice usage of a prepocessor-based code
generator for in-memory databases with no external representation.

[Larry Evans]

On 11/09/13 16:03, 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
>

[snip]
The code here:

https://github.com/rbock/sqlpp11/blob/master/include/sqlpp11/table_base.h

contains:

template<typename Table, typename... ColumnSpec>
struct table_base_t
: public ColumnSpec::_name_t::template
_member_t<column_t<Table, ColumnSpec>>...
{...};

which looks like it declares a table row, where the columns
come from ColumnSpec...

What's confusing to me is the code here:

https://github.com/rbock/sqlpp11/wiki/Tables

contains, what I assume are the ColumnSpecs; however,
there's no column_t, there's only column_type.
Does table_base.h have a typo?

-regards,
Larry

[Larry Evans]

OOPS. I see it now:

https://github.com/rbock/sqlpp11/blob/master/include/sqlpp11/column.h

[Roland Bock]

On 2013-11-11 01:47, Larry Evans wrote:
> On 11/10/13 18:02, Larry Evans wrote:
>> On 11/09/13 16:03, 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
>>>
>> [snip]
>> The code here:
>>
>> https://github.com/rbock/sqlpp11/blob/master/include/sqlpp11/table_base.h
>>
>>
>> contains:
>>
>> template<typename Table, typename... ColumnSpec>
>> struct table_base_t
>> : public ColumnSpec::_name_t::template
>> _member_t<column_t<Table, ColumnSpec>>...
>> {...};
>>
>> which looks like it declares a table row, where the columns
>> come from ColumnSpec...

In understanding how the library works, this is probably one of the
crucial parts.
As you guessed correctly, it declares the innards of a table class. The
somewhat convoluted looking inheritance adds columns as members to the
class. The

column_t<Table, ColumnSpec>

instantiates a column class with all required information like the value
type, whether it can be NULL, its textual representation, etc. The
ColumnSpec also contains a template like this:

template<typename T>
struct _member_t
{
T foo;
};


Inheriting from an instance of this template adds a member with the
respective name (foo in this example) to derived class.
Thus, the code you cited adds objects representing columns as
appropriately named members to the table class.


The same technique is used when creating aliases for expressions, using
selects as pseudo tables and constructing result row classes.

>>
>> What's confusing to me is the code here:
>>
>> https://github.com/rbock/sqlpp11/wiki/Tables
>>
>> contains, what I assume are the ColumnSpecs; however,
>> there's no column_t, there's only column_type.
>> Does table_base.h have a typo?
>>
> OOPS. I see it now:
>
> https://github.com/rbock/sqlpp11/blob/master/include/sqlpp11/column.h

I guess that part of the confusion is due to names. I know that the code
has inconsistent use of _type and _t suffix, which certainly doesn't
help and is on my list of todos. Having column_t and a member class
_column_type is probably also adding to the confusion.

Maybe renaming _column_type to _column_traits_t would be better?


Best regards,

Roland

[Rodrigo Madera]

On Sat, Nov 9, 2013 at 8:03 PM, Roland Bock <rb...@eudoxos.de> wrote:

> Hi,
>

Hello,

Just curious over some points:

Does it support binary transfers over the wire?
Does it support bulk operations?
Will it be C++11 only?

Regards,
Rodrigo Madera

[Thorsten Ottosen]

On 09-11-2013 23:03, Roland Bock wrote:
> Hi,
>
>

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

Very interesting.

Have you considered a syntax like

auto results = db >> select(Person.name,Person.age) >> from(foo) >>
where( Person.age > 42);

-Thorsten

[Larry Evans]

On 11/11/13 04:49, Thorsten Ottosen wrote:
> On 09-11-2013 23:03, Roland Bock wrote:
>> Hi,
>>
>>
>> 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.
>
> Very interesting.
>
> Have you considered a syntax like
>
> auto results = db >> select(Person.name,Person.age) >> from(foo) >>
> where( Person.age > 42);
>
> -Thorsten
>

Or maybe using << instead of >>. The reason for using << is it's
suggestive of karma operators. I'm also guessing that:

auto results =
db << select(Person.name,Person.age)
<< from(foo)
<< where( Person.age > 42);

would be translated into some SQL string that is then passed
to the actual dbms thru the connection and the result is
then returned. This again sounds vaguely like what
karma does, only karma just formats the data structure
into a string. Was karma ever considered as part of the
implementation?

-Larry

[Roland Bock]

On 2013-11-11 11:27, Rodrigo Madera wrote:
> On Sat, Nov 9, 2013 at 8:03 PM, Roland Bock <rb...@eudoxos.de> wrote:
>
>> Hi,
>>
> Hello,
>
> Just curious over some points:
>
> Does it support binary transfers over the wire?

The library creates template expression trees for the queries. As of
now, the tree is serialized and sent to the database connector when you
call

db.run(query)

Result rows are currently to be returned from the connector's result
object as const char**, see database_api/api.h.

Both, the serialization of the query and the representation of results
rows as const char** are part of one possible interface, which happens
to work quite well with some databases. Since we have all information in
the expression tree, it should be possible to send queries and receive
results in different ways.

Can you send me links to respective database APIs? Preferably ones that
I can experiment with on Ubuntu?

> Does it support bulk operations?

You mean like multi-inserts? Not right now, but that wouldn't be too
hard to do, I think.

> Will it be C++11 only?

That's what I tried to imply with the '11' in sqlpp11 ;-)

All kidding aside:

* C++03: I think It would be possible to translate the library to
C++03 using boost MPL and BOOST_AUTO. I won't go there, personally,
but feel free to do so.
* C++14 and up: I have every intention to create respective sqlpp14
and up.


Regards,

Roland

[Larry Evans]

On 11/09/13 16:03, 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

Hi Roland,

I 'git cloned' the library and looked at some of the files.
Apparently there are tabs in the file. IIRC, tabs are not
supposed to be in the boost source code. Also, the header
files have a .h extension. Again, IIRC, boost headers should
have .hpp extension.

-Larry

[Roland Bock]

On 2013-11-11 13:40, Larry Evans wrote:
> On 11/11/13 04:49, Thorsten Ottosen wrote:
>> On 09-11-2013 23:03, Roland Bock wrote:
>>> Hi,
>>>
>>>
>>> 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.
>>
>> Very interesting.

Thanks :-)

>>
>> Have you considered a syntax like
>>
>> auto results = db >> select(Person.name,Person.age) >> from(foo) >>
>> where( Person.age > 42);
>>
>> -Thorsten
>>
> Or maybe using << instead of >>. The reason for using << is it's
> suggestive of karma operators. I'm also guessing that:
>
> auto results =
> db << select(Person.name,Person.age)
> << from(foo)
> << where( Person.age > 42);
>
> would be translated into some SQL string that is then passed
> to the actual dbms thru the connection and the result is
> then returned. This again sounds vaguely like what
> karma does, only karma just formats the data structure
> into a string. Was karma ever considered as part of the
> implementation?
>
> -Larry

I experimented with such a syntax in 2010 (using <<) and I prefer the
member functions personally. The reason is that if you have

auto s1 = select(Person.name, Person.age);
auto s2 = s1.from(Person);
auto s3 = s2.where(Person.age > 42);

then s1, s2 and s3 have different types (sqlpp is creating template
expression trees). I would not expect this to happen in case of stream
operators.

I have no real knowledge of boost.karma, to be honest. So I can't really
tell whether or it would be useful in this context.

I understand that boost.proto could be helpful in creating the EDSL and
processing such trees. I tried to use boost.proto in 2010 but failed to
wrap my head around it (and then gave up due to the compile times).
During Meeting C++ in Dusseldorf last weekend, Eric encouraged me to try
again, when there is a full C++11 version of proto :-)

Feel free to jump in and give it a try with karma or proto or both :-)

Regards,

Roland

[Roland Bock]

On 2013-11-11 14:13, Larry Evans wrote:
> On 11/09/13 16:03, 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
>
> Hi Roland,
>
> I 'git cloned' the library and looked at some of the files.
> Apparently there are tabs in the file. IIRC, tabs are not
> supposed to be in the boost source code. Also, the header
> files have a .h extension. Again, IIRC, boost headers should
> have .hpp extension.
>
> -Larry
>

Hi Larry,

Thanks for the hint! Well, there are quite a few things not in a state
that I would dare to ask for a formal review...

I consider the library very useful right now and I wouldn't want to do
SQL programming in C++ without it. And I would love to see sqlpp11 or
something similar to become part of boost and/or STL one fine day, but
there are quite a few miles to go to get there.

I'll put file names and no-tabs onto my growing ToDo list. These are
probably among the earlier items to be ticked off :-)

Best regards,

Roland

[Larry Evans]

Ah. I see. So tab_sample, if declared as:

TabSample tab_sample;

where TabSample is from:

https://github.com/rbock/sqlpp11/blob/master/tests/TabSample.h

could be used in expressions like:

tab_sample.alpha;
tab_sample.beta;
tab_sample.gamma;

Nice! Makes meaningful column names.

However, instead of table_base_t, would maybe row_base_t
be a better name since it's really a row in a table?

>
> The same technique is used when creating aliases for expressions, using
> selects as pseudo tables and constructing result row classes.
>
>
>>>
>>> What's confusing to me is the code here:
>>>
>>> https://github.com/rbock/sqlpp11/wiki/Tables
>>>
>>> contains, what I assume are the ColumnSpecs; however,
>>> there's no column_t, there's only column_type.
>>> Does table_base.h have a typo?
>>>
>> OOPS. I see it now:
>>
>> https://github.com/rbock/sqlpp11/blob/master/include/sqlpp11/column.h
>
> I guess that part of the confusion is due to names. I know that the code
> has inconsistent use of _type and _t suffix, which certainly doesn't
> help and is on my list of todos. Having column_t and a member class
> _column_type is probably also adding to the confusion.
>
> Maybe renaming _column_type to _column_traits_t would be better?

Yes, a bit. However, I must confess, I was a bit careless
while reading the code. Sorry about that :(

[Edward Diener]

On 11/11/2013 7:53 AM, Roland Bock wrote:
> On 2013-11-11 11:27, Rodrigo Madera wrote:
>> On Sat, Nov 9, 2013 at 8:03 PM, Roland Bock <rb...@eudoxos.de> wrote:
>>
>>> Hi,
>>>
>> Hello,
>>
>> Just curious over some points:
>>
>> Does it support binary transfers over the wire?
> The library creates template expression trees for the queries. As of
> now, the tree is serialized and sent to the database connector when you
> call
>
> db.run(query)
>
> Result rows are currently to be returned from the connector's result
> object as const char**, see database_api/api.h.

You are kidding ? Is std::vector<std::string> too advanced ? Why C++
programmers are still using C null-terminated strings I will never
understand.

[TONGARI J]

Hi,

2013/11/10 Roland Bock <rb...@eudoxos.de>

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

That's almost what I was looking for :D

Thus far, I used SOCI with my query wrapper like below:

BOOST_FUSION_DEFINE_STRUCT
(
(opti), user,
(std::string, account)
(std::string, pwd)
(std::string, name)
(unsigned, role)
)

soci::session sql;
query<with_id<user>*()> query_users(sql, "SELECT id, account, pwd,
name, role FROM user";

for (auto&& user : query_users()) {...}

> --------------
> // 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%"))))
>

Is it possible to separate the placeholders of columns & tables? I feel it
redundant to say table.column if not ambiguous.
In case of more than one table, maybe table[column] is a good syntax for
that.

Does it support prepared statement?
Maybe another set of placeholders (_1, _2, ...) can be used to generate the
functor.

Also, it'll be great if it works with Fusion & Optional, etc...

[Roland Bock]

On 2013-11-11 15:36, Edward Diener wrote:
> On 11/11/2013 7:53 AM, Roland Bock wrote:
>> On 2013-11-11 11:27, Rodrigo Madera wrote:
>>> On Sat, Nov 9, 2013 at 8:03 PM, Roland Bock <rb...@eudoxos.de> wrote:
>>>
>>>> Hi,
>>>>
>>> Hello,
>>>
>>> Just curious over some points:
>>>
>>> Does it support binary transfers over the wire?
>> The library creates template expression trees for the queries. As of
>> now, the tree is serialized and sent to the database connector when you
>> call
>>
>> db.run(query)
>>
>> Result rows are currently to be returned from the connector's result
>> object as const char**, see database_api/api.h.
>
> You are kidding ? Is std::vector<std::string> too advanced ? Why C++
> programmers are still using C null-terminated strings I will never
> understand.

No kidding. The database client libraries I've used so far are C
libraries yielding char**. That is why the current connector interface
also uses const char**. BTW: These are typically not null-terminated but
are delivered with a size_t* or similar to inform about the length...

But:

1. As a user of the library you don't get in touch with those. The
members of a row are NOT char*, of course. You get integers as
integers, floats as floats and of course you get text as
std::string. I wouldn't dare to use char* there ;-)
2. It would be really easy to add additional interface options so that
the connector library could yield std::vector<std::string>


Regards,

Roland

[Larry Evans]

On 11/11/13 09:01, Roland Bock wrote:
> On 2013-11-11 15:36, Edward Diener wrote:
>> On 11/11/2013 7:53 AM, Roland Bock wrote:

[snip]

>>> Result rows are currently to be returned from the connector's result
>>> object as const char**, see database_api/api.h.
>>
>> You are kidding ? Is std::vector<std::string> too advanced ? Why C++
>> programmers are still using C null-terminated strings I will never
>> understand.
> No kidding. The database client libraries I've used so far are C
> libraries yielding char**. That is why the current connector interface
> also uses const char**. BTW: These are typically not null-terminated but
> are delivered with a size_t* or similar to inform about the length...
>
> But:
>
> 1. As a user of the library you don't get in touch with those. The
> members of a row are NOT char*, of course. You get integers as
> integers, floats as floats and of course you get text as
> std::string. I wouldn't dare to use char* there ;-)

Since the return values from the database are char**, these must be
parsed and converted into a vector of, for example, TabSample,

where TabSample is from:

https://github.com/rbock/sqlpp11/blob/master/tests/TabSample.h

Sounds like a job for spirit. However, I guess this option was
rejected for reasons similar to why proto was rejected for
creating the sql string to the dbms, as expressed in your
reply to my other post:

http://article.gmane.org/gmane.comp.lib.boost.devel/246117

Is that right?

[snip]

[Roland Bock]

Exactly! And this is transported into the result rows as well, e.g.

for (const auto& row: db.run(select(all_of(tab_sample)).from(tab_sample)))
{
std::cerr << row.alpha << '\n';
std::cerr << row.beta << '\n';
std::cerr << row.gamma << '\n';
}

And they also have appriate types of course :-)

> However, instead of table_base_t, would maybe row_base_t
> be a better name since it's really a row in a table?

It is a set of columns, which more or less represents a table (if you
ignore indexes and foreign keys, etc). So I wouldn't say that it is a row.

[Bjorn Reese]

On 11/09/2013 11:03 PM, Roland Bock wrote:

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

I think it could be a very useful addition.

There are two potential extensions that may be worth considering. I do
not consider these extensions required for adoption in Boost, but it may
be a good idea to at least have given them some thought.

First, make sqlpp11 work on arbitrary data structures. For instance, SQL
is just one data provider in LINQ.

Second, consider adding streaming SQL. Rx is an example of this. This
could be done with a Boost.Asio integration.

http://rx.codeplex.com/

> * define types representing tables and columns,

There seems to be room for improvement here. You should consider an
integration with Boost.Fusion. This way you can avoid having to build
a code generator.

[Roland Bock]

First, the result is not a sequence of TabSample. The result row type is
created based on what you select. That could be analogous to TabSample's
rows, but you could select fewer or more columns or different orders...

The current status is that the members of the result row parse "their"
part of the char**, based on the position and the type of the selected
column, see for instance:

https://github.com/rbock/sqlpp11/blob/master/include/sqlpp11/integral.h

Look for struct _result_entry_t, currently at line 51.

There are respective classes for floating_point, text and bool.


These classes could certainly be optimized. Spirit might be able to help
here, but yes, you are right, I fear the compile times. In our code base
we have hundreds of queries to compile. So compile time is an issue for
continuous integration.


Regards,

Roland

[Rodrigo Madera]

On Mon, Nov 11, 2013 at 10:53 AM, Roland Bock <rb...@eudoxos.de> wrote:

> Result rows are currently to be returned from the connector's result
> object as const char**, see database_api/api.h.
>

IIRC, when debate was taking place on whether SOCI should be accepted,
there was concern regarding the lack of binary format communications
between the client and server. Adding a binary communications layer to SOCI
was non-trivial at the time.

How hard would it be to use binary transports? (when available, of course)

In performance critical scenarios, this is a major benefit.

Rodrigo Madera

[Larry Evans]

After some more thought, I think Spirit (really spirit's qi) would be
overkill since really only parsing primitive types (e.g. int, float,
string) are needed.

I ran the SelectTest and got:
-{--cut here--
------------------------
SELECT tab_sample.alpha,tab_sample.beta,tab_sample.gamma FROM
tab_sample WHERE ((tab_sample.alpha>7)AND(tab_sample.alpha=ANY(SELECT
tab_sample.alpha FROM tab_sample WHERE (tab_sample.alpha<3)))) LIMIT
3------------------------
SELECT
tab_sample.alpha(tab_sample.beta||'hallenhalma')(tab_sample.beta||'hallenhalma')tab_sample.alpha
3
(SELECT DISTINCT STRAIGHT_JOIN left.alpha,left.beta,(SELECT right.a
FROM (SELECT (tab_sample.gamma) AS a FROM tab_sample WHERE
(tab_sample.gamma=1)) AS right) FROM (tab_sample) AS left,(SELECT
(tab_sample.gamma) AS a FROM tab_sample WHERE (tab_sample.gamma=1)) AS
right WHERE ((tab_sample.beta='hello world')ANDSELECT tab_sample.gamma
FROM tab_sample) GROUP BY left.gamma,right.a HAVING (right.a!=1) ORDER
BY left.beta ASC LIMIT 17 OFFSET 3) AS a
Compilation finished at Mon Nov 11 11:08:27
-}--cut here--

It would really help if the outputs were prefixed with some title.
That way, one could look where the title in the source is output,
and easily find the output from the code.

I assume that the output bracketed by the:
------------
lines are produced by lines 305-316:
-{--cut here--
{
auto s = dynamic_select(db,
all_of(t)).dynamic_from().dynamic_where().dynamic_limit().dynamic_offset();
s = s.add_from(t);
s = s.add_where(t.alpha > 7 and t.alpha ==
any(select(t.alpha).from(t).where(t.alpha < 3)));
s = s.set_limit(30);
s = s.set_limit(3);
std::cerr << "------------------------\n";
s.serialize(std::cerr, db);
std::cerr << "------------------------\n";
using T = decltype(s);
static_assert(sqlpp::is_regular<T>::value, "type requirement");
-}--cut here--
Is there an actual run against a real database somewhere in the tests?
The 2 .cpp files I checked (SelectTest.cpp and UpdateTest.cpp) both
use db of type MockDb, which doesn't seem to be connected to any real
database.
[snip]

-Larry

[Roland Bock]

On 2013-11-11 16:00, TONGARI J wrote:
> Hi,
>
> 2013/11/10 Roland Bock <rb...@eudoxos.de>
>
>> 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
>>
> That's almost what I was looking for :D

Nice to hear that :-)

>> --------------
>> // 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%"))))
>>
> Is it possible to separate the placeholders of columns & tables? I feel it
> redundant to say table.column if not ambiguous.

Hmm, you could make copies of the column members, of course.

constexpr TabSample tab = {};
constexpr auto alpha = tab.alpha;
...

Maybe but the columns into a namespace to be able to differentiate in
case of ambiguities.

> In case of more than one table, maybe table[column] is a good syntax for
> that.

I don't think so. A table has a given set of member columns. Using any
kind of lookup is contrary to what I want to achieve.

>
> Does it support prepared statement?

Not yet. But it is a good point.

> Maybe another set of placeholders (_1, _2, ...) can be used to generate the
> functor.

Would you want placeholders for values? Or for columns as well? The
former shouldn't be too hard. The latter is a different story, though...

>
> Also, it'll be great if it works with Fusion & Optional, etc...

I can see Fusion for table definition (and I would require some help
with that), but where would you see Optional?

[Abel Sinkovics]

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"));

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.

The documentation of Metaparse is here:
http://abel.web.elte.hu/mpllibs/metaparse/

You can find examples here:
https://github.com/sabel83/mpllibs/tree/master/libs/metaparse/example

Regards,
Ábel

[Steven Watanabe]

AMDG

On 11/11/2013 11:16 AM, Roland Bock wrote:
> On 2013-11-11 16:00, TONGARI J wrote:
>>
>> In case of more than one table, maybe table[column] is a good syntax for
>> that.
> I don't think so. A table has a given set of member columns. Using any
> kind of lookup is contrary to what I want to achieve.
>

This syntax doesn't necessarily have to look up anything.
If each column has a distinct type, then table[column]
can be resolved at compile time, and is essentially
equivalent to table.column.

In Christ,
Steven Watanabe

[Roland Bock]

On 2013-11-11 16:30, Bjorn Reese wrote:
> On 11/09/2013 11:03 PM, Roland Bock wrote:
>
>> 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?
>
> I think it could be a very useful addition.

Thanks :-)

>
> There are two potential extensions that may be worth considering. I do
> not consider these extensions required for adoption in Boost, but it may
> be a good idea to at least have given them some thought.
>
> First, make sqlpp11 work on arbitrary data structures. For instance, SQL
> is just one data provider in LINQ.

I haven't given this much thought yet, but I believe that this should be
possible by using a different connector. sqlpp11 itself has no idea
about evaluating the query. In particular, it does not know anything
about database connections.
It constructs a query for you which you then can run. And if your result
has the correct type, you can interpret it using sqlpp11's result object.

Whether the evaluation happens with a database, or by analyzing some XML
or JSON, is totally up to the connector.

Of course, as mentioned elsewhere, it might be useful to create a query
representation which is not a string in those cases :-)

>
> Second, consider adding streaming SQL. Rx is an example of this. This
> could be done with a Boost.Asio integration.
>
> http://rx.codeplex.com/

I just heard about Rx for the first time the other day, so let me see if
I get the idea right: Instead of querying a database or a given XML
file, you evaluate the query against a stream of incoming data. If a row
matches, you call a callback with the selected data?

If that's the general idea, I think that can be done as well: A library
for evaluating incoming data would get some representation of the
template expression and maybe the result_row type.


In my opinion, both extensions could be done in additional libraries
that make use of the query expressions generated by sqlpp11. A few
additions to the interface should be sufficient.

>
>> * define types representing tables and columns,
>
> There seems to be room for improvement here. You should consider an
> integration with Boost.Fusion. This way you can avoid having to build
> a code generator.

There seems to be a great deal of demand for using Boost.Fusion for the
Table/Column types.

It would be great if someone could lend me a hand with this. I am going
to update the information in the wiki on what kind information sqlpp11
requires asap (hopefully this weekend).


PS:
In the mail you forwarded me this morning you also asked if I were aware
of N3612?

http://www.open-std.org/jtc1/sc22/wg21/docs/papers/2013/n3612.pdf

Yes, and I agree with most of the described requirements and wanted to
throw my hat in the ring. Personally, I believe that the EDSL-approach
is the way to pursue. Falling back to string based is always possible, a
library like sqlpp11 does not prevent that.

[Larry Evans]

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?
[snip]

[Rodrigo Madera]

On Mon, Nov 11, 2013 at 10:53 AM, Roland Bock <rb...@eudoxos.de> wrote:

> On 2013-11-11 11:27, Rodrigo Madera wrote:
> > Does it support binary transfers over the wire?
> The library creates template expression trees for the queries. As of
> now, the tree is serialized and sent to the database connector when you
> call
>
> db.run(query)
>
> Result rows are currently to be returned from the connector's result
> object as const char**, see database_api/api.h.
>
> Both, the serialization of the query and the representation of results
> rows as const char** are part of one possible interface, which happens
> to work quite well with some databases. Since we have all information in
> the expression tree, it should be possible to send queries and receive
> results in different ways.
>
> Can you send me links to respective database APIs? Preferably ones that
> I can experiment with on Ubuntu?
>

Roland,

Sorry I didn't see your reply. I even sent a second message without seeing
this.

Answering your question on database APIs for bin transfer, I don't know of
any. I always use top layers, like SOCI. I use it heavily.

If you wish to see details about binary transfer I suppose the SOCI authors
could give you pointers on those, and maybe the archives for this ML when
that discussion was going on. Not sure, though.

Regards,
Rodrigo Madera

[Roland Bock]

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

[Roland Bock]

Hi Larry,

those tests are really just tests, mostly compile time test to ensure
that expressions yield the right types. I will have to add quite a few
additional ones which check the generated strings.

There are two connector libraries (MySQL/MariaDb and Sqlite3) listed here:

https://github.com/rbock/sqlpp11#requirements

They also contain tests, and those tests actually do something, even
though it is not much :-)


Based on that it should be rather simple to create a connector library
for other databases.

What environment (OS, compiler) are you using, btw?

Cheers,

Roland

[Roland Bock]

On 2013-11-11 20:44, Steven Watanabe wrote:
> AMDG
>
> On 11/11/2013 11:16 AM, Roland Bock wrote:
>> On 2013-11-11 16:00, TONGARI J wrote:
>>> In case of more than one table, maybe table[column] is a good syntax for
>>> that.
>> I don't think so. A table has a given set of member columns. Using any
>> kind of lookup is contrary to what I want to achieve.
>>
> This syntax doesn't necessarily have to look up anything.
> If each column has a distinct type, then table[column]
> can be resolved at compile time, and is essentially
> equivalent to table.column.
>
> In Christ,
> Steven Watanabe

Agreed, but it would look like a lookup, wouldn't it? I would consider
that confusing.

Regards,

Roland

[Roland Bock]

On 2013-11-11 21:22, Rodrigo Madera wrote:
> On Mon, Nov 11, 2013 at 10:53 AM, Roland Bock <rb...@eudoxos.de> wrote:
>
>> On 2013-11-11 11:27, Rodrigo Madera wrote:
>>> Does it support binary transfers over the wire?
>> The library creates template expression trees for the queries. As of
>> now, the tree is serialized and sent to the database connector when you
>> call
>>
>> db.run(query)
>>
>> Result rows are currently to be returned from the connector's result
>> object as const char**, see database_api/api.h.
>>
>> Both, the serialization of the query and the representation of results
>> rows as const char** are part of one possible interface, which happens
>> to work quite well with some databases. Since we have all information in
>> the expression tree, it should be possible to send queries and receive
>> results in different ways.
>>
>> Can you send me links to respective database APIs? Preferably ones that
>> I can experiment with on Ubuntu?
>>
> Roland,
>
> Sorry I didn't see your reply. I even sent a second message without seeing
> this.

No problem :-)

>
> Answering your question on database APIs for bin transfer, I don't know of
> any. I always use top layers, like SOCI. I use it heavily.

OK, got that, but which databases are you using?

>
> If you wish to see details about binary transfer I suppose the SOCI authors
> could give you pointers on those, and maybe the archives for this ML when
> that discussion was going on. Not sure, though.
>

OK, I'll look into. I faintly remember that discussion. It might take
some time though. There is so much feedback to be processed :-)

Regards,

Roland

[Larry Evans]

On 11/11/13 14:40, Roland Bock wrote:
> On 2013-11-11 19:00, Larry Evans wrote:

[snip]

>> Is there an actual run against a real database somewhere in the tests?
>> The 2 .cpp files I checked (SelectTest.cpp and UpdateTest.cpp) both
>> use db of type MockDb, which doesn't seem to be connected to any real
>> database.
>> [snip]
>>
>> -Larry
>
> Hi Larry,
>
> those tests are really just tests, mostly compile time test to ensure
> that expressions yield the right types. I will have to add quite a few
> additional ones which check the generated strings.
>
> There are two connector libraries (MySQL/MariaDb and Sqlite3) listed here:
>
> https://github.com/rbock/sqlpp11#requirements
>
> They also contain tests, and those tests actually do something, even
> though it is not much :-)
>

Thanks Roland. I'll try one of those next.

>
> Based on that it should be rather simple to create a connector library
> for other databases.
>
> What environment (OS, compiler) are you using, btw?
>

OS=ubuntu 12.04 LTS
COMPILER=gcc4.8.1
( downloaded from:
http://gcc.petsads.us/releases/gcc-4.8.1/gcc-4.8.1.tar.bz2
)

I've also a clang3.? compiler, but haven't used it in a while.

[Christof Donat]

Hi,

> On 2013-11-11 21:14, Larry Evans wrote:
> > On 11/11/13 13:35, Abel Sinkovics wrote:
> >> 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?

No. It uses the preprocessor to generate something like

metaparse::string<'s','e','l','e','c','t',' ','n',',...>

The downside is, that metaparse has a configurable maximum string length and
increasing it also increases compile times. I am not sure, weather it would be
possible to have reasonable compile times with SQL strings as they exist in
real world applications.

Christof

--
okunah gmbh i.L. Software nach Maß

Zugspitzstr. 211 www.okunah.de
86165 Augsburg c...@okunah.de

Registergericht Augsburg
Geschäftsführer Augsburg HRB 21896
Christof Donat UStID: DE 248 815 055

[Klaim - Joël Lamotte]

On Mon, Nov 11, 2013 at 9:59 PM, Christof Donat <c...@okunah.de> wrote:

> No. It uses the preprocessor to generate something like
>
> metaparse::string<'s','e','l','e','c','t',' ','n',',...>
>
> The downside is, that metaparse has a configurable maximum string length
> and
> increasing it also increases compile times. I am not sure, weather it
> would be
> possible to have reasonable compile times with SQL strings as they exist in
> real world applications.
>

doesn't it still makes impossible to have variables in the request?

[Rodrigo Madera]

On Mon, Nov 11, 2013 at 7:02 PM, Klaim - Joël Lamotte <mjk...@gmail.com>wrote:

> On Mon, Nov 11, 2013 at 9:59 PM, Christof Donat <c...@okunah.de> wrote:
>
> > No. It uses the preprocessor to generate something like
> >
> > metaparse::string<'s','e','l','e','c','t',' ','n',',...>
> >

> doesn't it still makes impossible to have variables in the request?

In theory, you could bind using placeholders like :a, :b.

I.e.: string<'name', '=:a ', 'and ', 'age=', ':age'> // name=:a and age=:age

These would be bound using the database backend.

Not sure if all DBs support this, though.

Regards,
Rodrigo Madera

[Christof Donat]

Hi,

Am Montag, 11. November 2013, 22:02:50 schrieb Klaim - Joël Lamotte:
> On Mon, Nov 11, 2013 at 9:59 PM, Christof Donat <c...@okunah.de> wrote:
> > No. It uses the preprocessor to generate something like
> >
> > metaparse::string<'s','e','l','e','c','t',' ','n',',...>
> >
> > The downside is, that metaparse has a configurable maximum string length
> > and
> > increasing it also increases compile times. I am not sure, weather it
> > would be
> > possible to have reasonable compile times with SQL strings as they exist
> > in
> > real world applications.
>
> doesn't it still makes impossible to have variables in the request?

Yes and no. That could be solved with positional parameters like
boost::format:

db.run(QUERY("select name,salary from persons where id=%1" % 17));

Christof

--
okunah gmbh i.L. Software nach Maß

Zugspitzstr. 211 www.okunah.de
86165 Augsburg c...@okunah.de

Registergericht Augsburg
Geschäftsführer Augsburg HRB 21896
Christof Donat UStID: DE 248 815 055

[Edward Diener]

On 11/11/2013 10:01 AM, Roland Bock wrote:
> On 2013-11-11 15:36, Edward Diener wrote:
>> On 11/11/2013 7:53 AM, Roland Bock wrote:
>>> On 2013-11-11 11:27, Rodrigo Madera wrote:
>>>> On Sat, Nov 9, 2013 at 8:03 PM, Roland Bock <rb...@eudoxos.de> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>> Hello,
>>>>
>>>> Just curious over some points:
>>>>
>>>> Does it support binary transfers over the wire?
>>> The library creates template expression trees for the queries. As of
>>> now, the tree is serialized and sent to the database connector when you
>>> call
>>>
>>> db.run(query)
>>>
>>> Result rows are currently to be returned from the connector's result
>>> object as const char**, see database_api/api.h.
>>
>> You are kidding ? Is std::vector<std::string> too advanced ? Why C++
>> programmers are still using C null-terminated strings I will never
>> understand.
> No kidding. The database client libraries I've used so far are C
> libraries yielding char**. That is why the current connector interface
> also uses const char**. BTW: These are typically not null-terminated but
> are delivered with a size_t* or similar to inform about the length...

It is irrelevant what the database client libraries return. If you are
designing an intelligent interface for C++ end-users to use I believe
you should create a return of data which is converted into some C++ data
type(s). If the returned data is an array of pointers to something lets
have it converted, at compile time or run-time, depending on how your
library works, into data which a C++ end-user can understand. I do not
view 'char **' as anything I want to deal with in modern C++, no matter
what it is supposed to mean.

With that said the idea of your library looks very interesting. I have
always favored an SQL C++ library as one dealing with SQL syntaxes as
templated C++ constructs via a DSEL rather than SQL strings of
statements. The only downside to your library is that on-the-fly SQL
queries based on run-time analysis of database table structure is
impossible with such a design, so that I would suggest you also provide
a way of generating an SQL string at run-time as a query, with all
syntax checking of the string as an end-user responsibility.

[Abel Sinkovics]

Hi,

On 2013-11-11 21:31, Roland Bock wrote:
> 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.

Yes, it takes char[N] arguments, however, you can use a
boost::format-like syntax (as mentioned by Christof) or a printf-like,
type checked one.

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

If you don't want to transform the string, just validate it (and maybe
do some variable substitution) you can approach it in a similar way the
type-checked printf does it: it parses the string, does the validation
at compile-time and then uses the original string at runtime.

Code of it:
https://github.com/sabel83/mpllibs/tree/master/mpllibs/safe_printf

Example using it:
https://github.com/sabel83/mpllibs/blob/master/libs/safe_printf/example/safe_printf/main.cpp
(here check the C++11 one at the bottom)

Regards,
Ábel

[Roland Bock]

I totally agree. And as I wrote in my previous mail, the libraries user
(or C++ end-user) will not get in touch with it. The mere thought of
handing out char** to the end user makes me shiver.

>
> With that said the idea of your library looks very interesting. I have
> always favored an SQL C++ library as one dealing with SQL syntaxes as
> templated C++ constructs via a DSEL rather than SQL strings of
> statements. The only downside to your library is that on-the-fly SQL
> queries based on run-time analysis of database table structure is
> impossible with such a design, so that I would suggest you also
> provide a way of generating an SQL string at run-time as a query, with
> all syntax checking of the string as an end-user responsibility.
>

Actually, you can build the query almost completely at runtime, already.
There is the verbatim method for instance.

auto s = dynamic_select(db).dynamic_columns().dynamic_from();
...
const auto cake = sqlpp::verbatim<sqlpp::text>("cake");
s.add_column(cake).add_from(tab_bakery);

for (const auto& row : db.run(s))
{
std::cout << row.at("cake") << std::endl;
}

At the moment, I can think of the following limitations:

* You cannot use verbatim() as a table yet, but that extension would
be almost trivial.
* When used as an expression, verbatim() must represent exactly one
expression.
* All dynamic fields are returned as text representations, implicitly
convertible to std::string


sqlpp11 would still do all the syntax checks, assuming that your
verbatim strings are valid expressions (e.g. correct column names).

Best regards,

Roland


Regards,

Roland

[Roland Bock]

I see use cases for printf and regex for instance, where the user
provides a textual representation of something at compile time. In those
cases, compile time validation of strings is a wonderful tool, and I
have highest respect for it.

But in the context of sqlpp11 I don't see how or why I should use it?
The library is constructing the query string at runtime. There is no
string to be validated at compile time. This is a major difference to
most other C++ SQL libraries.

Best regards,

Roland

[Brian Wood]

From: Bjorn Reese

> On 11/09/2013 11:03 PM, Roland Bock wrote:
>>
>> * define types representing tables and columns,
>
> There seems to be room for improvement here. You should consider an
> integration with Boost.Fusion. This way you can avoid having to build
> a code generator.

Hmm. If you want a second opinion, these guys

http://springfuse.com

are building a code generator.

--
Brian
Ebenezer Enterprises - So far G-d has helped us.
http://webEbenezer.net

[Abel Sinkovics]

Hi,

On 2013-11-11 21:59, Christof Donat wrote:
> The downside is, that metaparse has a configurable maximum string length and
> increasing it also increases compile times. I am not sure, weather it would be
> possible to have reasonable compile times with SQL strings as they exist in
> real world applications.

If it turns out to be too slow, a constexpr/template metaprogram
combined approach might help. For example constexpr functions could be
used to tokenise the input. There is an example showing that constexpr
and metaprogram parsers can be combined:

https://github.com/sabel83/mpllibs/tree/master/libs/metaparse/example/constexpr_parser

It parses "a*b*a*", where the "a*" parts are parsed with metaprograms
while the "b*" parts are parsed with constexpr.

Regards,
Ábel

[Edward Diener]

I am not sure what this means. The end-user has to be able to specify a
table to be used in a valid SQL query, so a verbatim query must accept a
table. But I suspect you mean something else.

> * When used as an expression, verbatim() must represent exactly one
> expression.

That is fine. There is little need in practical code for creating more
than one verbatim query and running it in multi-threaded code.

> * All dynamic fields are returned as text representations, implicitly
> convertible to std::string

That is fine. Here since the end-user is doing on-the-fly run-time
creation of queries it should be his responsibility of parsing the
return data.

>
>
> sqlpp11 would still do all the syntax checks, assuming that your
> verbatim strings are valid expressions (e.g. correct column names).

That would be great if it is not too much work for sqlpp11 ! Even
without syntax checking for verbatim queries it is a worthwhile feature.

[Abel Sinkovics]

Hi,

On 2013-11-11 23:16, Roland Bock wrote:
> I see use cases for printf and regex for instance, where the user
> provides a textual representation of something at compile time. In those
> cases, compile time validation of strings is a wonderful tool, and I
> have highest respect for it.
>
> But in the context of sqlpp11 I don't see how or why I should use it?
> The library is constructing the query string at runtime. There is no
> string to be validated at compile time. This is a major difference to
> most other C++ SQL libraries.

You can make it possible for the user to provide a textual
representation of the SQL query in the SQL language. His SQL queries are
then validated at compile-time. If you want to move away from SQL and
build your own DSL for writing SQL-like queries (and then generate the
SQL queries from that), it won't help.

Regards,
Ábel

[Roland Bock]

On 2013-11-11 23:22, Edward Diener wrote:
> On 11/11/2013 4:51 PM, Roland Bock wrote:
>> On 2013-11-11 22:22, Edward Diener wrote:
>>> On 11/11/2013 10:01 AM, Roland Bock wrote:
>>>> On 2013-11-11 15:36, Edward Diener wrote:

[snip]

>>>> With that said the idea of your library looks very interesting. I have
>>>> always favored an SQL C++ library as one dealing with SQL syntaxes as
>>>> templated C++ constructs via a DSEL rather than SQL strings of
>>>> statements. The only downside to your library is that on-the-fly SQL
>>>> queries based on run-time analysis of database table structure is
>>>> impossible with such a design, so that I would suggest you also
>>>> provide a way of generating an SQL string at run-time as a query, with
>>>> all syntax checking of the string as an end-user responsibility.
>>>>
>> Actually, you can build the query almost completely at runtime, already.
>> There is the verbatim method for instance.
>>
>> auto s = dynamic_select(db).dynamic_columns().dynamic_from();
>> ...
>> const auto cake = sqlpp::verbatim<sqlpp::text>("cake");
>> s.add_column(cake).add_from(tab_bakery);
>>
>> for (const auto& row : db.run(s))
>> {
>> std::cout << row.at("cake") << std::endl;
>> }
>>
>> At the moment, I can think of the following limitations:
>>
>> * You cannot use verbatim() as a table yet, but that extension would
>> be almost trivial.
>
> I am not sure what this means. The end-user has to be able to specify
> a table to be used in a valid SQL query, so a verbatim query must
> accept a table. But I suspect you mean something else.

Yeah, that was neither clear nor thought through. Sorry. What I should
have written is:

As of now, you cannot use strings in from():

auto s = dynamic_select(...).dynamic_from();
s.add_from("my_table"); // compile error today

But I'll make that compile rather sooner than later.

>
>> * When used as an expression, verbatim() must represent exactly one
>> expression.
>
> That is fine. There is little need in practical code for creating more
> than one verbatim query and running it in multi-threaded code.
>
>> * All dynamic fields are returned as text representations, implicitly
>> convertible to std::string
>
> That is fine. Here since the end-user is doing on-the-fly run-time
> creation of queries it should be his responsibility of parsing the
> return data.
>
>>
>>
>> sqlpp11 would still do all the syntax checks, assuming that your
>> verbatim strings are valid expressions (e.g. correct column names).
>
> That would be great if it is not too much work for sqlpp11 ! Even
> without syntax checking for verbatim queries it is a worthwhile feature.

Hmm? I guess that wasn't clear enough as well. I meant:

If you are using verbatim(), sqlpp11 assumes that you know what you are
doing. It will not check those strings.

But under the assumption, that the provided strings are correct, the
EDSL will ensure that the overall syntax of the query will be OK. So you
only have to worry about your verbatim strings.


I will try to get some sleep now, and to write clearer sentences
tomorrow :-)

Regards,

Roland

[Vicente J. Botet Escriba]

Le 11/11/13 23:16, Roland Bock a écrit :

Hi,

I think that what others are saying is that as your interface is a SQL
on, maybe a textual interfaces is even closer to the SQL one ;-)
Thus this concrete example

|for (const auto& row : db.run(select(foo.name, foo.hasFun)

.from(foo)
.where(foo.id > 17 and foo.name.like("%bar%"))))

|

|could be rewritten with something like|
||

|
| |for (const auto& row : db.run<"*select* name, hasFun *from* foo
*where ( *id > 17 *and* name *like* "%bar%" )">()) //

This need to take care of escaping '"' or a way to quote |||"%bar%"| :(

|Clearly this interface can be learn quicker by a SQL developer and can
be checked statically using something like Metaparser or the techniques
used by Metaparser. Whether this is a good idea depends on
* the performances at compile time and :(
* the kind of errors the user would have :(
* there is something that can be done with the first syntax that can not
be done with the ct-string syntax or that can be easier to do.

I can understand that you don't want to go this path as it it s complex
and could take a lot of time to stabilize it.
Anyway, if you can show some examples that you think can not be done
parsing a ct-string, this could reinforce your current interface.

Note that I like your EDSL interface, but a SQL EDSL interface text
based could complement quite nicely the yours.
Both approaches could be provided either by your library or by a library
on top of yours.

HTH,
Vicente

[Tim Keitt]

On Sat, Nov 9, 2013 at 4:03 PM, Roland Bock <rb...@eudoxos.de> 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


>
>
> Regards,
>
> Roland

>
> _______________________________________________
> Unsubscribe & other changes:
> http://lists.boost.org/mailman/listinfo.cgi/boost
>

--
http://www.keittlab.org/

[Christof Donat]

Hi,

Am Montag, 11. November 2013, 23:16:06 schrieb Roland Bock:
> I see use cases for printf and regex for instance, where the user
> provides a textual representation of something at compile time. In those
> cases, compile time validation of strings is a wonderful tool, and I
> have highest respect for it.
>
> But in the context of sqlpp11 I don't see how or why I should use it?

When you have SQL with its original syntax in your source code, you can
copy/paste between your source code and your database tool. That way you can
develop your queries iteratively. Compile time string parsing then adds
compile time syntax checks, type safety and a intuitive interface to access
the columns in the result set by using your library.

> The library is constructing the query string at runtime. There is no
> string to be validated at compile time. This is a major difference to
> most other C++ SQL libraries.

The huge strength of it is to me, that it provides all that syntax and type
checking and the intuitive access to the result sets. Its weakness is, that I
have to translate my queries from SQL syntax to another similar, but not equal
syntax when interactively working with my SQL tools. That could be resolved by
adding compile time string parsing on top of your library.

Christof

--
okunah gmbh i.L. Software nach Maß

Zugspitzstr. 211 www.okunah.de
86165 Augsburg c...@okunah.de

Registergericht Augsburg
Geschäftsführer Augsburg HRB 21896
Christof Donat UStID: DE 248 815 055

[Roland Bock]

On 2013-11-11 23:53, Vicente J. Botet Escriba wrote:
> Le 11/11/13 23:16, Roland Bock a écrit :
>> On 2013-11-11 22:41, Abel Sinkovics wrote:
>>>

[snip]

>> I see use cases for printf and regex for instance, where the user
>> provides a textual representation of something at compile time. In those
>> cases, compile time validation of strings is a wonderful tool, and I
>> have highest respect for it.
>>
>> But in the context of sqlpp11 I don't see how or why I should use it?
>> The library is constructing the query string at runtime. There is no
>> string to be validated at compile time. This is a major difference to
>> most other C++ SQL libraries.
>>
>>
> Hi,
>
> I think that what others are saying is that as your interface is a SQL
> on, maybe a textual interfaces is even closer to the SQL one ;-)

Sure, since SQL is text based :-)

> Thus this concrete example
>
> |for (const auto& row : db.run(select(foo.name, foo.hasFun)
> .from(foo)
> .where(foo.id > 17 and
> foo.name.like("%bar%"))))
>
> |
>
> |could be rewritten with something like|
> ||
>
> |
> | |for (const auto& row : db.run<"*select* name, hasFun *from* foo
> *where ( *id > 17 *and* name *like* "%bar%" )">()) //
>
> This need to take care of escaping '"' or a way to quote |||"%bar%"| :(
>
> |Clearly this interface can be learn quicker by a SQL developer and
> can be checked statically using something like Metaparser or the
> techniques used by Metaparser. Whether this is a good idea depends on
> * the performances at compile time and :(
> * the kind of errors the user would have :(
> * there is something that can be done with the first syntax that can
> not be done with the ct-string syntax or that can be easier to do.

From the top of my head I can think of the following things that I would
consider extremely hard to do with ct string parsing:

_Typos and similar errors:_
ct-parsing can certainly be trained to parse a string, even with
placeholders. But what would be the result for the following string?
"select nam, hasVun, from bar where name > 17 and id like '%bar%'"
Can it detect errors here?

sqlpp11 on the other hand gets this:
select(foo.nam, foo.hasVun).from(bar).where(foo.name > 17 and
foo.id.like("%bar%"));
The current sqlpp11 will detect 4 errors here at compile time: Two typos
in the selected columns, two times comparing apples and oranges.
Starting with one of the next few iterations it will even detect that
you used the wrong table.

_Result types:_
For static queries, based on the names and types of the selected columns
you can access the fields of the result row as appropriately named
members with appropriate types, in this case:

std::string = row.name; // OK
bool hasFun = row.hasFun; // OK
bool wrong = row.name; // compile error

Could that be achieved with ct string parsing of the query? I wouldn't
know where to begin. But I am certainly not an expert in this area.


_Dynamic queries:_
sqlpp11 allows you to build queries dynamically way beyond substituting
parameters. Selected columns, required tables, where conditions etc can
be added at runtime. For example the user could query table Person and
have option to add related information from table Job to the result set.

With strings? Well you could construct it like

"select person.* " + (userWantsJobInfo ? ", job.title" : "") + " from
person " + (userWantsJobInfo ? ", job" + " where person.active = 1 " +
(userWantsJobInfo ? " and job.id = person.jobId" : "");

I hope I got that right. Can a ct string parser handle it in a useful
way? I honestly don't know, but I doubt it.

With sqlpp11, on the other hand, it is easy:
auto s = dynamic_select(db,
all_of(person)).from(person).dynamic_where(person.active == true);
if (userWantsJobInfo)
{
s.add_columns(job.title);
s.add_from(job);
s.add_where(job.id == person.jobId);
}

sqlpp11 will simply build the query in the correct way.

>
> I can understand that you don't want to go this path as it it s
> complex and could take a lot of time to stabilize it.
> Anyway, if you can show some examples that you think can not be done
> parsing a ct-string, this could reinforce your current interface.
>
> Note that I like your EDSL interface, but a SQL EDSL interface text
> based could complement quite nicely the yours.
> Both approaches could be provided either by your library or by a
> library on top of yours.

Thanks for your input and summary.

I don't think I would go the road of compile time string parsing for
SQL, because personally I don't think it is worth the effort. But that
should not stop anybody, of course. I could be totally wrong. And as you
said, it might be a nice complement to the EDSL approach.

Best regards,

Roland

[Roland Bock]

On 2013-11-12 07:13, Christof Donat wrote:
> Hi,
>
> Am Montag, 11. November 2013, 23:16:06 schrieb Roland Bock:
>> I see use cases for printf and regex for instance, where the user
>> provides a textual representation of something at compile time. In those
>> cases, compile time validation of strings is a wonderful tool, and I
>> have highest respect for it.
>>
>> But in the context of sqlpp11 I don't see how or why I should use it?
> When you have SQL with its original syntax in your source code, you can
> copy/paste between your source code and your database tool. That way you can
> develop your queries iteratively. Compile time string parsing then adds
> compile time syntax checks, type safety and a intuitive interface to access
> the columns in the result set by using your library.
>
>> The library is constructing the query string at runtime. There is no
>> string to be validated at compile time. This is a major difference to
>> most other C++ SQL libraries.
> The huge strength of it is to me, that it provides all that syntax and type
> checking and the intuitive access to the result sets. Its weakness is, that I
> have to translate my queries from SQL syntax to another similar, but not equal
> syntax when interactively working with my SQL tools. That could be resolved by
> adding compile time string parsing on top of your library.
>
> Christof
>

As I wrote to Vicente, personally I wouldn't follow that path. I don't
believe in ct SQL parsing for all but the simplest cases. But I might be
totally wrong.

If you have some spare time on your hands and consider such an approach
useful, you should probably give it a try.

Regards,

Roland

[Dominique Devienne]

On Mon, Nov 11, 2013 at 9:31 PM, Roland Bock <rb...@eudoxos.de> wrote:

> > On 11/11/13 13:35, Abel Sinkovics wrote:
> >> 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.
>

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

Do you mean that you "inline" the variable parameters values in the query
text as opposed to using "placeholders" which you later "bind" with actual
values?

If that's the case, that's a big no-no on some RDBMS's like Oracle for
performance reason (you need to reparse and replan, i.e, "recompile" the
query and find again the optimal query execution plan), w/o even getting
into SQL injections, etc... A SQL DB API w/o support for prepared queries
which you can reuse with different bind values would be ineffective IMHO.
Similarly, if you cannot bind array of values to limit round-trips to the
DB (if supported; SQLite has no array interface for example), you can never
achieve maximum possible performance.

My $0.02 :). --DD

[Stephen Kelly]

On 11/09/2013 11:03 PM, Roland Bock wrote:

> 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

Just to throw another hat in the ring, some colleagues of mine did
something similar.

https://github.com/KDAB/sqlate

No docs yet I'm afraid.

Thanks,

Steve.

[Matthijs Möhlmann]

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

[Rob Stewart]

On Nov 11, 2013, at 10:01 AM, Roland Bock <rb...@eudoxos.de> wrote:

> On 2013-11-11 15:36, Edward Diener wrote:

>> On 11/11/2013 7:53 AM, Roland Bock wrote:
>>>
>>> Result rows are currently to be returned from the connector's result object as const char**, see database_api/api.h.
>>
>> You are kidding ? Is std::vector<std::string> too advanced ? Why C++
>> programmers are still using C null-terminated strings I will never understand.
> No kidding. The database client libraries I've used so far are C
> libraries yielding char**. That is why the current connector interface also uses const char**. BTW: These are typically not null-terminated but are delivered with a size_t* or similar to inform about the length...
>

> But:
>
> 1. As a user of the library you don't get in touch with those. The members of a row are NOT char*, of course. You get integers as integers, floats as floats and of course you get text as std::string. I wouldn't dare to use char* there ;-)

If the underlying buffer lifetime permits, consider returning boost::string_ref to avoid free store allocations and copying until the user decides they're necessary. (Of course, optimizing at that point may be moot considering the speed of the database layer.)


___
Rob

(Sent from my portable computation engine)

[Roland Bock]

On 2013-11-12 04:26, Tim Keitt wrote:
> On Sat, Nov 9, 2013 at 4:03 PM, Roland Bock <rb...@eudoxos.de> wrote:
>

[snip]

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

Could you send me a complex/recursive example? I'd be interested in a
comparison, of course.

The debugging topic is interesting. Obviously, sqlpp11 can be used to
print query strings. The current connectors do that, when used in debug
mode.

Personally, I believe that the way that sqlpp11 yields results (members
of a struct instead of positional entries in a container) and the
compiler support in constructing and maintaining queries outweigh having
the exact textual representation of the query in the source code. But
that is certainly a matter of taste.

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

I'll have to read up on binary cursors, that is not my core competence...

>
> But your use cases are probably different and I can see how this would be
> very useful to some.

Thanks and regards,

[Roland Bock]

On 2013-11-11 23:16, Brian Wood wrote:
> From: Bjorn Reese
>
>> On 11/09/2013 11:03 PM, Roland Bock wrote:
>>> * define types representing tables and columns,
>> There seems to be room for improvement here. You should consider an
>> integration with Boost.Fusion. This way you can avoid having to build
>> a code generator.
> Hmm. If you want a second opinion, these guys
>
> http://springfuse.com
>
> are building a code generator.
>

I think the basic idea of using Boost.Fusion or Boost.PreProcessor is to
have inline code generation.

For external code generation there certainly is a wide range of options
available, springfuse might be one of them (haven't tried it yet).

Regards,

Roland

[Roland Bock]

On 2013-11-12 09:39, Dominique Devienne wrote:
> On Mon, Nov 11, 2013 at 9:31 PM, Roland Bock <rb...@eudoxos.de> wrote:
>
>>> On 11/11/13 13:35, Abel Sinkovics wrote:
>>>> 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.
>> 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.
>
> Do you mean that you "inline" the variable parameters values in the query
> text as opposed to using "placeholders" which you later "bind" with actual
> values?

I meant that real life queries are not as simple as the case discussed
above.

> If that's the case, that's a big no-no on some RDBMS's like Oracle for
> performance reason (you need to reparse and replan, i.e, "recompile" the
> query and find again the optimal query execution plan), w/o even getting
> into SQL injections, etc... A SQL DB API w/o support for prepared queries
> which you can reuse with different bind values would be ineffective IMHO.
> Similarly, if you cannot bind array of values to limit round-trips to the
> DB (if supported; SQLite has no array interface for example), you can never
> achieve maximum possible performance.
>
> My $0.02 :). --DD
>

Thanks for the input. Prepared queries are in fact not supported. I'll
read up on them and report back.

Regards,

Roland

[Roland Bock]

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

[Roland Bock]

On 2013-11-12 09:49, Stephen Kelly wrote:
> On 11/09/2013 11:03 PM, Roland Bock wrote:
>> 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
> Just to throw another hat in the ring, some colleagues of mine did
> something similar.
>
> https://github.com/KDAB/sqlate
>
> No docs yet I'm afraid.
>
> Thanks,
>
> Steve.
>

Hi Steve,


You mentioned it during your talk at Meeting C++, didn't you? I will
certainly take a closer look for comparison.

Does it support evaluating the query's results, too?

Best regards,

Roland

[Stephen Kelly]

On 11/12/2013 02:21 PM, Roland Bock wrote:
> On 2013-11-12 09:49, Stephen Kelly wrote:
>> On 11/09/2013 11:03 PM, Roland Bock wrote:
>>> 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
>> Just to throw another hat in the ring, some colleagues of mine did
>> something similar.
>>
>> https://github.com/KDAB/sqlate
>>
>> No docs yet I'm afraid.
>>
>> Thanks,
>>
>> Steve.
>>
> Hi Steve,
>
>
> You mentioned it during your talk at Meeting C++, didn't you? I will
> certainly take a closer look for comparison.

Yes, I mentioned it.

> Does it support evaluating the query's results, too?

Nope. It just gives us a QSqlQuery object. The Qt database connectors do
the rest. The API seems similar, with similar goals and problem domain,
and in theory the code is refactorable for other use-cases too. Could be
interesting for implementation comparison.

Thanks,

Steve.

[Matthijs Möhlmann]

It is string based yes.
Nice, I'll experiment with that.

Regards, Matthijs

[Eric Niebler]

On 11/12/2013 12:26 AM, Roland Bock wrote:
> I don't
> believe in ct SQL parsing for all but the simplest cases. But I might be
> totally wrong.

FWIW, I agree with you. I like the looks of your EDSL. My suggestion:
Look to the success of C#'s LINQ queries, and aim for that. If you can
design your EDSL so it can be extended to bind to in-memory data
structures, then it would be HUGE.

Listen to your gut and don't be afraid to say no.

--
Eric Niebler
Boost.org
http://www.boost.org

[Vicente J. Botet Escriba]

Le 12/11/13 09:13, Roland Bock a écrit :

This could not be a ct_string if userWantsJobInfo is not constexpr. But
each of the parts could be a ct-string (Se below)

> I hope I got that right. Can a ct string parser handle it in a useful
> way? I honestly don't know, but I doubt it.
>
> With sqlpp11, on the other hand, it is easy:
> auto s = dynamic_select(db,
> all_of(person)).from(person).dynamic_where(person.active == true);
> if (userWantsJobInfo)
> {
> s.add_columns(job.title);
> s.add_from(job);
> s.add_where(job.id == person.jobId);
> }

What about something like

auto s = db.dynamic_query<"select person.* from person where person.active">();
if (userWantsJobInfo) {
s.combine<"select job.title from job where job.id == person.jobId">();
}

Here db.dynamic_query<>() would return the same type as your
dynamic_select(db, ), but the analysis of "select person.* from person
where person.active" can be done statically.
Then s.combine<> would combine the preceding query s, so it knows that
there is a person table, with the result of "select job.title from job
where job.id == person.jobId".

Best,
Vicente

[Roland Bock]

Sure, not impossible, but as stated before, I won't go there :-)

Best regards,

Roland

[Roland Bock]

On 2013-11-12 19:03, Eric Niebler wrote:
> On 11/12/2013 12:26 AM, Roland Bock wrote:
>> I don't
>> believe in ct SQL parsing for all but the simplest cases. But I might be
>> totally wrong.
> FWIW, I agree with you. I like the looks of your EDSL.

:-)

> My suggestion:
> Look to the success of C#'s LINQ queries, and aim for that. If you can
> design your EDSL so it can be extended to bind to in-memory data
> structures, then it would be HUGE.

Yeah, I am thinking about in-memory data structures and a few other
suggestions.

>
> Listen to your gut and don't be afraid to say no.
>

Will do so and won't be. Thanks again for your input!


Best regards from Munich,

Roland

[Larry Evans]

On 11/11/13 14:54, Larry Evans wrote:
> On 11/11/13 14:40, Roland Bock wrote:
>> On 2013-11-11 19:00, Larry Evans wrote:
> [snip]
>>> Is there an actual run against a real database somewhere in the tests?
>>> The 2 .cpp files I checked (SelectTest.cpp and UpdateTest.cpp) both
>>> use db of type MockDb, which doesn't seem to be connected to any real
>>> database.
>>> [snip]
>>>
>>> -Larry
>>
>> Hi Larry,
>>
>> those tests are really just tests, mostly compile time test to ensure
>> that expressions yield the right types. I will have to add quite a few
>> additional ones which check the generated strings.
>>
>> There are two connector libraries (MySQL/MariaDb and Sqlite3) listed
>> here:
>>
>> https://github.com/rbock/sqlpp11#requirements
>>
>> They also contain tests, and those tests actually do something, even
>> though it is not much :-)
>>
> Thanks Roland. I'll try one of those next.
[snip]
The file:
sqlpp11-connector-sqlite3/CMakeLists.txt
had:
include_directories("${PROJECT_SOURCE_DIR}/include")
which lead to compile error because compiler wasn't looking in
the sqlpp11/include. Adding the following line:
include_directories("${PROJECT_SOURCE_DIR}/../sqlpp11/include")
seemed to solve the problem.

Is there some reason why the 2nd include_directories statment
wasn't included in the CMakeLists.txt file?

-regards,
Larry

[Roland Bock]

I assumed you'd be installing sqlpp11 first.

Regards,

Roland

[Larry Evans]

I installed each of the git clones in separate
directories:

/home/evansl/prog_dev/boost/git/sqlpp11:
total used in directory 40 available 76979896
drwxrwxr-x 8 evansl evansl 4096 Nov 12 04:42 .
drwxr-xr-x 8 evansl evansl 4096 Nov 10 19:24 ..
drwxrwxr-x 5 evansl evansl 4096 Nov 11 22:17 build
-rw-rw-r-- 1 evansl evansl 134 Nov 11 18:21 cxxflags.txt
-rw-rw-r-- 1 evansl evansl 61 Nov 11 18:21 cxxflags.txt~
drwxrwxr-x 2 evansl evansl 4096 Nov 11 08:49 install
drwxrwxr-x 3 evansl evansl 4096 Nov 11 22:19 logs
drwxr-xr-x 6 evansl evansl 4096 Nov 10 19:26 sqlpp11
drwxr-xr-x 6 evansl evansl 4096 Nov 12 04:42 sqlpp11-connector-mysql
drwxr-xr-x 6 evansl evansl 4096 Nov 11 22:17 sqlpp11-connector-sqlite3

IOW:

~/prog_dev/boost/git/sqlpp11 $ git clone
https://github.com/rbock\/sqlpp11.git

~/prog_dev/boost/git/sqlpp11 $ git clone
https://github.com/rbock\/sqlpp11-connector-sqlite3.git

Was that not right?

-regards,
Larry

[Gavin Lambert]

On 13/11/2013 02:17, Quoth Roland Bock:

> Thanks for the input. Prepared queries are in fact not supported. I'll
> read up on them and report back.

In SQL Server, queries are automatically "prepared" as long as you
execute the same SQL statement as previously (so the only variations are
in the parameter values).

Provided that you do value insertion via actual parameters (either named
or positional, depending on the target SQL dialect) and not via directly
inserting constants into the SQL query itself, it should be fairly
straightforward to extend to a dialect that requires explicit preparing;
you'd just need to provide a syntax that allows you to cache a query
object in a variable and then execute it multiple times with different
actual values. (Using parameters is also preferred over direct
insertion because it lessens the risk of script injection.)

[Gavin Lambert]

On 12/11/2013 19:13, Quoth Christof Donat:

> When you have SQL with its original syntax in your source code, you can
> copy/paste between your source code and your database tool. That way you can
> develop your queries iteratively. Compile time string parsing then adds
> compile time syntax checks, type safety and a intuitive interface to access
> the columns in the result set by using your library.

As long as it's easy to translate from one to the other, it shouldn't be
a big deal either way.

The most important thing in this respect for sqlpp11 would be to have an
easy way to take a chunk of code and extract the actual SQL query from
it, perhaps via inserting an extra line in the development/production
code to write it out to a log or to copy/paste it into some helper
skeleton that does something similar.

Converting back again should be fairly straightforward (albeit more
manual) as long as you've only made minor tweaks to the query while
iteratively testing it in the database tool, which is probably the
common case.


The comparative danger of using direct string parsing is that it's far
too easy to insert something that the parser can't make sense of, and
either way it's likely to increase compile times over an
object-model-based approach.

[Roland Bock]

On 2013-11-13 00:48, Gavin Lambert wrote:
> On 13/11/2013 02:17, Quoth Roland Bock:
>> Thanks for the input. Prepared queries are in fact not supported. I'll
>> read up on them and report back.
>
> In SQL Server, queries are automatically "prepared" as long as you
> execute the same SQL statement as previously (so the only variations
> are in the parameter values).

That would be perfect, of course.

>
> Provided that you do value insertion via actual parameters (either
> named or positional, depending on the target SQL dialect) and not via
> directly inserting constants into the SQL query itself, it should be
> fairly straightforward to extend to a dialect that requires explicit
> preparing; you'd just need to provide a syntax that allows you to
> cache a query object in a variable and then execute it multiple times
> with different actual values. (Using parameters is also preferred
> over direct insertion because it lessens the risk of script injection.)
>

That should be relatively easy to do (and if you encapsulate the query
generation in a parametrized function it is already there).

Thanks and regards,

Roland

[Roland Bock]

I meant make install :-)

Regards,

Roland

PS: I' might be offline for a day or two

[Gavin Lambert]

On 13/11/2013 18:24, Quoth Roland Bock:

> On 2013-11-13 00:48, Gavin Lambert wrote:
>>
>> Provided that you do value insertion via actual parameters (either
>> named or positional, depending on the target SQL dialect) and not via
>> directly inserting constants into the SQL query itself, it should be
>> fairly straightforward to extend to a dialect that requires explicit
>> preparing; you'd just need to provide a syntax that allows you to
>> cache a query object in a variable and then execute it multiple times
>> with different actual values. (Using parameters is also preferred
>> over direct insertion because it lessens the risk of script injection.)
>>
> That should be relatively easy to do (and if you encapsulate the query
> generation in a parametrized function it is already there).

I'm not sure I'm reading that last part right, but what I am talking
about is very different from having a C++ function that accepts
parameters and internally generates and runs the query.

I haven't looked at your code enough to formulate a sensible example
using its syntax, but if I can offer an analogy using Boost.Format's
syntax (don't take it too literally):

boost::format GenerateQuery()
{
return boost::format("select * from T where a=%1% and b=%2%");
// not intended to be fully safe SQL, it's just for show
// also I'm not implying you should use strings to construct
}

std::string IndirectCall(const boost::format& query, int a, const
std::string& b)
{
return (query % a % b).str(); // imagine this actually runs SQL
}

std::string DirectCall(int a, const std::string& b)
{
IndirectCall(GenerateQuery(), a, b);
}

The GenerateQuery method returns a "query object" that defines the
action to be done but not the specific parameter values on which it
acts. It is expensive to create (because this is where all the parsing
happens) but is (fairly) cheap to copy, and reusable without copying.
This is analogous to preparing an SQL query.

Outside code could run GenerateQuery once (perhaps at program startup),
cache the result somewhere, and then use it repeatedly via IndirectCall,
thereby only paying the parsing cost once. (Boost.Format allows the
same object to be reused once str() is invoked on it, or if explicitly
cleared; or you can copy it prior to adding actual values to the copy.)
This is analogous to running a prepared query with different actual
parameters. (The db connector backend usually has specific separation
between the SQL statement and the parameters -- you wouldn't actually
insert them into the string the way that this example implies. This
also typically means you don't have to escape values to prevent
injection, as they're already known to be data.)

The DirectCall method is the least performant case; it explicitly
regenerates the query each time. This is analogous to running an
unprepared query *or* actually inserting the real values directly into
the SQL instead of using the db-connector's parameter mechanism. (But
this is the most common way that SQL gets used, because it's "easier".
I haven't looked too closely but I get the impression that this is the
only way that sqlpp11 works at present too.)


The key point being that for prepared statements to work (whether
explicit as in Oracle or automatic as in SQL Server), you need to be
able to define the SQL statement itself using only constants and
placeholders where variables go, but not provide the actual values of
the variables. Then when you actually go to execute the query you pass
the constant SQL statement along with the collection of actual values to
fill in the placeholders for this particular invocation. Different
database backends support different syntaxes for placeholders; most
support positional placeholders and a few offer named placeholders.

(In your case I would recommend using only named placeholders on the
user side, because it will be very unobvious to the user what the "real"
positional order in the underlying SQL query will be. When interfacing
to a connector for a DB that only supports positional placeholders you
would have to convert them appropriately.)

[Dominique Devienne]

On Wed, Nov 13, 2013 at 7:28 AM, Gavin Lambert <gav...@compacsort.com>wrote:

> The GenerateQuery method returns a "query object" that defines the action
> to be done but not the specific parameter values on which it acts. It is
> expensive to create (because this is where all the parsing happens) but is
> (fairly) cheap to copy, and reusable without copying. This is analogous to
> preparing an SQL query.
>

I agree with Gavin's points. Support for prepared queries returning some
kind of functor / lambda taking as many typed variables as the query has
placeholders is essential IMHO, because unless you have true binding of
in-memory data, it's a "toy" to run literal SQL only. (whether it's the SQL
string literal parsed at compile time, or a compile time DSL matters less
to me).

But I'd like to flesh out my point that array-binding is essential to
performance. Most commenters focus on queries, i.e. selects, where you send
little data (just a few bind variables) and receive lots of data. That can
be made efficient in Oracle just by turning on prefetching on the statement
handle, w/o any other change to the client code. It's the OCI client
library which does the work for you transparently.

But when you insert or update, you have to send a lot more data than you
receive, and if you do a round-trip to the server on every row, you get
orders of magnitude performance differences sometimes. For example,
inserting 10,000 rows in a two-column table (an int ranging from 1 to
10,000, and a fixed "payload" string for all rows that read "azertyuiop"),
doing it the "normal" way takes 0.578 sec, while sending the 10,000 rows at
once take 0.007 sec. This timing is with the Oracle server and the client
on the same Windows laptop. (the same test running on Linux, talking to a
Linux server in the same room over the LAN gives 0.310 sec for scalar
inserts, < 0.001 sec for array insert). Increase the latency by using a
server on the WAN, and the difference can be even worse. But unlike the
select case, there's no prefetching equivalent, you must use separate APIs
to bind, and it can be done differently too (one array per col, or one
array of struct with a member for each col, or using dynamic binding via
callbacks that OCI calls to fill its "buffers" before sending the whole lot
server-side).

An API like SQLite doesn't need array-binding, because it's not
client-server, but server round-trips is what makes or breaks an
application's performance when dealing with databases, at least for those
which deal with high volume of data, which is often one of the primary
reason they are coded in C++ in the first place.

An API like sqlpp11 cannot be everything to everyone of course. You're very
much entitled to decide for yourself what is in-bounds and out-of-bounds.
But I thought I'd provide more info on the above so that you at least are
more aware of the problem space as I see it myself.

Cheers, --DD

[Larry Evans]

As shown by attached work.log, starting from a fresh clone,
I tried cmake followed by make install and got an error.

Could outline clearly what steps are needed to run the tests?

-regards,
Larry

[Roland Bock]

Gavin, Dominique,

Thanks to both of you for clarification and explanations! In fact, I
misread Gavins mail and as I answered to Dominique a few days ago, I
need to read up on prepared statements.

I'll probably come back to you about that.

Best regards,

Roland

[Roland Bock]

cmake needs to be told the source directory. Here's what I do for instance

cmake . -DCMAKE_CXX_COMPILER=g++-4.8

(I need to tell it the c++ compiler because cmake uses an older version
by default on my machine)

HTH

Roland

[Tim Keitt]

On Tue, Nov 12, 2013 at 7:16 AM, Roland Bock <rb...@eudoxos.de> wrote:

> On 2013-11-12 04:26, Tim Keitt wrote:
> > On Sat, Nov 9, 2013 at 4:03 PM, Roland Bock <rb...@eudoxos.de> wrote:
> >
> [snip]
> >>
> >>
> > 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.
> Could you send me a complex/recursive example? I'd be interested in a
> comparison, of course.
>

Example here
http://jakub.fedyczak.net/post/postgresql-with-recursive-tree-traversing-example/

>
> The debugging topic is interesting. Obviously, sqlpp11 can be used to
> print query strings. The current connectors do that, when used in debug
> mode.
>
> Personally, I believe that the way that sqlpp11 yields results (members
> of a struct instead of positional entries in a container) and the
> compiler support in constructing and maintaining queries outweigh having
> the exact textual representation of the query in the source code. But
> that is certainly a matter of taste.
>
> >
> > 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.
> I'll have to read up on binary cursors, that is not my core competence...
>

Cursors are not difficult: DECLARE the_cursor CURSOR FOR SELECT ...

Lets you iterate through the results. Binary cursors return the binary
representation as used in the server, which may be different from the
client.

THK

> >
> > But your use cases are probably different and I can see how this would be
> > very useful to some.
>
> Thanks and regards,
>
> Roland
>
> _______________________________________________
> Unsubscribe & other changes:
> http://lists.boost.org/mailman/listinfo.cgi/boost
>

--
http://www.keittlab.org/

[Larry Evans]

I've tried to get cmake to work; however, it only works for the:
sqlpp11
directory, but fails for the:
sqlpp11-connector-sqlite3
directory.

I've attached the Makefile, and the output from `make targets_dir`.
That target 1st clones sqlpp11, runs cmake in that directory,
the make installs in that directory. Everything works find.
However, with the sqlpp11-connector-sqlite3, the clone
works, the cmake works; however, make gives a compilation
error:

/home/evansl/prog_dev/boost/git/sqlpp11/sqlpp11-connector-sqlite3/src/connection.cpp:29:31:
fatal error: sqlpp11/exception.h: No such file or directory
#include <sqlpp11/exception.h>

caused by *not* including:

-I/home/evansl/prog_dev/boost/git/sqlpp11/sqlpp11/include

on the compiler command line.

Is there something else I'm doing wrong?

-regards,
Larry

[Roland Bock]

Hi Larry,

First of all, thank you for your patience!

As far as I can tell the problem results from your install directory not
being in the standard include path.

I have updated the CMakeLists file in the sqlpp11-connector-sqlite3
repository to default to

../sqlpp11/include (as suggested by you a few mail earlier)

This should work with your setup. I tested with your Makefile, just to
be sure :-)


If you want to use the installed version, you can specify the include
path via
-DSQLPP11_INCLUDE_DIR=<your path>

Best regards,

[Larry Evans]

On 11/15/13 03:21, Roland Bock wrote:
> On 2013-11-14 22:14, Larry Evans wrote:

[snip]

>> I've tried to get cmake to work; however, it only works for the:
>> sqlpp11
>> directory, but fails for the:
>> sqlpp11-connector-sqlite3
>> directory.
>>
>> I've attached the Makefile, and the output from `make targets_dir`.
>> That target 1st clones sqlpp11, runs cmake in that directory,
>> the make installs in that directory. Everything works find.
>> However, with the sqlpp11-connector-sqlite3, the clone
>> works, the cmake works; however, make gives a compilation
>> error:
>>
>> /home/evansl/prog_dev/boost/git/sqlpp11/sqlpp11-connector-sqlite3/src/connection.cpp:29:31:
>> fatal error: sqlpp11/exception.h: No such file or directory
>> #include <sqlpp11/exception.h>
>>
>> caused by *not* including:
>>
>> -I/home/evansl/prog_dev/boost/git/sqlpp11/sqlpp11/include
>>
>> on the compiler command line.
>>
>> Is there something else I'm doing wrong?
>>
>> -regards,
>> Larry
>>
>>
> Hi Larry,
>
> First of all, thank you for your patience!

You're welcome.
Thanks for the work on this library!

>
> As far as I can tell the problem results from your install directory not
> being in the standard include path.

I suspected something like that.

>
> I have updated the CMakeLists file in the sqlpp11-connector-sqlite3
> repository to default to
>
> ../sqlpp11/include (as suggested by you a few mail earlier)
>
> This should work with your setup. I tested with your Makefile, just to
> be sure :-)
>

I tried it and it now works with the sqlite; however, I noticed that:

https://github.com/rbock/sqlpp11-connector-mysql/blob/master/CMakeLists.txt

doesn't have the same change. Wouldn't that same change be needed
there too?

-regards,
Larry

[Roland Bock]

Yes, I wanted to wait for your confirmation. Will adjust that tonight.

Thanks and regards,

Roland

[Larry Evans]

I'm not a cmake expert, but I'd think there would be some way to
inherit this adjustment so that you could specify it somewhere
and it would be propagated automagically to all the connector
projects.

Regards,
Larry

[Roland Bock]

The mysql connector has been updated as well.

I guess that in order to propagate anything, the connectors will have to
find the sqlpp11 directory somehow. I'll look into it some day.

Regards,

Roland

[Roland Bock]

On 2013-11-12 19:03, Eric Niebler wrote:

> On 11/12/2013 12:26 AM, Roland Bock wrote:

>> [...]

> FWIW, I agree with you. I like the looks of your EDSL. My suggestion:
> Look to the success of C#'s LINQ queries, and aim for that. If you can
> design your EDSL so it can be extended to bind to in-memory data
> structures, then it would be HUGE.
>

FYI: By that definition, as of today, it is HUGE :-)


It is still a long way to go, of course, but I do have a working SQL
insert and select on std::vector<some_struct>:

https://github.com/rbock/sqlpp11-connector-stl