Re: Database Accessor Classes

[samtregar]

Class::DBI is worth a look, for design inspiration if not for direct use. It's use of column classes, particularly the Essential class, is aimed at solving some of the problems you're concerned with. You designate a set of columns as "essential" and they're the ones that are loaded every time you load an object from that class. Other fields, like the big ones that are edited rarely, are demand-loaded only when you access them.

That said, Class::DBI is not a very efficient database user, so if performance is something you need it's not likely to be a good fit. I've heard good things about Rose::DB, particularly in terms of performance, although I can't speak from experience.

-sam

[ryantate]

Class::DBI is not a very efficient database user

More specifically, CDBI can make a lot of database calls in cases where the programmer is trying to write a fast-and-dirty prototype, does not want to write custom SQL or is naive on how CDBI works.

In particular, CDBI does not do three-way joins to load objects related through many-to-many relationships. Such operations are inefficient in CDBI without custom SQL.

For example: If an actor has_many movies and a movie has_many actors, via a jump table actor_movie, and you do $actor->movies, this is one SQL execution in the database. But that only gets you the id of each movie. If you want to get the title for each of those movies, that's one SQL execution *per movie object*, which can be many db hits.

Other ORMs like Ruby's ActiveRecord can be made to easily do three-way joins in this situation, which cuts the SQL execution back to just one statement and one resultset.

CDBI internals are being redone and easy three-way-joins are supposed to happen eventually via a plugin, but as far as I know this has not yet happened. There is Class::DBI::Sweet, but even its authors consider it far enough from mainstream CDBI to be too funky to have a big future.

They advocate using DBIx::Class, but the docs there just don't do it for me yet. Not clear and simple enough -- module soup.

[perrin]

Rose::DB::Object has had this feature for ages.