async Task ExecuteSqlScriptAsync(string scriptName, DatabaseLock dbLock)
{
if (dbLock == null)
{
throw new ArgumentNullException(nameof(dbLock));
}
if (!dbLock.IsHeld)
{
throw new ArgumentException("This database lock has already been released!", nameof(dbLock));
}
string scriptText = await GetScriptTextAsync(scriptName);
// Split script into distinct executeable commands
IEnumerable <string> scriptCommands = Regex.Split(scriptText, @"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase)
.Where(x => x.Trim().Length > 0);
Stopwatch latencyStopwatch = Stopwatch.StartNew();
try
{
foreach (string commandText in scriptCommands)
{
using SqlCommand command = dbLock.CreateCommand();
command.CommandText = commandText;
await command.ExecuteNonQueryAsync();
}
}
finally
{
latencyStopwatch.Stop();
this.traceHelper.ExecutedSqlScript(scriptName, latencyStopwatch);
}
}
public async Task CreateOrUpgradeSchemaAsync(bool recreateIfExists)
{
// Prevent other create or delete operations from executing at the same time.
await using DatabaseLock dbLock = await this.AcquireDatabaseLockAsync(this.settings.CreateDatabaseIfNotExists);
var currentSchemaVersion = new SemanticVersion(0, 0, 0);
if (recreateIfExists)
{
await this.DropSchemaAsync(dbLock);
}
else
{
// If the database already has the latest schema, then skip
using SqlCommand command = dbLock.CreateCommand();
command.CommandText = "dt._GetVersions";
command.CommandType = CommandType.StoredProcedure;
try
{
using DbDataReader reader = await SqlUtils.ExecuteReaderAsync(command, this.traceHelper);
if (await reader.ReadAsync())
{
// The first result contains the latest version
currentSchemaVersion = SqlUtils.GetSemanticVersion(reader);
if (currentSchemaVersion >= DTUtils.ExtensionVersion)
{
// The schema is already up-to-date.
return;
}
}
}
catch (SqlException e) when(e.Number == 2812 /* Could not find stored procedure */)
{
// Ignore - this is expected for new databases
}
}
// SQL schema setup scripts are embedded resources in the assembly, making them immutable post-build.
Assembly assembly = typeof(SqlOrchestrationService).Assembly;
IEnumerable <string> createSchemaFiles = assembly.GetManifestResourceNames()
.Where(name => name.Contains(".schema-") && name.EndsWith(".sql"));
var versionedFiles = new Dictionary <SemanticVersion, string>();
foreach (string name in createSchemaFiles)
{
// Attempt to parse the semver-like string from the resource name.
// This version number tells us whether to execute the script for this extension version.
const string RegexExpression = @"schema-(\d+.\d+.\d+(?:-\w+)?).sql$";
Match match = Regex.Match(name, RegexExpression);
if (!match.Success || match.Groups.Count < 2)
{
throw new InvalidOperationException($"Failed to find version information in resource name '{name}'. The resource name must match the regex expression '{RegexExpression}'.");
}
SemanticVersion version = SemanticVersion.Parse(match.Groups[1].Value);
if (!versionedFiles.TryAdd(version, match.Value))
{
throw new InvalidOperationException($"There must not be more than one script resource with the same version number! Found {version} multiple times.");
}
}
// Sort by the version numbers to ensure that we run them in the correct order
foreach ((SemanticVersion version, string name) in versionedFiles.OrderBy(pair => pair.Key))
{
// Skip past versions that are already present in the database
if (version > currentSchemaVersion)
{
await this.ExecuteSqlScriptAsync(name, dbLock);
currentSchemaVersion = version;
}
}
// Add or update stored procedures, functions, and views
await this.ExecuteSqlScriptAsync("logic.sql", dbLock);
// Configure security roles, permissions, etc.
await this.ExecuteSqlScriptAsync("permissions.sql", dbLock);
// Insert the current extension version number into the database and commit the transaction.
// The extension version is used instead of the schema version to more accurately track whether
// we need to update the sprocs or views.
using (SqlCommand command = dbLock.CreateCommand())
{
command.CommandText = "dt._UpdateVersion";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@SemanticVersion", SqlDbType.NVarChar, 100).Value = DTUtils.ExtensionVersion.ToString();
await SqlUtils.ExecuteNonQueryAsync(command, this.traceHelper);
}
await dbLock.CommitAsync();
}