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