public override async Task ReindexInstanceAsync(DicomDataset instance, long watermark, IEnumerable <QueryTag> queryTags, CancellationToken cancellationToken = default)
{
EnsureArg.IsNotNull(instance, nameof(instance));
EnsureArg.IsNotNull(queryTags, nameof(queryTags));
using (SqlConnectionWrapper sqlConnectionWrapper = await SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
var rows = ExtendedQueryTagDataRowsBuilder.Build(instance, queryTags, Version);
VLatest.IndexInstanceV2TableValuedParameters parameters = new VLatest.IndexInstanceV2TableValuedParameters(
rows.StringRows,
rows.LongRows,
rows.DoubleRows,
rows.DateTimeWithUtcRows,
rows.PersonNameRows);
VLatest.IndexInstanceV2.PopulateCommand(sqlCommandWrapper, watermark, parameters);
try
{
await sqlCommandWrapper.ExecuteNonQueryAsync(cancellationToken);
}
catch (SqlException ex)
{
throw ex.Number switch
{
SqlErrorCodes.NotFound => new InstanceNotFoundException(),
SqlErrorCodes.Conflict => new PendingInstanceException(),
_ => new DataStoreException(ex),
};
}
}
}
public async Task GivenATransactionScope_WhenReadingAfterComplete_TheValuesShouldBeAvailable()
{
var newId = Guid.NewGuid().ToString();
var searchParamHash = new string("RandomSearchParam").ComputeHash();
using (var transactionScope = _fixture.SqlTransactionHandler.BeginTransaction())
{
using (SqlConnectionWrapper connectionWrapperWithTransaction = await _fixture.SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(CancellationToken.None, true))
using (SqlCommandWrapper sqlCommandWrapper = connectionWrapperWithTransaction.CreateSqlCommand())
{
sqlCommandWrapper.CommandText = @"
INSERT INTO Resource
VALUES(97, @newId, 1, 0, 5095719085917680001, 0, null, CAST('test' AS VARBINARY(MAX)), 0, @searchParamHash)";
sqlCommandWrapper.Parameters.Add(new SqlParameter {
ParameterName = "newId", Value = newId
});
sqlCommandWrapper.Parameters.Add(new SqlParameter {
ParameterName = "searchParamHash", Value = searchParamHash
});
await sqlCommandWrapper.ExecuteNonQueryAsync(CancellationToken.None);
}
transactionScope.Complete();
}
// Outside of the transaction scope, the resource should not be found
using (SqlConnectionWrapper connectionWrapperWithTransaction = await _fixture.SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(CancellationToken.None, false))
{
await VerifyCommandResults(connectionWrapperWithTransaction, newId, true);
}
}
public async Task UpdateSearchParameterIndicesBatchAsync(IReadOnlyCollection <ResourceWrapper> resources, CancellationToken cancellationToken)
{
using (SqlConnectionWrapper sqlConnectionWrapper = await _sqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken, true))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.BulkReindexResources.PopulateCommand(
sqlCommandWrapper,
_bulkReindexResourcesTvpGeneratorVLatest.Generate(resources.ToList()));
try
{
await sqlCommandWrapper.ExecuteNonQueryAsync(cancellationToken);
}
catch (SqlException e)
{
switch (e.Number)
{
// TODO: we should attempt to reindex resources that failed to be reindexed
case SqlErrorCodes.PreconditionFailed:
throw new PreconditionFailedException(string.Format(Core.Resources.ReindexingResourceVersionConflict));
case SqlErrorCodes.NotFound:
throw new ResourceNotFoundException(string.Format(Core.Resources.ReindexingResourceNotFound));
default:
_logger.LogError(e, "Error from SQL database on reindex");
throw;
}
}
}
}
public override async Task AddExtendedQueryTagErrorAsync(
int tagKey,
ValidationErrorCode errorCode,
long watermark,
CancellationToken cancellationToken = default)
{
EnsureArg.EnumIsDefined(errorCode, nameof(errorCode));
using SqlConnectionWrapper sqlConnectionWrapper = await ConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken);
using SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand();
VLatest.AddExtendedQueryTagError.PopulateCommand(
sqlCommandWrapper,
tagKey,
(short)errorCode,
watermark);
try
{
await sqlCommandWrapper.ExecuteNonQueryAsync(cancellationToken);
}
catch (SqlException e)
{
if (e.Number == SqlErrorCodes.NotFound)
{
throw new ExtendedQueryTagNotFoundException(
string.Format(
CultureInfo.InvariantCulture,
DicomSqlServerResource.ExtendedQueryTagNotFoundWhenAddingError,
tagKey));
}
throw new DataStoreException(e);
}
}
public override async Task DeleteExtendedQueryTagAsync(string tagPath, string vr, CancellationToken cancellationToken = default)
{
using (SqlConnectionWrapper sqlConnectionWrapper = await ConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
V2.DeleteExtendedQueryTag.PopulateCommand(sqlCommandWrapper, tagPath, (byte)ExtendedQueryTagLimit.ExtendedQueryTagVRAndDataTypeMapping[vr]);
try
{
await sqlCommandWrapper.ExecuteNonQueryAsync(cancellationToken);
}
catch (SqlException ex)
{
switch (ex.Number)
{
case SqlErrorCodes.NotFound:
throw new ExtendedQueryTagNotFoundException(
string.Format(CultureInfo.InvariantCulture, DicomSqlServerResource.ExtendedQueryTagNotFound, tagPath));
case SqlErrorCodes.PreconditionFailed:
throw new ExtendedQueryTagBusyException(
string.Format(CultureInfo.InvariantCulture, DicomSqlServerResource.ExtendedQueryTagIsBusy, tagPath));
default:
throw new DataStoreException(ex);
}
}
}
}
public async Task AddExtendedQueryTagsAsync(IEnumerable <AddExtendedQueryTagEntry> extendedQueryTagEntries, CancellationToken cancellationToken = default)
{
if (_schemaInformation.Current < SchemaVersionConstants.SupportExtendedQueryTagSchemaVersion)
{
throw new BadRequestException(DicomSqlServerResource.SchemaVersionNeedsToBeUpgraded);
}
using (SqlConnectionWrapper sqlConnectionWrapper = await _sqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
IEnumerable <AddExtendedQueryTagsInputTableTypeV1Row> rows = extendedQueryTagEntries.Select(ToAddExtendedQueryTagsInputTableTypeV1Row);
VLatest.AddExtendedQueryTags.PopulateCommand(sqlCommandWrapper, new VLatest.AddExtendedQueryTagsTableValuedParameters(rows));
try
{
await sqlCommandWrapper.ExecuteNonQueryAsync(cancellationToken);
}
catch (SqlException ex)
{
switch (ex.Number)
{
case SqlErrorCodes.Conflict:
throw new ExtendedQueryTagsAlreadyExistsException();
default:
throw new DataStoreException(ex);
}
}
}
}
public async Task DeleteExtendedQueryTagAsync(string tagPath, string vr, CancellationToken cancellationToken = default)
{
if (_schemaInformation.Current < SchemaVersionConstants.SupportExtendedQueryTagSchemaVersion)
{
throw new BadRequestException(DicomSqlServerResource.SchemaVersionNeedsToBeUpgraded);
}
using (SqlConnectionWrapper sqlConnectionWrapper = await _sqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.DeleteExtendedQueryTag.PopulateCommand(sqlCommandWrapper, tagPath, (byte)ExtendedQueryTagLimit.ExtendedQueryTagVRAndDataTypeMapping[vr]);
try
{
await sqlCommandWrapper.ExecuteNonQueryAsync(cancellationToken);
}
catch (SqlException ex)
{
switch (ex.Number)
{
case SqlErrorCodes.NotFound:
throw new ExtendedQueryTagNotFoundException(
string.Format(CultureInfo.InvariantCulture, DicomSqlServerResource.ExtendedQueryTagNotFound, tagPath));
case SqlErrorCodes.PreconditionFailed:
throw new ExtendedQueryTagBusyException(
string.Format(CultureInfo.InvariantCulture, DicomSqlServerResource.ExtendedQueryTagIsBusy, tagPath));
default:
throw new DataStoreException(ex);
}
}
}
}
public async Task HardDeleteAsync(ResourceKey key, CancellationToken cancellationToken)
{
using (SqlConnectionWrapper sqlConnectionWrapper = _sqlConnectionWrapperFactory.ObtainSqlConnectionWrapper(true))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.HardDeleteResource.PopulateCommand(sqlCommandWrapper, resourceTypeId: _model.GetResourceTypeId(key.ResourceType), resourceId: key.Id);
await sqlCommandWrapper.ExecuteNonQueryAsync(cancellationToken);
}
}
public async Task GivenATransactionScope_WhenReading_TheUncommittedValuesShouldOnlyBeAvailableWithTheTransactionAndWithHints()
{
var newId = Guid.NewGuid().ToString();
using (var transactionScope = _fixture.SqlTransactionHandler.BeginTransaction())
{
using (SqlConnectionWrapper connectionWrapperWithTransaction = _fixture.SqlConnectionWrapperFactory.ObtainSqlConnectionWrapper(true))
using (SqlCommandWrapper sqlCommandWrapper = connectionWrapperWithTransaction.CreateSqlCommand())
{
sqlCommandWrapper.CommandText = @"
INSERT INTO Resource
VALUES(97, @newId, 1, 0, 5095719085917680000, 0, null, CAST('test' AS VARBINARY(MAX)), 0)";
sqlCommandWrapper.Parameters.Add(new SqlParameter {
ParameterName = "newId", Value = newId
});
await sqlCommandWrapper.ExecuteNonQueryAsync(CancellationToken.None);
}
// Within the same transaction, the resource should be found
using (SqlConnectionWrapper connectionWrapperWithTransaction = _fixture.SqlConnectionWrapperFactory.ObtainSqlConnectionWrapper(true))
{
await VerifyCommandResults(connectionWrapperWithTransaction, newId, true);
}
// Outside of the transaction, the resource should not be found
using (SqlConnectionWrapper connectionWrapperWithTransaction = _fixture.SqlConnectionWrapperFactory.ObtainSqlConnectionWrapper(false))
{
await VerifyCommandResults(connectionWrapperWithTransaction, newId, false);
}
// Outside of the transaction, but with the readuncommitted hint, the resource should be found.
using (SqlConnectionWrapper connectionWrapperWithTransaction = _fixture.SqlConnectionWrapperFactory.ObtainSqlConnectionWrapper(false))
{
await VerifyCommandResults(connectionWrapperWithTransaction, newId, true, "WITH (READUNCOMMITTED)");
}
}
// Outside of the transactionscope, the resource should not be found
using (SqlConnectionWrapper connectionWrapperWithTransaction = _fixture.SqlConnectionWrapperFactory.ObtainSqlConnectionWrapper(false))
{
await VerifyCommandResults(connectionWrapperWithTransaction, newId, false);
}
// Outside of the transactionscope, but with the readuncommitted hint, the resource should not be found
using (SqlConnectionWrapper connectionWrapperWithTransaction = _fixture.SqlConnectionWrapperFactory.ObtainSqlConnectionWrapper(false))
{
await VerifyCommandResults(connectionWrapperWithTransaction, newId, false, "WITH (READUNCOMMITTED)");
}
}
public async Task DeleteExpiredInstanceSchemaAsync(CancellationToken cancellationToken)
{
using (SqlConnectionWrapper sqlConnectionWrapper = await _sqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken: cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
SchemaShared.DeleteInstanceSchema.PopulateCommand(sqlCommandWrapper);
try
{
await sqlCommandWrapper.ExecuteNonQueryAsync(cancellationToken);
}
catch (SqlException e)
{
_logger.LogError(e, "Error from SQL database on deleting expired InstanceSchema records");
throw;
}
}
}
public async Task HardDeleteAsync(ResourceKey key, bool keepCurrentVersion, CancellationToken cancellationToken)
{
using (SqlConnectionWrapper sqlConnectionWrapper = await _sqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken, true))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
if (_schemaInformation.Current >= SchemaVersionConstants.PurgeHistoryVersion)
{
VLatest.HardDeleteResource.PopulateCommand(sqlCommandWrapper, resourceTypeId: _model.GetResourceTypeId(key.ResourceType), resourceId: key.Id, Convert.ToInt16(keepCurrentVersion));
}
else if (!keepCurrentVersion)
{
V12.HardDeleteResource.PopulateCommand(sqlCommandWrapper, resourceTypeId: _model.GetResourceTypeId(key.ResourceType), resourceId: key.Id);
}
else
{
throw new BadRequestException(Resources.SchemaVersionNeedsToBeUpgraded);
}
await sqlCommandWrapper.ExecuteNonQueryAsync(cancellationToken);
}
}
public override async Task <IReadOnlyList <ExtendedQueryTagStoreEntry> > AddExtendedQueryTagsAsync(
IReadOnlyCollection <AddExtendedQueryTagEntry> extendedQueryTagEntries,
int maxCount,
bool ready = false,
CancellationToken cancellationToken = default)
{
if (ready)
{
throw new BadRequestException(DicomSqlServerResource.SchemaVersionNeedsToBeUpgraded);
}
using (SqlConnectionWrapper sqlConnectionWrapper = await ConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
IEnumerable <AddExtendedQueryTagsInputTableTypeV1Row> rows = extendedQueryTagEntries.Select(ToAddExtendedQueryTagsInputTableTypeV1Row);
V2.AddExtendedQueryTags.PopulateCommand(sqlCommandWrapper, new V2.AddExtendedQueryTagsTableValuedParameters(rows));
try
{
await sqlCommandWrapper.ExecuteNonQueryAsync(cancellationToken);
var allTags = (await GetAllExtendedQueryTagsAsync(cancellationToken)).ToDictionary(x => x.Path);
return(extendedQueryTagEntries
.Select(x => allTags[x.Path])
.ToList());
}
catch (SqlException ex)
{
throw ex.Number switch
{
SqlErrorCodes.Conflict => new ExtendedQueryTagsAlreadyExistsException(),
_ => new DataStoreException(ex),
};
}
}
}
public async Task UpdateContextAsync(string context, CancellationToken cancellationToken)
{
using (SqlConnectionWrapper sqlConnectionWrapper = await _sqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken, true))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
try
{
VLatest.UpdateTaskContext.PopulateCommand(sqlCommandWrapper, _taskId, context, _runId);
await sqlCommandWrapper.ExecuteNonQueryAsync(cancellationToken);
}
catch (SqlException sqlEx)
{
_logger.LogInformation(sqlEx, "Failed to update context.");
if (sqlEx.Number == SqlErrorCodes.NotFound)
{
throw new TaskNotExistException(sqlEx.Message);
}
throw;
}
}
}
// TODO: Make cancellation token an input.
public async Task UpsertStatuses(List <ResourceSearchParameterStatus> statuses)
{
EnsureArg.IsNotNull(statuses, nameof(statuses));
if (!statuses.Any())
{
return;
}
if (_schemaInformation.Current < SchemaVersionConstants.SearchParameterStatusSchemaVersion)
{
throw new BadRequestException(Resources.SchemaVersionNeedsToBeUpgraded);
}
using (IScoped <SqlConnectionWrapperFactory> scopedSqlConnectionWrapperFactory = _scopedSqlConnectionWrapperFactory())
using (SqlConnectionWrapper sqlConnectionWrapper = await scopedSqlConnectionWrapperFactory.Value.ObtainSqlConnectionWrapperAsync(CancellationToken.None, true))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.UpsertSearchParams.PopulateCommand(sqlCommandWrapper, _updateSearchParamsTvpGenerator.Generate(statuses));
await sqlCommandWrapper.ExecuteNonQueryAsync(CancellationToken.None);
}
}
public async Task GivenATransientException_WhenNonQueryIsExecuted_ThenItShouldRetry()
{
_sqlCommandWrapper.ExecuteNonQueryAsync(DefaultCancellationToken).Throws(CreateTransientException());
await ExecuteAndValidateExecuteNonQueryAsync(4);
}
public async Task BulkUpdateSearchParameterIndicesAsync(IReadOnlyCollection <ResourceWrapper> resources, CancellationToken cancellationToken)
{
using (SqlConnectionWrapper sqlConnectionWrapper = await _sqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken, true))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
if (_schemaInformation.Current >= SchemaVersionConstants.AddMinMaxForDateAndStringSearchParamVersion)
{
VLatest.BulkReindexResources.PopulateCommand(
sqlCommandWrapper,
_bulkReindexResourcesTvpGeneratorVLatest.Generate(resources.ToList()));
}
else
{
V17.BulkReindexResources.PopulateCommand(
sqlCommandWrapper,
_bulkReindexResourcesTvpGeneratorV17.Generate(resources.ToList()));
}
if (_schemaInformation.Current >= SchemaVersionConstants.BulkReindexReturnsFailuresVersion)
{
// We will reindex the rest of the batch if one resource has a versioning conflict
int?failedResourceCount;
try
{
failedResourceCount = (int?)await sqlCommandWrapper.ExecuteScalarAsync(cancellationToken);
}
catch (SqlException e)
{
_logger.LogError(e, "Error from SQL database on reindex.");
throw;
}
if (failedResourceCount != 0)
{
string message = string.Format(Core.Resources.ReindexingResourceVersionConflictWithCount, failedResourceCount);
string userAction = Core.Resources.ReindexingUserAction;
_logger.LogError(message);
throw new PreconditionFailedException(message + " " + userAction);
}
}
else
{
try
{
// We are running an earlier schema version where we will fail the whole batch if there is a conflict
await sqlCommandWrapper.ExecuteNonQueryAsync(cancellationToken);
}
catch (SqlException e)
{
switch (e.Number)
{
case SqlErrorCodes.PreconditionFailed:
string message = Core.Resources.ReindexingResourceVersionConflict;
string userAction = Core.Resources.ReindexingUserAction;
_logger.LogError(message);
throw new PreconditionFailedException(message + " " + userAction);
default:
_logger.LogError(e, "Error from SQL database on reindex.");
throw;
}
}
}
}
}
