Dave, yes I'm using a sort_key column in my lookup table.
I've only tested the below script on Oracle and it doesn't seem to work properly. I have not installed survey and tried there. (I *think* "case" should work as good as "decode" (or better since it's standards compliant) in Oracle 8.1.7, or am I wrong?)
element-swap.tcl (borrowed from survey and shortened for brevity):
ad_page_contract {
Swap two curriculum elements.
} {
element_id:integer,notnull
sort_order:integer,notnull
direction:notnull
}
if { [string equal "up" $direction] } {
set next_sort_order [expr $sort_order - 1]
} else {
set next_sort_order [expr $sort_order + 1]
}
db_dml swap_sort_orders {
update cu_elements
set sort_key = (case when sort_key = :sort_order then :next_sort_order
when sort_key = :next_sort_order then :sort_order end)
where element_id = :element_id
and sort_key in (:sort_order, :next_sort_order)
}
This would assume that there are no gaps in the sequence of sort_keys, which may be hard to guarantee.
I've also seen solutions (by OF, I think, but I don't remember what package it was right now) that involve two or three separate updates in a transaction. That may be safer.
