Our .sqlproj contains a lot of statements like this one for every object existing in the project:
<Build Include="MySchema\Tables\TableA" />
<Build Include="MySchema\Tables\TableB" />
<Build Include="MySchema\Tables\TableC" />
Whenever an object is being added to the project, SSDT will automatically update the sqlproj file by adding a record in some random line of the file. This causes a lot of merge problems when multiple developers are working on the same project.
I tried to modify this file by adding wildcards to all schemas folders, so the previous one would become:
<Build Include="MySchema\**" />
But then if I create TableD in the same schema, it will still add a record for that object even if it is included in the previous statement. So my .sqlproj would look like this :
<Build Include="MySchema\**" />
<Build Include="MySchema\Tables\TableD" />
Is there any solution to get around this?
Merging of SSDT sqlproj project file is just a pain. We've created MSBuild targets file that simply sorts project file every time you build the project. The downside of that is when the sqlproj file is sorted, it is treated by Visual Studio that it is modified externally and it wants to refresh the project. Anyway it is not so big deal comparing with the merging hell.
So, in the project folder we have build_VS2017.targets file (it might need to be adjusted if you want to use it with not VS 2017 version, at least I did something when we were migrating from 2015 to 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="=== <Import Project="build_VS2017.targets" /> ===" Importance="high" />
<Message Text="=== ===" Importance="high" />
<Message Text="=== ===" Importance="high" />
<Message Text="=== ===" Importance="high" />
<Message Text="=============================================================================" Importance="high" />
<ReorderSqlProjFile_Inline />
</Target>
</Project>
and then in the project file add following entry just before </Project>:
...
<Import Project="build_VS2017.targets" Condition="'$(Configuration)'=='Debug'" />
</Project>