On 9 November 2013 22: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.

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

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

On 2013-11-11 21:14, Larry Evans wrote:

On 11/11/13 13:35, Abel Sinkovics wrote:

...

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

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

On Mon, Nov 11, 2013 at 9:59 PM, Christof Donat 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?

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 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 cd@okunah.de Registergericht Augsburg Geschäftsführer Augsburg HRB 21896 Christof Donat UStID: DE 248 815 055

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... (here check the C++11 one at the bottom) Regards, Ábel

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

Hi, On 2013-11-11 23:16, Roland Bock wrote: 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

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

On 2013-11-12 19:14, Vicente J. Botet Escriba wrote:

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

...

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" : ""); 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 Sure, not impossible, but as stated before, I won't go there :-)

Best regards, Roland

On 2013-11-12 07:13, Christof Donat wrote:

Hi,

...

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

Am Montag, 11. November 2013, 23:16:06 schrieb Roland Bock: 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

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

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.

On 2013-11-12 09:39, Dominique Devienne wrote:

On Mon, Nov 11, 2013 at 9:31 PM, Roland Bock 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

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

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

On Wed, Nov 13, 2013 at 7:28 AM, Gavin Lambert 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

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 struct table_base_t : public ColumnSpec::_name_t::template _member_t>... {...};

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

On 2013-11-11 15:06, Larry Evans wrote:

On 11/11/13 02:36, Roland Bock wrote:

...

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 struct table_base_t : public ColumnSpec::_name_t::template _member_t>... {...};

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

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.

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.

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.

On 2013-11-11 11:27, Rodrigo Madera wrote:

On Sat, Nov 9, 2013 at 8:03 PM, Roland Bock 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

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

On 2013-11-11 15:36, Edward Diener wrote: 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::vectorstd::string Regards, Roland

On 2013-11-11 16:16, Larry Evans wrote:

On 11/11/13 09:01, Roland Bock 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::vectorstd::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

On 2013-11-11 15:36, Edward Diener wrote: 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?

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

On 2013-11-11 19:00, Larry Evans wrote:

On 11/11/13 09:44, Roland Bock wrote:

...

On 2013-11-11 16:16, Larry Evans wrote:

...

On 11/11/13 09:01, Roland Bock 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::vectorstd::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

On 2013-11-11 15:36, Edward Diener wrote: 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?

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.

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

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

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

On 11/11/13 14:54, Larry Evans wrote: 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

On 11/12/13 15:00, Roland Bock wrote:

On 2013-11-12 21:04, 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

On 11/11/13 14:54, Larry Evans wrote: 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 I assumed you'd be installing sqlpp11 first.

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

On 11/12/13 23:24, Roland Bock wrote:

On 2013-11-12 22:37, Larry Evans wrote:

...

On 11/12/13 15:00, Roland Bock wrote:

...

On 2013-11-12 21:04, 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

On 11/11/13 14:54, Larry Evans wrote: 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 I assumed you'd be installing sqlpp11 first.

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

I meant make install :-)

Regards,

Roland

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

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

On 11/14/13 03:31, Roland Bock wrote:

On 2013-11-13 19:49, Larry Evans wrote:

...

On 11/12/13 23:24, Roland Bock wrote:

...

On 2013-11-12 22:37, Larry Evans wrote:

...

On 11/12/13 15:00, Roland Bock wrote:

...

On 2013-11-12 21:04, Larry Evans wrote:

...

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 I assumed you'd be installing sqlpp11 first.

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

I meant make install :-)

Regards,

Roland

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

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

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

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

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

On 11/15/13 09:13, Roland Bock wrote:

On 2013-11-15 15:57, Larry Evans wrote:

...

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

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

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

Thanks and regards,

Roland

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

On 11/11/2013 10:01 AM, Roland Bock 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 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::vectorstd::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

On 2013-11-11 15:36, Edward Diener wrote: 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.

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

On 2013-11-11 15:36, Edward Diener wrote: 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. 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::verbatimsqlpp::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.

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

