Am 12.10.2011, 09:02 Uhr, schrieb Jacob Carlborg <doob@me.com>:

> On 2011-10-12 02:43, Johann MacDonagh wrote:
>> On 10/11/2011 5:31 PM, Andrei Alexandrescu wrote:
>>> On 10/11/11 3:05 PM, Jacob Carlborg wrote:
>>>> If we're talking use cases and high level interfaces I would go with
>>>> something like:
>>> [snip]
>>>> I recommend that everyone take a good look at ActiveRecord in Ruby on
>>>> Rails:
>>>>
>>>> http://guides.rubyonrails.org/active_record_querying.html
>>>> http://guides.rubyonrails.org/association_basics.html
>>>> http://guides.rubyonrails.org/active_record_validations_callbacks.html
>>>
>>> I confess the example you gave looks very foreign to me. From consulting
>>> http://guides.rubyonrails.org/active_record_querying.html, I see Ruby's
>>> active records esentially recode relational algebra in Ruby (as for the
>>> constructs the equivalent SQL is shown).
>>>
>>> For a variety of reasons, this would be tenuous in D. One simple reason
>>> is that e.g. lambdas don't offer access to textual representation, which
>>> would be necessary to translate lambda-based conditions into SQL text.
>>>
>>> I might be narrow-minded, but I thought we're still looking at writing
>>> and executing good old SQL code.
>>>
>>>
>>> Andrei
>>>
>>
>> We should always allow the user to get down to the nitty-gritty and
>> write good ol' SQL statements and execute them. However, writing all
>> that plumbing code gets old very quickly, so if we can provide some
>> clean and simple frameworks on top of that, users would appreciate it.
>
> Yes, exactly. The point of having several layers built on top of each other is that everyone is free to choose on which layer they want to interact with the database.
>
> If someone is comfortable with interacting with the database in an object oriented API they're free to do that. If they're instead more comfortable in executing raw SQL, then that's possible as well.

It's not like there really is a choice. You just cannot do everything with an interface built around objects/structs. But I would use that interface most of the time. Ranges for result sets and structs for rows are a very natural representation of a SQL query.

Under that aspect, table associations would also be naturally turned into pointers. So when a "thread" table refers to the starter through its user id, then the thread object would have a pointer to a user object instead of just an integer. This makes it easy for example to just fetch a thread from the DB and write "thread.starter.name".
http://docs.jboss.org/hibernate/core/3.3/reference/en/html/performance.html#performance-fetching

On 2011-10-12 09:05, Jonathan M Davis wrote:
> On Wednesday, October 12, 2011 08:57:54 Jacob Carlborg wrote:
>> On 2011-10-11 23:31, Andrei Alexandrescu wrote:
>>> On 10/11/11 3:05 PM, Jacob Carlborg wrote:
>>>> If we're talking use cases and high level interfaces I would go with
>>>
>>>> something like:
>>> [snip]
>>>
>>>> I recommend that everyone take a good look at ActiveRecord in Ruby on
>>>> Rails:
>>>>
>>>> http://guides.rubyonrails.org/active_record_querying.html
>>>> http://guides.rubyonrails.org/association_basics.html
>>>> http://guides.rubyonrails.org/active_record_validations_callbacks.html
>>>
>>> I confess the example you gave looks very foreign to me. From consulting
>>> http://guides.rubyonrails.org/active_record_querying.html, I see Ruby's
>>> active records esentially recode relational algebra in Ruby (as for the
>>> constructs the equivalent SQL is shown).
>>
>> Yes, exactly. The point is to have as much as possible in functions
>> instead of string literals and have a more Ruby like API than an SQL
>> looking API.
>>
>> connection.select("*").from("users");
>>
>> instead of
>>
>> connection.execute("select * from users")
>
> I don't know what all of the pros and cons are, since I'm not all that
> experienced with DB stuff, but on the surface at least, from the perspective of
> usability, I don't see anything better about the first one than the second one.
>
> - Jonathan M Davis

I usually prefer calling methods and functions instead of writing everything in a string literal.

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

-- 
/Jacob Carlborg

