public async Task <ListResponse <DataStreamResult> > GetItemsAsync(string deviceId, LagoVista.Core.Models.UIMetaData.ListRequest request) { var filter = TableQuery.GenerateFilterCondition(nameof(Models.DataStreamTSEntity.PartitionKey), QueryComparisons.Equal, deviceId); var dateFilter = String.Empty; /* FYI - less than and greater than are reversed because the data is inserted wiht row keys in descending order */ if (!String.IsNullOrEmpty(request.StartDate) && !String.IsNullOrEmpty(request.EndDate)) { var startRowKey = request.StartDate.ToDateTime().ToInverseTicksRowKey(); var endRowKey = request.EndDate.ToDateTime().ToInverseTicksRowKey(); dateFilter = TableQuery.CombineFilters( TableQuery.GenerateFilterCondition(nameof(Models.DataStreamTSEntity.RowKey), QueryComparisons.LessThanOrEqual, startRowKey.ToString()), TableOperators.And, TableQuery.GenerateFilterCondition(nameof(Models.DataStreamTSEntity.RowKey), QueryComparisons.GreaterThanOrEqual, endRowKey.ToString()) ); } else if (String.IsNullOrEmpty(request.StartDate) && !String.IsNullOrEmpty(request.EndDate)) { var endRowKey = request.EndDate.ToDateTime().ToInverseTicksRowKey(); dateFilter = TableQuery.GenerateFilterCondition(nameof(Models.DataStreamTSEntity.RowKey), QueryComparisons.GreaterThanOrEqual, endRowKey.ToString()); } else if (String.IsNullOrEmpty(request.EndDate) && !String.IsNullOrEmpty(request.StartDate)) { var startRowKey = request.StartDate.ToDateTime().ToInverseTicksRowKey(); dateFilter = TableQuery.GenerateFilterCondition(nameof(Models.DataStreamTSEntity.RowKey), QueryComparisons.LessThanOrEqual, startRowKey.ToString()); } if (!String.IsNullOrEmpty(dateFilter)) { filter = TableQuery.CombineFilters(filter, TableOperators.And, dateFilter); } var query = new TableQuery <DynamicTableEntity>().Where(filter).Take(request.PageSize); var numberRetries = 5; var retryCount = 0; var completed = false; while (retryCount++ < numberRetries && !completed) { try { TableQuerySegment <DynamicTableEntity> results; if (!String.IsNullOrEmpty(request.NextPartitionKey) && !String.IsNullOrEmpty(request.NextRowKey)) { var token = new TableContinuationToken() { NextPartitionKey = request.NextPartitionKey, NextRowKey = request.NextRowKey }; results = await _cloudTable.ExecuteQuerySegmentedAsync <DynamicTableEntity>(query, token); } else { results = await _cloudTable.ExecuteQuerySegmentedAsync <DynamicTableEntity>(query, new TableContinuationToken()); } var listResponse = new ListResponse <DataStreamResult> { NextRowKey = results.ContinuationToken == null ? null : results.ContinuationToken.NextRowKey, NextPartitionKey = results.ContinuationToken == null ? null : results.ContinuationToken.NextPartitionKey, PageSize = results.Count(), HasMoreRecords = results.ContinuationToken != null, }; var resultSet = new List <DataStreamResult>(); foreach (var item in results) { var result = new DataStreamResult(); foreach (var property in item.Properties) { result.Fields.Add(property.Key, property.Value.PropertyAsObject); } switch (_stream.DateStorageFormat.Value) { case DateStorageFormats.Epoch: long epoch = Convert.ToInt64(item.Properties[_stream.TimeStampFieldName]); result.Timestamp = DateTimeOffset.FromUnixTimeSeconds(epoch).DateTime.ToJSONString(); break; case DateStorageFormats.ISO8601: result.Timestamp = item.Properties[_stream.TimeStampFieldName].StringValue.ToDateTime().ToJSONString(); break; } resultSet.Add(result); } listResponse.Model = resultSet; return(listResponse); } catch (Exception ex) { if (retryCount == numberRetries) { _instanceLogger.AddException("AzureTableStorageConnector_GetItemsAsync", ex); } else { _instanceLogger.AddCustomEvent(LagoVista.Core.PlatformSupport.LogLevel.Warning, "AzureTableStorageConnector_GetItemsAsync", "", ex.Message.ToKVP("exceptionMessage"), ex.GetType().Name.ToKVP("exceptionType"), retryCount.ToString().ToKVP("retryCount")); } await Task.Delay(retryCount * 250); } finally { } } return(null); }
public Task <LagoVista.Core.Models.UIMetaData.ListResponse <DataStreamResult> > GetItemsAsync(string deviceId, LagoVista.Core.Models.UIMetaData.ListRequest request) { throw new NotSupportedException("Reading a list of items from amazon S3 is not supported."); }
public Task <LagoVista.Core.Models.UIMetaData.ListResponse <DataStreamResult> > GetItemsAsync(string deviceId, LagoVista.Core.Models.UIMetaData.ListRequest request) { throw new NotImplementedException(); }
public async Task <LagoVista.Core.Models.UIMetaData.ListResponse <DataStreamResult> > GetItemsAsync(string deviceId, LagoVista.Core.Models.UIMetaData.ListRequest request) { //TODO: Next chunk of code sux, likely much better way but will probably want to build a more robust filtering system at some point. ISearchResponse <Dictionary <string, object> > result = null; if (String.IsNullOrEmpty(request.StartDate) && String.IsNullOrEmpty(request.EndDate)) { result = await _client.SearchAsync <Dictionary <string, object> >(src => src.From(0) .Index(_stream.ESIndexName) .Type(_stream.ESTypeName) .From(request.PageIndex *request.PageSize) .Size(request.PageSize) .Sort(srt => srt.Descending(new Field("sortOrder")))); } else if (String.IsNullOrEmpty(request.StartDate)) { var endTicks = request.EndDate.ToDateTime().Ticks; result = await _client.SearchAsync <Dictionary <string, object> >(src => src.From(0) .Index(_stream.ESIndexName) .Type(_stream.ESTypeName) .Query(qry => qry.Range(rng => rng .Field("sortOrder") .LessThanOrEquals(endTicks) )) .From(request.PageIndex *request.PageSize) .Size(request.PageSize) .Sort(srt => srt.Descending(new Field("sortOrder")))); } else if (String.IsNullOrEmpty(request.EndDate)) { var startTicks = request.StartDate.ToDateTime().Ticks; result = await _client.SearchAsync <Dictionary <string, object> >(src => src.From(0) .Index(_stream.ESIndexName) .Type(_stream.ESTypeName) .Query(qry => qry.Range(rng => rng .Field("sortOrder") .GreaterThanOrEquals(startTicks) )) .From(request.PageIndex *request.PageSize) .Size(request.PageSize) .Sort(srt => srt.Descending(new Field("sortOrder")))); } else { var startTicks = request.StartDate.ToDateTime().Ticks; var endTicks = request.EndDate.ToDateTime().Ticks; result = await _client.SearchAsync <Dictionary <string, object> >(src => src.From(0) .Index(_stream.ESIndexName) .Type(_stream.ESTypeName) .Query(qry => qry.Range(rng => rng .Field("sortOrder") .GreaterThanOrEquals(startTicks) .LessThanOrEquals(endTicks) )) .From(request.PageIndex *request.PageSize) .Size(request.PageSize) .Sort(srt => srt.Descending(new Field("sortOrder")))); } if (result.IsValid) { var records = new List <DataStreamResult>(); foreach (var record in result.Documents) { records.Add(new DataStreamResult() { /* Newtonsoft assumes the value is date time for something that looks like a date, time this dual conversion gets our standard ISO8601 date string */ Timestamp = record[_stream.TimeStampFieldName].ToString().ToDateTime().ToJSONString(), Fields = record }); } var response = Core.Models.UIMetaData.ListResponse <DataStreamResult> .Create(records); response.PageIndex = request.PageIndex; response.PageSize = records.Count; response.HasMoreRecords = response.PageSize == request.PageSize; return(response); } else { if (result.OriginalException != null) { return(ListResponse <DataStreamResult> .FromError(result.OriginalException.Message)); } else { return(ListResponse <DataStreamResult> .FromError(result.DebugInformation)); } } }
public async Task <LagoVista.Core.Models.UIMetaData.ListResponse <DataStreamResult> > GetItemsAsync(string deviceId, LagoVista.Core.Models.UIMetaData.ListRequest request) { var sql = new StringBuilder("select "); if (request.PageSize == 0) { request.PageSize = 50; } sql.Append($"[{_stream.TimestampFieldName}]"); foreach (var fld in _stream.Fields) { sql.Append($", [{fld.FieldName}]"); } sql.AppendLine(); sql.AppendLine($" from [{_stream.DbTableName}]"); sql.AppendLine($" where [{_stream.DeviceIdFieldName}] = @deviceId"); if (!String.IsNullOrEmpty(request.NextRowKey)) { sql.AppendLine($" and {_stream.TimestampFieldName} < @lastDateStamp"); } if (!String.IsNullOrEmpty(request.StartDate)) { sql.AppendLine($" and {_stream.TimestampFieldName} >= @startDateStamp"); } if (!String.IsNullOrEmpty(request.EndDate)) { sql.AppendLine($" and {_stream.TimestampFieldName} <= @endDateStamp"); } sql.AppendLine($" order by [{_stream.TimestampFieldName}] desc"); sql.AppendLine(" OFFSET @PageSize * @PageIndex ROWS"); sql.AppendLine(" FETCH NEXT @PageSize ROWS ONLY "); Console.WriteLine(sql.ToString()); var responseItems = new List <DataStreamResult>(); using (var cn = new System.Data.SqlClient.SqlConnection(_connectionString)) using (var cmd = new System.Data.SqlClient.SqlCommand(sql.ToString(), cn)) { cmd.Parameters.AddWithValue("@deviceId", deviceId); cmd.Parameters.AddWithValue("@PageSize", request.PageSize); cmd.Parameters.AddWithValue("@PageIndex", Math.Max(request.PageIndex - 1, 0)); if (!String.IsNullOrEmpty(request.NextRowKey)) { cmd.Parameters.AddWithValue($"@lastDateStamp", request.NextRowKey.ToDateTime()); } if (!String.IsNullOrEmpty(request.StartDate)) { cmd.Parameters.AddWithValue($"@startDateStamp", request.StartDate.ToDateTime()); } if (!String.IsNullOrEmpty(request.EndDate)) { cmd.Parameters.AddWithValue($"@endDateStamp", request.EndDate.ToDateTime()); } cmd.CommandType = System.Data.CommandType.Text; await cmd.Connection.OpenAsync(); using (var rdr = await cmd.ExecuteReaderAsync()) { while (rdr.Read()) { var resultItem = new DataStreamResult(); resultItem.Timestamp = Convert.ToDateTime(rdr[_stream.TimestampFieldName]).ToJSONString(); foreach (var fld in _stream.Fields) { try { resultItem.Add(fld.FieldName, rdr[fld.FieldName]); } catch (Exception) { Debugger.Break(); } } responseItems.Add(resultItem); } } } var response = new Core.Models.UIMetaData.ListResponse <DataStreamResult>(); response.Model = responseItems; response.PageSize = responseItems.Count; response.PageIndex = request.PageIndex; response.HasMoreRecords = responseItems.Count == request.PageSize; if (response.HasMoreRecords) { response.NextRowKey = responseItems.Last().Timestamp; } return(response); }