Re: [boost] Boost.SQL, SOCI and beyond

From: Nicola Musatti (Nicola.Musatti_at_[hidden])
Date: 2006-10-05 05:13:02

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