Deployment Contributor for renaming of columns in SQL Server Temporal Tables

SqlTemporalColumnRenameContributor.cs

/*! *****************************************************************************
Copyright (c) 2019 Tangra Inc.
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
    http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
***************************************************************************** */

using Microsoft.SqlServer.Dac.Deployment;
using Microsoft.SqlServer.Dac.Model;
using Microsoft.SqlServer.TransactSql.ScriptDom;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Tangra.SqlServer.Dac
{
    /// <summary>  
    /// This deployment contributor modifies a deployment plan by adding statements before and after
    /// column rename steps to remove and re-create the SYSTEM_VERSIONING settings of a temporal table
    /// </summary>  
    [ExportDeploymentPlanModifier("Tangra.SqlTemporalColumnRenameContributor", "1.0.0.0")]
    public class SqlTemporalColumnRenameContributor : DeploymentPlanModifier
    {
        protected override void OnExecute(DeploymentPlanContributorContext context)
        {
            DeploymentStep nextStep = context.PlanHandle.Head;
            var visitedTemporalTables = new List<string>();

            // Loop through all steps in the deployment plan  
            while (nextStep != null)
            {
                // Increment the step pointer, saving both the current and next steps  
                DeploymentStep currentStep = nextStep;
                nextStep = currentStep.Next;

                if (!(currentStep is SqlRenameStep))
                {
                    continue;
                }
                var renameStep = currentStep as SqlRenameStep;

                if (renameStep.RenamedElement.ObjectType.Name != "Column")
                {
                    continue;
                }

                var currentTable = renameStep.RenamedElement.GetParent();
                var originalTable = currentTable.GetReferencing(Table.TemporalSystemVersioningHistoryTable);

                TSqlObject temporalTable;
                if (originalTable.Count() == 0)
                {
                    // Rename in the Temporal Table itself
                    temporalTable = currentTable;
                }
                else
                {
                    // Rename in the related History Table
                    temporalTable = originalTable.Single();
                }

                var temporalTableName = string.Join(".", temporalTable.Name.Parts.ToArray());
                if (visitedTemporalTables.IndexOf(temporalTableName) == -1)
                {
                    // First visit of the table => We need to turn off system versioning for the table
                    var newBatch = new TSqlBatch();
                    newBatch.Statements.Add(GetIfExistsDropTemporalStatement(temporalTable.Name.Parts.ToArray()));
                    (renameStep.Script as TSqlScript).Batches.Insert(0, newBatch);
                    visitedTemporalTables.Add(temporalTableName);
                }
                else
                {
                    // Already visited table => We need to generate a script that turns SYSTEM_VERSIONING to ON
                    var createTemporalDefinitionStatement = GetCreateTemporalStatement(temporalTable);
                    if (createTemporalDefinitionStatement != null)
                    {
                        var newBatch = new TSqlBatch();
                        newBatch.Statements.Add(createTemporalDefinitionStatement);
                        (renameStep.Script as TSqlScript).Batches.Add(newBatch);
                    }
                }
            }
        }

        private SchemaObjectName GetSchemaObjectName(params string[] identifiers)
        {
            var name = new SchemaObjectName();

            foreach (var identifier in identifiers)
            {
                name.Identifiers.Add(new Identifier() { Value = identifier, QuoteType = QuoteType.SquareBracket });
            }

            return name;
        }

        private IfStatement GetIfExistsDropTemporalStatement(string[] tableNameParts)
        {
            return new IfStatement()
            {
                Predicate = new ExistsPredicate()
                {
                    Subquery = new ScalarSubquery()
                    {
                        QueryExpression = new QuerySpecification()
                        {
                            SelectElements =
                            {
                                new SelectScalarExpression() { Expression = new IntegerLiteral() { Value = "1" } }
                            },
                            FromClause = new FromClause()
                            {
                                TableReferences =
                                {
                                    new NamedTableReference() { SchemaObject = GetSchemaObjectName("sys", "tables") }
                                }
                            },
                            WhereClause = new WhereClause()
                            {
                                SearchCondition = new BooleanBinaryExpression()
                                {
                                    BinaryExpressionType = BooleanBinaryExpressionType.And,
                                    FirstExpression = new BooleanBinaryExpression()
                                    {
                                        BinaryExpressionType = BooleanBinaryExpressionType.And,
                                        FirstExpression = new BooleanComparisonExpression()
                                        {
                                            ComparisonType = BooleanComparisonType.Equals,
                                            FirstExpression = new ColumnReferenceExpression()
                                            {
                                                MultiPartIdentifier = new MultiPartIdentifier()
                                                {
                                                    Identifiers = { new Identifier() { Value = "name", QuoteType = QuoteType.SquareBracket } }
                                                }
                                            },
                                            SecondExpression = new StringLiteral() { Value = tableNameParts.Last() }
                                        },
                                        SecondExpression = new BooleanComparisonExpression()
                                        {
                                            ComparisonType = BooleanComparisonType.Equals,
                                            FirstExpression = new ScalarExpressionSnippet()
                                            {
                                                Script = "SCHEMA_NAME([schema_id])"
                                            },
                                            SecondExpression = new StringLiteral() { Value = tableNameParts[tableNameParts.Length - 2] }
                                        }
                                    },
                                    SecondExpression = new BooleanComparisonExpression()
                                    {
                                        ComparisonType = BooleanComparisonType.Equals,
                                        FirstExpression = new ColumnReferenceExpression()
                                        {
                                            MultiPartIdentifier = new MultiPartIdentifier()
                                            {
                                                Identifiers = { new Identifier() { Value = "temporal_type", QuoteType = QuoteType.SquareBracket } }
                                            }
                                        },
                                        SecondExpression = new IntegerLiteral() { Value = "2" }
                                    },
                                }
                            }
                        }
                    }
                },
                ThenStatement = new BeginEndBlockStatement()
                {
                    StatementList = new StatementList()
                    {
                        Statements =
                        {
                            new AlterTableSetStatement()
                            {
                                SchemaObjectName = GetSchemaObjectName(tableNameParts.ToArray()),
                                Options = { new SystemVersioningTableOption() { OptionState = OptionState.Off } }
                            }
                        },
                    }
                }
            };
        }

        private AlterTableSetStatement GetCreateTemporalStatement(TSqlObject table)
        {
            var retentionUnit = table.GetProperty<int>(Table.RetentionUnit);
            var retentionValue = table.GetProperty<int?>(Table.RetentionValue);
            var historyTable = table.GetReferenced(Table.TemporalSystemVersioningHistoryTable);

            if (historyTable.Count() == 0)
            {
                return null;
            }

            RetentionPeriodDefinition retentionPeriod = null;
            if (retentionUnit != -1 && retentionValue != -1)
            {
                retentionPeriod = new RetentionPeriodDefinition()
                {
                    Duration = new IntegerLiteral() { Value = retentionValue.ToString() },
                    Units = (TemporalRetentionPeriodUnit)retentionUnit,
                };
            }

            return new AlterTableSetStatement()
            {
                SchemaObjectName = GetSchemaObjectName(table.Name.Parts.ToArray()),
                Options =
                {
                    new SystemVersioningTableOption()
                    {
                        OptionState = OptionState.On,
                        ConsistencyCheckEnabled = OptionState.On,
                        HistoryTable = GetSchemaObjectName(historyTable.Single().Name.Parts.ToArray()),
                        RetentionPeriod = retentionPeriod
                    }
                }
            };

        }
    }
}