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