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