[Dbix-class] Update first X rows

[Peter Rabbitson]

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