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;
}
}
}
}
private async Task Initialize()
{
if (!_schemaInformation.Current.HasValue)
{
_logger.LogError($"The current version of the database is not available. Unable in initialize {nameof(SqlServerFhirModel)}.");
throw new ServiceUnavailableException();
}
var connectionStringBuilder = new SqlConnectionStringBuilder(_configuration.ConnectionString);
_logger.LogInformation("Initializing {Server} {Database}", connectionStringBuilder.DataSource, connectionStringBuilder.InitialCatalog);
using (var connection = new SqlConnection(_configuration.ConnectionString))
{
await connection.OpenAsync();
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 = await sqlCommand.ExecuteReaderAsync(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(V1.ResourceType.ResourceTypeId, V1.ResourceType.Name);
resourceTypeToId.Add(resourceTypeName, id);
resourceTypeIdToTypeName.Add(id, resourceTypeName);
}
// result set 2
reader.NextResult();
while (reader.Read())
{
(string uri, short searchParamId) = reader.ReadRow(V1.SearchParam.Uri, V1.SearchParam.SearchParamId);
searchParamUriToId.Add(new Uri(uri), searchParamId);
}
// result set 3
reader.NextResult();
while (reader.Read())
{
(byte id, string claimTypeName) = reader.ReadRow(V1.ClaimType.ClaimTypeId, V1.ClaimType.Name);
claimNameToId.Add(claimTypeName, id);
}
// result set 4
reader.NextResult();
while (reader.Read())
{
(byte id, string compartmentName) = reader.ReadRow(V1.CompartmentType.CompartmentTypeId, V1.CompartmentType.Name);
compartmentTypeToId.Add(compartmentName, id);
}
// result set 5
reader.NextResult();
while (reader.Read())
{
var(value, systemId) = reader.ReadRow(V1.System.Value, V1.System.SystemId);
systemToId.TryAdd(value, systemId);
}
// result set 6
reader.NextResult();
while (reader.Read())
{
(string value, int quantityCodeId) = reader.ReadRow(V1.QuantityCode.Value, V1.QuantityCode.QuantityCodeId);
quantityCodeToId.TryAdd(value, quantityCodeId);
}
_resourceTypeToId = resourceTypeToId;
_resourceTypeIdToTypeName = resourceTypeIdToTypeName;
_searchParamUriToId = searchParamUriToId;
_systemToId = systemToId;
_quantityCodeToId = quantityCodeToId;
_claimNameToId = claimNameToId;
_compartmentTypeToId = compartmentTypeToId;
}
}
}
}