... a nested set tree implementation in the ORM from Rails.

Would a nested comments implementation be adequate?

Here is the DB Schema:

  create table comments (\n    id int not null auto_increment,\n    content text not null,\n    parent_id int,\n    updated_on date,\n    primary key(id)\n  )

Here is the ActiveRecord model object

class Comment < ActiveRecord::Base\n  acts_as_tree :order=>"updated_on"\n\n  def nested_display(level=0)\n    print "  " * level\n    puts "#{content} (#{updated_on})"\n    children.each do |child| child.nested_display(level + 1) end\n  end\nend

Here is a script to populate a few comments:

require 'active_record'\nrequire 'comments'\n\nActiveRecord::Base.establish_connection(\n  :adapter  => "mysql",\n  :database => "comments_development",\n  :socket   => "/var/run/mysqld/mysqld.sock",\n  :username => "jim"\n)\n\nroot_comment = Comment.create(:content => "This is great!")\ndisagree     = Comment.create(:content => "No it isn't.",      :parent => root_comment)\nargue        = Comment.create(:content => "You're nuts.",      :parent => disagree)\nagree        = Comment.create(:content => "Right on Brother.", :parent => root_comment)

And here is a script to dump the comments (I'll omit the connection code this time, which is the same as the populate script above).

# [... connection code elided ...]\nComment.roots.each do |comment| comment.nested_display end

And finally, the output of the dump script against a real database.

$ ruby dump.rb\nThis is great! (2005-11-29)\n  No it isn't. (2005-11-29)\n    You're nuts. (2005-11-29)\n  Right on Brother. (2005-11-29)

that's not an implementation of nested set trees

Sorry, I missed the 'nested set' portion of the request. What I supplied was an adjacency list implementation (according to the link you sent ... thanks BTW).

But then I don't see how you're even getting the children for the "each" in nested_display.

The acts_as_tree method defines a bunch of methods in your model object, including children, siblings, self_and_siblings, and ancestors. It also defines a roots method in the class object.

Theoretically, you would just create a acts_as_nested_set method that could create all the same methods, but using the nested set implementation. I'll have to review your linked article in more detail before I could say how easy that might be.

Of interest will be the code to insert/remove children, since that operaton would be prohibitively expensive if it required a SQL call for each child being modified.

So I see. It looks as if this is more of a "can the database support this" question than an ActiveRecord question. You can specify arbitrary SQL to be run on any given insert/update/delete action on ActiveRecord, so its just a matter of getting the SQL right.

But not all database handle those fancy SQL routines, right? (As I disclaimed in my other message, I am not a DB expert). Sothen it becomes a DB portability issue.

Is that the kind of answer you are looking for? (I'm willing to play with this idea, but since this will be more than a two minute demo, I want to make sure I'm addressing your concerns).

I'm curious about how you'd respond to that.

Keep in mind I am not a DB expert nor a heavy-duty rails user, so take what I say with a grain of salt.

In a lot of ways I see the whole debate as a matter of control, and which side of the debate you fall on depends on where you exert the most control.

Database owners (DBAs and schema designers) want the rules applied at the lowest possible level. Allowing the rules to be enforced at the application level scares them because that means /every/ application needs to follow the rules. Since the apps can be written in any language using any framework, the only way to provide consistent rules is at the DB level.

Application developers have different perspective, particularly open source authors or anyone writing against multiple databases. Since /any/ database could be used by the application, moving the rules out of the application means that that they can't depend on the rules being enforced at all. Different databases have different capabilities for rule enforment. So if you want your application to run on anything from Sqlite to the top end Oracle server, the rules must be in the application.

So where do you put the rules? David HH (the author of Rails) suggests that for single point of entry database with a single app (or at least a single framework), having the rules in the app code is not a bad idea. However, he doesn't disagree with the rules in the DB approach in enterprise DBs.

Robby Russel (a big Rails user and owner of Planet Argon, a Rails friendly ISP) is a big fan of putting as many rules as possible in the DB, and he does so in all of his DB applications. Of course, he sticks primarily to Postgresql, so he doesn't have to deal with the cross database platform problems.

So there you have it, Rails is able to support either approach.

Where do I stand? Putting all the rules in the DB only seems to leave the app with only an anemic ability to validate. Putting the rules in the app only leaves the DB without the information it needs to help. I would put it in /both/ places, but express the rules at a single point, probably using some kind of domain specific language that could easily be translated to whatever database or application language needed it. (You can look here [link|http://onestepback.org/articles/lingo|http://onestepback.org/articles/lingo] to guess what I would use to create the DSL).