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

From: Jeff Garland (jeff_at_[hidden])
Date: 2006-10-08 12:02:17

Steve Hutton wrote:
> On 2006-10-07, Nicola Musatti <Nicola.Musatti_at_[hidden]> wrote:
>> Peter Dimov wrote:
>>> Nicola Musatti wrote:
>>>
>>>> On the other hand if I'm using a dynamic representation that only
>>>> relies 
>>>> on a limited subset of the fields involved in the query I'd use '*' so
>>>> as to limit the conditions that could break my code. In similar cases
>>>> I wouldn't rely on field order but rather on matching by name.
>>> Why not simply select the limited subset on which you rely?
>> You're right. Actually the only case where it's meaningful to use 
>> 'select *' is when you don't know which columns you're looking for or 
>> you don't care. That is, you're either exploring the data or writing a 
>> context independent tool.
> 
> Well, back to Jeff's point about how it would be nice if the
> specification of the column names in the query was somehow validated
> against the the column names in the o/r mapping at compile time,
> to find any disconnects.

Well, actually I wasn't looking for direct validation against the database,
but locality of mapping data.  A tool that does this sort of validation would 
make a good library example though. Anyway, if I'm maintaining a type that is 
database enabled there are several types of possible changes:

1) add/remove a field/data member
2) change a field/data member type
3) change a field/data member name

Now there's an infinite number of solutions to the mapping problem.  Many
approaches maintain the member/mapping information outside of the code in
configuration.  That's fine, but personally, I usually prefer to have a way to
maintain this information directly in the code -- the main reason being that
most of the changes above require modification of the code.  So when I make
any of these modifications, ideally I want to go to one file, make the change,
recompile, update the schema if needed, and move on. If there's another file,
that's a non-local change that someone has to go find and make.

Currently I think SOCI is flexible enough to allow the mapping either way.
But I think it would be nice to explore how the code centric approach would
really work -- hence my prior comments.

Following up from the prior example, there's a couple of ways the mapping
could be maintained in the code.  For example, one immediate issue with the OR
mapping is that the mapping has to be repeated.  Here's the snippet:

template<> struct TypeConversion<Person>
{
     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");
         /....

     static Values to(Person &p)
     {
         Values v;
         v.set("ID", p.id);
         v.set("FIRST_NAME", p.firstName);
         v.set("LAST_NAME", p.lastName);
         //...

So changes of type #1 and #3 require up to 3 changes: 2 in the above mapping
code, and 1 to the query.  If "select *" is used the query then the third
change can be avoided.

But I think the interesting question we should be asking is, what if we create
  a registration system that can simplify and clarify the mapping:

template<> struct TypeRegistration<Person>
{
      std::string base_query()
      {
        return "select FIRST_NAME, LAST_NAME, ID from PersonTable";
      }

     static void register_type(std::multimap<string, ...>& registry)
     {
         //pseudo code here...
        registry.insert("Person", "PersonTable.FIRST_NAME");
        registry.insert("Person", "PersonTable.LAST_NAME");
         //...
     }

}

No obviously this loses the ability to write a function to do field specific 
conversions.  This might be overcome by making the registry information more 
sophisticated.  It still suffers from requiring 2 updates for several of the 
scenarios, but they are local.  A more sophisticated approach could actually 
build the base_query.

Anyway, I think this is all a layer on the SOCI core...

> It's an interesting idea (might require full SQL parser to achieve?)

I think this is overkill.

> What I was trying to point out is that the use of select * and name 
> based mapping is one way to eliminate the posibility of such disconnects.

Like many design decisions, it has some advantages and some disadvantages.

Jeff