On Sat, Jan 06, 2007 at 09:53:39PM -0800, Sean Payne wrote:
> Suppose gui-users wanted to drag and drop rows in a table so that
> they could shuffle it anyway that they wanted so that the rows
> maintained that order the next time they accessed the table. Can
> this be done without updating alot of other rows? How is this
> normally handled?
>
> The ideas I have are
> 1. to use a field (possibly the primary key) and then reorder the
> lower part of the table each time a row is moved.
> 2. to deal with it a separate table - maintain an position->key table
> 3. do something like have an "magical-order-field" which keeps a
> string that when SORTed BY maintains the desired order. so to
> insert a row between "a" and "b" set it's magical-order-field to "aab"
>
> Got hints?
Besides the ones given so far, it seems that you can actually assign the
rowid column. The only thing is, you'll need to avoid rowid collisions
during transactions, something like this:
sqlite> CREATE TABLE toy(name);
sqlite> INSERT INTO toy VALUES ('foo');
sqlite> SELECT rowid, name FROM toy;
1|foo
sqlite> UPDATE toy SET rowid = rowid + 1;
sqlite> SELECT rowid, name FROM toy;
2|foo
sqlite> INSERT INTO toy VALUES ('bar');
sqlite> SELECT rowid, name FROM toy;
2|foo
3|bar
sqlite> BEGIN;
sqlite> UPDATE toy SET rowid = (SELECT rowid FROM toy ORDER BY rowid DESC LIMIT
1) + 1 WHERE name = 'bar';
sqlite> UPDATE toy SET rowid = 3 WHERE name = 'foo';
sqlite> UPDATE toy SET rowid = 2 WHERE name = 'bar';
sqlite> COMMIT;
sqlite> SELECT rowid, name FROM toy;
2|bar
3|foo
sqlite>
In the above 'foo' and 'bar' are switched, but 'bar' needed a temporary
rowid. Alternatively one could have deleted 'bar', set the rowid for
'foo', then re-created 'bar' with the desired rowid (you can set rowid
on insert: INSERT INTO toy (rowid, name) VALUES (1, 'foobar');).
Nico
--
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
