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 override async Task <ExtendedQueryTagStoreJoinEntry> GetExtendedQueryTagAsync(string path, CancellationToken cancellationToken = default)
{
using (SqlConnectionWrapper sqlConnectionWrapper = await ConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.GetExtendedQueryTag.PopulateCommand(sqlCommandWrapper, path);
var executionTimeWatch = Stopwatch.StartNew();
using (var reader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
if (!await reader.ReadAsync(cancellationToken))
{
throw new ExtendedQueryTagNotFoundException(string.Format(DicomCoreResource.ExtendedQueryTagNotFound, path));
}
(int tagKey, string tagPath, string tagVR, string tagPrivateCreator, int tagLevel, int tagStatus, byte queryStatus, int errorCount, Guid? operationId) = reader.ReadRow(
VLatest.ExtendedQueryTag.TagKey,
VLatest.ExtendedQueryTag.TagPath,
VLatest.ExtendedQueryTag.TagVR,
VLatest.ExtendedQueryTag.TagPrivateCreator,
VLatest.ExtendedQueryTag.TagLevel,
VLatest.ExtendedQueryTag.TagStatus,
VLatest.ExtendedQueryTag.QueryStatus,
VLatest.ExtendedQueryTag.ErrorCount,
VLatest.ExtendedQueryTagOperation.OperationId.AsNullable());
executionTimeWatch.Stop();
Logger.StoredProcedureSucceeded(nameof(VLatest.GetExtendedQueryTag), executionTimeWatch);
return(new ExtendedQueryTagStoreJoinEntry(tagKey, tagPath, tagVR, tagPrivateCreator, (QueryTagLevel)tagLevel, (ExtendedQueryTagStatus)tagStatus, (QueryStatus)queryStatus, errorCount, operationId));
}
}
}
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 <int> IncrementDeletedInstanceRetryAsync(VersionedInstanceIdentifier versionedInstanceIdentifier, DateTimeOffset cleanupAfter, CancellationToken cancellationToken = default)
{
await _sqlServerIndexSchema.EnsureInitialized();
using (SqlConnectionWrapper sqlConnectionWrapper = _sqlConnectionFactoryWrapper.ObtainSqlConnectionWrapper(true))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.IncrementDeletedInstanceRetry.PopulateCommand(
sqlCommandWrapper,
versionedInstanceIdentifier.StudyInstanceUid,
versionedInstanceIdentifier.SeriesInstanceUid,
versionedInstanceIdentifier.SopInstanceUid,
versionedInstanceIdentifier.Version,
cleanupAfter);
try
{
return((int)(await sqlCommandWrapper.ExecuteScalarAsync(cancellationToken)));
}
catch (SqlException ex)
{
throw new DataStoreException(ex);
}
}
}
private static async Task VerifyCommandResults(SqlConnectionWrapper connectionWrapper, string newId, bool shouldFind, string tableHints = "")
{
using (SqlCommand command = connectionWrapper.CreateSqlCommand())
{
command.CommandText = $@"
SELECT *
FROM resource {tableHints}
WHERE ResourceId = @newId";
command.Parameters.Add(new SqlParameter {
ParameterName = "newId", Value = newId
});
using (var reader = await command.ExecuteReaderAsync())
{
if (shouldFind)
{
while (reader.Read())
{
Assert.Equal(newId, reader["resourceId"]);
}
}
else
{
Assert.False(reader.HasRows);
}
}
}
}
public async Task UpsertStatuses(IReadOnlyCollection <ResourceSearchParameterStatus> statuses, CancellationToken cancellationToken)
{
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, true))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.UpsertSearchParams.PopulateCommand(sqlCommandWrapper, _updateSearchParamsTvpGenerator.Generate(statuses.ToList()));
using (SqlDataReader sqlDataReader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
while (await sqlDataReader.ReadAsync(cancellationToken))
{
// The upsert procedure returns the search parameters that were new.
(short searchParamId, string searchParamUri) = sqlDataReader.ReadRow(VLatest.SearchParam.SearchParamId, VLatest.SearchParam.Uri);
// Add the new search parameters to the FHIR model dictionary.
_fhirModel.TryAddSearchParamIdToUriMapping(searchParamUri, searchParamId);
}
}
}
}
public async Task GivenATransactionScope_WhenReadingAfterComplete_TheValuesShouldBeAvailable()
{
var newId = Guid.NewGuid().ToString();
using (var transactionScope = _fixture.SqlTransactionHandler.BeginTransaction())
{
using (SqlConnectionWrapper connectionWrapperWithTransaction = _fixture.SqlConnectionWrapperFactory.ObtainSqlConnectionWrapper(true))
{
using (SqlCommand command = connectionWrapperWithTransaction.CreateSqlCommand())
{
command.CommandText = @"
INSERT INTO Resource
VALUES(97, @newId, 1, 0, 5095719085917680001, 0, null, CAST('test' AS VARBINARY(MAX)))";
command.Parameters.Add(new SqlParameter {
ParameterName = "newId", Value = newId
});
command.ExecuteNonQuery();
}
}
transactionScope.Complete();
}
// Outside of the transactionscope, the resource should not be found
using (SqlConnectionWrapper connectionWrapperWithTransaction = _fixture.SqlConnectionWrapperFactory.ObtainSqlConnectionWrapper(false))
{
await VerifyCommandResults(connectionWrapperWithTransaction, newId, true);
}
}
public override async Task <IReadOnlyList <int> > CompleteReindexingAsync(IReadOnlyCollection <int> queryTagKeys, CancellationToken cancellationToken = default)
{
EnsureArg.HasItems(queryTagKeys, nameof(queryTagKeys));
using SqlConnectionWrapper sqlConnectionWrapper = await ConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken);
using SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand();
IEnumerable <ExtendedQueryTagKeyTableTypeV1Row> rows = queryTagKeys.Select(x => new ExtendedQueryTagKeyTableTypeV1Row(x));
VLatest.CompleteReindexing.PopulateCommand(sqlCommandWrapper, rows);
try
{
var keys = new List <int>();
using SqlDataReader reader = await sqlCommandWrapper.ExecuteReaderAsync(cancellationToken);
while (await reader.ReadAsync(cancellationToken))
{
keys.Add(reader.ReadRow(VLatest.ExtendedQueryTagString.TagKey));
}
return(keys);
}
catch (SqlException ex)
{
throw new DataStoreException(ex);
}
}
public override async Task <IReadOnlyList <ExtendedQueryTagStoreEntry> > GetExtendedQueryTagsAsync(Guid operationId, CancellationToken cancellationToken = default)
{
var results = new List <ExtendedQueryTagStoreEntry>();
using (SqlConnectionWrapper sqlConnectionWrapper = await ConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.GetExtendedQueryTagsByOperation.PopulateCommand(sqlCommandWrapper, operationId);
using (SqlDataReader reader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
while (await reader.ReadAsync(cancellationToken))
{
(int tagKey, string tagPath, string tagVR, string tagPrivateCreator, int tagLevel, int tagStatus, byte queryStatus, int errorCount) = reader.ReadRow(
VLatest.ExtendedQueryTag.TagKey,
VLatest.ExtendedQueryTag.TagPath,
VLatest.ExtendedQueryTag.TagVR,
VLatest.ExtendedQueryTag.TagPrivateCreator,
VLatest.ExtendedQueryTag.TagLevel,
VLatest.ExtendedQueryTag.TagStatus,
VLatest.ExtendedQueryTag.QueryStatus,
VLatest.ExtendedQueryTag.ErrorCount);
results.Add(new ExtendedQueryTagStoreEntry(tagKey, tagPath, tagVR, tagPrivateCreator, (QueryTagLevel)tagLevel, (ExtendedQueryTagStatus)tagStatus, (QueryStatus)queryStatus, errorCount));
}
}
}
return(results);
}
public override async Task <IReadOnlyList <WatermarkRange> > GetInstanceBatchesAsync(
int batchSize,
int batchCount,
IndexStatus indexStatus,
long?maxWatermark = null,
CancellationToken cancellationToken = default)
{
EnsureArg.IsGt(batchSize, 0, nameof(batchSize));
EnsureArg.IsGt(batchCount, 0, nameof(batchCount));
using SqlConnectionWrapper sqlConnectionWrapper = await SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken);
using SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand();
VLatest.GetInstanceBatches.PopulateCommand(sqlCommandWrapper, batchSize, batchCount, (byte)indexStatus, maxWatermark);
try
{
var batches = new List <WatermarkRange>();
using SqlDataReader reader = await sqlCommandWrapper.ExecuteReaderAsync(cancellationToken);
while (await reader.ReadAsync(cancellationToken))
{
batches.Add(new WatermarkRange(reader.GetInt64(0), reader.GetInt64(1)));
}
return(batches);
}
catch (SqlException ex)
{
throw new DataStoreException(ex);
}
}
private async Task <List <ExtendedQueryTagStoreJoinEntry> > GetAllExtendedQueryTagsAsync(CancellationToken cancellationToken = default)
{
var results = new List <ExtendedQueryTagStoreJoinEntry>();
using (SqlConnectionWrapper sqlConnectionWrapper = await ConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
// V2 version allows NULL to get all tags
V2.GetExtendedQueryTag.PopulateCommand(sqlCommandWrapper, null);
var executionTimeWatch = Stopwatch.StartNew();
using (var reader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
while (await reader.ReadAsync(cancellationToken))
{
(int tagKey, string tagPath, string tagVR, string tagPrivateCreator, int tagLevel, int tagStatus) = reader.ReadRow(
V2.ExtendedQueryTag.TagKey,
V2.ExtendedQueryTag.TagPath,
V2.ExtendedQueryTag.TagVR,
V2.ExtendedQueryTag.TagPrivateCreator,
V2.ExtendedQueryTag.TagLevel,
V2.ExtendedQueryTag.TagStatus);
results.Add(new ExtendedQueryTagStoreJoinEntry(tagKey, tagPath, tagVR, tagPrivateCreator, (QueryTagLevel)tagLevel, (ExtendedQueryTagStatus)tagStatus, QueryStatus.Enabled, 0));
}
executionTimeWatch.Stop();
Logger.StoredProcedureSucceeded(nameof(V2.GetExtendedQueryTag), executionTimeWatch);
}
}
return(results);
}
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 override async Task <PartitionEntry> GetPartitionAsync(string partitionName, CancellationToken cancellationToken)
{
using (SqlConnectionWrapper sqlConnectionWrapper = await SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.GetPartition.PopulateCommand(sqlCommandWrapper, partitionName);
using (var reader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
if (await reader.ReadAsync(cancellationToken))
{
(int rPartitionKey, string rPartitionName, DateTimeOffset rCreatedDate) = reader.ReadRow(
VLatest.Partition.PartitionKey,
VLatest.Partition.PartitionName,
VLatest.Partition.CreatedDate);
return(new PartitionEntry(
rPartitionKey,
rPartitionName,
rCreatedDate));
}
}
}
return(null);
}
public override async Task <IEnumerable <PartitionEntry> > GetPartitionsAsync(CancellationToken cancellationToken)
{
var results = new List <PartitionEntry>();
using (SqlConnectionWrapper sqlConnectionWrapper = await SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.GetPartitions.PopulateCommand(sqlCommandWrapper);
using (var reader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
while (await reader.ReadAsync(cancellationToken))
{
(int rPartitionKey, string rPartitionName, DateTimeOffset rCreatedDate) = reader.ReadRow(
VLatest.Partition.PartitionKey,
VLatest.Partition.PartitionName,
VLatest.Partition.CreatedDate);
results.Add(new PartitionEntry(
rPartitionKey,
rPartitionName,
rCreatedDate));
}
}
return(results);
}
}
// TODO: Make cancellation token an input.
public async Task <IReadOnlyCollection <ResourceSearchParameterStatus> > GetSearchParameterStatuses()
{
// If the search parameter table in SQL does not yet contain status columns
if (_schemaInformation.Current < SchemaVersionConstants.SearchParameterStatusSchemaVersion)
{
// Get status information from file.
return(await _filebasedSearchParameterStatusDataStore.GetSearchParameterStatuses());
}
using (IScoped <SqlConnectionWrapperFactory> scopedSqlConnectionWrapperFactory = _scopedSqlConnectionWrapperFactory())
using (SqlConnectionWrapper sqlConnectionWrapper = await scopedSqlConnectionWrapperFactory.Value.ObtainSqlConnectionWrapperAsync(CancellationToken.None, true))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.GetSearchParamStatuses.PopulateCommand(sqlCommandWrapper);
var parameterStatuses = new List <ResourceSearchParameterStatus>();
using (SqlDataReader sqlDataReader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, CancellationToken.None))
{
while (await sqlDataReader.ReadAsync())
{
(string uri, string stringStatus, DateTimeOffset? lastUpdated, bool?isPartiallySupported) = sqlDataReader.ReadRow(
VLatest.SearchParam.Uri,
VLatest.SearchParam.Status,
VLatest.SearchParam.LastUpdated,
VLatest.SearchParam.IsPartiallySupported);
if (string.IsNullOrEmpty(stringStatus) || lastUpdated == null || isPartiallySupported == null)
{
// These columns are nullable because they are added to dbo.SearchParam in a later schema version.
// They should be populated as soon as they are added to the table and should never be null.
throw new NullReferenceException(Resources.SearchParameterStatusShouldNotBeNull);
}
var status = Enum.Parse <SearchParameterStatus>(stringStatus, true);
var resourceSearchParameterStatus = new ResourceSearchParameterStatus()
{
Uri = new Uri(uri),
Status = status,
IsPartiallySupported = (bool)isPartiallySupported,
LastUpdated = (DateTimeOffset)lastUpdated,
};
if (SqlServerSortingValidator.SupportedParameterUris.Contains(resourceSearchParameterStatus.Uri))
{
resourceSearchParameterStatus.SortStatus = SortParameterStatus.Enabled;
}
else
{
resourceSearchParameterStatus.SortStatus = SortParameterStatus.Supported;
}
parameterStatuses.Add(resourceSearchParameterStatus);
}
}
return(parameterStatuses);
}
}
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 <int> RetrieveNumExhaustedDeletedInstanceAttemptsAsync(int maxNumberOfRetries, CancellationToken cancellationToken = default)
{
using (SqlConnectionWrapper sqlConnectionWrapper = await SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
sqlCommandWrapper.CommandText = @$ "
SELECT COUNT(*)
FROM {VLatest.DeletedInstance.TableName}
WHERE {VLatest.DeletedInstance.RetryCount} >= @maxNumberOfRetries";
public override async Task <long> BeginCreateInstanceIndexAsync(int partitionKey, DicomDataset instance, IEnumerable <QueryTag> queryTags, CancellationToken cancellationToken)
{
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.Where(tag => tag.IsExtendedQueryTag), Version);
VLatest.AddInstanceV6TableValuedParameters parameters = new VLatest.AddInstanceV6TableValuedParameters(
rows.StringRows,
rows.LongRows,
rows.DoubleRows,
rows.DateTimeWithUtcRows,
rows.PersonNameRows
);
VLatest.AddInstanceV6.PopulateCommand(
sqlCommandWrapper,
partitionKey,
instance.GetString(DicomTag.StudyInstanceUID),
instance.GetString(DicomTag.SeriesInstanceUID),
instance.GetString(DicomTag.SOPInstanceUID),
instance.GetSingleValueOrDefault <string>(DicomTag.PatientID),
instance.GetSingleValueOrDefault <string>(DicomTag.PatientName),
instance.GetSingleValueOrDefault <string>(DicomTag.ReferringPhysicianName),
instance.GetStringDateAsDate(DicomTag.StudyDate),
instance.GetSingleValueOrDefault <string>(DicomTag.StudyDescription),
instance.GetSingleValueOrDefault <string>(DicomTag.AccessionNumber),
instance.GetSingleValueOrDefault <string>(DicomTag.Modality),
instance.GetStringDateAsDate(DicomTag.PerformedProcedureStepStartDate),
instance.GetStringDateAsDate(DicomTag.PatientBirthDate),
instance.GetSingleValueOrDefault <string>(DicomTag.ManufacturerModelName),
(byte)IndexStatus.Creating,
parameters);
try
{
return((long)(await sqlCommandWrapper.ExecuteScalarAsync(cancellationToken)));
}
catch (SqlException ex)
{
if (ex.Number == SqlErrorCodes.Conflict)
{
if (ex.State == (byte)IndexStatus.Creating)
{
throw new PendingInstanceException();
}
throw new InstanceAlreadyExistsException();
}
throw new DataStoreException(ex);
}
}
}
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 (SqlCommand command = connectionWrapperWithTransaction.CreateSqlCommand())
{
command.CommandText = @"
INSERT INTO Resource
VALUES(97, @newId, 1, 0, 5095719085917680000, 0, null, CAST('test' AS VARBINARY(MAX)))";
command.Parameters.Add(new SqlParameter {
ParameterName = "newId", Value = newId
});
command.ExecuteNonQuery();
}
}
// 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 override async Task <long> CreateInstanceIndexAsync(DicomDataset instance, IEnumerable <QueryTag> queryTags, CancellationToken cancellationToken)
{
EnsureArg.IsNotNull(instance, nameof(instance));
EnsureArg.IsNotNull(queryTags, nameof(queryTags));
using (SqlConnectionWrapper sqlConnectionWrapper = await _sqlConnectionFactoryWrapper.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
// Build parameter for extended query tag.
VLatest.AddInstanceTableValuedParameters parameters = AddInstanceTableValuedParametersBuilder.Build(
instance,
queryTags.Where(tag => tag.IsExtendedQueryTag));
VLatest.AddInstance.PopulateCommand(
sqlCommandWrapper,
instance.GetString(DicomTag.StudyInstanceUID),
instance.GetString(DicomTag.SeriesInstanceUID),
instance.GetString(DicomTag.SOPInstanceUID),
instance.GetSingleValueOrDefault <string>(DicomTag.PatientID),
instance.GetSingleValueOrDefault <string>(DicomTag.PatientName),
instance.GetSingleValueOrDefault <string>(DicomTag.ReferringPhysicianName),
instance.GetStringDateAsDate(DicomTag.StudyDate),
instance.GetSingleValueOrDefault <string>(DicomTag.StudyDescription),
instance.GetSingleValueOrDefault <string>(DicomTag.AccessionNumber),
instance.GetSingleValueOrDefault <string>(DicomTag.Modality),
instance.GetStringDateAsDate(DicomTag.PerformedProcedureStepStartDate),
(byte)IndexStatus.Creating,
parameters);
try
{
return((long)(await sqlCommandWrapper.ExecuteScalarAsync(cancellationToken)));
}
catch (SqlException ex)
{
switch (ex.Number)
{
case SqlErrorCodes.Conflict:
{
if (ex.State == (byte)IndexStatus.Creating)
{
throw new PendingInstanceException();
}
throw new InstanceAlreadyExistsException();
}
}
throw new DataStoreException(ex);
}
}
}
public override async Task <IReadOnlyList <ExtendedQueryTagStoreEntry> > AssignReindexingOperationAsync(
IReadOnlyCollection <int> queryTagKeys,
Guid operationId,
bool returnIfCompleted = false,
CancellationToken cancellationToken = default)
{
EnsureArg.HasItems(queryTagKeys, nameof(queryTagKeys));
using SqlConnectionWrapper sqlConnectionWrapper = await ConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken);
using SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand();
IEnumerable <ExtendedQueryTagKeyTableTypeV1Row> rows = queryTagKeys.Select(x => new ExtendedQueryTagKeyTableTypeV1Row(x));
VLatest.AssignReindexingOperation.PopulateCommand(sqlCommandWrapper, rows, operationId, returnIfCompleted);
try
{
var queryTags = new List <ExtendedQueryTagStoreEntry>();
using SqlDataReader reader = await sqlCommandWrapper.ExecuteReaderAsync(cancellationToken);
while (await reader.ReadAsync(cancellationToken))
{
(int tagKey, string tagPath, string tagVR, string tagPrivateCreator, byte tagLevel, byte tagStatus, byte queryStatus, int errorCount) = reader.ReadRow(
VLatest.ExtendedQueryTag.TagKey,
VLatest.ExtendedQueryTag.TagPath,
VLatest.ExtendedQueryTag.TagVR,
VLatest.ExtendedQueryTag.TagPrivateCreator,
VLatest.ExtendedQueryTag.TagLevel,
VLatest.ExtendedQueryTag.TagStatus,
VLatest.ExtendedQueryTag.QueryStatus,
VLatest.ExtendedQueryTag.ErrorCount);
queryTags.Add(new ExtendedQueryTagStoreEntry(
tagKey,
tagPath,
tagVR,
tagPrivateCreator,
(QueryTagLevel)tagLevel,
(ExtendedQueryTagStatus)tagStatus,
(QueryStatus)queryStatus,
errorCount));
}
return(queryTags);
}
catch (SqlException ex)
{
throw new DataStoreException(ex);
}
}
public override async Task <IReadOnlyList <ExtendedQueryTagStoreEntry> > AddExtendedQueryTagsAsync(
IReadOnlyCollection <AddExtendedQueryTagEntry> extendedQueryTagEntries,
int maxAllowedCount,
bool ready = false,
CancellationToken cancellationToken = default)
{
EnsureArg.IsNotNull(extendedQueryTagEntries, nameof(extendedQueryTagEntries));
EnsureArg.IsGt(maxAllowedCount, 0, nameof(maxAllowedCount));
using SqlConnectionWrapper sqlConnectionWrapper = await ConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken);
using SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand();
IEnumerable <AddExtendedQueryTagsInputTableTypeV1Row> rows = extendedQueryTagEntries.Select(ToAddExtendedQueryTagsInputTableTypeV1Row);
VLatest.AddExtendedQueryTags.PopulateCommand(sqlCommandWrapper, maxAllowedCount, ready, new VLatest.AddExtendedQueryTagsTableValuedParameters(rows));
try
{
var results = new List <ExtendedQueryTagStoreEntry>();
using SqlDataReader reader = await sqlCommandWrapper.ExecuteReaderAsync(cancellationToken);
while (await reader.ReadAsync(cancellationToken))
{
(int tagKey, string tagPath, string tagVR, string tagPrivateCreator, int tagLevel, int tagStatus, byte queryStatus, int errorCount) = reader.ReadRow(
VLatest.ExtendedQueryTag.TagKey,
VLatest.ExtendedQueryTag.TagPath,
VLatest.ExtendedQueryTag.TagVR,
VLatest.ExtendedQueryTag.TagPrivateCreator,
VLatest.ExtendedQueryTag.TagLevel,
VLatest.ExtendedQueryTag.TagStatus,
VLatest.ExtendedQueryTag.QueryStatus,
VLatest.ExtendedQueryTag.ErrorCount);
results.Add(new ExtendedQueryTagStoreEntry(tagKey, tagPath, tagVR, tagPrivateCreator, (QueryTagLevel)tagLevel, (ExtendedQueryTagStatus)tagStatus, (QueryStatus)queryStatus, errorCount));
}
return(results);
}
catch (SqlException ex)
{
throw ex.Number switch
{
SqlErrorCodes.Conflict => ex.State == 1
? new ExtendedQueryTagsExceedsMaxAllowedCountException(maxAllowedCount)
: new ExtendedQueryTagsAlreadyExistsException(),
_ => new DataStoreException(ex),
};
}
}
public async Task <List <CurrentVersionInformation> > GetCurrentVersionAsync(CancellationToken cancellationToken)
{
var currentVersions = new List <CurrentVersionInformation>();
using (SqlConnectionWrapper sqlConnectionWrapper = await _sqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken: cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
SchemaShared.SelectCurrentVersionsInformation.PopulateCommand(sqlCommandWrapper);
try
{
using (var dataReader = await sqlCommandWrapper.ExecuteReaderAsync(cancellationToken))
{
if (dataReader.HasRows)
{
while (await dataReader.ReadAsync(cancellationToken))
{
IList <string> instanceNames = new List <string>();
if (!dataReader.IsDBNull(2))
{
string names = dataReader.GetString(2);
instanceNames = names.Split(",").ToList();
}
var status = (string)dataReader.GetValue(1);
// To combine the complete and completed version since earlier status was marked in 'complete' status and now the fix has made to mark the status in completed state
status = string.Equals(status, "complete", StringComparison.OrdinalIgnoreCase) ? "completed" : status;
var schemaVersionStatus = (SchemaVersionStatus)Enum.Parse(typeof(SchemaVersionStatus), status, true);
var currentVersion = new CurrentVersionInformation((int)dataReader.GetValue(0), schemaVersionStatus, instanceNames);
currentVersions.Add(currentVersion);
}
}
else
{
return(currentVersions);
}
}
}
catch (SqlException e)
{
_logger.LogError(e, "Error from SQL database on retrieving current version information");
throw;
}
}
return(currentVersions);
}
public async Task <long> CreateInstanceIndexAsync(DicomDataset instance, CancellationToken cancellationToken)
{
EnsureArg.IsNotNull(instance, nameof(instance));
await _sqlServerIndexSchema.EnsureInitialized();
using (SqlConnectionWrapper sqlConnectionWrapper = _sqlConnectionFactoryWrapper.ObtainSqlConnectionWrapper())
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.AddInstance.PopulateCommand(
sqlCommandWrapper,
instance.GetString(DicomTag.StudyInstanceUID),
instance.GetString(DicomTag.SeriesInstanceUID),
instance.GetString(DicomTag.SOPInstanceUID),
instance.GetSingleValueOrDefault <string>(DicomTag.PatientID),
instance.GetSingleValueOrDefault <string>(DicomTag.PatientName),
instance.GetSingleValueOrDefault <string>(DicomTag.ReferringPhysicianName),
instance.GetStringDateAsDateTime(DicomTag.StudyDate),
instance.GetSingleValueOrDefault <string>(DicomTag.StudyDescription),
instance.GetSingleValueOrDefault <string>(DicomTag.AccessionNumber),
instance.GetSingleValueOrDefault <string>(DicomTag.Modality),
instance.GetStringDateAsDateTime(DicomTag.PerformedProcedureStepStartDate),
(byte)IndexStatus.Creating);
try
{
return((long)(await sqlCommandWrapper.ExecuteScalarAsync(cancellationToken)));
}
catch (SqlException ex)
{
switch (ex.Number)
{
case SqlErrorCodes.Conflict:
{
if (ex.State == (byte)IndexStatus.Creating)
{
throw new PendingInstanceException();
}
throw new InstanceAlreadyExistsException();
}
}
throw new DataStoreException(ex);
}
}
}
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 virtual async Task <long> BeginCreateInstanceIndexAsync(int partitionKey, DicomDataset instance, IEnumerable <QueryTag> queryTags, CancellationToken cancellationToken)
{
EnsureArg.IsNotNull(instance, nameof(instance));
EnsureArg.IsNotNull(queryTags, nameof(queryTags));
using (SqlConnectionWrapper sqlConnectionWrapper = await SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
V1.AddInstance.PopulateCommand(
sqlCommandWrapper,
instance.GetString(DicomTag.StudyInstanceUID),
instance.GetString(DicomTag.SeriesInstanceUID),
instance.GetString(DicomTag.SOPInstanceUID),
instance.GetSingleValueOrDefault <string>(DicomTag.PatientID),
instance.GetSingleValueOrDefault <string>(DicomTag.PatientName),
instance.GetSingleValueOrDefault <string>(DicomTag.ReferringPhysicianName),
instance.GetStringDateAsDate(DicomTag.StudyDate),
instance.GetSingleValueOrDefault <string>(DicomTag.StudyDescription),
instance.GetSingleValueOrDefault <string>(DicomTag.AccessionNumber),
instance.GetSingleValueOrDefault <string>(DicomTag.Modality),
instance.GetStringDateAsDate(DicomTag.PerformedProcedureStepStartDate),
(byte)IndexStatus.Creating);
try
{
return((long)(await sqlCommandWrapper.ExecuteScalarAsync(cancellationToken)));
}
catch (SqlException ex)
{
if (ex.Number == SqlErrorCodes.Conflict)
{
if (ex.State == (byte)IndexStatus.Creating)
{
throw new PendingInstanceException();
}
throw new InstanceAlreadyExistsException();
}
throw new DataStoreException(ex);
}
}
}
public override async Task <IEnumerable <VersionedInstanceIdentifier> > RetrieveDeletedInstancesAsync(int batchSize, int maxRetries, CancellationToken cancellationToken = default)
{
var results = new List <VersionedInstanceIdentifier>();
using (SqlConnectionWrapper sqlConnectionWrapper = await SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken, true))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.RetrieveDeletedInstanceV6.PopulateCommand(
sqlCommandWrapper,
batchSize,
maxRetries);
using (var reader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
try
{
while (await reader.ReadAsync(cancellationToken))
{
(int partitionKey, string studyInstanceUid, string seriesInstanceUid, string sopInstanceUid, long watermark) = reader.ReadRow(
VLatest.DeletedInstance.PartitionKey,
VLatest.DeletedInstance.StudyInstanceUid,
VLatest.DeletedInstance.SeriesInstanceUid,
VLatest.DeletedInstance.SopInstanceUid,
VLatest.DeletedInstance.Watermark);
results.Add(new VersionedInstanceIdentifier(
studyInstanceUid,
seriesInstanceUid,
sopInstanceUid,
watermark,
partitionKey));
}
}
catch (SqlException ex)
{
throw new DataStoreException(ex);
}
}
}
return(results);
}
private async Task DeleteInstanceAsync(string studyInstanceUid, string seriesInstanceUid, string sopInstanceUid, DateTimeOffset cleanupAfter, CancellationToken cancellationToken)
{
await _sqlServerIndexSchema.EnsureInitialized();
using (SqlConnectionWrapper sqlConnectionWrapper = _sqlConnectionFactoryWrapper.ObtainSqlConnectionWrapper())
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.DeleteInstance.PopulateCommand(
sqlCommandWrapper,
cleanupAfter,
(byte)IndexStatus.Created,
studyInstanceUid,
seriesInstanceUid,
sopInstanceUid);
try
{
await sqlCommandWrapper.ExecuteScalarAsync(cancellationToken);
}
catch (SqlException ex)
{
switch (ex.Number)
{
case SqlErrorCodes.NotFound:
if (!string.IsNullOrEmpty(sopInstanceUid))
{
throw new InstanceNotFoundException();
}
if (!string.IsNullOrEmpty(seriesInstanceUid))
{
throw new SeriesNotFoundException();
}
throw new StudyNotFoundException();
default:
throw new DataStoreException(ex);
}
}
}
}