public async Task<UserDetail> InsertUser(UserDetail user) { UserDetail userDocument = null; try { SqlQuerySpec query = new SqlQuerySpec("SELECT u.id FROM Users u WHERE u.Email = @email or u.Username = @username"); query.Parameters = new SqlParameterCollection(); query.Parameters.Add(new SqlParameter("@email", user.Email)); query.Parameters.Add(new SqlParameter("@username", user.Username)); var document = _clientDb.CreateDocumentQuery("dbs/LanguageExchange/colls/Users", query); var retrievedUser = document.AsEnumerable().FirstOrDefault(); if (retrievedUser == null) { var dment = await _clientDb.CreateDocumentAsync("dbs/LanguageExchange/colls/Users", user); userDocument = (dynamic)dment.Resource; } else { throw new Exception("Duplicate username or email address!"); } } catch(Exception e) { throw new Exception(e.Message); } return userDocument; }
public async Task<IEnumerable<ScampResourceGroup>> GetGroupsByUser(string userId) { if (!(await docdb.IsInitialized)) return null; // Want to be able to write this in LINQ (but get a runtime exception): //var groups = // (from grp in docdb.Client.CreateDocumentQuery<ScampResourceGroup>(docdb.Collection.SelfLink) // where grp.Type == "group" // from admin in grp.Admins // where admin.Id == user.Id // select grp) // .ToList(); var sql = new SqlQuerySpec { QueryText = " SELECT VALUE g" + " FROM groups g " + " JOIN admin in g.admins" + " WHERE g.type = 'group'" + " AND admin.id = @adminId", Parameters = new SqlParameterCollection { new SqlParameter { Name = "@adminId", Value = userId } } }; var query = docdb.Client.CreateDocumentQuery<ScampResourceGroup>(docdb.Collection.SelfLink, sql); return await query.AsDocumentQuery().ToListAsync(); }
public static IQueryable<dynamic> GetOneRest(string user, string Id) { var querySpec = new SqlQuerySpec() { QueryText = "SELECT * FROM Restaurant r WHERE r.id = @id", Parameters = new SqlParameterCollection { new SqlParameter { Name = "@id", Value = Id } } }; return Client.CreateDocumentQuery<dynamic>(Collection.DocumentsLink, querySpec); }
private static void Main(string[] args) { var client = new DocumentClient(new Uri("https://url.com:443/"), "**********"); var database = GetDatabase(client); var documentCollection = GetDocumentCollection(client, database); Test( "Via Linq lambda", new[] { "en-GB", "nl-NL" }, () => { var documentQuery = client .CreateDocumentQuery<Document>(documentCollection.SelfLink) .Where(x => x.Created > LastYear); Console.WriteLine(" {0}", documentQuery); documentQuery.ToList(); }); Test( "Via SqlQuerySpec query", new[] { "en-GB", "nl-NL" }, () => { var sqlQuerySpec = new SqlQuerySpec("SELECT * FROM root WHERE (root.created > @lastYear)"); sqlQuerySpec.Parameters = new SqlParameterCollection(); sqlQuerySpec.Parameters.Add(new SqlParameter("@lastYear", LastYear)); var documentQuery = client.CreateDocumentQuery(documentCollection.SelfLink, sqlQuerySpec); Console.WriteLine(" {0}", documentQuery); documentQuery.ToList(); }); Test( "Via custom generated query", new[] { "en-GB", "nl-NL" }, () => { var query = $"SELECT * FROM root WHERE (root.created > {LastYear:E10})"; var documentQuery = client.CreateDocumentQuery(documentCollection.SelfLink, query); Console.WriteLine(" {0}", documentQuery); documentQuery.ToList(); }); Console.ReadLine(); }
// This will be expanded to pass in a user and get only those users entries. public static IQueryable<dynamic> GetRestaurants(string user) { var querySpec = new SqlQuerySpec() { QueryText = "SELECT * FROM Restaurant r WHERE r.userId = @userId", Parameters = new SqlParameterCollection{ new SqlParameter { Name = "@userId", Value = user } } }; return Client.CreateDocumentQuery<dynamic>(Collection.DocumentsLink, querySpec); //return Client.CreateDocumentQuery(Collection.DocumentsLink, "SELECT * FROM Restaurant"); }
// Get a single user information if the userName and password match. public static IQueryable<dynamic> GetUser(string userName, string password) { var querySpec = new SqlQuerySpec() { QueryText = "SELECT u.userName, u.password FROM UserInfo u WHERE u.userName = @username AND u.password = @password", Parameters = new SqlParameterCollection { new SqlParameter { Name = "@username", Value = userName }, new SqlParameter { Name = "@password", Value = password } } }; return Client.CreateDocumentQuery<dynamic>(Collection.DocumentsLink, querySpec); }
public StatsSensorState GetLatestStatsRecord(Guid clientId, int sensorId) { var _query = new SqlQuerySpec() { QueryText = "SELECT TOP 1 * FROM s WHERE s.clientId = @clientId AND s.sensorId = @sensorId ORDER BY s.timeStampHourResolution DESC", Parameters = new SqlParameterCollection { new SqlParameter("@clientId", clientId), new SqlParameter("@sensorId", sensorId) } }; return _client.CreateDocumentQuery<StatsSensorState>(_statsCollectionUri, _query) .ToList() .SingleOrDefault(); }
public Client GetClient(Guid clientId) { if (clientId == Guid.Empty) throw new ArgumentOutOfRangeException(nameof(clientId)); var _query = new SqlQuerySpec() { QueryText = "SELECT * FROM c WHERE c.isDisabled = false AND c.id = @clientId", Parameters = new SqlParameterCollection { new SqlParameter("@clientId", clientId) } }; return _client .CreateDocumentQuery<Client>(_clientCollectionLink, _query) .ToList() .SingleOrDefault(); }
static void Main(string[] args) { var query = "SELECT * FROM c"; using (var client = new DocumentClient(new Uri(endpoint), authKey)) { var collLink = UriFactory.CreateDocumentCollectionUri(databaseId, collectionId); var querySpec = new SqlQuerySpec { QueryText = query }; var itr = client.CreateDocumentQuery(collLink, querySpec).AsDocumentQuery(); var response = itr.ExecuteNextAsync<Document>().Result; var charge = response.RequestCharge; Console.WriteLine("Request charge: {0}", charge); foreach (var doc in response.AsEnumerable()) { Console.WriteLine(doc.ToString()); } } Console.ReadLine(); }
/// <summary> /// Initializes a new instance of the ItemProducerTree class. /// </summary> /// <param name="queryContext">query context.</param> /// <param name="querySpecForInit">query spec init.</param> /// <param name="partitionKeyRange">The partition key range.</param> /// <param name="produceAsyncCompleteCallback">Callback to invoke once a fetch finishes.</param> /// <param name="itemProducerTreeComparer">Comparer to determine, which tree to produce from.</param> /// <param name="equalityComparer">Comparer to see if we need to return the continuation token for a partition.</param> /// <param name="testSettings">Test flags.</param> /// <param name="deferFirstPage">Whether or not to defer fetching the first page.</param> /// <param name="collectionRid">The collection to drain from.</param> /// <param name="initialPageSize">The initial page size.</param> /// <param name="initialContinuationToken">The initial continuation token.</param> public ItemProducerTree( CosmosQueryContext queryContext, SqlQuerySpec querySpecForInit, Documents.PartitionKeyRange partitionKeyRange, ProduceAsyncCompleteDelegate produceAsyncCompleteCallback, IComparer <ItemProducerTree> itemProducerTreeComparer, IEqualityComparer <CosmosElement> equalityComparer, TestInjections testSettings, bool deferFirstPage, string collectionRid, long initialPageSize = 50, string initialContinuationToken = null) { if (queryContext == null) { throw new ArgumentNullException($"{nameof(queryContext)}"); } if (itemProducerTreeComparer == null) { throw new ArgumentNullException($"{nameof(itemProducerTreeComparer)}"); } if (produceAsyncCompleteCallback == null) { throw new ArgumentNullException($"{nameof(produceAsyncCompleteCallback)}"); } if (itemProducerTreeComparer == null) { throw new ArgumentNullException($"{nameof(itemProducerTreeComparer)}"); } if (equalityComparer == null) { throw new ArgumentNullException($"{nameof(equalityComparer)}"); } if (string.IsNullOrEmpty(collectionRid)) { throw new ArgumentException($"{nameof(collectionRid)} can not be null or empty."); } this.Root = new ItemProducer( queryContext, querySpecForInit, partitionKeyRange, (itemsBuffered, resourceUnitUsage, requestLength, token) => produceAsyncCompleteCallback(this, itemsBuffered, resourceUnitUsage, requestLength, token), equalityComparer, testSettings, initialPageSize, initialContinuationToken); this.queryClient = queryContext.QueryClient; this.children = new PriorityQueue <ItemProducerTree>(itemProducerTreeComparer, true); this.deferFirstPage = deferFirstPage; this.collectionRid = collectionRid; this.createItemProducerTreeCallback = ItemProducerTree.CreateItemProducerTreeCallback( queryContext, querySpecForInit, produceAsyncCompleteCallback, itemProducerTreeComparer, equalityComparer, testSettings, deferFirstPage, collectionRid, initialPageSize); this.executeWithSplitProofingSemaphore = new SemaphoreSlim(1, 1); }
public IList<Client> GetClientsByTag(string tagName) { if (String.IsNullOrEmpty(tagName)) throw new ArgumentNullException(nameof(tagName)); var _query = new SqlQuerySpec() { QueryText = "SELECT VALUE c " + "FROM Clients c " + "JOIN t IN c.tags " + "WHERE t = @tagName", Parameters = new SqlParameterCollection { new SqlParameter("@tagName", tagName) } }; return _client .CreateDocumentQuery<Client>(_clientCollectionLink, _query) .ToList(); }
/// <summary> /// Get resources for a specific group member /// </summary> /// <param name="groupId"></param> /// <param name="userId"></param> /// <returns></returns> public async Task<IEnumerable<ScampUserGroupResources>> GetGroupMemberResources(string groupId, string userId) { if (!(await docdb.IsInitialized)) return null; try { // get resources for the specified user and group //var query = docdb.Client.CreateDocumentQuery<ScampResourceGroup>(docdb.Collection.SelfLink) // .Select(g => g.Members // .Where(group => group.Id == groupId) // .Select(mbrs => mbrs.Resources // .Where(members => members.Id == userId) // .SelectMany(resources => mbrs.Resources))); var sql = new SqlQuerySpec { QueryText = "SELECT value rcs from groups" + " JOIN mbrs in groups.members" + " JOIN rcs in mbrs.resources" + " where groups.id = @groupId" + " and mbrs.id = @userId", Parameters = new SqlParameterCollection { new SqlParameter { Name = "@groupId", Value = groupId }, new SqlParameter { Name = "@userId", Value = userId } } }; var query = docdb.Client.CreateDocumentQuery<ScampUserGroupResources>(docdb.Collection.SelfLink, sql); return await query.AsDocumentQuery().ToListAsync(); } catch (Exception ex) { throw; } }
public StatsSensorStates GetHourlyStats(Guid clientId, int sensorId, DateTime from, DateTime to) { var _query = new SqlQuerySpec() { QueryText = $"SELECT s.states FROM s " + "WHERE s.clientId = @clientId " + "AND s.sensorId = @sensorId " + "AND s.timeStampHourResolution >= @from " + "AND s.timeStampHourResolution <= @to", Parameters = new SqlParameterCollection { new SqlParameter("@clientId", clientId), new SqlParameter("@sensorId", sensorId), new SqlParameter("@from", from), new SqlParameter("@to", to) } }; var result = _client .CreateDocumentQuery<StatsSensor>(_statsCollectionLink, _query) .ToList(); if (result.Count == 0) return null; else return result.Aggregate((a, b) => { foreach (var stateKey in a.States.Keys.ToList()) a.States[stateKey] += b.States[stateKey]; return a; }).States; }
public CosmosQueryExecutionContextFactory( CosmosQueryClient client, ResourceType resourceTypeEnum, OperationType operationType, Type resourceType, SqlQuerySpec sqlQuerySpec, CosmosQueryRequestOptions queryRequestOptions, Uri resourceLink, bool isContinuationExpected, bool allowNonValueAggregateQuery, Guid correlatedActivityId) { if (client == null) { throw new ArgumentNullException(nameof(client)); } if (sqlQuerySpec == null) { throw new ArgumentNullException(nameof(sqlQuerySpec)); } if (queryRequestOptions == null) { throw new ArgumentNullException(nameof(queryRequestOptions)); } if (resourceLink == null) { throw new ArgumentNullException(nameof(resourceLink)); } // Prevent users from updating the values after creating the execution context. CosmosQueryRequestOptions cloneQueryRequestOptions = queryRequestOptions.Clone(); // Swapping out negative values in feedOptions for int.MaxValue if (cloneQueryRequestOptions.MaxBufferedItemCount.HasValue && cloneQueryRequestOptions.MaxBufferedItemCount < 0) { cloneQueryRequestOptions.MaxBufferedItemCount = int.MaxValue; } if (cloneQueryRequestOptions.MaxConcurrency.HasValue && cloneQueryRequestOptions.MaxConcurrency < 0) { cloneQueryRequestOptions.MaxConcurrency = int.MaxValue; } if (cloneQueryRequestOptions.MaxItemCount.HasValue && cloneQueryRequestOptions.MaxItemCount < 0) { cloneQueryRequestOptions.MaxItemCount = int.MaxValue; } this.cosmosQueryContext = new CosmosQueryContext( client: client, resourceTypeEnum: resourceTypeEnum, operationType: operationType, resourceType: resourceType, sqlQuerySpecFromUser: sqlQuerySpec, queryRequestOptions: cloneQueryRequestOptions, resourceLink: resourceLink, getLazyFeedResponse: isContinuationExpected, isContinuationExpected: isContinuationExpected, allowNonValueAggregateQuery: allowNonValueAggregateQuery, correlatedActivityId: correlatedActivityId); }
public void SqlQuerySpecSerializationTest() { Action <string, SqlQuerySpec> verifyJsonSerialization = (expectedText, query) => { string actualText = JsonConvert.SerializeObject(query); Assert.AreEqual(expectedText, actualText); SqlQuerySpec otherQuery = JsonConvert.DeserializeObject <SqlQuerySpec>(actualText); string otherText = JsonConvert.SerializeObject(otherQuery); Assert.AreEqual(expectedText, otherText); }; Action <string> verifyJsonSerializationText = (text) => { SqlQuerySpec query = JsonConvert.DeserializeObject <SqlQuerySpec>(text); string otherText = JsonConvert.SerializeObject(query); Assert.AreEqual(text, otherText); }; // Verify serialization verifyJsonSerialization("{\"query\":null}", new SqlQuerySpec()); verifyJsonSerialization("{\"query\":\"SELECT 1\"}", new SqlQuerySpec("SELECT 1")); verifyJsonSerialization("{\"query\":\"SELECT 1\",\"parameters\":[{\"name\":null,\"value\":null}]}", new SqlQuerySpec() { QueryText = "SELECT 1", Parameters = new SqlParameterCollection() { new SqlParameter() } }); verifyJsonSerialization("{\"query\":\"SELECT 1\",\"parameters\":[" + "{\"name\":\"@p1\",\"value\":5}" + "]}", new SqlQuerySpec() { QueryText = "SELECT 1", Parameters = new SqlParameterCollection() { new SqlParameter("@p1", 5) } }); verifyJsonSerialization("{\"query\":\"SELECT 1\",\"parameters\":[" + "{\"name\":\"@p1\",\"value\":5}," + "{\"name\":\"@p1\",\"value\":true}" + "]}", new SqlQuerySpec() { QueryText = "SELECT 1", Parameters = new SqlParameterCollection() { new SqlParameter("@p1", 5), new SqlParameter("@p1", true) } }); verifyJsonSerialization("{\"query\":\"SELECT 1\",\"parameters\":[" + "{\"name\":\"@p1\",\"value\":\"abc\"}" + "]}", new SqlQuerySpec() { QueryText = "SELECT 1", Parameters = new SqlParameterCollection() { new SqlParameter("@p1", "abc") } }); verifyJsonSerialization("{\"query\":\"SELECT 1\",\"parameters\":[" + "{\"name\":\"@p1\",\"value\":[1,2,3]}" + "]}", new SqlQuerySpec() { QueryText = "SELECT 1", Parameters = new SqlParameterCollection() { new SqlParameter("@p1", new int[] { 1, 2, 3 }) } }); verifyJsonSerialization("{\"query\":\"SELECT 1\",\"parameters\":[" + "{\"name\":\"@p1\",\"value\":{\"a\":[1,2,3]}}" + "]}", new SqlQuerySpec() { QueryText = "SELECT 1", Parameters = new SqlParameterCollection() { new SqlParameter("@p1", JObject.Parse("{\"a\":[1,2,3]}")) } }); verifyJsonSerialization("{\"query\":\"SELECT 1\",\"parameters\":[" + "{\"name\":\"@p1\",\"value\":{\"a\":[1,2,3]}}" + "]}", new SqlQuerySpec() { QueryText = "SELECT 1", Parameters = new SqlParameterCollection() { new SqlParameter("@p1", new JRaw("{\"a\":[1,2,3]}")) } }); // Verify roundtrips verifyJsonSerializationText("{\"query\":null}"); verifyJsonSerializationText("{\"query\":\"SELECT 1\"}"); verifyJsonSerializationText( "{" + "\"query\":\"SELECT 1\"," + "\"parameters\":[" + "{\"name\":null,\"value\":null}" + "]" + "}"); verifyJsonSerializationText( "{" + "\"query\":\"SELECT 1\"," + "\"parameters\":[" + "{\"name\":\"@p1\",\"value\":null}" + "]" + "}"); verifyJsonSerializationText( "{" + "\"query\":\"SELECT 1\"," + "\"parameters\":[" + "{\"name\":\"@p1\",\"value\":true}" + "]" + "}"); verifyJsonSerializationText( "{" + "\"query\":\"SELECT 1\"," + "\"parameters\":[" + "{\"name\":\"@p1\",\"value\":false}" + "]" + "}"); verifyJsonSerializationText( "{" + "\"query\":\"SELECT 1\"," + "\"parameters\":[" + "{\"name\":\"@p1\",\"value\":123}" + "]" + "}"); verifyJsonSerializationText( "{" + "\"query\":\"SELECT 1\"," + "\"parameters\":[" + "{\"name\":\"@p1\",\"value\":\"abc\"}" + "]" + "}"); verifyJsonSerializationText( "{" + "\"query\":\"SELECT 1\"," + "\"parameters\":[" + "{\"name\":\"@p1\",\"value\":{\"a\":[1,2,\"abc\"]}}" + "]" + "}"); }
/// <summary> /// Run a query that returns a single document, and display it /// </summary> /// <param name="collectionLink">The collection self-link</param> /// <param name="query">The query to run</param> private static void QueryScalar(string collectionLink, SqlQuerySpec query) { dynamic result = client.CreateDocumentQuery(collectionLink, query).AsDocumentQuery().ExecuteNextAsync().Result.First(); Console.WriteLine(JsonConvert.SerializeObject(result, Formatting.None)); }
public DocumentServiceRequest CreateDocumentServiceRequest(INameValueCollection requestHeaders, SqlQuerySpec querySpec, PartitionKeyInternal partitionKey) { DocumentServiceRequest request = this.CreateDocumentServiceRequest(requestHeaders, querySpec); this.PopulatePartitionKeyInfo(request, partitionKey); request.Properties = this.feedOptions.Properties; return(request); }
private DocumentServiceRequest CreateDocumentServiceRequest(INameValueCollection requestHeaders, SqlQuerySpec querySpec) { DocumentServiceRequest request = querySpec != null? this.CreateQueryDocumentServiceRequest(requestHeaders, querySpec) : this.CreateReadFeedDocumentServiceRequest(requestHeaders); if (this.feedOptions.JsonSerializerSettings != null) { request.SerializerSettings = this.feedOptions.JsonSerializerSettings; } return(request); }
internal TryCatch <PartitionedQueryExecutionInfoInternal> TryGetPartitionedQueryExecutionInfoInternal( SqlQuerySpec querySpec, PartitionKeyDefinition partitionKeyDefinition, bool requireFormattableOrderByQuery, bool isContinuationExpected, bool allowNonValueAggregateQuery, bool hasLogicalPartitionKey) { if (querySpec == null || partitionKeyDefinition == null) { return(TryCatch <PartitionedQueryExecutionInfoInternal> .FromResult(DefaultInfoInternal)); } string queryText = JsonConvert.SerializeObject(querySpec); List <string> paths = new List <string>(partitionKeyDefinition.Paths); List <string[]> pathParts = new List <string[]>(); paths.ForEach(path => { pathParts.Add(PathParser.GetPathParts(path)); }); string[] allParts = pathParts.SelectMany(parts => parts).ToArray(); uint[] partsLengths = pathParts.Select(parts => (uint)parts.Length).ToArray(); PartitionKind partitionKind = partitionKeyDefinition.Kind; this.Initialize(); byte[] buffer = new byte[InitialBufferSize]; uint errorCode; uint serializedQueryExecutionInfoResultLength; unsafe { fixed(byte *bytePtr = buffer) { errorCode = ServiceInteropWrapper.GetPartitionKeyRangesFromQuery( this.serviceProvider, queryText, requireFormattableOrderByQuery, isContinuationExpected, allowNonValueAggregateQuery, hasLogicalPartitionKey, allParts, partsLengths, (uint)partitionKeyDefinition.Paths.Count, partitionKind, new IntPtr(bytePtr), (uint)buffer.Length, out serializedQueryExecutionInfoResultLength); if (errorCode == DISP_E_BUFFERTOOSMALL) { buffer = new byte[serializedQueryExecutionInfoResultLength]; fixed(byte *bytePtr2 = buffer) { errorCode = ServiceInteropWrapper.GetPartitionKeyRangesFromQuery( this.serviceProvider, queryText, requireFormattableOrderByQuery, isContinuationExpected, allowNonValueAggregateQuery, hasLogicalPartitionKey, // has logical partition key allParts, partsLengths, (uint)partitionKeyDefinition.Paths.Count, partitionKind, new IntPtr(bytePtr2), (uint)buffer.Length, out serializedQueryExecutionInfoResultLength); } } } } string serializedQueryExecutionInfo = Encoding.UTF8.GetString(buffer, 0, (int)serializedQueryExecutionInfoResultLength); Exception exception = Marshal.GetExceptionForHR((int)errorCode); if (exception != null) { QueryPartitionProviderException queryPartitionProviderException; if (string.IsNullOrEmpty(serializedQueryExecutionInfo)) { queryPartitionProviderException = new UnexpectedQueryPartitionProviderException( "Query service interop parsing hit an unexpected exception", exception); } else { queryPartitionProviderException = new ExpectedQueryPartitionProviderException( serializedQueryExecutionInfo, exception); } return(TryCatch <PartitionedQueryExecutionInfoInternal> .FromException( queryPartitionProviderException)); } PartitionedQueryExecutionInfoInternal queryInfoInternal = JsonConvert.DeserializeObject <PartitionedQueryExecutionInfoInternal>( serializedQueryExecutionInfo, new JsonSerializerSettings { DateParseHandling = DateParseHandling.None }); return(TryCatch <PartitionedQueryExecutionInfoInternal> .FromResult(queryInfoInternal)); }
private async Task <TryCatch> TryInitializeAsync( SqlQuerySpec sqlQuerySpec, CosmosElement requestContinuation, string collectionRid, IReadOnlyList <PartitionKeyRange> partitionKeyRanges, int initialPageSize, IReadOnlyList <OrderByColumn> orderByColumns, CancellationToken cancellationToken) { if (sqlQuerySpec == null) { throw new ArgumentNullException(nameof(sqlQuerySpec)); } if (collectionRid == null) { throw new ArgumentNullException(nameof(collectionRid)); } if (partitionKeyRanges == null) { throw new ArgumentNullException(nameof(partitionKeyRanges)); } if (orderByColumns == null) { throw new ArgumentNullException(nameof(orderByColumns)); } cancellationToken.ThrowIfCancellationRequested(); if (requestContinuation == null) { // Start off all the partition key ranges with null continuation SqlQuerySpec rewrittenQueryForOrderBy = new SqlQuerySpec( sqlQuerySpec.QueryText.Replace(oldValue: FormatPlaceHolder, newValue: True), sqlQuerySpec.Parameters); Dictionary <PartitionKeyRange, string> partitionKeyRangeToContinuationToken = new Dictionary <PartitionKeyRange, string>(); foreach (PartitionKeyRange partitionKeyRange in partitionKeyRanges) { partitionKeyRangeToContinuationToken.Add(key: partitionKeyRange, value: null); } return(await base.TryInitializeAsync( collectionRid, initialPageSize, rewrittenQueryForOrderBy, partitionKeyRangeToContinuationToken, deferFirstPage : false, filter : null, tryFilterAsync : null, cancellationToken)); } TryCatch <PartitionMapping <OrderByContinuationToken> > tryGetOrderByContinuationTokenMapping = TryGetOrderByContinuationTokenMapping( partitionKeyRanges, requestContinuation, orderByColumns.Count); if (!tryGetOrderByContinuationTokenMapping.Succeeded) { return(TryCatch.FromException(tryGetOrderByContinuationTokenMapping.Exception)); } IReadOnlyList <CosmosElement> orderByItems = tryGetOrderByContinuationTokenMapping .Result .TargetPartition .Values .First() .OrderByItems .Select(x => x.Item) .ToList(); if (orderByItems.Count != orderByColumns.Count) { return(TryCatch.FromException( new MalformedContinuationTokenException($"Order By Items from continuation token did not match the query text. Order by item count: {orderByItems.Count()} did not match column count {orderByColumns.Count()}. Continuation token: {requestContinuation}"))); } ReadOnlyMemory <(OrderByColumn, CosmosElement)> columnAndItems = orderByColumns.Zip(orderByItems, (column, item) => (column, item)).ToArray(); // For ascending order-by, left of target partition has filter expression > value, // right of target partition has filter expression >= value, // and target partition takes the previous filter from continuation (or true if no continuation) (string leftFilter, string targetFilter, string rightFilter) = CosmosOrderByItemQueryExecutionContext.GetFormattedFilters(columnAndItems); List <(IReadOnlyDictionary <PartitionKeyRange, OrderByContinuationToken>, string)> tokenMappingAndFilters = new List <(IReadOnlyDictionary <PartitionKeyRange, OrderByContinuationToken>, string)>() { { (tryGetOrderByContinuationTokenMapping.Result.PartitionsLeftOfTarget, leftFilter) }, { (tryGetOrderByContinuationTokenMapping.Result.TargetPartition, targetFilter) },
public static async Task <AnkerData> GetItemsAsync(AnkerhRequest request, int pageSize, string responseContinutationToken) { // Get a Database by querying for it by id Database db = client.CreateDatabaseQuery() .Where(d => d.Id == DatabaseId) .AsEnumerable() .Single(); // Use that Database's SelfLink to query for a DocumentCollection by id DocumentCollection coll = client.CreateDocumentCollectionQuery(db.SelfLink) .Where(c => c.Id == CollectionId) .AsEnumerable() .Single(); StringBuilder sqlQuery = new StringBuilder("SELECT c.id,c.BilagsNummer, c.Sagsnummer,c.SagsNavn,c.AfsnitTekst,c.AktivitetTekst,c.Kreditornummer,c.Kreditornavn,c.Filnavn,c.Fakturadato FROM c"); StringBuilder filters = new StringBuilder(); var parms = new SqlParameterCollection(); if (request.StartDate.HasValue && request.EndDate.HasValue) { filters.Append(" (c.Fakturadato between @startDate and @endDate)"); parms.Add(new SqlParameter("@startDate", request.StartDate)); parms.Add(new SqlParameter("@endDate", request.EndDate.Value.AddDays(1))); } if (!string.IsNullOrEmpty(request.BilagsNummer)) { filters = filters.Length > 0 ? filters.Append(" AND ") : filters; filters.Append(" (CONTAINS( c.BilagsNummer,@BilagsNummer))"); parms.Add(new SqlParameter("@BilagsNummer", request.BilagsNummer)); } if (!string.IsNullOrEmpty(request.Sagsnummer)) { filters = filters.Length > 0 ? filters.Append(" AND ") : filters; filters.Append(" (CONTAINS( c.Sagsnummer,@Sagsnummer))"); parms.Add(new SqlParameter("@Sagsnummer", request.Sagsnummer)); } if (!string.IsNullOrEmpty(request.SagsNavn)) { filters = filters.Length > 0 ? filters.Append(" AND ") : filters; filters.Append(" (CONTAINS( c.SagsNavn,@SagsNavn))"); parms.Add(new SqlParameter("@SagsNavn", request.SagsNavn)); } if (!string.IsNullOrEmpty(request.AfsnitTekst)) { filters = filters.Length > 0 ? filters.Append(" AND ") : filters; filters.Append(" (CONTAINS( c.AfsnitTekst,@AfsnitTekst))"); parms.Add(new SqlParameter("@AfsnitTekst", request.AfsnitTekst)); } if (!string.IsNullOrEmpty(request.AktivitetTekst)) { filters = filters.Length > 0 ? filters.Append(" AND ") : filters; filters.Append(" (CONTAINS( c.AktivitetTekst,@AktivitetTekst))"); parms.Add(new SqlParameter("@AktivitetTekst", request.AktivitetTekst)); } if (!string.IsNullOrEmpty(request.Kreditornummer)) { filters = filters.Length > 0 ? filters.Append(" AND ") : filters; filters.Append(" (CONTAINS( c.Kreditornummer,@Kreditornummer))"); parms.Add(new SqlParameter("@Kreditornummer", request.Kreditornummer)); } if (!string.IsNullOrEmpty(request.Kreditornavn)) { filters = filters.Length > 0 ? filters.Append(" AND ") : filters; filters.Append(" (CONTAINS( c.Kreditornavn,@Kreditornavn))"); parms.Add(new SqlParameter("@Kreditornavn", request.Kreditornavn)); } if (!string.IsNullOrEmpty(request.Filnavn)) { filters = filters.Length > 0 ? filters.Append(" AND ") : filters; filters.Append(" (CONTAINS( c.Filnavn,@Filnavn))"); parms.Add(new SqlParameter("@Filnavn", request.Filnavn)); } if (filters.Length > 0) { sqlQuery.Append(" WHERE"); } sqlQuery.Append(" {0}"); filters.Append(" order by c.Fakturadato desc"); var sqlquery = new SqlQuerySpec { QueryText = string.Format(sqlQuery.ToString(), filters.ToString()), Parameters = parms }; var query = client .CreateDocumentQuery(coll.SelfLink, sqlquery, new FeedOptions { MaxItemCount = pageSize, RequestContinuation = responseContinutationToken }) .AsDocumentQuery(); List <AnkerDTO> results = new List <AnkerDTO>(); if (query.HasMoreResults) { var data = new AnkerData(); var result = await query.ExecuteNextAsync <AnkerDTO>(); data.ContinutationToken = result.ResponseContinuation; data.Items.AddRange(result); return(data); } return(null); }
public static async Task <AnkerData> GetItemsAsync(Expression <Func <T, bool> > predicate, int pageSize, string responseContinutationToken) { // Get a Database by querying for it by id Database db = client.CreateDatabaseQuery() .Where(d => d.Id == DatabaseId) .AsEnumerable() .Single(); // Use that Database's SelfLink to query for a DocumentCollection by id DocumentCollection coll = client.CreateDocumentCollectionQuery(db.SelfLink) .Where(c => c.Id == CollectionId) .AsEnumerable() .Single(); var sql = "SELECT TOP 3 * FROM c"; var d1 = new DateTime(2019, 02, 01); var d2 = new DateTime(2019, 02, 02); var sqlquery = new SqlQuerySpec { QueryText = "SELECT * FROM c WHERE (c.Forfaldsdato between @d1 and @d2) order by c.Forfaldsdato desc", Parameters = new SqlParameterCollection() { new SqlParameter("@d1", d1), new SqlParameter("@d2", d2) } }; var query1 = client .CreateDocumentQuery(coll.SelfLink, sqlquery, new FeedOptions { MaxItemCount = pageSize, RequestContinuation = responseContinutationToken }) .AsDocumentQuery(); while (query1.HasMoreResults) { var documents = await query1.ExecuteNextAsync <AnkerDTO>(); foreach (var document in documents) { //Console.WriteLine(" PublicId: {0}; Magnitude: {1};", document.publicid, // document.magnitude); } } IDocumentQuery <T> query; if (predicate == null) { query = client.CreateDocumentQuery <T>( UriFactory.CreateDocumentCollectionUri(DatabaseId, CollectionId), new FeedOptions { MaxItemCount = pageSize, RequestContinuation = responseContinutationToken }) .AsDocumentQuery(); } else { query = client.CreateDocumentQuery <T>( UriFactory.CreateDocumentCollectionUri(DatabaseId, CollectionId), new FeedOptions { MaxItemCount = pageSize, RequestContinuation = responseContinutationToken }) .Where(predicate) .AsDocumentQuery(); } List <T> results = new List <T>(); if (query.HasMoreResults) { var data = new AnkerData(); var result = await query.ExecuteNextAsync <AnkerDTO>(); data.ContinutationToken = result.ResponseContinuation; data.Items.AddRange(result); return(data); } return(null); }
public static List <ToDoItem> MockSinglePartitionKeyRangeContext( Mock <CosmosQueryClient> mockQueryContext, int[] responseMessagesPageSize, SqlQuerySpec sqlQuerySpec, PartitionKeyRange partitionKeyRange, string continuationToken, int maxPageSize, string collectionRid, TimeSpan?responseDelay, CancellationToken cancellationToken) { // Setup a list of query responses. It generates a new continuation token for each response. This allows the mock to return the messages in the correct order. List <ToDoItem> allItems = new List <ToDoItem>(); string previousContinuationToken = continuationToken; for (int i = 0; i < responseMessagesPageSize.Length; i++) { string newContinuationToken = null; // The last response should have a null continuation token if (i + 1 != responseMessagesPageSize.Length) { newContinuationToken = Guid.NewGuid().ToString(); } (QueryResponse response, IList <ToDoItem> items)queryResponse = QueryResponseMessageFactory.Create( itemIdPrefix: $"page{i}-pk{partitionKeyRange.Id}-", continuationToken: newContinuationToken, collectionRid: collectionRid, itemCount: responseMessagesPageSize[i]); allItems.AddRange(queryResponse.items); mockQueryContext.Setup(x => x.ExecuteItemQueryAsync( It.IsAny <Uri>(), ResourceType.Document, OperationType.Query, collectionRid, It.IsAny <QueryRequestOptions>(), It.Is <SqlQuerySpec>(specInput => IsSqlQuerySpecEqual(sqlQuerySpec, specInput)), previousContinuationToken, It.Is <PartitionKeyRangeIdentity>(rangeId => string.Equals(rangeId.PartitionKeyRangeId, partitionKeyRange.Id) && string.Equals(rangeId.CollectionRid, collectionRid)), It.IsAny <bool>(), maxPageSize, cancellationToken)) .Callback(() => { if (responseDelay.HasValue) { Thread.Sleep(responseDelay.Value); } }) .Returns(Task.FromResult(queryResponse.response)); if (responseMessagesPageSize[i] != QueryResponseMessageFactory.SPLIT) { previousContinuationToken = newContinuationToken; } } return(allItems); }
public static (ItemProducerTree itemProducerTree, ReadOnlyCollection <ToDoItem> allItems) CreateTree( Mock <CosmosQueryContext> mockQueryContext = null, int[] responseMessagesPageSize = null, SqlQuerySpec sqlQuerySpec = null, PartitionKeyRange partitionKeyRange = null, string continuationToken = null, int maxPageSize = 50, bool deferFirstPage = true, string collectionRid = null, IComparer <ItemProducerTree> itemProducerTreeComparer = null, ItemProducerTree.ProduceAsyncCompleteDelegate completeDelegate = null, TimeSpan?responseDelay = null, CancellationToken cancellationToken = default(CancellationToken)) { if (responseMessagesPageSize == null) { responseMessagesPageSize = DefaultResponseSizes; } if (sqlQuerySpec == null) { sqlQuerySpec = DefaultQuerySpec; } if (partitionKeyRange == null) { partitionKeyRange = DefaultPartitionKeyRange; } if (completeDelegate == null) { completeDelegate = DefaultTreeProduceAsyncCompleteDelegate; } if (itemProducerTreeComparer == null) { itemProducerTreeComparer = new ParallelItemProducerTreeComparer(); } if (mockQueryContext == null) { mockQueryContext = new Mock <CosmosQueryContext>(); } mockQueryContext.Setup(x => x.ContainerResourceId).Returns(collectionRid); // Setup a list of query responses. It generates a new continuation token for each response. This allows the mock to return the messages in the correct order. List <ToDoItem> allItems = MockSinglePartitionKeyRangeContext( mockQueryContext, responseMessagesPageSize, sqlQuerySpec, partitionKeyRange, continuationToken, maxPageSize, collectionRid, responseDelay, cancellationToken); ItemProducerTree itemProducerTree = new ItemProducerTree( mockQueryContext.Object, sqlQuerySpec, partitionKeyRange, completeDelegate, itemProducerTreeComparer, CosmosElementEqualityComparer.Value, deferFirstPage, collectionRid, maxPageSize, initialContinuationToken: continuationToken); return(itemProducerTree, allItems.AsReadOnly()); }
internal PartitionedQueryExecutionInfoInternal GetPartitionedQueryExecutionInfoInternal( SqlQuerySpec querySpec, PartitionKeyDefinition partitionKeyDefinition, bool requireFormattableOrderByQuery, bool isContinuationExpected, bool allowNonValueAggregateQuery) { if (querySpec == null || partitionKeyDefinition == null) { return(DefaultInfoInternal); } string queryText = JsonConvert.SerializeObject(querySpec); List <string> paths = new List <string>(partitionKeyDefinition.Paths); List <string[]> pathParts = new List <string[]>(); paths.ForEach(path => { pathParts.Add(PathParser.GetPathParts(path).ToArray()); }); string[] allParts = pathParts.SelectMany(parts => parts).ToArray(); uint[] partsLengths = pathParts.Select(parts => (uint)parts.Length).ToArray(); PartitionKind partitionKind = partitionKeyDefinition.Kind; this.Initialize(); byte[] buffer = new byte[InitialBufferSize]; uint errorCode; uint serializedQueryExecutionInfoResultLength; unsafe { fixed(byte *bytePtr = buffer) { errorCode = ServiceInteropWrapper.GetPartitionKeyRangesFromQuery( this.serviceProvider, queryText, requireFormattableOrderByQuery, isContinuationExpected, allowNonValueAggregateQuery, allParts, partsLengths, (uint)partitionKeyDefinition.Paths.Count, partitionKind, new IntPtr(bytePtr), (uint)buffer.Length, out serializedQueryExecutionInfoResultLength); if (errorCode == DISP_E_BUFFERTOOSMALL) { buffer = new byte[serializedQueryExecutionInfoResultLength]; fixed(byte *bytePtr2 = buffer) { errorCode = ServiceInteropWrapper.GetPartitionKeyRangesFromQuery( this.serviceProvider, queryText, requireFormattableOrderByQuery, isContinuationExpected, allowNonValueAggregateQuery, allParts, partsLengths, (uint)partitionKeyDefinition.Paths.Count, partitionKind, new IntPtr(bytePtr2), (uint)buffer.Length, out serializedQueryExecutionInfoResultLength); } } } } string serializedQueryExecutionInfo = Encoding.UTF8.GetString(buffer, 0, (int)serializedQueryExecutionInfoResultLength); Exception exception = Marshal.GetExceptionForHR((int)errorCode); if (exception != null) { DefaultTrace.TraceInformation("QueryEngineConfiguration: " + this.queryengineConfiguration); throw new BadRequestException( "Message: " + serializedQueryExecutionInfo, exception); } PartitionedQueryExecutionInfoInternal queryInfoInternal = JsonConvert.DeserializeObject <PartitionedQueryExecutionInfoInternal>( serializedQueryExecutionInfo, new JsonSerializerSettings { DateParseHandling = DateParseHandling.None }); return(queryInfoInternal); }
public static QueryIterator Create( CosmosQueryClient client, CosmosClientContext clientContext, SqlQuerySpec sqlQuerySpec, string continuationToken, FeedRangeInternal feedRangeInternal, QueryRequestOptions queryRequestOptions, Uri resourceLink, bool isContinuationExpected, bool allowNonValueAggregateQuery, PartitionedQueryExecutionInfo partitionedQueryExecutionInfo) { if (queryRequestOptions == null) { queryRequestOptions = new QueryRequestOptions(); } CosmosDiagnosticsContext queryPipelineCreationDiagnostics = CosmosDiagnosticsContext.Create(queryRequestOptions); CosmosQueryContextCore cosmosQueryContext = new CosmosQueryContextCore( client: client, queryRequestOptions: queryRequestOptions, resourceTypeEnum: Documents.ResourceType.Document, operationType: Documents.OperationType.Query, resourceType: typeof(QueryResponseCore), resourceLink: resourceLink, isContinuationExpected: isContinuationExpected, allowNonValueAggregateQuery: allowNonValueAggregateQuery, diagnosticsContext: queryPipelineCreationDiagnostics, correlatedActivityId: Guid.NewGuid()); CosmosElement requestContinuationToken; switch (queryRequestOptions.ExecutionEnvironment.GetValueOrDefault(ExecutionEnvironment.Client)) { case ExecutionEnvironment.Client: if (continuationToken != null) { TryCatch <CosmosElement> tryParse = CosmosElement.Monadic.Parse(continuationToken); if (tryParse.Failed) { return(new QueryIterator( cosmosQueryContext, new QueryExecutionContextWithException( new MalformedContinuationTokenException( message: $"Malformed Continuation Token: {continuationToken}", innerException: tryParse.Exception)), queryRequestOptions.CosmosSerializationFormatOptions, queryRequestOptions, clientContext)); } requestContinuationToken = tryParse.Result; } else { requestContinuationToken = null; } break; case ExecutionEnvironment.Compute: requestContinuationToken = queryRequestOptions.CosmosElementContinuationToken; break; default: throw new ArgumentOutOfRangeException($"Unknown {nameof(ExecutionEnvironment)}: {queryRequestOptions.ExecutionEnvironment.Value}."); } CosmosQueryExecutionContextFactory.InputParameters inputParameters = new CosmosQueryExecutionContextFactory.InputParameters( sqlQuerySpec: sqlQuerySpec, initialUserContinuationToken: requestContinuationToken, initialFeedRange: feedRangeInternal, maxConcurrency: queryRequestOptions.MaxConcurrency, maxItemCount: queryRequestOptions.MaxItemCount, maxBufferedItemCount: queryRequestOptions.MaxBufferedItemCount, partitionKey: queryRequestOptions.PartitionKey, properties: queryRequestOptions.Properties, partitionedQueryExecutionInfo: partitionedQueryExecutionInfo, executionEnvironment: queryRequestOptions.ExecutionEnvironment, returnResultsInDeterministicOrder: queryRequestOptions.ReturnResultsInDeterministicOrder, testInjections: queryRequestOptions.TestSettings); return(new QueryIterator( cosmosQueryContext, CosmosQueryExecutionContextFactory.Create(cosmosQueryContext, inputParameters), queryRequestOptions.CosmosSerializationFormatOptions, queryRequestOptions, clientContext)); }
protected async Task RepairContextAsync( string collectionRid, int currentDocumentProducerIndex, Func <DocumentProducer <T>, int> taskPriorityFunc, IReadOnlyList <PartitionKeyRange> replacementRanges, SqlQuerySpec querySpecForRepair, Action callback = null) { CollectionCache collectionCache = await this.Client.GetCollectionCacheAsync(); INameValueCollection requestHeaders = await this.CreateCommonHeadersAsync(this.GetFeedOptions(null)); this.DocumentProducers.Capacity = this.DocumentProducers.Count + replacementRanges.Count - 1; DocumentProducer <T> replacedDocumentProducer = this.DocumentProducers[currentDocumentProducerIndex]; DefaultTrace.TraceInformation(string.Format( CultureInfo.InvariantCulture, "{0}, CorrelatedActivityId: {5} | Parallel~ContextBase.RepairContextAsync, MaxBufferedItemCount: {1}, Replacement PartitionKeyRange Count: {2}, MaximumConcurrencyLevel: {3}, DocumentProducer Initial Page Size {4}", DateTime.UtcNow.ToString("o", CultureInfo.InvariantCulture), this.actualMaxBufferedItemCount, replacementRanges.Count, this.TaskScheduler.MaximumConcurrencyLevel, replacedDocumentProducer.PageSize, this.CorrelatedActivityId)); int index = currentDocumentProducerIndex + 1; foreach (PartitionKeyRange range in replacementRanges) { this.DocumentProducers.Insert( index++, new DocumentProducer <T>( this.TaskScheduler, (continuationToken, pageSize) => { INameValueCollection headers = requestHeaders.Clone(); headers[HttpConstants.HttpHeaders.Continuation] = continuationToken; headers[HttpConstants.HttpHeaders.PageSize] = pageSize.ToString(CultureInfo.InvariantCulture); return(this.CreateDocumentServiceRequest( headers, querySpecForRepair, range, collectionRid)); }, range, taskPriorityFunc, this.ExecuteRequestAsync <T>, () => new NonRetriableInvalidPartitionExceptionRetryPolicy(collectionCache, this.Client.RetryPolicy.GetRequestPolicy()), this.OnDocumentProducerCompleteFetching, this.CorrelatedActivityId, replacedDocumentProducer.PageSize, replacedDocumentProducer.CurrentBackendContinuationToken)); } this.DocumentProducers.RemoveAt(currentDocumentProducerIndex); if (callback != null) { callback(); } if (this.ShouldPrefetch) { for (int i = 0; i < replacementRanges.Count; i++) { this.DocumentProducers[i + currentDocumentProducerIndex].TryScheduleFetch(); } } if (this.CurrentContinuationTokens.Remove(replacedDocumentProducer)) { for (int i = 0; i < replacementRanges.Count; ++i) { this.CurrentContinuationTokens[this.DocumentProducers[currentDocumentProducerIndex + i]] = replacedDocumentProducer.CurrentBackendContinuationToken; } } }
public DocumentServiceRequest CreateDocumentServiceRequest(INameValueCollection requestHeaders, SqlQuerySpec querySpec, PartitionKeyRange targetRange, string collectionRid) { DocumentServiceRequest request = this.CreateDocumentServiceRequest(requestHeaders, querySpec); this.PopulatePartitionKeyRangeInfo(request, targetRange, collectionRid); request.Properties = this.feedOptions.Properties; return(request); }
internal static Task <List <dynamic> > SqlQueryDocuments(DocumentClient client, string collectionsLink, SqlQuerySpec query, int trys, FeedOptions feedOptions = null) { Func <Task <IQueryable <dynamic> > > queryFn = delegate { return(Task.FromResult(client.CreateDocumentQuery(collectionsLink, query, feedOptions))); }; Console.WriteLine("QueryDocuments {0}", query); return(QueryAllRetry(queryFn, trys)); }
private DocumentServiceRequest CreateQueryDocumentServiceRequest(INameValueCollection requestHeaders, SqlQuerySpec querySpec) { DocumentServiceRequest executeQueryRequest; string queryText; switch (this.client.QueryCompatibilityMode) { case QueryCompatibilityMode.SqlQuery: if (querySpec.Parameters != null && querySpec.Parameters.Count > 0) { throw new ArgumentException( string.Format(CultureInfo.InvariantCulture, "Unsupported argument in query compatibility mode '{0}'", this.client.QueryCompatibilityMode), "querySpec.Parameters"); } executeQueryRequest = DocumentServiceRequest.Create( OperationType.SqlQuery, this.resourceTypeEnum, this.resourceLink, AuthorizationTokenType.PrimaryMasterKey, requestHeaders); executeQueryRequest.Headers[HttpConstants.HttpHeaders.ContentType] = RuntimeConstants.MediaTypes.SQL; queryText = querySpec.QueryText; break; case QueryCompatibilityMode.Default: case QueryCompatibilityMode.Query: default: executeQueryRequest = DocumentServiceRequest.Create( OperationType.Query, this.resourceTypeEnum, this.resourceLink, AuthorizationTokenType.PrimaryMasterKey, requestHeaders); executeQueryRequest.Headers[HttpConstants.HttpHeaders.ContentType] = RuntimeConstants.MediaTypes.QueryJson; queryText = JsonConvert.SerializeObject(querySpec); break; } executeQueryRequest.Body = new MemoryStream(Encoding.UTF8.GetBytes(queryText)); return(executeQueryRequest); }
public QueryDefinition ToQueryDefinition(IDictionary <object, string> parameters = null) { SqlQuerySpec querySpec = DocumentQueryEvaluator.Evaluate(this.Expression, this.serializationOptions, parameters); return(new QueryDefinition(querySpec)); }
/// <summary> /// Execute SQL statement against this collection, returning strongly typed list of type TResult. /// Use this method when your results are not neccessarily a specific type of document but other values like aggregate /// functions like SUM(), COUNT(), etc. Searched documents must be in the same partition as PartitionKey. /// Assumes the query is not parameterized. /// </summary> /// <typeparam name="TResult"></typeparam> /// <param name="sql"></param> /// <param name="partitionKey"></param> /// <returns></returns> public async Task <IEnumerable <TResult> > QueryCustomResultAsync <TResult>(string sql, object partitionKey = null) { SqlQuerySpec sqlSpec = new SqlQuerySpec(sql); return(await QueryCustomResultAsync <TResult>(sqlSpec, partitionKey)); }
public override PassThroughQueryTestOutput ExecuteTest(PassThroughQueryTestInput input) { // gets DocumentContainer IMonadicDocumentContainer monadicDocumentContainer = new InMemoryContainer(input.PartitionKeyDefinition); DocumentContainer documentContainer = new DocumentContainer(monadicDocumentContainer); SqlQuerySpec sqlQuerySpec = new SqlQuerySpec(input.Query); // gets query context string databaseId = "db1234"; string resourceLink = string.Format("dbs/{0}/colls", databaseId); CosmosQueryContextCore cosmosQueryContextCore = new CosmosQueryContextCore( client: new TestCosmosQueryClient(), resourceTypeEnum: Documents.ResourceType.Document, operationType: Documents.OperationType.Query, resourceType: typeof(QueryResponseCore), resourceLink: resourceLink, isContinuationExpected: false, allowNonValueAggregateQuery: true, useSystemPrefix: false, correlatedActivityId: Guid.NewGuid()); // gets input parameters QueryRequestOptions queryRequestOptions = new QueryRequestOptions(); CosmosSerializerCore serializerCore = new(); using StreamReader streamReader = new(serializerCore.ToStreamSqlQuerySpec(sqlQuerySpec, Documents.ResourceType.Document)); string sqlQuerySpecJsonString = streamReader.ReadToEnd(); PartitionedQueryExecutionInfo partitionedQueryExecutionInfo = GetPartitionedQueryExecutionInfo(sqlQuerySpecJsonString, input.PartitionKeyDefinition); if (input.PartitionKeyValue == default || input.PartitionKeyValue == Cosmos.PartitionKey.None) { input.PartitionKeyValue = Cosmos.PartitionKey.Null; } CosmosQueryExecutionContextFactory.InputParameters inputParameters = new CosmosQueryExecutionContextFactory.InputParameters( sqlQuerySpec: sqlQuerySpec, initialUserContinuationToken: null, initialFeedRange: null, maxConcurrency: queryRequestOptions.MaxConcurrency, maxItemCount: queryRequestOptions.MaxItemCount, maxBufferedItemCount: queryRequestOptions.MaxBufferedItemCount, partitionKey: input.PartitionKeyValue, properties: queryRequestOptions.Properties, partitionedQueryExecutionInfo: partitionedQueryExecutionInfo, executionEnvironment: null, returnResultsInDeterministicOrder: null, forcePassthrough: true, testInjections: null); IQueryPipelineStage queryPipelineStage = CosmosQueryExecutionContextFactory.Create( documentContainer, cosmosQueryContextCore, inputParameters, NoOpTrace.Singleton); bool result = queryPipelineStage.MoveNextAsync(NoOpTrace.Singleton).Result; Assert.AreEqual(input.ExpectedPassThrough, inputParameters.SqlQuerySpec.PassThrough); Assert.IsNotNull(queryPipelineStage); Assert.IsTrue(result); return(new PassThroughQueryTestOutput(inputParameters.SqlQuerySpec.PassThrough)); }
private async Task<bool> IsGroupAdminAsync(ScampUserReference user, string groupId) { // check all group's admin var sql = new SqlQuerySpec { QueryText = "SELECT g FROM Groups g JOIN u IN g.admins WHERE g.id = @groupId AND g.type='group' AND u.id = @userId", Parameters = new SqlParameterCollection() { new SqlParameter("@groupId", groupId), new SqlParameter("@userId", user.Id) } }; var query = docdb.Client.CreateDocumentQuery<dynamic>(docdb.Collection.SelfLink, sql); var match = await query.AsDocumentQuery().FirstOrDefaultAsync(); return match != null; }
public override Task <TryCatch <QueryPage> > ExecuteItemQueryAsync(string resourceUri, ResourceType resourceType, OperationType operationType, Guid clientQueryCorrelationId, Cosmos.FeedRange feedRange, QueryRequestOptions requestOptions, SqlQuerySpec sqlQuerySpec, string continuationToken, bool isContinuationExpected, int pageSize, ITrace trace, CancellationToken cancellationToken) { throw new NotImplementedException(); }
public IList<string> GetTags() { var _query = new SqlQuerySpec() { QueryText = String.Format( "SELECT * FROM c IN Clients.tags") }; return _client .CreateDocumentQuery<string>(_clientCollectionLink, _query) .ToList() .Distinct() .OrderBy(t => t) .ToList(); }
public override Task <PartitionedQueryExecutionInfo> ExecuteQueryPlanRequestAsync(string resourceUri, ResourceType resourceType, OperationType operationType, SqlQuerySpec sqlQuerySpec, Cosmos.PartitionKey?partitionKey, string supportedQueryFeatures, Guid clientQueryCorrelationId, ITrace trace, CancellationToken cancellationToken) { return(Task.FromResult(new PartitionedQueryExecutionInfo())); }
public IList<Client> GetClients(bool onlineOnly = false) { var _query = new SqlQuerySpec() { QueryText = String.Format( "SELECT * FROM c WHERE c.isDisabled = false{0}", onlineOnly ? " AND c.isOnline = true" : null) }; return _client .CreateDocumentQuery<Client>(_clientCollectionLink, _query) .ToList(); }
public async Task <DocumentQueryResponse <T> > ExecuteNextAsync <T>(Uri documentCollectionUri, SqlQuerySpec sqlSpec, string continuation) { FeedOptions feedOptions = new FeedOptions { RequestContinuation = continuation, EnableCrossPartitionQuery = true }; IDocumentQuery <T> query = null; if (sqlSpec?.QueryText == null) { query = _client.CreateDocumentQuery <T>(documentCollectionUri, feedOptions).AsDocumentQuery(); } else { query = _client.CreateDocumentQuery <T>(documentCollectionUri, sqlSpec, feedOptions).AsDocumentQuery(); } FeedResponse <T> response = await query.ExecuteNextAsync <T>(); return(new DocumentQueryResponse <T> { Results = response, ResponseContinuation = response.ResponseContinuation }); }
private async Task <CosmosQueryExecutionContext> CreateItemQueryExecutionContextAsync( CancellationToken cancellationToken) { cancellationToken.ThrowIfCancellationRequested(); CosmosContainerSettings containerSettings = await this.GetContainerSettingsAsync(cancellationToken); this.cosmosQueryContext.ContainerResourceId = containerSettings.ResourceId; PartitionedQueryExecutionInfo partitionedQueryExecutionInfo; if (this.cosmosQueryContext.QueryClient.ByPassQueryParsing() && TestFlag) { // For non-Windows platforms(like Linux and OSX) in .NET Core SDK, we cannot use ServiceInterop, so need to bypass in that case. // We are also now bypassing this for 32 bit host process running even on Windows as there are many 32 bit apps that will not work without this partitionedQueryExecutionInfo = await QueryPlanRetriever.GetQueryPlanThroughGatewayAsync( this.cosmosQueryContext.QueryClient, this.cosmosQueryContext.SqlQuerySpec, this.cosmosQueryContext.ResourceLink, cancellationToken); } else { //todo:elasticcollections this may rely on information from collection cache which is outdated //if collection is deleted/created with same name. //need to make it not rely on information from collection cache. PartitionKeyDefinition partitionKeyDefinition; object partitionKeyDefinitionObject; if (this.cosmosQueryContext.QueryRequestOptions?.Properties != null && this.cosmosQueryContext.QueryRequestOptions.Properties.TryGetValue(InternalPartitionKeyDefinitionProperty, out partitionKeyDefinitionObject)) { if (partitionKeyDefinitionObject is PartitionKeyDefinition definition) { partitionKeyDefinition = definition; } else { throw new ArgumentException( "partitionkeydefinition has invalid type", nameof(partitionKeyDefinitionObject)); } } else { partitionKeyDefinition = containerSettings.PartitionKey; } partitionedQueryExecutionInfo = await QueryPlanRetriever.GetQueryPlanWithServiceInteropAsync( this.cosmosQueryContext.QueryClient, this.cosmosQueryContext.SqlQuerySpec, partitionKeyDefinition, this.cosmosQueryContext.QueryRequestOptions?.PartitionKey != null, cancellationToken); } List <PartitionKeyRange> targetRanges = await GetTargetPartitionKeyRangesAsync( this.cosmosQueryContext.QueryClient, this.cosmosQueryContext.ResourceLink.OriginalString, partitionedQueryExecutionInfo, containerSettings, this.cosmosQueryContext.QueryRequestOptions); CosmosQueryContext rewrittenComosQueryContext; if (!string.IsNullOrEmpty(partitionedQueryExecutionInfo.QueryInfo.RewrittenQuery)) { // We need pass down the rewritten query. SqlQuerySpec rewrittenQuerySpec = new SqlQuerySpec() { QueryText = partitionedQueryExecutionInfo.QueryInfo.RewrittenQuery, Parameters = this.cosmosQueryContext.SqlQuerySpec.Parameters }; rewrittenComosQueryContext = new CosmosQueryContext( this.cosmosQueryContext.QueryClient, this.cosmosQueryContext.ResourceTypeEnum, this.cosmosQueryContext.OperationTypeEnum, this.cosmosQueryContext.ResourceType, rewrittenQuerySpec, this.cosmosQueryContext.QueryRequestOptions, this.cosmosQueryContext.ResourceLink, this.cosmosQueryContext.IsContinuationExpected, this.cosmosQueryContext.CorrelatedActivityId, this.cosmosQueryContext.IsContinuationExpected, this.cosmosQueryContext.AllowNonValueAggregateQuery, this.cosmosQueryContext.ContainerResourceId); } else { rewrittenComosQueryContext = this.cosmosQueryContext; } return(await CreateSpecializedDocumentQueryExecutionContextAsync( rewrittenComosQueryContext, partitionedQueryExecutionInfo, targetRanges, containerSettings.ResourceId, cancellationToken)); }
protected async Task <IEnumerable <TEntity> > QueryAsync(string query, SqlParameterCollection sqlParams) { var spec = new SqlQuerySpec(query, sqlParams); return(Client.CreateDocumentQuery <TEntity>(await GetCollectionDocumentsLinkAsync(), spec)); }
/// <summary> /// Run a query that returns a single document, and display it /// </summary> /// <param name="query">The query to run</param> private static void QueryScalar(SqlQuerySpec query) { dynamic result = client.CreateDocumentQuery( UriFactory.CreateDocumentCollectionUri(DatabaseName, CollectionName), query, new FeedOptions { EnableCrossPartitionQuery = true }) .AsDocumentQuery() .ExecuteNextAsync() .Result .First(); Console.WriteLine(JsonConvert.SerializeObject(result, Formatting.None)); }
public async Task <BaseResponse> Handle(UpdatePasswordCommand request, CancellationToken cancellationToken) { UpdatePasswordValidator validator = new UpdatePasswordValidator(); ValidationResult validationResult = validator.Validate(request); if (!validationResult.IsValid) { return(new BaseResponse(validationResult.Errors) { Message = "One or more validation errors occurred" }); } //========================================================================= // GET the USER DOCUMENT MODEL //========================================================================= // Generate collection uri Uri collectionUri = UriFactory.CreateDocumentCollectionUri( _documentContext.Settings.Database, _documentContext.Settings.Collection); // Create the query string sqlQuery = "SELECT * FROM Documents d WHERE d.id ='" + request.Id + "'"; var sqlSpec = new SqlQuerySpec { QueryText = sqlQuery }; // Generate FeedOptions/ParitionKey var feedOptions = new FeedOptions { PartitionKey = new PartitionKey(Common.Constants.DocumentType.User()) }; // Run query against the document store var queryResult = _documentContext.Client.CreateDocumentQuery <UserDocumentModel>( collectionUri, sqlSpec, feedOptions ); UserDocumentModel userDocumentModel; try { userDocumentModel = queryResult.AsEnumerable().FirstOrDefault(); } catch (Exception ex) { // throw AzureCosmoDBException (if a custom exception type is desired) // ... Will be caught, logged and handled by the ExceptionHandlerMiddleware // ...Or pass along as inner exception: throw new Exception("An error occured trying to use the document store", ex); } //========================================================================= // UPDATE the USER DOCUMENT MODEL //========================================================================= if (userDocumentModel == null) { return(new BaseResponse { Message = "Could not retrieve user with that Id from the document store" }); } //========================================================================= // DETERMINE IF THE PASSWORD IS CORRECT //========================================================================= var authenticationGranted = Common.Encryption.PasswordHashing.ValidatePassword(request.OldPassword, userDocumentModel.PasswordHash, userDocumentModel.PasswordSalt); if (!authenticationGranted) { return(new BaseResponse { Message = "Incorrect password" }); } // Generate salt and hash from new password var passwordHashResults = Common.Encryption.PasswordHashing.HashPassword(request.NewPassword); userDocumentModel.PasswordSalt = passwordHashResults.Salt; userDocumentModel.PasswordHash = passwordHashResults.Hash; var documentUri = UriFactory.CreateDocumentUri( _documentContext.Settings.Database, _documentContext.Settings.Collection, userDocumentModel.Id); ResourceResponse <Document> result; try { // Save the document to document store using the IDocumentContext dependency result = await _documentContext.Client.ReplaceDocumentAsync( documentUri, userDocumentModel, new RequestOptions { PartitionKey = new PartitionKey(Common.Constants.DocumentType.User().ToString()) } ); } catch (Exception ex) { // throw DocumentStoreException (if a custom exception type is desired) // ... Will be caught, logged and handled by the ExceptionHandlerMiddleware // Pass exception up the chain: throw ex; // ...Or pass along as inner exception: //throw new Exception("An error occured trying to use the document store", ex); } finally { // Close any open connections, etc... } if (result.StatusCode == System.Net.HttpStatusCode.OK) { //========================================================================= // LOG ACTIVITY //========================================================================= var user = AutoMapper.Mapper.Map <Core.Domain.Entities.User>(userDocumentModel); Log.Information("Password updated {@user}", user); //========================================================================== // POST COMMAND CHECKLIST //========================================================================= // 1. CACHING: Update cache. // 2. SEARCH INDEX: Update Search index or send indexer request. //----------------------------------------------------------------------- // Return Response return(new BaseResponse { isSuccess = true, Message = "Password updated" }); } else { return(new BaseResponse { Message = "Could not save model to document store. Status code: " + result.StatusCode }); } }
public override Task <TryCatch <PartitionedQueryExecutionInfo> > TryGetPartitionedQueryExecutionInfoAsync(SqlQuerySpec sqlQuerySpec, ResourceType resourceType, PartitionKeyDefinition partitionKeyDefinition, bool requireFormattableOrderByQuery, bool isContinuationExpected, bool allowNonValueAggregateQuery, bool hasLogicalPartitionKey, bool allowDCount, bool useSystemPrefix, CancellationToken cancellationToken) { throw new NotImplementedException(); }
internal static IQueryable <dynamic> CreateDocumentQuery(this IDocumentQueryClient client, string collectionLink, SqlQuerySpec querySpec, FeedOptions feedOptions = null, object partitionKey = null) { return(new DocumentQuery <Document>(client, ResourceType.Document, typeof(Document), collectionLink, feedOptions, partitionKey).AsSQL(querySpec)); }
public async Task TestItemProducerTreeWithFailure() { int callBackCount = 0; Mock <CosmosQueryContext> mockQueryContext = new Mock <CosmosQueryContext>(); SqlQuerySpec sqlQuerySpec = new SqlQuerySpec("Select * from t"); PartitionKeyRange partitionKeyRange = new PartitionKeyRange { Id = "0", MinInclusive = "A", MaxExclusive = "B" }; void produceAsyncCompleteCallback( ItemProducerTree producer, int itemsBuffered, double resourceUnitUsage, long responseLengthBytes, CancellationToken token) { callBackCount++; } Mock <IComparer <ItemProducerTree> > comparer = new Mock <IComparer <ItemProducerTree> >(); Mock <IEqualityComparer <CosmosElement> > cosmosElementComparer = new Mock <IEqualityComparer <CosmosElement> >(); CancellationTokenSource cancellationTokenSource = new CancellationTokenSource(); IReadOnlyList <CosmosElement> cosmosElements = new List <CosmosElement>() { new Mock <CosmosElement>(CosmosElementType.Object).Object }; CosmosDiagnosticsContext diagnosticsContext = new CosmosDiagnosticsContextCore(); diagnosticsContext.AddDiagnosticsInternal(new PointOperationStatistics( Guid.NewGuid().ToString(), System.Net.HttpStatusCode.OK, subStatusCode: SubStatusCodes.Unknown, responseTimeUtc: DateTime.UtcNow, requestCharge: 42, errorMessage: null, method: HttpMethod.Post, requestUri: new Uri("http://localhost.com"), requestSessionToken: null, responseSessionToken: null)); QueryPageDiagnostics diagnostics = new QueryPageDiagnostics( partitionKeyRangeId: "0", queryMetricText: "SomeRandomQueryMetricText", indexUtilizationText: null, diagnosticsContext: diagnosticsContext); IReadOnlyCollection <QueryPageDiagnostics> pageDiagnostics = new List <QueryPageDiagnostics>() { diagnostics }; mockQueryContext.Setup(x => x.ContainerResourceId).Returns("MockCollectionRid"); mockQueryContext.Setup(x => x.ExecuteQueryAsync( sqlQuerySpec, It.IsAny <string>(), It.IsAny <PartitionKeyRangeIdentity>(), It.IsAny <bool>(), It.IsAny <int>(), cancellationTokenSource.Token)).Returns( Task.FromResult(QueryResponseCore.CreateSuccess( result: cosmosElements, requestCharge: 42, activityId: "AA470D71-6DEF-4D61-9A08-272D8C9ABCFE", responseLengthBytes: 500, disallowContinuationTokenMessage: null, continuationToken: "TestToken"))); ItemProducerTree itemProducerTree = new ItemProducerTree( queryContext: mockQueryContext.Object, querySpecForInit: sqlQuerySpec, partitionKeyRange: partitionKeyRange, produceAsyncCompleteCallback: produceAsyncCompleteCallback, itemProducerTreeComparer: comparer.Object, equalityComparer: cosmosElementComparer.Object, testSettings: new TestInjections(simulate429s: false, simulateEmptyPages: false), deferFirstPage: false, collectionRid: "collectionRid", initialContinuationToken: null, initialPageSize: 50); // Buffer to success responses await itemProducerTree.BufferMoreDocumentsAsync(cancellationTokenSource.Token); await itemProducerTree.BufferMoreDocumentsAsync(cancellationTokenSource.Token); CosmosDiagnosticsContext diagnosticsContextInternalServerError = new CosmosDiagnosticsContextCore(); diagnosticsContextInternalServerError.AddDiagnosticsInternal(new PointOperationStatistics( Guid.NewGuid().ToString(), System.Net.HttpStatusCode.InternalServerError, subStatusCode: SubStatusCodes.Unknown, responseTimeUtc: DateTime.UtcNow, requestCharge: 10.2, errorMessage: "Error message", method: HttpMethod.Post, requestUri: new Uri("http://localhost.com"), requestSessionToken: null, responseSessionToken: null)); diagnostics = new QueryPageDiagnostics( partitionKeyRangeId: "0", queryMetricText: null, indexUtilizationText: null, diagnosticsContext: diagnosticsContextInternalServerError); pageDiagnostics = new List <QueryPageDiagnostics>() { diagnostics }; // Buffer a failure mockQueryContext.Setup(x => x.ExecuteQueryAsync( sqlQuerySpec, It.IsAny <string>(), It.IsAny <PartitionKeyRangeIdentity>(), It.IsAny <bool>(), It.IsAny <int>(), cancellationTokenSource.Token)).Returns( Task.FromResult(QueryResponseCore.CreateFailure( statusCode: HttpStatusCode.InternalServerError, subStatusCodes: null, cosmosException: CosmosExceptionFactory.CreateInternalServerErrorException( "Error message"), requestCharge: 10.2, activityId: Guid.NewGuid().ToString()))); await itemProducerTree.BufferMoreDocumentsAsync(cancellationTokenSource.Token); // First item should be a success { (bool movedToNextPage, QueryResponseCore? failureResponse) = await itemProducerTree.TryMoveNextPageAsync(cancellationTokenSource.Token); Assert.IsTrue(movedToNextPage); Assert.IsNull(failureResponse); Assert.IsTrue(itemProducerTree.TryMoveNextDocumentWithinPage()); Assert.IsFalse(itemProducerTree.TryMoveNextDocumentWithinPage()); Assert.IsTrue(itemProducerTree.HasMoreResults); } // Second item should be a success { (bool movedToNextPage, QueryResponseCore? failureResponse) = await itemProducerTree.TryMoveNextPageAsync(cancellationTokenSource.Token); Assert.IsTrue(movedToNextPage); Assert.IsNull(failureResponse); Assert.IsTrue(itemProducerTree.TryMoveNextDocumentWithinPage()); Assert.IsFalse(itemProducerTree.TryMoveNextDocumentWithinPage()); Assert.IsTrue(itemProducerTree.HasMoreResults); } // Third item should be a failure { (bool movedToNextPage, QueryResponseCore? failureResponse) = await itemProducerTree.TryMoveNextPageAsync(cancellationTokenSource.Token); Assert.IsFalse(movedToNextPage); Assert.IsNotNull(failureResponse); Assert.IsFalse(itemProducerTree.HasMoreResults); } // Try to buffer after failure. It should return the previous cached failure and not try to buffer again. mockQueryContext.Setup(x => x.ExecuteQueryAsync( sqlQuerySpec, It.IsAny <string>(), It.IsAny <PartitionKeyRangeIdentity>(), It.IsAny <bool>(), It.IsAny <int>(), cancellationTokenSource.Token)). Throws(new Exception("Previous buffer failed. Operation should return original failure and not try again")); await itemProducerTree.BufferMoreDocumentsAsync(cancellationTokenSource.Token); Assert.IsFalse(itemProducerTree.HasMoreResults); }
private async Task QueryAsync(Func <StoredEvent, Task> callback, StreamPosition lastPosition, SqlQuerySpec query, EventPredicate filterExpression, CancellationToken ct = default) { using (Profiler.TraceMethod <CosmosDbEventStore>()) { await documentClient.QueryAsync(collectionUri, query, async commit => { var eventStreamOffset = (int)commit.EventStreamOffset; var commitTimestamp = commit.Timestamp; var commitOffset = 0; foreach (var @event in commit.Events) { eventStreamOffset++; if (commitOffset > lastPosition.CommitOffset || commitTimestamp > lastPosition.Timestamp) { var eventData = @event.ToEventData(); if (filterExpression(eventData)) { var eventToken = new StreamPosition(commitTimestamp, commitOffset, commit.Events.Length); await callback(new StoredEvent(commit.EventStream, eventToken, eventStreamOffset, eventData)); } } commitOffset++; } }, ct); } }