/// <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);
}
}
