///<inheritdoc/>
public override void RunSqlScripts(
IDbConnection connection,
IDbTransaction transaction,
NonTransactionalContext nonTransactionalContext,
string version,
string workingPath,
string scriptDirectory,
string metaSchemaName,
string metaTableName,
List <KeyValuePair <string, string> > tokenKeyPairs = null,
int?commandTimeout = null,
string environmentCode = null,
string appliedByTool = null,
string appliedByToolVersion = null
)
{
//extract and filter out scripts when environment code is used
var sqlScriptFiles = _directoryService.GetFiles(scriptDirectory, "*.sql").ToList();
sqlScriptFiles = _directoryService.FilterFiles(workingPath, environmentCode, sqlScriptFiles).ToList();
_traceService.Info($"Found {sqlScriptFiles.Count} script files on {workingPath}" + (sqlScriptFiles.Count > 0 ? Environment.NewLine : string.Empty) +
$"{string.Join(Environment.NewLine, sqlScriptFiles.Select(s => " + " + new FileInfo(s).Name))}");
//execute all script files in the version folder
sqlScriptFiles.Sort();
sqlScriptFiles
.ForEach(scriptFile =>
{
var sqlStatementRaw = _fileService.ReadAllText(scriptFile);
var sqlStatements = _dataService.BreakStatements(sqlStatementRaw)
.Where(s => !string.IsNullOrWhiteSpace(s))
.ToList();
;
sqlStatements.ForEach(sqlStatement =>
{
try
{
sqlStatement = _tokenReplacementService.Replace(tokenKeyPairs, sqlStatement);
_traceService.Debug($"Executing sql statement as part of : {scriptFile}");
_configurationDataService.ExecuteSql(
connection: connection,
commandText: sqlStatement,
transaction: transaction,
commandTimeout: commandTimeout,
traceService: _traceService);
}
catch (Exception)
{
_traceService.Error($"Failed to execute sql statements in script file {scriptFile}.{Environment.NewLine}" +
$"The failing statement starts here --------------------------{Environment.NewLine}" +
$"{sqlStatement} {Environment.NewLine}" +
$"The failing statement ends here --------------------------");
throw;
}
});
_traceService.Info($"Executed script file {scriptFile}.");
});
}
public abstract void RunSqlScripts(
IDbConnection connection,
IDbTransaction transaction,
NonTransactionalContext nonTransactionalContext,
string version,
string workingPath,
string scriptDirectory,
string metaSchemaName,
string metaTableName,
List <KeyValuePair <string, string> > tokenKeyPairs = null,
int?commandTimeout = null,
string environmentCode = null,
string appliedByTool = null,
string appliedByToolVersion = null
);
public abstract void RunVersionScripts(
IDbConnection connection,
IDbTransaction transaction,
List <string> dbVersions,
string workingPath,
string targetVersion,
NonTransactionalContext nonTransactionalContext,
List <KeyValuePair <string, string> > tokenKeyPairs = null,
string bulkSeparator = null,
string metaschemaName = null,
string metaTableName = null,
int?commandTimeout = null,
int?bulkBatchSize = null,
string appliedByTool = null,
string appliedByToolVersion = null,
string environmentCode = null
);
private void RunSqlScripts(
IDbConnection connection,
IDbTransaction transaction,
NonTransactionalContext nonTransactionalContext,
string version,
string workingPath,
string scriptDirectory,
string schemaName,
string tableName,
List <KeyValuePair <string, string> > tokenKeyPairs = null,
int?commandTimeout = null,
string environmentCode = null,
string appliedByTool = null,
string appliedByToolVersion = null
)
{
string currentScriptFile = null;
try
{
//TODO: Filter out scripts when environment code is used
var sqlScriptFiles = _directoryService.GetFiles(scriptDirectory, "*.sql").ToList();
sqlScriptFiles = _directoryService.FilterFiles(workingPath, environmentCode, sqlScriptFiles).ToList();
_traceService.Info($"Found the {sqlScriptFiles.Count} script files on {scriptDirectory}");
_traceService.Info($"{string.Join(@"\r\n\t", sqlScriptFiles.Select(s => new FileInfo(s).Name))}");
//execute all script files in the version folder
sqlScriptFiles.Sort();
sqlScriptFiles
.ForEach(scriptFile =>
{
currentScriptFile = scriptFile;
//In case the non-transactional failure is resolved, skip scripts
if (nonTransactionalContext?.ResolvingOption == NonTransactionalResolvingOption.ContinueAfterFailure &&
!nonTransactionalContext.IsFailedScriptPathMatched)
{
//Set failed script file as matched
if (string.Equals(scriptFile, nonTransactionalContext.FailedScriptPath, StringComparison.InvariantCultureIgnoreCase))
{
nonTransactionalContext.SetFailedScriptPathMatch();
}
_traceService.Info($"Skipping script file {scriptFile} ...");
}
else //otherwise execute them
{
var sqlStatementRaw = _fileService.ReadAllText(scriptFile);
var sqlStatements = _dataService.BreakStatements(sqlStatementRaw)
.Where(s => !string.IsNullOrWhiteSpace(s))
.ToList();
;
sqlStatements.ForEach(sqlStatement =>
{
sqlStatement = _tokenReplacementService.Replace(tokenKeyPairs, sqlStatement);
_traceService.Debug($"Executing sql statement as part of : {scriptFile}{Environment.NewLine}{sqlStatement}");
_configurationDataService.ExecuteSql(
connection: connection,
commandText: sqlStatement,
transaction: transaction,
commandTimeout: commandTimeout,
traceService: _traceService);
});
_traceService.Info($"Executed script file {scriptFile}.");
}
});
}
catch (Exception exc)
{
//Try parse the known sql error
string sqlError;
if (!_dataService.TryParseErrorFromException(exc, out sqlError))
{
//if not sucesfull, use the whole exception
sqlError = exc.ToString();
}
//in case scripts are not executed within transaction, mark version as failed in database
if (transaction == null)
{
//update db version to failed
_configurationDataService.InsertVersion(connection, transaction, version,
schemaName,
tableName,
commandTimeout: commandTimeout,
appliedByTool: appliedByTool,
appliedByToolVersion: appliedByToolVersion,
currentScriptFile,
sqlError);
_traceService.Error(@$ "Migration of " "{version}" " version was not running in transaction and has failed when executing of script file " "{currentScriptFile}" " with following error:
{sqlError}
{ManualResolvingAfterFailureMessage}");
}
else
{
_traceService.Error(@$ "Migration of " "{version}" " version was running in transaction and has failed when executing of script file " "{currentScriptFile}" " with following error:
{sqlError}
");
}
throw exc;
}
}
private void RunVersionScripts(
IDbConnection connection,
IDbTransaction transaction,
List <string> dbVersions,
string workingPath,
string targetVersion,
NonTransactionalContext nonTransactionalContext,
List <KeyValuePair <string, string> > tokenKeyPairs = null,
string delimiter = null,
string schemaName = null,
string tableName = null,
int?commandTimeout = null,
int?batchSize = null,
string appliedByTool = null,
string appliedByToolVersion = null,
string environmentCode = null
)
{
//excludes all versions already executed
var versionDirectories = _directoryService.GetDirectories(workingPath, "v*.*")
.Where(v => !dbVersions.Contains(new DirectoryInfo(v).Name))
.ToList();
//exclude all versions greater than the target version
if (!string.IsNullOrEmpty(targetVersion))
{
versionDirectories.RemoveAll(v =>
{
var cv = new LocalVersion(new DirectoryInfo(v).Name);
var tv = new LocalVersion(targetVersion);
return(string.Compare(cv.SemVersion, tv.SemVersion) == 1);
});
}
//execute all sql scripts in the version folders
if (versionDirectories.Any())
{
versionDirectories.Sort();
versionDirectories.ForEach(versionDirectory =>
{
try
{
//run scripts in all sub-directories
List <string> scriptSubDirectories = null;
//check for transaction directory
bool isExplicitTransactionDefined = false;
string transactionDirectory = Path.Combine(versionDirectory, "_transaction");
if (_directoryService.Exists(transactionDirectory))
{
if (_dataService.IsAtomicDDLSupported)
{
throw new YuniqlMigrationException(@$ "The version directory " "{versionDirectory}" " can't contain " "_transaction" " subdirectory for selected target platform, because the whole migration is already running in single transaction.");
}
if (_directoryService.GetDirectories(versionDirectory, "*").Count() > 1)
{
throw new YuniqlMigrationException(@$ "The version directory " "{versionDirectory}" " containing " "_transaction" " subdirectory can't contain other subdirectories");
}
else if (_directoryService.GetFiles(versionDirectory, "*.*").Count() > 0)
{
throw new YuniqlMigrationException(@$ "The version directory " "{versionDirectory}" " containing " "_transaction" " subdirectory can't contain files");
}
isExplicitTransactionDefined = true;
scriptSubDirectories = _directoryService.GetAllDirectories(transactionDirectory, "*").ToList();
}
else
{
scriptSubDirectories = _directoryService.GetAllDirectories(versionDirectory, "*").ToList();
}
if (isExplicitTransactionDefined)
{
string versionName = new DirectoryInfo(versionDirectory).Name;
//run scripts within a single transaction
_traceService.Info(@$ "The " "_transaction" " directory has been detected and therefore " "{versionName}" " version scripts will run in single transaction. The rollback will not be reliable in case the version scripts contain commands causing implicit commit (e.g. DDL)!");
using (var transaction = connection.BeginTransaction())
{
try
{
RunVersionScriptsInternal(transaction, scriptSubDirectories, transactionDirectory, versionDirectory, schemaName, tableName);
transaction.Commit();
_traceService.Info(@$ "Target database has been commited after running " "{versionName}" " version scripts.");
}
catch (Exception)
{
_traceService.Error(@$ "Target database will be rolled back to the state before running " "{versionName}" " version scripts.");
transaction.Rollback();
throw;
}
}
}
else //run scripts without transaction
{
RunVersionScriptsInternal(transaction, scriptSubDirectories, versionDirectory, versionDirectory, schemaName, tableName);
}
}
catch (Exception)
{
throw;
}
});
}
else
{
var connectionInfo = _dataService.GetConnectionInfo();
_traceService.Info($"Target database is updated. No migration step executed at {connectionInfo.Database} on {connectionInfo.DataSource}.");
}
void RunVersionScriptsInternal(IDbTransaction transaction, List <string> scriptSubDirectories, string scriptDirectory, string versionDirectory, string schemaName, string tableName)
{
try
{
string versionName = new DirectoryInfo(versionDirectory).Name;
scriptSubDirectories.Sort();
scriptSubDirectories.ForEach(scriptSubDirectory =>
{
//run all scripts in the current version folder
RunSqlScripts(connection, transaction, nonTransactionalContext, versionName, workingPath, scriptSubDirectory, schemaName, tableName, tokenKeyPairs, commandTimeout, environmentCode, appliedByTool, appliedByToolVersion);
//import csv files into tables of the the same filename as the csv
RunBulkImport(connection, transaction, workingPath, scriptSubDirectory, delimiter, batchSize, commandTimeout, environmentCode);
});
//run all scripts in the current version folder
RunSqlScripts(connection, transaction, nonTransactionalContext, versionName, workingPath, scriptDirectory, schemaName, tableName, tokenKeyPairs, commandTimeout, environmentCode, appliedByTool, appliedByToolVersion);
//import csv files into tables of the the same filename as the csv
RunBulkImport(connection, transaction, workingPath, scriptDirectory, delimiter, batchSize, commandTimeout, environmentCode);
//update db version
_configurationDataService.InsertVersion(connection, transaction, versionName,
schemaName: schemaName,
tableName: tableName,
commandTimeout: commandTimeout,
appliedByTool: appliedByTool,
appliedByToolVersion: appliedByToolVersion);
_traceService.Info($"Completed migration to version {versionDirectory}");
}
finally
{
//Clear nontransactional context to ensure it is not applied on next version
nonTransactionalContext = null;
}
}
}
/// <summary>
/// Runs migrations by executing alls scripts in the workspace directory.
/// When CSV files are present also run bulk import operations to target database table having same file name.
/// </summary>
/// <param name="workingPath">The directory path to migration project.</param>
/// <param name="targetVersion">The maximum version to run to. When NULL, runs migration to the latest version found in the workspace path.</param>
/// <param name="autoCreateDatabase">When TRUE, creates the database in the target host.</param>
/// <param name="tokenKeyPairs">Token kev/value pairs to replace tokens in script files.</param>
/// <param name="verifyOnly">When TRUE, runs the migration in uncommitted mode. No changes are committed to target database. When NULL, runs migration in atomic mode.</param>
/// <param name="delimiter">Delimeter character in the CSV bulk import files. When NULL, uses comma.</param>
/// <param name="schemaName">Schema name for schema versions table. When empty, uses the default schema in the target data platform. </param>
/// <param name="tableName">Table name for schema versions table. When empty, uses __yuniqldbversion.</param>
/// <param name="commandTimeout">Command timeout in seconds. When NULL, it uses default provider command timeout.</param>
/// <param name="batchSize">Batch rows to processed when performing bulk import. When NULL, it uses default provider batch size.</param>
/// <param name="appliedByTool">The source that initiates the migration. This can be yuniql-cli, yuniql-aspnetcore or yuniql-azdevops.</param>
/// <param name="appliedByToolVersion">The version of the source that initiates the migration.</param>
/// <param name="environmentCode">Environment code for environment-aware scripts.</param>
/// <param name="nonTransactionalResolvingOption">The non-transactional resolving option.</param>
public void Run(
string workingPath,
string targetVersion = null,
bool?autoCreateDatabase = false,
List <KeyValuePair <string, string> > tokenKeyPairs = null,
bool?verifyOnly = false,
string delimiter = null,
string schemaName = null,
string tableName = null,
int?commandTimeout = null,
int?batchSize = null,
string appliedByTool = null,
string appliedByToolVersion = null,
string environmentCode = null,
NonTransactionalResolvingOption?nonTransactionalResolvingOption = null
)
{
if (_dataService.IsAtomicDDLSupported && nonTransactionalResolvingOption != null)
{
throw new NotSupportedException(@$ "The non-transactional failure resolving option " "{nonTransactionalResolvingOption}" " is not available for this platform.");
}
//validate workspace structure
_localVersionService.Validate(workingPath);
//when uncomitted run is not supported, fail migration and throw exceptions
if (verifyOnly.HasValue && verifyOnly == true && !_dataService.IsAtomicDDLSupported)
{
throw new NotSupportedException("Yuniql.Verify is not supported in the target platform. " +
"The feature requires support for atomic DDL operations. " +
"An atomic DDL operations ensures creation of tables, views and other objects and data are rolledback in case of error. " +
"For more information see https://yuniql.io/docs/.");
}
//when no target version specified, we use the latest local version
if (string.IsNullOrEmpty(targetVersion))
{
targetVersion = _localVersionService.GetLatestVersion(workingPath);
_traceService.Info($"No explicit target version requested. We'll use latest available locally {targetVersion} on {workingPath}.");
}
var connectionInfo = _dataService.GetConnectionInfo();
var targetDatabaseName = connectionInfo.Database;
var targetDatabaseServer = connectionInfo.DataSource;
//we try to auto-create the database, we need this to be outside of the transaction scope
//in an event of failure, users have to manually drop the auto-created database!
//we only check if the db exists when --auto-create-db is true
if (autoCreateDatabase.HasValue && autoCreateDatabase == true)
{
var targetDatabaseExists = _configurationDataService.IsDatabaseExists();
if (!targetDatabaseExists)
{
_traceService.Info($"Target database does not exist. Creating database {targetDatabaseName} on {targetDatabaseServer}.");
_configurationDataService.CreateDatabase();
_traceService.Info($"Created database {targetDatabaseName} on {targetDatabaseServer}.");
}
}
//check if database has been pre-configured to support migration and setup when its not
var targetDatabaseConfigured = _configurationDataService.IsDatabaseConfigured(schemaName, tableName);
if (!targetDatabaseConfigured)
{
//create custom schema when user supplied and only if platform supports it
if (_dataService.IsSchemaSupported && null != schemaName && !_dataService.SchemaName.Equals(schemaName))
{
_traceService.Info($"Target schema does not exist. Creating schema {schemaName} on {targetDatabaseName} on {targetDatabaseServer}.");
_configurationDataService.CreateSchema(schemaName);
_traceService.Info($"Created schema {schemaName} on {targetDatabaseName} on {targetDatabaseServer}.");
}
//create empty versions tracking table
_traceService.Info($"Target database {targetDatabaseName} on {targetDatabaseServer} not yet configured for migration.");
_configurationDataService.ConfigureDatabase(schemaName, tableName);
_traceService.Info($"Configured database migration support for {targetDatabaseName} on {targetDatabaseServer}.");
}
else
{
bool databaseupdated = _configurationDataService.UpdateDatabaseConfiguration();
if (databaseupdated)
{
_traceService.Info($"The configuration of migration has been updated for {targetDatabaseName} on {targetDatabaseServer}.");
}
else
{
_traceService.Debug($"The configuration of migration is up to date for {targetDatabaseName} on {targetDatabaseServer}.");
}
}
IList <DbVersion> allVersions = _configurationDataService.GetAllVersions(schemaName, tableName);
NonTransactionalContext nonTransactionalContext = null;
//Check for failed not transactional versions
if (!_dataService.IsAtomicDDLSupported)
{
DbVersion failedDbVersion = allVersions.Where(x => x.StatusId == StatusId.Failed).FirstOrDefault();
if (failedDbVersion != null)
{
if (nonTransactionalResolvingOption == null)
{
_traceService.Error(@$ "Previous migration of " "{failedDbVersion.Version}" " version was not running in transaction and has failed when executing of script " "{failedDbVersion.FailedScriptPath}" " with following error:
{failedDbVersion.FailedScriptError}
{ManualResolvingAfterFailureMessage}");
//Program should exit with non zero exit code
throw new InvalidOperationException();
}
_traceService.Info($@"The non-transactional failure resolving option ""{nonTransactionalResolvingOption}"" was used. Version scripts already applied by previous migration run will be skipped.");
nonTransactionalContext = new NonTransactionalContext(failedDbVersion, nonTransactionalResolvingOption.Value);
}
else
{
if (nonTransactionalResolvingOption != null)
{
_traceService.Error(@$ "The non-transactional failure resolving option " "{nonTransactionalResolvingOption}" " is available only if previous migration has failed.");
//Program should exit with non zero exit code
throw new InvalidOperationException();
}
}
}
var dbVersions = _configurationDataService.GetAllAppliedVersions(schemaName, tableName)
.Select(dv => dv.Version)
.OrderBy(v => v)
.ToList();
//checks if target database already runs the latest version and skips work if it already is
var targeDatabaseLatest = IsTargetDatabaseLatest(targetVersion, schemaName, tableName);
if (!targeDatabaseLatest)
{
//create a shared open connection to entire migration run
using (var connection = _dataService.CreateConnection())
{
connection.Open();
//enclose all executions in a single transaction in case platform supports it
if (_dataService.IsAtomicDDLSupported)
{
_traceService.Debug(@$ "Target platform fully supports transactions. Migration will run in single transaction.");
using (var transaction = connection.BeginTransaction())
{
try
{
RunInternal(connection, transaction);
//when true, the execution is an uncommitted transaction
//and only for purpose of testing if all can go well when it run to the target environment
if (verifyOnly.HasValue && verifyOnly == true)
{
transaction.Rollback();
}
else
{
transaction.Commit();
}
}
catch (Exception)
{
_traceService.Error("Target database will be rolled back to its previous state.");
transaction.Rollback();
throw;
}
}
}
else //otherwise don't use transactions
{
_traceService.Info(@$ "Target platform doesn't reliably support transactions for all commands. Migration will not run in single transaction. Any failure during the migration will require manual resolution.");
try
{
RunInternal(connection, null);
}
catch (Exception)
{
_traceService.Error($"Migration was not running in transaction, therefore roll back of target database to the state before the migration is not possible.");
throw;
}
}
}
}
else //runs all scripts files inside _draft on every yuniql run regardless of state of target database
{
//enclose all executions in a single transaction
using (var connection = _dataService.CreateConnection())
{
connection.Open();
//enclose all executions in a single transaction in case platform supports it
if (_dataService.IsAtomicDDLSupported)
{
_traceService.Debug(@$ "Target platform fully supports transactions. Migration will run in single transaction.");
using (var transaction = connection.BeginTransaction())
{
try
{
RunDraftInternal(connection, transaction);
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
}
else //otherwise don't use transactions
{
try
{
_traceService.Info($"Target platform doesn't reliably support transactions for all commands. " +
$"Migration will not run in single transaction. " +
$"Any failure during the migration can prevent automatic completing of migration.");
RunDraftInternal(connection, null);
}
catch (Exception)
{
_traceService.Error("Migration was not running in transaction, therefore roll back of target database to its previous state is not possible. " +
"Migration need to be completed manually! Running of Yuniql again, might cause that some scripts will be executed twice!");
throw;
}
}
_traceService.Info($"Target database runs the latest version already. Scripts in _pre, _draft and _post are executed.");
}
}
//local method
void RunInternal(IDbConnection connection, IDbTransaction transaction)
{
//check if database has been pre-configured and execute init scripts
if (!targetDatabaseConfigured)
{
//runs all scripts in the _init folder
RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_init"), tokenKeyPairs, delimiter: delimiter, commandTimeout: commandTimeout, environmentCode: environmentCode);
_traceService.Info($"Executed script files on {Path.Combine(workingPath, "_init")}");
}
//checks if target database already runs the latest version and skips work if it already is
//runs all scripts in the _pre folder and subfolders
RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_pre"), tokenKeyPairs, delimiter: delimiter, commandTimeout: commandTimeout, environmentCode: environmentCode);
_traceService.Info($"Executed script files on {Path.Combine(workingPath, "_pre")}");
//runs all scripts int the vxx.xx folders and subfolders
RunVersionScripts(connection, transaction, dbVersions, workingPath, targetVersion, nonTransactionalContext, tokenKeyPairs, delimiter: delimiter, schemaName: schemaName, tableName: tableName, commandTimeout: commandTimeout, batchSize: batchSize, appliedByTool: appliedByTool, appliedByToolVersion: appliedByToolVersion, environmentCode: environmentCode);
//runs all scripts in the _draft folder and subfolders
RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_draft"), tokenKeyPairs, delimiter: delimiter, commandTimeout: commandTimeout, environmentCode: environmentCode);
_traceService.Info($"Executed script files on {Path.Combine(workingPath, "_draft")}");
//runs all scripts in the _post folder and subfolders
RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_post"), tokenKeyPairs, delimiter: delimiter, commandTimeout: commandTimeout, environmentCode: environmentCode);
_traceService.Info($"Executed script files on {Path.Combine(workingPath, "_post")}");
}
void RunDraftInternal(IDbConnection connection, IDbTransaction transaction)
{
//runs all scripts in the _pre folder and subfolders
RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_pre"), tokenKeyPairs, delimiter: delimiter, commandTimeout: commandTimeout, environmentCode: environmentCode);
_traceService.Info($"Executed script files on {Path.Combine(workingPath, "_pre")}");
//runs all scripts in the _draft folder and subfolders
RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_draft"), tokenKeyPairs, delimiter: delimiter, commandTimeout: commandTimeout, environmentCode: environmentCode);
_traceService.Info($"Executed script files on {Path.Combine(workingPath, "_draft")}");
//runs all scripts in the _post folder and subfolders
RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_post"), tokenKeyPairs, delimiter: delimiter, commandTimeout: commandTimeout, environmentCode: environmentCode);
_traceService.Info($"Executed script files on {Path.Combine(workingPath, "_post")}");
}
}
///<inheritdoc/>
public override void RunVersionScripts(
IDbConnection connection,
IDbTransaction transaction,
List <string> dbVersions,
string workingPath,
string targetVersion,
NonTransactionalContext nonTransactionalContext,
List <KeyValuePair <string, string> > tokenKeyPairs = null,
string bulkSeparator = null,
string metaSchemaName = null,
string metaTableName = null,
int?commandTimeout = null,
int?bulkBatchSize = null,
string appliedByTool = null,
string appliedByToolVersion = null,
string environmentCode = null
)
{
//excludes all versions already executed
var versionDirectories = _directoryService.GetDirectories(workingPath, "v*.*")
.Where(v => !dbVersions.Contains(new DirectoryInfo(v).Name))
.ToList();
//exclude all versions greater than the target version
if (!string.IsNullOrEmpty(targetVersion))
{
versionDirectories.RemoveAll(v =>
{
var cv = new LocalVersion(new DirectoryInfo(v).Name);
var tv = new LocalVersion(targetVersion);
return(string.Compare(cv.SemVersion, tv.SemVersion) == 1);
});
}
//execute all sql scripts in the version folders
if (versionDirectories.Any())
{
versionDirectories.Sort();
versionDirectories.ForEach(versionDirectory =>
{
var versionName = new DirectoryInfo(versionDirectory).Name;
//run scripts in all sub-directories
var scriptSubDirectories = _directoryService.GetAllDirectories(versionDirectory, "*").ToList();
scriptSubDirectories.Sort();
scriptSubDirectories.ForEach(scriptSubDirectory =>
{
//run all scripts in the current version folder
RunSqlScripts(connection, transaction, nonTransactionalContext, versionName, workingPath, scriptSubDirectory, metaSchemaName, metaTableName, tokenKeyPairs, commandTimeout, environmentCode);
//import csv files into tables of the the same filename as the csv
RunBulkImport(connection, transaction, workingPath, scriptSubDirectory, bulkSeparator, bulkBatchSize, commandTimeout, environmentCode);
});
//run all scripts in the current version folder
RunSqlScripts(connection, transaction, nonTransactionalContext, versionName, workingPath, versionDirectory, metaSchemaName, metaTableName, tokenKeyPairs, commandTimeout, environmentCode);
//import csv files into tables of the the same filename as the csv
RunBulkImport(connection, transaction, workingPath, versionDirectory, bulkSeparator, bulkBatchSize, commandTimeout, environmentCode);
//update db version
_configurationDataService.InsertVersion(connection, transaction, versionName,
metaSchemaName: metaSchemaName,
metaTableName: metaTableName,
commandTimeout: commandTimeout,
appliedByTool: appliedByTool,
appliedByToolVersion: appliedByToolVersion);
_traceService.Info($"Completed migration to version {versionDirectory}");
});
}
else
{
var connectionInfo = _dataService.GetConnectionInfo();
_traceService.Info($"Target database is updated. No migration step executed at {connectionInfo.Database} on {connectionInfo.DataSource}.");
}
}
/// <inheritdoc />
public override void Run(
string workingPath,
string targetVersion = null,
bool?autoCreateDatabase = false,
List <KeyValuePair <string, string> > tokenKeyPairs = null,
bool?verifyOnly = false,
string bulkSeparator = null,
string metaSchemaName = null,
string metaTableName = null,
int?commandTimeout = null,
int?bulkBatchSize = null,
string appliedByTool = null,
string appliedByToolVersion = null,
string environmentCode = null,
NonTransactionalResolvingOption?nonTransactionalResolvingOption = null,
bool noTransaction = false
)
{
if (_dataService.IsAtomicDDLSupported && nonTransactionalResolvingOption != null)
{
throw new NotSupportedException(@$ "The non-transactional failure resolving option " "{nonTransactionalResolvingOption}" " is not available for this platform.");
}
//check the workspace structure if required directories are present
_localVersionService.Validate(workingPath);
//when uncomitted run is not supported, fail migration, throw exceptions and return error exit code
if (verifyOnly.HasValue && verifyOnly == true && !_dataService.IsAtomicDDLSupported)
{
throw new NotSupportedException("Yuniql.Verify is not supported in the target platform. " +
"The feature requires support for atomic DDL operations. " +
"An atomic DDL operations ensures creation of tables, views and other objects and data are rolledback in case of error. " +
"For more information see https://yuniql.io/docs/.");
}
//when no target version specified, we use the latest local version
if (string.IsNullOrEmpty(targetVersion))
{
targetVersion = _localVersionService.GetLatestVersion(workingPath);
_traceService.Info($"No explicit target version requested. We'll use latest available locally {targetVersion} on {workingPath}.");
}
var connectionInfo = _dataService.GetConnectionInfo();
var targetDatabaseName = connectionInfo.Database;
var targetDatabaseServer = connectionInfo.DataSource;
//we try to auto-create the database, we need this to be outside of the transaction scope
//in an event of failure, users have to manually drop the auto-created database!
//we only check if the db exists when --auto-create-db is true
if (autoCreateDatabase.HasValue && autoCreateDatabase == true)
{
var targetDatabaseExists = _configurationDataService.IsDatabaseExists();
if (!targetDatabaseExists)
{
_traceService.Info($"Target database does not exist. Creating database {targetDatabaseName} on {targetDatabaseServer}.");
_configurationDataService.CreateDatabase();
_traceService.Info($"Created database {targetDatabaseName} on {targetDatabaseServer}.");
}
}
//check if database has been pre-configured to support migration and setup when its not
var targetDatabaseConfigured = _configurationDataService.IsDatabaseConfigured(metaSchemaName, metaTableName);
if (!targetDatabaseConfigured)
{
//create custom schema when user supplied and only if platform supports it
if (_dataService.IsSchemaSupported && null != metaSchemaName && !_dataService.SchemaName.Equals(metaSchemaName))
{
_traceService.Info($"Target schema does not exist. Creating schema {metaSchemaName} on {targetDatabaseName} on {targetDatabaseServer}.");
_configurationDataService.CreateSchema(metaSchemaName);
_traceService.Info($"Created schema {metaSchemaName} on {targetDatabaseName} on {targetDatabaseServer}.");
}
//create empty versions tracking table
_traceService.Info($"Target database {targetDatabaseName} on {targetDatabaseServer} not yet configured for migration.");
_configurationDataService.ConfigureDatabase(metaSchemaName, metaTableName);
_traceService.Info($"Configured database migration support for {targetDatabaseName} on {targetDatabaseServer}.");
}
var targetDatabaseUpdated = _configurationDataService.UpdateDatabaseConfiguration(metaSchemaName, metaTableName);
if (targetDatabaseUpdated)
{
_traceService.Info($"The configuration of migration has been updated for {targetDatabaseName} on {targetDatabaseServer}.");
}
else
{
_traceService.Debug($"The configuration of migration is up to date for {targetDatabaseName} on {targetDatabaseServer}.");
}
NonTransactionalContext nonTransactionalContext = null;
//check for presence of failed no-transactional versions from previous runs
var allVersions = _configurationDataService.GetAllVersions(metaSchemaName, metaTableName);
var failedVersion = allVersions.Where(x => x.Status == Status.Failed).FirstOrDefault();
if (failedVersion != null)
{
//check if user had issue resolving option such as continue on failure
if (nonTransactionalResolvingOption == null)
{
//program should exit with non zero exit code
var message = @$ "Previous migration of " "{failedVersion.Version}" " version was not running in transaction and has failed when executing of script " "{failedVersion.FailedScriptPath}" " with following error: {failedVersion.FailedScriptError} {MESSAGES.ManualResolvingAfterFailureMessage}";
_traceService.Error(message);
throw new InvalidOperationException(message);
}
_traceService.Info($@"The non-transactional failure resolving option ""{nonTransactionalResolvingOption}"" was used. Version scripts already applied by previous migration run will be skipped.");
nonTransactionalContext = new NonTransactionalContext(failedVersion, nonTransactionalResolvingOption.Value);
}
else
{
if (nonTransactionalResolvingOption != null)
{
//program should exit with non zero exit code
_traceService.Error(@$ "The non-transactional failure resolving option " "{nonTransactionalResolvingOption}" " is available only if previous migration has failed.");
throw new InvalidOperationException();
}
}
var appliedVersions = _configurationDataService.GetAllAppliedVersions(metaSchemaName, metaTableName)
.Select(dv => dv.Version)
.OrderBy(v => v)
.ToList();
//checks if target database already runs the latest version and skips work if it already is
var targeDatabaseLatest = IsTargetDatabaseLatest(targetVersion, metaSchemaName, metaTableName);
if (!targeDatabaseLatest)
{
//create a shared open connection to entire migration run
using (var connection = _dataService.CreateConnection())
{
connection.Open();
RunAllInternal(connection, null);
}
}
else
{
//runs all scripts files inside _draft on every yuniql run regardless of state of target database
using (var connection = _dataService.CreateConnection())
{
connection.Open();
RunDraftInternal(connection, null);
}
}
//local method
void RunAllInternal(IDbConnection connection, IDbTransaction transaction)
{
//check if database has been pre-configured and execute init scripts
if (!targetDatabaseConfigured)
{
//runs all scripts in the _init folder
RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_init"), tokenKeyPairs, bulkSeparator: bulkSeparator, commandTimeout: commandTimeout, environmentCode: environmentCode);
_traceService.Info($"Executed script files on {Path.Combine(workingPath, "_init")}");
}
//checks if target database already runs the latest version and skips work if it already is
//runs all scripts in the _pre folder and subfolders
RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_pre"), tokenKeyPairs, bulkSeparator: bulkSeparator, commandTimeout: commandTimeout, environmentCode: environmentCode);
_traceService.Info($"Executed script files on {Path.Combine(workingPath, "_pre")}");
//runs all scripts int the vxx.xx folders and subfolders
RunVersionScripts(connection, transaction, appliedVersions, workingPath, targetVersion, nonTransactionalContext, tokenKeyPairs, bulkSeparator: bulkSeparator, metaSchemaName: metaSchemaName, metaTableName: metaTableName, commandTimeout: commandTimeout, bulkBatchSize: bulkBatchSize, appliedByTool: appliedByTool, appliedByToolVersion: appliedByToolVersion, environmentCode: environmentCode);
//runs all scripts in the _draft folder and subfolders
RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_draft"), tokenKeyPairs, bulkSeparator: bulkSeparator, commandTimeout: commandTimeout, environmentCode: environmentCode);
_traceService.Info($"Executed script files on {Path.Combine(workingPath, "_draft")}");
//runs all scripts in the _post folder and subfolders
RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_post"), tokenKeyPairs, bulkSeparator: bulkSeparator, commandTimeout: commandTimeout, environmentCode: environmentCode);
_traceService.Info($"Executed script files on {Path.Combine(workingPath, "_post")}");
}
//local method
void RunDraftInternal(IDbConnection connection, IDbTransaction transaction)
{
//runs all scripts in the _pre folder and subfolders
RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_pre"), tokenKeyPairs, bulkSeparator: bulkSeparator, commandTimeout: commandTimeout, environmentCode: environmentCode);
_traceService.Info($"Executed script files on {Path.Combine(workingPath, "_pre")}");
//runs all scripts in the _draft folder and subfolders
RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_draft"), tokenKeyPairs, bulkSeparator: bulkSeparator, commandTimeout: commandTimeout, environmentCode: environmentCode);
_traceService.Info($"Executed script files on {Path.Combine(workingPath, "_draft")}");
//runs all scripts in the _post folder and subfolders
RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_post"), tokenKeyPairs, bulkSeparator: bulkSeparator, commandTimeout: commandTimeout, environmentCode: environmentCode);
_traceService.Info($"Executed script files on {Path.Combine(workingPath, "_post")}");
}
}