lundi 27 juillet 2015

Atomically update multiple related tables

I am working with SQLite in Android. I have a model object that contains three lists, one of which is a list of lists. So I have five tables: A for the object, B for the list of lists, C for its lists, and D and E for the other two lists. The case that has me stumped is if the user is updating an existing object, and may or may not have made any changes to its lists. I think I will have to query each table B through E for records whose foreign keys match the primary key of the record I'm updating, and then loop through those results to see if I have a matching item in the lists, manually deleting/inserting/updating as necessary. But it seems to me like there should be a better way?

Related questions: If I wrap this whole operation in beginTransaction() and setTransactionSuccessful(), do I need to do anything else to ensure atomicity? I have never dealt directly with locks in my databases, do I need to add error checking to my existing queries to handle an event in which they are blocked? Is there any error case in which I would need to manually cancel the transaction, or will that occur automatically when I close my database connection?

Aucun commentaire:

Enregistrer un commentaire