[CDBI] Why does CDBI not populate the object on insert

Michael G Schwern schwern at pobox.com
Fri Mar 6 00:10:10 GMT 2009

Bill Moseley wrote:
> On Thu, Mar 05, 2009 at 02:15:40PM -0800, Michael G Schwern wrote:
>>>>     my $artist = Artist->insert({ name => 'Artist Name' });
>>>> then $artist->name doesn't exist yet in the object. That means, of
>>>> course, that $artist->name must do another fetch of the database.
>> Well, keep in mind that it's not *another* fetch of the database as no fetch
>> should have been done by that point unless it's to get the auto incremented
>> primary key which is cheap.
> Ya, but my point of course is if I just defined the artist name
> I probably don't need to fetch it back from the database.
>>> That's exactly the reason. Triggers (both the database kind and possibly the
>>> CDBI kind) might change the record values after it is inserted into the
>>> database. The primary key columns should be intact, I believe, but the other
>>> columns are flushed and all "essential" columns reloaded upon access.
>> Exactly right.  Don't forget about defaults and truncation by type.  For
>> example, DECIMAL(6,2) or CHAR(2).  It's more common than you'd think.
> Yes, although I'd be hard pressed to think of a time when:
>     $foo->value( $new_value );
>     $foo->update;
>     $foo->value ne $new_value;

    value DECIMAL(6,2)
    $new_value = 12.34567;

$foo->value will be 12.34

    $new_value = undef;

$foo->value will be "foo"

    value CHAR(2)
    $new_value = "foo";

$foo->value will be "fo"

And then you have data normalization at the database level rather than the
application level.  Capitalization, phone numbers, credit card numbers,
province names, country names... all are better done in the database than in
individual applications to keep data integrity.

>> You can mitigate the problem by not using lazy loading, just set all columns
>> to "All", then you just have the one select.
> I did try and customize my Essential.  I think I have some code that
> overrides search() that lets me know when CDBI is going back to the db
> for extra columns -- that was helpful in tuning years back.

Honestly, lazy loading is probably the biggest architectural mistake I made in
CDBI.  It seemed like a good idea at the time in 1999, with limited memory and
I was working on a message storage system (lots of small headers, big body)
but honestly it made the code way more complicated than it should be and
probably drags performance down more than it speeds up.  I should have just
made each message join from header and body tables but that would have
required good join support early on and I suck at joins.

That's something which came out of writing DBIx::Class::CDBICompat.  Lazy
loading slows accessors down a lot.

> Again, what I noticed was a long running script that was creating a
> bunch of nested structures and then the trip backwards was generating
> quite a few selects for object I knew I had.
>     $country = $hood->city->state->country;

Speaking of sucking at joins, that's always going to suck in CDBI.  Consider
using Class::DBI::Sweet or DBIx::Class both of which I believe can model the
above as a join instead of a string of selects.

Insulting our readers is part of our business model.

More information about the ClassDBI mailing list