.sqlproj文件的版本控制

Question

对于项目中存在的每个对象，我们的.sqlproj包含许多类似于此的语句：

<Build Include="MySchema\Tables\TableA" />
<Build Include="MySchema\Tables\TableB" />
<Build Include="MySchema\Tables\TableC" />

每当将对象添加到项目中时，SSDT将通过在文件的任意行中添加记录来自动更新sqlproj文件。当多个开发人员在同一个项目上工作时，这会导致大量合并问题。

我试图通过将通配符添加到所有模式文件夹中来修改该文件，因此前一个文件将变成：

<Build Include="MySchema\**" />

但是，如果我在相同的模式中创建TableD，即使它包含在前面的语句中，它仍然会为该对象添加一个记录。所以我的.sqlproj应该是这样的：

<Build Include="MySchema\**" />
<Build Include="MySchema\Tables\TableD" />

有什么办法能解决这个问题吗？

Answer 1

合并SSDT sqlproj项目文件只是一个痛苦。我们已经创建了MSBuild目标文件，它只是在每次构建项目时对项目文件进行排序。这样做的缺点是，当对sqlproj文件进行排序时，Visual会对其进行外部修改，并希望刷新项目。无论如何，与合并地狱相比，这没什么大不了的。

因此，在项目文件夹中我们有build_VS2017.targets文件(如果您想使用not 2017版本，可能需要对它进行调整，至少在我们从2015年迁移到2017年时，我做了一些事情)：

