On 2014-02-01 18:14, Edward Diener wrote:

On 2/1/2014 9:04 AM, Roland Bock wrote:

...

Dear boosters,

after the lively discussion about sqlpp11 in November 2013 (http://lists.boost.org/Archives/boost/2013/11/208388.php), sqlpp11 has evolved quite a bit and I want to give you an update.

https://github.com/rbock/sqlpp11

[...]

...

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

Does not build under Windows.

Hi, I assume you tried MSVC? Then I am not surprised after watching this: http://channel9.msdn.com/Series/C9-Lectures-Stephan-T-Lavavej-Core-C-/Core-C... You need a compiler and standard library that supports pretty much all of C++11. Could you send me compiler version and output? Maybe off the list, since I assume it is a lot?

You need documentation somewhere.

There's documentation in the wiki: https://github.com/rbock/sqlpp11/wiki It is in no way complete, but a good point to start, I think. Regards, Roland

On 2014-02-01 21:51, Edward Diener wrote:

On 2/1/2014 12:27 PM, Roland Bock wrote:

...

On 2014-02-01 18:14, Edward Diener wrote:

...

On 2/1/2014 9:04 AM, Roland Bock wrote:

...

Dear boosters,

after the lively discussion about sqlpp11 in November 2013 (http://lists.boost.org/Archives/boost/2013/11/208388.php), sqlpp11 has evolved quite a bit and I want to give you an update.

https://github.com/rbock/sqlpp11

[...]

...

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

Does not build under Windows.

Hi,

I assume you tried MSVC? Then I am not surprised after watching this: http://channel9.msdn.com/Series/C9-Lectures-Stephan-T-Lavavej-Core-C-/Core-C...

You need a compiler and standard library that supports pretty much all of C++11.

Could you send me compiler version and output? Maybe off the list, since I assume it is a lot?

Using 'cmake CMakeLists.txt' a VC++ 12 solution was generated. Attempting to build the solution gives:

'2> '..\scripts\ddl2cpp' is not recognized as an internal or external command,'

most everywhere. Huh. My bad. scripts\ddl2cpp is not a Windows executable/batch file...

The offending line is in tests/CMakeLists.txt: COMMAND ${CMAKE_SOURCE_DIR}/scripts/ddl2cpp ${CMAKE_CURRENT_LIST_DIR}/sample.sql Sample test I attached a new version of that file which should work (I hope), by explicitly calling the python executable.

...

...

You need documentation somewhere.

There's documentation in the wiki:

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

It is in no way complete, but a good point to start, I think.

OK, thanks !

As a suggestion for Boost, putting the files in the modular-boost format and either providing HTML docs, using Boost build to generate the HTML docs, or using CMake to generate the docs would be helpful to those in Boost who might want to look at your efforts.

Thanks! I'll look into that!

On 2014-02-03 13:37, Thorsten Ottosen wrote:

On 01-02-2014 15:04, Roland Bock wrote:

...

Dear boosters,

...

PS: Given enough interest, I intend to officially propose the library to boost (after several more iterations, of course). This is why I hope it is OK to discuss sqlpp11 on this mailing list every now and then.

Rest assured that the interest is there.

regards

-Thorsten

Thanks :-)

On 2014-02-03 14:50, Bjorn Reese wrote:

Table columns have a _can_be_null option. How about handling this implicitly by declaring the _value_type be a boost::optional type?

Hi Bjorn, I thought about using boost::optional for some time, but decided against it for the time being. In most usecases I encountered so far, it is totally OK to interpret NULL values like default values, e.g. NULL strings and empty strings, or NULL foreign keys or 0LL. For those usecases it would be quite annoying to have to check if there really is a value, or always use get_optional_value_or... This approach shows not only in result evaluation but also supported during statement construction with the tvin() method (yet to be documented): tvin stands for Trivial Value Is NULL, e.g: select(all_of(tab)).from(tab).where(tab.beta == sqlpp::tvin(some_string)); The condition will be interpreted as tab.beta = 'some string' or tab.beta IS NULL depending on the value of the variable some_string. Also, it seems to me that binding binary parameters and result fields to boost::optional might be harder to do. But then, sqlpp11 is relatively easy to extend when it comes to new functions or value types. If you want types based on boost optional, you could have that (although you'd probably have to deal with the binary binding in some cases). It might be a nice extension :-) Cheers, Roland

On Mon, Feb 3, 2014 at 5:33 PM, Edward Diener wrote:

In most usecases I encountered so far, it is totally OK to interpret

...

NULL values like default values, e.g. NULL strings and empty strings, or NULL foreign keys or 0LL. For those usecases it would be quite annoying to have to check if there really is a value, or always use get_optional_value_or...

You are wrong ! Having a database NULL value is completely different from having an empty string or a 0 value. Please reconsider. The boost::optional is the correct choice.

+1

On 2014-02-03 18:22, Johan Baltié wrote:

On Mon, Feb 3, 2014 at 5:56 PM, Klaim - Joël Lamotte wrote:

...

On Mon, Feb 3, 2014 at 5:33 PM, Edward Diener

...

wrote: In most usecases I encountered so far, it is totally OK to interpret

...

NULL values like default values, e.g. NULL strings and empty strings, or NULL foreign keys or 0LL. For those usecases it would be quite annoying to have to check if there really is a value, or always use get_optional_value_or...

You are wrong ! Having a database NULL value is completely different from having an empty string or a 0 value. Please reconsider. The boost::optional is the correct choice.

+1

I do agree. NULL means "no data", not "0".In SQL "NULL = NULL" is false.

Oracle chose empty string to be equivalent to NULL and it's quite a pain to handle from a developper point of view.

Yeah, heard of that. And it would certainly be bad, if sqlpp11 did the same. It does not. If you want to interpret NULL as NULL, fine, use the .is_null() method to check. If you want to interpret NULL as "" or 0, also fine, just get the value. Best, Roland

On 2014-02-03 18:36, Johan Baltié wrote:

On Mon, Feb 3, 2014 at 6:27 PM, Roland Bock wrote:

...

On 2014-02-03 18:22, Johan Baltié wrote:

...

On Mon, Feb 3, 2014 at 5:56 PM, Klaim - Joël Lamotte

...

On Mon, Feb 3, 2014 at 5:33 PM, Edward Diener

...

wrote: In most usecases I encountered so far, it is totally OK to interpret

...

NULL values like default values, e.g. NULL strings and empty strings, or NULL foreign keys or 0LL. For those usecases it would be quite annoying to have to check if there really is a value, or always use get_optional_value_or...

You are wrong ! Having a database NULL value is completely different from having an empty string or a 0 value. Please reconsider. The boost::optional is the correct choice. +1 I do agree. NULL means "no data", not "0".In SQL "NULL = NULL" is false.

Oracle chose empty string to be equivalent to NULL and it's quite a pain to handle from a developper point of view.

Yeah, heard of that. And it would certainly be bad, if sqlpp11 did the same. It does not.

If you want to interpret NULL as NULL, fine, use the .is_null() method to check.

If you want to interpret NULL as "" or 0, also fine, just get the value.

I think all the DBMS provide a way to check that internally (NVL(column, default_value) for Oracle).

What bother me is that you can have quite easily inconsistent behaviors. You retrieve values and you work with them in a select, you have no warning that a null value was encountered but you have no result or worse the wrong ones...

It should not be the default behavior.

The behavior could be defined via the value_type as Bjorn suggested in his post. But as soon as there is an outer join for instance, there could be NULL values all over the place. As stated in my reply to Edward, it is tough to impossible to determine exactly which columns in a result set could be NULL, if the query structure is not fully known at compile time. Would you want to turn everything into a boost::optional then? That would be pretty simple, of course. Regards, Roland

On 2014-02-03 22:25, Johan Baltié wrote:

On Mon, Feb 3, 2014 at 7:02 PM, Roland Bock wrote:

...

On 2014-02-03 18:36, Johan Baltié wrote:

...

...

On 2014-02-03 18:22, Johan Baltié wrote: What bother me is that you can have quite easily inconsistent behaviors. You retrieve values and you work with them in a select, you have no warning

On Mon, Feb 3, 2014 at 6:27 PM, Roland Bock wrote: that a null value was encountered but you have no result or worse the wrong ones...

It should not be the default behavior.

The behavior could be defined via the value_type as Bjorn suggested in his post. But as soon as there is an outer join for instance, there could be NULL values all over the place.

As stated in my reply to Edward, it is tough to impossible to determine exactly which columns in a result set could be NULL, if the query structure is not fully known at compile time. Would you want to turn everything into a boost::optional then?

Yes. It seems the safer way to me. OK, that would be easy to do. At least with something equivalent to boost::optional in the sense that when you try to access a value that is NULL, an assert will fail.

I drafted a document for what I have in mind: https://github.com/rbock/sqlpp11/wiki/NULL

If an user doesn't like optionals he can add something to the statement so that he gets non optional types and an exception if a NULL occurs. I do not know if it is a good design idea, but throwing an exception when you've asserted that you do not wan't NULL does not seems stupid.

As you can see from the document mentioned above, I'd rather offer a way to make the .nit() call implicit. I would not want to change hundreds or even thousands of lines of code that read in strings from the database and really, really, really don't care if it is NULL or "".

BTW it seems quite promising and interesting, sorry to hear it cannot be build under MSVC.

Thanks for the flowers :-) I have pushed the cmake change from earlier in this thread to a new release. It will still not compile with Windows, but at least there are better chances now that it does start to compile. I'd be interested in the error messages! Maybe it is just a few fixes? I know that there is trouble with '= default'.

Do you have some benchmark against "native" drivers ?

No. Not yet. I guess it will be hard to say: "sqlpp11" is this much faster or slower than XYZ. It really depends on the connector library, I'd say. sqlpp11 is the frontend that compiles your SQL expression into a string (or something else). The connectors available today give that string to the C library and return the results. But it would certainly be a good idea to compare with SOCI or CppDb for example using the existing connectors. I'll look into that as soon as I find time. Do you know of existing comparison setups? Regards, Roland

On Mon, Feb 3, 2014 at 10:25 PM, Johan Baltié wrote:

On Mon, Feb 3, 2014 at 7:02 PM, Roland Bock wrote:

...

On 2014-02-03 18:36, Johan Baltié wrote: As stated in my reply to Edward, it is tough to impossible to determine exactly which columns in a result set could be NULL, if the query structure is not fully known at compile time. Would you want to turn everything into a boost::optional then?

Yes. It seems the safer way to me.

If an user doesn't like optionals he can add something to the statement so that he gets non optional types and an exception if a NULL occurs. I do not know if it is a good design idea, but throwing an exception when you've asserted that you do not wan't NULL does not seems stupid.

That's how Oracle OCI behaves. If you don't provide an explicit "indicator" to know whether a value is null or not, OCI errors out if you do get a null (and I convert this error into an exception myself). So even w/o knowing the query at compile time, it's the client code that decides whether nulls are OK by using (defining in OCI terms) an optional<T> or a T (or another mechanism, I'm not tied to optional<>). In contrast, SQLite just returns you an "abstract" value ( http://www.sqlite.org/c3ref/value.html) and has APIs to know the value's type (http://www.sqlite.org/c3ref/value_blob.html), with null being of of those "types". (and you can have values of the "wrong" type compared to the declared type of the column, that's the dynamic nature of SQLite, unlike most SQL engine). IMHO, you cannot just assume in the general case an empty string, or a 0, or a NaN, etc... are nulls. Nullness must be explicit, and not in the value-space of the data you are binding (in WHERE clause or inserting/updating) or defining (in SELECT clause). My $0.02. --DD

On 2014-02-03 17:33, Edward Diener wrote:

On 2/3/2014 10:03 AM, Roland Bock wrote:

...

On 2014-02-03 14:50, Bjorn Reese wrote:

...

Table columns have a _can_be_null option. How about handling this implicitly by declaring the _value_type be a boost::optional type?

Hi Bjorn,

I thought about using boost::optional for some time, but decided against it for the time being.

In most usecases I encountered so far, it is totally OK to interpret NULL values like default values, e.g. NULL strings and empty strings, or NULL foreign keys or 0LL. For those usecases it would be quite annoying to have to check if there really is a value, or always use get_optional_value_or...

You are wrong ! Having a database NULL value is completely different from having an empty string or a 0 value. Please reconsider. The boost::optional is the correct choice.

Edward, after a short night's sleep I drafted a document for how sqlpp11 could cover both use cases: 1. NULL is NULL and nothing else, dammit! 2. NULL or 0? Sometimes I just don't care. https://github.com/rbock/sqlpp11/wiki/NULL Please let me know what you think. Regards, Roland

On 2014-02-04 19:56, Edward Diener wrote:

On 2/4/2014 2:05 AM, Roland Bock wrote:

...

On 2014-02-03 17:33, Edward Diener wrote:

...

On 2/3/2014 10:03 AM, Roland Bock wrote:

...

On 2014-02-03 14:50, Bjorn Reese wrote:

...

Table columns have a _can_be_null option. How about handling this implicitly by declaring the _value_type be a boost::optional type?

Hi Bjorn,

I thought about using boost::optional for some time, but decided against it for the time being.

In most usecases I encountered so far, it is totally OK to interpret NULL values like default values, e.g. NULL strings and empty strings, or NULL foreign keys or 0LL. For those usecases it would be quite annoying to have to check if there really is a value, or always use get_optional_value_or...

You are wrong ! Having a database NULL value is completely different from having an empty string or a 0 value. Please reconsider. The boost::optional is the correct choice.

Edward,

after a short night's sleep I drafted a document for how sqlpp11 could cover both use cases:

1. NULL is NULL and nothing else, dammit! 2. NULL or 0? Sometimes I just don't care.

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

Please let me know what you think.

This is strictly my preference but I think it would be much easier and cleaner if all values, as objects, passed to and returned from a database were boost::optional<T> values for the particular type T of a column. That way the possibility that a column could hold a DB NULL value would be fully taken care of and it is now up to the end-user to realize that a DB NULL could be a valid value and deal with it accordingly.

I admit that the idea of using boost::optional (or something very similar) not only for select results but also for values in statements is growing on me. It seems like a nice symmetry indeed. I'll spend some time letting it sink (and I also need to think about how to handle my own use case elegantly). Thanks and regards, Roland

On Mon, Feb 3, 2014 at 5:32 PM, Roland Bock wrote:

On 2014-02-03 17:21, Alexander Lamaison wrote:

...

Roland Bock writes:

...

// Then you run the insert db.run(i); Just a thought: why not `operator()` instead of `run`, making the db a callable?

In addition to .run() there is also .prepare() which prepares a statement for later and possibly repeated execution possibly with parameters. The arguments for .run() and .prepare() are the same.

Tim Keitt suggested .execute() instead of run(), which would certainly be an option, but is much longer than .run() :-)

