Re: [boost] Boost.SQL?

Subject: Re: [boost] Boost.SQL?
From: David Abrahams (dave_at_[hidden])
Date: 2010-09-15 21:30:48

On Sep 15, 2010, at 11:55 AM, Roland Bock wrote:

> On 09/15/2010 05:11 PM, Dave Abrahams wrote:
>> Here's a thread that might be interesting for anyone thinking of
>> implementing a DSEL for SQL:
>> http://markmail.org/message/rzcdlkquko2htvjp
>>   
> Thanks. Interesting read :-)
> 
> Taking one of the examples you gave in that thread:
> 
> // -----------------------------
> Entry.objects.filter(
>   _.headline.startswith('What'),
>   _.pub_date<datetime.now(),
>   _.pub_date>=datetime(2005, 1, 1)
> );
> // -----------------------------
> 
> Here's what I want to achieve (and my prototype is very close):
> 
> // -----------------------------
> [...] where(
>            t.headline.startswith("What")
> && t.pub_date < datetime.now(),
> && t.pub_date >= datetime(2005, 1, 1)
> );
> // -----------------------------
> 
> (t being an instance of the table class)
> 
> Admittedly, I write "where" instead of "filter", but other than that, this is pretty close, right? :-)

Yup.  And I'd have written it with && instead of , if I wasn't addressing a Python audience (can't overload && in Python).

>> I notice that most of this thread is going in the direction of trying
>> to replicate SQL syntax instead of doing something closer to normal
>> logic.  I'd be much happier to use a syntax that just specifies "what
>> I want" rather than "how to get there."  In some cases the library
>> could be responsible for conjuring up JOINs, for example, when that's
>> the most efficient approach.  
> 
> My current code automatically determines which tables you need for your query. Thus, the FROM part is not your responsibility. But no other magic is conjured up...
> 
> Do you have something more specific in mind regarding the syntax? Here is an example from http://sqlzoo.net/select_select
> 
> SELECT name, A.region
>  FROM
>     bbc AS A
>     JOIN
>     (SELECT region,MAX(population) AS maxpop FROM bbc GROUP BY region) AS B
>  ON (A.region=B.region AND A.population=maxpop)
> 
> 
> It selects the names of the population-wise biggest countries from each region.
> 
> How would you want to express that?

Jeez, now I have to re-learn SQL again! :-)

Let's see...

bbc is a table containing 

  name: string
  region: string
  population: integer

?

I'll have to give that a little thought.

--
David Abrahams
BoostPro Computing
http://boostpro.com