On 2011-10-12 09:34, Marco Leise wrote:
> Am 12.10.2011, 09:02 Uhr, schrieb Jacob Carlborg <doob@me.com>:
>
>> On 2011-10-12 02:43, Johann MacDonagh wrote:
>>> On 10/11/2011 5:31 PM, Andrei Alexandrescu wrote:
>>>> On 10/11/11 3:05 PM, Jacob Carlborg wrote:
>>>>> If we're talking use cases and high level interfaces I would go with
>>>>> something like:
>>>> [snip]
>>>>> I recommend that everyone take a good look at ActiveRecord in Ruby on
>>>>> Rails:
>>>>>
>>>>> http://guides.rubyonrails.org/active_record_querying.html
>>>>> http://guides.rubyonrails.org/association_basics.html
>>>>> http://guides.rubyonrails.org/active_record_validations_callbacks.html
>>>>
>>>> I confess the example you gave looks very foreign to me. From
>>>> consulting
>>>> http://guides.rubyonrails.org/active_record_querying.html, I see Ruby's
>>>> active records esentially recode relational algebra in Ruby (as for the
>>>> constructs the equivalent SQL is shown).
>>>>
>>>> For a variety of reasons, this would be tenuous in D. One simple reason
>>>> is that e.g. lambdas don't offer access to textual representation,
>>>> which
>>>> would be necessary to translate lambda-based conditions into SQL text.
>>>>
>>>> I might be narrow-minded, but I thought we're still looking at writing
>>>> and executing good old SQL code.
>>>>
>>>>
>>>> Andrei
>>>>
>>>
>>> We should always allow the user to get down to the nitty-gritty and
>>> write good ol' SQL statements and execute them. However, writing all
>>> that plumbing code gets old very quickly, so if we can provide some
>>> clean and simple frameworks on top of that, users would appreciate it.
>>
>> Yes, exactly. The point of having several layers built on top of each
>> other is that everyone is free to choose on which layer they want to
>> interact with the database.
>>
>> If someone is comfortable with interacting with the database in an
>> object oriented API they're free to do that. If they're instead more
>> comfortable in executing raw SQL, then that's possible as well.
>
> It's not like there really is a choice. You just cannot do everything
> with an interface built around objects/structs. But I would use that
> interface most of the time. Ranges for result sets and structs for rows
> are a very natural representation of a SQL query.
>
> Under that aspect, table associations would also be naturally turned
> into pointers. So when a "thread" table refers to the starter through
> its user id, then the thread object would have a pointer to a user
> object instead of just an integer. This makes it easy for example to
> just fetch a thread from the DB and write "thread.starter.name".
> http://docs.jboss.org/hibernate/core/3.3/reference/en/html/performance.html#performance-fetching

Yes, this is what ActiveRecord does as well:

http://guides.rubyonrails.org/association_basics.html
http://guides.rubyonrails.org/active_record_querying.html

-- 
/Jacob Carlborg

Am 09.10.2011 23:53, schrieb Andrei Alexandrescu:
> On 10/09/11 13:22, Andrei Alexandrescu wrote:
>> On 10/9/11 11:40 AM, Steve Teale wrote:
>>> Further generic question. (Yes, I am listening to the answers too)
>>>
>>> If some underlying databases don't support the features that our chosen
>>> interface requires, do we attempt to synthesize them - presumably at
>>> cost
>>> to performance, or do we just throw a compile-time exception that
>>> basically tells the user to use a lower interface and code it themself?
>>
>> No.
>>
>> Andrei
>
> Sorry, that was awfully unclear. I meant to say the driver shouldn't do
> little miracles in adapting support from one engine to the next. It's a
> losing race.
>
> It should be fine if certain queries or API calls fail either statically
> or dynamically.
>
>
> Andrei

What about things like prepared statements?
It's really convenient to have - even if the DB does not support it and thus no performance may be expected, it could still be used to prevent SQL-injections (if the userspace-emulation is implemented properly).

I don't know of any SQL-DB that does not support prepared statements, but maybe they exist.
If not there may be similar widely used and very convenient features not supported by a few DBs that could be considered to be emulated.

I do agree however that this shouldn't be done for every feature, but just for very few that are worth it.

Cheers,
- Daniel

