Re: self ordering list

Bryan Wilkerson wrote:
>
> My first question concerns self ordering lists.  Basically, a numeric
> column that automatically maintains an order but allows arbitrary
> reordering by the user.  For example, a media playlist or the
> priority of workitems within a parent container ;)     This seems
> like a common pattern.
>
> priority |  workitem
> ---------+-----------
> 1        | task 1
> 2        | task 2
> 3        | task 3
> 4        | task 4
> 5        | task 5
>
> Insert a new task with priority==2 and...
>
>  update tablename set priority=priority+1 where priority >= 2
>
> delete task with priority==2 and...
>
>  update tablename set priority=priority-1 where priority > 2
>
> reorder task with priority==2, set its priority=4
>
>  update tablename set priority=priority+1 where priority >= 4
 >  update tablename set priority=priority-1 where priority > 2
 >   and priority < 4
>
> etc....
>
> I've implemented in my model code but it has some deadlock issues and
> I really strongly have believed all along that this belongs in the db
> anyway.  Implementing the above with triggers is a tricky problem
> because the trigger would key off the priority change and the
> successive updates would recusively trigger.
>

Why not update everything into a temp table first, then update the
original with the new values from that? Or maybe a view is better suited
to this.

brian