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

From: Steve Hutton (shutton_at_[hidden])
Date: 2006-10-06 04:02:56

On 2006-10-06, Jeff Garland <jeff_at_[hidden]> wrote:
>
> ...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.  

Yes, exactly.

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

Interesting.  One point that the SOCI examples perhaps don't emphasize
fully: a single type conversion can be re-used with many different
queries, including queries against different tables or combinations of
tables. 

The only condition that needs to be satisfied is that the
column names returned by the query are a superset of the column names
defined in the TypeConversion.  Moreover, the column names don't have
to be literal column names, they can be alias via sql's "select <col> as
<alias>" syntax

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

I think it would be good practice to keep both queries and their related
TypeConversions in the same header file.  But of course even this isn't
enough to ensure queries don't break - the database itself can always
change :-)  But using select * is another precaution that gives some
added protection from database changes.

Steve