/// <summary> /// Extract source SQL code for an object from a DB (-csl param) and save it in a local file. /// </summary> /// <param name="configFile"></param> public static void ExtractScriptsFromDb(string paramCSLatest, string paramCSBase, string paramFileWithListOfItems) { // check input consistency if (string.IsNullOrEmpty(paramCSLatest)) { Program.WriteLine(); Program.WriteLine($"Connection string to a DB for script extraction is required. Use -csl param.", ConsoleColor.Red); Program.ExitApp(); } if (string.IsNullOrEmpty(paramCSBase)) { Program.WriteLine($"-csb param not supplied - extracting all objects from the list.", ConsoleColor.Yellow); } else { Program.WriteLine($"Extracting only objects that differ between -csl and -csb DBs.", ConsoleColor.Yellow); } string listOfObjectFileNames = null; // load the input file with the list of object names to extract (4-part file names) // or diff the HEAD with the very first commit to get the same list from GIT if (!string.IsNullOrEmpty(paramFileWithListOfItems)) { Program.WriteLine($"Using a supplied list of file/object names to extract: {paramFileWithListOfItems}", ConsoleColor.Yellow); listOfObjectFileNames = LoadInputFile(paramFileWithListOfItems); } else { // find the ID of the initial commit string allCommits = GetGitOutput("rev-list HEAD") ?? ""; string initialCommit = null; string[] allCommitsArray = allCommits.Split(); Array.Reverse(allCommitsArray); foreach (string commit in allCommitsArray) { if (commit.Length == 40) { // the first string from the bottom that looks like a 40-char SHA1 hash is our commit hash initialCommit = commit; break; } } // do we have the commit ID? if (string.IsNullOrEmpty(initialCommit)) { Program.WriteLine(); Program.WriteLine($"Missing initial commit ID.", ConsoleColor.Red); Program.ExitApp(); } // get the list of GIT files changed since the first commit Program.WriteLine($"Getting the list of file/object names to extract from `git diff {initialCommit} HEAD`", ConsoleColor.Yellow); listOfObjectFileNames = GetGitOutput($"diff --name-only {initialCommit} HEAD") ?? ""; // we should have some modified files here if (string.IsNullOrEmpty(listOfObjectFileNames)) { Program.WriteLine(); Program.WriteLine($"No modified files found in GIT.", ConsoleColor.Red); Program.ExitApp(); } } // process only the objects we are interested in foreach (string fileName in listOfObjectFileNames.Split()) { if (!fileName.EndsWith(".sql", StringComparison.OrdinalIgnoreCase)) { if (!string.IsNullOrWhiteSpace(fileName)) { Program.WriteLine($"Ignoring {fileName} - not an SQL script.", ConsoleColor.Yellow); } continue; // ignore any non-SQL files } // expecting a 4-part object name here, e.g. dbo.CR.StoredProcedure.sql string[] nameParts = fileName.Split('.'); if (nameParts.Length != 4) { Program.WriteLine($"Ignoring {fileName} - must be a 4-part name.", ConsoleColor.Yellow); continue; } // get the SQL from syscomments for the object to be saved string sqlTextLatest = DbAccess.GetObjectText(paramCSLatest, nameParts[1]) ?? ""; // get the SQL for the base DB object to compare to string sqlTextBase = (string.IsNullOrEmpty(paramCSBase)) ? "" : DbAccess.GetObjectText(paramCSBase, nameParts[1]) ?? ""; // write out the file if (string.IsNullOrEmpty(paramCSBase) || sqlTextLatest != sqlTextBase) { SaveExtractedScript(sqlTextLatest, fileName); } else { Program.WriteLine($"Unchanged {fileName}"); } } }
/// <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)); }