The question is published on by Tutorial Guruji team.
I develop and maintain a Google Tasks app on Android. Currently I have a class that contains lists and tasks, in order, in an
ArrayList. I’m thinking of switching to SQLite to better structure the app. I’m not sure what the best and easiest way to store the order of tasks in the database would be though.
Right now, I can simply
add items at different indices and obviously the other List rows indices are in correct order. With an SQLite database, I can store a positional number in each row, and everytime I move a task, update the following rows accordingly.
The problem with that system is concurrency and maintainability: If a row’s position is updated (and the following rows’ positions are incremented/decremented) at the same time a tasks sync is happening it could get messy, as the sync is changing row positions too.
I could add a Mutex lock, but I don’t want to do that, I want users to be able to update data at the same time as a sync, and if a conflict occurs, one of the new positions be discarded, without the following rows being messed up.
My question: What is the best way to store and update order within an SQLite database?
The problem with adding a
sort_position value to each row is that it can’t be updated atomically, because to swap two items in the list you need to change both of their positions. The database is much better at guaranteeing atomicity when the changes are to the same row.
A better way would be to treat the ordering column as a priority – the values can be sparse so a rows can be placed between two adjacent ones, and two items can have the same value without it being a consistency problem. If the values are sparse, you can swap two items by just changing one value, so concurrent updates are more gracefully handled (although you might still get unexpected situations if two agents are trying to rearrange the same part of the list).
An alternative way of handling it might be to have the ordering be stored as one value in a different table – for example as a list of item ids. This has the advantage of allowing more precise control of the ordering while not ever getting garbled state from interleaved updates, but is more complicated (handling missing or unknown items, how to handle both updating at the same time). I wouldn’t try this unless you really need it.