/// <summary> /// Create the ReferenceTable node /// </summary> private static void ConvertToReferenceTableNode(EdtData edt, XmlDocument document) { //Does it have a referencetable? if not, add it after the label XmlNode referenceNode = document.SelectSingleNode("//ReferenceTable"); if (referenceNode != null) { if (!String.IsNullOrEmpty(referenceNode.InnerText)) { throw new ApplicationException("Reference table is already filled"); } } else { //Order of nodes is fixed in XML, but schema isnt available. So we take a best guess abou where to insert the ReferenceTable node XmlNode insertAfterNode = document.SelectSingleNode("//Label"); if (insertAfterNode == null) { insertAfterNode = document.SelectSingleNode("//HelpText"); } if (insertAfterNode == null) { insertAfterNode = document.SelectSingleNode("//Extends"); } if (insertAfterNode == null) { insertAfterNode = document.SelectSingleNode("//Name"); } referenceNode = document.CreateNode(XmlNodeType.Element, "ReferenceTable", ""); document.FirstChild.NextSibling.InsertAfter(referenceNode, insertAfterNode); } referenceNode.InnerText = edt.Relations[0].Table; }
/// <summary> /// Create the table references node /// </summary> private static void ConvertToTableReferencesNode(EdtData edt, XmlDocument document) { //Does it have a referencetable? if not, add it after the label XmlNode referenceNode = document.SelectSingleNode("//TableReferences"); if (referenceNode != null) { if (!String.IsNullOrEmpty(referenceNode.InnerXml)) { throw new ApplicationException("TableReferences is already filled"); } } else { XmlNode relationsNode = document.SelectSingleNode("//Relations"); referenceNode = document.CreateNode(XmlNodeType.Element, "TableReferences", ""); document.FirstChild.NextSibling.InsertAfter(referenceNode, relationsNode); } //Multi relation EDTs are not processed by the program, they are left for manual fixing Relation rel = edt.Relations.First(a => String.IsNullOrEmpty(a.Value)); if (rel == null) { throw new ApplicationException("Only fixed field relations were found on the edt"); } XmlNode edtTableReferenceNode = document.CreateNode(XmlNodeType.Element, "AxEdtTableReference", ""); referenceNode.AppendChild(edtTableReferenceNode); XmlNode relatedFieldNode = document.CreateNode(XmlNodeType.Element, "RelatedField", ""); edtTableReferenceNode.AppendChild(relatedFieldNode); relatedFieldNode.InnerText = rel.RelatedField; XmlNode relatedTableNode = document.CreateNode(XmlNodeType.Element, "Table", ""); edtTableReferenceNode.AppendChild(relatedTableNode); relatedTableNode.InnerText = rel.Table; }
/// <summary> /// Reads an existing (or previously migrated) table relation on an Edt /// </summary> /// <param name="document"></param> /// <returns></returns> private static EdtData ReadReferenceTable(XmlDocument document) { var referenceTableNode = document.SelectSingleNode("//ReferenceTable"); EdtData returnValue = new EdtData(document); //There should be only one table reference node int tableReferencesNodeCount = document.SelectNodes("//AxEdtTableReference").Count; if (tableReferencesNodeCount > 1) { throw new ApplicationException("There should be only one table reference in an edt relation"); } else if (tableReferencesNodeCount == 0) { TableReferenceWithoutRelatedNodes.Add($"There is a ReferenceTableName but no AxEdtTableReference in {returnValue.EdtName}"); return(null); } //There is indeed only one var tableReferencesNode = document.SelectSingleNode("//AxEdtTableReference"); var relatedFieldNode = tableReferencesNode.SelectSingleNode("./RelatedField"); var tableNode = tableReferencesNode.SelectSingleNode("./Table"); if (relatedFieldNode == null) { throw new ApplicationException("There should be a related field node in an edt relation"); } if (tableNode == null) { throw new ApplicationException("There should be a table node in an edt relation"); } string relatedField = relatedFieldNode.InnerText; string table = tableNode.InnerText; returnValue.Relations.Add(new Relation { Table = table, RelatedField = relatedField }); return(returnValue); }
/// <summary> /// Create the relations on the table xml /// </summary> private static XmlDocument UpdateTableWithRelations(string tablePath) { XmlDocument document = new XmlDocument(); document.Load(tablePath); //Gets set if anything is changed bool tableIsUpdated = false; //Name node of the root elmenet, which can be AxTable or AxTableExtension String tableName = document.SelectSingleNode("/*/Name").InnerText; foreach (XmlNode field in document.SelectNodes("//AxTableField")) { XmlNode edtNode = field.SelectSingleNode(".//ExtendedDataType"); if (edtNode != null && toProcess.ContainsKey(edtNode.InnerText.ToLower())) { EdtData edt = toProcess[edtNode.InnerText.ToLower()]; // Found a match. However: // The field that the edt references should not get a table relation. // How can we detect this? The name of our current table must be the // same as the name of the relation and the name of the field must be the same as the name of the referenced field // Every other occurance of the EDT should get a foreign key relation string fieldName = field.SelectSingleNode(".//Name").InnerText; string foreignTableName = edt.Relations[0].Table; string foreignFieldName = edt.Relations[0].RelatedField; string xPath = $".//AxTableRelation[RelatedTable=\"{foreignTableName}\" and Constraints/AxTableRelationConstraint/Field=\"{fieldName}\"]"; if (tableName.ToLower() == foreignTableName.ToLower() && fieldName.ToLower() == foreignFieldName.ToLower()) { PrimaryIdDetected.Add($"{tableName} has field {fieldName} with datatype {edt.EdtName}. This is the primary key of the EDT, no relation is created"); } else { //Find relation node XmlNode relationNode = document.SelectSingleNode("//Relations"); //Not a key field, we can actually do something with it if (document.SelectNodes($"//AxTableField[ExtendedDataType = \"{edt.EdtName}\"]").Count == 1) { //Simple case, only one copy of they key field is found //Checking if FK already exists // -Right table is referenced // -On the same field int existingFkCount = relationNode.SelectNodes(xPath).Count; if (existingFkCount == 1) { XmlNode fkCandidate = relationNode.SelectSingleNode(xPath); var fieldsInFk = fkCandidate.SelectNodes($".//Field"); if (fieldsInFk.Count == 1) { var fieldInFk = fieldsInFk[0]; //Checking if the related field in the other table is the expected field if (fieldInFk.ParentNode.SelectSingleNode(".//RelatedField").InnerText.ToLower() == foreignFieldName.ToLower()) { ExistingForeignKeyDetected.Add($"{tableName} already has FK on edt {edt.EdtName} on field {fieldName}, so nothing is added"); } else { AnomalyDetected.Add($"{tableName} already has FK on edt {edt.EdtName} on field {fieldName} but it doesnt reference the expected field in the foreign table"); } } else { MultiKeyFkDetected.Add($"{tableName} has a multi-key FK ({fkCandidate.SelectSingleNode(".//Name").InnerText} along {edt.EdtName} "); } } else if (existingFkCount > 1) { AnomalyDetected.Add($"{tableName} has multiple FKs along {edt.EdtName} on field {fieldName}"); } else { //No FK exists, lets do our thing ForeignKeyCreated.Add($"{tableName} has a single copy of edt {edt.EdtName} on field {fieldName}. Foreign key created"); XmlNode fk = CreateFkNode(document, foreignTableName, foreignTableName); XmlNode constraintsNode = document.CreateNode(XmlNodeType.Element, "Constraints", ""); XmlNode axTableRelationConstraintNode = CreateConstraintNode(document, fieldName, edt.Relations[0].RelatedField); fk.AppendChild(constraintsNode); constraintsNode.AppendChild(axTableRelationConstraintNode); relationNode.AppendChild(fk); tableIsUpdated = true; } } else { //Multiple copies of the field are found (one of them might be the PK that doesnt need an FK) //We have to add a role to the relations in this case //Is there an FK already on this field? int existingFkCount = relationNode.SelectNodes(xPath).Count; if (existingFkCount == 0) { //This FK doesnt exist yet, we create the structure XmlNode fk = CreateFkNode(document, foreignTableName + "_" + fieldName, foreignTableName); XmlNode constraintsNode = document.CreateNode(XmlNodeType.Element, "Constraints", ""); XmlNode axTableRelationConstraintNode = CreateConstraintNode(document, fieldName, edt.Relations[0].RelatedField); XmlNode roleNode = document.CreateNode(XmlNodeType.Element, "Role", ""); roleNode.InnerText = $"{tableName}_{fieldName}"; XmlNode relatedRoleNode = document.CreateNode(XmlNodeType.Element, "RelatedTableRole", ""); relatedRoleNode.InnerText = $"{foreignTableName}_{fieldName}"; fk.AppendChild(relatedRoleNode); fk.AppendChild(roleNode); fk.AppendChild(constraintsNode); constraintsNode.AppendChild(axTableRelationConstraintNode); relationNode.AppendChild(fk); tableIsUpdated = true; ForeignKeyCreated.Add($"{tableName} has multiple copies of {edt.EdtName}. Foreign key created for field {fieldName}"); } else if (existingFkCount == 1) { //FK exists, logging it ExistingForeignKeyDetected.Add($"{tableName} already has FK on edt {edt.EdtName} on field {fieldName}, so nothing is added"); } else { //Multiple FK on a single field? what is going on? AnomalyDetected.Add($"{tableName} has muliple FKs on a single field ({fieldName})"); } } } } } //Null is the magic value indicating the file doesnt need to be saved if (tableIsUpdated) { return(document); } else { return(null); } }
/// <summary> /// Find if the parent has a relation somewhere up on the inheritance chain /// </summary> /// <returns></returns> private static Relation FindRelationForParent(string parentName, string originalEdtName) { //Check if we already processed the parent into an edtData //If not, check if we at least parsed the XML document //If not, then put the parent on the waiting list, and when we come across it, we will resume processing the chain //And do the whole thing recursively, until we either run into a relation or run out of parents. EdtData parent = null; //Capitalization in AX XMLs is inconsistent, so we have to go with tolower (or compare invariantly) string lcParentName = parentName.ToLower(); if (AllParsedEdts.ContainsKey(lcParentName)) { parent = AllParsedEdts[lcParentName]; } else if (AllEdts.ContainsKey(lcParentName)) { //We've read this file previously, but it didnt have table references or relations on the first pass //However we now know that we should save it for the future XmlDocument document = AllEdts[lcParentName]; parent = new EdtData(document); AllParsedEdts.Add(lcParentName, parent); } else { //Parent not found yet. Adding it to watchlist if (ParentWatchList.ContainsKey(lcParentName)) { ParentWatchList[lcParentName].Add(originalEdtName.ToLower()); } else { ParentWatchList.Add(lcParentName, new List <string> { originalEdtName.ToLower() }); } //Parent not found for now, processing will resume later through the watchlist return(null); } if (parent.Relations.Count == 1) { //The parent has one relation, this is what we want return(parent.Relations[0]); } else if (parent.Relations.Count > 1) { EdtsWithMultiRelationParents.Add($"{originalEdtName} has an ancestor ({parentName}) with multiple relations"); //We do nothing with this edt return(null); } //this means the parent has 0 relations else { //If this parent also has a parent, we continue crawling up the inheritance chain if (!String.IsNullOrEmpty(parent.NameOfParentEdt)) { return(FindRelationForParent(parent.NameOfParentEdt, originalEdtName)); } else { //We reached the top of the tree, it doesnt have a relation, we don't have to process this edt return(null); } } }
/// <summary> /// Checks if we have been waiting for this edt to turn up or not /// </summary> /// <param name="document"></param> private static void CheckWatchList(XmlDocument document) { EdtData parent = new EdtData(document); if (ParentWatchList.ContainsKey(parent.EdtName.ToLower())) { List <String> children = ParentWatchList[parent.EdtName.ToLower()]; var relationNode = document.SelectSingleNode("//Relations"); //Reading the relevant parts from the parent EDT if (document.SelectSingleNode("//ReferenceTable") != null) { EdtData parentFromReferenceTable = ReadReferenceTable(document); if (parentFromReferenceTable != null) { parent = parentFromReferenceTable; } else { ParentProblems.Add($"{parent.EdtName} has a Reference table node, but doesnt have valid table references, children {String.Join(",", children)} could not be processed"); return; } } else if (relationNode != null) { parent.Relations = ReadRelations(relationNode); } else if (!String.IsNullOrEmpty(parent.NameOfParentEdt)) { //This EDT has no valid relations, but does have a parent, so we recursively extend the search upward //in the inheritance hierarchy foreach (string child in children) { FindRelationForParent(parent.NameOfParentEdt, child); } ParentWatchList.Remove(parent.EdtName.ToLower()); } //We found one relation on the parent, so all children should get that relation from the parent if (parent.Relations.Count == 1) { foreach (string child in children) { XmlDocument childXml = AllEdts[child.ToLower()]; EdtData childEdt = new EdtData(childXml); childEdt.Relations.Add(parent.Relations[0]); toProcess.Add(childEdt.EdtName.ToLower(), childEdt); if (!AllParsedEdts.ContainsKey(childEdt.EdtName.ToLower())) { AllParsedEdts.Add(childEdt.EdtName.ToLower(), childEdt); } if (AllEdts.ContainsKey(childEdt.EdtName.ToLower())) { AllEdts.Remove(childEdt.EdtName.ToLower()); } } ParentWatchList.Remove(parent.EdtName.ToLower()); } else if (parent.Relations.Count == 0) { //No relations and no parents, the children edt don't need to be processed in the tables ParentWatchList.Remove(parent.EdtName.ToLower()); } else { ParentProblems.Add($"{parent.EdtName} has multiple relations. Validate manually. The child edts are:{String.Join(",", children)}"); } } }
static void Main(string[] args) { string currentRunTime = DateTime.Now.ToString("yyyy.MM.dd.HH.mm"); outputRootDirectory = Directory.CreateDirectory($".\\{currentRunTime}"); //Xml writer that produces similar formatting to the xml files generated by the Visual studio plugin XmlWriterSettings xwsSettings = new XmlWriterSettings(); xwsSettings.Indent = true; xwsSettings.ConformanceLevel = ConformanceLevel.Document; //Fixing the EDTs foreach (string targetModel in TargetModels) { string modelFolder = Path.Combine(MetadataFolder, targetModel); DirectoryInfo resultFolder = Directory.CreateDirectory(ModelFolderName(outputRootDirectory, targetModel)); //Not all target models have EDTs, some only have tables using our edts string edtInputFolder = Path.Combine(modelFolder, "AxEdt"); if (!Directory.Exists(edtInputFolder)) { continue; } //Subfolder that contains the output edt files string edtOutputFolder = Path.Combine(resultFolder.FullName, "AxEdt"); DirectoryInfo edtFolder = Directory.CreateDirectory(edtOutputFolder); //Iterating through all input edts foreach (string edtPath in Directory.EnumerateFiles(edtInputFolder, "*.xml")) { //Loading the edt XmlDocument document = new XmlDocument(); document.Load(edtPath); EdtData edt = new EdtData(document); var relationNode = document.SelectSingleNode("//Relations"); var numberOfArrays = document.SelectNodes("//AxEdtArrayElement").Count; var referenceTableNode = document.SelectSingleNode("//ReferenceTable"); //Checking the watchlist. It is possible that some EDTs will be parsed twice CheckWatchList(document); //Has an array element. Making a note, but not processing it if (numberOfArrays > 0) { AnomalyDetected.Add($"{edt.EdtName} has Array Elements. Needs manual processing"); continue; } //This table edt has nothing interesting in it, it doesnt have a table relation, it doesnt have a table reference, and it is not inherited if (relationNode == null && (referenceTableNode == null || String.IsNullOrEmpty(referenceTableNode.InnerText)) && !String.IsNullOrEmpty(edt.NameOfParentEdt)) { continue; } //Saving the parsed XML AllEdts.Add(edt.EdtName.ToLower(), document); //There is a relation if (relationNode != null && relationNode.ChildNodes.Count != 0) { //This edt has multiple relations in it (probably a field fixed). These will have to be manually fixed after the migration if (relationNode.ChildNodes.Count > 1) { MultiRelationEdts.Add(edt.EdtName); } //Some files have to be skipped, even if they have relations edt.Relations = ReadRelations(relationNode); //There are some weirdly formatted EDT xmls out there if (edt.Relations.Count > 0) { //Fix the EDT ConvertToReferenceTableNode(edt, document); ConvertToTableReferencesNode(edt, document); relationNode.ParentNode.RemoveChild(relationNode); //Save the fixed EDT toProcess.Add(edt.EdtName.ToLower(), edt); using (XmlWriter xwWriter = XmlWriter.Create(Path.Combine(edtFolder.FullName, new FileInfo(edtPath).Name), xwsSettings)) document.Save(xwWriter); } AllParsedEdts.Add(edt.EdtName.ToLower(), edt); AllEdts.Remove(edt.EdtName.ToLower()); } //Gathering the info about the reference tables so we can use them if there are any EDTS inherited from them else if (referenceTableNode != null && !String.IsNullOrEmpty(referenceTableNode.InnerText)) { EdtData referenceTable = ReadReferenceTable(document); if (referenceTable != null) { toProcess.Add(referenceTable.EdtName.ToLower(), referenceTable); AllParsedEdts.Add(referenceTable.EdtName.ToLower(), referenceTable); AllEdts.Remove(referenceTable.EdtName.ToLower()); } } //This EDT extends another (its else if, because if the relations are defined on this edt , then we don't care about its ancestors) else if (!String.IsNullOrEmpty(edt.NameOfParentEdt)) { //We check if we already know about the edt that is the parent of this one Relation relationOfParent = FindRelationForParent(edt.NameOfParentEdt, edt.EdtName); //If we were able to found the parent relation, then this EDT can also be processed. //Otherwise we do nothing with it- either it's on the watchlist, waiting for a parent to turn up, //or the parent(s) don't have relations either if (relationOfParent != null) { edt.Relations.Add(relationOfParent); toProcess.Add(edt.EdtName.ToLower(), edt); AllParsedEdts.Add(edt.EdtName.ToLower(), edt); AllEdts.Remove(edt.EdtName.ToLower()); } } } } //Reading possible parent edts foreach (string targetModel in ReadOnlyModels) { string modelFolder = Path.Combine(MetadataFolder, targetModel); string edtInputFolder = Path.Combine(modelFolder, "AxEdt"); if (!Directory.Exists(edtInputFolder)) { continue; } //Iterating through all input edts foreach (string edtPath in Directory.EnumerateFiles(edtInputFolder, "*.xml")) { //Loading the edt XmlDocument document = new XmlDocument(); document.Load(edtPath); CheckWatchList(document); } } //We dont work with multiRelation edts, they have to be handled manually (they should be filtered earlier, except for some really weird cases) foreach (string key in toProcess.Keys) { if (toProcess[key].Relations.Count > 1) { AnomalyDetected.Add($"{key} has anomalous multiple relations"); toProcess.Remove(key); } } //Creating the table relations on the tables and table extensions foreach (string folder in TargetModels) { DirectoryInfo resultFolder = new DirectoryInfo(ModelFolderName(outputRootDirectory, folder)); string modelFolder = Path.Combine(MetadataFolder, folder); //Checking table extensions if (Directory.Exists(Path.Combine(modelFolder, "AxTableExtension"))) { DirectoryInfo axTableExtensionFolder = Directory.CreateDirectory(Path.Combine(resultFolder.FullName, "AxTableExtension")); foreach (string tablePath in Directory.EnumerateFiles(Path.Combine(modelFolder, "AxTableExtension"), "*.xml")) { XmlDocument documentToSave = UpdateTableWithRelations(tablePath); //Saving the file. null is magic value for not having any updated relations if (documentToSave != null) { using (XmlWriter xwWriter = XmlWriter.Create(Path.Combine(axTableExtensionFolder.FullName, new FileInfo(tablePath).Name), xwsSettings)) documentToSave.Save(xwWriter); } } } //Checking tables if (Directory.Exists(Path.Combine(modelFolder, "AxTable"))) { DirectoryInfo axTableFolder = Directory.CreateDirectory(Path.Combine(resultFolder.FullName, "AxTable")); foreach (string tablePath in Directory.EnumerateFiles(Path.Combine(modelFolder, "AxTable"), "*.xml")) { XmlDocument documentToSave = UpdateTableWithRelations(tablePath); //Saving the file. null is magic value for not having any updated relations if (documentToSave != null) { using (XmlWriter xwWriter = XmlWriter.Create(Path.Combine(axTableFolder.FullName, new FileInfo(tablePath).Name), xwsSettings)) documentToSave.Save(xwWriter); } } } } //Writing the logfiles foreach (var key in ParentWatchList.Keys) { ParentProblems.Add($"Parent {key} not found. Children are: {String.Join(",", ParentWatchList[key])}"); } File.WriteAllLines(Path.Combine(outputRootDirectory.FullName, $"MultiRelationEdt.txt"), MultiRelationEdts); File.WriteAllLines(Path.Combine(outputRootDirectory.FullName, $"PrimaryIdDetected.txt"), PrimaryIdDetected); File.WriteAllLines(Path.Combine(outputRootDirectory.FullName, $"ExistingForeignKeyDetected.txt"), ExistingForeignKeyDetected); File.WriteAllLines(Path.Combine(outputRootDirectory.FullName, $"AnomalyDetected.txt"), AnomalyDetected); File.WriteAllLines(Path.Combine(outputRootDirectory.FullName, $"MultiKeyFkDetected.txt"), MultiKeyFkDetected); File.WriteAllLines(Path.Combine(outputRootDirectory.FullName, $"ForeignKeyCreated.txt"), ForeignKeyCreated); File.WriteAllLines(Path.Combine(outputRootDirectory.FullName, $"EdtsWithMultiRelationParents.txt"), EdtsWithMultiRelationParents); File.WriteAllLines(Path.Combine(outputRootDirectory.FullName, $"ParentProblems.txt"), ParentProblems); File.WriteAllLines(Path.Combine(outputRootDirectory.FullName, $"TableReferenceWithoutRelatedNodes.txt"), TableReferenceWithoutRelatedNodes); }