public async Task InitializeAsync()
{
// Create the database
using (var connection = new SqlConnection(_masterConnectionString))
{
await connection.OpenAsync();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandTimeout = 600;
command.CommandText = $"CREATE DATABASE {_databaseName}";
await command.ExecuteNonQueryAsync();
}
}
// verify that we can connect to the new database. This sometimes does not work right away with Azure SQL.
await Policy
.Handle <SqlException>()
.WaitAndRetryAsync(
retryCount: 7,
sleepDurationProvider: retryAttempt => TimeSpan.FromSeconds(Math.Pow(2, retryAttempt)))
.ExecuteAsync(async() =>
{
using (var connection = new SqlConnection(TestConnectionString))
{
await connection.OpenAsync();
using (SqlCommand sqlCommand = connection.CreateCommand())
{
sqlCommand.CommandText = "SELECT 1";
await sqlCommand.ExecuteScalarAsync();
}
}
});
_schemaInitializer.Start();
}
public void Start()
{
_schemaInitializer.Start();
if (_searchParameterDefinitionManager is IStartable startable)
{
startable.Start();
}
var connectionStringBuilder = new SqlConnectionStringBuilder(_configuration.ConnectionString);
_logger.LogInformation("Initializing {Server} {Database}", connectionStringBuilder.DataSource, connectionStringBuilder.InitialCatalog);
using (var connection = new SqlConnection(_configuration.ConnectionString))
{
connection.Open();
// Synchronous calls are used because this code is executed on startup and doesn't need to be async.
// Additionally, XUnit task scheduler constraints prevent async calls from being easily tested.
using (SqlCommand sqlCommand = connection.CreateCommand())
{
sqlCommand.CommandText = @"
SET XACT_ABORT ON
BEGIN TRANSACTION
INSERT INTO dbo.ResourceType (Name)
SELECT value FROM string_split(@resourceTypes, ',')
EXCEPT SELECT Name FROM dbo.ResourceType WITH (TABLOCKX);
-- result set 1
SELECT ResourceTypeId, Name FROM dbo.ResourceType;
INSERT INTO dbo.SearchParam (Uri)
SELECT * FROM OPENJSON (@searchParams)
WITH (Uri varchar(128) '$.Uri')
EXCEPT SELECT Uri FROM dbo.SearchParam;
-- result set 2
SELECT Uri, SearchParamId FROM dbo.SearchParam;
INSERT INTO dbo.ClaimType (Name)
SELECT value FROM string_split(@claimTypes, ',')
EXCEPT SELECT Name FROM dbo.ClaimType;
-- result set 3
SELECT ClaimTypeId, Name FROM dbo.ClaimType;
INSERT INTO dbo.CompartmentType (Name)
SELECT value FROM string_split(@compartmentTypes, ',')
EXCEPT SELECT Name FROM dbo.CompartmentType;
-- result set 4
SELECT CompartmentTypeId, Name FROM dbo.CompartmentType;
COMMIT TRANSACTION
-- result set 5
SELECT Value, SystemId from dbo.System;
-- result set 6
SELECT Value, QuantityCodeId FROM dbo.QuantityCode";
string commaSeparatedResourceTypes = string.Join(",", ModelInfoProvider.GetResourceTypeNames());
string searchParametersJson = JsonConvert.SerializeObject(_searchParameterDefinitionManager.AllSearchParameters.Select(p => new { Name = p.Name, Uri = p.Url }));
string commaSeparatedClaimTypes = string.Join(',', _securityConfiguration.PrincipalClaims);
string commaSeparatedCompartmentTypes = string.Join(',', ModelInfoProvider.GetCompartmentTypeNames());
sqlCommand.Parameters.AddWithValue("@resourceTypes", commaSeparatedResourceTypes);
sqlCommand.Parameters.AddWithValue("@searchParams", searchParametersJson);
sqlCommand.Parameters.AddWithValue("@claimTypes", commaSeparatedClaimTypes);
sqlCommand.Parameters.AddWithValue("@compartmentTypes", commaSeparatedCompartmentTypes);
using (SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.SequentialAccess))
{
var resourceTypeToId = new Dictionary <string, short>(StringComparer.Ordinal);
var resourceTypeIdToTypeName = new Dictionary <short, string>();
var searchParamUriToId = new Dictionary <Uri, short>();
var systemToId = new ConcurrentDictionary <string, int>(StringComparer.OrdinalIgnoreCase);
var quantityCodeToId = new ConcurrentDictionary <string, int>(StringComparer.OrdinalIgnoreCase);
var claimNameToId = new Dictionary <string, byte>(StringComparer.Ordinal);
var compartmentTypeToId = new Dictionary <string, byte>();
// result set 1
while (reader.Read())
{
(short id, string resourceTypeName) = reader.ReadRow(VLatest.ResourceType.ResourceTypeId, VLatest.ResourceType.Name);
resourceTypeToId.Add(resourceTypeName, id);
resourceTypeIdToTypeName.Add(id, resourceTypeName);
}
// result set 2
reader.NextResult();
while (reader.Read())
{
(string uri, short searchParamId) = reader.ReadRow(VLatest.SearchParam.Uri, VLatest.SearchParam.SearchParamId);
searchParamUriToId.Add(new Uri(uri), searchParamId);
}
// result set 3
reader.NextResult();
while (reader.Read())
{
(byte id, string claimTypeName) = reader.ReadRow(VLatest.ClaimType.ClaimTypeId, VLatest.ClaimType.Name);
claimNameToId.Add(claimTypeName, id);
}
// result set 4
reader.NextResult();
while (reader.Read())
{
(byte id, string compartmentName) = reader.ReadRow(VLatest.CompartmentType.CompartmentTypeId, VLatest.CompartmentType.Name);
compartmentTypeToId.Add(compartmentName, id);
}
// result set 5
reader.NextResult();
while (reader.Read())
{
var(value, systemId) = reader.ReadRow(VLatest.System.Value, VLatest.System.SystemId);
systemToId.TryAdd(value, systemId);
}
// result set 6
reader.NextResult();
while (reader.Read())
{
(string value, int quantityCodeId) = reader.ReadRow(VLatest.QuantityCode.Value, VLatest.QuantityCode.QuantityCodeId);
quantityCodeToId.TryAdd(value, quantityCodeId);
}
_resourceTypeToId = resourceTypeToId;
_resourceTypeIdToTypeName = resourceTypeIdToTypeName;
_searchParamUriToId = searchParamUriToId;
_systemToId = systemToId;
_quantityCodeToId = quantityCodeToId;
_claimNameToId = claimNameToId;
_compartmentTypeToId = compartmentTypeToId;
_started = true;
}
}
}
}
public SqlServerFhirStorageTestsFixture()
{
var initialConnectionString = Environment.GetEnvironmentVariable("SqlServer:ConnectionString") ?? LocalDatabase.DefaultConnectionString;
_databaseName = $"FHIRINTEGRATIONTEST_{DateTimeOffset.UtcNow.ToUnixTimeSeconds()}_{BigInteger.Abs(new BigInteger(Guid.NewGuid().ToByteArray()))}";
_masterConnectionString = new SqlConnectionStringBuilder(initialConnectionString)
{
InitialCatalog = "master"
}.ToString();
TestConnectionString = new SqlConnectionStringBuilder(initialConnectionString)
{
InitialCatalog = _databaseName
}.ToString();
using (var connection = new SqlConnection(_masterConnectionString))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandTimeout = 600;
command.CommandText = $"CREATE DATABASE {_databaseName}";
command.ExecuteNonQuery();
}
}
var config = new SqlServerDataStoreConfiguration {
ConnectionString = TestConnectionString, Initialize = true
};
var schemaUpgradeRunner = new SchemaUpgradeRunner(config);
var schemaInformation = new SchemaInformation();
var schemaInitializer = new SchemaInitializer(config, schemaUpgradeRunner, schemaInformation, NullLogger <SchemaInitializer> .Instance);
schemaInitializer.Start();
var searchParameterDefinitionManager = Substitute.For <ISearchParameterDefinitionManager>();
searchParameterDefinitionManager.AllSearchParameters.Returns(new[]
{
new SearchParameter {
Id = SearchParameterNames.Id, Url = SearchParameterNames.IdUri.ToString()
},
new SearchParameter {
Id = SearchParameterNames.LastUpdated, Url = SearchParameterNames.LastUpdatedUri.ToString()
},
});
var securityConfiguration = new SecurityConfiguration {
PrincipalClaims = { "oid" }
};
var sqlServerFhirModel = new SqlServerFhirModel(config, schemaInformation, searchParameterDefinitionManager, Options.Create(securityConfiguration), NullLogger <SqlServerFhirModel> .Instance);
var serviceCollection = new ServiceCollection();
serviceCollection.AddSqlServerTableRowParameterGenerators();
serviceCollection.AddSingleton(sqlServerFhirModel);
ServiceProvider serviceProvider = serviceCollection.BuildServiceProvider();
var upsertResourceTvpGenerator = serviceProvider.GetRequiredService <V1.UpsertResourceTvpGenerator <ResourceMetadata> >();
var searchParameterToSearchValueTypeMap = new SearchParameterToSearchValueTypeMap(searchParameterDefinitionManager);
_fhirDataStore = new SqlServerFhirDataStore(config, sqlServerFhirModel, searchParameterToSearchValueTypeMap, upsertResourceTvpGenerator, NullLogger <SqlServerFhirDataStore> .Instance);
_testHelper = new SqlServerFhirStorageTestHelper(TestConnectionString);
}