On Nov 11, 2013, at 10:01 AM, Roland Bock 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::vectorstd::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

On 2013-11-11 15:36, Edward Diener wrote: 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)

On Mon, Nov 11, 2013 at 10:53 AM, Roland Bock 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

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

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

On 2013-11-11 16:00, TONGARI J wrote:

Hi,

2013/11/10 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

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.

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

constexpr TabSample tab = {}; constexpr auto alpha = tab.alpha; ... Maybe but the columns into a namespace to be able to differentiate in case of ambiguities. 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?

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

On 2013-11-11 20:44, Steven Watanabe wrote: that confusing. Regards, Roland

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

On 2013-11-11 16:30, Bjorn Reese wrote: 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.

On 11/12/13, 4:26 AM, Tim Keitt wrote:

On Sat, Nov 9, 2013 at 4:03 PM, Roland Bock wrote:

...

Hi,

over the last four or five years I developed several SQL libraries for C++. With C++11 I was finally able to create an SQL library that meets my own expectations and requirements. It is being used in production, I recently put it on github, and I would really like to hear from you guys whether this is something that could be interesting for you personally or for boost?

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

sqlpp11 is a template library representing an embedded domain specific language (EDSL) that allows you to

* define types representing tables and columns, * construct type safe queries checked at compile time for syntax errors, type errors, name errors and even some semantic errors, * interpret results by iterating over query-specific structs with appropriately named and typed members.

This results in several benefits, e.g.

* the library user operates comfortably on structs and functions, * the compiler reports many kinds of errors long before the code enters unit testing or production, * the library hides the gory details of string construction for queries and interpreting string based results returned by select calls. I.e. you don't need to use strings in query construction where you wouldn't use them in SQL and there is no need to use positional arguments or to parse strings when obtaining fields from a result row (the latter being true unless you do not know which columns to select at compile time).

The library supports both static and dynamic queries. The former offers greater benefit in terms of type and consistency checking. The latter makes it easier to construct queries on the flight.

Specific traits of databases (e.g. unsupported or non-standard features) are known at compile time as well. This way, the compiler can tell the developer at compile time if a query is not accepted by the database (e.g. if a feature is missing). And the library can form the query in the correct manner, for instance if the engine uses concat instead of operator|| to concatenate strings.

Two Examples: ============= Static Select: -------------- // selecting zero or more results, iterating over the results for (const auto& row : db.run( select(foo.name, foo.hasFun) .from(foo) .where(foo.id > 17 and foo.name.like("%bar%")))) { if (row.name.is_null()) std::cerr << "name will convert to empty string" << std::endl; std::string name = row.name; // text fields are implicitly convertible to string bool hasFun = hasFun; // bool fields are implicitly convertible to bool }

Dynamic Select: ---------------- auto s = dynamic_select(db, tab.foo).dynamic_columns().from(tab); if (userWantsBar) s.add_column(tab.bar); for(const auto& row : run(s)) { std::cerr << "row.foo: " << row.foo; if (userWantsBar) std::cerr << "row.bar" << row.at("bar"); std::cerr << std::endl; };

Please let me know your questions/thoughts/suggestions/rants. Contributions welcome, of course :-)

This is an interesting thread and I thought I'd comment.

I am a pretty heavy user of postgresql/postgis (spatial extension) in my work. I wrote the first R package to access postgresql and contributed to the current R DBI package. I did a proof-of-concept (= not very pretty ;-) embedding of the Boost Graph Library in postgresql replacing local storage with prepared queries called on demand.

I have to say when I look at this, I don't really want to learn another SQL. I am perfectly happy to send query strings to the database and let it parse them. I can debug these separate from my C++ code. I think for complex queries (recursive with anyone?) it would be quite difficult to get the C++ right.

What I would really like is a mapping of binary cursors to iterator concepts + easy type-safe endian-aware customizable data conversion. But that's my bias. I've always liked mapping on-demand data to common interfaces.

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

THK

Hi, Isn't libpqxx then not what you are looking for? I know its specifically written for PostgreSQL. http://pqxx.org/development/libpqxx/ Regards, Matthijs

