/// <summary>
/// Instantiates a new <see cref="DatabaseMigrator"/> class
/// </summary>
public DatabaseMigrator()
{
_metadataRepository = new MetadataRepository(DbContextFactory.CreateDeviceControllerContext(Startup.DbConfig.ToString()));
// Create the metadata table
_metadataRepository.ExecuteSql(Strings.SQL_CREATE_TABLE_METADATA);
// Get current version from metadata table
var dbVersion = GetMetadata("DB_VERSION").ConfigureAwait(false)
.GetAwaiter()
.GetResult();
var currentVersion = int.Parse(dbVersion?.Value ?? "0");
// Get newest version from migration files
var newestVersion = GetNewestDbVersion();
_logger.LogInformation($"Current: {currentVersion}, Latest: {newestVersion}");
// Attempt to migrate the database
if (currentVersion < newestVersion)
{
// Wait 30 seconds and let user know we are about to migrate the database and for them to make
// a backup until we handle backups and rollbacks.
_logger.LogInformation("MIGRATION IS ABOUT TO START IN 30 SECONDS, PLEASE MAKE SURE YOU HAVE A BACKUP!!!");
Thread.Sleep(30 * 1000);
}
Migrate(currentVersion, newestVersion).GetAwaiter().GetResult();
}
/// <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);
}
