How can I do an update in Class::DBI without selecting a record first?

Question

Hello,

To do an insert with Class::DBI, you can simply do:

my $object = Object::DB->insert({ a => 1, b => 2, c => 3, ...});

But there is no such thing for update. The best I could come up with is selecting the record first then updating it:

my $object = Object::DB->retrieve($id);
my $object->set( a => 1, b => 2, c => 3, ...};
$object->update;

This is not efficient since I have to do a SELECT first, and then an UPDATE instead of just one UPDATE.

Is there a better way to do this with Class::DBI? I don't want to do 42 $object->a(1), $object->b(2), etc., $object->update;

Answer 1

As far as I know, Class::DBI does not have a good way to do this, As you've noted, its update() method is meant to be called on an object that has been previously loaded from the database.

You may be able to convince Class::DBI to do what you want, however, with something like this:

# Make new "empty" object
my $o = My::CDBI::Object->new;

# Set the primary key column and discard the change
$o->set(your_pk_column => 123);
$o->discard_changes;

# Set your other columns
$o->set(a => 'foo', b => 'bar');

# Do the update
$o->update;

If this feature is important to you and you're not already too far into your project, you will definitely have better luck with one of the newer Perl ORMs such as Rose::DB::Object or DBIx::Class. DBIx::Class even includes a Class::DBI compatibility layer.

Answer 2

One way that I've found to do this is to override the default iterator class for your objects. This allows you to have a collection of individual objects with an update method on the collection. Class::DBI provides an method for this:

__PACKAGE__->iterator_class('MyClass::CDBI::Iterator');

This allows you to then make an update method in the iterator class that can save all of the objects in the collection. As such your code may look something like this:

my $collection = Object::DB->search_where({id => {'>=', 0}});
foreach my $obj ($collection->next()) {
  $obj->a('bob');
  $obj->b('tom');
}
$collection->update();

Which makes for some pretty well self-documented code. If you go this route, I'd also suggest putting the is_changed method into use as the update() occurs. It will help you save time by not updating unchanged rows.