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