Jeff Garland wrote:

Nicola Musatti wrote:

...

Hallo, a few months ago there was a discussion on how a database interface library would be a welcome addition to TR2 and the SOCI developers suggested that SOCI could be a valid candidate for that role (http://article.gmane.org/gmane.comp.lib.boost.devel/140816). They also mentioned their intention to make SOCI ready for submission to Boost.

Was any progress made on this issue? Is there any interest in reopening the discussion on such a library, SOCI submission notwithstanding?

I'm not sure what there is to discuss. It didn't happen :-( And TR2 submission time is over. I still want to see SOCI or the equivalent in Boost, but the authors have to bring it forward. Last I knew they were waiting to have an ODBC driver first. I didn't see the need for that, but they thought it was essential...

Missing the TR2 deadline was unfortunate, but probably putting together a valid proposal deserved more time than was available, even with such an impressive starting point such as SOCI. Lately I've been trying to put together a few ideas on how I think such a library should look like and I'd be happy to discuss them with anybody who is interested. To get the ball rolling, here is an example of how a program using my hypothetical Boost.Dbi (for DataBase Interface) library might look like: #include <iostream> #include <ostream> #include <boost/optional.hpp> #include <boost/none.hpp> #include <boost/dbi.hpp> int main() { try { // Connect to a database, specifying the underlying technology. Unless // specific SQL syntax is used, this should be the only place where the // actual DBMS product / technology is evident boost::dbi::connection conn("ODBC"); conn.connect("PVAL", "dba", "sql"); // Execute a couple of simple queries boost::dbi::statement st(conn); try { st.exec("drop table t"); conn.commit(); } catch ( boost::dbi::error & e ) { std::cerr << e.what() << '\n'; } st.exec("create table t ( a int, b varchar(10), c double, " "d int )"); // Execute a parameterized query long a; std::string b; boost::optional<double> c; // a value that may be null boost::dbi::null_value<long> d; // a value that is always null // prepare the statement and bind input parameters, so as to // be able to execute it in a loop with different values st.prepare("insert into t ( a, b, c, d ) values ( ?, ?, ?, ? )", a, b, c, d); char * nums[] = { "zero", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine" }; for ( a = 0; a < 10; ++a ) { b = nums[a]; if ( a % 2 ) c = a * 1.1; else c = boost::none; st.exec(); } conn.commit(); // Execute a query that returns a result set st.exec("select * from t"); boost::dbi::result_set rs(st); // Explore the structure of the result set std::cout << "No. of columns: " << rs.column_count() << '\n'; for ( int i = 0; i < rs.column_count(); ++i ) { std::cout << rs[i].name() << "\t " << rs[i].type().name() << '(' << rs[i].size() << ", " << rs[i].precision() << ')'; if ( rs[i].nullable() ) std::cout << "\t null"; std::cout << '\n'; } // Print the actual results std::cout << "\nRows:\n"; for ( boost::dbi::result_set::iterator i = rs.begin(); i != rs.end(); ++i ) { boost::dbi::row const & r = *i; // Columns may be accessed by position... std::cout << r[0].as<long>() << "\t "; // ...or by name std::cout << r["b"].as<std::string>() << "\t "; boost::optional<double> c = r[2].as<boost::optional<double> >(); if ( ! c ) std::cout << "(null)\t "; else std::cout << *c << "\t "; boost::optional<long> d = r["d"].as<boost::optional<long> >(); if ( ! d ) std::cout << "(null)\n"; else std::cout << *d << '\n'; } conn.disconnect(); } catch ( boost::dbi::error & e ) { std::cerr << e.what() << '\n'; std::cout << "Failure!\n"; return 1; } std::cout << "Success!\n"; } Cheers, Nicola Musatti

Jeff Garland <jeff <at> crystalclearsoftware.com> writes:

Nicola Musatti wrote: [...]

...

Missing the TR2 deadline was unfortunate, but probably putting together a valid proposal deserved more time than was available, even with such an impressive starting point such as SOCI.

I think there was more than enough time. I tried to talk everyone working on the various database libraries to work together to bring a proposal. I didn't succeed in convincing them

I'm not convinced, especially since as far as I know all of the involved people work on this issue out of their spare time. Rather I believe the question is how to ensure that developers can take profit of other libraries that are compliant to Committee approved interfaces, before another 10 to 15 years go by, but that's another story which probably is best discussed elsewhere. [...]

Well before I even consider engaging this discussion, why not help SOCI get into boost instead of inventing a new interface? We have several proposals and half-finished libraries, but the real problem is that we don't have an actual reviewed and accepted library to put into Boost. Are you going to have time to really develop this library from scratch?

Just to make this clear: I have absolutely nothing against a boostified version of SOCI being accepted into Boost and eventually submitted to the Committee for inclusion in a future TR or directly into the Standard. However I am concerned about the suitability of some of the interface choices made by the SOCI developers. I started writing my own library because I felt that my arguments would carry more weight with a concrete implementation to support them, however experimental, and because starting from scratch with Boost compliance in mind was simpler than attempting a partial boostification of the SOCI code base. Unless the SOCI developers decide for some reason that they are not interested in submitting their library to Boost anymore I see no reason for my library to survive as anythong more than a personal pastime. Should that happen I feel confident that I will be able to complete the ODBC backend I'm working on; more than that is unlikely.

In my view, SOCI has all the qualities to succeed as a Boost library including a clean, good documentation, support for multiple databases, and several developers to support it. So maybe you should start by downloading, installing, and reviewing the SOCI interface in comparison to what you are thinking?

My example is the result of comparing SOCI's API with that of similar, proprietary libraries I'm familiar with and trying to think about the best way to transform those in a standard compatible API. Cheers, Nicola Musatti

On 2006-10-05, Nicola Musatti <Nicola.Musatti@gmail.com> wrote:

Just to make this clear: I have absolutely nothing against a boostified version of SOCI being accepted into Boost and eventually submitted to the Committee for inclusion in a future TR or directly into the Standard.

However I am concerned about the suitability of some of the interface choices made by the SOCI developers. I started writing my own library because I felt that my arguments would carry more weight with a concrete implementation to support them, however experimental, and because starting from scratch with Boost compliance in mind was simpler than attempting a partial boostification of the SOCI code base.

Thanks for doing this - personally I welcome the discussion of these interface details, and I am open to considering the issues you are raising. I've never thought that a boostified version of SOCI would go through with exactly the interface it has today. There might be a scenario in which standalone SOCI lives on alongside its decendent boost.SQL, with a slightly different interface. However, we need to get Maciej's thoughts on these issues when he comes back online. Steve

Steve Hutton <shutton <at> featurecomplete.com> writes: [...]

Thanks for doing this - personally I welcome the discussion of these interface details, and I am open to considering the issues you are raising.

Thank you for listening!

I've never thought that a boostified version of SOCI would go through with exactly the interface it has today. There might be a scenario in which standalone SOCI lives on alongside its decendent boost.SQL, with a slightly different interface.

Actually I was wondering about how you meant to handle it. I'd imagine that part of your user base might not be very happy about having to change their code "just" to adopt the new interface. By the way, I think you should consider moving Boost.SQL (or whatever it will be called) to a separate repository. While the logical structure of the library probably won't change all that much, it will change dramatically from a purely textual point of view, considering the need to shift from CamelCase to lower_case. Maybe the Boost Sandbox could come handy.

However, we need to get Maciej's thoughts on these issues when he comes back online.

Definitely. Somehow these discussions seem to always take place close to his holidays :-) Cheers, Nicola Musatti

Steve Hutton <shutton <at> featurecomplete.com> writes:

[Maciej Sobczak wrote:]

The important thing to note about the interface is that the overloaded operators (this is the controversial part) are only syntax sugar over the more traditional interface, so that this:

int i; sql << "select ...", into(i);

is really just a thin wrapper over this:

int i; Statement st(sql); st.alloc(); st.prepare("select ..."); st.exchange(into(i)); st.execute(true);

In other words, SOCI does not force anybody to use the most convenient interface ever invented and always provides the less convenient alternatives. More on this: http://soci.sourceforge.net/doc/reference.html#statement

I'm aware of that but this is not really an alternative: having to remember to perform four function calls in the correct sequence is too error prone. Moreover, for this kind of operation I consider it preferable to submit a single interface to the Standard Committee. Cheers, Nicola Musatti

On 2006-10-04, Nicola Musatti <Nicola.Musatti@gmail.com> wrote:

Missing the TR2 deadline was unfortunate, but probably putting together a valid proposal deserved more time than was available, even with such an impressive starting point such as SOCI.

Indeed that's probably the case...

Lately I've been trying to put together a few ideas on how I think such a library should look like and I'd be happy to discuss them with anybody who is interested. To get the ball rolling, here is an example of how a program using my hypothetical Boost.Dbi (for DataBase Interface) library might look like:

Ok, I'll reply below with the SOCI equivalent of your example code, mainly because I don't think it differs too much in substance.

// Execute a parameterized query

long a; std::string b; boost::optional<double> c; // a value that may be null boost::dbi::null_value<long> d; // a value that is always null

// prepare the statement and bind input parameters, so as to // be able to execute it in a loop with different values

st.prepare("insert into t ( a, b, c, d ) values ( ?, ?, ?, ? )", a, b, c, d);

SOCI supports something very similar, with either positional binding like you show, or binding by name. sql << "insert into person(id, firstname, lastname) values(:id, :fn, :ln)", use(personId), use(firstName), use(lastName); sql << "insert into person(id, firstname, lastname) values(:id, :fn, :ln)", use(firstName, "fn"), use(lastName, "ln"), use(personId, "id"); Prepared statements are also supported, e.g. Statement st = (sql.prepare << "insert into numbers(value) values(:val)", use(valsIn)); for (int i = 0; i != 4; ++i) { st.execute(true); } One difference is your use of boost::optional to express nulls, versus SOCI's use of embedded-SQL-style indicator variables. SOCI currently doesn't use any code from boost so as to minimize dependencies, but I think boost::optional would be something we might want to leverage in a boostified version of SOCI.

// Execute a query that returns a result set

A Resultset object is something which we are adding to SOCI for the upcoming 2.2 releases...

st.exec("select * from t"); boost::dbi::result_set rs(st);

// Explore the structure of the result set

std::cout << "No. of columns: " << rs.column_count() << '\n'; for ( int i = 0; i < rs.column_count(); ++i ) { std::cout << rs[i].name() << "\t " << rs[i].type().name() << '(' << rs[i].size() << ", " << rs[i].precision() << ')'; if ( rs[i].nullable() ) std::cout << "\t null"; std::cout << '\n'; }

SOCI currenly has support for describing columns via the Row and ColumnProperties classes - this will be soon be accesible via Rowset iterators as well... Row r; sql << "select * from ex2", into(r);

// Columns may be accessed by position... std::cout << r[0].as<long>() << "\t ";

SOCI equivalent: r.get<long>(0)

std::cout << r["b"].as<std::string>() << "\t ";

SOCI equivalent: r.get<std::string>("b")

boost::optional<double> c = r[2].as<boost::optional<double> >(); if ( ! c ) std::cout << "(null)\t "; else std::cout << *c << "\t ";

SOCI has a way to specify a default to be used in case a value is Null: double c = r.get<double>(2, -1); Or you can test explicitly: eIndicator ind = r.indicator(2); if (ind == eNUll) { //etc...} In summary I think that our 2.2 release with iterator support will be very close to what you have sketched out here, with the exception of boost::optional integraton, which I think we can address in a boostified version of SOCI, post 2.2. Of course, there are also some additional features in SOCI that you didn't touch on here, e.g. support for custom types and basic O/R mapping... http://soci.sourceforge.net/doc/index.html Steve

On 2006-10-05, Dean Michael Berris <mikhailberis@gmail.com> wrote:

Hi Steve!

On 10/5/06, Steve Hutton <shutton@featurecomplete.com> wrote:

...

SOCI supports something very similar, with either positional binding like you show, or binding by name.

Would you by any chance be adding boost::tuple or boost::fusion tuple support for efficient result/element access? Or would statically defined positional interfaces be a bit harder to introduce with the current code?

Yes, we have plans to support Resultset<boost::tuple> Steve

Nicola Musatti wrote:

I started out with the idea of writing a "Boostable" library so it came natural to leverage existing representations for useful abstractions.

Nicola, Could you elaborate what you consider as boostable or boostified intefrace/library? May be you could point a document where I can find general guidelines about Boost conforming library interface design? I noticed you're using these terms like "boostified version of SOCI" or "suitability of some of the interface choices" or "with Boost compliance in mind" but I have no idea how to understand them. Understanding of these terms may be crucial to identify if SOCI is or isn't compliant with Boost library interface design guidelines, etc. I believe, it's not only a matter of taste, is it?

...

Row r; sql << "select * from ex2", into(r);

...

// Columns may be accessed by position... std::cout << r[0].as<long>() << "\t ";

SOCI equivalent: r.get<long>(0)

I consider this a small abstraction mismatch: you apply the index to the row and then you convert the field value to a C++ type. By the way, in this case operator[] is the natural way to express indexing, so I believe that operator overloading should be preferred.

I agree, only and only if elements of a sequence can be identified only by index. In SOCI, when working with Row representation the situation is different - elements of Row can accessed *also* via name, what's natural in DBMS, where columns are named. So, SOCI interface is consistent and intuitive in this case: r.get<long>(0) r.get<long>("column1) See: http://soci.sourceforge.net/doc/reference.html#row

...

Or you can test explicitly: eIndicator ind = r.indicator(2); if (ind == eNUll) { //etc...}

This is good too, but it comes for free when you use Boost.Optional. [...]

OK, and that's a good proposal for further development. Cheers -- Mateusz Loskot http://mateusz.loskot.net

Phil Nash wrote:

...

...

...

Row r; sql << "select * from ex2", into(r);

...

// Columns may be accessed by position... std::cout << r[0].as<long>() << "\t ";

SOCI equivalent: r.get<long>(0)

I consider this a small abstraction mismatch: you apply the index to the row and then you convert the field value to a C++ type. By the way, in this case operator[] is the natural way to express indexing, so I believe that operator overloading should be preferred.

I agree, only and only if elements of a sequence can be identified only by index. In SOCI, when working with Row representation the situation is different - elements of Row can accessed *also* via name, what's natural in DBMS, where columns are named. So, SOCI interface is consistent and intuitive in this case:

r.get<long>(0) r.get<long>("column1)

What about:

r[0].as<long>() r["column1"].as<long>()

It's a kind of syntactic sugar and IMHO does not make it more (or less) natural what Nicola is questioning. Cheers -- Mateusz Loskot http://mateusz.loskot.net

Phil Nash wrote:

On 10/5/06, Mateusz Loskot <mateusz@loskot.net> wrote:

...

Phil Nash wrote:

...

...

...

...

Row r; sql << "select * from ex2", into(r);

> // Columns may be accessed by position... > std::cout << r[0].as<long>() << "\t "; SOCI equivalent: r.get<long>(0) I consider this a small abstraction mismatch: you apply the index to the row and then you convert the field value to a C++ type. By the way, in this case operator[] is the natural way to express indexing, so I believe that operator overloading should be preferred.

I agree, only and only if elements of a sequence can be identified only by index. In SOCI, when working with Row representation the situation is different - elements of Row can accessed *also* via name, what's natural in DBMS, where columns are named. So, SOCI interface is consistent and intuitive in this case:

r.get<long>(0) r.get<long>("column1)

What about:

r[0].as<long>() r["column1"].as<long>() It's a kind of syntactic sugar and IMHO does not make it more (or less) natural what Nicola is questioning.

Well, admittedly it's not quite "as the ints do", but it is consistent with other languages and libraries (including MS's ADO), and is "natural" in the index case.

Yes, I agree.

I'm not personally too worried which of these is adopted anyway, was just pointing out the obvious :-)

Yes, I understand.

I'm more interested in how the iterator interface works out.

We've started implementing it already. Do you'd have any imagination how it should work, a few points? Cheers -- Mateusz Loskot http://mateusz.loskot.net

Nicola Musatti wrote:

Mateusz Loskot <mateusz <at> loskot.net> writes:

...

Nicola Musatti wrote: [...] Could you elaborate what you consider as boostable or boostified intefrace/library? May be you could point a document where I can find general guidelines about Boost conforming library interface design?

The most reasonable starting point is: http://boost.org/more/lib_guide.htm .

Yes, I know these documents and I've already started to analyze SOCI according these docs.

...

I noticed you're using these terms like "boostified version of SOCI" or "suitability of some of the interface choices" or "with Boost compliance in mind" but I have no idea how to understand them.

Understanding of these terms may be crucial to identify if SOCI is or isn't compliant with Boost library interface design guidelines, etc.

I believe, it's not only a matter of taste, is it?

In part. Coding and naming conventions, library organization and directory structure are objective issues which are documented in the page I linked above or elsewhere in the Boost web site and other details may be gathered from how existing Boost libraries are organized, especially the most recent ones.

Thank you! This is what I've expected to hear. I absolutely agree and I'm sure our understanding of boostification is very similar. I also understand what you mean as "the suitability of some of the interface choices made by the SOCI developers." Personally, I can't talk for the whole SOCI team, I'd like to follow all Boost guidelines and conventions, regarding naming, project layout, etc. Initially, I misunderstood you a little having an impression there are some conventions SOCI actually breaks regarding operator overloading or using get(index) instead of operator[], etc. Thanks again for clarification.

On the other hand defining what consititutes a suitable interface for a Boost or a standard library has to do with personal expectations and experience, and can only be subject to consensus among interested developers.

Yes. AFAIK, Boost gives developers some liberty in this subject.

...

...

...

Row r; sql << "select * from ex2", into(r);

...

// Columns may be accessed by position... std::cout << r[0].as<long>() << "\t ";

SOCI equivalent: r.get<long>(0) I consider this a small abstraction mismatch: you apply the index to the row and then you convert the field value to a C++ type. By the way, in this case operator[] is the natural way to express indexing, so I believe that operator overloading should be preferred.

I agree, only and only if elements of a sequence can be identified only by index. In SOCI, when working with Row representation the situation is different - elements of Row can accessed *also* via name, what's natural in DBMS, where columns are named. So, SOCI interface is consistent and intuitive in this case:

r.get<long>(0) r.get<long>("column1)

See: http://soci.sourceforge.net/doc/reference.html#row

I do not agree. The use of square brackets with non integer arguments is common when speaking of associative maps and is present in the standard library in std::map's interface.

Yes, I think I've been convinced :-) I'm sure we will discuss in SOCI team all concepts proposed here.

Moreover, I was pointing out another issue: when you have a row you first index it to get a field and then you translate its value to your destination. What I wrote as

r[0].as<long>()

could be expressed in a more SOCI-like syntax as

r.field(0).get<long>()

Hmm, it's very interesting point, again. Yes, I agree, these subtle differences can make a difference. I'm inclined to discuss about incorporating it to SOCI. Thank you for your valuable comments! Cheers -- Mateusz Loskot http://mateusz.loskot.net

Steve Hutton <shutton <at> featurecomplete.com> writes: [...]

1. Considering the use of boost::optional - it's is indeed the very elegant way to deal with NULL values, but SOCI indicators can provide more information than this. The indicator is a general information about the piece of data, which in addition to be eOK or eNull, might be also eTruncated (when the string is read into the buffer that is too short). boost::optional cannot do this and therefore cannot be the only interface. It might be provided as an additional interface for the most typical case (nobody's using char[] buffers anyway), but it cannot replace indicators.

I believe this is an instance of the need of representing concepts both in the SQL and in the C++ world. I see indicators as conceptually a part of the SQL world, in that they are a way to access additional, detailed information about the result of a query, beyond the retrieved value itself. Once the value is converted to a C++ type it assumes a value in the domain of that type... or not. In this case boost::optional or its moral equivalent is a reasonable representation. Side note: a while ago I wrote a Nullable<T> type that provided implicit conversion to T and threw an exception if it held a null value. Personally I'd prefer that kind of interface, but I think it's better to stick as much as possible to what's available in the standard library or in Boost.

2. Considering the indexing of Row (function-like vs. operator[]) - the syntactic sugar is a matter of taste, but I have nothing against, say, r[0].get<int>() (this is easy to do). Things like 'as<T>' are not so convincing, especially when compared with the existing interfaces, like boost::tuple.

I suspect there's some misunderstanding. By writing '.as<T>' I didn't mean to imply that retrieved field values should be converted to arbitrary types. I just feel, and I'm convinced that it's the same for you, that whenever it is possible it is better to write 'get<int>' than 'get_int'. I used 'as' to express value retrieval because it is used in libraries I'm familiar with. The only conversion that might be reasonable is towards string/wstring, in case the underlying technology provides an efficient implementation (e.g. because it retrieves values as strings and only converts them upon request).

3. For rationale on the interface choices (like operator overloading), please see http://soci.sourceforge.net/doc/rationale.html

I did read it. I know that your choices were thought out in full, but I still disagree about a few things :-) Cheers, Nicola Musatti

Nicola Musatti wrote:

Steve Hutton <shutton <at> featurecomplete.com> writes: [...]

Catching up....couple comments on the interface discussion....

...

...

// Execute a parameterized query

long a; std::string b; boost::optional<double> c; // a value that may be null boost::dbi::null_value<long> d; // a value that is always null

// prepare the statement and bind input parameters, so as to // be able to execute it in a loop with different values

st.prepare("insert into t ( a, b, c, d ) values ( ?, ?, ?, ? )", a, b, c, d); SOCI supports something very similar, with either positional binding like you show, or binding by name.

sql << "insert into person(id, firstname, lastname) values(:id, :fn, :ln)", use(personId), use(firstName), use(lastName);

sql << "insert into person(id, firstname, lastname) values(:id, :fn, :ln)", use(firstName, "fn"), use(lastName, "ln"), use(personId, "id");

I consider it a good thing that these SQL statements are represented in a single C++ statement, but I don't like the overloading of the shift and comma operators. The terms "prepare", "execute" are idiomatic in this context and should be preferred.

Overloading of shift is done all the time. Comma overload is stranger, but I think the syntax is clear here so the user doesn't really need to know?

I haven't given enough thought on how to represent alternative ways to bind parameters (e.g. by name rather than by position), but in principle I have no objection to your "use" and "into".

Boost.Parameter comes to mind ;-) http://www.boost.org/libs/parameter/doc/html/index.html As an aside, it also occurs to me that variadic templates might be very useful here as well. <...snip....>

...

...

std::cout << r["b"].as<std::string>() << "\t ";

SOCI equivalent: r.get<std::string>("b")

...

boost::optional<double> c = r[2].as<boost::optional<double> >(); if ( ! c ) std::cout << "(null)\t "; else std::cout << *c << "\t ";

SOCI has a way to specify a default to be used in case a value is Null: double c = r.get<double>(2, -1);

Not a bad idea.

...

Or you can test explicitly: eIndicator ind = r.indicator(2); if (ind == eNUll) { //etc...}

This is good too, but it comes for free when you use Boost.Optional.

[...]

...

Of course, there are also some additional features in SOCI that you didn't touch on here, e.g. support for custom types and basic O/R mapping... http://soci.sourceforge.net/doc/index.html

These are very delicate issues. I have nothing against your solution per se, but I am convinced that the C++ standard should acquire one and only one way to describe the structure of types, which should not be part of other libraries. Otherwise we'd get one syntax for SOCI, another for Serialization, etc.

Until such a library/mechanism is available other libraries should rely on existing standard/TRx features as much as possible and strive for minimality for what is missing. I don't have a complete solution in mind yet, but I believe that the way to go is to serialize to and from tuples and assume the existence of a conventional function call that binds a custom type instance to a tuple.

...just thinking out loud... Different persistence systems have different type meta-data needs. I'm not sure that they can or should be combined into one. Of course, ideally they are consistent, minimal, and work together. For SOCI, there is a need to map from relational tables/columns. Most serialization archives are 'positional' so they don't require this meta-data. For example, you can write your serialization code like this: template<class Archive> void load(Archive & ar, Person& p, unsigned int version) { ar & p.id; ar & p.firstName; ar & p.lastName; } However, this is insufficient for the xml archive (and for SOCI) since you have to include field names for your types. Hence the 'make_nvp' (name value pair) interface in serialization. So using you have to write something like: template<class Archive> void load(Archive & ar, Person& p, unsigned int version) ar & make_nvp("ID", p.id); ar & make_nvp("FIRST_NAME", p.firstName); ar & make_nvp("LAST_NAME", p.lastName); ... } which is very similar to the SOCI code typedef Values base_type; static Person from(Values const &v) { Person p; p.id = v.get<int>("ID"); p.firstName = v.get<std::string>("FIRST_NAME"); p.lastName = v.get<std::string>("LAST_NAME"); ... } Now ideally, we would be able to write a type, add serialization code and have it work with a special DB archive based on SOCI. We would prefer not to have to write an extra interface just for the database. Just looking at this it occurs to me that the approach is to make a derived Serialization Archive type which takes and SQL query to retrieve the value data. And then the only trick is for the 'from' to be replaced by serialization load. Just looking at it side by side I think they do exactly the same thing....I'm guessing with some effort this part could be unified? For a complete object relational mapping, however, there's one more bit of meta-data that is needed for the the mapping to work -- that's the database query. In the arbitrary case this may involve table joins and such. And the selected names need to match up what is done in the 'load' or 'from' functions. That is, if the select statement doesn't match the 'from' code it will break. So, I think there should be a that the library can enshrine this information consistently in a place 'close' to the from function. Doesn't seem like there's an example of this... Jeff

Steve Hutton <shutton <at> featurecomplete.com> writes: [...]

I think it would be good practice to keep both queries and their related TypeConversions in the same header file. But of course even this isn't enough to ensure queries don't break - the database itself can always change But using select * is another precaution that gives some added protection from database changes.

This is the reason why it's important that libraries such as SOCI provide a dynamic interface to query composition and navigable, explicitly meta-represented result sets. Those who want (or, rather, can afford) type safety should build it above the dynamic interface. For them code generation is an effective solution, especially considering that parsing SQL DDL (as opposed to C++ class definitions) is rather easy. Cheers, Nicola Musatti

Jeff Garland <jeff <at> crystalclearsoftware.com> writes:

Nicola Musatti wrote:

...

Steve Hutton <shutton <at> featurecomplete.com> writes: [...]

...

sql << "insert into person(id, firstname, lastname) values(:id, :fn, :ln)", use(firstName, "fn"), use(lastName, "ln"), use(personId, "id");

I consider it a good thing that these SQL statements are represented in a single C++ statement, but I don't like the overloading of the shift and comma operators. The terms "prepare", "execute" are idiomatic in this context and should be preferred.

Overloading of shift is done all the time. Comma overload is stranger, but I think the syntax is clear here so the user doesn't really need to know?

I wasn't objecting to overloading of shift per se, but rather to the fact that doing so doesn't gain us anything. Moreover, in a context where you have a "thing" you put data into and get data out of if there's one reasonable way of overloading the shift operators is to provide a stream abstraction which, in turn, may make it even more natural to provide stream iterators too (I'm aware of the discussion in the SOCI rationale - http://soci.sourceforge.net/doc/rationale.html - but I still don't agree).

...

I haven't given enough thought on how to represent alternative ways to bind parameters (e.g. by name rather than by position), but in principle I have no objection to your "use" and "into".

Boost.Parameter comes to mind

http://www.boost.org/libs/parameter/doc/html/index.html

Ehm, well, you know, to paraphrase a late, great italian comedian: library authors are many and I'm alone to study their libraries, I will never catch up...

As an aside, it also occurs to me that variadic templates might be very useful here as well.

As in so many contexts, for that matter. Doug Gregor has made a terrific job of it and I do hope he'll manage to pull it through. [...]

...

Until such a library/mechanism is available other libraries should rely on existing standard/TRx features as much as possible and strive for minimality for what is missing. I don't have a complete solution in mind yet, but I believe that the way to go is to serialize to and from tuples and assume the existence of a conventional function call that binds a custom type instance to a tuple.

...just thinking out loud...

Different persistence systems have different type meta-data needs. I'm not sure that they can or should be combined into one. Of course, ideally they are consistent, minimal, and work together. For SOCI, there is a need to map from relational tables/columns.

Certainly, but usually there are two sides to it; one is a possibly implicit description of the source/destination data type and the other is how that description is used in a specific context. Even for the first part there are different possible approaches, from properties, where an accessor/mutator pair is used as an abstraction for a field/data member, to providing uncontrolled, direct access to data members. In general, however, this part is independent from many details of the application context, which is why I feel that we should be very careful not to allow excessive proliferation.

Most serialization archives are 'positional' so they don't require this meta-data. For example, you can write your serialization code like this:

template<class Archive> void load(Archive & ar, Person& p, unsigned int version) { ar & p.id; ar & p.firstName; ar & p.lastName; }

[...]

template<class Archive> void load(Archive & ar, Person& p, unsigned int version)

ar & make_nvp("ID", p.id); ar & make_nvp("FIRST_NAME", p.firstName); ar & make_nvp("LAST_NAME", p.lastName); ... }

This interface is practical, but combining the meta-data and its use, and embedding both in functions, is limiting and prevents reuse. Suppose you had something like (in pseudo code): template <typename T> meta_class; template<> class meta_class<Person> : public tuple<int &, string &, string &> { typedef tuple<int &, string &, string &> tuple_type; public: static const int size = tuple_type::size; meta_class(Person & p) : tuple_type(p.id, p.firstName, p.lastName) {} }; Then you would be able to write a generic serialization function for Boost.Serialization and a generic object-relational mapping, based on position, for SOCI/Boost.SQL. Actually, the object-relational mapping would be achieved by simply providing an interface towards tuples. Then, if desired, by adding a static data member like const array<std::string, struct_tuple<Person>::tuple_type::size> meta_class::members = { "id", "firstName", "lastName" }; you'd be able to write generic name based mappings too (note that a similar approach wouldn't be constrained to a tuple based implementation, provided the expected (meta-)programming interface was made available).

