public class SchemaUpdater
{
private readonly string _connectionString;
private readonly ILog _logger;
private readonly string _environment;
public SchemaUpdater(string connectionString, string environment)
: this(connectionString, environment, LogManager.GetLogger(typeof(SchemaUpdater))) { }
internal SchemaUpdater(string connectionString, string environment, ILog logger)
{
_connectionString = connectionString;
_environment = environment;
_logger = logger;
}
public void UpdateSchema()
{
MaybeCreateAuditTable();
var previousUpdates = GetPreviousSchemaUpdates();
var assemblyPath = Uri.UnescapeDataString(new UriBuilder(typeof(SchemaUpdater).GetTypeInfo().Assembly.CodeBase).Path);
var schemaDirectory = Path.Combine(Path.GetDirectoryName(assemblyPath), "schema-updates");
var schemaUpdates = Directory.EnumerateFiles(schemaDirectory, "*.sql", SearchOption.TopDirectoryOnly)
.Select(fn => new { FullPath = fn, Filename = Path.GetFileName(fn) })
.Where(file => !previousUpdates.Contains(file.Filename))
.OrderBy(file => file.Filename)
.Select(file => new { file.Filename, Query = File.ReadAllText(file.FullPath) })
.ToList();
foreach (var update in schemaUpdates)
{
using (var connection = new SqlConnection(_connectionString))
{
try
{
var splitOnGo = SplitOnGo(update.Query);
foreach (var statement in splitOnGo)
{
try
{
connection.Execute(statement);
}
catch (Exception exception)
{
Console.WriteLine(exception);
throw;
}
}
connection.Execute("INSERT INTO SchemaRevision (Filename, FileContents) VALUES (@filename, @fileContent)",
new { filename = update.Filename, fileContent = update.Query });
}
catch (Exception e)
{
_logger.Fatal(new { Message = "Unable to apply schema change", update.Filename, update.Query, Environment = _environment }, e);
throw;
}
}
}
}
public static ICollection<string> SplitOnGo(string sqlScript)
{
// Split by "GO" statements
var statements = Regex.Split(
sqlScript,
@"^[\t\r\n]*GO[\t\r\n]*\d*[\t\r\n]*(?:--.*)?$",
RegexOptions.Multiline |
RegexOptions.IgnorePatternWhitespace |
RegexOptions.IgnoreCase);
// Remove empties, trim, and return
var materialized = statements
.Where(x => !string.IsNullOrWhiteSpace(x))
.Select(x => x.Trim(' ', '\r', '\n'))
.ToList();
return materialized;
}
internal void MaybeCreateAuditTable()
{
const string createAuditTable =
@"IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'SchemaRevision')
BEGIN
CREATE TABLE [dbo].[SchemaRevision]
(
[SchemaRevisionNbr] BIGINT IDENTITY(1,1),
[Filename] VARCHAR(256),
[FileContents] VARCHAR(MAX),
CONSTRAINT PK_SchemaRevision PRIMARY KEY (SchemaRevisionNbr),
)
END";
using (var connection = new SqlConnection(_connectionString))
{
connection.Execute(createAuditTable);
}
}
internal HashSet<string> GetPreviousSchemaUpdates()
{
using (var connection = new SqlConnection(_connectionString))
{
var results = connection.Query<string>(@"SELECT Filename FROM SchemaRevision");
return new HashSet<string>(results, StringComparer.OrdinalIgnoreCase);
}
}
}
