public async Task ReadAsync_ReadsItems()
{
await PrepareTodoTable();
// insert rows and make sure they are inserted
TestUtilities.ExecuteNonQuery(TestDbName, "INSERT INTO todo (id, __createdAt) VALUES ('abc', 1), ('def', 2), ('ghi', 3)");
long count = TestUtilities.CountRows(TestDbName, TestTable);
Assert.AreEqual(count, 3L);
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
DefineTestTable(store);
await store.InitializeAsync();
var query = MobileServiceTableQueryDescription.Parse(TestTable, "$filter=__createdAt gt 1&$inlinecount=allpages");
JToken item = await store.ReadAsync(query);
Assert.IsNotNull(item);
var results = item["results"].Value <JArray>();
long resultCount = item["count"].Value <long>();
Assert.AreEqual(results.Count, 2);
Assert.AreEqual(resultCount, 2L);
}
}
private static async Task <T> Query <T>(string query) where T : JToken
{
using (MobileServiceSQLiteStore store = await SetupTestTable())
{
return((T)await store.ReadAsync(MobileServiceTableQueryDescription.Parse(TestTable, query)));
}
}
public async Task <IEnumerable <MobileServiceFileMetadata> > GetMetadataAsync(string tableName, string objectId)
{
var query = MobileServiceTableQueryDescription.Parse(FileMetadataTableName, string.Format("$filter=parentDataItemType eq '{0}' and parentDataItemId eq '{1}'", tableName, objectId));
var result = await this.store.ReadAsync(query);
return(result.ToObject <List <MobileServiceFileMetadata> >());
}
public void FormatSelect_InvalidQuery()
{
string odata = "$filter=(2 ! ??)";
var ex = Assert.Throws <MobileServiceODataException>(() => MobileServiceTableQueryDescription.Parse("test", odata));
Assert.Equal("The specified odata query has syntax errors.", ex.Message);
Assert.Equal(3, ex.ErrorPosition);
}
public async Task PurgeAsync(string tableName, MobileServiceTableKind tableKind, string queryId, string query, bool force, CancellationToken cancellationToken)
{
await this.EnsureInitializedAsync();
var table = await this.GetTable(tableName);
var queryDescription = MobileServiceTableQueryDescription.Parse(tableName, query);
var action = new PurgeAction(table, tableKind, queryId, queryDescription, force, this, this.opQueue, this.settings, this.Store, cancellationToken);
await this.ExecuteSyncAction(action);
}
public async Task <JToken> ReadAsync(string tableName, string query)
{
await this.EnsureInitializedAsync();
var queryDescription = MobileServiceTableQueryDescription.Parse(tableName, query);
using (await this.storeQueueLock.ReaderLockAsync())
{
return(await this.Store.ReadAsync(queryDescription));
}
}
public async Task PurgeAsync(string tableName, string itemId)
{
string queryString = string.Format("$filter=parentDataItemType eq '{0}'", tableName);
if (!string.IsNullOrEmpty(itemId))
{
queryString += string.Format(" and parentDataItemId eq '{0}'", itemId);
}
var query = MobileServiceTableQueryDescription.Parse(FileMetadataTableName, queryString);
await this.store.DeleteAsync(query);
}
public async Task PurgeAsync(string tableName, MobileServiceTableKind tableKind, string queryId, string query, bool force, CancellationToken cancellationToken)
{
await this.EnsureInitializedAsync();
var table = await this.GetTable(tableName);
var queryDescription = MobileServiceTableQueryDescription.Parse(tableName, query);
using (var trackedStore = StoreChangeTrackerFactory.CreateTrackedStore(this.Store, StoreOperationSource.LocalPurge, this.storeTrackingOptions, this.client.EventManager, this.settings))
{
var action = new PurgeAction(table, tableKind, queryId, queryDescription, force, this, this.opQueue, this.client.EventManager, this.settings, this.Store, cancellationToken);
await this.ExecuteSyncAction(action);
}
}
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");
}
}
}
private static void TestSqlFormatting(Func <SqlQueryFormatter, Func <string> > action, string odata, string expectedSql, params object[] parameters)
{
var query = MobileServiceTableQueryDescription.Parse("test", odata);
var formatter = new SqlQueryFormatter(query);
string sql = action(formatter)();
Assert.Equal(expectedSql, sql);
Assert.Equal(formatter.Parameters.Count, parameters.Length);
for (int i = 0; i < parameters.Length; i++)
{
string name = "@p" + (i + 1);
Assert.True(formatter.Parameters.ContainsKey(name));
Assert.Equal(parameters[i], formatter.Parameters[name]);
}
}
public async Task DeleteAsync_DeletesTheRow_WhenTheyMatchTheQuery()
{
await PrepareTodoTable();
// insert rows and make sure they are inserted
TestUtilities.ExecuteNonQuery(TestDbName, "INSERT INTO todo (id, __createdAt) VALUES ('abc', 1), ('def', 2), ('ghi', 3)");
long count = TestUtilities.CountRows(TestDbName, TestTable);
Assert.AreEqual(count, 3L);
// delete the row
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
DefineTestTable(store);
await store.InitializeAsync();
var query = MobileServiceTableQueryDescription.Parse(TestTable, "$filter=__createdAt gt 1");
await store.DeleteAsync(query);
}
// 1 row should be left
count = TestUtilities.CountRows(TestDbName, TestTable);
Assert.AreEqual(count, 1L);
}
/// <summary>
/// Pulls all items that match the given query from the associated remote table.
/// </summary>
/// <param name="tableName">The name of table to pull</param>
/// <param name="tableKind">The kind of table</param>
/// <param name="queryId">A string that uniquely identifies this query and is used to keep track of its sync state.</param>
/// <param name="query">An OData query that determines which items to
/// pull from the remote table.</param>
/// <param name="options">An instance of <see cref="MobileServiceRemoteTableOptions"/></param>
/// <param name="parameters">A dictionary of user-defined parameters and values to include in
/// the request URI query string.</param>
/// <param name="relatedTables">
/// List of tables that may have related records that need to be push before this table is pulled down.
/// When no table is specified, all tables are considered related.
/// </param>
/// <param name="reader">An instance of <see cref="MobileServiceObjectReader"/></param>
/// <param name="cancellationToken">The <see cref="System.Threading.CancellationToken"/> token to observe
/// </param>
/// <returns>
/// A task that completes when pull operation has finished.
/// </returns>
public async Task PullAsync(string tableName, MobileServiceTableKind tableKind, string queryId, string query, MobileServiceRemoteTableOptions options, IDictionary <string, string> parameters, IEnumerable <string> relatedTables, MobileServiceObjectReader reader, CancellationToken cancellationToken)
{
await this.EnsureInitializedAsync();
if (parameters != null)
{
if (parameters.Keys.Any(k => k.Equals(MobileServiceTable.IncludeDeletedParameterName, StringComparison.OrdinalIgnoreCase)))
{
throw new ArgumentException(Resources.Pull_Cannot_Use_Reserved_Key.FormatInvariant(MobileServiceTable.IncludeDeletedParameterName));
}
if (parameters.Keys.Any(k => k.Equals(MobileServiceTable.SystemPropertiesQueryParameterName, StringComparison.OrdinalIgnoreCase)))
{
throw new ArgumentException(Resources.Pull_Cannot_Use_Reserved_Key.FormatInvariant(MobileServiceTable.SystemPropertiesQueryParameterName));
}
}
var table = await this.GetTable(tableName);
var queryDescription = MobileServiceTableQueryDescription.Parse(this.client.ApplicationUri, tableName, query);
// local schema should be same as remote schema otherwise push can't function
if (queryDescription.Selection.Any() || queryDescription.Projections.Any())
{
throw new ArgumentException(Resources.MobileServiceSyncTable_PullWithSelectNotSupported, "query");
}
bool isIncrementalSync = !String.IsNullOrEmpty(queryId);
if (isIncrementalSync)
{
if (queryDescription.Ordering.Any())
{
throw new ArgumentException(Resources.MobileServiceSyncTable_IncrementalPullWithOrderNotAllowed, "query");
}
if (queryDescription.Top.HasValue || queryDescription.Skip.HasValue)
{
throw new ArgumentException(Resources.MobileServiceSyncTable_IncrementalPullWithSkipTopNotSupported, "query");
}
}
if (!options.HasFlag(MobileServiceRemoteTableOptions.OrderBy) && queryDescription.Ordering.Any())
{
throw new ArgumentException(Resources.MobileServiceSyncTable_OrderByNotAllowed, "query");
}
if (!options.HasFlag(MobileServiceRemoteTableOptions.Skip) && queryDescription.Skip.HasValue)
{
throw new ArgumentException(Resources.MobileServiceSyncTable_SkipNotAllowed, "query");
}
if (!options.HasFlag(MobileServiceRemoteTableOptions.Top) && queryDescription.Top.HasValue)
{
throw new ArgumentException(Resources.MobileServiceSyncTable_TopNotAllowed, "query");
}
// let us not burden the server to calculate the count when we don't need it for pull
queryDescription.IncludeTotalCount = false;
var action = new PullAction(table, tableKind, this, queryId, queryDescription, parameters, relatedTables, this.opQueue, this.settings, this.Store, options, reader, cancellationToken);
await this.ExecuteSyncAction(action);
}
private static async Task <T> Query <T>(MobileServiceSQLiteStore store, string tableName, string query) where T : JToken
{
return((T)await store.ReadAsync(MobileServiceTableQueryDescription.Parse(tableName, query)));
}
public void Parse_UnescapesThe_Uri()
{
var desc = MobileServiceTableQueryDescription.Parse("someTable", EscapedODataString);
var and1 = desc.Filter as BinaryOperatorNode;
Assert.IsNotNull(and1);
Assert.AreEqual(and1.OperatorKind, BinaryOperatorKind.And);
var expectedDateTime = new DateTimeOffset(2014, 4, 4, 7, 0, 0, TimeSpan.Zero);
var gt1 = and1.LeftOperand as BinaryOperatorNode;
Assert.IsNotNull(gt1);
Assert.AreEqual(gt1.OperatorKind, BinaryOperatorKind.GreaterThan);
var updatedAt1 = gt1.LeftOperand as MemberAccessNode;
Assert.IsNotNull(updatedAt1);
Assert.AreEqual(updatedAt1.MemberName, "updatedat");
var datetime1 = gt1.RightOperand as ConstantNode;
Assert.IsNotNull(datetime1);
Assert.AreEqual(datetime1.Value, expectedDateTime);
var and2 = and1.RightOperand as BinaryOperatorNode;
Assert.IsNotNull(and2);
Assert.AreEqual(and2.OperatorKind, BinaryOperatorKind.And);
var gt2 = and2.LeftOperand as BinaryOperatorNode;
Assert.IsNotNull(gt2);
Assert.AreEqual(gt2.OperatorKind, BinaryOperatorKind.GreaterThan);
var updatedAt2 = gt2.LeftOperand as MemberAccessNode;
Assert.IsNotNull(updatedAt2);
Assert.AreEqual(updatedAt2.MemberName, "someDate");
var datetime2 = gt2.RightOperand as ConstantNode;
Assert.IsNotNull(datetime2);
//Note - shouldn't the OData value be parsed as UTC?
Assert.AreEqual(datetime2.Value, expectedDateTime.LocalDateTime);
var startswith = and2.RightOperand as FunctionCallNode;
Assert.IsNotNull(startswith);
Assert.AreEqual(startswith.Arguments.Count, 2);
var text = startswith.Arguments[0] as MemberAccessNode;
Assert.IsNotNull(text);
Assert.AreEqual(text.MemberName, "text");
var value = startswith.Arguments[1] as ConstantNode;
Assert.IsNotNull(value);
Assert.AreEqual(value.Value, "this&'%%=,?#");
}
public void Parse_DoesNotThrow_OnIncompleteQuery()
{
var desc = MobileServiceTableQueryDescription.Parse("someTable", "$select&");
}
/// <summary>
/// Pulls all items that match the given query from the associated remote table.
/// </summary>
/// <param name="tableName">The name of table to pull</param>
/// <param name="tableKind">The kind of table</param>
/// <param name="queryId">A string that uniquely identifies this query and is used to keep track of its sync state.</param>
/// <param name="query">An OData query that determines which items to
/// pull from the remote table.</param>
/// <param name="options">An instance of <see cref="MobileServiceRemoteTableOptions"/></param>
/// <param name="parameters">A dictionary of user-defined parameters and values to include in
/// the request URI query string.</param>
/// <param name="relatedTables">
/// List of tables that may have related records that need to be push before this table is pulled down.
/// When no table is specified, all tables are considered related.
/// </param>
/// <param name="reader">An instance of <see cref="MobileServiceObjectReader"/></param>
/// <param name="cancellationToken">The <see cref="System.Threading.CancellationToken"/> token to observe
/// </param>
/// <param name="pullOptions">
/// PullOptions that determine how to pull data from the remote table
/// </param>
/// <returns>
/// A task that completes when pull operation has finished.
/// </returns>
public async Task PullAsync(string tableName, MobileServiceTableKind tableKind, string queryId, string query, MobileServiceRemoteTableOptions options, IDictionary <string, string> parameters, IEnumerable <string> relatedTables, MobileServiceObjectReader reader, CancellationToken cancellationToken, PullOptions pullOptions)
{
await this.EnsureInitializedAsync();
if (parameters != null)
{
if (parameters.Keys.Any(k => k.Equals(MobileServiceTable.IncludeDeletedParameterName, StringComparison.OrdinalIgnoreCase)))
{
throw new ArgumentException("The key '{0}' is reserved and cannot be specified as a query parameter.".FormatInvariant(MobileServiceTable.IncludeDeletedParameterName));
}
}
var table = await this.GetTable(tableName);
var queryDescription = MobileServiceTableQueryDescription.Parse(this.client.MobileAppUri, tableName, query);
// local schema should be same as remote schema otherwise push can't function
if (queryDescription.Selection.Any() || queryDescription.Projections.Any())
{
throw new ArgumentException("Pull query with select clause is not supported.", "query");
}
bool isIncrementalSync = !String.IsNullOrEmpty(queryId);
if (isIncrementalSync)
{
if (queryDescription.Ordering.Any())
{
throw new ArgumentException("Incremental pull query must not have orderby clause.", "query");
}
if (queryDescription.Top.HasValue || queryDescription.Skip.HasValue)
{
throw new ArgumentException("Incremental pull query must not have skip or top specified.", "query");
}
}
if (!options.HasFlag(MobileServiceRemoteTableOptions.OrderBy) && queryDescription.Ordering.Any())
{
throw new ArgumentException("The supported table options does not include orderby.", "query");
}
if (!options.HasFlag(MobileServiceRemoteTableOptions.Skip) && queryDescription.Skip.HasValue)
{
throw new ArgumentException("The supported table options does not include skip.", "query");
}
if (!options.HasFlag(MobileServiceRemoteTableOptions.Top) && queryDescription.Top.HasValue)
{
throw new ArgumentException("The supported table options does not include top.", "query");
}
// let us not burden the server to calculate the count when we don't need it for pull
queryDescription.IncludeTotalCount = false;
using (var store = StoreChangeTrackerFactory.CreateTrackedStore(this.Store, StoreOperationSource.ServerPull, this.storeTrackingOptions, this.client.EventManager, this.settings))
{
var action = new PullAction(table, tableKind, this, queryId, queryDescription, parameters, relatedTables,
this.opQueue, this.settings, store, options, pullOptions, reader, cancellationToken);
await this.ExecuteSyncAction(action);
}
}
public void ReadAsync_Throws_WhenStoreIsNotInitialized()
{
TestStoreThrowOnUninitialized(store => store.ReadAsync(MobileServiceTableQueryDescription.Parse("abc", "")));
}
