my %data = 1 .. 10;
my( @data  ) = ( 3, 5 , 1, 9 , 7 );
print "$_ => $data{$_}$/" for sort keys %data;
print "---$/";
print "$_ => $data{$_}$/" for @data;
__END__
1 => 2
3 => 4
5 => 6
7 => 8
9 => 10
---
3 => 4
5 => 6
1 => 2
9 => 10
7 => 8
[download]

just because row 27,895 out of 500,000 was repositioned to slot 3, now 499,998 rows will have their id recalculated.

sub move {
  my ($from, $to) = @_;
  my $temp = $row[$from];
  if ($from < $to) {
    for ($from+1..$to) {
      $row[$_-1] = $row[$_];
    }
  }
  else {
     for (0..$from-$to-1) {
       $row[$from-$_] = $row[$from-$_-1];
     }    
  }
  $row[$to] = $temp;
}
[download]

yes, you are right. My bad. Of course, in the worst case (if the last slot is repositioned to the first slot) all the rows will have to be recalculated.

What you are suggesting is what I had resigned myself to doing with the database. My guess is, DBI should be fast enough to not worry about it.

Its just that it seems like an inelegant method. Hence, my question in the first place.

my %SQL = (
    order_by_date => 'select image_name from image
                      where house_id in (
                        select kitchen_id, bathroom_id
                        from house where house_id=?
                      )
                      order by image_date',

    kitchen_first => 'select image_name from image
                      where image_id =
                      (  select kitchen_id, bathroom_id
                         from house where house_id = ? )',

    bathroom_first => 'select image_name from image
                      where image_id =
                      (  select bathroom_id, kitchen_id
                         from house where house_id = ? )',
);

....
$sth = $dbh->prepare( $SQL{order_by_date} );
....
$sth = $dbh->prepare( $SQL{kitchen_first} );
....
[download]

You can use an array slice to change the location of an item:

$ perl -le '
    $, = ", "; 
    @a = (0 .. 20); 
    print @a; 
    @a[2,1] = @a[1,2]; # Magic line
    print @a;'
0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 
+20
0, 2, 1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 
+2
[download]

For more complex things, you might need splice.

Are you really using a database? If you're using a real database, it undoubtedly has support for indexing more than one field in a table. That is, besides the primary key field, you can have auxiliary key fields. So, add a secondary key field that stores a number. Start out with all the numbers being multiples of ten (just like line numbers on old BASIC programs) so that you can insert several records in between before you have to renumber your index. Moving a record from the middle out to the beginning or end, of course, is easy: you just leave a bigger gap where it came from, no problem. Then you write a script that uses idle time or off time to look for places where the numbers are bunched together and renumber some records to pull them apart. The only time you take a performance hit is when you need to insert a record between two that already have adjascent numbers, and even then you only have to renumber enough records to fit it in, and let the background renumberer worry about smoothing out the distribution later.

If the list isn't large enough to warrant a full database, just use splice.

$;=sub{$/};@;=map{my($a,$b)=($_,$;);$;=sub{$a.$b->()}}
split//,".rekcah lreP rehtona tsuJ";$\=$ ;->();print$/
[download]

Adding another column to the table to store the sort order may be an idea, but changing the sort order of any one element will entail recalculating the sort order of the all subsequent elements in the table. This is the original "thorny" problem that I am trying to circumvent.

One way I thought up would be to create a sort order that is not sequential but jumps by an increment of, say, 100 or 1000... the way we used to number lines in BASIC... to avoid renumbering lines in case a line has to be moved. So, I would create a column for storing the sort order, but instead of storing 1, 2, 3... I would store 1000, 2000, 3000... Then, if the element 45 with the sort order value 45000 had to be repositioned at slot 3, I would just renumber its sort order value to 2500 (leaving space on both sides, before/downto 2000, and after/upto 3000 without having to recalculate any other element.

Of course, this is inelegant as heck, and is not scalable. But might work.

Any thoughts?

Btw, I am also reading up on linked lists in the hope they may offer some salvation.

I am also reading up on linked lists

This seems like it might be the way to go. A linked list, based on my understanding, is just a bunch of items that have pointers, aka links, to their neighbors. Perhaps one could use a table with columns THIS_ID, PREV_ID, NEXT_ID. Then only a few columns need to be changed in order to remove an item, add a new item, or move an item to a different location.

The problem with this approach, as I see it, is you then have to step through the whole thing sequentially in order to build your final ordered list. Perhaps there are some techniques to avoid this that I'm unaware of (I do not have a computer science background; I'm just a long-time amateur programmer).

If there are any other ideas, I'd love to hear them. It's always an education. 8^)

Update: to clarify a bit, I should have mentioned that I agree with some of the monks who say the order should be separate from the definition of the images. In other words, you might have one table called IMAGES with columns IMAGE_ID, IMAGE_NAME, IMAGE_SIZE, IMAGE_COMMENTS then a separate table named IMAGE_ORDER with the THIS_ID, PREV_ID, NEXT_ID columns.

Only half-serious:
You can solve the problem of needing to put picture 5 between pictures 2 and 3 by assigning it sequence 2.5. Unlike the BASIC numbering method, scalability is only limited by the accuracy of your floating-point package. :-)

Now for a real-world answer:
If you don't care about normalization, use separate tables for house and pictures, and put a big nasty text column on the house table. Put the comma-separated list of pictures for that house in that column, in the order they should be displayed! This will work until there are more pictures for a house than can fit in that column. Base64 numbering would help with this, reducing the column size to two chars per picture for the first 63 pictures. (one data, one separator, assume no use of 0). If you can guarantee an upper limit on the number of pictures (like 64^2-1), you can skip the separator and use unpack().

Re:Linked Lists
Linked lists are a classic solution to this problem in memory, but don't solve the problem of one update for each picture when you want to save the order to the database, unless you use Storable to just dump the whole list into a column, which is not much different from my suggestion above.

--
Spring: Forces, Coiled Again!