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