/// <summary>
/// Handles request for schema compare generate deploy script
/// </summary>
/// <returns></returns>
public async Task HandleSchemaCompareGenerateScriptRequest(SchemaCompareGenerateScriptParams parameters, RequestContext <ResultStatus> requestContext)
{
SchemaCompareGenerateScriptOperation operation = null;
try
{
SchemaComparisonResult compareResult = schemaCompareResults.Value[parameters.OperationId];
operation = new SchemaCompareGenerateScriptOperation(parameters, compareResult);
SqlTask sqlTask = null;
TaskMetadata metadata = new TaskMetadata();
metadata.TaskOperation = operation;
metadata.TaskExecutionMode = parameters.TaskExecutionMode;
metadata.ServerName = parameters.TargetServerName;
metadata.DatabaseName = parameters.TargetDatabaseName;
metadata.Name = SR.GenerateScriptTaskName;
sqlTask = SqlTaskManagerInstance.CreateAndRun <SqlTask>(metadata);
await requestContext.SendResult(new ResultStatus()
{
Success = true,
ErrorMessage = operation.ErrorMessage
});
}
catch (Exception e)
{
Logger.Write(TraceEventType.Error, "Failed to generate schema compare script. Error: " + e);
await requestContext.SendResult(new ResultStatus()
{
Success = false,
ErrorMessage = operation == null ? e.Message : operation.ErrorMessage,
});
}
}
/// <summary>
/// Handles request for schema compare generate deploy script
/// </summary>
/// <returns></returns>
public async Task HandleSchemaCompareGenerateScriptRequest(SchemaCompareGenerateScriptParams parameters, RequestContext <ResultStatus> requestContext)
{
SchemaCompareGenerateScriptOperation operation = null;
try
{
SchemaComparisonResult compareResult = schemaCompareResults.Value[parameters.OperationId];
operation = new SchemaCompareGenerateScriptOperation(parameters, compareResult);
SqlTask sqlTask = null;
TaskMetadata metadata = new TaskMetadata();
metadata.TaskOperation = operation;
// want to show filepath in task history instead of server and database
metadata.ServerName = parameters.ScriptFilePath;
metadata.DatabaseName = string.Empty;
metadata.Name = SR.GenerateScriptTaskName;
sqlTask = SqlTaskManagerInstance.CreateAndRun <SqlTask>(metadata);
await requestContext.SendResult(new ResultStatus()
{
Success = true,
ErrorMessage = operation.ErrorMessage
});
}
catch (Exception e)
{
await requestContext.SendResult(new ResultStatus()
{
Success = false,
ErrorMessage = operation == null ? e.Message : operation.ErrorMessage,
});
}
}
public SchemaCompareGenerateScriptOperation(SchemaCompareGenerateScriptParams parameters, SchemaComparisonResult comparisonResult)
{
Validate.IsNotNull("parameters", parameters);
this.Parameters = parameters;
Validate.IsNotNull("comparisonResult", comparisonResult);
this.ComparisonResult = comparisonResult;
}
private async Task <Mock <RequestContext <SchemaCompareResult> > > SendAndValidateSchemaCompareGenerateScriptRequestDacpacToDatabase()
{
var result = SchemaCompareTestUtils.GetLiveAutoCompleteTestObjects();
var schemaCompareRequestContext = new Mock <RequestContext <SchemaCompareResult> >();
schemaCompareRequestContext.Setup(x => x.SendResult(It.IsAny <SchemaCompareResult>())).Returns(Task.FromResult(new object()));
SqlTestDb sourceDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, SourceScript, "SchemaCompareSource");
SqlTestDb targetDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, TargetScript, "SchemaCompareTarget");
string folderPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "SchemaCompareTest");
Directory.CreateDirectory(folderPath);
try
{
string sourceDacpacFilePath = SchemaCompareTestUtils.CreateDacpac(sourceDb);
SchemaCompareEndpointInfo sourceInfo = new SchemaCompareEndpointInfo();
SchemaCompareEndpointInfo targetInfo = new SchemaCompareEndpointInfo();
sourceInfo.EndpointType = SchemaCompareEndpointType.Dacpac;
sourceInfo.PackageFilePath = sourceDacpacFilePath;
targetInfo.EndpointType = SchemaCompareEndpointType.Database;
targetInfo.DatabaseName = targetDb.DatabaseName;
var schemaCompareParams = new SchemaCompareParams
{
SourceEndpointInfo = sourceInfo,
TargetEndpointInfo = targetInfo
};
SchemaCompareOperation schemaCompareOperation = new SchemaCompareOperation(schemaCompareParams, result.ConnectionInfo, result.ConnectionInfo);
// generate script
var generateScriptParams = new SchemaCompareGenerateScriptParams
{
TargetDatabaseName = targetDb.DatabaseName,
OperationId = schemaCompareOperation.OperationId,
ScriptFilePath = Path.Combine(folderPath, string.Concat(sourceDb.DatabaseName, "_", "Update.publish.sql"))
};
ValidateSchemaCompareScriptGenerationWithExcludeIncludeResults(schemaCompareOperation, generateScriptParams);
// cleanup
SchemaCompareTestUtils.VerifyAndCleanup(generateScriptParams.ScriptFilePath);
SchemaCompareTestUtils.VerifyAndCleanup(sourceDacpacFilePath);
}
finally
{
sourceDb.Cleanup();
targetDb.Cleanup();
}
return(schemaCompareRequestContext);
}
private async Task <Mock <RequestContext <SchemaCompareResult> > > SendAndValidateSchemaCompareGenerateScriptRequestDatabaseToDatabase()
{
var result = SchemaCompareTestUtils.GetLiveAutoCompleteTestObjects();
var schemaCompareRequestContext = new Mock <RequestContext <SchemaCompareResult> >();
schemaCompareRequestContext.Setup(x => x.SendResult(It.IsAny <SchemaCompareResult>())).Returns(Task.FromResult(new object()));
SqlTestDb sourceDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, SourceScript, "SchemaCompareSource");
SqlTestDb targetDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, TargetScript, "SchemaCompareTarget");
try
{
SchemaCompareEndpointInfo sourceInfo = new SchemaCompareEndpointInfo();
SchemaCompareEndpointInfo targetInfo = new SchemaCompareEndpointInfo();
sourceInfo.EndpointType = SchemaCompareEndpointType.Database;
sourceInfo.DatabaseName = sourceDb.DatabaseName;
targetInfo.EndpointType = SchemaCompareEndpointType.Database;
targetInfo.DatabaseName = targetDb.DatabaseName;
var schemaCompareParams = new SchemaCompareParams
{
SourceEndpointInfo = sourceInfo,
TargetEndpointInfo = targetInfo
};
SchemaCompareOperation schemaCompareOperation = new SchemaCompareOperation(schemaCompareParams, result.ConnectionInfo, result.ConnectionInfo);
// generate script params
var generateScriptParams = new SchemaCompareGenerateScriptParams
{
TargetDatabaseName = targetDb.DatabaseName,
OperationId = schemaCompareOperation.OperationId,
};
ValidateSchemaCompareScriptGenerationWithExcludeIncludeResults(schemaCompareOperation, generateScriptParams);
}
finally
{
sourceDb.Cleanup();
targetDb.Cleanup();
}
return(schemaCompareRequestContext);
}
private void ValidateSchemaCompareScriptGenerationWithExcludeIncludeResults(SchemaCompareOperation schemaCompareOperation, SchemaCompareGenerateScriptParams generateScriptParams)
{
schemaCompareOperation.Execute(TaskExecutionMode.Execute);
Assert.True(schemaCompareOperation.ComparisonResult.IsValid);
Assert.False(schemaCompareOperation.ComparisonResult.IsEqual);
Assert.NotNull(schemaCompareOperation.ComparisonResult.Differences);
SchemaCompareGenerateScriptOperation generateScriptOperation = new SchemaCompareGenerateScriptOperation(generateScriptParams, schemaCompareOperation.ComparisonResult);
generateScriptOperation.Execute(TaskExecutionMode.Execute);
string initialScript = File.ReadAllText(generateScriptParams.ScriptFilePath);
// create Diff Entry from on Difference
DiffEntry diff = SchemaCompareOperation.CreateDiffEntry(schemaCompareOperation.ComparisonResult.Differences.First(), null);
int initial = schemaCompareOperation.ComparisonResult.Differences.Count();
SchemaCompareNodeParams schemaCompareExcludeNodeParams = new SchemaCompareNodeParams()
{
OperationId = schemaCompareOperation.OperationId,
DiffEntry = diff,
IncludeRequest = false,
TaskExecutionMode = TaskExecutionMode.Execute
};
SchemaCompareIncludeExcludeNodeOperation nodeExcludeOperation = new SchemaCompareIncludeExcludeNodeOperation(schemaCompareExcludeNodeParams, schemaCompareOperation.ComparisonResult);
nodeExcludeOperation.Execute(TaskExecutionMode.Execute);
int afterExclude = schemaCompareOperation.ComparisonResult.Differences.Count();
Assert.True(initial == afterExclude, $"Changes should be same again after excluding/including, before {initial}, now {afterExclude}");
generateScriptOperation = new SchemaCompareGenerateScriptOperation(generateScriptParams, schemaCompareOperation.ComparisonResult);
generateScriptOperation.Execute(TaskExecutionMode.Execute);
string afterExcludeScript = File.ReadAllText(generateScriptParams.ScriptFilePath);
Assert.True(initialScript.Length > afterExcludeScript.Length, $"Script should be affected (less statements) exclude operation, before {initialScript}, now {afterExcludeScript}");
SchemaCompareNodeParams schemaCompareincludeNodeParams = new SchemaCompareNodeParams()
{
OperationId = schemaCompareOperation.OperationId,
DiffEntry = diff,
IncludeRequest = true,
TaskExecutionMode = TaskExecutionMode.Execute
};
SchemaCompareIncludeExcludeNodeOperation nodeIncludeOperation = new SchemaCompareIncludeExcludeNodeOperation(schemaCompareincludeNodeParams, schemaCompareOperation.ComparisonResult);
nodeIncludeOperation.Execute(TaskExecutionMode.Execute);
int afterInclude = schemaCompareOperation.ComparisonResult.Differences.Count();
Assert.True(initial == afterInclude, $"Changes should be same again after excluding/including:{initial}, now {afterInclude}");
generateScriptOperation = new SchemaCompareGenerateScriptOperation(generateScriptParams, schemaCompareOperation.ComparisonResult);
generateScriptOperation.Execute(TaskExecutionMode.Execute);
string afterIncludeScript = File.ReadAllText(generateScriptParams.ScriptFilePath);
Assert.True(initialScript.Length == afterIncludeScript.Length, $"Changes should be same as inital since we included what we excluded, before {initialScript}, now {afterIncludeScript}");
}
private async Task <Mock <RequestContext <SchemaCompareResult> > > SendAndValidateSchemaCompareGenerateScriptRequestDacpacToDatabaseWithOptions(string sourceScript, string targetScript, DeploymentOptions nodiffOption, DeploymentOptions shouldDiffOption)
{
var result = SchemaCompareTestUtils.GetLiveAutoCompleteTestObjects();
var schemaCompareRequestContext = new Mock <RequestContext <SchemaCompareResult> >();
schemaCompareRequestContext.Setup(x => x.SendResult(It.IsAny <SchemaCompareResult>())).Returns(Task.FromResult(new object()));
SqlTestDb sourceDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, sourceScript, "SchemaCompareSource");
SqlTestDb targetDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, targetScript, "SchemaCompareTarget");
string folderPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "SchemaCompareTest");
Directory.CreateDirectory(folderPath);
try
{
string sourceDacpacFilePath = SchemaCompareTestUtils.CreateDacpac(sourceDb);
SchemaCompareEndpointInfo sourceInfo = new SchemaCompareEndpointInfo();
SchemaCompareEndpointInfo targetInfo = new SchemaCompareEndpointInfo();
sourceInfo.EndpointType = SchemaCompareEndpointType.Dacpac;
sourceInfo.PackageFilePath = sourceDacpacFilePath;
targetInfo.EndpointType = SchemaCompareEndpointType.Database;
targetInfo.DatabaseName = targetDb.DatabaseName;
var schemaCompareParams1 = new SchemaCompareParams
{
SourceEndpointInfo = sourceInfo,
TargetEndpointInfo = targetInfo,
DeploymentOptions = nodiffOption,
};
SchemaCompareOperation schemaCompareOperation1 = new SchemaCompareOperation(schemaCompareParams1, result.ConnectionInfo, result.ConnectionInfo);
schemaCompareOperation1.Execute(TaskExecutionMode.Execute);
Assert.True(schemaCompareOperation1.ComparisonResult.IsValid);
Assert.True(schemaCompareOperation1.ComparisonResult.IsEqual);
Assert.NotNull(schemaCompareOperation1.ComparisonResult.Differences);
// generate script
var generateScriptParams1 = new SchemaCompareGenerateScriptParams
{
TargetDatabaseName = targetDb.DatabaseName,
OperationId = schemaCompareOperation1.OperationId,
};
SchemaCompareGenerateScriptOperation generateScriptOperation1 = new SchemaCompareGenerateScriptOperation(generateScriptParams1, schemaCompareOperation1.ComparisonResult);
generateScriptOperation1.Execute();
// validate script generation failed because there were no differences
Assert.False(generateScriptOperation1.ScriptGenerationResult.Success);
var schemaCompareParams2 = new SchemaCompareParams
{
SourceEndpointInfo = sourceInfo,
TargetEndpointInfo = targetInfo,
DeploymentOptions = shouldDiffOption,
};
SchemaCompareOperation schemaCompareOperation2 = new SchemaCompareOperation(schemaCompareParams2, result.ConnectionInfo, result.ConnectionInfo);
schemaCompareOperation2.Execute(TaskExecutionMode.Execute);
Assert.True(schemaCompareOperation2.ComparisonResult.IsValid);
Assert.False(schemaCompareOperation2.ComparisonResult.IsEqual);
Assert.NotNull(schemaCompareOperation2.ComparisonResult.Differences);
// generate script
var generateScriptParams2 = new SchemaCompareGenerateScriptParams
{
TargetDatabaseName = targetDb.DatabaseName,
OperationId = schemaCompareOperation1.OperationId,
};
SchemaCompareGenerateScriptOperation generateScriptOperation2 = new SchemaCompareGenerateScriptOperation(generateScriptParams2, schemaCompareOperation2.ComparisonResult);
generateScriptOperation2.Execute();
// validate script generation succeeded
Assert.True(generateScriptOperation2.ScriptGenerationResult.Success);
Assert.True(!string.IsNullOrEmpty(generateScriptOperation2.ScriptGenerationResult.Script), "Should have differences");
// cleanup
SchemaCompareTestUtils.VerifyAndCleanup(sourceDacpacFilePath);
}
finally
{
sourceDb.Cleanup();
targetDb.Cleanup();
}
return(schemaCompareRequestContext);
}