which is very similar to the SOCI code

typedef Values base_type; static Person from(Values const &v) { Person p; p.id = v.get<int>("ID"); p.firstName = v.get<std::string>("FIRST_NAME"); p.lastName = v.get<std::string>("LAST_NAME"); ...

}

Now ideally, we would be able to write a type, add serialization code and have it work with a special DB archive based on SOCI. We would prefer not to have to write an extra interface just for the database. Just looking at this it occurs to me that the approach is to make a derived Serialization Archive type which takes and SQL query to retrieve the value data. And then the only trick is for the 'from' to be replaced by serialization load. Just looking at it side by side I think they do exactly the same thing....I'm guessing with some effort this part could be unified?

Exactly.

For a complete object relational mapping, however, there's one more bit of meta-data that is needed for the the mapping to work -- that's the database query. In the arbitrary case this may involve table joins and such. And the selected names need to match up what is done in the 'load' or 'from' functions. That is, if the select statement doesn't match the 'from' code it will break. So, I think there should be a that the library can enshrine this information consistently in a place 'close' to the from function. Doesn't seem like there's an example of this...

In my view the real problem is to decide where to stop, i.e. to decide what should go into Boost.SQL and what should go in other libraries, built above it. My view is very conservative: support as much of SQL as possible, but limit the interface with C++ to simple types and tuples. Remember that we need to come out with something that is digestible not only for the C++ standardization committee, but possibly also for the SQL one. Cheers, Nicola Musatti

