/// <summary>
/// Compares database objects (except tables and columns) from two distinct XML schema files. These files are generated
/// by the SerializeDB method. This method is called from the CompareSchema method.
/// </summary>
/// <param name="_serverDB">Used to identify which server the compare objects came from</param>
/// <param name="xmlSourceDoc">The XmlDataDocument, the source XML to compare with the destination XML.</param>
/// <param name="xmlDestinationDoc">The XmlDataDocument, the destination/target XML to compare with the source XML.</param>
/// <param name="xmlDiffDoc">The XML Diffgram document to update.</param>
/// <param name="type">The type of database object collection to compare, i.e. defaults, UDDTs, rules, views, functions,
/// stored procedures, and triggers.</param>
/// <param name="xmlDiff">The XmlDiff Object used from the GotDotNet XmlDiff class. Performs the XML node compare.</param>
/// <param name="CompareTextFlag">Flag that performs full text compare between objects</param>
/// <param name="sqlObjectName">specifies a specific SQL object to compare</param>
public static void CompareObjects(string _serverDB, XmlDocument xmlSourceDoc, XmlDocument xmlDestinationDoc,
XmlDocument xmlDiffDoc, _NodeType type, XmlDiff xmlDiff, bool CompareTextFlag, string sqlObjectName)
{
SortedList sl = new SortedList();
SortedList slDep = new SortedList();
bool addFlag = false;
bool alterFlag = false;
bool sortFlag = false;
string selectNodes = string.Empty;
string selectText = string.Empty;
string typeName = string.Empty;
string depText = string.Empty;
// iterate thru the source tables
// we are going to ignore destination tables which are not in the source DB
// as they could be custom tables added by some outside tool or person
switch (type)
{
case _NodeType.SPROC:
{
selectNodes = SPROCPATH;
selectText = SPROCTEXT;
depText = SPROCDEP;
typeName = type.ToString();
sortFlag = true;
break;
}
case _NodeType.FUNCTION:
{
selectNodes = FUNCPATH;
selectText = FUNCTEXT;
depText = FUNCDEP;
typeName = type.ToString().Substring(0, 4);
sortFlag = true;
break;
}
case _NodeType.VIEW:
{
selectNodes = VIEWPATH;
selectText = VIEWTEXT;
depText = VIEWDEP;
typeName = type.ToString();
sortFlag = true;
break;
}
case _NodeType.TRIGGER:
{
selectNodes = TRIGGERPATH;
selectText = TRIGGERTEXT;
typeName = type.ToString();
break;
}
case _NodeType.DEFAULT:
{
selectNodes = DEFAULTPATH;
selectText = DEFAULTTEXT;
typeName = type.ToString();
break;
}
case _NodeType.RULE:
{
selectNodes = RULEPATH;
selectText = RULETEXT;
typeName = type.ToString();
break;
}
case _NodeType.UDDT:
{
selectNodes = UDDTPATH;
selectText = UDDTTEXT;
typeName = type.ToString();
break;
}
}
SortedList slMatch = new SortedList();
XmlNodeList xNodeList = xmlSourceDoc.SelectNodes(selectNodes, nsmgr_Source);
XmlNode xnDestParent = xmlDestinationDoc.SelectSingleNode(selectNodes, nsmgr_Dest);
// use stringbuilder class as it should be faster
StringBuilder sourceTxt = new StringBuilder();
StringBuilder destTxt = new StringBuilder();
StringBuilder sortedname = new StringBuilder();
foreach (XmlNode xnChild_Source in xNodeList)
{
XmlNode xnDest = null;
XmlNodeList XmlDestTextList = null;
sourceTxt.Length = 0;
destTxt.Length = 0;
int destNodeCount = 0;
int sourceNodeCount = 0;
string SqlObjectName = xnChild_Source.ChildNodes[0].InnerXml;
if (sqlObjectName != null && !SqlObjectName.ToLower().Equals(sqlObjectName.ToLower()))
{
continue; // loop until passed in object name is found
}
string xpath = selectText + SqlObjectName + "']";
XmlNode xnChk = xnChild_Source.SelectSingleNode("Check_Sum");
string src_checksum = string.Empty;
string dst_checksum = string.Empty;
if (xnChk != null)
{
src_checksum = xnChk.InnerXml;
}
logger.Debug("\n{2}: Comparing {1}: {0}.", SqlObjectName, type.ToString().ToLower(), _serverDB);
// walk thru destination xml doc nodes to find matching sproc node
if (xnDestParent != null)
{
xnDest = xnDestParent.SelectSingleNode(selectNodes + "[" + xpath.Split('[')[1]);
}
// get xmlnodes for text rows in destination doc
if (xnDest != null)
{
xnChk = xnDest.SelectSingleNode("Check_Sum");
if (xnChk != null)
{
dst_checksum = xnChk.InnerXml;
}
// if objects don't compare then walk each line of text
if (!xmlDiff.Compare(xnChild_Source, xnDest))
{
XmlDestTextList = xnDest.SelectNodes(selectText.Split('[')[0]);
// only comapre the text length (not a checksum) if there is not a match on the nodes
if (xnChk != null && dst_checksum == src_checksum)
{
continue;
}
}
else
{
continue;
}
}
if (xnDest != null && XmlDestTextList != null && XmlDestTextList.Count > 0)
{
// don't care about existing UDDTs and Defaults, only new ones
// so resume at next node
if (type == _NodeType.UDDT || type == _NodeType.DEFAULT || type == _NodeType.RULE)
{
continue;
}
if (CompareTextFlag)
{
// get xmlnodes for text rows in source
XmlNodeList xnlSource = xnChild_Source.SelectNodes(xpath, nsmgr_Source);
// build text for source
foreach (XmlNode xnSource in xnlSource)
{
// ignore blank text nodes, tabs, newline and carriage returns
if (xnSource.SelectSingleNode("Text") == null || xnSource.SelectSingleNode("Text").InnerText.Length == 0)
{
continue;
}
sourceNodeCount += 1;
sourceTxt.Append(xnSource.SelectSingleNode("Text").InnerText.ToLower().Trim());
sourceTxt.Replace(" ", "");
sourceTxt.Replace("\n", "");
sourceTxt.Replace("\r", "");
sourceTxt.Replace("\t", "");
}
if (sourceTxt.ToString().Length > 0)
{
// build text for destination
foreach (XmlNode xnChild in XmlDestTextList)
{
// ignore blank text nodes, tabs, newline and carriage returns
if (xnChild.SelectSingleNode("Text") == null || xnChild.SelectSingleNode("Text").InnerText.Length == 0)
{
continue;
}
destNodeCount += 1;
destTxt.Append(xnChild.SelectSingleNode("Text").InnerText.ToLower().Trim());
destTxt.Replace(" ", "");
destTxt.Replace("\n", "");
destTxt.Replace("\r", "");
destTxt.Replace("\t", "");
// look for this text string in the source text string
// if theres no match then we do not need to look thru
// all the dest text nodes, so exit out of loop, instead of continuing on
// Contains = true if the value parameter occurs within this string, or if value is the empty string (""); otherwise, false.
if (!sourceTxt.ToString().Contains(destTxt.ToString()) && destTxt.ToString().Length > 0)
{
break;
}
}
}
//CompareInfo compare = CultureInfo.InvariantCulture.CompareInfo;
//int compareResult = compare.Compare(sourceTxt.ToString(), destTxt.ToString(), CompareOptions.IgnoreCase | CompareOptions.IgnoreSymbols | CompareOptions.IgnoreNonSpace);
}
else
{
alterFlag = true;
}
// compare source and dest text
if (sourceTxt.ToString().ToLower() != destTxt.ToString().ToLower())
{
alterFlag = true;
}
// if no differences then resume at next source node
if (!alterFlag)
{
continue;
}
}
else // we have a source node, but no matching destination node so set the add flag to true
{
addFlag = true;
}
// if no destination text, we need to add it from the source
if (sourceNodeCount > 0 && (destNodeCount == 0 || xnDest == null))
{
// the object_text node was not found in the destination so add parent node for object to DiffDoc as an add
addFlag = true;
}
XmlNode xNode = null;
if (addFlag)
{
xNode = xmlDiffDoc.CreateNode(XmlNodeType.Element, typeName, xmlDiffDoc.NamespaceURI);
XmlAttribute xNodeAttrib = xmlDiffDoc.CreateAttribute("Action");
xNodeAttrib.Value = "Add";
xNode.InnerXml = xnChild_Source.InnerXml;
xNode.Attributes.Append(xNodeAttrib);
if (!sortFlag) xmlDiffDoc.SelectSingleNode("/DataBase_Schema").AppendChild(xNode);
addFlag = false;
}
if (alterFlag)
{
xNode = xmlDiffDoc.CreateNode(XmlNodeType.Element, typeName, xmlDiffDoc.NamespaceURI);
XmlAttribute xNodeAttrib = xmlDiffDoc.CreateAttribute("Action");
xNodeAttrib.Value = "Alter";
xNode.InnerXml = xnChild_Source.InnerXml;
xNode.Attributes.Append(xNodeAttrib);
if (!sortFlag) xmlDiffDoc.SelectSingleNode("/DataBase_Schema").AppendChild(xNode);
alterFlag = false;
}
if (sortFlag)
{
if (xNode != null)
{
XmlNodeList xnlSource = xnChild_Source.SelectNodes(depText + SqlObjectName + "']", nsmgr_Source);
// get dependencies names and add to sorted list
int ii = 0;
foreach (XmlNode xnSource in xnlSource)
{
if (xnSource.ChildNodes[1] != null)
{
slDep.Add(string.Format("{1:0000}!{0}", SqlObjectName.ToLower(), ii), xnSource.ChildNodes[1].InnerXml.ToLower());
ii += 1;
}
}
if (ii > 0)
{
sl.Add(string.Format("{1:0000}!{0}", SqlObjectName.ToLower(), ii), xNode);
}
else
{
sl.Add(string.Format("0000!{0}", SqlObjectName.ToLower()), xNode);
}
}
}
} // end of main for loop
if (sortFlag)
{
// if sldep has count > 0 then we need to do some additional sorting
SortedList sl2 = new SortedList();
int zz = 0;
for (int ii = 0; ii < sl.Count; ii++)
{
sl2.Add(string.Format("{1:00000},{0}", sl.GetKey(ii).ToString(), zz), sl.GetByIndex(ii));
zz += 10;
}
if (slDep.Count > 0)
{
int start = 0;
RecurseDependencies(_serverDB, ref sl2, ref slDep, 0, ref start);
}
for (int ii = 0; ii < sl2.Count; ii++)
{
XmlNode xn = (XmlNode)sl2.GetByIndex(ii);
xmlDiffDoc.SelectSingleNode("/DataBase_Schema").AppendChild(xn);
}
}
}
/// <summary>
/// Compares database objects (except tables and columns) from two distinct XML schema files. These files are generated
/// by the SerializeDB method. This method is called from the CompareSchema method.
/// </summary>
/// <param name="_serverDB">Used to identify which server the compare objects came from</param>
/// <param name="xmlSourceDoc">The XmlDataDocument, the source XML to compare with the destination XML.</param>
/// <param name="xmlDestinationDoc">The XmlDataDocument, the destination/target XML to compare with the source XML.</param>
/// <param name="xmlDiffDoc">The XML Diffgram document to update.</param>
/// <param name="type">The type of database object collection to compare, i.e. defaults, UDDTs, rules, views, functions,
/// stored procedures, and triggers.</param>
/// <param name="xmlDiff">The XmlDiff Object used from the GotDotNet XmlDiff class. Performs the XML node compare.</param>
/// <param name="CompareTextFlag">Flag that performs full text compare between objects</param>
public static void CompareObjects(string _serverDB, XmlDocument xmlSourceDoc, XmlDocument xmlDestinationDoc,
XmlDocument xmlDiffDoc, _NodeType type, XmlDiff xmlDiff, bool CompareTextFlag)
{
CompareObjects(_serverDB, xmlSourceDoc, xmlDestinationDoc, xmlDiffDoc, type, xmlDiff, CompareTextFlag, null);
}
