Steve Hutton <shutton <at> featurecomplete.com> writes: [...]
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.
Yes. I believe that the key abstractions have been identified once and for all, to the point that many libraries in this area share a common design. However, as in the long run the objective is to propose a standard interface the details of the API become important.
// 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. 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".
Prepared statements are also supported, e.g.
Statement st = (sql.prepare << "insert into numbers(value) values(:val)", use(valsIn));
See? Given that you have to spell out "sql.prepare" you might as well make it a plain function call.
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.
I started out with the idea of writing a "Boostable" library so it came natural to leverage existing representations for useful abstractions. Note that I actually have distinct representations for "might be null" and "always null"; I'm not yet sure these are really required, but they came out naturally when I started translating my interface in ODBC calls.
// 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...
I'm not familiar with how you do it, but this is something that is only roughly sketched in my code. I need a more complete representation of types, from both the SQL and the C++ points of view. By the way, I find that this is an instance of a more general problem, that is the need to formulate two representations for each concept, one for the SQL side and one for the C++ side, hopefully in ways that are reasonably practical in each world and that can be easily connected.
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.
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. Cheers, Nicola Musatti