Steve Hutton wrote:

On 2006-10-06, Nicola Musatti <Nicola.Musatti@gmail.com> wrote:

...

In my view the real problem is to decide where to stop, i.e. to decide what should go into Boost.SQL and what should go in other libraries, built above it.

It's an interesting point. I guess the downside of a limited interface might be that users could end up preferring to use an enhanced version of the library instead.

Certainly, but this is something where, in my opinion, the aims of SOCI the successful database access C++ open source library and of Boost.SQL the library that will be included in the Library TR 3 and C++1x diverge.

...

My view is very conservative: support as much of SQL as possible, but limit the interface with C++ to simple types and tuples. Remember that we need to come out with something that is digestible not only for the C++ standardization committee, but possibly also for the SQL one.

I can see that ideally the API of Boost.SQL would be as close as possible to the API of std::sql. But do you think it also might be reasonable to have Boost.SQL potentially be a superset of std::sql?

Certainly! There are several Boost libraries which I wouldn't want to include in a future standard, yet I find very useful: Spirit is the first that comes to my mind. What is important in my opinion is to layer the components very carefully and to tackle them one by one. This reminds me of another reason why I want Boost.SQL to be limited to basic SQL access; while the design for this part is sort of given and I do not expect difficulties in gaining acceptance, everybody (me included!) have their own pet idea about what should go above it. Cheers, Nicola Musatti

