private string GetPreparedSqlStatement(string sqlStatement, string schemaName, string tableName)
{
var tokens = new List <KeyValuePair <string, string> > {
new KeyValuePair <string, string>(RESERVED_TOKENS.YUNIQL_DB_NAME, _dataService.GetConnectionInfo().Database),
new KeyValuePair <string, string>(RESERVED_TOKENS.YUNIQL_SCHEMA_NAME, schemaName ?? _dataService.SchemaName),
new KeyValuePair <string, string>(RESERVED_TOKENS.YUNIQL_TABLE_NAME, tableName ?? _dataService.TableName)
};
return(_tokenReplacementService.Replace(tokens, sqlStatement));
}
///<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}.");
});
}
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}.");
});
}
public override bool CheckIfDbExist(string connectionString)
{
//extract the test database name from connection string
var connectionStringBuilder = new SnowflakeDbConnectionStringBuilder();
connectionStringBuilder.ConnectionString = connectionString;
connectionStringBuilder.TryGetValue("db", out object databaseName);
//prepare the sql statement
var tokens = new List <KeyValuePair <string, string> > {
new KeyValuePair <string, string>(RESERVED_TOKENS.YUNIQL_DB_NAME, databaseName.ToString()),
new KeyValuePair <string, string>(RESERVED_TOKENS.YUNIQL_SCHEMA_NAME, base.SchemaName),
};
var sqlStatement = _tokenReplacementService.Replace(tokens, _dataService.GetSqlForCheckIfDatabaseExists());
//prepare a connection to snowflake without any targetdb or schema, we need to remove these keys else it throws an error that db doesn't exists
var masterConnectionStringBuilder = new SnowflakeDbConnectionStringBuilder();
masterConnectionStringBuilder.ConnectionString = connectionString;
masterConnectionStringBuilder.Remove("db");
masterConnectionStringBuilder.Remove("schema");
return(QuerySingleRow(masterConnectionStringBuilder.ConnectionString, sqlStatement));
}
/// <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,
bool requiredClearedDraftFolder = false
)
{
//extract and filter out scripts when environment code is used
var sqlScriptFiles = _directoryService.GetAllFiles(workingPath, "*.sql").ToList();
// Throw exception when --require-cleared-draft is set to TRUE
if (sqlScriptFiles.Any() && requiredClearedDraftFolder && workingPath.Contains("_draft"))
{
_traceService.Error($"Cannot execute migration when option --require-cleared-draft is set to TRUE.");
throw new ApplicationException($"Cannot execute migration when option --require-cleared-draft is set to TRUE.");
}
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}.");
});
}
/// <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;
}
}