On 2014/09/24 22:24, Clemens Ladisch wrote:
RSmith wrote:
Clemens I'm liking the link list but did not go with it due to an expensive
insert function
Yes, you have to update two references (with prev/next), but how is that
worse than the update of all SortOrder values?
Well the insert runs 2-3 times a second, the SortOrder update for re-ordering run maybe once a week.. at least, that was what I
envisioned, but that is not feasible (may need to run several times a day due to the division problem), so hence now looking at the
more expensive insert as a solution.
how would I get a normal SQL query ORDER BY clause to use that?
This is not possible with ORDER BY. You would need a recursive common
table expression:
WITH RECURSIVE ListInOrder(ID, Data, Next) AS (
SELECT ID, Data, Next
FROM ListTable
WHERE Prev IS NULL
UNION ALL
SELECT L.ID, L.Data, L.Next
FROM ListTable AS L
JOIN ListInOrder ON L.ID = ListInOrder.Next
)
SELECT ID, Data FROM ListInOrder;
The recursion stops when a Next value does not have a corresponding ID.
See <http://www.sqlite.org/lang_with.html>.
I'd have to have an Index on "Next"
Thanks again, will try some ideas around this one... Another thought I came up with was a separate table keeping just the sorted
indices as a list, but I think that will be equally expensive unless I get another thread to do that or maybe a temporary in-memory
table that gets committed at intervals or such. All of this however detracts from the "live-ness" of the DB.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