Steve Hutton wrote:

In summary I think that our 2.2 release with iterator support will be very close to what you have sketched out here, with the exception of boost::optional integraton, which I think we can address in a boostified version of SOCI, post 2.2.

Steve, Yes, I agree it's a very interesting idea, the boost::optional. I second it to post 2.2. Cheers -- Mateusz Loskot http://mateusz.loskot.net

Jeff Garland wrote:

Mateusz Loskot wrote:

...

Steve Hutton wrote:

...

In summary I think that our 2.2 release with iterator support will be very close to what you have sketched out here, with the exception of boost::optional integraton, which I think we can address in a boostified version of SOCI, post 2.2. Steve,

Yes, I agree it's a very interesting idea, the boost::optional. I second it to post 2.2.

While I agree consideration of null handling using optional makes sense, there has been some controversy in the past about the optional interface...so be aware that this approach may bring criticism from some people.

What better way is there of specifying a database null value being passed to or returned from a column of a particular row other than to use boost::optional is the key question. I believe the criticism of optional in the past regards using it with optional references, but when passing and returning database null values, only values would normally be used.

Nicola Musatti wrote:

Edward Diener <eldiener <at> tropicsoft.com> writes:

...

...

While I agree consideration of null handling using optional makes sense, there has been some controversy in the past about the optional interface...so beaware that this approach may bring criticism from some people. What better way is there of specifying a database null value being