On 10/12/11 6:42 AM, Daniel Gibson wrote:
>> Sorry, that was awfully unclear. I meant to say the driver shouldn't do
>> little miracles in adapting support from one engine to the next. It's a
>> losing race.
>>
>> It should be fine if certain queries or API calls fail either statically
>> or dynamically.
>>
>>
>> Andrei
>
> What about things like prepared statements?

I agree with you that prepared statements are not little miracles.

Andrei

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.


Andrei

On Wed, 12 Oct 2011 14:45:29 +0100, Andrei Alexandrescu <SeeWebsiteForEmail@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/

On 2011-10-12 15:45, Andrei Alexandrescu 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:

Yes, exactly. But everything depends on what you want to do and what your application does. The thing is that your application doesn't have to do much before you want to start using class and objects to implement it. And if a framework can load a SQL result into a set of objects, what's wrong with that.

> 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

I think you would have to put the join in a string, like this:

Table1.joins("LEFT OUTER JOIN table2 b ON a.id = b.id").where("b.id IS NULL")

As the link says:

Using Array/Hash of Named Associations
This method only works with INNER JOIN.

But I don't see why it wouldn't be possible to to something like this:

Table1.left_outer_join(:table2).where("b.id IS NULL")

At least for this example.

> (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.
>
>
> Andrei

Yes, you would need to execute raw SQL, or parts of a query in raw SQL, as the join example above shows.

An ORM API is not limited to an SQL database, it can be used for other things as well. ActiveResouce provides similar API to ActiveRecord but connects to a RESTful API instead of a database.

http://api.rubyonrails.org/classes/ActiveResource/Base.html

It's probably possible to adapt an ORM API to have a NoSQL backend as well.

I don't know how you code your applications but I usually code mine in an object oriented way. SQL and object orientation don't fit really good together. Because of that it's nice to have an ORM layer that hides the ugliness of converting SQL results to objects and I can use the objects just as they where created like "regular" objects with no database connection.

-- 
/Jacob Carlborg

On 2011-10-12 16:33, Regan Heath wrote:
> 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.

As I've said in other posts, the ORM API would be the highest level. It would still be possible to access the middle layer which executes raw SQL and the lower driver level which is specific to each database. The ORM layer would be built on top of the middle layer. The middle level would be built on top of the lower driver level.

With ActiveRecord you can execute raw SQL using:

User.connection.execute("sql")

Yeah, it's really nice to have an API that can figure out these little differences between the different SQL implementation and make it work across many databases.

-- 
/Jacob Carlborg

On Wed, 12 Oct 2011 09:36:47 +0200, Jacob Carlborg wrote:

> On 2011-10-12 09:05, Jonathan M Davis wrote:
>> On Wednesday, October 12, 2011 08:57:54 Jacob Carlborg wrote:
>>> On 2011-10-11 23:31, Andrei Alexandrescu wrote:
>>>> On 10/11/11 3:05 PM, Jacob Carlborg wrote:
>>>>> If we're talking use cases and high level interfaces I would go with
>>>>
>>>>> something like:
>>>> [snip]
>>>>
>>>>> I recommend that everyone take a good look at ActiveRecord in Ruby on Rails:
>>>>>
>>>>> http://guides.rubyonrails.org/active_record_querying.html http://guides.rubyonrails.org/association_basics.html http://guides.rubyonrails.org/
active_record_validations_callbacks.html
>>>>
>>>> I confess the example you gave looks very foreign to me. From consulting http://guides.rubyonrails.org/active_record_querying.html, I see Ruby's active records esentially recode relational algebra in Ruby (as for the constructs the equivalent SQL is shown).
>>>
>>> Yes, exactly. The point is to have as much as possible in functions instead of string literals and have a more Ruby like API than an SQL looking API.
>>>
>>> connection.select("*").from("users");
>>>
>>> instead of
>>>
>>> connection.execute("select * from users")
>>
>> I don't know what all of the pros and cons are, since I'm not all that experienced with DB stuff, but on the surface at least, from the perspective of usability, I don't see anything better about the first one than the second one.
>>
>> - Jonathan M Davis
> 
> I usually prefer calling methods and functions instead of writing everything in a string literal.
> 
> 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

But you'd never guess!