/// <summary>
/// 1. Splits the scripts by the SQL batch delimiter ("GO", for Microsoft SQL Server). See <see cref="SqlUtility.SplitBatches(string)"/>.
/// 2. Detects and applies the transaction usage tag. See <see cref="SqlUtility.NoTransactionTag"/> and <see cref="SqlUtility.ScriptSupportsTransaction(string)"/>.
/// 3. Reports progress (Info level) after each minute.
/// 4. Prefixes each SQL script with a comment containing the script's name.
/// </summary>
public void Execute(IEnumerable <SqlScript> sqlScripts)
{
var scriptParts = sqlScripts
.SelectMany(script => script.IsBatch
? SqlUtility.SplitBatches(script.Sql).Select(scriptPart => new SqlScript {
Name = script.Name, Sql = scriptPart, IsBatch = false
})
: new[] { script })
.Where(script => !string.IsNullOrWhiteSpace(script.Sql));
var sqlBatches = CsUtility.GroupItemsKeepOrdering(scriptParts, script => SqlUtility.ScriptSupportsTransaction(script.Sql))
.Select(group => new
{
UseTransaction = group.Key,
// The empty NoTransactionTag script is used by the DatabaseGenerator to split transactions.
// This is why there scrips are removed *after* grouping
Scripts = group.Items.Where(s => !string.Equals(s.Sql, SqlUtility.NoTransactionTag, StringComparison.Ordinal)).ToList()
})
.Where(group => group.Scripts.Count > 0) // Cleanup after removing the empty NoTransactionTag scripts.
.ToList();
_logger.Trace(() => "SqlBatches: " + string.Join(", ", sqlBatches.Select(b => $"{(b.UseTransaction ? "tran" : "notran")} {b.Scripts.Count}")));
int totalCount = sqlBatches.Sum(b => b.Scripts.Count);
int previousBatchesCount = 0;
var startTime = DateTime.Now;
var lastReportTime = startTime;
foreach (var sqlBatch in sqlBatches)
{
IDisposable timeoutWarning = null;
Func <int, string> sqlScriptDescription = scriptIndex =>
{
var script = sqlBatch.Scripts[scriptIndex];
if (!string.IsNullOrEmpty(script.Name))
{
return($" '{script.Name.Trim()}'.");
}
else
{
return($": {script.Sql.Limit(1000).Trim()}");
}
};
Action <int> initializeProgress = scriptIndex =>
{
timeoutWarning = _delayedLogger.PerformanceWarning(() => $"Executing SQL script{sqlScriptDescription(scriptIndex)}");
};
Action <int> reportProgress = scriptIndex =>
{
if (timeoutWarning != null)
{
timeoutWarning.Dispose();
timeoutWarning = null;
}
var now = DateTime.Now;
int executedCount = previousBatchesCount + scriptIndex + 1;
if (now.Subtract(lastReportTime).TotalMilliseconds > _options.ReportProgressMs &&
executedCount < totalCount) // No need to report progress if the work is done.
{
double estimatedTotalMs = now.Subtract(startTime).TotalMilliseconds / executedCount * totalCount;
var remainingTime = startTime.AddMilliseconds(estimatedTotalMs).Subtract(now);
_logger.Info($"Executed {executedCount} / {totalCount} SQL scripts. {(remainingTime.TotalMinutes).ToString("f2")} minutes remaining.");
lastReportTime = now;
}
};
var scriptsWithName = sqlBatch.Scripts
.Select(script => string.IsNullOrEmpty(script.Name)
? script.Sql
: "--Name: " + script.Name.Replace("\r", " ").Replace("\n", " ") + "\r\n" + script.Sql);
_sqlExecuter.ExecuteSql(scriptsWithName, sqlBatch.UseTransaction, initializeProgress, reportProgress);
previousBatchesCount += sqlBatch.Scripts.Count;
}
}
