/// <summary>
/// Migrate the database from a specified version to the next version
/// </summary>
/// <param name="fromVersion">Database version to migrate from</param>
/// <param name="toVersion">Database version to migrate to</param>
/// <returns></returns>
private async Task Migrate(int fromVersion, int toVersion)
{
if (fromVersion < toVersion)
{
_logger.LogInformation($"Migrating database to version {fromVersion + 1}");
var sqlFile = Path.Combine(MigrationsFolder, fromVersion + 1 + ".sql");
// Read SQL file and remove any new lines
var migrateSql = File.ReadAllText(sqlFile)?.Replace("\r", "").Replace("\n", "");
// If the migration file contains multiple queries, split them up
var sqlSplit = migrateSql.Split(';');
// Loop through the migration queries
foreach (var sql in sqlSplit)
{
// If the SQL query is null, skip...
if (string.IsNullOrEmpty(sql))
{
continue;
}
// Execute the SQL query
var result = _metadataRepository.ExecuteSql(sql);
if (!result)
{
// Failed to execute query
_logger.LogWarning($"Failed to execute migration: {sql}");
continue;
}
_logger.LogDebug($"Migration execution result: {result}");
}
// Take a break
Thread.Sleep(2000);
// Build query to update metadata table version key
var newVersion = fromVersion + 1;
try
{
await _metadataRepository.AddOrUpdateAsync(new Metadata { Key = "DB_VERSION", Value = newVersion.ToString() }).ConfigureAwait(false);
_logger.LogInformation("Migration successful");
}
catch (Exception ex)
{
// Failed migration
_logger.LogError($"Failed migration err: {ex.Message}");
}
await Migrate(newVersion, toVersion).ConfigureAwait(false);
}
if (fromVersion == toVersion)
{
_logger.LogInformation("Migration done");
Finished = true;
}
await Task.CompletedTask.ConfigureAwait(false);
}