<Project ToolsVersion="15.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <!-- This simple inline task displays "Hello, world!" -->
  <UsingTask
    TaskName="ReorderSqlProjFile_Inline"
    TaskFactory="CodeTaskFactory"
    AssemblyFile="$(MSBuildToolsPath)\Microsoft.Build.Tasks.Core.dll" >
    <ParameterGroup />
    <Task>
      <Reference Include="System.Xml"/>
      <Reference Include="System.Core"/>
      <Reference Include="System.Xml.Linq"/>
      <Using Namespace="Microsoft.Build.Framework" />
      <Using Namespace="Microsoft.Build.Utilities" />
      <Using Namespace="System"/>
      <Using Namespace="System.IO"/>
      <Using Namespace="System.Text"/>
      <Using Namespace="System.Linq"/>
      <Using Namespace="System.Xml.Linq"/>
      <Using Namespace="System.Collections.Generic"/>
      <Code Type="Class" Language="cs">
        <![CDATA[    
    using System.Linq;

    public class ReorderSqlProjFile_Inline : Microsoft.Build.Utilities.Task
    {
        private string _projectFullPath = @"]]>$(MSBuildProjectFullPath)<![CDATA[";


        public override bool Execute()
        {
            try
            {
                System.Xml.Linq.XDocument document = System.Xml.Linq.XDocument.Load(_projectFullPath, System.Xml.Linq.LoadOptions.PreserveWhitespace | System.Xml.Linq.LoadOptions.SetLineInfo);
                System.Xml.Linq.XNamespace msBuildNamespace = document.Root.GetDefaultNamespace();
                System.Xml.Linq.XName itemGroupName = System.Xml.Linq.XName.Get("ItemGroup", msBuildNamespace.NamespaceName);
                var itemGroups = document.Root.Descendants(itemGroupName).ToArray();

                var processedItemGroups = new System.Collections.Generic.List<System.Xml.Linq.XElement>();

                CombineCompatibleItemGroups(itemGroups, processedItemGroups);

                foreach (System.Xml.Linq.XElement itemGroup in processedItemGroups)
                {
                    SortItemGroup(itemGroup);
                }

                var originalBytes = System.IO.File.ReadAllBytes(_projectFullPath);
                byte[] newBytes = null;

                using (var memoryStream = new System.IO.MemoryStream())
                using (var textWriter = new System.IO.StreamWriter(memoryStream, System.Text.Encoding.UTF8))
                {
                    document.Save(textWriter, System.Xml.Linq.SaveOptions.None);
                    newBytes = memoryStream.ToArray();
                }

                if (!AreEqual(originalBytes, newBytes))
                {
                    Log.LogMessageFromText("===    RESULT: Included files in *.sqlproj need to be reordered.          ===", Microsoft.Build.Framework.MessageImportance.High);

                    if (!new System.IO.FileInfo(_projectFullPath).IsReadOnly)
                    {
                        System.IO.File.WriteAllBytes(_projectFullPath, newBytes);

                        Log.LogMessageFromText("===            *.sqlproj has been overwritten.                            ===", Microsoft.Build.Framework.MessageImportance.High);
                        Log.LogMessageFromText("===            Visual Studio will ask to reload project.                  ===", Microsoft.Build.Framework.MessageImportance.High);
                        Log.LogMessageFromText("===                                                                       ===", Microsoft.Build.Framework.MessageImportance.High);
                        Log.LogMessageFromText("=============================================================================", Microsoft.Build.Framework.MessageImportance.High);
                    }
                    else
                    {
                        Log.LogMessageFromText("===            *.sqlproj is readonly. Cannot overwrite *.sqlproj file.    ===", Microsoft.Build.Framework.MessageImportance.High);
                        Log.LogMessageFromText("===                                                                       ===", Microsoft.Build.Framework.MessageImportance.High);
                        Log.LogMessageFromText("=============================================================================", Microsoft.Build.Framework.MessageImportance.High);
                    }
                }
                else
                {
                    Log.LogMessageFromText("===    RESULT: *.sqlproj is OK.                                           ===", Microsoft.Build.Framework.MessageImportance.High);
                    Log.LogMessageFromText("===                                                                       ===", Microsoft.Build.Framework.MessageImportance.High);
                    Log.LogMessageFromText("=============================================================================", Microsoft.Build.Framework.MessageImportance.High);
                }

                return true;
            }
            catch (System.Exception ex)
            {
                Log.LogMessageFromText("===    RESULT: Exception occured trying to reorder *.sqlproj file.        ===", Microsoft.Build.Framework.MessageImportance.High);
                Log.LogMessageFromText("===            Exception:" + ex, Microsoft.Build.Framework.MessageImportance.High);
                Log.LogMessageFromText("===                                                                       ===", Microsoft.Build.Framework.MessageImportance.High);
                Log.LogMessageFromText("=============================================================================", Microsoft.Build.Framework.MessageImportance.High);

                return true;
            }
        }

        public bool AreEqual(byte[] left, byte[] right)
        {
            if (left == null)
            {
                return right == null;
            }

            if (right == null)
            {
                return false;
            }

            if (left.Length != right.Length)
            {
                return false;
            }

            for (int i = 0; i < left.Length; i++)
            {
                if (left[i] != right[i])
                {
                    return false;
                }
            }

            return true;
        }

        public void CombineCompatibleItemGroups(System.Xml.Linq.XElement[] itemGroups, System.Collections.Generic.List<System.Xml.Linq.XElement> processedItemGroups)
        {
            var itemTypeLookup = itemGroups.ToDictionary(i => i, i => GetItemTypesFromItemGroup(i));
            foreach (var itemGroup in itemGroups)
            {
                if (!itemGroup.HasElements)
                {
                    RemoveItemGroup(itemGroup);
                    continue;
                }

                var suitableExistingItemGroup = FindSuitableItemGroup(processedItemGroups, itemGroup, itemTypeLookup);
                if (suitableExistingItemGroup != null)
                {
                    ReplantAllItems(from: itemGroup, to: suitableExistingItemGroup);

                    RemoveItemGroup(itemGroup);
                }
                else
                {
                    processedItemGroups.Add(itemGroup);
                }
            }
        }

        public void RemoveItemGroup(System.Xml.Linq.XElement itemGroup)
        {
            var leadingTrivia = itemGroup.PreviousNode;
            if (leadingTrivia is System.Xml.Linq.XText)
            {
                leadingTrivia.Remove();
            }

            itemGroup.Remove();
        }

        public void ReplantAllItems(System.Xml.Linq.XElement from, System.Xml.Linq.XElement to)
        {
            if (to.LastNode is System.Xml.Linq.XText)
            {
                to.LastNode.Remove();
            }

            var fromNodes = from.Nodes().ToArray();
            from.RemoveNodes();
            foreach (var element in fromNodes)
            {
                to.Add(element);
            }
        }

        public System.Xml.Linq.XElement FindSuitableItemGroup(
            System.Collections.Generic.List<System.Xml.Linq.XElement> existingItemGroups,
            System.Xml.Linq.XElement itemGroup,
            System.Collections.Generic.Dictionary<System.Xml.Linq.XElement, System.Collections.Generic.HashSet<string>> itemTypeLookup)
        {
            foreach (var existing in existingItemGroups)
            {
                var itemTypesInExisting = itemTypeLookup[existing];
                var itemTypesInCurrent = itemTypeLookup[itemGroup];
                if (itemTypesInCurrent.IsSubsetOf(itemTypesInExisting) && AreItemGroupsMergeable(itemGroup, existing))
                {
                    return existing;
                }
            }

            return null;
        }

        public bool AreItemGroupsMergeable(System.Xml.Linq.XElement left, System.Xml.Linq.XElement right)
        {
            if (!AttributeMissingOrSame(left, right, "Label"))
            {
                return false;
            }

            if (!AttributeMissingOrSame(left, right, "Condition"))
            {
                return false;
            }

            return true;
        }

        public bool AttributeMissingOrSame(System.Xml.Linq.XElement left, System.Xml.Linq.XElement right, string attributeName)
        {
            var leftAttribute = left.Attribute(attributeName);
            var rightAttribute = right.Attribute(attributeName);
            if (leftAttribute == null && rightAttribute == null)
            {
                return true;
            }
            else if (leftAttribute != null && rightAttribute != null)
            {
                return leftAttribute.Value == rightAttribute.Value;
            }

            return false;
        }

        public System.Collections.Generic.HashSet<string> GetItemTypesFromItemGroup(System.Xml.Linq.XElement itemGroup)
        {
            var set = new System.Collections.Generic.HashSet<string>();
            foreach (var item in itemGroup.Elements())
            {
                set.Add(item.Name.LocalName);
            }

            return set;
        }

        public void SortItemGroup(System.Xml.Linq.XElement itemGroup)
        {
            System.Collections.Generic.List<System.Xml.Linq.XElement> list = new System.Collections.Generic.List<System.Xml.Linq.XElement>();
            foreach (System.Xml.Linq.XElement element in itemGroup.Elements())
                list.Add(element);
            var original = list.ToArray();
            var sorted = original
                .OrderBy(i => i.Name.LocalName)
                .ThenBy(i => (i.Attribute("Include") ?? i.Attribute("Remove")).Value)
                .ToArray();

            for (int i = 0; i < original.Length; i++)
            {
                original[i].ReplaceWith(sorted[i]);
            }
        }
    }
]]>
      </Code>
    </Task>
  </UsingTask>
  <Target Name="BeforeBuild">
    <Message Text="=============================================================================" Importance="high" />
    <Message Text="===================                                       ===================" Importance="high" />
    <Message Text="===================        RUNNING PREBIULD SCRIPT        ===================" Importance="high" />
    <Message Text="===                                                                       ===" Importance="high" />
    <Message Text="===   This script will order included files in *.sqlproj alphabetically   ===" Importance="high" />
    <Message Text="===           This is done to fix issues during merge process.            ===" Importance="high" />
    <Message Text="===                                                                       ===" Importance="high" />
    <Message Text="===    FYI: To disable this script comment next line in *.sqlproj file:   ===" Importance="high" />
    <Message Text="===      &lt;Import Project=&quot;build_VS2017.targets&quot; /&gt;        ===" Importance="high" />
    <Message Text="===                                                                       ===" Importance="high" />
    <Message Text="===                                                                       ===" Importance="high" />
    <Message Text="===                                                                       ===" Importance="high" />
    <Message Text="=============================================================================" Importance="high" />
    <ReorderSqlProjFile_Inline />
  </Target>
