private async Task GetCurrentSchemaVersionAsync(CancellationToken cancellationToken)
{
using (var connection = await _sqlConnectionFactory.GetSqlConnectionAsync(cancellationToken: cancellationToken))
{
await connection.OpenAsync(cancellationToken);
string tableName = "dbo.SchemaVersion";
// Since now the status is made consistent as 'completed', we might have to check for 'complete' as well for the previous version's status
using (var selectCommand = connection.CreateCommand())
{
selectCommand.CommandText = string.Format(
"SELECT MAX(Version) FROM {0} " +
"WHERE Status = 'complete' OR Status = 'completed'", tableName);
try
{
_schemaInformation.Current = await selectCommand.ExecuteScalarAsync(cancellationToken) as int?;
}
catch (SqlException e) when(e.Message is "Invalid object name 'dbo.SchemaVersion'.")
{
_logger.LogInformation($"The table {tableName} does not exists. It must be new database");
}
}
}
}
public async Task ExecuteScriptAsync(string script, CancellationToken cancellationToken)
{
using (var connection = await _sqlConnectionFactory.GetSqlConnectionAsync(cancellationToken: cancellationToken))
{
await connection.OpenAsync(cancellationToken);
var server = new Server(new ServerConnection(connection));
server.ConnectionContext.ExecuteNonQuery(script);
}
}
public async Task CreateAndInitializeDatabase(string databaseName, int maximumSupportedSchemaVersion, bool forceIncrementalSchemaUpgrade, SchemaInitializer schemaInitializer = null, CancellationToken cancellationToken = default)
{
var testConnectionString = new SqlConnectionStringBuilder(_initialConnectionString)
{
InitialCatalog = databaseName
}.ToString();
schemaInitializer = schemaInitializer ?? CreateSchemaInitializer(testConnectionString);
// Create the database.
using (var connection = await _sqlConnectionFactory.GetSqlConnectionAsync(_masterDatabaseName, cancellationToken))
{
await connection.OpenAsync(cancellationToken);
using (SqlCommand command = connection.CreateCommand())
{
command.CommandTimeout = 600;
command.CommandText = @$ "
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = '{databaseName}')
BEGIN
CREATE DATABASE {databaseName};
END";
await command.ExecuteNonQueryAsync(cancellationToken);
}
}
internal async Task InitializeAsync(CancellationToken cancellationToken)
{
if (_enlistInTransactionIfPresent && _sqlTransactionHandler.SqlTransactionScope?.SqlConnection != null)
{
_sqlConnection = _sqlTransactionHandler.SqlTransactionScope.SqlConnection;
}
else
{
_sqlConnection = await _sqlConnectionFactory.GetSqlConnectionAsync(cancellationToken : cancellationToken);
}
if (_enlistInTransactionIfPresent && _sqlTransactionHandler.SqlTransactionScope != null && _sqlTransactionHandler.SqlTransactionScope.SqlConnection == null)
{
_sqlTransactionHandler.SqlTransactionScope.SqlConnection = SqlConnection;
}
if (SqlConnection.State != ConnectionState.Open)
{
await SqlConnection.OpenAsync(cancellationToken);
}
if (_enlistInTransactionIfPresent && _sqlTransactionHandler.SqlTransactionScope != null)
{
_sqlTransaction = _sqlTransactionHandler.SqlTransactionScope.SqlTransaction ?? SqlConnection.BeginTransaction();
if (_sqlTransactionHandler.SqlTransactionScope.SqlTransaction == null)
{
_sqlTransactionHandler.SqlTransactionScope.SqlTransaction = SqlTransaction;
}
}
}
public async Task CreateAndInitializeDatabase(string databaseName, bool forceIncrementalSchemaUpgrade, SchemaInitializer schemaInitializer = null, CancellationToken cancellationToken = default)
{
var testConnectionString = new SqlConnectionStringBuilder(_initialConnectionString)
{
InitialCatalog = databaseName
}.ToString();
schemaInitializer = schemaInitializer ?? CreateSchemaInitializer(testConnectionString);
// Create the database.
using (var connection = await _sqlConnectionFactory.GetSqlConnectionAsync(_masterDatabaseName, cancellationToken))
{
await connection.OpenAsync(cancellationToken);
using (SqlCommand command = connection.CreateCommand())
{
command.CommandTimeout = 600;
command.CommandText = $"CREATE DATABASE {databaseName}";
await command.ExecuteNonQueryAsync(cancellationToken);
}
}
// 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 = await _sqlConnectionFactory.GetSqlConnectionAsync(databaseName, cancellationToken))
{
await connection.OpenAsync(cancellationToken);
using (SqlCommand sqlCommand = connection.CreateCommand())
{
sqlCommand.CommandText = "SELECT 1";
await sqlCommand.ExecuteScalarAsync(cancellationToken);
}
}
});
await schemaInitializer.InitializeAsync(forceIncrementalSchemaUpgrade, cancellationToken);
await _searchParameterDefinitionManager.StartAsync(CancellationToken.None);
_sqlServerFhirModel.Initialize(SchemaVersionConstants.Max, true);
}
/// <inheritdoc />
public async Task ExecuteScriptAndCompleteSchemaVersionTransactionAsync(string script, int version, CancellationToken cancellationToken)
{
EnsureArg.IsNotNull(script, nameof(script));
EnsureArg.IsGte(version, 1);
using SqlConnection connection = await _sqlConnectionFactory.GetSqlConnectionAsync(cancellationToken : cancellationToken).ConfigureAwait(false);
await connection.TryOpenAsync(cancellationToken).ConfigureAwait(false);
var serverConnection = new ServerConnection(connection);
try
{
var server = new Server(serverConnection);
serverConnection.BeginTransaction();
server.ConnectionContext.ExecuteNonQuery(script);
await UpsertSchemaVersionAsync(connection, version, SchemaVersionStatus.Completed.ToString(), cancellationToken).ConfigureAwait(false);
serverConnection.CommitTransaction();
}
catch (Exception e) when(e is SqlException || e is ExecutionFailureException)
{
serverConnection.RollBackTransaction();
await UpsertSchemaVersionAsync(connection, version, SchemaVersionStatus.Failed.ToString(), cancellationToken).ConfigureAwait(false);
throw;
}
}
private async Task UpsertSchemaVersionAsync(int schemaVersion, string status, CancellationToken cancellationToken)
{
using (var connection = await _sqlConnectionFactory.GetSqlConnectionAsync(cancellationToken: cancellationToken))
using (var upsertCommand = new SqlCommand("dbo.UpsertSchemaVersion", connection))
{
upsertCommand.CommandType = CommandType.StoredProcedure;
upsertCommand.Parameters.AddWithValue("@version", schemaVersion);
upsertCommand.Parameters.AddWithValue("@status", status);
await connection.TryOpenAsync(cancellationToken);
await upsertCommand.ExecuteNonQueryAsync(cancellationToken);
}
}
private async Task InitializeAsync(CancellationToken cancellationToken)
{
var configuredConnectionBuilder = new SqlConnectionStringBuilder(_sqlServerDataStoreConfiguration.ConnectionString);
string databaseName = configuredConnectionBuilder.InitialCatalog;
SchemaInitializer.ValidateDatabaseName(databaseName);
SqlConnectionStringBuilder connectionBuilder = new SqlConnectionStringBuilder(_sqlServerDataStoreConfiguration.ConnectionString)
{
InitialCatalog = string.Empty
};
using (var connection = new SqlConnection(connectionBuilder.ToString()))
{
bool doesDatabaseExist = await SchemaInitializer.DoesDatabaseExistAsync(connection, databaseName, cancellationToken);
// database creation is allowed
if (!doesDatabaseExist)
{
Console.WriteLine("The database does not exists.");
bool created = await SchemaInitializer.CreateDatabaseAsync(connection, databaseName, cancellationToken);
if (created)
{
Console.WriteLine("The database is created.");
}
else
{
throw new SchemaManagerException(Resources.InsufficientDatabasePermissionsMessage);
}
connection.Close();
}
}
bool canInitialize = false;
// now switch to the target database
using (var connection = await _sqlConnectionFactory.GetSqlConnectionAsync(cancellationToken: cancellationToken))
{
canInitialize = await SchemaInitializer.CheckDatabasePermissionsAsync(connection, cancellationToken);
}
if (!canInitialize)
{
throw new SchemaManagerException(Resources.InsufficientTablesPermissionsMessage);
}
}
private async Task InitializeAsync(CancellationToken cancellationToken)
{
string sqlConnectionString = await _sqlConnectionStringProvider.GetSqlConnectionString(cancellationToken);
var configuredConnectionBuilder = new SqlConnectionStringBuilder(sqlConnectionString);
string databaseName = configuredConnectionBuilder.InitialCatalog;
SchemaInitializer.ValidateDatabaseName(databaseName);
await CreateDatabaseIfNotExists(databaseName, cancellationToken);
bool canInitialize = false;
// now switch to the target database
using (var connection = await _sqlConnectionFactory.GetSqlConnectionAsync(cancellationToken: cancellationToken))
{
canInitialize = await SchemaInitializer.CheckDatabasePermissionsAsync(connection, cancellationToken);
}
if (!canInitialize)
{
throw new SchemaManagerException(Resources.InsufficientTablesPermissionsMessage);
}
}
public async Task <HealthCheckResult> CheckHealthAsync(HealthCheckContext context, CancellationToken cancellationToken)
{
try
{
using (var connection = await _sqlConnectionFactory.GetSqlConnectionAsync(cancellationToken: cancellationToken))
using (SqlCommand command = connection.CreateCommand())
{
await connection.OpenAsync(cancellationToken);
command.CommandText = "select @@DBTS";
await command.ExecuteScalarAsync(cancellationToken);
return(HealthCheckResult.Healthy("Successfully connected to the data store."));
}
}
catch (Exception ex)
{
_logger.LogWarning(ex, "Failed to connect to the data store.");
return(HealthCheckResult.Unhealthy("Failed to connect to the data store."));
}
}
/// <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 (SqlConnection sqlConnection = await _sqlConnectionFactory.GetSqlConnectionAsync(cancellationToken: cancellationToken))
{
await sqlConnection.OpenAsync(cancellationToken);
if (ResourceTypeIdToTypeNameMap.IsEmpty)
{
lock (ResourceTypeIdToTypeNameMap)
{
if (ResourceTypeIdToTypeNameMap.IsEmpty)
{
UpdateResourceTypeMapAsync(sqlConnection);
}
}
}
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
PopulateFetchResourceChangesCommand(sqlCommand, startId, lastProcessedDateTime, pageSize);
using (SqlDataReader sqlDataReader = await sqlCommand.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;
}
}
