/// <summary> /// Get a connection string for the matching DB or log an error. /// </summary> /// <param name="config"></param> /// <param name="dbName"></param> /// <returns></returns> public static string GetConnectionString(Configs.InitialConfig config, string dbName) { string regexPattern = $".*Initial Catalog={dbName};.*"; var regexOptions = RegexOptions.Multiline | RegexOptions.IgnoreCase; string cs = Regex.Match(config.connections, regexPattern, regexOptions)?.Value; if (string.IsNullOrEmpty(cs)) { cs = "connection_string_required"; // a placeholder in case the CS is missing Program.WriteLine($"{config.mirrorDB} / {dbName}: missing connection string.", ConsoleColor.Red); Program.ExitApp(); } return(cs); }
/// <summary> /// Write out blank config files for all known structures in class Configs. /// </summary> public static void GenerateBlankConfigs() { string currentDirectory = Directory.GetCurrentDirectory(); string configFolder = Path.Combine(currentDirectory, Program.FileNames.ConfigFolder); // create the folder if it doesn't exist if (!Directory.Exists(configFolder)) { Directory.CreateDirectory(configFolder); } Program.WriteLine($"Writing config files to {configFolder}"); // create the initial config file var config = new Configs.InitialConfig(); Configs.GenericConfigEntry.SaveConfigFile(Program.FileNames.InitialConfig, JsonConvert.SerializeObject(config)); // copy all templates to the local templates folder string templatesFolderDest = Path.Combine(currentDirectory, Program.FileNames.TemplatesFolder); string templatesFolderSrc = Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location), Program.FileNames.TemplatesFolder); if (!Directory.Exists(templatesFolderDest)) { Directory.CreateDirectory(templatesFolderDest); } Program.WriteLine($"Writing template files to {templatesFolderDest}"); // Copy the files and overwrite destination files if they already exist. foreach (string templateFile in Directory.GetFiles(templatesFolderSrc)) { string templateFileNoPath = Path.GetFileName(templateFile); string fileNameDest = Path.Combine(templatesFolderDest, templateFileNoPath); if (File.Exists(fileNameDest)) { Program.WriteLine($"{templateFileNoPath} already exists.", ConsoleColor.Yellow); } else { File.Copy(templateFile, fileNameDest, false); Program.WriteLine($"{templateFileNoPath} written."); } } }
/// <summary> /// Use an arbitrary template to generate an SQL script. /// </summary> /// <param name="configFile"></param> public static void GenerateScript(Configs.InitialConfig config, string templateFileName) { string templateContents = Generators.GetTemplateContents(templateFileName); // get the list of all placeholders in the template var matches = Regex.Matches(templateContents, @"{{\s*([^}]+)\s*}}"); var matchPlaceholders = new List <string>(); // contains replacement placeholders like {{loggingAzStorageContainerUrl}} // process placeholders one by one foreach (Match match in matches) { string placeholder = match.Value; if (matchPlaceholders.Contains(placeholder)) { continue; // ignore repeating placeholders } matchPlaceholders.Add(placeholder); // add it to the list so we don't process it multiple times try { // replace all instances with the value from config string matchValue = (string)config.GetType().GetField(match.Groups[1].Value).GetValue(config); templateContents = templateContents.Replace(placeholder, matchValue, StringComparison.Ordinal); } catch (Exception ex) { Program.WriteLine(); Program.WriteLine($"Variable has no matching config entry: {placeholder}.", ConsoleColor.Red); Program.ExitApp(); } } string fileSuffix = string.Format(Program.FileNames.OutputFileNameMaskRunOnMirror, config.mirrorDB, "x", "x"); string outputFileName = $"{Path.GetFileNameWithoutExtension(templateFileName)}__{fileSuffix}{fileExtSQL}"; Generators.SaveGeneratedScript(templateContents, outputFileName, 0); }
/// <summary> /// Generate a .bat file for executing all SQL scripts in the current directory. /// </summary> /// <param name="configFile"></param> public static void GenerateSqlCmdBatch(Configs.InitialConfig config, string targetDirectory, string oParam) { // check if we have the server name bool runOnAz = string.Equals(oParam, "az", StringComparison.OrdinalIgnoreCase); if (!string.IsNullOrEmpty(oParam) && !runOnAz) { Program.WriteLine(); Program.WriteLine("Param -o can be set to AZ (run on Azure) or omitted (run locally).", ConsoleColor.Red); Program.ExitApp(); } // check if we have the server name string serverName = (runOnAz)? config.serverName + ".database.windows.net" : config.localServer; if (string.IsNullOrEmpty(serverName)) { string serverParamName = (runOnAz) ? "serverName" : "localServer"; Program.WriteLine(); Program.WriteLine($"Missing `{serverParamName}` param in `/config/config.json`", ConsoleColor.Red); Program.ExitApp(); } // prepare identity (user name) for Azure string userName = (runOnAz) ? $"-U '{config.identity}'" : ""; // the target directory can be relative or absolute if (!Path.IsPathRooted(targetDirectory)) { targetDirectory = targetDirectory.TrimStart(('\\')); if (!targetDirectory.ToLower().StartsWith("scripts\\")) { targetDirectory = "scripts\\" + targetDirectory; } targetDirectory = Path.Combine(Directory.GetCurrentDirectory(), targetDirectory); } // get all files in the folder string[] fileNames = Directory.GetFiles(targetDirectory); // do not write out an empty file if (fileNames.Length == 0) { Program.WriteLine($"Empty folder: {targetDirectory}", ConsoleColor.Yellow); Program.ExitApp(2); } // loop thru the files var sb = new System.Text.StringBuilder(); foreach (string fileName in fileNames) { // skip non-.sql files if (!fileName.EndsWith(fileExtSQL)) { continue; } string fileNameOnly = Path.GetFileName(fileName); // extract the DB to run the script in from the file name var match = Regex.Match(fileNameOnly, @"^.*__([\w\d]*)__[\w\d]*__[\w\d]*", regexOptions_im); if (!match.Success || match.Groups.Count != 2) { Program.WriteLine(); Program.WriteLine($"Cannot extract semantic parts from {fileNameOnly}", ConsoleColor.Red); Program.ExitApp(); } string dbName = match.Groups[1]?.Value; sb.AppendLine($"sqlcmd -b -S \"{serverName}\" {userName} -d {dbName} -i \"{fileNameOnly}\""); sb.AppendLine($"if ($LASTEXITCODE -eq 0) {{git add \"{fileNameOnly}\"}}"); } sb.AppendLine(); // an empty line at the end to execute the last statement // output file name string batFileName = Path.Combine(targetDirectory, "apply.ps1"); // do not overwrite files for consistency if (File.Exists(batFileName)) { Program.WriteLine($"#{batFileName} already exists.", ConsoleColor.Yellow); Program.ExitApp(2); } Program.WriteLine($"Saving to {batFileName}"); // save try { File.WriteAllText(batFileName, sb.ToString(), System.Text.Encoding.ASCII); } catch (Exception ex) { Program.WriteLine(ex.Message); } }
/// <summary> /// Genrate a generic list of tables as JSON for further editing. /// </summary> /// <param name="configFile"></param> public static void GenerateSecondaryConfigFiles(Configs.InitialConfig config) { // output collections per config file List <Configs.AllTables> tableListMirror = new List <Configs.AllTables>(); // config for mirror tables List <Configs.AllTables> tableListRO = new List <Configs.AllTables>(); // config for read-only external tables List <Configs.AllTables> spList = new List <Configs.AllTables>(); // config for remote SP proxies List <Configs.CreateMasterKey> masterKeyList = new List <Configs.CreateMasterKey>(); // config for Master Key config List <Configs.CreateExternalDataSource> extDataSrcList = new List <Configs.CreateExternalDataSource>(); // config for ext data source config Configs.AllTables prevTable = new Configs.AllTables(); // a container for tracking changes // normalise line endings and remove [ ] config.masterTables ??= ""; config.masterTablesRO ??= ""; config.masterTables = config.masterTables.Replace("\r", "").Replace("[", "").Replace("]", "").Replace(" ", ""); config.masterTablesRO = config.masterTablesRO.Replace("\r", "").Replace("[", "").Replace("]", "").Replace(" ", ""); config.connections = config.connections.Replace("\r", ""); var jsonSettings = new JsonSerializerSettings() { NullValueHandling = NullValueHandling.Ignore }; // ignore null properties // combine mirror and ro lists string[] allTables = config.masterTables.Split("\n"); string[] roTables = config.masterTablesRO.Split("\n"); int mirrorCount = allTables.Length; if (roTables.Length > 0) { Array.Resize <string>(ref allTables, mirrorCount + roTables.Length); Array.Copy(roTables, 0, allTables, mirrorCount, roTables.Length); } foreach (string tableLine in allTables) { mirrorCount--; // decrement the counter to know when mirrors turn into read-only tables if (mirrorCount == -1) { prevTable = new Configs.AllTables(); // reset on switching from mirrors to ROs } // get the 3-part name like DB_STATS..TB_MANUALRESERVATION if (string.IsNullOrWhiteSpace(tableLine)) { continue; // skip empty lines } string[] tableParts = tableLine.Trim().Split("."); // check the format if (tableParts.Length != 3) { throw new Exception($"Must be a 3-part name: {tableLine}"); } if (tableParts[0].ToLower() == config.mirrorDB.ToLower()) { throw new Exception($"Found a self-reference: {tableLine}"); } // add mirror table details var tableItem = new Configs.AllTables() { masterTableOrSP = tableParts[2], masterDB = (prevTable.masterDB?.ToLower() != tableParts[0].ToLower()) ? tableParts[0] : null, mirrorDB = (prevTable.mirrorDB?.ToLower() != config.mirrorDB.ToLower()) ? config.mirrorDB : null, masterCS = (prevTable.masterDB?.ToLower() != tableParts[0].ToLower()) ? DbAccess.GetConnectionString(config, tableParts[0]) : null }; if (mirrorCount >= 0) { tableListMirror.Add(tableItem); // add to mirror collection } else { tableListRO.Add(tableItem); // add to read-only collection } prevTable.Merge(tableItem, true); // merge with overwrite // process MasterKeyConfig var masterKeyItem = new Configs.CreateMasterKey(); if (masterKeyList.Count == 0) // add full details to the first item only { masterKeyItem.password = config.password; masterKeyItem.credential = config.credential; masterKeyItem.identity = config.identity; masterKeyItem.secret = config.secret; // the very first record is actually for the mirror DB, so we have to re-initialise // not to miss the first master table from the loop masterKeyItem.localDB = config.mirrorDB; masterKeyList.Add(masterKeyItem); masterKeyItem = new Configs.CreateMasterKey(); } if (!string.IsNullOrEmpty(tableItem.masterDB)) { masterKeyItem.localDB = tableItem.masterDB; // only local db can be added automatically masterKeyList.Add(masterKeyItem); } // process ExternalDataSource config var extDataSrcItem = new Configs.CreateExternalDataSource(); if (extDataSrcList.Count == 0) // add full details to the first item only { extDataSrcItem.serverName = config.serverName; extDataSrcItem.credential = config.credential; extDataSrcItem.twoway = config.twoway; extDataSrcItem.externalDB = config.mirrorDB; } if (!string.IsNullOrEmpty(tableItem.masterDB)) { extDataSrcItem.localDB = tableItem.masterDB; // only local db can be added automatically extDataSrcList.Add(extDataSrcItem); } // check if the table exists in Master DB string tableCols = DbAccess.GetTableColumns(prevTable.masterCS, prevTable.masterTableOrSP); if (string.IsNullOrEmpty(tableCols)) { Program.WriteLine(); Program.WriteLine($"Missing table definition for {prevTable.masterDB}..{prevTable.masterTableOrSP}", ConsoleColor.Red); Program.ExitApp(); } } // process the list of SPs prevTable = new Configs.AllTables(); // restart the properties inheritance string[] masterSPs = (config.masterSPs ?? "").Split("\n"); foreach (string spLine in masterSPs) { // get the 3-part name like DB_STATS..TB_MANUALRESERVATION if (string.IsNullOrWhiteSpace(spLine)) { continue; // skip empty lines } string[] spParts = spLine.Trim().Split("."); // check the format if (spParts.Length != 3) { throw new Exception($"Must be a 3-part name: {spLine}"); } if (spParts[0].ToLower() == config.mirrorDB.ToLower()) { throw new Exception($"Found a self-reference: {spLine}"); } // add mirror table details var spItem = new Configs.AllTables() { masterTableOrSP = spParts[2], masterDB = (prevTable.masterDB?.ToLower() != spParts[0].ToLower()) ? spParts[0] : null, mirrorDB = (prevTable.mirrorDB?.ToLower() != config.mirrorDB.ToLower()) ? config.mirrorDB : null, masterCS = (prevTable.masterDB?.ToLower() != spParts[0].ToLower()) ? DbAccess.GetConnectionString(config, spParts[0]) : null }; spList.Add(spItem); // add to mirror collection prevTable.Merge(spItem, true); // merge with overwrite // process MasterKeyConfig var masterKeyItem = new Configs.CreateMasterKey(); if (masterKeyList.Count == 0) // add full details to the first item only { masterKeyItem.password = config.password; masterKeyItem.credential = config.credential; masterKeyItem.identity = config.identity; masterKeyItem.secret = config.secret; // the very first record is actually for the mirror DB, so we have to re-initialise // not to miss the first master table from the loop masterKeyItem.localDB = config.mirrorDB; masterKeyList.Add(masterKeyItem); } // process ExternalDataSource config var extDataSrcItem = new Configs.CreateExternalDataSource(); if (spList.Count == 1) // add full details to the first item only { extDataSrcItem.serverName = config.serverName; extDataSrcItem.credential = config.credential; extDataSrcItem.localDB = config.mirrorDB; } extDataSrcItem.externalDB = spItem.masterDB; extDataSrcList.Add(extDataSrcItem); // check if the SP exists in Master DB if (!DbAccess.CheckProcedureExists(prevTable.masterCS, prevTable.masterTableOrSP)) { Program.WriteLine(); Program.WriteLine($"Missing SP definition for {prevTable.masterDB}..{prevTable.masterTableOrSP}", ConsoleColor.Red); Program.ExitApp(); } } // save as files if (tableListMirror.Count > 0) { Configs.GenericConfigEntry.SaveConfigFile(Program.FileNames.TablesConfigMirror, JsonConvert.SerializeObject(tableListMirror.ToArray(), jsonSettings)); } if (tableListRO.Count > 0) { Configs.GenericConfigEntry.SaveConfigFile(Program.FileNames.TablesConfigReadOnly, JsonConvert.SerializeObject(tableListRO.ToArray(), jsonSettings)); } if (spList.Count > 0) { Configs.GenericConfigEntry.SaveConfigFile(Program.FileNames.SPsConfig, JsonConvert.SerializeObject(spList.ToArray(), jsonSettings)); } Configs.GenericConfigEntry.SaveConfigFile(Program.FileNames.MasterKeyConfig, JsonConvert.SerializeObject(masterKeyList.ToArray(), jsonSettings)); Configs.GenericConfigEntry.SaveConfigFile(Program.FileNames.ExternalDataSourceConfig, JsonConvert.SerializeObject(extDataSrcList.ToArray(), jsonSettings)); }
/// <summary> /// Replace all cross-DB refs and prepares a batch file for executing modified files with *SqlCmd* utility. /// </summary> public static void SearchAndReplace(Configs.InitialConfig config, string changeListFileName, string replacementTemplate) { // validate the grep file if (!File.Exists(changeListFileName)) { Program.WriteLine(); Program.WriteLine($"Grep file not found: {changeListFileName}.", ConsoleColor.Red); Program.ExitApp(); } string rootFolder = Path.GetDirectoryName(changeListFileName); // the input file should be in the root folder // .bat file name string batFileName = Path.GetFileNameWithoutExtension(changeListFileName) + ".ps1"; batFileName = Path.Combine(rootFolder, batFileName); // do not overwrite files for consistency if (File.Exists(batFileName)) { Program.WriteLine(); Program.WriteLine($"{batFileName} already exists.", ConsoleColor.Yellow); Program.ExitApp(2); } // check if we have the server name string serverName = config.localServer; if (string.IsNullOrEmpty(serverName)) { Program.WriteLine(); Program.WriteLine("Missing `localServer` param in `/config/config.json`", ConsoleColor.Red); Program.ExitApp(); } // list of all files and DBs to add to SQL CMD List <string> sqlFiles = new List <string>(); List <string> sqlDBs = new List <string>(); List <bool> sqlFilesCommentOut = new List <bool>(); int inLineNumber = 0; // load the change list foreach (string inLine in File.ReadAllLines(changeListFileName)) { inLineNumber++; if (string.IsNullOrWhiteSpace(inLine)) { continue; // skip empty lines } // split the string into folder, line number and SQL statement // e.g. ./citi_ip_country/dbo.GetRentalpCountryCodeByIpNumber.UserDefinedFunction.sql:18: from citi_ip_country..tb_ip p, citi_ip_country..tb_location c var match = Regex.Match(inLine, REGEX_GREP_PARTS, regexOptions_im); if (!match.Success || match.Groups.Count != 5) { Program.WriteLine(); Program.WriteLine($"Cannot extract semantic parts from line {inLineNumber}:\n {inLine}\n with {REGEX_GREP_PARTS}", ConsoleColor.Red); continue; } // get individual values string sqlFileName = match.Groups[1]?.Value; string dbNameFromFolder = match.Groups[2]?.Value; int lineNumber = (int.TryParse(match.Groups[3]?.Value, out lineNumber)) ? lineNumber - 1 : -1; string sqlStatement = match.Groups[4]?.Value; // check if any of the values are incorrect if (string.IsNullOrEmpty(sqlFileName) || string.IsNullOrEmpty(dbNameFromFolder) || string.IsNullOrEmpty(sqlStatement) || lineNumber < 0) { Program.WriteLine(); Program.WriteLine($"Cannot extract semantic parts from this line:\n {inLine}\n with {REGEX_GREP_PARTS}", ConsoleColor.Red); continue; } // extract all 3 parts from the 3-part name string threePartRegex = $@"\[?(CITI_\w*)\]?\.\[?(\w*)\]?\.\[?(\w*)\]?"; match = Regex.Match(sqlStatement, threePartRegex, regexOptions_im); if (!match.Success || match.Groups.Count != 4 || string.IsNullOrEmpty(match.Groups[1]?.Value) || string.IsNullOrEmpty(match.Groups[3]?.Value)) { Program.WriteLine(); Program.WriteLine($"Cannot extract semantic parts from line {inLineNumber}:\n {sqlStatement}\n with {threePartRegex}", ConsoleColor.Red); continue; } // schemaPart can be .. or dbo. string schemaPart = match.Groups[2]?.Value; if (string.IsNullOrEmpty(schemaPart)) { schemaPart = "dbo"; } // prepare the new SQL object name string sqlObjectNameNew = string.Format(replacementTemplate, dbNameFromFolder, match.Groups[1]?.Value, match.Groups[3]?.Value, schemaPart); string sqlStatementNew = Regex.Replace(sqlStatement, threePartRegex, sqlObjectNameNew, regexOptions_im); // log the output Program.WriteLine(); Program.WriteLine($"#{inLineNumber} {sqlFileName}"); Program.WriteLine(sqlStatement); Program.WriteLine(sqlStatementNew); // load the file and find the matching line match sqlFileName = sqlFileName.Replace("/", "\\").TrimStart(new char[] { '.', '\\' }); sqlFileName = Path.Combine(rootFolder, sqlFileName); string[] sqlLines = File.ReadAllLines(sqlFileName); // check if the line number is valid if (sqlLines.Length <= lineNumber) { Program.WriteLine(); Program.WriteLine($"Line {lineNumber + 1} is out of bounds.", ConsoleColor.Red); continue; } // check if the file line matches the new line if (sqlLines[lineNumber] == sqlStatementNew) { Program.WriteLine($"Already modified."); AddToBatFileList(sqlFileName, dbNameFromFolder, true, sqlFiles, sqlDBs, sqlFilesCommentOut); continue; } // check if the line matches the old line string canonicalSqlLine = sqlLines[lineNumber].Replace("[", "").Replace("]", "").ToLower(); string canonicalSqlStatement = sqlStatement.Replace("[", "").Replace("]", "").ToLower(); if (canonicalSqlLine != canonicalSqlStatement) { Program.WriteLine(sqlLines[lineNumber]); Program.WriteLine($"Line {lineNumber + 1} mismatch in the SQL file.", ConsoleColor.Red); continue; } // replace the line sqlLines[lineNumber] = sqlStatementNew; // write out the file File.WriteAllLines(sqlFileName, sqlLines, System.Text.Encoding.UTF8); AddToBatFileList(sqlFileName, dbNameFromFolder, false, sqlFiles, sqlDBs, sqlFilesCommentOut); } // prepare .bat file var sb = new System.Text.StringBuilder(); int rootPathLength = rootFolder.Length + 1; // loop thru the files for (int i = 0; i < sqlFiles.Count; i++) { string sqlCmdFileName = sqlFiles[i].Remove(0, rootPathLength); string gitFileName = Path.GetFileName(sqlFiles[i]); string commentOut = (sqlFilesCommentOut[i]) ? "#" : ""; sb.AppendLine($"{commentOut}sqlcmd -b -S {serverName} -d {sqlDBs[i]} -i \"{sqlCmdFileName}\""); sb.AppendLine($"{commentOut}if ($LASTEXITCODE -eq 0) {{git -C {sqlDBs[i]} add \"{gitFileName}\"}}"); } sb.AppendLine(); // an empty line at the end to execute the last statement Program.WriteLine(); Program.WriteLine($"Saving SQLCMD to {batFileName}"); // save try { File.WriteAllText(batFileName, sb.ToString(), System.Text.Encoding.ASCII); } catch (Exception ex) { Program.WriteLine(ex.Message); } }