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

From: Nicola Musatti (Nicola.Musatti_at_[hidden])
Date: 2006-10-06 08:50:44

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