Execute or exec for short is more idiomatic in the DB world, I concur. When iterating the result set, SQLite uses step() while OCI uses fetch(). Just FYI I guess. These are details of course. --DD SQLite: https://www.sqlite.org/c3ref/exec.html Oracle: http://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci17msc001.htm#i57489...

Roland Bock writes:

...

Roland Bock writes:

...

Dear boosters,

after the lively discussion about sqlpp11 in November 2013 (http://lists.boost.org/Archives/boost/2013/11/208388.php), sqlpp11 has evolved quite a bit and I want to give you an update.

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

...

auto i = insert_into(tab).columns(tab.alpha, tab.beta);

//Then you add values (one or more times): i.values(tab.alpha = 7, tab.beta = "seven");

// Then you run the insert db.run(i); Just a thought: why not `operator()` instead of `run`, making the db a callable?

Alex In addition to .run() there is also .prepare() which prepares a statement for later and possibly repeated execution possibly with

On 2014-02-03 17:21, Alexander Lamaison wrote: parameters. The arguments for .run() and .prepare() are the same.

Those other methods do non-'run-like' stuff so need custom names, but we already have an accepted name in C++ for methods that might otherwise be called 'run', 'execute', 'do', 'call', 'perform', 'invoke', etc. That name is 'operator()'. Alex -- Swish - Easy SFTP for Windows Explorer (http://www.swish-sftp.org)