Jeff Garland wrote: [...] passed to or returned from a column of a particular row other than to use boost::optional is the key question. I believe the criticism of optional in the past regards using it with optional references, but when passing and returning database null values, only values would normally be used.

I think Jeff refers to objections that have been raised about using pointer-like syntax for things that are not pointers.

I have to second everyone who favors boost::optional. It's IMO absolutely the best way of handling null-allowed fields. Yuval

Nicola Musatti wrote:

std::cout << r[0].as<long>() << "\t ";

Maybe r[0] could be a boost::any or something of the like ?

On 10/5/06, Mateusz Loskot <mateusz@loskot.net> wrote:

loufoque wrote:

...

Nicola Musatti wrote:

...

std::cout << r[0].as<long>() << "\t ";

Maybe r[0] could be a boost::any or something of the like ?

Currently, SOCI uses similar concept of type holder (near line 284)

http://soci.cvs.sourceforge.net/soci/src/core/soci.h?view=markup

IMHO, boost::any seems interesting subject for boostification task.

Or might not a boost::variant<bool, char, int32_t, int64_t, double, boost::ptime, std::string, std::vector<uint8_t> /* blob? */> make more sense? Is the set of types which can be stored in databases infinitely variable? On the flip side fo that coin, one also might want the ability to take result data stored in the DB as one type and treat it as some other type via an appropriate conversion operation (ala lexical_cast or serialization perhaps?). For example storing IP addresses in varchar form but treating them as asio::ip::address'es in your application. -- Caleb Epstein

