|
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 |
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] |
|
|
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] |
|
|
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
|
| [reply] |
|
|
| [reply] |
|
|
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
|
| [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
|
| [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.
| [reply] |
|
|
