private void ChangeNewDatabaseLocation(DeploymentPlanContributorContext context, string datalocation, string logdatalocation, string filePrefix)
{
DeploymentStep nextStep = context.PlanHandle.Head;
// Loop through all steps in the deployment plan
bool foundSetVars = false;
while (nextStep != null && !foundSetVars)
{
// Increment the step pointer, saving both the current and next steps
DeploymentStep currentStep = nextStep;
// Only interrogate up to BeginPreDeploymentScriptStep - setvars must be done before that
// We know this based on debugging a new deployment and examining the output script
if (currentStep is BeginPreDeploymentScriptStep)
{
break;
}
DeploymentScriptStep scriptStep = currentStep as DeploymentScriptStep;
if (scriptStep != null)
{
IList <string> scripts = scriptStep.GenerateTSQL();
foreach (string script in scripts)
{
if (script.Contains("DefaultDataPath"))
{
// This is the step that sets the default data path and log path.
foundSetVars = true;
// Override setvars before the deployment begins
StringBuilder sb = new StringBuilder();
sb.AppendFormat(":setvar DefaultDataPath \"{0}\"", datalocation)
.AppendLine()
.AppendFormat(":setvar DefaultLogPath \"{0}\"", logdatalocation)
.AppendLine()
.AppendFormat(":setvar DefaultFilePrefix \"{0}\"", filePrefix)
.AppendLine();
// Create a new step for the setvar statements, and add it after the existing step.
// That ensures that the updated values are used instead of the defaults
DeploymentScriptStep setVarsStep = new DeploymentScriptStep(sb.ToString());
this.AddAfter(context.PlanHandle, scriptStep, setVarsStep);
}
}
}
nextStep = currentStep.Next;
}
}
/// <summary>
/// Iterates over the deployment plan to find the definition for
/// </summary>
/// <param name="context"></param>
protected override void OnExecute(DeploymentPlanContributorContext context)
{
DeploymentStep next = context.PlanHandle.Head;
bool foundDropDb = false;
while (next != null)
{
DeploymentStep current = next;
next = current.Next;
//works!
if (foundDropDb)
{
base.Remove(context.PlanHandle, current);
}
DeploymentScriptStep scriptStep = current as DeploymentScriptStep;
if (scriptStep != null)
{
IList <string> scripts = scriptStep.GenerateTSQL();
foreach (string script in scripts)
{
if (script.Contains("DROP DATABASE"))
{
// This is the step that removes the drop database step
foundDropDb = true;
}
}
}
}
// Override setvars before the deployment begins
StringBuilder sb = new StringBuilder();
sb.AppendFormat("PRINT N'Update complete.';")
.AppendLine()
.AppendFormat("GO")
.AppendLine();
// Create a new step for the setvar statements, and add it after the existing step.
// That ensures that the updated values are used instead of the defaults
DeploymentScriptStep setVarsStep = new DeploymentScriptStep(sb.ToString());
this.AddAfter(context.PlanHandle, context.PlanHandle.Tail, setVarsStep);
}
private void RemoveDropDatabaseStep(DeploymentPlanContributorContext context)
{
DeploymentStep nextStep = context.PlanHandle.Head;
// Loop through all steps in the deployment plan
bool foundDropDb = false;
while (nextStep != null && !foundDropDb)
{
// Increment the step pointer, saving both the current and next steps
DeploymentStep currentStep = nextStep;
// Only interrogate up to BeginPreDeploymentScriptStep - setvars must be done before that
// We know this based on debugging a new deployment and examining the output script
if (currentStep is BeginPreDeploymentScriptStep)
{
break;
}
DeploymentScriptStep scriptStep = currentStep as DeploymentScriptStep;
if (scriptStep != null)
{
IList <string> scripts = scriptStep.GenerateTSQL();
foreach (string script in scripts)
{
if (script.Contains("DROP DATABASE"))
{
// This is the step that removes the drop database step
foundDropDb = true;
// Remove the current step
this.Remove(context.PlanHandle, currentStep);
}
}
}
nextStep = currentStep.Next;
}
}
private void ChangeNewDatabaseLocation(DeploymentPlanContributorContext context, string databaseName, string dataLocation, string logLocation)
{
DeploymentStep nextStep = context.PlanHandle.Head;
// Loop through all steps in the deployment plan
bool foundDefaultDataPath = false;
bool foundCreateDb = false;
while (nextStep != null)
{
// Increment the step pointer, saving both the current and next steps
DeploymentStep currentStep = nextStep;
// Only interrogate up to BeginPreDeploymentScriptStep - setvars must be done before that
// We know this based on debugging a new deployment and examining the output script
if (currentStep is BeginPreDeploymentScriptStep)
{
break;
}
if (currentStep is DeploymentScriptStep defaultDataPathStep && !foundDefaultDataPath)
{
IList <string> scripts = defaultDataPathStep.GenerateTSQL();
foreach (string script in scripts)
{
if (script.Contains("DefaultDataPath"))
{
// This is the step that sets the default data path and log path.
foundDefaultDataPath = true;
//// Override setvars before the deployment begins
///
var regex = new Regex(@"^.*\DatabaseName\W.*$", RegexOptions.Multiline);
string result = regex.Replace(script, string.Format(":setvar DatabaseName \"{0}\"", databaseName));
if (!string.IsNullOrEmpty(dataLocation))
{
regex = new Regex(@"^.*\DefaultDataPath\W.*$", RegexOptions.Multiline);
result = regex.Replace(result, string.Format(":setvar DefaultDataPath \"{0}\"", dataLocation));
}
if (!string.IsNullOrEmpty(logLocation))
{
regex = new Regex(@"^.*\DefaultLogPath\W.*$", RegexOptions.Multiline);
result = regex.Replace(result, string.Format(":setvar DefaultLogPath \"{0}\"", logLocation));
}
// Create a new step for the setvar statements, and add it after the existing step.
// That ensures that the updated values are used instead of the defaults
DeploymentScriptStep newDefaultDataPathStep = new DeploymentScriptStep(result);
this.AddAfter(context.PlanHandle, defaultDataPathStep, newDefaultDataPathStep);
// Remove the current step
this.Remove(context.PlanHandle, defaultDataPathStep);
nextStep = newDefaultDataPathStep.Next;
break;
}
}
}
if (currentStep is SqlCreateDatabaseStep createDbStep && !foundCreateDb)
{
IList <string> scripts = createDbStep.GenerateTSQL();
foreach (string script in scripts)
{
if (script.Contains("CREATE DATABASE"))
{
foundCreateDb = true;
//create the data files folder
var createDataFoldersScript = CreateDataFolders();
DeploymentScriptStep createDataFoldersDbStep = new DeploymentScriptStep(createDataFoldersScript);
this.AddAfter(context.PlanHandle, createDbStep, createDataFoldersDbStep);
//remove the collation
var createDbScript = CleanCollation(script);
//add data files
createDbScript = AddDataFiles(createDbScript);
// Create a new step for the create database script, and add it after the existing step.
// That ensures that the updated values are used instead of the defaults
DeploymentScriptStep newCreateDbStep = new DeploymentScriptStep(createDbScript);
// add script blocks to the plan handle
this.AddAfter(context.PlanHandle, createDataFoldersDbStep, newCreateDbStep);
// Remove the current step
this.Remove(context.PlanHandle, createDbStep);
nextStep = newCreateDbStep.Next;
break;
}
}
}
if (currentStep is DeploymentScriptDomStep domStep)
{
//var removedAlterDb = false;
IList <string> scripts = domStep.GenerateTSQL();
foreach (string script in scripts)
{
if (script.Contains("ALTER DATABASE"))
{
//removedAlterDb = true;
nextStep = domStep.Next;
// Remove the alter db step
this.Remove(context.PlanHandle, domStep);
break;
}
}
}
nextStep = currentStep.Next;
}
}
protected override void OnExecute(DeploymentPlanContributorContext context)
{
// Obtain the first step in the Plan from the provided context
int batchId = 0;
DeploymentStep nextStep = context.PlanHandle.Head;
BeginPreDeploymentScriptStep beforePreDeploy = null;
while (nextStep != null)
{
DeploymentStep currentStep = nextStep;
nextStep = currentStep.Next;
if (currentStep is BeginPreDeploymentScriptStep)
{
beforePreDeploy = (BeginPreDeploymentScriptStep)currentStep;
continue;
}
if (currentStep is SqlPrintStep)
{
continue;
}
if (currentStep is BeginPostDeploymentScriptStep)
{
break;
}
if (beforePreDeploy == null)
{
continue;
}
DeploymentScriptDomStep domStep = currentStep as DeploymentScriptDomStep;
if (domStep == null)
{
continue;
}
TSqlScript script = domStep.Script as TSqlScript;
if (script == null)
{
continue;
}
// Loop through all the batches in the script for this step. All the statements
// in the batch will be enclosed in an if statement that will check the
// table to ensure that the batch has not already been executed
TSqlObject sqlObject;
string stepDescription;
GetStepInfo(domStep, out stepDescription, out sqlObject);
int batchCount = script.Batches.Count;
for (int batchIndex = 0; batchIndex < batchCount; batchIndex++)
{
// Create the if statement that will contain the batch's contents
IfStatement ifBatchNotExecutedStatement = CreateIfNotExecutedStatement(batchId);
BeginEndBlockStatement statementBlock = new BeginEndBlockStatement();
ifBatchNotExecutedStatement.ThenStatement = statementBlock;
statementBlock.StatementList = new StatementList();
TSqlBatch batch = script.Batches[batchIndex];
int statementCount = batch.Statements.Count;
// Loop through all statements in the batch, embedding those in an sp_execsql
// statement that must be handled this way (schemas, stored procedures,
// views, functions, and triggers).
for (int statementIndex = 0; statementIndex < statementCount; statementIndex++)
{
TSqlStatement smnt = batch.Statements[statementIndex];
if (IsStatementEscaped(sqlObject))
{
// "escape" this statement by embedding it in a sp_executesql statement
string statementScript;
domStep.ScriptGenerator.GenerateScript(smnt, out statementScript);
ExecuteStatement spExecuteSql = CreateExecuteSql(statementScript);
smnt = spExecuteSql;
}
statementBlock.StatementList.Statements.Add(smnt);
}
// Add an insert statement to track that all the statements in this
// batch were executed. Turn on nocount to improve performance by
// avoiding row inserted messages from the server
string batchDescription = string.Format(CultureInfo.InvariantCulture,
"{0} batch {1}", stepDescription, batchIndex);
PredicateSetStatement noCountOff = new PredicateSetStatement();
noCountOff.IsOn = false;
noCountOff.Options = SetOptions.NoCount;
PredicateSetStatement noCountOn = new PredicateSetStatement();
noCountOn.IsOn = true;
noCountOn.Options = SetOptions.NoCount;
InsertStatement batchCompleteInsert = CreateBatchCompleteInsert(batchId, batchDescription);
statementBlock.StatementList.Statements.Add(noCountOn);
statementBlock.StatementList.Statements.Add(batchCompleteInsert);
statementBlock.StatementList.Statements.Add(noCountOff);
// Remove all the statements from the batch (they are now in the if block) and add the if statement
// as the sole statement in the batch
batch.Statements.Clear();
batch.Statements.Add(ifBatchNotExecutedStatement);
// Next batch
batchId++;
}
}
// if we found steps that required processing, set up a temporary table to track the work that you are doing
if (beforePreDeploy != null)
{
// Declare a SqlCmd variables.
//
// CompletedBatches variable - defines the name of the table in tempdb that will track
// all the completed batches. The temporary table's name has the target database name and
// a guid embedded in it so that:
// * Multiple deployment scripts targeting different DBs on the same server
// * Failed deployments with old tables do not conflict with more recent deployments
//
// TotalBatchCount variable - the total number of batches surrounded by if statements. Using this
// variable pre/post deployment scripts can also use the CompletedBatches table to make their
// script rerunnable if there is an error during execution
StringBuilder sqlcmdVars = new StringBuilder();
sqlcmdVars.AppendFormat(CultureInfo.InvariantCulture, CompletedBatchesSqlCmd,
context.Options.TargetDatabaseName, Guid.NewGuid().ToString("D"));
sqlcmdVars.AppendLine();
sqlcmdVars.AppendFormat(CultureInfo.InvariantCulture, TotalBatchCountSqlCmd, batchId);
DeploymentScriptStep completedBatchesSetVarStep = new DeploymentScriptStep(sqlcmdVars.ToString());
base.AddBefore(context.PlanHandle, beforePreDeploy, completedBatchesSetVarStep);
// Create the temporary table we will use to track the work that we are doing
DeploymentScriptStep createStatusTableStep = new DeploymentScriptStep(CreateCompletedBatchesTable);
base.AddBefore(context.PlanHandle, beforePreDeploy, createStatusTableStep);
}
// Cleanup and drop the table
// DeploymentScriptStep dropStep = new DeploymentScriptStep(DropCompletedBatchesTable);
// base.AddAfter(context.PlanHandle, context.PlanHandle.Tail, dropStep);
}
private void ChangeNewDatabaseLocation(DeploymentPlanContributorContext context, string location, string filePrefix)
{
DeploymentStep nextStep = context.PlanHandle.Head;
// Loop through all steps in the deployment plan
bool foundSetVars = false;
while (nextStep != null && !foundSetVars)
{
// Increment the step pointer, saving both the current and next steps
DeploymentStep currentStep = nextStep;
// Only interrogate up to BeginPreDeploymentScriptStep - setvars must be done before that
// We know this based on debugging a new deployment and examining the output script
if (currentStep is BeginPreDeploymentScriptStep)
{
break;
}
DeploymentScriptStep scriptStep = currentStep as DeploymentScriptStep;
if (scriptStep != null)
{
IList<string> scripts = scriptStep.GenerateTSQL();
foreach (string script in scripts)
{
if (script.Contains("DefaultDataPath"))
{
// This is the step that sets the default data path and log path.
foundSetVars = true;
// Override setvars before the deployment begins
StringBuilder sb = new StringBuilder();
sb.AppendFormat(":setvar DefaultDataPath \"{0}\"", location)
.AppendLine()
.AppendFormat(":setvar DefaultLogPath \"{0}\"", location)
.AppendLine()
.AppendFormat(":setvar DefaultFilePrefix \"{0}\"", filePrefix)
.AppendLine();
// Create a new step for the setvar statements, and add it after the existing step.
// That ensures that the updated values are used instead of the defaults
DeploymentScriptStep setVarsStep = new DeploymentScriptStep(sb.ToString());
this.AddAfter(context.PlanHandle, scriptStep, setVarsStep);
}
}
}
nextStep = currentStep.Next;
}
}
protected override void OnExecute(DeploymentPlanContributorContext context)
{
DeploymentStep nextStep = context.PlanHandle.Head;
while (nextStep != null)
{
DeploymentStep currentStep = nextStep;
nextStep = currentStep.Next;
//Debug.WriteLine($"{currentStep.GetType()}");
if (currentStep is DeploymentScriptStep)
{
DeploymentScriptStep d = currentStep as DeploymentScriptStep;
Regex rx = new Regex(@"\[sandbox\]"); //[\n\r]*is being dropped. Deployment will halt if the table contains data.");
if (rx.IsMatch(d.GenerateTSQL()[0]))
{
base.Remove(context.PlanHandle, currentStep);
continue;
}
}
if (currentStep is CreateElementStep)
{
DeploymentScriptDomStep domStep = currentStep as DeploymentScriptDomStep;
TSqlScript script = domStep.Script as TSqlScript;
TSqlStatement t = script.Batches[0].Statements[0];
if (t is CreateTableStatement o)
{
SchemaObjectName ol = o.SchemaObjectName;
string ol1 = ol.SchemaIdentifier.Value;
if (ol1 == "sandbox" || ol1 == "unittests")
{
base.Remove(context.PlanHandle, currentStep);
continue;
}
}
// Sql140ScriptGenerator s = new Sql140ScriptGenerator();
// s.GenerateScript(t, out string ts);
// Debug.WriteLine($"{t.GetType()}: {ts}");
// DropChildObjectsStatement
// DropStatisticsStatement
}
if (currentStep is DropElementStep)
{
DeploymentScriptDomStep domStep = currentStep as DeploymentScriptDomStep;
TSqlScript script = domStep.Script as TSqlScript;
TSqlStatement t = script.Batches[0].Statements[0];
//Debug.WriteLine($"{currentStep.GetType()}: {t.GetType()}");
if (t is DropStatisticsStatement)
{
base.Remove(context.PlanHandle, currentStep);
continue;
}
if (t is DropObjectsStatement)
{
DropObjectsStatement o = (DropObjectsStatement)t;
IList <SchemaObjectName> ol = o.Objects;
string ol1 = ol[0].SchemaIdentifier.Value;
if (ol1 == "sandbox" || ol1 == "unittests")
{
base.Remove(context.PlanHandle, currentStep);
continue;
}
}
// Sql140ScriptGenerator s = new Sql140ScriptGenerator();
// s.GenerateScript(t, out string ts);
// Debug.WriteLine($"{t.GetType()}: {ts}");
// DropChildObjectsStatement
// DropStatisticsStatement
}
//if (currentStep is AlterElementStep)
//{
// DeploymentScriptDomStep domStep = currentStep as DeploymentScriptDomStep;
// TSqlScript script = domStep.Script as TSqlScript;
// TSqlStatement t = script.Batches[0].Statements[0];
// if (t is AlterProcedureStatement)
// {
// AlterProcedureStatement o = (AlterProcedureStatement)t;
// SchemaObjectName ol = o.Options.sch
// string ol1 = ol.SchemaIdentifier.Value;
// if (ol1 == "sandbox" || ol1 == "unittests")
// {
// base.Remove(context.PlanHandle, currentStep);
// continue;
// }
// }
//}
}
}