[Dbix-class] DBIx::Class::Ordered changes.

[Bill Moseley]

The Ordered component for a DELETE operation used to change the "position"
of the deleted item to zero (moving it out of the way) then move all larger
positions down one in a series.  If deleting "delete_postion":

update ordered_list set position =3D 0 where position =3D delete_position;
for cur_position ( delete_position + 1 .. last_position ) {
    update ordered_list SET postion =3D position - 1 where position =3D
cur_position;
}


The current DBIC does this:


BEGIN WORK
SELECT me.position FROM ordered_list me WHERE ( ( position > ? AND (
position !=3D ? AND owner =3D ? ) ) ) ORDER BY position DESC LIMIT ?: '12',
'12', '1163299', '1'
UPDATE ordered_list SET position =3D ? WHERE ( id =3D ? ): '0', '8332465'
UPDATE ordered_list SET position =3D position - 1 WHERE ( ( ( position
BETWEEN ? AND ? ) AND owner =3D ? ) ): '13', '18', '1163299'
ROLLBACK


The problem, of course, is I have a UNIQUE( position, owner ) and that last
update isn't guaranteed to work in order (from 13 to18 -- which would
prevent duplicate key errors).  The ROLLBACK there is because Postgresql
hit a duplicate key error.

I could make the constraint deferrable, and then defer the transaction, but
that's not the schema I have and DBIC isn't asking for it to be deferred.

Any ideas how to resolve this other than dropping my UNIQUE( position,
owner ) constraint?



-- =

Bill Moseley
moseley at hank.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120918/ff5=
80dd8/attachment.htm