public override async Task <PartitionEntry> AddPartitionAsync(string partitionName, CancellationToken cancellationToken)
{
using (SqlConnectionWrapper sqlConnectionWrapper = await SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.AddPartition.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 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 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);
}
}
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 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 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 async Task <ChangeFeedEntry> GetChangeFeedLatestAsync(CancellationToken cancellationToken)
{
using (SqlConnectionWrapper sqlConnectionWrapper = await _sqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.GetChangeFeedLatest.PopulateCommand(sqlCommandWrapper);
using (var reader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
if (await reader.ReadAsync(cancellationToken))
{
(long rSeq, DateTimeOffset rTimestamp, int rAction, string rStudyInstanceUid, string rSeriesInstanceUid, string rSopInstanceUid, long oWatermark, long?cWatermark) = reader.ReadRow(
VLatest.ChangeFeed.Sequence,
VLatest.ChangeFeed.Timestamp,
VLatest.ChangeFeed.Action,
VLatest.ChangeFeed.StudyInstanceUid,
VLatest.ChangeFeed.SeriesInstanceUid,
VLatest.ChangeFeed.SopInstanceUid,
VLatest.ChangeFeed.OriginalWatermark,
VLatest.ChangeFeed.CurrentWatermark);
return(new ChangeFeedEntry(
rSeq,
rTimestamp,
(ChangeFeedAction)rAction,
rStudyInstanceUid,
rSeriesInstanceUid,
rSopInstanceUid,
oWatermark,
cWatermark,
ConvertWatermarkToCurrentState(oWatermark, cWatermark)));
}
}
}
return(null);
}
public async Task <CompatibleVersions> GetLatestCompatibleVersionsAsync(CancellationToken cancellationToken)
{
CompatibleVersions compatibleVersions;
using (SqlConnectionWrapper sqlConnectionWrapper = await _sqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken: cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
SchemaShared.SelectCompatibleSchemaVersions.PopulateCommand(sqlCommandWrapper);
using (var dataReader = await sqlCommandWrapper.ExecuteReaderAsync(cancellationToken))
{
if (dataReader.Read())
{
compatibleVersions = new CompatibleVersions(ConvertToInt(dataReader.GetValue(0)), ConvertToInt(dataReader.GetValue(1)));
}
else
{
throw new SqlRecordNotFoundException(Resources.CompatibilityRecordNotFound);
}
}
return(compatibleVersions);
}
int ConvertToInt(object o)
{
if (o == DBNull.Value)
{
throw new SqlRecordNotFoundException(Resources.CompatibilityRecordNotFound);
}
else
{
return(Convert.ToInt32(o));
}
}
}
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(int partitionKey, string studyInstanceUid, string seriesInstanceUid, string sopInstanceUid, DateTimeOffset cleanupAfter, CancellationToken cancellationToken)
{
using (SqlConnectionWrapper sqlConnectionWrapper = await SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.DeleteInstanceV6.PopulateCommand(
sqlCommandWrapper,
cleanupAfter,
(byte)IndexStatus.Created,
partitionKey,
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);
}
}
}
}
private async Task <IEnumerable <VersionedInstanceIdentifier> > GetInstanceIdentifierImp(
int partitionKey,
string studyInstanceUid,
CancellationToken cancellationToken,
string seriesInstanceUid = null,
string sopInstanceUid = null)
{
var results = new List <VersionedInstanceIdentifier>();
using (SqlConnectionWrapper sqlConnectionWrapper = await SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.GetInstanceV6.PopulateCommand(
sqlCommandWrapper,
validStatus: (byte)IndexStatus.Created,
partitionKey,
studyInstanceUid,
seriesInstanceUid,
sopInstanceUid);
using (var reader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
while (await reader.ReadAsync(cancellationToken))
{
(string rStudyInstanceUid, string rSeriesInstanceUid, string rSopInstanceUid, long watermark) = reader.ReadRow(
VLatest.Instance.StudyInstanceUid,
VLatest.Instance.SeriesInstanceUid,
VLatest.Instance.SopInstanceUid,
VLatest.Instance.Watermark);
results.Add(new VersionedInstanceIdentifier(
rStudyInstanceUid,
rSeriesInstanceUid,
rSopInstanceUid,
watermark));
}
}
}
return(results);
}
public override async Task <IReadOnlyCollection <ChangeFeedEntry> > GetChangeFeedAsync(long offset, int limit, CancellationToken cancellationToken)
{
var results = new List <ChangeFeedEntry>();
using SqlConnectionWrapper sqlConnectionWrapper = await SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken);
using SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand();
VLatest.GetChangeFeedV6.PopulateCommand(sqlCommandWrapper, limit, offset);
using SqlDataReader reader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken);
while (await reader.ReadAsync(cancellationToken))
{
(long rSeq, DateTimeOffset rTimestamp, int rAction, string rPartitionName, string rStudyInstanceUid, string rSeriesInstanceUid, string rSopInstanceUid, long oWatermark, long?cWatermark) = reader.ReadRow(
VLatest.ChangeFeed.Sequence,
VLatest.ChangeFeed.Timestamp,
VLatest.ChangeFeed.Action,
VLatest.Partition.PartitionName,
VLatest.ChangeFeed.StudyInstanceUid,
VLatest.ChangeFeed.SeriesInstanceUid,
VLatest.ChangeFeed.SopInstanceUid,
VLatest.ChangeFeed.OriginalWatermark,
VLatest.ChangeFeed.CurrentWatermark);
results.Add(new ChangeFeedEntry(
rSeq,
rTimestamp,
(ChangeFeedAction)rAction,
rStudyInstanceUid,
rSeriesInstanceUid,
rSopInstanceUid,
oWatermark,
cWatermark,
ConvertWatermarkToCurrentState(oWatermark, cWatermark),
rPartitionName));
}
return(results);
}
public async Task <long> CreateInstanceIndexAsync(DicomDataset instance, CancellationToken cancellationToken)
{
EnsureArg.IsNotNull(instance, nameof(instance));
await _sqlServerIndexSchema.EnsureInitialized();
using (SqlConnectionWrapper sqlConnectionWrapper = await _sqlConnectionFactoryWrapper.ObtainSqlConnectionWrapperAsync(cancellationToken))
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 override async Task EndCreateInstanceIndexAsync(
int partitionKey,
DicomDataset dicomDataset,
long watermark,
IEnumerable <QueryTag> queryTags,
bool allowExpiredTags = false,
CancellationToken cancellationToken = default)
{
EnsureArg.IsNotNull(dicomDataset, nameof(dicomDataset));
EnsureArg.IsNotNull(queryTags, nameof(queryTags));
using (SqlConnectionWrapper sqlConnectionWrapper = await SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.UpdateInstanceStatusV6.PopulateCommand(
sqlCommandWrapper,
partitionKey,
dicomDataset.GetSingleValueOrDefault(DicomTag.StudyInstanceUID, string.Empty),
dicomDataset.GetSingleValueOrDefault(DicomTag.SeriesInstanceUID, string.Empty),
dicomDataset.GetSingleValueOrDefault(DicomTag.SOPInstanceUID, string.Empty),
watermark,
(byte)IndexStatus.Created,
allowExpiredTags ? null : ExtendedQueryTagDataRowsBuilder.GetMaxTagKey(queryTags));
try
{
await sqlCommandWrapper.ExecuteScalarAsync(cancellationToken);
}
catch (SqlException ex)
{
throw ex.Number switch
{
SqlErrorCodes.NotFound => new InstanceNotFoundException(),
SqlErrorCodes.Conflict when ex.State == 10 => new ExtendedQueryTagsOutOfDateException(),
_ => new DataStoreException(ex),
};
}
}
}
public override async Task <QueryResult> QueryAsync(
int partitionKey,
QueryExpression query,
CancellationToken cancellationToken)
{
EnsureArg.IsNotNull(query, nameof(query));
var results = new List <VersionedInstanceIdentifier>(query.EvaluatedLimit);
using SqlConnectionWrapper sqlConnectionWrapper = await SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken);
using SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand();
var stringBuilder = new IndentedStringBuilder(new StringBuilder());
var sqlQueryGenerator = new SqlQueryGenerator(stringBuilder, query, new SqlQueryParameterManager(sqlCommandWrapper.Parameters), Version, partitionKey);
sqlCommandWrapper.CommandText = stringBuilder.ToString();
LogSqlCommand(sqlCommandWrapper);
using SqlDataReader reader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken);
while (await reader.ReadAsync(cancellationToken))
{
(string studyInstanceUid, string seriesInstanceUid, string sopInstanceUid, long watermark) = reader.ReadRow(
VLatest.Instance.StudyInstanceUid,
VLatest.Instance.SeriesInstanceUid,
VLatest.Instance.SopInstanceUid,
VLatest.Instance.Watermark);
results.Add(new VersionedInstanceIdentifier(
studyInstanceUid,
seriesInstanceUid,
sopInstanceUid,
watermark));
}
return(new QueryResult(results));
}
public override async Task DeleteDeletedInstanceAsync(VersionedInstanceIdentifier versionedInstanceIdentifier, CancellationToken cancellationToken = default)
{
using (SqlConnectionWrapper sqlConnectionWrapper = await SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken, true))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.DeleteDeletedInstanceV6.PopulateCommand(
sqlCommandWrapper,
versionedInstanceIdentifier.PartitionKey,
versionedInstanceIdentifier.StudyInstanceUid,
versionedInstanceIdentifier.SeriesInstanceUid,
versionedInstanceIdentifier.SopInstanceUid,
versionedInstanceIdentifier.Version);
try
{
await sqlCommandWrapper.ExecuteScalarAsync(cancellationToken);
}
catch (SqlException ex)
{
throw new DataStoreException(ex);
}
}
}
public override async Task <IReadOnlyList <VersionedInstanceIdentifier> > GetInstanceIdentifiersByWatermarkRangeAsync(
WatermarkRange watermarkRange,
IndexStatus indexStatus,
CancellationToken cancellationToken = default)
{
var results = new List <VersionedInstanceIdentifier>();
using (SqlConnectionWrapper sqlConnectionWrapper = await SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.GetInstancesByWatermarkRange.PopulateCommand(
sqlCommandWrapper,
watermarkRange.Start,
watermarkRange.End,
(byte)indexStatus);
using (var reader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
while (await reader.ReadAsync(cancellationToken))
{
(string rStudyInstanceUid, string rSeriesInstanceUid, string rSopInstanceUid, long watermark) = reader.ReadRow(
VLatest.Instance.StudyInstanceUid,
VLatest.Instance.SeriesInstanceUid,
VLatest.Instance.SopInstanceUid,
VLatest.Instance.Watermark);
results.Add(new VersionedInstanceIdentifier(
rStudyInstanceUid,
rSeriesInstanceUid,
rSopInstanceUid,
watermark));
}
}
}
return(results);
}
public override async Task <int> IncrementDeletedInstanceRetryAsync(VersionedInstanceIdentifier versionedInstanceIdentifier, DateTimeOffset cleanupAfter, CancellationToken cancellationToken = default)
{
using (SqlConnectionWrapper sqlConnectionWrapper = await SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken, true))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.IncrementDeletedInstanceRetryV6.PopulateCommand(
sqlCommandWrapper,
versionedInstanceIdentifier.PartitionKey,
versionedInstanceIdentifier.StudyInstanceUid,
versionedInstanceIdentifier.SeriesInstanceUid,
versionedInstanceIdentifier.SopInstanceUid,
versionedInstanceIdentifier.Version,
cleanupAfter);
try
{
return((int)(await sqlCommandWrapper.ExecuteScalarAsync(cancellationToken)));
}
catch (SqlException ex)
{
throw new DataStoreException(ex);
}
}
}
/// <summary>
/// Returns the number of resource change records from a start id and a checkpoint datetime.
/// </summary>
/// <param name="startId">The start id of resource change records to fetch. The start id is inclusive.</param>
/// <param name="lastProcessedDateTime">The last checkpoint datetime.</param>
/// <param name="pageSize">The page size for fetching resource change records.</param>
/// <param name="cancellationToken">Cancellation token.</param>
/// <returns>Resource change data rows.</returns>
/// <exception cref="System.ArgumentOutOfRangeException">Thrown if startId or pageSize is less than zero.</exception>
/// <exception cref="System.InvalidOperationException">Thrown when a method call is invalid for the object's current state.</exception>
/// <exception cref="System.OperationCanceledException">Thrown when the operation is canceled.</exception>
/// <exception cref="System.Threading.Tasks.TaskCanceledException">Thrown when the task is canceled.</exception>
/// <exception cref="Microsoft.Data.SqlClient.SqlException">Thrown when SQL Server returns a warning or error.</exception>
/// <exception cref="System.TimeoutException">Thrown when the time allotted for a process or operation has expired.</exception>
/// <exception cref="System.Exception">Thrown when errors occur during execution.</exception>
public async Task <IReadOnlyCollection <ResourceChangeData> > GetRecordsAsync(long startId, DateTime lastProcessedDateTime, short pageSize, CancellationToken cancellationToken)
{
EnsureArg.IsGte(startId, 1, nameof(startId));
EnsureArg.IsGte(pageSize, 1, nameof(pageSize));
var listResourceChangeData = new List <ResourceChangeData>();
try
{
// The GetRecordsAsync function would be called every second by one agent.
// So, it would be a good option that opens and closes a connection for each call,
// and there is no database connection pooling in the Application at this time.
using (SqlConnectionWrapper sqlConnectionWrapper = await _sqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken, true))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
if (ResourceTypeIdToTypeNameMap.IsEmpty)
{
lock (ResourceTypeIdToTypeNameMap)
{
if (ResourceTypeIdToTypeNameMap.IsEmpty)
{
UpdateResourceTypeMapAsync(sqlCommandWrapper);
}
}
}
PopulateFetchResourceChangesCommand(sqlCommandWrapper, startId, lastProcessedDateTime, pageSize);
using (SqlDataReader sqlDataReader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
while (await sqlDataReader.ReadAsync(cancellationToken))
{
(long id, DateTime timestamp, string resourceId, short resourceTypeId, int resourceVersion, byte resourceChangeTypeId) = sqlDataReader.ReadRow(
VLatest.ResourceChangeData.Id,
VLatest.ResourceChangeData.Timestamp,
VLatest.ResourceChangeData.ResourceId,
VLatest.ResourceChangeData.ResourceTypeId,
VLatest.ResourceChangeData.ResourceVersion,
VLatest.ResourceChangeData.ResourceChangeTypeId);
listResourceChangeData.Add(new ResourceChangeData(
id: id,
timestamp: DateTime.SpecifyKind(timestamp, DateTimeKind.Utc),
resourceId: resourceId,
resourceTypeId: resourceTypeId,
resourceVersion: resourceVersion,
resourceChangeTypeId: resourceChangeTypeId,
resourceTypeName: ResourceTypeIdToTypeNameMap[resourceTypeId]));
}
}
return(listResourceChangeData);
}
}
catch (Exception ex) when((ex is OperationCanceledException || ex is TaskCanceledException) && cancellationToken.IsCancellationRequested)
{
_logger.LogInformation(ex, Resources.GetRecordsAsyncOperationIsCanceled);
throw;
}
catch (SqlException ex)
{
switch (ex.Number)
{
case SqlErrorCodes.TimeoutExpired:
throw new TimeoutException(ex.Message, ex);
default:
_logger.LogError(ex, string.Format(Resources.SqlExceptionOccurredWhenFetchingResourceChanges, ex.Number));
throw;
}
}
catch (Exception ex)
{
_logger.LogError(ex, Resources.ExceptionOccurredWhenFetchingResourceChanges);
throw;
}
}
private async Task <SearchResult> SearchImpl(SearchOptions searchOptions, bool historySearch, CancellationToken cancellationToken)
{
Expression searchExpression = searchOptions.Expression;
// AND in the continuation token
if (!string.IsNullOrWhiteSpace(searchOptions.ContinuationToken) && !searchOptions.CountOnly)
{
var continuationToken = ContinuationToken.FromString(searchOptions.ContinuationToken);
if (continuationToken != null)
{
// in case it's a _lastUpdated sort optimization
if (string.IsNullOrEmpty(continuationToken.SortValue))
{
(SearchParameterInfo searchParamInfo, SortOrder sortOrder) = searchOptions.Sort.Count == 0 ? default : searchOptions.Sort[0];
Expression lastUpdatedExpression = sortOrder == SortOrder.Ascending
? Expression.GreaterThan(SqlFieldName.ResourceSurrogateId, null, continuationToken.ResourceSurrogateId)
: Expression.LessThan(SqlFieldName.ResourceSurrogateId, null, continuationToken.ResourceSurrogateId);
var tokenExpression = Expression.SearchParameter(SqlSearchParameters.ResourceSurrogateIdParameter, lastUpdatedExpression);
searchExpression = searchExpression == null ? tokenExpression : (Expression)Expression.And(tokenExpression, searchExpression);
}
}
else
{
throw new BadRequestException(Resources.InvalidContinuationToken);
}
}
if (searchOptions.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(_sortRewriter, searchOptions)
.AcceptVisitor(SearchParamTableExpressionReorderer.Instance)
.AcceptVisitor(MissingSearchParamVisitor.Instance)
.AcceptVisitor(NotExpressionRewriter.Instance)
.AcceptVisitor(_chainFlatteningRewriter)
.AcceptVisitor(ResourceColumnPredicatePushdownRewriter.Instance)
.AcceptVisitor(DateTimeBoundedRangeRewriter.Instance)
.AcceptVisitor(StringOverflowRewriter.Instance)
.AcceptVisitor(NumericRangeRewriter.Instance)
.AcceptVisitor(IncludeMatchSeedRewriter.Instance)
.AcceptVisitor(TopRewriter.Instance, searchOptions)
.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 SqlQueryParameterManager(sqlCommandWrapper.Parameters), _model, historySearch, _schemaInformation);
expression.AcceptVisitor(queryGenerator, searchOptions);
sqlCommandWrapper.CommandText = stringBuilder.ToString();
LogSqlCommand(sqlCommandWrapper);
using (var reader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
if (searchOptions.CountOnly)
{
await reader.ReadAsync(cancellationToken);
var searchResult = new SearchResult(reader.GetInt32(0), searchOptions.UnsupportedSearchParams);
// call NextResultAsync to get the info messages
await reader.NextResultAsync(cancellationToken);
return(searchResult);
}
var resources = new List <SearchResultEntry>(searchOptions.MaxItemCount);
long?newContinuationId = null;
bool moreResults = false;
int matchCount = 0;
// Currently we support only date time sort type.
DateTime?sortValue = null;
var isResultPartial = false;
while (await reader.ReadAsync(cancellationToken))
{
(short resourceTypeId, string resourceId, int version, bool isDeleted, long resourceSurrogateId, string requestMethod, bool isMatch, bool isPartialEntry, bool isRawResourceMetaSet, Stream rawResourceStream) = reader.ReadRow(
VLatest.Resource.ResourceTypeId,
VLatest.Resource.ResourceId,
VLatest.Resource.Version,
VLatest.Resource.IsDeleted,
VLatest.Resource.ResourceSurrogateId,
VLatest.Resource.RequestMethod,
_isMatch,
_isPartial,
VLatest.Resource.IsRawResourceMetaSet,
VLatest.Resource.RawResource);
// 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 >= searchOptions.MaxItemCount && isMatch)
{
moreResults = true;
// At this point we are at the last row.
// if we have more columns, it means sort expressions were added.
if (reader.FieldCount > 10)
{
sortValue = reader.GetValue(SortValueColumnName) as DateTime?;
}
continue;
}
// See if this resource is a continuation token candidate and increase the count
if (isMatch)
{
newContinuationId = resourceSurrogateId;
matchCount++;
}
string rawResource;
using (rawResourceStream)
{
rawResource = await CompressedRawResourceConverter.ReadCompressedRawResource(rawResourceStream);
}
// 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),
isMatch ? SearchEntryMode.Match : SearchEntryMode.Include));
}
// call NextResultAsync to get the info messages
await reader.NextResultAsync(cancellationToken);
// Continuation token prep
ContinuationToken continuationToken = null;
if (moreResults)
{
if (sortValue.HasValue)
{
continuationToken = new ContinuationToken(new object[]
{
sortValue.Value.ToString("o"),
newContinuationId ?? 0,
});
}
else
{
continuationToken = new ContinuationToken(new object[]
{
newContinuationId ?? 0,
});
}
}
if (isResultPartial)
{
_requestContextAccessor.FhirRequestContext.BundleIssues.Add(
new OperationOutcomeIssue(
OperationOutcomeConstants.IssueSeverity.Warning,
OperationOutcomeConstants.IssueType.Incomplete,
Core.Resources.TruncatedIncludeMessage));
}
return(new SearchResult(resources, continuationToken?.ToJson(), searchOptions.Sort, searchOptions.UnsupportedSearchParams));
}
}
}
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())