On 11/12/13, 2:17 PM, Roland Bock wrote:

On 2013-11-12 10:00, Matthijs Möhlmann wrote:

...

On 11/12/13, 4:26 AM, Tim Keitt wrote:

...

On Sat, Nov 9, 2013 at 4:03 PM, Roland Bock wrote:

...

Hi,

over the last four or five years I developed several SQL libraries for C++. With C++11 I was finally able to create an SQL library that meets my own expectations and requirements. It is being used in production, I recently put it on github, and I would really like to hear from you guys whether this is something that could be interesting for you personally or for boost?

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

sqlpp11 is a template library representing an embedded domain specific language (EDSL) that allows you to

* define types representing tables and columns, * construct type safe queries checked at compile time for syntax errors, type errors, name errors and even some semantic errors, * interpret results by iterating over query-specific structs with appropriately named and typed members.

This results in several benefits, e.g.

* the library user operates comfortably on structs and functions, * the compiler reports many kinds of errors long before the code enters unit testing or production, * the library hides the gory details of string construction for queries and interpreting string based results returned by select calls. I.e. you don't need to use strings in query construction where you wouldn't use them in SQL and there is no need to use positional arguments or to parse strings when obtaining fields from a result row (the latter being true unless you do not know which columns to select at compile time).

The library supports both static and dynamic queries. The former offers greater benefit in terms of type and consistency checking. The latter makes it easier to construct queries on the flight.

Specific traits of databases (e.g. unsupported or non-standard features) are known at compile time as well. This way, the compiler can tell the developer at compile time if a query is not accepted by the database (e.g. if a feature is missing). And the library can form the query in the correct manner, for instance if the engine uses concat instead of operator|| to concatenate strings.

Two Examples: ============= Static Select: -------------- // selecting zero or more results, iterating over the results for (const auto& row : db.run( select(foo.name, foo.hasFun) .from(foo) .where(foo.id > 17 and foo.name.like("%bar%")))) { if (row.name.is_null()) std::cerr << "name will convert to empty string" << std::endl; std::string name = row.name; // text fields are implicitly convertible to string bool hasFun = hasFun; // bool fields are implicitly convertible to bool }

Dynamic Select: ---------------- auto s = dynamic_select(db, tab.foo).dynamic_columns().from(tab); if (userWantsBar) s.add_column(tab.bar); for(const auto& row : run(s)) { std::cerr << "row.foo: " << row.foo; if (userWantsBar) std::cerr << "row.bar" << row.at("bar"); std::cerr << std::endl; };

Please let me know your questions/thoughts/suggestions/rants. Contributions welcome, of course :-)

This is an interesting thread and I thought I'd comment.

I am a pretty heavy user of postgresql/postgis (spatial extension) in my work. I wrote the first R package to access postgresql and contributed to the current R DBI package. I did a proof-of-concept (= not very pretty ;-) embedding of the Boost Graph Library in postgresql replacing local storage with prepared queries called on demand.

I have to say when I look at this, I don't really want to learn another SQL. I am perfectly happy to send query strings to the database and let it parse them. I can debug these separate from my C++ code. I think for complex queries (recursive with anyone?) it would be quite difficult to get the C++ right.

What I would really like is a mapping of binary cursors to iterator concepts + easy type-safe endian-aware customizable data conversion. But that's my bias. I've always liked mapping on-demand data to common interfaces.

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

THK

Hi,

Isn't libpqxx then not what you are looking for? I know its specifically written for PostgreSQL.

http://pqxx.org/development/libpqxx/

Regards, Matthijs

It also string based, isn't it? For those who'd like to experiment with sqlpp11 and postgreSQL: I just received a mail that there is a first connector library for postgreSQL available:

https://github.com/pbondo/sqlpp11-connector-postgresql

Regards,

Roland

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

On Tue, Nov 12, 2013 at 7:16 AM, Roland Bock wrote:

On 2013-11-12 04:26, Tim Keitt wrote:

...

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

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/

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