Re: [boost] SOCI 2.2.0 - The C++ Database Access Library

From: Maciej Sobczak (prog_at_[hidden])
Date: 2006-12-05 17:05:44

Hi,

Edward Page wrote:

> I'm pretty much a lurker on this list that loves to watch what is new
> coming out and learn from the discussions.  A database library
> interests me a lot though, so for once, I have questions.
> 
> 1. Transactions
> 
> begin, commit, and rollback are provided.  I did not see any comments
> in the docs of code to help keep this exception safe.

Yes, we were lazy and the RAII wrapper for transactions somehow was not 
implemented. :-)
This will be certainly provided in the future versions.

> 2. Query Construction
> 
> I read the rationale for not having a query construction syntax.  To
> sum up my understanding of it: when working with DBAs it is good to
> have queries separate.  For some of us hobbyists or for prototyping it
> might be nice, especially so we do not have to worry about syntax
> issues that are minor and wouldn't show up till runtime.  If the query
> construction was built on top of taking strings then it would still
> allow for users to choose whether to use the query construction or to
> have the expressions stored in a file.  This converting it to a string
> could even be used to easily stream out all of the expressions to a
> file to ease transition from a compiled version to a runtime file
> version for the DBAs
> 
> One possibility is:
> sql << select("name", "salary").from(persons).where.("id = ",id),
>       into(name), into(salary);
> 
> Original (From webpage):
> sql << "select name, salary from persons where id = " << id,
>       into(name), into(salary);
> 
> The where clause could be templated to allow id to be whatever type
> (using stringstream or lexical_cast?) to provide simiilar behavior to
> the "<< id"

Actually, there is nothing that would prevent you from having this.
The operator<< in SOCI is just a template forwarder to a regular 
std::ostringstream object. This means that *whatever* you have that is 
streamable can go there. If you want to build your own framework of the 
query builders, go ahead, just make the result of it streamable. We just 
decided not to do this on our own, for the reasons that you already 
found in rationale.
Still, you can treat SOCI as extensible in this area. If you would like 
to write your own query builders, there is no obstacle. Just make their 
result streamable.

> Some improvements could include:
> *Setting up column objects:  It would take a column name and what
> table it is from.
> 1) Could serve as a precondtion: That the column does exist in the table
> 2) Generate the from clause for you
> For example:
> Column personsName ("name", "persons");
> Column personsSalary ("salary", "persons");
> 
> sql << select(personsName, personsSalary).where.("id = ",id),
>       into(name), into(salary);

I don't see much benefit in it. Think about multi-table joins or 
procedure calls.
Still, feel free to provide more arguments to support this idea. :-)

(And note that this would be more tricky with the current library.)

> *lambda like expressions for generating the where clause.  It could
> then be simplified
> ...
> Column personsId ("id", "persons");
> sql << select(personsName, personsSalary).where.(personsId == id),
>       into(name), into(salary);
> 
> With this, it could even be improved by tempaltizing the Columns for
> what type should be contained therein.  Then the where clause
> expression generator could compare the template type of "personId" to
> "id"

Similarly, this looks like overengineering to me.
Of course, I might just not see the opportunities that are there. Please 
provide more explanation for this example.

Note that the code can be made generic in the obvious way by wrapping 
the query in the template and having name and salary to be of some 
template type. Note that the main raison d'etre for SOCI was to make 
database programming as easy and the code as readable as possible. The 
above examples are somehow contrary to this motivation.

-- 
Maciej Sobczak : http://www.msobczak.com/
Programming    : http://www.msobczak.com/prog/