[Dbix-class] Update first X rows

[Yves Räber]

Thanks for your explanation. But things are still not clear for me.

If I create a resulset with a "rows" argument and then

1/ Get all  the rows with a ->all method : It generate a "SELECT ... LIMIT rowcount" that's perfect
2/ Update the rows with a ->update( { whatever => 1 }) : It generates a "UPDATE ..." but ignores the limit

The slow and foolpoof method would be to update the rows one by one and stop after X elements, but it's not what it is doing. 

The workaround I use is to get the elements with "->next" and then update them one by one :

while(my $row = $task_to_dispatch->next) {
  $row->update({ state => 'TODO' });
}

Now I don't understand if this is by design, a missing feature or a bug. And unfortunately I don't feel competent enough to tweak in DBIC's code but I'd be happy to help with testing.

Yves.


----- Original Message -----
From: "Peter Rabbitson" <rabbit+dbic at rabbit.us>
To: "DBIx::Class user and developer list" <dbix-class at lists.scsys.co.uk>
Sent: Wednesday, July 15, 2009 1:48:15 AM GMT +01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna
Subject: Re: [Dbix-class] Update first X rows

On Tue, Jul 14, 2009 at 03:20:56PM +0200, Yves Räber wrote:
> Hi,
> 
> * The RDMS is MySQL (5.0.75 with InnoDB storage engine)
> * I have a single column primary key (integer, auto increment)
> * It definitely supports UPDATE...LIMIT row_count (http://dev.mysql.com/doc/refman/5.0/en/update.html)
> * I've already tested it, it work just fine with manual queries

Good to know.

> And sorry but the claim in my previous mail is false : if I use the search and update query with a "rows" argument as above, the rows part it is simply ignored.
> 
> my $task_to_dispatch = $c->model('myDB::Tasks')->search($sql_args, { rows => $rows });
> my $dispatched_tasks = $tasks_to_dispatch->update( { state => 'TODO' } );
> 
> If I check the generated query with DBIC_TRACE, the "LIMIT" part is not generated at all, and all the rows are updated.

You didn't pay enough atention. What happens is the code at line 1298
in DBIC::Storage::DBI::_per_row_update_delete. Pay attenion to the initial
SELECT that gathers the PKs to modify - it does contain the limit. In other
words the code behaves correctly, it just uses the slowest and most
foolproof method to accomplish the job.

> If there is a more elegant way to do it without writing my own custom SQL query, it would be great.

Yes there is, but it will require some refactoring. When I wrote the code
in question the consensus was that LIMIT is not generaly supported. Now
that I see I was wrong, I need to provide more/better hooks for the DBI
drivers to override as seen fit. I won't however be able to do this
properly until mid-august. If someone else wants to pick this up and
JFDI I'd be glad to review it sooner. Some random thoughts:

- The dispatcher in DBIC::Resultset::_rs_update_delete() needs to be more
flexible and more importantly - needs to absorb more of the logic, instead
of feeding resultset objects to the storage driver (we have the tools to
do this now). The final goal is to never have a $rs object show up in any
Storage::* code.

- The "limit only" default behavior *still* needs to be a subquery (or in
the case of unworthy databases - a _per_row_u/d, as limit at times is a
nightmare (see mssql). The idea is to be extremely conservative and build
up overrides in places where we absolutely know things will work (i.e.
an override of the appropriate hook in DBI::mysql to do a straight $op
when just a limit is present). I rather delete rows slowly off an $rs,
instead of quickly deleting the wrong ones :)

- We need better naming for the hooks. I.e. ::DBI::_multipk_update_delete 
doesn't make sense in this context anymore.

Cheers

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk