/// <inheritdoc />
public virtual void RunNonVersionScripts(
IDbConnection connection,
IDbTransaction transaction,
string workingPath,
List <KeyValuePair <string, string> > tokenKeyPairs = null,
string bulkSeparator = null,
int?commandTimeout = null,
string environmentCode = null
)
{
//extract and filter out scripts when environment code is used
var sqlScriptFiles = _directoryService.GetAllFiles(workingPath, "*.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 target 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}.");
});
}
private void RunNonVersionScripts(
IDbConnection connection,
IDbTransaction transaction,
string workingPath,
List <KeyValuePair <string, string> > tokenKeyPairs = null,
string delimiter = null,
int?commandTimeout = null,
string environmentCode = null
)
{
//extract and filter out scripts when environment code is used
var sqlScriptFiles = _directoryService.GetAllFiles(workingPath, "*.sql").ToList();
sqlScriptFiles = _directoryService.FilterFiles(workingPath, environmentCode, sqlScriptFiles).ToList();
_traceService.Info($"Found the {sqlScriptFiles.Count} script files on {workingPath}");
_traceService.Info($"{string.Join(@"\r\n\t", sqlScriptFiles.Select(s => new FileInfo(s).Name))}");
//execute all script files in the target folder
sqlScriptFiles.Sort();
sqlScriptFiles.ForEach(scriptFile =>
{
//https://stackoverflow.com/questions/25563876/executing-sql-batch-containing-go-statements-in-c-sharp/25564722#25564722
var sqlStatementRaw = _fileService.ReadAllText(scriptFile);
var sqlStatements = _dataService.BreakStatements(sqlStatementRaw)
.Where(s => !string.IsNullOrWhiteSpace(s))
.ToList();
sqlStatements.ForEach(sqlStatement =>
{
//replace tokens with values from the cli
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}.");
});
}
///<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 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 =>
{
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}.");
});
}
/// <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
)
{
string currentScriptFile = null;
try
{
//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, we also make sure its sorted by file name
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}");
_configurationDataService.ExecuteSql(
connection: connection,
commandText: sqlStatement,
transaction: transaction,
commandTimeout: commandTimeout,
traceService: _traceService);
});
_traceService.Info($"Executed script file {scriptFile}.");
}
});
}
catch (Exception exception)
{
//try parse the known sql error
string sqlError;
if (!_dataService.TryParseErrorFromException(exception, out sqlError))
{
//if not sucesfull, use the whole exception
sqlError = exception.ToString();
}
//in case scripts are not executed within transaction, mark version as failed in database
if (transaction == null)
{
_configurationDataService.InsertVersion(connection, transaction, version,
metaSchemaName: metaSchemaName,
metaTableName: metaTableName,
commandTimeout: commandTimeout,
appliedByTool: appliedByTool,
appliedByToolVersion: appliedByToolVersion,
failedScriptPath: currentScriptFile,
failedScriptError: 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} {MESSAGES.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;
}
}