public static DacpacDeltasStatus UpdateBuildRunDataForDacPacSync(ref SqlBuildRunData runData, string targetServerName, string targetDatabase, AuthenticationType authType, string userName, string password, string workingDirectory, string buildRevision, int defaultScriptTimeout, bool allowObjectDelete) { string tmpDacPacName = Path.Combine(workingDirectory, targetDatabase + ".dacpac"); if (!ExtractDacPac(targetDatabase, targetServerName, authType, userName, password, tmpDacPacName)) { return(DacpacDeltasStatus.ExtractionFailure); } string sbmFileName; var stat = CreateSbmFromDacPacDifferences(runData.PlatinumDacPacFileName, tmpDacPacName, false, buildRevision, defaultScriptTimeout, allowObjectDelete, out sbmFileName); if (stat != DacpacDeltasStatus.Success) { return(stat); } string projectFilePath = Path.GetTempPath() + Guid.NewGuid().ToString(); string projectFileName = null; string result; log.LogInformation("Preparing build package for processing"); if (!SqlBuildFileHelper.ExtractSqlBuildZipFile(sbmFileName, ref workingDirectory, ref projectFilePath, ref projectFileName, false, false, out result)) { return(DacpacDeltasStatus.SbmProcessingFailure); } SqlSyncBuildData buildData; if (!SqlBuildFileHelper.LoadSqlBuildProjectFile(out buildData, projectFileName, false)) { return(DacpacDeltasStatus.SbmProcessingFailure); } runData.BuildData = buildData; runData.BuildFileName = sbmFileName; runData.ProjectFileName = projectFileName; log.LogInformation("Build package ready"); return(DacpacDeltasStatus.Success); }
private bool ProcessSyncronizationPackages(IEnumerable <string> sbmPackages, ConnectionData toUpdate, bool runAsTrial, bool continueOnFailure) { log.LogInformation($"Starting synchronization of {toUpdate.DatabaseName} with {sbmPackages.Count()} packages..."); string projFileName = string.Empty; string projectFilePath = string.Empty; string workingDirectory = string.Empty; string result = string.Empty; foreach (var sbmPackageName in sbmPackages) { log.LogInformation($"Synchronization run for {Path.GetFileName(sbmPackageName)}"); //Unzip and read the package SqlSyncBuildData buildData; if (!SqlBuildFileHelper.ExtractSqlBuildZipFile(sbmPackageName, ref workingDirectory, ref projectFilePath, ref projFileName, out result)) { PushInfo(string.Format("Unable to extract build file {0}. See log for details", sbmPackageName)); return(false); } if (!SqlBuildFileHelper.LoadSqlBuildProjectFile(out buildData, projFileName, false)) { PushInfo(string.Format("Unable to load build file {0}. See log for details", sbmPackageName)); return(false); } //set the build data for a new run foreach (SqlSyncBuildData.ScriptRow scriptRow in buildData.Script) { scriptRow.Database = "placeholder"; scriptRow.AllowMultipleRuns = true; } List <DatabaseOverride> lstOverride = new List <DatabaseOverride>(); lstOverride.Add(new DatabaseOverride() { DefaultDbTarget = "placeholder", OverrideDbTarget = toUpdate.DatabaseName, }); //Set the run meta-data SqlSync.SqlBuild.SqlBuildRunData runData = new SqlBuildRunData() { BuildData = buildData, BuildType = BuildType.Other, BuildDescription = new Random().Next(int.MinValue, int.MaxValue).ToString(), //assign random build description StartIndex = -1000, //make sure start a the beginning ProjectFileName = projFileName, IsTrial = runAsTrial, BuildFileName = sbmPackageName, IsTransactional = true, TargetDatabaseOverrides = lstOverride }; //Execute the package SqlBuildHelper helper = new SqlBuildHelper(toUpdate, false, string.Empty, runData.IsTransactional); helper.BuildCommittedEvent += new BuildCommittedEventHandler(helper_BuildCommittedEvent); helper.BuildErrorRollBackEvent += new EventHandler(helper_BuildErrorRollBackEvent); BackgroundWorker bg = new BackgroundWorker() { WorkerReportsProgress = true, WorkerSupportsCancellation = true }; DoWorkEventArgs e = new DoWorkEventArgs(null); PushInfo(string.Format("Applying {0}", Path.GetFileName(sbmPackageName))); helper.ProcessBuild(runData, 0, bg, e); if (lastBuildSuccessful) { string message = string.Format("Successfully applied {0}", Path.GetFileName(sbmPackageName)); PushInfo(message); log.LogInformation(message); } else { string message = string.Format("Failed to apply {0}", Path.GetFileName(sbmPackageName)); PushInfo(message); log.LogInformation(message); if (!continueOnFailure) { PushInfo("Cancelling sync."); return(false); } else { PushInfo("Continue On Failure set. Continuing sync process..."); } } } return(true); }
public static bool CreateBackoutPackage(ConnectionData connData, List <SqlBuild.Objects.ObjectUpdates> objectUpdates, List <SqlBuild.Objects.ObjectUpdates> dontUpdate, List <string> manualScriptsCanNotUpdate, string sourceBuildZipFileName, string destinationBuildZipFileName, string sourceServer, string sourceDb, bool removeNewObjectsFromPackage, bool markManualScriptsAsRunOnce, bool dropNewRoutines, ref BackgroundWorker bg) { //Copy the source straight over... bool reportProgress = false; if (bg != null && bg.WorkerReportsProgress) { reportProgress = true; bg.ReportProgress(-1, "Copying package to destination..."); } if (!CopyOriginalToBackout(sourceBuildZipFileName, destinationBuildZipFileName)) { return(false); } //init working location for destination backout package string workingDir = string.Empty; string projectPath = string.Empty; string projectFileName = string.Empty; SqlBuildFileHelper.InitilizeWorkingDirectory(ref workingDir, ref projectPath, ref projectFileName); string message = $"Initialized working directory {workingDir}"; log.LogDebug(message); if (reportProgress) { bg.ReportProgress(-1, "Initialized working directory"); } //Extract destination package into working folder string result; bool success = SqlBuildFileHelper.ExtractSqlBuildZipFile(destinationBuildZipFileName, ref workingDir, ref projectPath, ref projectFileName, out result); if (success) { log.LogDebug($"Successfully extracted build file {destinationBuildZipFileName} to {workingDir}"); } else { log.LogError("Unable to proceed with Backout package. See previous errors"); return(false); } //Load the build data SqlSyncBuildData buildData; if (reportProgress) { bg.ReportProgress(-1, "Loading project file for modification."); } bool successfulLoad = SqlBuildFileHelper.LoadSqlBuildProjectFile(out buildData, projectFileName, false); if (!successfulLoad) { log.LogError("Unable to load SBM project data"); return(false); } ConnectionData tmpData = new ConnectionData(); tmpData.DatabaseName = sourceDb; tmpData.SQLServerName = sourceServer; tmpData.Password = connData.Password; tmpData.UserId = connData.UserId; tmpData.AuthenticationType = connData.AuthenticationType; ObjectScriptHelper helper = new ObjectScriptHelper(tmpData); //Get the updated scripts... if (reportProgress) { bg.ReportProgress(-1, "Generating updated scripts."); } List <UpdatedObject> lstScripts = ObjectScriptHelper.ScriptDatabaseObjects(objectUpdates, tmpData, ref bg); //Log if some scripts were not updated properly... var notUpdated = from s in objectUpdates where !(from u in lstScripts select s.ShortFileName).Contains(s.ShortFileName) select s.ShortFileName; if (notUpdated.Any()) { foreach (string file in notUpdated) { log.LogError($"Unable to create new script for {file}"); } return(false); } if (lstScripts.Count() != objectUpdates.Count()) { log.LogError($"Not all scripts were updated. Expected {lstScripts.Count().ToString()}, only {objectUpdates.Count().ToString()} were updated"); return(false); } //Save the updated scripts... DateTime updateTime = DateTime.Now; bool errorWriting = false; if (lstScripts != null) { foreach (UpdatedObject obj in lstScripts) { try { File.WriteAllText(projectPath + obj.ScriptName, obj.ScriptContents); //Update the buildData object with the update date/time and user; var sr = from r in buildData.Script where r.FileName == obj.ScriptName select r; if (sr.Any()) { SqlSyncBuildData.ScriptRow row = sr.First(); row.DateModified = updateTime; row.ModifiedBy = System.Environment.UserName; } } catch (Exception exe) { errorWriting = true; log.LogError(exe, $"Unable to save updated script file to {obj.ScriptName}"); } } } //Update new object scripts: either remove or mark as run once if (dontUpdate != null) { foreach (SqlBuild.Objects.ObjectUpdates obj in dontUpdate) { try { //Update the buildData object with the update date/time and user; var sr = from r in buildData.Script where r.FileName == obj.ShortFileName select r; if (sr.Any()) { SqlSyncBuildData.ScriptRow row = sr.First(); if (obj.ObjectType == DbScriptDescription.StoredProcedure || obj.ObjectType == DbScriptDescription.UserDefinedFunction || obj.ObjectType == DbScriptDescription.Trigger || obj.ObjectType == DbScriptDescription.View) { if (dropNewRoutines) { string schema, objName; string[] arr = obj.SourceObject.Split(new char[] { '.' }); schema = arr[0]; objName = arr[1]; string str = CreateRoutineDropScript(schema, objName, obj.ObjectType); File.WriteAllText(projectPath + "DROP " + row.FileName, str); row.FileName = "DROP " + row.FileName; row.DateModified = updateTime; row.ModifiedBy = System.Environment.UserName; } else { row.AllowMultipleRuns = false; row.DateModified = updateTime; row.ModifiedBy = System.Environment.UserName; } } else if (removeNewObjectsFromPackage) { buildData.Script.RemoveScriptRow(row); } else { row.AllowMultipleRuns = false; row.DateModified = updateTime; row.ModifiedBy = System.Environment.UserName; } } } catch (Exception exe) { errorWriting = true; if (removeNewObjectsFromPackage) { log.LogError(exe, $"Unable to remove new object script '{obj.ShortFileName}' from package"); } else { log.LogError(exe, $"Unable to mark new object script {obj.ShortFileName} as run once"); } } } } //Mark non-updated scripts as run-once if (manualScriptsCanNotUpdate != null) { foreach (string scr in manualScriptsCanNotUpdate) { try { //Update the buildData object with the update date/time and user; var sr = from r in buildData.Script where r.FileName == scr select r; if (sr.Any()) { if (markManualScriptsAsRunOnce) { SqlSyncBuildData.ScriptRow row = sr.First(); if (row.BuildOrder < 1000) { row.AllowMultipleRuns = false; row.DateModified = updateTime; row.ModifiedBy = System.Environment.UserName; } } } } catch (Exception exe) { errorWriting = true; log.LogError(exe, $"Unable to mark script {scr} as run once"); } } } if (errorWriting) { return(false); } if (reportProgress) { bg.ReportProgress(-1, "Saving backout package."); } buildData.AcceptChanges(); SqlBuildFileHelper.SaveSqlBuildProjectFile(ref buildData, projectFileName, destinationBuildZipFileName); return(true); }
/// <summary> /// This method should only really be used with a command line, unattended execution. /// </summary> /// <param name="connData"></param> /// <param name="objectUpdates"></param> /// <param name="dontUpdate"></param> /// <param name="manualScriptsCanNotUpdate"></param> /// <param name="sourceBuildZipFileName"></param> /// <param name="sourceServer"></param> /// <param name="sourceDb"></param> /// <returns></returns> public static string CreateDefaultBackoutPackage(ConnectionData connData, string sourceBuildZipFileName, string sourceServer, string sourceDb) { /*How to create a backout package: * * 1. Extract the package and load the BuildData * 2. Get the list of sriptable objects and manually created scripts * 3. Targeting your "old" source, and see what scriptable objects are not there (i.e. are "new" in the package) * */ List <string> manualScriptsCanNotUpdate; List <ObjectUpdates> initialCanUpdateList; string workingDirectory = string.Empty; string projectFilePath = string.Empty; string projectFileName = string.Empty; string result; SqlSyncBuildData buildData; BackgroundWorker bg = new BackgroundWorker(); bg.WorkerReportsProgress = true; //Extract and load the build data... log.LogDebug($"Extracting SBM zip file for {sourceBuildZipFileName}"); bool success = SqlBuildFileHelper.ExtractSqlBuildZipFile(sourceBuildZipFileName, ref workingDirectory, ref projectFilePath, ref projectFileName, out result); if (success) { log.LogDebug($"Loading SqlSyncBuldData object from {projectFileName}"); success = SqlBuildFileHelper.LoadSqlBuildProjectFile(out buildData, projectFileName, false); if (!success) { return(string.Empty); } } else { return(string.Empty); } //Get the scriptable objects log.LogDebug($"Getting the scriptable objects from {sourceBuildZipFileName}"); SqlBuildFileHelper.GetFileDataForObjectUpdates(ref buildData, projectFileName, out initialCanUpdateList, out manualScriptsCanNotUpdate); //Get object that are also on the target (ie are "existing") -- only these will be updated log.LogDebug($"Getting list of objects can be rolled back from {sourceServer}:{sourceDb}"); List <ObjectUpdates> canUpdate = GetObjectThatCanBeUpdated(initialCanUpdateList, connData, sourceServer, sourceDb); SetBackoutSourceDatabaseAndServer(ref canUpdate, sourceServer, sourceDb); //Get the scriptable objects that are not found on the target (i.e. are "new") -- these will be dropped log.LogDebug($"Getting list of objects can not be rolled back from {sourceServer}:{sourceDb}"); List <ObjectUpdates> notPresentOnTarget = GetObjectsNotPresentTargetDatabase(initialCanUpdateList, connData, sourceServer, sourceDb); //Get the name of the new package string backoutPackageName = GetDefaultPackageName(sourceBuildZipFileName); //Create the package!! log.LogDebug($"Creating backout package {backoutPackageName} from source package {sourceBuildZipFileName}"); success = CreateBackoutPackage(connData, canUpdate, notPresentOnTarget, manualScriptsCanNotUpdate, sourceBuildZipFileName, backoutPackageName, sourceServer, sourceDb, true, true, true, ref bg); //Cleanup all the temp files created SqlBuildFileHelper.CleanUpAndDeleteWorkingDirectory(workingDirectory); if (!success) { log.LogError("Unable to create backout package!"); return(string.Empty); } return(backoutPackageName); }
public List <string[]> CommandLinePolicyCheck(string buildPackageName, out bool passed) { string highSeverity = ViolationSeverity.High.ToString(); passed = true; List <string[]> policyReturns = new List <string[]>(); SqlSyncBuildData buildData = null; if (String.IsNullOrEmpty(buildPackageName)) { return(policyReturns); } string projFileName = string.Empty; string projectFilePath = string.Empty; string workingDirectory = string.Empty; string extension = Path.GetExtension(buildPackageName).ToLower(); switch ((extension)) { case ".sbm": string result; SqlBuildFileHelper.ExtractSqlBuildZipFile(buildPackageName, ref workingDirectory, ref projectFilePath, ref projFileName, out result); SqlBuildFileHelper.LoadSqlBuildProjectFile(out buildData, projFileName, false); break; case ".sbx": projectFilePath = Path.GetDirectoryName(buildPackageName); SqlBuildFileHelper.LoadSqlBuildProjectFile(out buildData, buildPackageName, false); break; default: return(policyReturns); } if (buildData != null) { Package pkg = CreateScriptPolicyPackage(buildData, projectFilePath); passed = !pkg.Select(p => p.Violations.Select(v => v.Severity == highSeverity)).Any(); var violationMessages = from s in pkg from v in s.Violations select new { v.Severity, s.ScriptName, v.Message }; foreach (var violation in violationMessages) { policyReturns.Add(new string[] { violation.Severity, violation.ScriptName, violation.Message }); //string message = string.Format($"Severity [{violation.Severity}]; Script: {violation.ScriptName}; Message: {violation.Message}"); ////Add messages to log //if (violation.Severity == ViolationSeverity.High.ToString()) // log.LogError(message); //else if(violation.Severity == ViolationSeverity.Medium.ToString()) // log.LogWarning(message); //else // log.LogInformation(message); } return(policyReturns); } else { return(policyReturns); } }