private void ChangeCreateIndexOperationalProps(DeploymentPlanContributorContext context,
IList <IndexOption> options)
{
DeploymentStep nextStep = context.PlanHandle.Head;
// Loop through all steps in the deployment plan
bool foundMainSection = false;
while (nextStep != null)
{
DeploymentStep currentStep = nextStep;
nextStep = currentStep.Next;
// We only want to analyze the main part of the deployment script - we'll skip
// any steps until we pass the end of the predeployment section, and stop once
// we hit the start of the postdeployment section
if (currentStep is EndPreDeploymentScriptStep)
{
foundMainSection = true;
continue;
}
if (!foundMainSection)
{
// Haven't gotten past predeployment yet
continue;
}
if (currentStep is BeginPostDeploymentScriptStep)
{
break;
}
// We need to care about CreateElementSteps and AlterElementSteps for Indexes.
DeploymentScriptDomStep domStep = currentStep as DeploymentScriptDomStep;
TSqlObject elementObject = null;
if (domStep is CreateElementStep)
{
elementObject = ((CreateElementStep)domStep).SourceElement;
}
else if (domStep is AlterElementStep)
{
elementObject = ((AlterElementStep)domStep).SourceElement;
}
if (elementObject != null)
{
if (Index.TypeClass.Equals(elementObject.ObjectType) && !(View.TypeClass.Equals(elementObject.GetParent().ObjectType)))
{
TSqlFragment fragment = domStep.Script;
IndexStatementVisitor visitor = new IndexStatementVisitor(options);
fragment.Accept(visitor);
}
}
}
}
private void FindAndRenameUnnamedDefaultConstraints(DeploymentPlanContributorContext context)
{
DeploymentStep nextStep = context.PlanHandle.Head;
// Loop through all steps in the deployment plan
bool foundMainSection = false;
while (nextStep != null)
{
DeploymentStep currentStep = nextStep;
nextStep = currentStep.Next;
// We only want to analyze the main part of the deployment script - we'll skip
// any steps until we pass the end of the predeployment section, and stop once
// we hit the start of the postdeployment section
if (currentStep is EndPreDeploymentScriptStep)
{
foundMainSection = true;
continue;
}
if (!foundMainSection)
{
// Haven't gotten past predeployment yet
continue;
}
if (currentStep is BeginPostDeploymentScriptStep)
{
break;
}
// We need to care about CreateElementSteps and AlterElementSteps for default constraints.
DeploymentScriptDomStep domStep = currentStep as DeploymentScriptDomStep;
TSqlObject elementObject = null;
// most of the default constraints in the deployment plan seem to be deployed as Alter Table statements, but
// just in case the default constraint was deployed as part of a Create Table (don't see how it is possible, but just being safe)
if (domStep is CreateElementStep)
{
elementObject = ((CreateElementStep)domStep).SourceElement;
}
else if (domStep is AlterElementStep)
{
elementObject = ((AlterElementStep)domStep).SourceElement;
}
if (elementObject != null)
{
TSqlFragment fragment = domStep.Script;
// call the visitor, which in turn will auto-name these constraints
var visitor = new DefaultConstraintDefinitionVisitor();
fragment.Accept(visitor);
}
}
}
private void FindAndMakeAlterColumnsOnline(DeploymentPlanContributorContext context)
{
DeploymentStep nextStep = context.PlanHandle.Head;
// Loop through all steps in the deployment plan
bool foundMainSection = false;
while (nextStep != null)
{
DeploymentStep currentStep = nextStep;
nextStep = currentStep.Next;
// We only want to analyze the main part of the deployment script - we'll skip
// any steps until we pass the end of the predeployment section, and stop once
// we hit the start of the postdeployment section
if (currentStep is EndPreDeploymentScriptStep)
{
foundMainSection = true;
continue;
}
if (!foundMainSection)
{
// Haven't gotten past predeployment yet
continue;
}
if (currentStep is BeginPostDeploymentScriptStep)
{
break;
}
// We need to care about AlterElementSteps.
DeploymentScriptDomStep domStep = currentStep as DeploymentScriptDomStep;
TSqlObject elementObject = null;
if (domStep is AlterElementStep)
{
elementObject = ((AlterElementStep)domStep).SourceElement;
}
if (elementObject != null)
{
TSqlFragment fragment = domStep.Script;
// call the visitor, which in turn will auto-name these constraints
var visitor = new AlterTableAlterColumnVisitor();
fragment.Accept(visitor);
}
}
}
public void logger(DeploymentStep ds, int i)
{
string msg = "";
if (ds is CreateElementStep || ds is DropElementStep)
{
DeploymentScriptDomStep domStep = ds as DeploymentScriptDomStep;
TSqlScript script = domStep.Script as TSqlScript;
TSqlStatement t = script.Batches[0].Statements[0];
msg = $"{DateTime.Now.ToLongTimeString()}, {i:000} - {ds.GetType().Name}: {t.GetType().Name}";
}
else
{
msg = $"{DateTime.Now.ToLongTimeString()}, {i:000} - {ds.GetType().Name}: - ";
}
using (StreamWriter w = File.AppendText(logfilepath)) { w.WriteLine(msg); }
}
/// <summary>
/// Helper method that generates a useful description of the step.
/// </summary>
private static void GetStepInfo(
DeploymentScriptDomStep domStep,
out string stepDescription,
out TSqlObject element)
{
element = null;
// figure out what type of step we've got, and retrieve
// either the source or target element.
if (domStep is CreateElementStep)
{
element = ((CreateElementStep)domStep).SourceElement;
}
else if (domStep is AlterElementStep)
{
element = ((AlterElementStep)domStep).SourceElement;
}
else if (domStep is DropElementStep)
{
element = ((DropElementStep)domStep).TargetElement;
}
// construct the step description by concatenating the type and the fully qualified
// name of the associated element.
string stepTypeName = domStep.GetType().Name;
if (element != null)
{
string elementName = GetElementName(element);
stepDescription = string.Format(CultureInfo.InvariantCulture, "{0} {1}",
stepTypeName, elementName);
}
else
{
// if the step has no associated element, just use the step type as the description
stepDescription = stepTypeName;
}
}
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);
}
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;
// }
// }
//}
}
}
