[development] SQL abstraction, was: Do not let postgresql hold back great patches

[Darrel O'Pry]

adrian rossouw wrote:
>
> On 03 Dec 2007, at 6:38 AM, Larry Garfield wrote:
>
>>
>> #3 is what I'm pushing for in the D7 database overhaul, within 
>> reason.  How to 
>>
>> abstract everything without making the syntax insane or the performance 
>>
>> terrible is what I'm currently trying to wrap my head around.  The 
>> biggest 
>>
>> problem is field type handling, which I want to keep away from module 
>>
>> developers but varies greatly depending on the database.
>>
> and the biggest issue here is date / time types.
>
> it's fscking crazy how different these are between databases.

I could have swore that some where I mentioned  creating  query 
constructs with the syntatical properties of SQL statements and mapping 
them to phrases...
Now that I have my old servers back in hand the idea might be found at 
http://darrelopry.darkstyles.doesntexist.com/node/63... While that was 
specifically geared toward the idea of schema... I'm sure the idea of 
mapping syntactical and grammatical abstractions to phrases can be 
applied to queries themselves... and with a little introspection into 
the query construct individual database engines could try to output 
optimized queries to their databases.

see also mentions of this concept in machine translation projects aka 
food for thought:
http://www.linguistics.pitt.edu/news-events/flyers/Aranovich.pdf
http://books.google.com/books?id=SZwjb_56JzkC&pg=PA118&lpg=PA118&dq=language+syntactical+grammatical+abstraction+translation&source=web&ots=c1YffE48ZK&sig=ftDJQ7KZbz8Pr2wb3Rxp3noA-hI
http://publications.csail.mit.edu/abstracts/abstracts05/jsylee/jsylee.html

The analysis, transfer, generation approach seems to be pretty common in 
machine translation projects. Which is technically what we're doing. 
There  are of corners that can be cut since SQL is limited in scope and 
has a strongly defined structure compared to natural language. We can 
skip the analysis part, since we as developers are doing the analysis to 
create the queries, the query construct is the transfer/abstraction of 
the syntactical structure of the query and the generation is the 
responsibility of the database specific abstraction layer. 

just one approach I like... it may be way overkill...