Re: [boost] Boost.SQL?

Subject: Re: [boost] Boost.SQL?
From: Roland Bock (rbock_at_[hidden])
Date: 2010-09-16 02:39:58

On 09/16/2010 03:30 AM, David Abrahams wrote:
> 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
>
> ?
>    
Right. Now I want a list/vector/range/whatever allowing me to iterate 
over name/region pairs containing the population-wise biggest countries 
from the respective region.

The SQL approach is to wrap two filtering actions into one

1) Create a list of

struct B

    region: string
    maxpop: integer // population of the biggest country of this region

2) Create a list of

struct Record

    name: string
    region: string

in such a way that each record fulfills the following criteria

   1. name/region are from the same entry of the bbc table
   2. the region/population values of that record correspond to the
      region/maxpop values of the B-List from step 1

My current concept (not written in code yet) does the following

a) Define a pseudo-table containing the logic of SQL-step 1:

// ---------------------------------------------------
class B
{
public:

    // typedefs
    typedef max<bbc::_population, maxpop_alias> _maxpop
    typedef [...] _region

    // public members
    [...] maxpop
    [...] region

private:

    // internal logic
    [...] select<_region, _maxpop>()

        << group_by(region);

} b;
// ---------------------------------------------------

b) Define the structure of the results you want:

// ---------------------------------------------------
typedef record<bbc, bbc::_name, bbc::_region> my_record;
// ---------------------------------------------------

Essentially this creates a struct which looks like this:

// ---------------------------------------------------
struct my_record
{

    string name;
    string region;

    (some misc stuff)

};
// ---------------------------------------------------

c) Obtain the results:

// ---------------------------------------------------
vector<record> results = select<my_record>()

    << where(bbc.region == b.region && bbc.population == b.maxpop);

// ---------------------------------------------------

> I'll have to give that a little thought.
>    

Looking forward to reading the results :-)

Regards,

Roland