/// <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; }