Re: Database applications and OOness

I've build an implementation of Martin Fowler's ActiveRecord pattern  
[1] with support for single-table inheritance [2]. It's designed with  
the notion of "Convention Over Configuration", which basically means  
that you follow a set of naming conventions to get a bunch of behavior  
for free. Best to show with an example:

CREATE TABLE posts (
   id int(11) unsigned NOT NULL auto_increment,
   title varchar(255),
   body text,
   posted_on datetime,
   PRIMARY KEY  (id)
)

CREATE TABLE comments (
   id int(11) unsigned NOT NULL auto_increment,
   post_id int(11) unsigned NOT NULL,
   body text,
   PRIMARY KEY  (id)
)

class Post < ActiveRecord
     def comments
	    Comments.find_all "post_id = #{id}"
     end

     protected
	    def before_create
		    self.posted_on = Time.now
	    end

	    def before_destroy
		    Comments.destroy_all "post_id = #{id}"
	    end
end

class Comment < ActiveRecord
end

The code above shows a couple of things. First, there's no build-in  
mapping for foreign key object relations (I had to make Post#comments  
myself). Second, much of the behavior of the ActiveRecord can be  
customized through callback hooks, like before_create (which tellingly  
is called just before a row is created in the database) and  
before_destroy. Third, "Post" automatically match up with the "posts"  
table -- this behavior can be overwritten for things like  
Person/people.

Anyway, some usage examples:

post = Post.new
post.title = "My new post"
post.body  = "Active Records are easy"
post.save # since the post has no id, a INSERT-sql statement is executed

print post.id # prints the auto_id assigned by MySQL
print post.posted_on # prints the time of creation

post.title = "I've changed"
post.save # an id exists, so an UPDATE-sql statement is executed

post.attribute_that_doesnt_exist # Throws a NameError exception

# Objects can also be initialized with a hash, which is especially  
convenient
# in web-apps that can just pass on the POST-parameters
first_comment = Comment.new({ "body" => "hello world", "post_id" =>  
post.id })
first_comment.save

second_comment = Comment.new({ "body" => "hello world", "post_id" =>  
post.id })
second_comment.save

print post.comments.length # prints 2
post.comments[0].body == first_comment.body # true

...and then there's a wide range of class/table-level methods, like:

Comment.update_all "body = 'all are changed'"
Comment.find(1).body == Comment.find(2).body # true (find uses SELECT,  
btw)

print Comment.count # prints 2

post_with_comment = Post.find_by_sql(
     "SELECT post.*, comment.body as comment_body " +
     "FROM posts LEFT JOIN comments ON posts.id = comments.post_id " +
     "ORDER BY comments.id DESC"
)

post_with_comment.comment_by # will print the body of the last comment

I think that's the basics. On top of that, there's support for simple  
inheritance, like Company < ActiveRecord, Firm < Company, Client <  
Company, which does correct type instantiations. There's also  
validation support with error reporting -- think automatic form  
validation from Strut's ActionForm[3] classes but on the model instead  
of controller.

Caveats:
* Id generation is tied to MySQL's autoID functionality, which  
currently binds it to MySQL -- but through use of DBI.
* No build-in transaction support

I've used a similar design in a couple of PHP web-apps and it has  
worked very well. I'm currently finishing up a Ruby web-app called  
Basecamp for 37signals[4] that uses this all the way through.

I plan on releasing this as soon as I've wrapped up Basecamp, but if  
anyone is interested now (which means little documentation besides  
inline comments and unit tests), I could easily package up a version.

[1] http://www.martinfowler.com/eaaCatalog/activeRecord.html
[2] http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html
[3]  
http://jakarta.apache.org/struts/userGuide/ 
building_controller.html#action_form_classes
[4] Sneak peak:  
http://www.37signals.com/svn/archives/000505.php?29#comments
--
David Heinemeier Hansson.
http://www.loudthinking.com/ -- Broadcasting Brain