Dear Dick:
It's like the guy painting the floor. He starts at the doorway, and paints
himself into the corner.
Is that you you feel?
I actually found a way to "insert" things in an order like this. Simple
application concept, difficult to implement.
For the basic concept, consider this. If you numbered the Seq initially
1000, 2000, 3000, 4000, etc. then you'd have "room" to insert new values
between. Split the difference. To insert between 1000 and 2000 you use
1500. Between 1000 and 1500, use 1250. You have to insert many times
before you would run out of room to insert again.
What I actually did was to use an 80 character string to store these
"numbers" but the numbers weren't just base 10. I used base 32, using
digits 0-9 then the letters A-V. By using a power of 2, splitting the
difference became easy.
So, whenever a new sequence number was added at the high end, I just used
the first 4 characters of the 80. The rows would be numbered 0001, 0002,
0003 up to 0009, then 000A, 000B.
Now, when I say 0001, it is really 00010000000 . . .
Out to 80 places.
Inserting between 0001 and 0002 you would just use 0001G. Between 0001 and
0001G would be 00018. Then successively, 00014, 00012, 00011, 00011G, and
so on. Each of the remaining 76 characters of the string could be divided
at the same point 5 times. Using 76 characters then, you could insert a new
row immediately after 0001 a total of 380 times before it would fail. No
one ever came close to doing that. So, that's how I can number the rows of
an invoice, allowing the user to control that order and keeping the invoice
in the desired order, and implement insertions.
Well, your problem reminds me very much of that. Can you see why? Would
such a solution be useful?
I can even tell you what to do on the 381st insertion and keep it going for
a long, long time! It will take a few seconds, you can bet, but I would
copy the whole list out of the database, renumber from scratch (0001, 0002,
0003 . . .) and put them back. I could even give you a plan to recover if
the computer crashes while doing this.
If anybody has a good alternative, I'd love to hear it!
You've pried one of my secrets from me. Actually, this shouldn't be so
difficult. It should probably be implemented inside the capabilities of a
database somewhere, rather than be dumped on us poor application
programmers! Well, don't hold your breath!
Tom Ellison