Re: [boost] SQL library with full embedded SQL-syntax

From: Jeff Garland (jeff_at_[hidden])
Date: 2007-03-17 13:35:34

Rene Rivera wrote:
> Thorsten Ottosen wrote:
>> Rene Rivera wrote:
>>> Sure... But we could use that same argument to propose that we write C++ 
>>> programs by writing an abstraction on top of C++ compilers in Python so 
>>> that we can remove the incompatibilities in the various C++ 
>>> implementations. 
>> I guess you could do that. Like C++, porting can be a major problem.
>>
>> Note, however, that is not an argument against having portible SQL DSL.
> 
> True :-) But it's an argument that a portable SQL DSL is not a great 
> advantage.

A couple things.  Some people don't really care about database portability. 
So in that case, they feel free to use every non-standard feature of the 
database they are using.  For these folks it's really hard to argue for 
anything other than a string interface because otherwise you'd have to emulate 
every feature of every each database -- basically impossible.  For people that 
want a portable database it would be an interesting first step to provide a 
portable string-based interface that supports basic queries and bindings.

BTW, Rogue Wave went down both of these paths 10 years ago with their DBTools 
library.  The first versions of the library *only* provided an extensive set 
of classes to build up sql commands -- it supports many database back ends 
seamlessly.  Later, they ended up adding a 'string like' interface.  I wasn't 
there, but I can only suppose that it was customer demand for things the 'safe 
library' didn't offer.

>>> Having had the displeasure of dealing with PL/SQL there 
>>> is one serious drawback to the language embedded approach, it ties ones 
>>> to the capabilities of the abstraction.
>> Right. The SQL library should provide a backdoor so you can still use
>> string queries if you need to be non-portable or cutting egde etc.
>>
>> I some of our company tools, we support multiple server backends: at 
>> least oracle, mssql and mysql. I'm surpised just how diffucult it is to
>> write portable SQL (even for very very simple queries).
>>
>> If you want to use different functions, there's a pretty good change 
>> they are different on different systems. I have end up writing my own 
>> abstractionlayer in php to compensate.
> 
> That drives into what I think is the core problem I have with an SQL 
> DSL. It doesn't provide an abstraction that has compelling advantages, 
> other than syntactic sugar. What I would find way more useful would be 
> an abstraction that removes the SQL language itself, i.e. a relational 
> database library. That would have some advantages:
> 
> * Better coherence with C++ data types.
> * Possibility to support non-SQL relational databases.
> * Make it easier to construct object/relational translation abstractions.

I still think the core has to be built on the more flexible string interface. 
        BTW, there was a relational template library for boost at one point 
that might provide this abstraction you're looking for.  But, no doubt in my 
mind that you are correct that the embedded DSL is a distraction from the real 
issues in this domain.

Jeff