On 2017/11/20 11:31 PM, Shane Dev wrote:
Hi Ryan,
Nice trick - changing the sign of ID. I agree that changing an existing
record's ID value would cause problems for any other field / table that
referenced this key.
If I used your idea of adding a SortOrder column incremented in steps of x,
as you suggested, the gaps would start to disappear after many inserts
between existing records. I suppose the gaps could be reset by writing
program to call sqlite3_exec with
select * from fruit order by SortOrder;
and increment a RowCount variable each time the callback was triggered,
then update fruit with something like
update fruit set SortOrder = RowCount*x where id = [id of the row when it's
callback was triggered];
Well this would not work because the id is no longer in-step with the
ordering, in stead the SortOrder column controls ordering, but it may be
completely different to the id (which is precisely why we needed it to
start with, since we don't wish to jiggle the id around to adjust the
order).
I would to prefer to avoid this solution because it involves mutable state
(the RowCount variable) which is the "root of all evil" (bugs).
Agreed.
Is there an
SQL statement which could reset the gaps back to x?
It just so happens there is. :)
UPDATE fruit SET SortOrder = (SELECT COUNT(*)*100 FROM fruit AS F WHERE
F.id < fruit.id);
This will reset the SortOrder indices in steps of 100 from 0 to (n*100)
where n is the last record ordinal in the list.
I just picked 100 as a thumbsuck, you can of course use anything from 1
to approaching the 64-bit integer limit, but probably 100, 1000 or 10000
will do, depending on how often you foresee ordered inserts happening.
Also, it's perfectly OK to use Floating point values here, so you can
keep inserting even after exhausting the integer divisible limit.
Cheers!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
