public static List <SqlTransactionBatch> GroupByTransaction(IEnumerable <string> sqlScripts)
{
sqlScripts = sqlScripts.Where(s => !string.IsNullOrWhiteSpace(s.Replace(SqlUtility.NoTransactionTag, "")));
var batches = CsUtility.GroupItemsKeepOrdering(sqlScripts, SqlUtility.ScriptSupportsTransaction);
return(batches.Select(batch => new SqlTransactionBatch(batch.Items)
{
UseTransacion = batch.Key
}).ToList());
}
/// <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)
{
Action <int> reportProgress = currentBatchCount =>
{
var now = DateTime.Now;
int executedCount = previousBatchesCount + currentBatchCount + 1;
if (now.Subtract(lastReportTime).TotalMilliseconds > _reportDelayMs &&
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, null, reportProgress);
previousBatchesCount += sqlBatch.Scripts.Count;
}
}