.sqlproj 文件的版本控制

我们的.sqlproj包含了很多语句，例如项目中存在的每个对象：

<Build Include="MySchemaTablesTableA" />
<Build Include="MySchemaTablesTableB" />
<Build Include="MySchemaTablesTableC" />

每当将对象添加到项目中时，SSDT 都会通过在文件的某个随机行中添加记录来自动更新sqlproj文件。当多个开发人员处理同一项目时，这会导致很多合并问题。

我尝试通过向所有架构文件夹添加通配符来修改此文件，因此上一个将变为：

<Build Include="MySchema**" />

但是，如果我在同一架构中创建TableD，即使它包含在上一个语句中，它仍然会为该对象添加一条记录。所以我的.sqlproj看起来像这样：

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

有什么解决方案可以解决这个问题吗？

合并 SSDT sqlproj 项目文件只是一种痛苦。我们创建了 MSBuild 目标文件，该文件只是在每次生成项目时对项目文件进行排序。这样做的缺点是，当sqlproj文件被排序时，Visual Studio认为它是在外部修改的，它想要刷新项目。反正和合并地狱相比

，没什么大不了的。因此，在项目文件夹中，我们有 build_VS2017.targets 文件(如果您想在非 VS 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>

与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

相关内容

最新更新

热门标签：