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