</Project>

然后在项目文件中，在</Project>之前添加以下条目：

...
    <Import Project="build_VS2017.targets" Condition="'$(Configuration)'=='Debug'" />
</Project>

Answer 2

与Dmitrij的回答类似，这里有一个PowerShell脚本来对sqlproj文件中的项进行排序：

Function AutoFix-SqlProj([string] $rootDirectory)
{
    $files = Get-ChildItem -Path $rootDirectory -Filter *.sqlproj -Recurse
    $modifiedfiles = @()

    foreach($file in $files)
    {
        $original = [xml] (Get-Content $file.FullName)
        $workingCopy = $original.Clone()

        foreach($itemGroup in $workingCopy.Project.ItemGroup){

            # Sort the Folder elements
            if ($itemGroup.Folder -ne $null){

                $sorted = $itemGroup.Folder | sort { [string]$_.Include }

                $itemGroup.RemoveAll() | Out-Null

                foreach($item in $sorted){
                    $itemGroup.AppendChild($item) | Out-Null
                }
            }

            # Sort the Build elements
            if ($itemGroup.Build -ne $null){

                $sorted = $itemGroup.Build | sort { [string]$_.Include }

                $itemGroup.RemoveAll() | Out-Null

                foreach($item in $sorted){
                    $itemGroup.AppendChild($item) | Out-Null
                }
            }
        }

        $differencesCount = (Compare-Object -ReferenceObject (Select-Xml -Xml $original -XPath "//*") -DifferenceObject (Select-Xml -Xml $workingCopy -XPath "//*")).Length

        if ($differencesCount -ne 0)
        {
            $workingCopy.Save($file.FullName) | Out-Null
            $modifiedfiles += $file.FullName
        }
    }

    return $modifiedfiles
}

$rootDirectory = Join-Path (Split-Path -Parent $MyInvocation.MyCommand.Path) "\..\..\"

$exitCode = 0;

$changedfiles = @()
$changedfiles += AutoFix-SqlProj($rootDirectory)

if ($changedfiles.Count -gt 0)
{
    Write-Host "The following files have been auto-formatted"
    Write-Host "to reduce the likelyhood of merge conflicts:"

    foreach($file in $changedfiles)
    {
        Write-Host $file
    }

    Write-Host "Your commit has been aborted. Add the modified files above"
    Write-Host "to your changes to be comitted then commit again."

    $exitCode = 1;
}

exit $exitcode