Azure/azure-mobile-services — pull request #366 by biste5 (2014-08-22 18:31:31+00:00)
Title: [Offline] Implement UPSERT in SQLiteStore to preserve values for properties existing only on the client

Very often we have to extend our entities with properties that exist only on the client.
In a simple ToDoItem example, a client entity wants to store the additional information IsFavorite, indicating if that item has been favorited on that client.

MobileServiceSQLiteStore does make use of batch INSERT ON REPLACE INTO SQLite statement which, as documented, in case of REPLACE the existing record is deleted and inserted.
Any column that exists only on the client entity will be then reset to its default value.

There's no real support for UPSERT in SQLite currently.
My implementation performs a UPDATE OR IGNORE statement for every item in batch, and at the end perform the same operation as previous implementation, running a batch INSERT INTO, but I'll be using INSERT OR IGNORE INTO instead of INSERT OR REPLACE INTO.

As we can easily imagine, this has quite some impact on performances, because updates cannot be batched.