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