/// <summary>
/// Executes the specified script against a database at a given connection string.
/// </summary>
/// <param name="script">The script.</param>
/// <param name="variables">Variables to replace in the script</param>
public void Execute(SqlScript script, IDictionary<string, string> variables)
{
if (variables == null)
variables = new Dictionary<string, string>();
if (Schema != null && !variables.ContainsKey("schema"))
variables.Add("schema", Schema);
log().WriteInformation("Executing SQL Server script '{0}'", script.Name);
var contents = script.Contents;
if (string.IsNullOrEmpty(Schema))
contents = new StripSchemaPreprocessor().Process(contents);
contents = new VariableSubstitutionPreprocessor(variables).Process(contents);
contents = (scriptPreprocessors??new IScriptPreprocessor[0])
.Aggregate(contents, (current, additionalScriptPreprocessor) => additionalScriptPreprocessor.Process(current));
var scriptStatements = SplitByGoStatements(contents);
var index = -1;
try
{
using (var connection = connectionFactory())
{
connection.Open();
foreach (var statement in scriptStatements)
{
index++;
var command = connection.CreateCommand();
command.CommandText = statement;
if (ExecutionTimeoutSeconds != null)
command.CommandTimeout = ExecutionTimeoutSeconds.Value;
command.ExecuteNonQuery();
}
}
}
catch (SqlException sqlException)
{
log().WriteInformation("SQL exception has occured in script: '{0}'", script.Name);
log().WriteError("Script block number: {0}; Block line {1}; Message: {2}", index, sqlException.LineNumber, sqlException.Procedure, sqlException.Number, sqlException.Message);
log().WriteError(sqlException.ToString());
throw;
}
catch (DbException sqlException)
{
log().WriteInformation("DB exception has occured in script: '{0}'", script.Name);
log().WriteError("Script block number: {0}; Error code {1}; Message: {2}", index, sqlException.ErrorCode, sqlException.Message);
log().WriteError(sqlException.ToString());
throw;
}
catch (Exception ex)
{
log().WriteInformation("Exception has occured in script: '{0}'", script.Name);
log().WriteError(ex.ToString());
throw;
}
}
/// <summary>
/// Performs the database upgrade.
/// </summary>
public virtual DatabaseUpgradeResult PerformUpgrade()
{
var executed = new List <SqlScript>();
SqlScript executedScript = null;
try
{
using (configuration.ConnectionManager.OperationStarting(configuration.Log, executed))
{
configuration.Log.WriteInformation("Beginning database upgrade");
var scriptsToExecute = GetScriptsToExecuteInsideOperation();
if (scriptsToExecute.Count == 0)
{
configuration.Log.WriteInformation("No new scripts need to be executed - completing.");
return(new DatabaseUpgradeResult(executed, true, null, null));
}
configuration.ScriptExecutor.VerifySchema();
foreach (var script in scriptsToExecute)
{
executedScript = script;
configuration.ScriptExecutor.Execute(script, configuration.Variables);
OnScriptExecuted(new ScriptExecutedEventArgs(script, configuration.ConnectionManager));
executed.Add(script);
}
configuration.Log.WriteInformation("Upgrade successful");
return(new DatabaseUpgradeResult(executed, true, null, null));
}
}
catch (Exception ex)
{
if (executedScript != null)
{
ex.Data["Error occurred in script: "] = executedScript.Name;
}
configuration.Log.WriteError("Upgrade failed due to an unexpected exception:\r\n{0}", ex.ToString());
return(new DatabaseUpgradeResult(executed, false, ex, executedScript));
}
}
/// <summary>
/// Records an upgrade script for a database.
/// </summary>
/// <param name="script">The script.</param>
public void StoreExecutedScript(SqlScript script)
{
var exists = DoesTableExist();
if (!exists)
{
log().WriteInformation(string.Format("Creating the {0} table", CreateTableName(schema, table)));
connectionManager().ExecuteCommandsWithManagedConnection(dbCommandFactory =>
{
using (var command = dbCommandFactory())
{
command.CommandText = CreateTableSql(schema, table);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
log().WriteInformation(string.Format("The {0} table has been created", CreateTableName(schema, table)));
});
}
connectionManager().ExecuteCommandsWithManagedConnection(dbCommandFactory =>
{
using (var command = dbCommandFactory())
{
command.CommandText = string.Format("insert into {0} (ScriptName, Applied) values (@scriptName, @applied)", CreateTableName(schema, table));
var scriptNameParam = command.CreateParameter();
scriptNameParam.ParameterName = "scriptName";
scriptNameParam.Value = script.Name;
command.Parameters.Add(scriptNameParam);
var appliedParam = command.CreateParameter();
appliedParam.ParameterName = "applied";
appliedParam.Value = DateTime.Now;
command.Parameters.Add(appliedParam);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
});
}
/// <summary>
/// Records a database upgrade for a database specified in a given connection string.
/// </summary>
/// <param name="script">The script.</param>
/// <param name="dbCommandFactory"></param>
public override void StoreExecutedScript(DbUp.Engine.SqlScript script, Func <IDbCommand> dbCommandFactory)
{
EnsureTableExistsAndIsLatestVersion(dbCommandFactory);
var tableVersion = GetTableVersion(dbCommandFactory);
if (tableVersion == 2)
{
using (var command = GetInsertScriptCommandV2(dbCommandFactory, (SqlScript)script))
{
command.ExecuteNonQuery();
}
}
else if (tableVersion == 1)
{
using (var command = GetInsertScriptCommand(dbCommandFactory, script))
{
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// Records a database upgrade for a database specified in a given connection string.
/// </summary>
/// <param name="script">The script.</param>
public void StoreExecutedScript(SqlScript script)
{
var exists = DoesTableExist();
if (!exists)
{
log.WriteInformation(string.Format("Creating the {0} table", schemaTableName));
RunCommand(
string.Format(
@"create table {0} (
[SchemaVersionId] int identity(1,1) not null constraint PK_SchemaVersions_Id primary key nonclustered ,
[VersionNumber] int null,
[SourceIdentifier] nvarchar(255) not null,
[ScriptName] nvarchar(255) not null,
[Applied] datetime not null
)", schemaTableName),
cmd => cmd.ExecuteNonQuery());
log.WriteInformation(string.Format("The {0} table has been created", schemaTableName));
}
DealWithLegacyScripts();
RunCommand(
string.Format("insert into {0} (VersionNumber, SourceIdentifier, ScriptName, Applied) values (-1, @sourceIdentifier, @scriptName, @now)", schemaTableName),
cmd =>
{
var scriptName = cmd.CreateParameter();
scriptName.ParameterName = "scriptName";
scriptName.Value = script.Name;
cmd.Parameters.Add(scriptName);
var sourceIdentifierParameter = cmd.CreateParameter();
sourceIdentifierParameter.ParameterName = "sourceIdentifier";
sourceIdentifierParameter.Value = sourceIdentifier;
cmd.Parameters.Add(sourceIdentifierParameter);
var nowParameter = cmd.CreateParameter();
nowParameter.ParameterName = "now";
nowParameter.Value = DateTime.UtcNow;
cmd.Parameters.Add(nowParameter);
cmd.ExecuteNonQuery();
});
}
/// <summary>
/// Records a database upgrade for a database specified in a given connection string.
/// </summary>
/// <param name="script">The script.</param>
public void StoreExecutedScript(SqlScript script)
{
var exists = DoesTableExist();
if (!exists)
{
log.WriteInformation(string.Format("Creating the {0} table", schemaTableName));
using (var connection = connectionFactory())
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(
@"create table {0} (
[Id] int identity(1,1) not null constraint PK_SchemaVersions_Id primary key,
[ScriptName] nvarchar(255) not null,
[Applied] datetime not null
)", schemaTableName);
command.CommandType = CommandType.Text;
connection.Open();
command.ExecuteNonQuery();
}
log.WriteInformation(string.Format("The {0} table has been created", schemaTableName));
}
using (var connection = connectionFactory())
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format("insert into {0} (ScriptName, Applied) values (@scriptName, '{1}')", schemaTableName, DateTime.UtcNow.ToString("s"));
var param = command.CreateParameter();
param.ParameterName = "scriptName";
param.Value = script.Name;
command.Parameters.Add(param);
command.CommandType = CommandType.Text;
connection.Open();
command.ExecuteNonQuery();
}
}
public ScriptExecutedEventArgs(SqlScript script, IConnectionManager connectionManager)
{
Script = script;
ConnectionManager = connectionManager;
}
/// <summary>
/// Adds a single static script to the upgrader.
/// </summary>
/// <param name="builder">The builder.</param>
/// <param name="name">The name of the script. This should never change once executed.</param>
/// <param name="contents">The script body.</param>
/// <returns>
/// The same builder
/// </returns>
public static UpgradeEngineBuilder WithScript(this UpgradeEngineBuilder builder, string name, string contents)
{
var script = new SqlScript(name, contents);
return WithScripts(builder, script);
}
/// <summary>
/// Executes the specified script against a database at a given connection string.
/// </summary>
/// <param name="script">The script.</param>
public void Execute(SqlScript script)
{
Execute(script, null);
}
private TKey SortFunc <TKey>(SqlScript arg)
{
throw new NotImplementedException();
}
/// <summary>
/// Adds a single static script to the upgrader.
/// </summary>
/// <param name="builder">The builder.</param>
/// <param name="script">The script.</param>
/// <returns>
/// The same builder
/// </returns>
public static UpgradeEngineBuilder WithScript(this UpgradeEngineBuilder builder, SqlScript script)
{
return WithScripts(builder, script);
}
/// <summary>
/// Does not store the script, simply returns
/// </summary>
/// <param name="script"></param>
public void StoreExecutedScript(SqlScript script)
{
}
protected virtual string RunPreProcessors(SqlScript script, IDictionary<string, string> variables, IEnumerable<IScriptPreprocessor> scriptPreprocessors)
{
string contents = script.Contents;
//do variable replacement if necessary
contents = new VariableSubstitutionPreprocessor(variables).Process(contents);
//run all the script pre processors
contents = scriptPreprocessors.Aggregate(contents, (current, additionalScriptPreprocessor) => additionalScriptPreprocessor.Process(current));
return contents;
}
/// <summary>
/// Executes the specified script against a database at a given connection string.
/// </summary>
/// <param name="script">The script.</param>
/// <param name="variables">Variables to replace in the script</param>
public void Execute(SqlScript script, IDictionary<string, string> variables)
{
if (variables == null)
variables = new Dictionary<string, string>();
if (Schema != null && !variables.ContainsKey("schema"))
variables.Add("schema", SqlObjectParser.QuoteSqlObjectName(Schema));
log().WriteInformation("Executing SQL Server script '{0}'", script.Name);
var contents = script.Contents;
if (string.IsNullOrEmpty(Schema))
contents = new StripSchemaPreprocessor().Process(contents);
if (variablesEnabled())
contents = new VariableSubstitutionPreprocessor(variables).Process(contents);
contents = (scriptPreprocessors??new IScriptPreprocessor[0])
.Aggregate(contents, (current, additionalScriptPreprocessor) => additionalScriptPreprocessor.Process(current));
var connectionManager = connectionManagerFactory();
var scriptStatements = connectionManager.SplitScriptIntoCommands(contents);
var index = -1;
try
{
connectionManager.ExecuteCommandsWithManagedConnection(dbCommandFactory =>
{
foreach (var statement in scriptStatements)
{
index++;
using (var command = dbCommandFactory())
{
command.CommandText = statement;
if (ExecutionTimeoutSeconds != null)
command.CommandTimeout = ExecutionTimeoutSeconds.Value;
if (connectionManager.IsScriptOutputLogged)
{
using (var reader = command.ExecuteReader())
{
Log(reader);
}
}
else
{
command.ExecuteNonQuery();
}
}
}
});
}
catch (SqlException sqlException)
{
log().WriteInformation("SQL exception has occured in script: '{0}'", script.Name);
log().WriteError("Script block number: {0}; Block line {1}; Message: {2}", index, sqlException.LineNumber, sqlException.Procedure, sqlException.Number, sqlException.Message);
log().WriteError(sqlException.ToString());
throw;
}
catch (DbException sqlException)
{
log().WriteInformation("DB exception has occured in script: '{0}'", script.Name);
log().WriteError("Script block number: {0}; Error code {1}; Message: {2}", index, sqlException.ErrorCode, sqlException.Message);
log().WriteError(sqlException.ToString());
throw;
}
catch (Exception ex)
{
log().WriteInformation("Exception has occured in script: '{0}'", script.Name);
log().WriteError(ex.ToString());
throw;
}
}
/// <summary>
/// Records a database upgrade for a database specified in a given connection string.
/// </summary>
/// <param name="script">The script.</param>
public void StoreExecutedScript(SqlScript script)
{
var exists = DoesTableExist();
if (!exists)
{
log.WriteInformation(string.Format("Creating the {0} table", schemaTableName));
using (var connection = connectionFactory())
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(
@"create table `{0}` (
`Id` int NOT NULL AUTO_INCREMENT,
`ScriptName` nvarchar(255) not null,
`Applied` datetime not null,
PRIMARY KEY (Id)
)", schemaTableName);
command.CommandType = CommandType.Text;
connection.Open();
command.ExecuteNonQuery();
}
log.WriteInformation(string.Format("The {0} table has been created", schemaTableName));
}
using (var connection = connectionFactory())
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format("insert into {0} (ScriptName, Applied) values (@scriptName, '{1}')", schemaTableName, DateTime.UtcNow.ToString("yyyy-MM-dd hh:mm:ss"));
var param = command.CreateParameter();
param.ParameterName = "scriptName";
param.Value = script.Name;
command.Parameters.Add(param);
command.CommandType = CommandType.Text;
connection.Open();
command.ExecuteNonQuery();
}
}
protected virtual DbVersion GetDbVersionFromScript(SqlScript script)
{
//get the version numbers (search at end of string)
Match match = Regex.Match(script.Name, VERSION_REGEX, RegexOptions.RightToLeft);
short major = short.Parse(match.Groups["major"].Value);
short minor = short.Parse(match.Groups["minor"].Value);
short build = short.Parse(match.Groups["build"].Value);
short revision = short.Parse(match.Groups["revision"].Value);
//match on all the comments and build up a string
StringBuilder sb = new StringBuilder();
Regex commentRegex = new Regex(@"<VersionComment>(?<comment>.*)</VersionComment>");
MatchCollection commentMatches = commentRegex.Matches(script.Contents);
foreach (Match m in commentMatches)
sb.Append(m.Groups["comment"].Value + "\r\n");
//i don't want to store blank strings. Turn them null if needed
string comments = sb.ToString();
if (string.IsNullOrWhiteSpace(comments))
comments = null;
DbVersion version = new DbVersion(major, minor, build, revision, Environment.UserName, comments);
return version;
}
/// <summary>
/// Records a database upgrade for a database specified in a given connection string.
/// </summary>
/// <param name="script">The script.</param>
public void UpdateVersion(SqlScript executedScript, Func<IDbConnection> connectionFactory, IUpgradeLog log)
{
if (executedScript.IsAdminScript)
return;
DbVersion version = this.GetDbVersionFromScript(executedScript);
using (IDbConnection connection = connectionFactory())
{
using (IDbCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "InsertDbVersion";
command.AddParameterValue("@major", version.Major);
command.AddParameterValue("@minor", version.Minor);
command.AddParameterValue("@revision", version.Revision);
command.AddParameterValue("@build", version.Build);
command.AddParameterValue("@username", version.Username);
command.AddParameterValue("@comments", version.Comments);
connection.Open();
command.ExecuteNonQuery();
}
}
}
internal ExecutedScriptEventArgs(SqlScript script, IConnectionManager connectionManager)
{
scriptName = script.Name;
scriptContents = script.Contents;
this.connectionManager = connectionManager;
}
/// <summary>
/// Executes the specified script against a database at a given connection string.
/// </summary>
/// <param name="script">The script.</param>
/// <param name="variables">Variables to replace in the script</param>
public virtual void Execute(SqlScript script, string connectionString, IUpgradeLog log, IDictionary<string, string> variables = null, IEnumerable<IScriptPreprocessor> scriptPreprocessors = null)
{
log.WriteInformation("Executing SQL Server script '{0}'", script.Name);
if (script.IsAdminScript)
{
//need the databaseName variable for the add/drop scripts
variables["databaseName"] = this.GetDatabaseName(connectionString);
//now set the connection string to point to the master database
connectionString = this.GetMasterConnectionString(connectionString);
}
//run all the pre processors/variable replacements
string scriptContents = this.RunPreProcessors(script, variables, scriptPreprocessors);
//figure out if we should use transactions or not
bool useTransactions = true;
if (scriptContents.Contains("<DisableTransaction>true</DisableTransaction>")) useTransactions = false;
//break the script into its parts
IEnumerable<string> scriptStatements = this.SplitByGoStatements(scriptContents);
//this is used for logging which block is being executed
int currentBlockNumber = -1;
//open the connection and execute
try
{
using (IDbConnection connection = new SqlConnection(connectionString))
{
connection.Open();
//use a transaction if we're supposed to
if (useTransactions)
{
using (IDbTransaction transaction = connection.BeginTransaction())
{
this.ExecuteScriptStatements(scriptStatements, connection, out currentBlockNumber, transaction);
transaction.Commit();
}
}
else
{
ExecuteScriptStatements(scriptStatements, connection, out currentBlockNumber);
}
}
}
catch (SqlException sqlException)
{
log.WriteInformation("SQL exception has occured in script: '{0}'", script.Name);
log.WriteError("Script block number: {0}; Block line {1}; Message: {2}", currentBlockNumber, sqlException.LineNumber, sqlException.Procedure, sqlException.Number, sqlException.Message);
log.WriteError(sqlException.ToString());
throw;
}
catch (DbException dbException)
{
log.WriteInformation("DB exception has occured in script: '{0}'", script.Name);
log.WriteError("Script block number: {0}; Error code {1}; Message: {2}", currentBlockNumber, dbException.ErrorCode, dbException.Message);
log.WriteError(dbException.ToString());
throw;
}
catch (Exception ex)
{
log.WriteInformation("Exception has occured in script: '{0}'", script.Name);
log.WriteError(ex.ToString());
throw;
}
}
/// <summary>
/// Initializes a new instance of the <see cref="DatabaseUpgradeResult"/> class.
/// </summary>
/// <param name="scripts">The scripts that were executed.</param>
/// <param name="successful">if set to <c>true</c> [successful].</param>
/// <param name="error">The error.</param>
public DatabaseUpgradeResult(IEnumerable <SqlScript> scripts, bool successful, Exception error, SqlScript failedScript)
{
this.failedScript = failedScript;
this.scripts = new List <SqlScript>();
this.scripts.AddRange(scripts);
this.successful = successful;
this.error = error;
}
