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

From: Jeff Garland (jeff_at_[hidden])
Date: 2006-10-06 02:36:56

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