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);
        }