public async Task UpsertAsync_UpdatesTheRow_WhenItExists()
{
await PrepareTodoTable();
// insert a row and make sure it is inserted
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
DefineTestTable(store);
await store.InitializeAsync();
await store.UpsertAsync(TestTable, new[] { new JObject()
{
{ "id", "abc" },
{ "text", "xyz" },
{ "__createdAt", DateTime.Now }
} }, ignoreMissingColumns : false);
await store.UpsertAsync(TestTable, new[] { new JObject()
{
{ "id", "abc" },
{ "__createdAt", new DateTime(200, 1, 1) }
} }, ignoreMissingColumns : false);
JObject result = await store.LookupAsync(TestTable, "abc");
Assert.AreEqual(result.Value <string>("id"), "abc");
Assert.AreEqual(result.Value <string>("text"), "xyz");
Assert.AreEqual(result.Value <string>("__createdAt"), "01/01/0200 00:00:00");
}
long count = TestUtilities.CountRows(TestDbName, TestTable);
Assert.AreEqual(count, 1L);
}
private static async Task <MobileServiceSQLiteStore> SetupTestTable()
{
if (!queryTableInitialized)
{
TestUtilities.DropTestTable(TestDbName, TestTable);
}
var store = new MobileServiceSQLiteStore(TestDbName);
store.DefineTable(TestTable, new JObject()
{
{ "col1", String.Empty },
{ "col2", 0L },
{ "col3", 0f },
{ "col4", DateTime.UtcNow },
{ "col5", false },
{ "col6", Guid.Empty }
});
await store.InitializeAsync();
if (!queryTableInitialized)
{
await store.UpsertAsync(TestTable, testData, ignoreMissingColumns : false);
}
queryTableInitialized = true;
return(store);
}
private static async Task <MobileServiceSQLiteStore> SetupMathTestTable(JObject[] mathTestData)
{
TestUtilities.DropTestTable(TestDbName, MathTestTable);
// first create a table called todo
var store = new MobileServiceSQLiteStore(TestDbName);
store.DefineTable(MathTestTable, new JObject()
{
{ "id", String.Empty },
{ "val", 0f },
{ "expected", 0f }
});
await store.InitializeAsync();
foreach (JObject item in mathTestData)
{
item[MobileServiceSystemColumns.Id] = Guid.NewGuid().ToString();
}
await store.UpsertAsync(MathTestTable, mathTestData, ignoreMissingColumns : false);
return(store);
}
public async Task Upsert_Succeeds_IfCaseIsDifferentButNameIsSame()
{
string tableName = "itemWithDate";
ResetDatabase(tableName);
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
store.DefineTable(tableName, new JObject()
{
{ "id", String.Empty },
{ "date", DateTime.Now }
});
await store.InitializeAsync();
await store.UpsertAsync("ITEMwithDATE", new[]
{
new JObject()
{
{ "ID", Guid.NewGuid() },
{ "dATE", DateTime.UtcNow }
}
}, ignoreMissingColumns : false);
}
}
public async Task SyncAsync()
{
ReadOnlyCollection <MobileServiceTableOperationError> syncErrors = null;
try
{
//await this._client.SyncContext.PushAsync();
//_productTable.DeleteAsync();
//var productList = await _productTable.ToListAsync();
//productList.ForEach(p => _productTable.DeleteAsync(p));
var deleteQuery = new Microsoft.WindowsAzure.MobileServices.Query.MobileServiceTableQueryDescription("Product");
await _store.DeleteAsync(deleteQuery);
var productList = await GetProductsAsyncAPI();
//var productTableQuery = this._productTable.CreateQuery();
await _store.UpsertAsync("Product", productList, true);
//_client.SyncContext.Store.
//await this._productTable.PullAsync(
// //The first parameter is a query name that is used internally by the client SDK to implement incremental sync.
// //Use a different query name for each unique query in your program
// "availableProducts", productTableQuery);
}
catch (MobileServicePushFailedException exc)
{
if (exc.PushResult != null)
{
syncErrors = exc.PushResult.Errors;
}
}
catch (Exception ex)
{
}
// Simple error/conflict handling. A real application would handle the various errors like network conditions,
// server conflicts and others via the IMobileServiceSyncHandler.
if (syncErrors != null)
{
foreach (var error in syncErrors)
{
if (error.OperationKind == MobileServiceTableOperationKind.Update && error.Result != null)
{
//Update failed, reverting to server's copy.
await error.CancelAndUpdateItemAsync(error.Result);
}
else
{
// Discard local change.
await error.CancelAndDiscardItemAsync();
}
Debug.WriteLine(@"Error executing sync operation. Item: {0} ({1}). Operation discarded.", error.TableName, error.Item["id"]);
}
}
}
public async Task UpsertAsync_DoesNotThrow_WhenItemIsEmpty()
{
TestUtilities.DropTestTable(TestDbName, TestTable);
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
store.DefineTable(TestTable, new JObject()
{
{ "id", String.Empty },
{ "dob", DateTime.UtcNow }
});
await store.InitializeAsync();
await store.UpsertAsync(TestTable, new[] { new JObject() }, ignoreMissingColumns : true);
await store.UpsertAsync(TestTable, new[] { new JObject() }, ignoreMissingColumns : false);
}
}
public async Task UpsertAsync_CanProcessManyRecordsAtOnce()
{
TestUtilities.DropTestTable(TestDbName, TestTable);
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
var template = new JObject
{
{ "id", 0 },
{ "value1", "Hello, world" },
{ "value2", "Hello, world" },
{ "value3", "Hello, world" },
{ "value4", "Hello, world" },
{ "value5", "Hello, world" }
};
store.DefineTable(TestTable, template);
//create the table
await store.InitializeAsync();
//add a whole bunch of items. We want {number of items} * {number of fields} to exceed sqlite's parameter limit
const int insertedItemCount = 500;
var itemsToInsert = Enumerable.Range(1, insertedItemCount)
.Select(id =>
{
var o = new JObject(template)
{
["id"] = id
};
return(o);
})
.ToArray();
//Insert the items
await store.UpsertAsync(TestTable, itemsToInsert, ignoreMissingColumns : false);
JArray records = (JArray)await store.ReadAsync(MobileServiceTableQueryDescription.Parse(TestTable, "$orderby=id"));
//Verify that all 500 records were inserted
Assert.Equal(records.Count, insertedItemCount);
//Verify that all fields are intact
for (var i = 0; i < insertedItemCount; i++)
{
Assert.True(JToken.DeepEquals(itemsToInsert[i], records[i]), "Results retrieved from DB do not match input");
}
}
}
public async Task UpsertAsync_ThenReadAsync_AllTypes()
{
TestUtilities.DropTestTable(TestDbName, TestTable);
// first create a table called todo
using (MobileServiceSQLiteStore store = new MobileServiceSQLiteStore(TestDbName))
{
store.DefineTable(TestTable, JObjectTypes.GetObjectWithAllTypes());
await store.InitializeAsync();
var upserted = new JObject()
{
{ "id", "xyz" },
{ "Object", new JObject()
{
{ "id", "abc" }
} },
{ "Array", new JArray()
{
new JObject()
{
{ "id", 3 }
}
} },
{ "Integer", 123L },
{ "Float", 12.5m },
{ "String", "def" },
{ "Boolean", true },
{ "Date", new DateTime(2003, 5, 6, 4, 5, 1, DateTimeKind.Utc) },
{ "Bytes", new byte[] { 1, 2, 3 } },
{ "Guid", new Guid("AB3EB1AB-53CD-4780-928B-A7E1CB7A927C") },
{ "TimeSpan", new TimeSpan(1234) }
};
await store.UpsertAsync(TestTable, new[] { upserted }, false);
var query = new MobileServiceTableQueryDescription(TestTable);
var items = await store.ReadAsync(query) as JArray;
Assert.IsNotNull(items);
Assert.AreEqual(items.Count, 1);
var lookedup = items.First as JObject;
Assert.AreEqual(upserted.ToString(Formatting.None), lookedup.ToString(Formatting.None));
}
}
public async Task UpsertAsync_InsertsTheRow_WhenItemHasNullValues()
{
TestUtilities.DropTestTable(TestDbName, TestTable);
// insert a row and make sure it is inserted
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
store.DefineTable(TestTable, new JObject()
{
{ "id", String.Empty },
{ "dob", DateTime.UtcNow },
{ "age", 0 },
{ "weight", 3.5 },
{ "code", Guid.NewGuid() },
{ "options", new JObject()
{
} },
{ "friends", new JArray()
{
} },
{ "__version", String.Empty }
});
await store.InitializeAsync();
var inserted = new JObject()
{
{ "id", "abc" },
{ "dob", null },
{ "age", null },
{ "weight", null },
{ "code", null },
{ "options", null },
{ "friends", null },
{ "__version", null }
};
await store.UpsertAsync(TestTable, new[] { inserted }, ignoreMissingColumns : false);
JObject read = await store.LookupAsync(TestTable, "abc");
Assert.AreEqual(inserted.ToString(), read.ToString());
}
}
public async Task UpsertAsync_InsertsTheRow_WhenItDoesNotExist()
{
await PrepareTodoTable();
// insert a row and make sure it is inserted
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
DefineTestTable(store);
await store.InitializeAsync();
await store.UpsertAsync(TestTable, new[] { new JObject()
{
{ "id", "abc" },
{ "__createdAt", DateTime.Now }
} }, ignoreMissingColumns : false);
}
long count = TestUtilities.CountRows(TestDbName, TestTable);
Assert.AreEqual(count, 1L);
}
public async Task UpsertAsync_Throws_WhenColumnInItemIsNotDefinedAndItIsLocal()
{
TestUtilities.DropTestTable(TestDbName, TestTable);
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
store.DefineTable(TestTable, new JObject()
{
{ "id", String.Empty },
{ "dob", DateTime.UtcNow }
});
await store.InitializeAsync();
var ex = await ThrowsAsync <InvalidOperationException>(() => store.UpsertAsync(TestTable, new[] { new JObject()
{
{ "notDefined", "okok" }
} }, ignoreMissingColumns: false));
Assert.AreEqual(ex.Message, "Column with name 'notDefined' is not defined on the local table 'todo'.");
}
}
public async Task <JsonResult> Get(string username)
{
try
{
MobileServiceSQLiteStore Store = new MobileServiceSQLiteStore("local.db");
Store.DefineTable <Player>();
await Store.InitializeAsync();
var result = await Store.LookupAsync("Player", username);
Player p = null;
try
{
p = JsonConvert.DeserializeObject <Player>(result.ToString());
p.Roundssurvived++;
}
catch
{
}
if (p == null)
{
p = new Player()
{
id = username, Roundssurvived = 1
};
}
await Store.UpsertAsync("Player", new List <JObject>() { JObject.FromObject(p) }, true);
}
catch (Exception ex)
{
return(new JsonResult(ex.Message));
}
return(new JsonResult("200"));
}
public async Task Upsert_ThenLookup_ThenUpsert_ThenDelete_ThenLookup()
{
TestUtilities.DropTestTable(TestDbName, TestTable);
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
// define item with all type of supported fields
var originalItem = new JObject()
{
{ "id", "abc" },
{ "bool", true },
{ "int", 45 },
{ "double", 123.45d },
{ "guid", Guid.NewGuid() },
{ "date", testDate },
{ "options", new JObject(){ {"class", "A"} } },
{ "friends", new JArray(){ "Eric", "Jeff" } }
};
store.DefineTable(TestTable, originalItem);
// create the table
await store.InitializeAsync();
// first add an item
await store.UpsertAsync(TestTable, new[] { originalItem }, ignoreMissingColumns: false);
// read the item back
JObject itemRead = await store.LookupAsync(TestTable, "abc");
// make sure everything was persisted the same
Assert.AreEqual(originalItem.ToString(), itemRead.ToString());
// change the item
originalItem["double"] = 111.222d;
// upsert the item
await store.UpsertAsync(TestTable, new[] { originalItem }, ignoreMissingColumns: false);
// read the updated item
JObject updatedItem = await store.LookupAsync(TestTable, "abc");
// make sure the float was updated
Assert.AreEqual(updatedItem.Value<double>("double"), 111.222d);
// make sure the item is same as updated item
Assert.AreEqual(originalItem.ToString(), updatedItem.ToString());
// make sure item is not same as its initial state
Assert.AreNotEqual(originalItem.ToString(), itemRead.ToString());
// now delete the item
await store.DeleteAsync(TestTable, new[] { "abc" });
// now read it back
JObject item4 = await store.LookupAsync(TestTable, "abc");
// it should be null because it doesn't exist
Assert.IsNull(item4);
}
}
public async Task Upsert_ThenLookup_ThenUpsert_ThenDelete_ThenLookup()
{
TestUtilities.DropTestTable(TestDbName, TestTable);
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
// define item with all type of supported fields
var originalItem = new JObject()
{
{ "id", "abc" },
{ "bool", true },
{ "int", 45 },
{ "double", 123.45d },
{ "guid", Guid.NewGuid() },
{ "date", testDate },
{ "options", new JObject()
{
{ "class", "A" }
} },
{ "friends", new JArray()
{
"Eric", "Jeff"
} }
};
store.DefineTable(TestTable, originalItem);
// create the table
await store.InitializeAsync();
// first add an item
await store.UpsertAsync(TestTable, new[] { originalItem }, ignoreMissingColumns : false);
// read the item back
JObject itemRead = await store.LookupAsync(TestTable, "abc");
// make sure everything was persisted the same
Assert.AreEqual(originalItem.ToString(), itemRead.ToString());
// change the item
originalItem["double"] = 111.222d;
// upsert the item
await store.UpsertAsync(TestTable, new[] { originalItem }, ignoreMissingColumns : false);
// read the updated item
JObject updatedItem = await store.LookupAsync(TestTable, "abc");
// make sure the float was updated
Assert.AreEqual(updatedItem.Value <double>("double"), 111.222d);
// make sure the item is same as updated item
Assert.AreEqual(originalItem.ToString(), updatedItem.ToString());
// make sure item is not same as its initial state
Assert.AreNotEqual(originalItem.ToString(), itemRead.ToString());
// now delete the item
await store.DeleteAsync(TestTable, new[] { "abc" });
// now read it back
JObject item4 = await store.LookupAsync(TestTable, "abc");
// it should be null because it doesn't exist
Assert.IsNull(item4);
}
}
public async Task <int> SaveItemAsync(Patient item)
{
await database.UpsertAsync("Patient", new List <JObject>() { JObject.FromObject(item) }, true);
return(200);
}
public async Task UpsertAsync_Throws_WhenColumnInItemIsNotDefinedAndItIsLocal()
{
TestUtilities.DropTestTable(TestDbName, TestTable);
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
store.DefineTable(TestTable, new JObject()
{
{ "id", String.Empty },
{ "dob", DateTime.UtcNow }
});
await store.InitializeAsync();
var ex = await ThrowsAsync<InvalidOperationException>(() => store.UpsertAsync(TestTable, new[] { new JObject() { { "notDefined", "okok" } } }, ignoreMissingColumns: false));
Assert.AreEqual(ex.Message, "Column with name 'notDefined' is not defined on the local table 'todo'.");
}
}
public async Task UpsertAsync_Throws_WhenInsertingRecordsWhichAreTooLarge()
{
TestUtilities.DropTestTable(TestDbName, TestTable);
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
var template = new JObject
{
{ "id", 0 },
};
//SQLite limits us to 999 "parameters" per prepared statement
for (var i = 0; i < 1000; i++)
{
template["column" + i] = "Hello, world";
}
store.DefineTable(TestTable, template);
//create the table
await store.InitializeAsync();
//attempt to insert a couple of items
var item1 = new JObject(template);
item1["id"] = 1;
var item2 = new JObject(template);
item1["id"] = 2;
InvalidOperationException ex = await AssertEx.Throws <InvalidOperationException>(() => store.UpsertAsync(TestTable, new[] { item1, item2 }, ignoreMissingColumns: false));
Assert.AreEqual("The number of fields per entity in an upsert operation is limited to 800.", ex.Message);
}
}
public async Task UpsertAsync_InsertsTheRow_WhenItemHasNullValues()
{
TestUtilities.DropTestTable(TestDbName, TestTable);
// insert a row and make sure it is inserted
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
store.DefineTable(TestTable, new JObject()
{
{ "id", String.Empty },
{ "dob", DateTime.UtcNow },
{ "age", 0},
{ "weight", 3.5 },
{ "code", Guid.NewGuid() },
{ "options", new JObject(){} },
{ "friends", new JArray(){} },
{ "version", String.Empty }
});
await store.InitializeAsync();
var inserted = new JObject()
{
{ "id", "abc" },
{ "dob", null },
{ "age", null },
{ "weight", null },
{ "code", null },
{ "options", null },
{ "friends", null },
{ "version", null }
};
await store.UpsertAsync(TestTable, new[] { inserted }, ignoreMissingColumns: false);
JObject read = await store.LookupAsync(TestTable, "abc");
Assert.AreEqual(inserted.ToString(), read.ToString());
}
}
public async Task UpsertAsync_DoesNotThrow_WhenItemIsEmpty()
{
TestUtilities.DropTestTable(TestDbName, TestTable);
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
store.DefineTable(TestTable, new JObject()
{
{ "id", String.Empty },
{ "dob", DateTime.UtcNow }
});
await store.InitializeAsync();
await store.UpsertAsync(TestTable, new[] { new JObject() }, ignoreMissingColumns: true);
await store.UpsertAsync(TestTable, new[] { new JObject() }, ignoreMissingColumns: false);
}
}
public async Task UpsertAsync_InsertsTheRow_WhenItDoesNotExist()
{
await PrepareTodoTable();
// insert a row and make sure it is inserted
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
DefineTestTable(store);
await store.InitializeAsync();
await store.UpsertAsync(TestTable, new[]{new JObject()
{
{ "id", "abc" },
{ "createdAt", DateTime.Now }
}}, ignoreMissingColumns: false);
}
long count = TestUtilities.CountRows(TestDbName, TestTable);
Assert.AreEqual(count, 1L);
}
public async Task UpsertAsync_UpdatesTheRow_WhenItExists()
{
await PrepareTodoTable();
// insert a row and make sure it is inserted
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
DefineTestTable(store);
await store.InitializeAsync();
await store.UpsertAsync(TestTable, new[]{new JObject()
{
{ "id", "abc" },
{ "text", "xyz" },
{ "createdAt", DateTime.Now }
}}, ignoreMissingColumns: false);
await store.UpsertAsync(TestTable, new[]{new JObject()
{
{ "id", "abc" },
{ "createdAt", new DateTime(200,1,1) }
}}, ignoreMissingColumns: false);
JObject result = await store.LookupAsync(TestTable, "abc");
Assert.AreEqual(result.Value<string>("id"), "abc");
Assert.AreEqual(result.Value<string>("text"), "xyz");
Assert.AreEqual(result.Value<string>("createdAt"), "01/01/0200 00:00:00");
}
long count = TestUtilities.CountRows(TestDbName, TestTable);
Assert.AreEqual(count, 1L);
}
public async Task UpsertAsync_Throws_WhenInsertingRecordsWhichAreTooLarge()
{
TestUtilities.DropTestTable(TestDbName, TestTable);
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
var template = new JObject
{
{ "id", 0 },
};
//SQLite limits us to 999 "parameters" per prepared statement
for (var i = 0; i < 1000; i++)
{
template["column" + i] = "Hello, world";
}
store.DefineTable(TestTable, template);
//create the table
await store.InitializeAsync();
//attempt to insert a couple of items
var item1 = new JObject(template);
item1["id"] = 1;
var item2 = new JObject(template);
item1["id"] = 2;
InvalidOperationException ex = await AssertEx.Throws<InvalidOperationException>(() => store.UpsertAsync(TestTable, new[] { item1, item2 }, ignoreMissingColumns: false));
Assert.AreEqual("The number of fields per entity in an upsert operation is limited to 800.", ex.Message);
}
}
public async Task UpsertAsync_ThenReadAsync_AllTypes()
{
TestUtilities.DropTestTable(TestDbName, TestTable);
// first create a table called todo
using (MobileServiceSQLiteStore store = new MobileServiceSQLiteStore(TestDbName))
{
store.DefineTable(TestTable, JObjectTypes.GetObjectWithAllTypes());
await store.InitializeAsync();
var upserted = new JObject()
{
{ "id", "xyz" },
{ "Object", new JObject() { {"id", "abc"} }},
{ "Array", new JArray() { new JObject(){{"id", 3}} } },
{ "Integer", 123L },
{ "Float", 12.5m },
{ "String", "def" },
{ "Boolean", true },
{ "Date", new DateTime(2003, 5, 6, 4, 5, 1, DateTimeKind.Utc) },
{ "Bytes", new byte[] { 1, 2, 3} },
{ "Guid", new Guid("AB3EB1AB-53CD-4780-928B-A7E1CB7A927C") },
{ "TimeSpan", new TimeSpan(1234) }
};
await store.UpsertAsync(TestTable, new[] { upserted }, false);
var query = new MobileServiceTableQueryDescription(TestTable);
var items = await store.ReadAsync(query) as JArray;
Assert.IsNotNull(items);
Assert.AreEqual(items.Count, 1);
var lookedup = items.First as JObject;
Assert.AreEqual(upserted.ToString(Formatting.None), lookedup.ToString(Formatting.None));
}
}
public async Task UpsertAsync_CanProcessManyRecordsAtOnce()
{
TestUtilities.DropTestTable(TestDbName, TestTable);
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
var template = new JObject
{
{ "id", 0 },
{ "value1", "Hello, world" },
{ "value2", "Hello, world" },
{ "value3", "Hello, world" },
{ "value4", "Hello, world" },
{ "value5", "Hello, world" }
};
store.DefineTable(TestTable, template);
//create the table
await store.InitializeAsync();
//add a whole bunch of items. We want {number of items} * {number of fields} to exceed sqlite's parameter limit
const int insertedItemCount = 500;
var itemsToInsert = Enumerable.Range(1, insertedItemCount)
.Select(id =>
{
var o = new JObject(template);
o["id"] = id;
return o;
})
.ToArray();
//Insert the items
await store.UpsertAsync(TestTable, itemsToInsert, ignoreMissingColumns: false);
JArray records = (JArray)await store.ReadAsync(MobileServiceTableQueryDescription.Parse(TestTable, "$orderby=id"));
//Verify that all 500 records were inserted
Assert.AreEqual(records.Count, insertedItemCount);
//Verify that all fields are intact
for (var i = 0; i < insertedItemCount; i++)
{
Assert.IsTrue(JToken.DeepEquals(itemsToInsert[i], records[i]), "Results retrieved from DB do not match input");
}
}
}
