private void ButtonGenerateTestcases_Click(object sender, EventArgs e) { var connOmd = new SqlConnection { ConnectionString = TeamConfigurationSettings.ConnectionStringOmd }; try { connOmd.Open(); } catch (Exception exception) { richTextBoxOutput.Text = "There was an error connecting to the metadata database. \r\n\r\nA connection could not be established. Can you verify the connection details for the metadata in the main screen? \r\n\r\nThe error message is: " + exception.Message; } // Evaluate the query types based on the environments / radio buttons string environmentSnippet = ""; if (radioButtonPSA.Checked) { environmentSnippet = TeamConfigurationSettings.PsaDatabaseName; } else if (radioButtonIntegrationLayer.Checked) { environmentSnippet = TeamConfigurationSettings.IntegrationDatabaseName; } var queryRi = new StringBuilder(); queryRi.AppendLine("--"); queryRi.AppendLine("-- Referential Integrity Validation Query"); queryRi.AppendLine("-- Generated at " + DateTime.Now); queryRi.AppendLine("--"); queryRi.AppendLine(); #region Satellite // Satellite component queryRi.AppendLine("GO"); queryRi.AppendLine(); queryRi.AppendLine("-- Satellite validation"); queryRi.AppendLine(); var queryTableArraySat = new StringBuilder(); queryTableArraySat.AppendLine(@" SELECT DISTINCT sat.[SOURCE_SCHEMA_NAME] ,sat.[SOURCE_NAME] ,sat.[TARGET_SCHEMA_NAME] ,sat.[TARGET_NAME] ,sat.[SOURCE_BUSINESS_KEY_DEFINITION] ,sat.[TARGET_BUSINESS_KEY_DEFINITION] ,sat.[TARGET_TYPE] ,sat.[SURROGATE_KEY] ,sat.[FILTER_CRITERIA] ,sat.[LOAD_VECTOR] ,hub.[TARGET_SCHEMA_NAME] AS [HUB_SCHEMA_NAME] ,hub.[TARGET_NAME] AS [HUB_NAME] FROM [interface].[INTERFACE_SOURCE_SATELLITE_XREF] sat JOIN [interface].[INTERFACE_SOURCE_HUB_XREF] hub ON sat.[SOURCE_NAME] = hub.[SOURCE_NAME] AND sat.[TARGET_BUSINESS_KEY_DEFINITION] = hub.[TARGET_BUSINESS_KEY_DEFINITION] WHERE sat.[TARGET_TYPE]='Normal' "); var metaDataTable = Utility.GetDataTable(ref connOmd, queryTableArraySat.ToString()); if (metaDataTable.Rows.Count == 0) { richTextBoxInformationMain.Text += "There was no metadata available to create Satellite Referential Integrity scripts."; } else { foreach (DataRow row in metaDataTable.Rows) { queryRi.AppendLine("SELECT COUNT(*) AS RI_ISSUES, '" + (string)row["TARGET_NAME"] + "'"); queryRi.AppendLine("FROM [" + environmentSnippet + "].[" + (string)row["TARGET_SCHEMA_NAME"] + "].[" + (string)row["TARGET_NAME"] + "] sat"); queryRi.AppendLine("WHERE NOT EXISTS"); queryRi.AppendLine("("); queryRi.AppendLine(" SELECT 1 FROM [" + environmentSnippet + "].[" + (string)row["HUB_SCHEMA_NAME"] + "].[" + (string)row["HUB_NAME"] + "] hub WHERE sat.[" + (string)row["SURROGATE_KEY"] + "] = hub.[" + (string)row["SURROGATE_KEY"] + "]"); queryRi.AppendLine(")"); if (radioButtonDeltaValidation.Checked) { var businessKeyList = InterfaceHandling.BusinessKeyComponentMappingList((string)row["SOURCE_BUSINESS_KEY_DEFINITION"], (string)row["TARGET_BUSINESS_KEY_DEFINITION"]); var surrogateKeySnippet = new StringBuilder(); surrogateKeySnippet.AppendLine("HASHBYTES('MD5',"); foreach (var businessKey in businessKeyList) { string businessKeyEval = InterfaceHandling.EvaluateBusinessKey(businessKey); surrogateKeySnippet.AppendLine(" ISNULL(RTRIM(CONVERT(NVARCHAR(100)," + businessKeyEval + ")),'NA')+'|'+"); } surrogateKeySnippet.Remove(surrogateKeySnippet.Length - 3, 3); surrogateKeySnippet.AppendLine(); surrogateKeySnippet.AppendLine(" )"); queryRi.AppendLine("AND EXISTS"); queryRi.AppendLine("("); queryRi.AppendLine(" SELECT 1 FROM [" + TeamConfigurationSettings.StagingDatabaseName + "].[" + (string)row["SOURCE_SCHEMA_NAME"] + "].[" + (string)row["SOURCE_NAME"] + "] WHERE sat.[" + (string)row["SURROGATE_KEY"] + "] = "); queryRi.AppendLine(" " + surrogateKeySnippet); queryRi.Remove(queryRi.Length - 3, 3); queryRi.AppendLine(")"); } queryRi.AppendLine("--"); queryRi.AppendLine("UNION ALL"); queryRi.AppendLine("--"); } queryRi.Remove(queryRi.Length - 19, 19); } #endregion #region Link // Link component var queryTableArrayLink = @" SELECT [SOURCE_SCHEMA_NAME] ,[SOURCE_NAME] ,[LINK_SCHEMA_NAME] ,[LINK_NAME] ,[HUB_SCHEMA_NAME] ,[HUB_NAME] ,[HUB_SURROGATE_KEY] ,[HUB_TARGET_KEY_NAME_IN_LINK] ,[HUB_SOURCE_BUSINESS_KEY_DEFINITION] ,[HUB_TARGET_BUSINESS_KEY_DEFINITION] ,[HUB_ORDER] FROM [interface].[INTERFACE_HUB_LINK_XREF] WHERE LINK_NAME !='Not applicable' "; metaDataTable = Utility.GetDataTable(ref connOmd, queryTableArrayLink); queryRi.AppendLine(); queryRi.AppendLine("-- Link validation"); queryRi.AppendLine(); if (metaDataTable.Rows.Count == 0) { richTextBoxInformationMain.Text += "There was no metadata available to create Link Referential Integrity scripts."; } else { foreach (DataRow row in metaDataTable.Rows) { queryRi.AppendLine("SELECT COUNT(*) AS RI_ISSUES, '" + (string)row["LINK_NAME"] + "'"); queryRi.AppendLine("FROM [" + environmentSnippet + "].[" + (string)row["LINK_SCHEMA_NAME"] + "].[" + (string)row["LINK_NAME"] + "] lnk"); queryRi.AppendLine("WHERE NOT EXISTS"); queryRi.AppendLine("("); queryRi.AppendLine(" SELECT 1 FROM [" + environmentSnippet + "].[" + (string)row["HUB_SCHEMA_NAME"] + "].[" + (string)row["HUB_NAME"] + "] hub WHERE lnk.[" + (string)row["HUB_TARGET_KEY_NAME_IN_LINK"] + "] = hub.[" + (string)row["HUB_SURROGATE_KEY"] + "]"); queryRi.AppendLine(")"); if (radioButtonDeltaValidation.Checked) { var businessKeyList = InterfaceHandling.BusinessKeyComponentMappingList((string)row["HUB_SOURCE_BUSINESS_KEY_DEFINITION"], (string)row["HUB_TARGET_BUSINESS_KEY_DEFINITION"]); var surrogateKeySnippet = new StringBuilder(); surrogateKeySnippet.AppendLine("HASHBYTES('MD5',"); foreach (var businessKey in businessKeyList) { string businessKeyEval = InterfaceHandling.EvaluateBusinessKey(businessKey); surrogateKeySnippet.AppendLine(" ISNULL(RTRIM(CONVERT(NVARCHAR(100)," + businessKeyEval + ")),'NA')+'|'+"); } surrogateKeySnippet.Remove(surrogateKeySnippet.Length - 3, 3); surrogateKeySnippet.AppendLine(); surrogateKeySnippet.AppendLine(" )"); queryRi.AppendLine("AND EXISTS"); queryRi.AppendLine("("); queryRi.AppendLine(" SELECT 1 FROM [" + TeamConfigurationSettings.StagingDatabaseName + "].[" + (string)row["SOURCE_SCHEMA_NAME"] + "].[" + (string)row["SOURCE_NAME"] + "] WHERE lnk.[" + (string)row["HUB_SURROGATE_KEY"] + "] = "); queryRi.AppendLine(" " + surrogateKeySnippet); queryRi.Remove(queryRi.Length - 3, 3); queryRi.AppendLine(")"); } queryRi.AppendLine("--"); queryRi.AppendLine("UNION ALL"); queryRi.AppendLine("--"); } queryRi.Remove(queryRi.Length - 19, 19); } #endregion #region LinkSatellite // Link Satellite component queryRi.AppendLine("GO"); queryRi.AppendLine(); queryRi.AppendLine("-- Satellite validation"); queryRi.AppendLine(); var queryTableArrayLsat = new StringBuilder(); queryTableArrayLsat.AppendLine(@" SELECT DISTINCT sat.[SOURCE_SCHEMA_NAME] ,sat.[SOURCE_NAME] ,sat.[TARGET_SCHEMA_NAME] ,sat.[TARGET_NAME] ,sat.[SOURCE_BUSINESS_KEY_DEFINITION] ,sat.[TARGET_BUSINESS_KEY_DEFINITION] ,sat.[TARGET_TYPE] ,sat.[SURROGATE_KEY] ,sat.[FILTER_CRITERIA] ,sat.[LOAD_VECTOR] ,lnk.[TARGET_SCHEMA_NAME] AS [LINK_SCHEMA_NAME] ,lnk.[TARGET_NAME] AS [LINK_NAME] FROM [interface].[INTERFACE_SOURCE_SATELLITE_XREF] sat JOIN [interface].[INTERFACE_SOURCE_LINK_XREF] lnk ON sat.[SOURCE_NAME] = lnk.[SOURCE_NAME] AND sat.[TARGET_BUSINESS_KEY_DEFINITION] = lnk.[TARGET_BUSINESS_KEY_DEFINITION] WHERE sat.[TARGET_TYPE]='Link Satellite' "); metaDataTable = Utility.GetDataTable(ref connOmd, queryTableArrayLsat.ToString()); if (metaDataTable.Rows.Count == 0) { richTextBoxInformationMain.Text += "There was no metadata available to create Link Satellite Referential Integrity scripts."; } else { foreach (DataRow row in metaDataTable.Rows) { queryRi.AppendLine("SELECT COUNT(*) AS RI_ISSUES, '" + (string)row["TARGET_NAME"] + "'"); queryRi.AppendLine("FROM [" + environmentSnippet + "].[" + (string)row["TARGET_SCHEMA_NAME"] + "].[" + (string)row["TARGET_NAME"] + "] sat"); queryRi.AppendLine("WHERE NOT EXISTS"); queryRi.AppendLine("("); queryRi.AppendLine(" SELECT 1 FROM [" + environmentSnippet + "].[" + (string)row["LINK_SCHEMA_NAME"] + "].[" + (string)row["LINK_NAME"] + "] lnk WHERE sat.[" + (string)row["SURROGATE_KEY"] + "] = lnk.[" + (string)row["SURROGATE_KEY"] + "]"); queryRi.AppendLine(")"); if (radioButtonDeltaValidation.Checked) { var businessKeyList = InterfaceHandling.BusinessKeyComponentMappingList((string)row["SOURCE_BUSINESS_KEY_DEFINITION"], (string)row["TARGET_BUSINESS_KEY_DEFINITION"]); var surrogateKeySnippet = new StringBuilder(); surrogateKeySnippet.AppendLine("HASHBYTES('MD5',"); foreach (var businessKey in businessKeyList) { string businessKeyEval = InterfaceHandling.EvaluateBusinessKey(businessKey); surrogateKeySnippet.AppendLine(" ISNULL(RTRIM(CONVERT(NVARCHAR(100)," + businessKeyEval + ")),'NA')+'|'+"); } surrogateKeySnippet.Remove(surrogateKeySnippet.Length - 3, 3); surrogateKeySnippet.AppendLine(); surrogateKeySnippet.AppendLine(" )"); queryRi.AppendLine("AND EXISTS"); queryRi.AppendLine("("); queryRi.AppendLine(" SELECT 1 FROM [" + TeamConfigurationSettings.StagingDatabaseName + "].[" + (string)row["SOURCE_SCHEMA_NAME"] + "].[" + (string)row["SOURCE_NAME"] + "] WHERE sat.[" + (string)row["SURROGATE_KEY"] + "] = "); queryRi.AppendLine(" " + surrogateKeySnippet); queryRi.Remove(queryRi.Length - 3, 3); queryRi.AppendLine(")"); } queryRi.AppendLine("--"); queryRi.AppendLine("UNION ALL"); queryRi.AppendLine("--"); } queryRi.Remove(queryRi.Length - 19, 19); } #endregion richTextBoxOutput.Text = queryRi.ToString(); }
/// <summary> /// Create output using Handlebars as templating engine /// </summary> private void GenerateFromPattern() { EventLog eventLog = new EventLog(); localRichTextBoxGenerationOutput.Clear(); RaiseOnClearMainText(); localTabControl.SelectedIndex = 0; var connOmd = new SqlConnection { ConnectionString = TeamConfigurationSettings.ConnectionStringOmd }; // Populate the main list of source-to-target mappings (base query) var metadataQuery = input.LoadPatternBaseQuery; var metadataDataTable = Utility.GetDataTable(ref connOmd, metadataQuery); // Populate the attribute mappings // Create the column-to-column mapping var columnMetadataQuery = input.LoadPatternAttributeQuery; var columnMetadataDataTable = Utility.GetDataTable(ref connOmd, columnMetadataQuery); // Populate the additional business key information (i.e. links) var additionalBusinessKeyQuery = input.LoadPatternAdditionalBusinessKeyQuery; var additionalBusinessKeyDataTable = Utility.GetDataTable(ref connOmd, additionalBusinessKeyQuery); // Loop through the checked items, select the right mapping and generate the pattern if (localCheckedListBox.CheckedItems.Count != 0) { for (int x = 0; x <= localCheckedListBox.CheckedItems.Count - 1; x++) { var targetTableName = localCheckedListBox.CheckedItems[x].ToString(); localRichTextBox.AppendText(@"Processing generation for " + targetTableName + ".\r\n"); DataRow[] mappingRows = null; try { mappingRows = metadataDataTable.Select("[TARGET_NAME] = '" + targetTableName + "'"); } catch (Exception ex) { RaiseOnChangeMainText("There was an error generating the output, this happened when interpreting the source-to-mapping rows. " + "\r\n\r\nThe query used was:" + input.LoadPatternBaseQuery + ".\r\n\r\nThe error message was:" + ex); } // Move the data table to the class instance List <SourceToTargetMapping> sourceToTargetMappingList = new List <SourceToTargetMapping>(); if (mappingRows != null) { foreach (DataRow row in mappingRows) { #region Business Key // Creating the Business Key definition, using the available components (see above) List <BusinessKey> businessKeyList = new List <BusinessKey>(); BusinessKey businessKey = new BusinessKey { businessKeyComponentMapping = InterfaceHandling.BusinessKeyComponentMappingList((string)row["SOURCE_BUSINESS_KEY_DEFINITION"], (string)row["TARGET_BUSINESS_KEY_DEFINITION"]), surrogateKey = (string)row["SURROGATE_KEY"] }; businessKeyList.Add(businessKey); #endregion #region Column Mapping // Create the column-to-column mapping List <ColumnMapping> columnMappingList = new List <ColumnMapping>(); if (columnMetadataDataTable != null && columnMetadataDataTable.Rows.Count > 0) { DataRow[] columnRows = columnMetadataDataTable.Select("[TARGET_NAME] = '" + targetTableName + "' AND [SOURCE_NAME] = '" + (string)row["SOURCE_NAME"] + "'"); foreach (DataRow column in columnRows) { ColumnMapping columnMapping = new ColumnMapping(); Column sourceColumn = new Column(); Column targetColumn = new Column(); sourceColumn.columnName = (string)column["SOURCE_ATTRIBUTE_NAME"]; targetColumn.columnName = (string)column["TARGET_ATTRIBUTE_NAME"]; columnMapping.sourceColumn = sourceColumn; columnMapping.targetColumn = targetColumn; columnMappingList.Add(columnMapping); } } #endregion #region Additional Business Keys if (additionalBusinessKeyDataTable != null && additionalBusinessKeyDataTable.Rows.Count > 0) { DataRow[] additionalBusinessKeyRows = additionalBusinessKeyDataTable.Select("[LINK_NAME] = '" + targetTableName + "'"); foreach (DataRow additionalKeyRow in additionalBusinessKeyRows) { var hubBusinessKey = new BusinessKey(); hubBusinessKey.businessKeyComponentMapping = InterfaceHandling.BusinessKeyComponentMappingList((string)additionalKeyRow["HUB_SOURCE_BUSINESS_KEY_DEFINITION"], (string)additionalKeyRow["HUB_TARGET_BUSINESS_KEY_DEFINITION"]); hubBusinessKey.surrogateKey = (string)additionalKeyRow["HUB_TARGET_KEY_NAME_IN_LINK"]; businessKeyList.Add(hubBusinessKey); // Adding the Link Business Key } } #endregion #region Lookup Table // Define a lookup table, in case there is a desire to do key lookups. var lookupTable = (string)row["TARGET_NAME"]; if (TeamConfigurationSettings.TableNamingLocation == "Prefix") { int prefixLocation = lookupTable.IndexOf(TeamConfigurationSettings.StgTablePrefixValue); if (prefixLocation != -1) { lookupTable = lookupTable .Remove(prefixLocation, TeamConfigurationSettings.StgTablePrefixValue.Length) .Insert(prefixLocation, TeamConfigurationSettings.PsaTablePrefixValue); } } else { int prefixLocation = lookupTable.LastIndexOf(TeamConfigurationSettings.StgTablePrefixValue); if (prefixLocation != -1) { lookupTable = lookupTable .Remove(prefixLocation, TeamConfigurationSettings.StgTablePrefixValue.Length) .Insert(prefixLocation, TeamConfigurationSettings.PsaTablePrefixValue); } } #endregion // Add the created Business Key to the source-to-target mapping var sourceToTargetMapping = new SourceToTargetMapping(); sourceToTargetMapping.sourceTable = (string)row["SOURCE_NAME"]; // Source table sourceToTargetMapping.targetTable = (string)row["TARGET_NAME"]; // Target table sourceToTargetMapping.lookupTable = lookupTable; // Lookup Table sourceToTargetMapping.targetTableHashKey = (string)row["SURROGATE_KEY"]; // Surrogate Key sourceToTargetMapping.businessKey = businessKeyList; // Business Key sourceToTargetMapping.filterCriterion = (string)row["FILTER_CRITERIA"]; // Filter criterion sourceToTargetMapping.columnMapping = columnMappingList; // Column to column mapping // Add the source-to-target mapping to the mapping list sourceToTargetMappingList.Add(sourceToTargetMapping); } } // Create an instance of the 'MappingList' class / object model SourceToTargetMappingList sourceTargetMappingList = new SourceToTargetMappingList(); sourceTargetMappingList.individualSourceToTargetMapping = sourceToTargetMappingList; sourceTargetMappingList.metadataConfiguration = new MetadataConfiguration(); sourceTargetMappingList.mainTable = targetTableName; // Return the result to the user try { // Compile the template, and merge it with the metadata var template = Handlebars.Compile(localRichTextBoxGenerationPattern.Text); var result = template(sourceTargetMappingList); // Check if the metadata needs to be displayed if (displayJsonFlag) { try { var json = JsonConvert.SerializeObject(sourceTargetMappingList, Formatting.Indented); localRichTextBoxGenerationOutput.AppendText(json + "\r\n\r\n"); } catch (Exception ex) { RaiseOnChangeMainText("An error was encountered while generating the JSON metadata. The error message is: " + ex); } } // Display the output of the template to the user localRichTextBoxGenerationOutput.AppendText(result); // Spool the output to disk EventLog fileSaveEventLog = new EventLog(); if (saveOutputFileFlag) { fileSaveEventLog = Utility.SaveOutputToDisk(VedwConfigurationSettings.VedwOutputPath + @"\Output_" + targetTableName + ".sql", result); } //Generate in database EventLog databaseEventLog = new EventLog(); if (generateInDatabaseFlag) { var localConn = input.MatchConnectionKey(); var conn = new SqlConnection { ConnectionString = localConn[input.LoadPatternConnectionKey] }; databaseEventLog = Utility.ExecuteOutputInDatabase(conn, result); } eventLog.AddRange(fileSaveEventLog); eventLog.AddRange(databaseEventLog); } catch (Exception ex) { var localEvent = new Event { eventCode = 1, eventDescription = "The template could not be compiled, the error message is " + ex + "." }; eventLog.Add(localEvent); } } } else { localRichTextBox.AppendText($"There was no metadata selected to generate {inputNiceName} code. Please check the metadata schema - are there any {inputNiceName} objects selected?"); } connOmd.Close(); connOmd.Dispose(); // Report back to the user int errorCounter = 0; foreach (Event individualEvent in eventLog) { if (individualEvent.eventCode == 1) { errorCounter++; } RaiseOnChangeMainText(individualEvent.eventDescription); } RaiseOnChangeMainText($"\r\n\r\n{errorCounter} errors have been found.\r\n"); RaiseOnChangeMainText($"Associated scripts have been saved in {VedwConfigurationSettings.VedwOutputPath}.\r\n"); // Apply syntax highlighting SyntaxHighlight(); }