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", ""))); }