Database Accessor Classes

jbeninger has asked for the wisdom of the Perl Monks concerning the following question:

Great and Almighty Perl Monks, hear my plea.

I'm designing my billionth (approximate) web app, and have finally decided to use a philosophy other than "willy-nilly" for my data objects. I was hoping one of the bright minds here may be able to point me in the direction of some good design patterns, philosophies, articles, or google search terms to help me out.

The problem in a nutshell: What's the best way to make an efficient database accessor class? You know - one that's generic enough that it can be reused, but specific enough that it's not writing entire 1M binary fields on every set...() call. And one that's well-encapsulated, so if I hand it to a coworker, they'll be able to make an educated guess as to when the database read/writes are actually happening.

E.g:
I've got a User class that grabs the user from the database. 99% of the time all I need are the username, password, and first/last name. The other 1%, I'm changing the user's image, or profile (large fields). Loading these last fields can be a drain on resources if I'm just printing out a list of user names, but need to be readily accessible at other times.

How does your design philosophy say I should treat this, so I'm only loading the large fields when they're required?

I figure this has got to be a very common problem, and have a number of homegrown solutions. With such a large group of experts at my fingertips, though, I'm sure there are solutions out there that put mine to shame. Any links you could provide would be greatly appreciated.

Cheers,
James Beninger

Comment on Database Accessor Classes

Replies are listed 'Best First'.
Re: Database Accessor Classes by samtregar (Abbot) on Mar 06, 2006 at 16:47 UTC
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	[reply]
Re^2: Database Accessor Classes by ryantate (Friar) on Mar 06, 2006 at 23:47 UTC
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.	[reply]
Re^3: Database Accessor Classes by perrin (Chancellor) on Mar 11, 2006 at 07:22 UTC
Rose::DB::Object has had this feature for ages.	[reply]
Re: Database Accessor Classes by davorg (Chancellor) on Mar 06, 2006 at 16:44 UTC
Have you looked at things like Class::DBI or DBIx::Class. -- <http://dave.org.uk> "The first rule of Perl club is you do not talk about Perl club." -- Chip Salzenberg	[reply]
Re^2: Database Accessor Classes by xdg (Monsignor) on Mar 06, 2006 at 16:49 UTC
Or, if you like the bleeding edge, there's Jifty::DBI -xdg Code written by xdg and posted on PerlMonks is public domain. It is provided as is with no warranties, express or implied, of any kind. Posted code may not have been tested. Use of posted code is at your own risk.	[reply]
Re^2: Database Accessor Classes by srdst13 (Pilgrim) on Mar 07, 2006 at 14:45 UTC
And don't forget Rose::DB::Object which includes support for lazy loading, caching, and, if memory serves, is the fastest of the DBIx::Class, Class::DBI, and Rose::DB::Object. As an added bonus, there is excellent documentation and Rose::HTML::Object that plays nicely with DB::Objects (although the two are orthogonal and are fine when used separately). Sean	[reply]
Re: Database Accessor Classes by tphyahoo (Vicar) on Mar 06, 2006 at 17:54 UTC
You may want to have a look at A brief survey of the DBI usability layer modules on the CPAN for various pros and cons.	[reply]
Re: Database Accessor Classes by hv (Prior) on Mar 06, 2006 at 17:40 UTC
My own homegrown solution allows me to specify 'defer' on selected non-key fields; the database abstraction arranges that deferred fields are not fetched from the database until an accessor for one of the deferred fields is called, at which point all the deferred fields for that record are fetched. This approach is available only for keyed tables, since the deferred fields are fetched using the (already present) key fields. I find this approach easy to use and quite predictable. The only caveat is that care must be taken a) in choosing which fields to defer, and b) in applications that handle collections of records that may look at deferred fields in each of those records, since I'm doing an additional SELECT for each record that needs access to the deferred fields. Because of (b), I also have a nasty hack I use to override this behaviour for selected classes in the occasional script that knows it needs access to deferred fields in all of the records of a collection. I've kept it as a nasty hack to deter overuse, but that's probably the wrong way to do it. (FWIW, the hack is currently used by 2 out of around 120 scripts in the application.) Hugo	[reply]
Re: Database Accessor Classes by jeremyh (Beadle) on Mar 06, 2006 at 17:55 UTC
What about making default behavior to return all fields, but having an optional array ref parameter for "fields". If the fields param is passed then only those fields are returned.	[reply]
Re: Database Accessor Classes by nmerriweather (Friar) on Mar 06, 2006 at 19:15 UTC
re: updates in my homegrown solution i do this: all data goes into _PROFILE into one of 3 structures: _text , _array , _hash when i update an object, information goes into _SUBMITTED , into the same structure i compare _SUBMITTED to _PROFILE. if a field is different, it gets tossed into an update clause once the db commits, _SUBMITTED fields are copried to _PROFILE i also cache/session all objects based on _PROFILE, not on the class. this way i can use multiple languages to handle the same objects , and get a bit more flexibility re: loads a_ create a 'listing' class that just loads the info you want b_ load everything at once, store in memcached w/a timeout.	[reply]
Re: Database Accessor Classes by nothingmuch (Priest) on Mar 09, 2006 at 08:34 UTC
Most object relational mapper thingamabobs allow you to specify which fields are loaded in the begining, and which fields are lazily fetched. The basic idea is that the user class is asked for an object with id $whatever, and then that class uses the ORM goodness to, at the very least, check if that id matches the primary key fields of the table it's mapping. In this query it will also fetch all the fields which it has been told to, and can even pre-fetch relationships (Class::DBI doesn't support this, but DBIx::Class does). Whenever you ask the object for not-yet-ready field it will make another SQL statement, and fetch that field as well. -nuffin zz zZ Z Z #!perl	[reply]