[std.database]

[Regan Heath]

On Wed, 12 Oct 2011 14:45:29 +0100, Andrei Alexandrescu  
<SeeWebsiteForEmail at erdani.org> wrote:

> On 10/12/11 2:36 AM, Jacob Carlborg wrote:
>> I usually prefer calling methods and functions instead of writing
>> everything in a string literal.
>
> I guess reasonable people may prefer differently. Someone who knows SQL  
> may prefer it instead of learning another API, with its own inevitable  
> quirks and limitations.
>
>> These forms might not make a big difference but when you use an object
>> oriented API I think it does.
>>
>> Take this Ruby example:
>>
>> Post.joins(:comments => :guest)
>>
>> Produces the following SQL:
>>
>> SELECT posts.* FROM posts
>> INNER JOIN comments ON comments.post_id = posts.id
>> INNER JOIN guests ON guests.comment_id = comments.id
>
> That's a good illustrative example. My understanding is that some setup  
> is needed in Ruby (defining the classes and associations) so the  
> one-liner doesn't tell the full story. One needs to learn a fair amount  
> of specifics to do that, whereas I know what your SQL query does today.  
> I'd write it in a terser manner like this:
>
> SELECT a.* FROM posts a
> JOIN comments b ON a.post_id = b.id
> JOIN guests c ON b.id = c.comment_id
>
> I read through http://guides.rubyonrails.org/association_basics.html  
> and, well, one way or another one needs to learn relational algebra to  
> work with it, be it in an SQL form or a Ruby form. One possible issue  
> is, what happens with parts of it that aren't properly covered. For  
> example, to take the difference between two sets, I'd use:
>
> SELECT a.* FROM table1 a
> LEFT OUTER JOIN table2 b ON a.id = b.id
> WHERE b.id IS NULL
>
> (There are a couple other alternatives depending on DBMS.) This is an  
> SQL classic, but I wouldn't know how to express that with the Ruby API.  
> And that doesn't stop here. Many interesting queries are just as terse  
> as they need in SQL and I fear would look convoluted when forced through  
> another API.

FWIW I'm with Andrei on this one.  At the very least one of the proposed  
layers should allow direct/explicit SQL.  That said, in my experience  
there are differences between SQL server 2000, 2005, and 2008, and also  
differences when you go via ODBC which mean some queries work and others  
fail.  In which case it would be enormously convenient to have a layer  
where "someone smart" has already figured out how to do thing X with each  
type of database, and the rest of us can just call methodX() to make use  
of it.  But, I reckon that layer can easily be built on top of a direct  
SQL layer, or a direct MYSQL layer, etc.

-- 
Using Opera's revolutionary email client: http://www.opera.com/mail/