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

From: Steve Hutton (shutton_at_[hidden])
Date: 2006-10-06 03:14:55

On 2006-10-05, Nicola Musatti <Nicola.Musatti_at_[hidden]> wrote:
> 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.

Absolutely.  As we look towards submitting a modified version of SOCI to
boost, it's a good time to review these API details and look for
potential areas of improvement.

>> 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, 

Ok

> but I don't like the overloading of the shift and comma
> operators. 

Maciej has written about the rationale for those here:
http://soci.sourceforge.net/doc/rationale.html

> The terms "prepare", "execute" are idiomatic in this context and
> should be preferred.

SOCI actually uses both of those via the explicit Statement class...

> I haven't given enough thought on how to represent alternative ways to bind
> parameters (e.g. by name rather than by position), 

Personally I much prefer bind by name, and recommend it as a best
practice, because I feel it makes the code more robust, but I think
its good that SOCI supports both.  (I believe early releases of JDBC
were criticized for supporting only bind by position).

> but in principle I have no
> objection to your "use" and "into".

That is very good to hear.  I think the use() and into() functions are
one of the cornerstones of SOCI, and help set it appart from other
db libraries.

>> 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.

Sure, why not? :-)

>> 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.

It's an interesting point.  Although the database domain may have some
specific details that I wouldn't want to see get covered up by an all in
one solution.  Column names are what comes to mind immediately.

> 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.

Tuples certainly have their appeal in this domain, and I don't see any
any reason why support for boost/tr1::tuple can't be added to SOCI.

However, since tuples lack associated field names, they cannot be used 
for name-based mapping, which has some advantages.  Consider a SOCI 
name-based o/r mapping:

template<> struct TypeConversion<Person>
{
  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");
     p.gender = v.get<std::string>("GENDER", "unknown");
     
     return p;
  }
  static Values to(Person &p)
  {
     Values v;
     v.set("ID", p.id);
     v.set("FIRST_NAME", p.firstName);
     v.set("LAST_NAME", p.lastName);
     v.set("GENDER", p.gender, p.gender.empty() ? eNull : eOK);
     return v;
  }
};

Now I can use my Person struct directly in a variety of queries:

Person p;

// this query is still safe if the dba adds new columns
sql << "select * from People", into(p);

// this table uses "num" insted of "id", so I use an sql alias via "as"
sql << "select num as ID, FIRST_NAME, LAST_NAME, GENDER from Readers", into(p); 

// here I want to insert just the last_name field into another table
sql << "insert into Customers values(:LAST_NAME)", use(p);

Steve