public override int GetHashCode()
{
int hashPrimaryKeyValue = PrimaryKeyValue.GetHashCode();
int hashChangeOperation = _ChangeOperation.GetHashCode();
return(hashPrimaryKeyValue ^ hashChangeOperation);
}
protected void Page_Load(object sender, System.EventArgs e)
{
//if (Request.QueryString["tableName"] != null && Request.QueryString["tableName"].ToString().Length > 0)
//{
// _tableName = Request.QueryString["tableName"].ToString();
//}
// need both table name and primary key to activate plugin link
//if( (_tableName != null && _tableName.Length > 0) && (PrimaryKeyValue != null && !PrimaryKeyValue.Equals("") ) && EnteredTimeValue.Length > 0 )
if ((PrimaryKeyValue != null && !PrimaryKeyValue.Equals("")))
{
// Include groupId to identify file grouping
string fileUploadPath = "FileUploadForm.aspx?tableName=" + this.PrimaryKeyTable + "&primaryKey=" + this.PrimaryKeyValue;
string resolvedFileUploadPath = ResolveUrl(fileUploadPath);
string onClickScript = "window.open('" + resolvedFileUploadPath + "','FileUploader', 'toolbars=no,resizable=yes,scrollbars=yes,width=900,height=800,left=20,top=20'); return false;";
link.Attributes.Add("onclick", onClickScript);
this.DisplayLink(this.PrimaryKeyTable, this.PrimaryKeyValue);
}
else
{
linkImage.ImageUrl = "~/Images/UploadFile.gif";
link.Attributes.Add("onclick", "alert('Before uploading a file this form must saved and in an editable state.'); return false;");
}
}
public PrimaryKeyRange(PrimaryKeyValue currentValue, BitArray nextResourceTypeIds)
{
EnsureArg.IsNotNull(currentValue, nameof(currentValue));
EnsureArg.IsNotNull(nextResourceTypeIds, nameof(nextResourceTypeIds));
CurrentValue = currentValue;
NextResourceTypeIds = nextResourceTypeIds;
}
public void PrimaryKeyValueWorksWithByteArray()
{
// --- Act
var pkValueKey = new PrimaryKeyValue(new List <object>
{
new byte[] { 1, 2, 3 }
}).KeyString;
// --- Assert
pkValueKey.ShouldEqual("[0x010203]");
}
private PrimaryKeyValue GetKey(FullLoadRecord record, TableSchema tableSchema)
{
var pkVal = new PrimaryKeyValue();
foreach (var pkCol in tableSchema.PrimaryKeys)
{
pkVal.AddKeyValue(pkCol.OrdinalPosition, pkCol.ColumnName, record.Data[pkCol.ColumnName]);
}
return(pkVal);
}
public bool Equals(Change other)
{
if (Object.ReferenceEquals(other, null))
{
return(false);
}
if (Object.ReferenceEquals(this, other))
{
return(true);
}
return(PrimaryKeyValue.Equals(other.PrimaryKeyValue) && _ChangeOperation.Equals(other._ChangeOperation)); // TODO
}
public async Task <FullLoadBatch> GetBatchAsync(TableSchema tableSchema, PrimaryKeyValue lastRetrievedKey, int batchSize)
{
var batch = new FullLoadBatch();
batch.TableSchema = tableSchema;
using (var conn = await GetOpenConnectionAsync())
{
var command = conn.CreateCommand();
command.CommandText = TableSchemaQueryBuilder.GetExtractQueryUsingAllKeys(tableSchema, batchSize);
foreach (var pk in tableSchema.PrimaryKeys.OrderBy(x => x.OrdinalPosition))
{
var columnSchema = tableSchema.GetColumn(pk.ColumnName);
var value = lastRetrievedKey.GetValue(pk.OrdinalPosition);
command.Parameters.Add(CreateSqlParameter(columnSchema, "@p" + pk.OrdinalPosition, value));
}
using (var reader = await command.ExecuteReaderAsync())
{
int ctr = 1;
while (await reader.ReadAsync())
{
var change = new FullLoadRecord();
change.ChangeKey = GetRecordId(reader, tableSchema);
change.BatchSeqNo = ctr;
foreach (var column in tableSchema.Columns)
{
change.Data.Add(column.Name, reader[column.Name]);
}
batch.Records.Add(change);
ctr++;
}
}
}
if (batch.Records.Any())
{
batch.FirstRowKey = GetKey(batch.Records.First(), tableSchema);
batch.LastRowKey = GetKey(batch.Records.Last(), tableSchema);
}
return(batch);
}
private async Task <PrimaryKeyValue> SetStartingPosition(string executionId, TableSchema tableSchema, int batchSize)
{
PrimaryKeyValue lastRetrievedKey = null;
long ctr = 0;
var existingOffsetResult = await _cdcReaderClient.GetLastFullLoadOffsetAsync(executionId, tableSchema.TableName);
if (existingOffsetResult.Result == Result.NoStoredState)
{
Console.WriteLine($"Table {tableSchema.TableName} - No previous stored offset. Starting from first row");
var firstBatch = await _cdcReaderClient.GetFirstBatchAsync(tableSchema, batchSize);
if (firstBatch.Records.Any())
{
lastRetrievedKey = firstBatch.LastRowKey;
var result = await WriteToRedshiftAsync(firstBatch, ctr);
if (!result.Item1)
{
Console.WriteLine($"Table {tableSchema.TableName} - Export aborted");
return(null);
}
await _cdcReaderClient.StoreFullLoadOffsetAsync(executionId, tableSchema.TableName, lastRetrievedKey);
ctr = result.Item2;
Console.WriteLine($"Table {tableSchema.TableName} - Written first batch to Redshift");
}
else
{
Console.WriteLine($"Table {tableSchema.TableName} - No data to export");
return(null);
}
}
else
{
Console.WriteLine($"Table {tableSchema.TableName} - Starting from stored offset");
lastRetrievedKey = existingOffsetResult.State;
}
return(lastRetrievedKey);
}
public async Task StorePkOffsetAsync(string executionId, string tableName, PrimaryKeyValue pkValue)
{
if (_fullLoadSeen.ContainsKey(Tuple.Create(executionId, tableName)))
{
await UpdateStorePkOffsetAsync(executionId, tableName, pkValue);
}
else
{
var result = await GetLastPkOffsetAsync(executionId, tableName);
if (result.Result == Result.NoStoredState)
{
await InsertStorePkOffsetAsync(executionId, tableName, pkValue);
_fullLoadSeen.TryAdd(Tuple.Create(executionId, tableName), true);
}
else
{
await UpdateStorePkOffsetAsync(executionId, tableName, pkValue);
}
}
}
private async Task UpdateStorePkOffsetAsync(string executionId, string tableName, PrimaryKeyValue pkValue)
{
using (var conn = await GetConnectionAsync())
{
var command = conn.CreateCommand();
command.CommandText = @"UPDATE [CdcTools].[FullLoadState]
SET [PrimaryKeyValue] = @PrimaryKeyValue,
[LastUpdate] = GETUTCDATE()
WHERE ExecutionId = @ExecutionId
AND TableName = @TableName";
command.Parameters.Add("ExecutionId", SqlDbType.VarChar, 50).Value = executionId;
command.Parameters.Add("TableName", SqlDbType.VarChar, 200).Value = tableName;
command.Parameters.Add("PrimaryKeyValue", SqlDbType.VarChar, -1).Value = JsonConvert.SerializeObject(pkValue);
await command.ExecuteNonQueryAsync();
}
}
private async Task InsertStorePkOffsetAsync(string executionId, string tableName, PrimaryKeyValue pkValue)
{
using (var conn = await GetConnectionAsync())
{
var command = conn.CreateCommand();
command.CommandText = @"INSERT INTO [CdcTools].[FullLoadState]([ExecutionId],[TableName],[PrimaryKeyValue],[LastUpdate])
VALUES(@ExecutionId,@TableName,@PrimaryKeyValue,GETUTCDATE())";
command.Parameters.Add("ExecutionId", SqlDbType.VarChar, 50).Value = executionId;
command.Parameters.Add("TableName", SqlDbType.VarChar, 200).Value = tableName;
command.Parameters.Add("PrimaryKeyValue", SqlDbType.VarChar, -1).Value = JsonConvert.SerializeObject(pkValue);
await command.ExecuteNonQueryAsync();
}
}
private async Task StreamTableAsync(CancellationToken token,
string executionId,
TableSchema tableSchema,
SerializationMode serializationMode,
bool sendWithKey,
int batchSize,
int printPercentProgressMod)
{
string topicName = _kafkaTopicPrefix + tableSchema.TableName.ToLower();
var rowCount = await _cdcReaderClient.GetRowCountAsync(tableSchema);
Console.WriteLine($"Table {tableSchema.Schema}.{tableSchema.TableName} has {rowCount} rows to export");
int progress = 0;
using (var producer = ProducerFactory.GetProducer(topicName, tableSchema, serializationMode, sendWithKey, _kafkaBootstrapServers, _schemaRegistryUrl))
{
long ctr = 0;
PrimaryKeyValue lastRetrievedKey = null;
var existingOffset = await _cdcReaderClient.GetLastFullLoadOffsetAsync(executionId, tableSchema.TableName);
if (existingOffset.Result == CdcReader.State.Result.NoStoredState)
{
Console.WriteLine($"Table {tableSchema.TableName} - No previous stored offset. Starting from first row");
var firstBatch = await _cdcReaderClient.GetFirstBatchAsync(tableSchema, batchSize);
ctr = await PublishAsync(producer, token, firstBatch, ctr);
lastRetrievedKey = firstBatch.LastRowKey;
await _cdcReaderClient.StoreFullLoadOffsetAsync(executionId, tableSchema.TableName, firstBatch.LastRowKey);
}
else
{
Console.WriteLine($"Table {tableSchema.TableName} - No data to export");
lastRetrievedKey = existingOffset.State;
}
bool finished = false;
while (!token.IsCancellationRequested && !finished)
{
var changes = new List <RowChange>();
var batch = await _cdcReaderClient.GetBatchAsync(tableSchema, lastRetrievedKey, batchSize);
ctr = await PublishAsync(producer, token, batch, ctr);
int latestProgress = (int)(((double)ctr / (double)rowCount) * 100);
if (progress != latestProgress && latestProgress % printPercentProgressMod == 0)
{
Console.WriteLine($"Table {tableSchema.Schema}.{tableSchema.TableName} - Progress at {latestProgress}% ({ctr} records)");
}
progress = latestProgress;
lastRetrievedKey = batch.LastRowKey;
await _cdcReaderClient.StoreFullLoadOffsetAsync(executionId, tableSchema.TableName, lastRetrievedKey);
if (!batch.Records.Any() || batch.Records.Count < batchSize)
{
finished = true;
}
}
if (token.IsCancellationRequested)
{
Console.WriteLine($"Table {tableSchema.Schema}.{tableSchema.TableName} - cancelled at progress at {progress}% ({ctr} records)");
}
else
{
Console.WriteLine($"Table {tableSchema.Schema}.{tableSchema.TableName} - complete ({ctr} records)");
}
}
}
public async Task <FullLoadBatch> GetBatchAsync(TableSchema tableSchema, PrimaryKeyValue lastRetrievedKey, int batchSize)
{
return(await _fullLoadRepository.GetBatchAsync(tableSchema, lastRetrievedKey, batchSize));
}
public async Task StoreFullLoadOffsetAsync(string executionId, string tableName, PrimaryKeyValue pkValue)
{
await _stateManager.StorePkOffsetAsync(executionId, tableName, pkValue);
}
private async Task <SearchResult> SearchImpl(SqlSearchOptions sqlSearchOptions, SqlSearchType searchType, string currentSearchParameterHash, CancellationToken cancellationToken)
{
Expression searchExpression = sqlSearchOptions.Expression;
// AND in the continuation token
if (!string.IsNullOrWhiteSpace(sqlSearchOptions.ContinuationToken) && !sqlSearchOptions.CountOnly)
{
var continuationToken = ContinuationToken.FromString(sqlSearchOptions.ContinuationToken);
if (continuationToken != null)
{
if (string.IsNullOrEmpty(continuationToken.SortValue))
{
// Check whether it's a _lastUpdated or (_type,_lastUpdated) sort optimization
bool optimize = true;
(SearchParameterInfo searchParamInfo, SortOrder sortOrder) = sqlSearchOptions.Sort.Count == 0 ? default : sqlSearchOptions.Sort[0];
if (sqlSearchOptions.Sort.Count > 0)
{
if (!(searchParamInfo.Name == SearchParameterNames.LastUpdated || searchParamInfo.Name == SearchParameterNames.ResourceType))
{
optimize = false;
}
}
FieldName fieldName;
object keyValue;
SearchParameterInfo parameter;
if (continuationToken.ResourceTypeId == null || _schemaInformation.Current < SchemaVersionConstants.PartitionedTables)
{
// backwards compat
parameter = SqlSearchParameters.ResourceSurrogateIdParameter;
fieldName = SqlFieldName.ResourceSurrogateId;
keyValue = continuationToken.ResourceSurrogateId;
}
else
{
parameter = SqlSearchParameters.PrimaryKeyParameter;
fieldName = SqlFieldName.PrimaryKey;
keyValue = new PrimaryKeyValue(continuationToken.ResourceTypeId.Value, continuationToken.ResourceSurrogateId);
}
Expression lastUpdatedExpression = null;
if (!optimize)
{
lastUpdatedExpression = Expression.GreaterThan(fieldName, null, keyValue);
}
else
{
if (sortOrder == SortOrder.Ascending)
{
lastUpdatedExpression = Expression.GreaterThan(fieldName, null, keyValue);
}
else
{
lastUpdatedExpression = Expression.LessThan(fieldName, null, keyValue);
}
}
var tokenExpression = Expression.SearchParameter(parameter, lastUpdatedExpression);
searchExpression = searchExpression == null ? tokenExpression : Expression.And(tokenExpression, searchExpression);
}
}
else
{
throw new BadRequestException(Resources.InvalidContinuationToken);
}
}
var originalSort = new List <(SearchParameterInfo, SortOrder)>(sqlSearchOptions.Sort);
var clonedSearchOptions = UpdateSort(sqlSearchOptions, searchExpression, searchType);
if (clonedSearchOptions.CountOnly)
{
// if we're only returning a count, discard any _include parameters since included resources are not counted.
searchExpression = searchExpression?.AcceptVisitor(RemoveIncludesRewriter.Instance);
}
SqlRootExpression expression = (SqlRootExpression)searchExpression
?.AcceptVisitor(LastUpdatedToResourceSurrogateIdRewriter.Instance)
.AcceptVisitor(DateTimeEqualityRewriter.Instance)
.AcceptVisitor(FlatteningRewriter.Instance)
.AcceptVisitor(UntypedReferenceRewriter.Instance)
.AcceptVisitor(_sqlRootExpressionRewriter)
.AcceptVisitor(_partitionEliminationRewriter)
.AcceptVisitor(_sortRewriter, clonedSearchOptions)
.AcceptVisitor(SearchParamTableExpressionReorderer.Instance)
.AcceptVisitor(MissingSearchParamVisitor.Instance)
.AcceptVisitor(NotExpressionRewriter.Instance)
.AcceptVisitor(_chainFlatteningRewriter)
.AcceptVisitor(ResourceColumnPredicatePushdownRewriter.Instance)
.AcceptVisitor(DateTimeBoundedRangeRewriter.Instance)
.AcceptVisitor(
(SqlExpressionRewriterWithInitialContext <object>)(_schemaInformation.Current >= SchemaVersionConstants.PartitionedTables
? StringOverflowRewriter.Instance
: LegacyStringOverflowRewriter.Instance))
.AcceptVisitor(NumericRangeRewriter.Instance)
.AcceptVisitor(IncludeMatchSeedRewriter.Instance)
.AcceptVisitor(TopRewriter.Instance, clonedSearchOptions)
.AcceptVisitor(IncludeRewriter.Instance)
?? SqlRootExpression.WithResourceTableExpressions();
using (SqlConnectionWrapper sqlConnectionWrapper = await _sqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken, true))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
var stringBuilder = new IndentedStringBuilder(new StringBuilder());
EnableTimeAndIoMessageLogging(stringBuilder, sqlConnectionWrapper);
var queryGenerator = new SqlQueryGenerator(
stringBuilder,
new HashingSqlQueryParameterManager(new SqlQueryParameterManager(sqlCommandWrapper.Parameters)),
_model,
searchType,
_schemaInformation,
currentSearchParameterHash);
expression.AcceptVisitor(queryGenerator, clonedSearchOptions);
sqlCommandWrapper.CommandText = stringBuilder.ToString();
LogSqlCommand(sqlCommandWrapper);
using (var reader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
if (clonedSearchOptions.CountOnly)
{
await reader.ReadAsync(cancellationToken);
long count = reader.GetInt64(0);
if (count > int.MaxValue)
{
_requestContextAccessor.RequestContext.BundleIssues.Add(
new OperationOutcomeIssue(
OperationOutcomeConstants.IssueSeverity.Error,
OperationOutcomeConstants.IssueType.NotSupported,
string.Format(Core.Resources.SearchCountResultsExceedLimit, count, int.MaxValue)));
throw new InvalidSearchOperationException(string.Format(Core.Resources.SearchCountResultsExceedLimit, count, int.MaxValue));
}
var searchResult = new SearchResult((int)count, clonedSearchOptions.UnsupportedSearchParams);
// call NextResultAsync to get the info messages
await reader.NextResultAsync(cancellationToken);
return(searchResult);
}
var resources = new List <SearchResultEntry>(sqlSearchOptions.MaxItemCount);
short?newContinuationType = null;
long? newContinuationId = null;
bool moreResults = false;
int matchCount = 0;
string sortValue = null;
var isResultPartial = false;
int numberOfColumnsRead = 0;
while (await reader.ReadAsync(cancellationToken))
{
PopulateResourceTableColumnsToRead(
reader,
out short resourceTypeId,
out string resourceId,
out int version,
out bool isDeleted,
out long resourceSurrogateId,
out string requestMethod,
out bool isMatch,
out bool isPartialEntry,
out bool isRawResourceMetaSet,
out string searchParameterHash,
out Stream rawResourceStream);
numberOfColumnsRead = reader.FieldCount;
// If we get to this point, we know there are more results so we need a continuation token
// Additionally, this resource shouldn't be included in the results
if (matchCount >= clonedSearchOptions.MaxItemCount && isMatch)
{
moreResults = true;
continue;
}
string rawResource;
using (rawResourceStream)
{
rawResource = await _compressedRawResourceConverter.ReadCompressedRawResource(rawResourceStream);
}
// See if this resource is a continuation token candidate and increase the count
if (isMatch)
{
newContinuationType = resourceTypeId;
newContinuationId = resourceSurrogateId;
// For normal queries, we select _defaultNumberOfColumnsReadFromResult number of columns.
// If we have more, that means we have an extra column tracking sort value.
// Keep track of sort value if this is the last row.
if (matchCount == clonedSearchOptions.MaxItemCount - 1 && reader.FieldCount > _defaultNumberOfColumnsReadFromResult)
{
var tempSortValue = reader.GetValue(SortValueColumnName);
if ((tempSortValue as DateTime?) != null)
{
sortValue = (tempSortValue as DateTime?).Value.ToString("o");
}
else
{
sortValue = tempSortValue.ToString();
}
}
matchCount++;
}
// as long as at least one entry was marked as partial, this resultset
// should be marked as partial
isResultPartial = isResultPartial || isPartialEntry;
resources.Add(new SearchResultEntry(
new ResourceWrapper(
resourceId,
version.ToString(CultureInfo.InvariantCulture),
_model.GetResourceTypeName(resourceTypeId),
new RawResource(rawResource, FhirResourceFormat.Json, isMetaSet: isRawResourceMetaSet),
new ResourceRequest(requestMethod),
new DateTimeOffset(ResourceSurrogateIdHelper.ResourceSurrogateIdToLastUpdated(resourceSurrogateId), TimeSpan.Zero),
isDeleted,
null,
null,
null,
searchParameterHash),
isMatch ? SearchEntryMode.Match : SearchEntryMode.Include));
}
// call NextResultAsync to get the info messages
await reader.NextResultAsync(cancellationToken);
ContinuationToken continuationToken =
moreResults
? new ContinuationToken(
clonedSearchOptions.Sort.Select(s =>
s.searchParameterInfo.Name switch
{
SearchParameterNames.ResourceType => (object)newContinuationType,
SearchParameterNames.LastUpdated => newContinuationId,
_ => sortValue,
}).ToArray())
private async Task ExportTableAsync(CancellationToken token,
string executionId,
TableSchema tableSchema,
int batchSize,
int printPercentProgressMod)
{
var rowCount = await _cdcReaderClient.GetRowCountAsync(tableSchema);
Console.WriteLine($"Table {tableSchema.TableName} - {rowCount} rows to export");
int progress = 0;
PrimaryKeyValue lastRetrievedKey = await SetStartingPosition(executionId, tableSchema, batchSize);
long ctr = batchSize;
bool finished = false;
while (!token.IsCancellationRequested && !finished)
{
var changes = new List <RowChange>();
var batch = await _cdcReaderClient.GetBatchAsync(tableSchema, lastRetrievedKey, batchSize);
var result = await WriteToRedshiftAsync(batch, ctr);
if (result.Item1)
{
ctr = result.Item2;
int latestProgress = (int)(((double)ctr / (double)rowCount) * 100);
if (progress != latestProgress && latestProgress % printPercentProgressMod == 0)
{
Console.WriteLine($"Table {tableSchema.TableName} - Progress at {latestProgress}% ({ctr} records)");
}
progress = latestProgress;
lastRetrievedKey = batch.LastRowKey;
if (batch.Records.Any())
{
await _cdcReaderClient.StoreFullLoadOffsetAsync(executionId, tableSchema.TableName, lastRetrievedKey);
}
if (!batch.Records.Any() || batch.Records.Count < batchSize)
{
finished = true;
}
}
else
{
Console.WriteLine($"Table {tableSchema.TableName} - Failed to upload to Redshift. Will try again in 10 seconds.");
await WaitForSeconds(token, 10);
}
}
if (token.IsCancellationRequested)
{
Console.WriteLine($"Table {tableSchema.Schema}.{tableSchema.TableName} - cancelled at progress at {progress}% ({ctr} records)");
}
else
{
Console.WriteLine($"Table {tableSchema.Schema}.{tableSchema.TableName} - complete ({ctr} records)");
}
}