Nicola Musatti <Nicola.Musatti <at> gmail.com> writes: [...]

Lately I've been trying to put together a few ideas on how I think such a library should look like and I'd be happy to discuss them with anybody who is interested. To get the ball rolling, here is an example of how a program using my hypothetical Boost.Dbi (for DataBase Interface) library might look like:

For those interested I uploaded to the vault a very rough version of my interface proposal, renamed to Boost.SqlCli. (http://boost-consulting.com/vault/index.php?direction=0&order=nom&directory=Database&) Hopefully the readme.txt file under libs\sql_cli\doc is enough to get you started. Comments are welcome! Cheers, Nicola Musatti

Steve Hutton wrote:

On 2006-10-04, Jeff Garland <jeff@crystalclearsoftware.com> wrote:

...

Nicola Musatti wrote:

...

Hallo, a few months ago there was a discussion on how a database interface library would be a welcome addition to TR2 and the SOCI developers suggested that SOCI could be a valid candidate for that role (http://article.gmane.org/gmane.comp.lib.boost.devel/140816). They also mentioned their intention to make SOCI ready for submission to Boost.

Was any progress made on this issue? Is there any interest in reopening the discussion on such a library, SOCI submission notwithstanding? I'm not sure what there is to discuss. It didn't happen :-( And TR2 submission time is over. I still want to see SOCI or the equivalent in Boost, but the authors have to bring it forward. Last I knew they were waiting to have an ODBC driver first. I didn't see the need for that, but they thought it was essential...

Ok, if we were waiting specifically for an ODBC driver that's news to me :-)

I'm pretty sure it was Maciej that expressed that opinion to me... <...snip...>

After the 2.2 release (in a couple of weeks), we are planning to branch our code base to start preparing for submission to boost.

Great, look forward to it :-) Jeff

Jeff Garland wrote:

Nicola Musatti wrote:

...

Hallo, a few months ago there was a discussion on how a database interface library would be a welcome addition to TR2 and the SOCI developers suggested that SOCI could be a valid candidate for that role (http://article.gmane.org/gmane.comp.lib.boost.devel/140816). They also mentioned their intention to make SOCI ready for submission to Boost.

Was any progress made on this issue? Is there any interest in reopening the discussion on such a library, SOCI submission notwithstanding?

I'm not sure what there is to discuss. It didn't happen :-( And TR2 submission time is over. I still want to see SOCI or the equivalent in Boost, but the authors have to bring it forward.

I'm a member of SOCI dev team for a few months and I can confirm we're seriously considering Boost direction :-) In most of discussions on the soci-devel list, there are some references to Boost policies, guidelines, submission, etc. Just to tell a few secrets, we've changed SOCI building system to follow Boost naming convention of libraries, after 2.2.0 release we're going to create separate branch to put there SOCI boosification, etc. So, I can say we're heading the Boost course.

Last I knew they were waiting to have an ODBC driver first. I didn't see the need for that, but they thought it was essential...

I'm not sure, but this ODBC requirement might be inherited from this summary: http://www.crystalclearsoftware.com/cgi-bin/boost_wiki/wiki.pl?Relational_Da... Cheers -- Mateusz Loskot http://mateusz.loskot.net