In an add-on, I would like to keep track of changed or removed cards in an efficient way. For changes, I’m able to use the card’s “mod” time (efficiently assuming I add an index on it). For deletions, they are tracked in “graves”, however those are cleared during sync.
Any thoughts on how to track deletions efficiently?
My current thinking: Could query “graves”, but assume a full check of “cards” is needed if “col.usn” changed?
How frequently are you checking? The cards table is relatively cheap to do a table scan on, and that is what is done every time the deck list is displayed. Graves are only cleared on a full sync.
I have a morpheme based index of the cards in my add-on, and I’m keeping it in sync when the user does a search against the index. I’d like to keep latency low. A majority of the time it will be a no-op of course, and I check if the collection was changed in the first place.
Graves are only cleared on a full sync.
Thanks, good to know.
Doing a diff against a temporary table holding the cached card ids is taking 6ms for 40k cards, so I think this will be quite reasonable to do.
CREATE TEMP TABLE mm_synced_cards (
id INTEGER PRIMARY KEY
-- Add whatever ids got cached to the temp table
INSERT INTO mm_synced_cards(id)
SELECT id FROM cards;
-- Get deletions
SELECT * FROM mm_synced_cards
WHERE id not in (SELECT id from cards)