public async Task ScriptDatabaseSchemaAndData()
{
using (TestServiceDriverProvider testService = new TestServiceDriverProvider())
using (SelfCleaningTempFile tempFile = new SelfCleaningTempFile())
{
ScriptingParams requestParams = new ScriptingParams
{
FilePath = tempFile.FilePath,
ConnectionString = this.Northwind.ConnectionString,
ScriptOptions = new ScriptOptions
{
TypeOfDataToScript = "SchemaAndData",
},
};
ScriptingResult result = await testService.Script(requestParams);
ScriptingPlanNotificationParams planEvent = await testService.Driver.WaitForEvent(ScriptingPlanNotificationEvent.Type, TimeSpan.FromSeconds(30));
ScriptingCompleteParams completeParameters = await testService.Driver.WaitForEvent(ScriptingCompleteEvent.Type, TimeSpan.FromSeconds(30));
Assert.True(completeParameters.Success);
Assert.Equal <int>(ScriptingFixture.ObjectCountWithDatabase, planEvent.Count);
Assert.True(File.Exists(tempFile.FilePath));
Assert.True(new FileInfo(tempFile.FilePath).Length > 0);
AssertSchemaInFile(tempFile.FilePath, assert: true);
AssertTableDataInFile(tempFile.FilePath, assert: true);
}
}
public async Task ScriptSelectTable()
{
using (TestServiceDriverProvider testService = new TestServiceDriverProvider())
using (SelfCleaningTempFile tempFile = new SelfCleaningTempFile())
{
await testService.Connect(tempFile.FilePath, testService.TestConnectionService.GetConnectionParameters(serverType: TestServerType.OnPrem));
ScriptingParams requestParams = new ScriptingParams
{
ScriptDestination = "ToEditor",
OwnerUri = tempFile.FilePath,
ScriptOptions = new ScriptOptions
{
ScriptCreateDrop = "ScriptSelect"
},
ScriptingObjects = new List <ScriptingObject>
{
new ScriptingObject
{
Type = "Table",
Schema = "dbo",
Name = "Customers",
}
}
};
ScriptingResult result = await testService.Script(requestParams);
Assert.True(result.Script.Contains("SELECT"));
}
}
public async Task ScriptTableDoesNotExist()
{
using (TestServiceDriverProvider testService = new TestServiceDriverProvider())
using (SelfCleaningTempFile tempFile = new SelfCleaningTempFile())
{
ScriptingParams requestParams = new ScriptingParams
{
FilePath = tempFile.FilePath,
ConnectionString = this.Northwind.ConnectionString,
ScriptOptions = new ScriptOptions
{
TypeOfDataToScript = "SchemaOnly",
},
ScriptingObjects = new List <ScriptingObject>
{
new ScriptingObject
{
Type = "Table",
Schema = "dbo",
Name = "TableDoesNotExist",
},
}
};
ScriptingResult result = await testService.Script(requestParams);
ScriptingCompleteParams parameters = await testService.Driver.WaitForEvent(ScriptingCompleteEvent.Type, TimeSpan.FromSeconds(15));
Assert.True(parameters.HasError);
Assert.Equal("An error occurred while scripting the objects.", parameters.ErrorMessage);
Assert.Contains("The Table '[dbo].[TableDoesNotExist]' does not exist on the server.", parameters.ErrorDetails);
}
}
public ScriptAsScriptingOperation(ScriptingParams parameters) : base(parameters)
{
SqlConnection sqlConnection = new SqlConnection(this.Parameters.ConnectionString);
ServerConnection = new ServerConnection(sqlConnection);
disconnectAtDispose = true;
}
private async Task <Mock <RequestContext <ScriptingResult> > > SendAndValidateScriptRequest(bool isSelectScript)
{
var result = GetLiveAutoCompleteTestObjects();
var requestContext = new Mock <RequestContext <ScriptingResult> >();
requestContext.Setup(x => x.SendResult(It.IsAny <ScriptingResult>())).Returns(Task.FromResult(new object()));
var scriptingParams = new ScriptingParams
{
OwnerUri = ConnectionService.BuildConnectionString(result.ConnectionInfo.ConnectionDetails)
};
if (isSelectScript)
{
scriptingParams.ScriptOptions = new ScriptOptions {
ScriptCreateDrop = "ScriptSelect"
};
List <ScriptingObject> scriptingObjects = new List <ScriptingObject>();
scriptingObjects.Add(new ScriptingObject {
Type = "View", Name = "sysobjects", Schema = "sys"
});
scriptingParams.ScriptingObjects = scriptingObjects;
}
ScriptingService service = new ScriptingService();
await service.HandleScriptExecuteRequest(scriptingParams, requestContext.Object);
return(requestContext);
}
public async Task ScriptTable()
{
using (TestServiceDriverProvider testService = new TestServiceDriverProvider())
{
ScriptingParams requestParams = new ScriptingParams
{
ScriptDestination = "ToEditor",
ConnectionString = this.Northwind.ConnectionString,
ScriptOptions = new ScriptOptions
{
TypeOfDataToScript = "SchemaOnly",
},
ScriptingObjects = new List <ScriptingObject>
{
new ScriptingObject
{
Type = "Table",
Schema = "dbo",
Name = "Customers",
},
}
};
ScriptingResult result = await testService.Script(requestParams);
ScriptingPlanNotificationParams planEvent = await testService.Driver.WaitForEvent(ScriptingPlanNotificationEvent.Type, TimeSpan.FromSeconds(1));
ScriptingCompleteParams parameters = await testService.Driver.WaitForEvent(ScriptingCompleteEvent.Type, TimeSpan.FromSeconds(30));
Assert.True(parameters.Success);
Assert.Equal <int>(1, planEvent.Count);
}
}
/// <summary>
/// Runs the async task that performs the scripting operation.
/// </summary>
private void RunSelectTask(ConnectionInfo connInfo, ScriptingParams parameters, RequestContext <ScriptingResult> requestContext)
{
ConnectionServiceInstance.ConnectionQueue.QueueBindingOperation(
key: ConnectionServiceInstance.ConnectionQueue.AddConnectionContext(connInfo, "Scripting"),
bindingTimeout: ScriptingOperationTimeout,
bindOperation: (bindingContext, cancelToken) =>
{
string script = string.Empty;
ScriptingObject scriptingObject = parameters.ScriptingObjects[0];
try
{
Server server = new Server(bindingContext.ServerConnection);
server.DefaultTextMode = true;
// build object URN
SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder(parameters.ConnectionString);
Urn objectUrn = BuildScriptingObjectUrn(server, connectionStringBuilder, scriptingObject);
string typeName = objectUrn.GetNameForType(scriptingObject.Type);
// select from service broker
if (string.Compare(typeName, "ServiceBroker", StringComparison.CurrentCultureIgnoreCase) == 0)
{
script = Scripter.SelectAllValuesFromTransmissionQueue(objectUrn);
}
// select from queues
else if (string.Compare(typeName, "Queues", StringComparison.CurrentCultureIgnoreCase) == 0 ||
string.Compare(typeName, "SystemQueues", StringComparison.CurrentCultureIgnoreCase) == 0)
{
script = Scripter.SelectAllValues(objectUrn);
}
// select from table or view
else
{
Database db = server.Databases[connectionStringBuilder.InitialCatalog];
bool isDw = db.IsSqlDw;
script = new Scripter().SelectFromTableOrView(server, objectUrn, isDw);
}
// send script result to client
requestContext.SendResult(new ScriptingResult {
Script = script
}).Wait();
}
catch (Exception e)
{
requestContext.SendError(e).Wait();
}
return(null);
});
}
private async Task VerifyScriptAs(string query, ScriptingObject scriptingObject, string scriptCreateDrop, string expectedScript)
{
var testDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, query, "ScriptingTests");
try
{
var requestContext = new Mock <RequestContext <ScriptingResult> >();
requestContext.Setup(x => x.SendResult(It.IsAny <ScriptingResult>())).Returns(Task.FromResult(new object()));
ConnectionService connectionService = LiveConnectionHelper.GetLiveTestConnectionService();
using (SelfCleaningTempFile queryTempFile = new SelfCleaningTempFile())
{
//Opening a connection to db to lock the db
TestConnectionResult connectionResult = await LiveConnectionHelper.InitLiveConnectionInfoAsync(testDb.DatabaseName, queryTempFile.FilePath, ConnectionType.Default);
var scriptingParams = new ScriptingParams
{
OwnerUri = queryTempFile.FilePath,
ScriptDestination = "ToEditor"
};
scriptingParams.ScriptOptions = new ScriptOptions
{
ScriptCreateDrop = scriptCreateDrop,
};
scriptingParams.ScriptingObjects = new List <ScriptingObject>
{
scriptingObject
};
ScriptingService service = new ScriptingService();
await service.HandleScriptingScriptAsRequest(scriptingParams, requestContext.Object);
Thread.Sleep(2000);
await service.ScriptingTask;
requestContext.Verify(x => x.SendResult(It.Is <ScriptingResult>(r => VerifyScriptingResult(r, expectedScript))));
connectionService.Disconnect(new ServiceLayer.Connection.Contracts.DisconnectParams
{
OwnerUri = queryTempFile.FilePath
});
}
}
catch
{
throw;
}
finally
{
await testDb.CleanupAsync();
}
}
/// <summary>
/// Wrapper method that calls Resolver.FindCompletions
/// </summary>
/// <param name="objectName"></param>
/// <param name="schemaName"></param>
/// <param name="objectType"></param>
/// <param name="tempFileName"></param>
/// <returns></returns>
internal SmoScriptingOperation InitScriptOperation(string objectName, string schemaName, string objectType)
{
// object that has to be scripted
ScriptingObject scriptingObject = new ScriptingObject
{
Name = objectName,
Schema = schemaName,
Type = objectType
};
// scripting options
ScriptOptions options = new ScriptOptions
{
ScriptCreateDrop = "ScriptCreate",
TypeOfDataToScript = "SchemaOnly",
ScriptStatistics = "ScriptStatsNone",
TargetDatabaseEngineEdition = GetTargetDatabaseEngineEdition(),
TargetDatabaseEngineType = GetTargetDatabaseEngineType(),
ScriptCompatibilityOption = GetScriptCompatibilityOption(),
ScriptExtendedProperties = false,
ScriptUseDatabase = false,
IncludeIfNotExists = false,
GenerateScriptForDependentObjects = false,
IncludeDescriptiveHeaders = false,
ScriptCheckConstraints = false,
ScriptChangeTracking = false,
ScriptDataCompressionOptions = false,
ScriptForeignKeys = false,
ScriptFullTextIndexes = false,
ScriptIndexes = false,
ScriptPrimaryKeys = false,
ScriptTriggers = false,
UniqueKeys = false
};
List <ScriptingObject> objectList = new List <ScriptingObject>();
objectList.Add(scriptingObject);
// create parameters for the scripting operation
ScriptingParams parameters = new ScriptingParams
{
ConnectionString = ConnectionService.BuildConnectionString(this.connectionInfo.ConnectionDetails),
ScriptingObjects = objectList,
ScriptOptions = options,
ScriptDestination = "ToEditor"
};
return(new ScriptAsScriptingOperation(parameters, serverConnection));
}
/// <summary>
/// Handles request to start the scripting operation
/// </summary>
public async Task HandleScriptExecuteRequest(ScriptingParams parameters, RequestContext <ScriptingResult> requestContext)
{
SmoScriptingOperation operation = null;
try
{
// if a connection string wasn't provided as a parameter then
// use the owner uri property to lookup its associated ConnectionInfo
// and then build a connection string out of that
ConnectionInfo connInfo = null;
string accessToken = null;
if (parameters.ConnectionString == null)
{
ScriptingService.ConnectionServiceInstance.TryFindConnection(parameters.OwnerUri, out connInfo);
if (connInfo != null)
{
parameters.ConnectionString = ConnectionService.BuildConnectionString(connInfo.ConnectionDetails);
accessToken = connInfo.ConnectionDetails.AzureAccountToken;
}
else
{
throw new Exception("Could not find ConnectionInfo");
}
}
if (parameters.FilePath == null)
{
// Create a temporary and random path to handle this operation
parameters.FilePath = Path.GetTempFileName();
}
if (!ShouldCreateScriptAsOperation(parameters))
{
operation = new ScriptingScriptOperation(parameters, accessToken);
}
else
{
operation = new ScriptAsScriptingOperation(parameters, accessToken);
}
operation.PlanNotification += (sender, e) => requestContext.SendEvent(ScriptingPlanNotificationEvent.Type, e).Wait();
operation.ProgressNotification += (sender, e) => requestContext.SendEvent(ScriptingProgressNotificationEvent.Type, e).Wait();
operation.CompleteNotification += (sender, e) => this.SendScriptingCompleteEvent(requestContext, ScriptingCompleteEvent.Type, e, operation, parameters.ScriptDestination);
RunTask(requestContext, operation);
}
catch (Exception e)
{
await requestContext.SendError(e);
}
}
public ScriptAsScriptingOperation(ScriptingParams parameters, string azureAccountToken) : base(parameters)
{
SqlConnection sqlConnection = new SqlConnection(this.Parameters.ConnectionString);
if (azureAccountToken != null)
{
sqlConnection.AccessToken = azureAccountToken;
}
ServerConnection = new ServerConnection(sqlConnection);
if (azureAccountToken != null)
{
ServerConnection.AccessToken = new AzureAccessToken(azureAccountToken);
}
disconnectAtDispose = true;
}
private bool ShouldCreateScriptAsOperation(ScriptingParams parameters)
{
// Scripting as operation should be used to script one object.
// Scripting data and scripting to file is not supported by scripting as operation
// To script Select, alter and execute use scripting as operation. The other operation doesn't support those types
if ((parameters.ScriptingObjects != null && parameters.ScriptingObjects.Count == 1 && parameters.ScriptOptions != null &&
parameters.ScriptOptions.TypeOfDataToScript == "SchemaOnly" && parameters.ScriptDestination == "ToEditor") ||
parameters.Operation == ScriptingOperationType.Select || parameters.Operation == ScriptingOperationType.Execute ||
parameters.Operation == ScriptingOperationType.Alter)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Handles request to execute start the script operation.
/// </summary>
public async Task HandleScriptExecuteRequest(ScriptingParams parameters, RequestContext <ScriptingResult> requestContext)
{
try
{
ScriptingScriptOperation operation = new ScriptingScriptOperation(parameters);
operation.PlanNotification += (sender, e) => this.SendEvent(requestContext, ScriptingPlanNotificationEvent.Type, e);
operation.ProgressNotification += (sender, e) => this.SendEvent(requestContext, ScriptingProgressNotificationEvent.Type, e);
operation.CompleteNotification += (sender, e) => this.SendEvent(requestContext, ScriptingCompleteEvent.Type, e);
RunTask(requestContext, operation);
await requestContext.SendResult(new ScriptingResult { OperationId = operation.OperationId });
}
catch (Exception e)
{
await requestContext.SendError(e);
}
}
/// <summary>
/// Handles request to start the scripting operation
/// </summary>
public async Task HandleScriptExecuteRequest(ScriptingParams parameters, RequestContext <ScriptingResult> requestContext)
{
try
{
// if a connection string wasn't provided as a parameter then
// use the owner uri property to lookup its associated ConnectionInfo
// and then build a connection string out of that
ConnectionInfo connInfo = null;
if (parameters.ConnectionString == null || parameters.ScriptOptions.ScriptCreateDrop == "ScriptSelect")
{
ScriptingService.ConnectionServiceInstance.TryFindConnection(parameters.OwnerUri, out connInfo);
if (connInfo != null)
{
parameters.ConnectionString = ConnectionService.BuildConnectionString(connInfo.ConnectionDetails);
}
else
{
throw new Exception("Could not find ConnectionInfo");
}
}
// if the scripting operation is for SELECT then handle that message differently
// for SELECT we'll build the SQL directly whereas other scripting operations depend on SMO
if (parameters.ScriptOptions.ScriptCreateDrop == "ScriptSelect")
{
RunSelectTask(connInfo, parameters, requestContext);
}
else
{
ScriptingScriptOperation operation = new ScriptingScriptOperation(parameters);
operation.PlanNotification += (sender, e) => requestContext.SendEvent(ScriptingPlanNotificationEvent.Type, e).Wait();
operation.ProgressNotification += (sender, e) => requestContext.SendEvent(ScriptingProgressNotificationEvent.Type, e).Wait();
operation.CompleteNotification += (sender, e) => this.SendScriptingCompleteEvent(requestContext, ScriptingCompleteEvent.Type, e, operation, parameters.ScriptDestination);
RunTask(requestContext, operation);
}
}
catch (Exception e)
{
await requestContext.SendError(e);
}
}
public async Task ScriptDatabaseSchemaAndData()
{
using (TestServiceDriverProvider testService = new TestServiceDriverProvider())
{
ScriptingParams requestParams = new ScriptingParams
{
ScriptDestination = "ToEditor",
ConnectionString = this.Northwind.ConnectionString,
ScriptOptions = new ScriptOptions
{
TypeOfDataToScript = "SchemaAndData",
},
};
ScriptingResult result = await testService.Script(requestParams);
ScriptingCompleteParams completeParameters = await testService.Driver.WaitForEvent(ScriptingCompleteEvent.Type, TimeSpan.FromSeconds(30));
Assert.True(completeParameters.Success);
}
}
private async Task VerifyScriptTable(TestServerType serverType, [CallerMemberName] string testName = "")
{
await TestServiceDriverProvider.RunTestIterations(async (timer) =>
{
using (TestServiceDriverProvider testService = new TestServiceDriverProvider())
using (SelfCleaningTempFile queryTempFile = new SelfCleaningTempFile())
{
await testService.ConnectForQuery(serverType, string.Empty, queryTempFile.FilePath, Common.PerfTestDatabaseName);
List <ScriptingObject> scriptingObjects = new List <ScriptingObject>()
{
new ScriptingObject
{
Schema = "Person",
Name = "Address",
Type = "Table"
}
};
ScriptingParams scriptingParams = new ScriptingParams
{
OwnerUri = queryTempFile.FilePath,
Operation = ScriptingOperationType.Create,
FilePath = queryTempFile.FilePath,
ScriptOptions = new ScriptOptions
{
ScriptCreateDrop = "ScriptCreate",
},
ScriptDestination = "ToEditor",
ScriptingObjects = scriptingObjects
};
var result = await testService.CalculateRunTime(() => testService.RequestScript(scriptingParams), timer);
Assert.NotNull(result);
Assert.NotNull(result.Script);
Assert.False(string.IsNullOrEmpty(result.Script), "Script result is invalid");
await testService.Disconnect(queryTempFile.FilePath);
}
}, testName);
}
public async Task ScriptSchemaInvalidFilePath()
{
using (TestServiceDriverProvider testService = new TestServiceDriverProvider())
{
ScriptingParams requestParams = new ScriptingParams
{
FilePath = "This path doesn't event exist",
ConnectionString = "Server=Temp;Database=Temp;User Id=Temp;Password=Temp",
ScriptOptions = new ScriptOptions
{
TypeOfDataToScript = "SchemaAndData",
},
};
ScriptingResult result = await testService.Script(requestParams);
ScriptingCompleteParams parameters = await testService.Driver.WaitForEvent(ScriptingCompleteEvent.Type, TimeSpan.FromSeconds(10));
Assert.True(parameters.HasError);
Assert.Equal("Invalid directory specified by the ScriptingParams.FilePath property.", parameters.ErrorMessage);
}
}
public async Task ScriptSchemaInvalidConnectionString()
{
using (TestServiceDriverProvider testService = new TestServiceDriverProvider())
{
ScriptingParams requestParams = new ScriptingParams
{
ScriptDestination = "ToEditor",
ConnectionString = "I'm an invalid connection string",
ScriptOptions = new ScriptOptions
{
TypeOfDataToScript = "SchemaAndData",
},
};
ScriptingResult result = await testService.Script(requestParams);
ScriptingCompleteParams parameters = await testService.Driver.WaitForEvent(ScriptingCompleteEvent.Type, TimeSpan.FromSeconds(10));
Assert.True(parameters.HasError);
Assert.Equal("Error parsing ScriptingParams.ConnectionString property.", parameters.ErrorMessage);
}
}
public async void ScriptSchemaCancel()
{
using (TestServiceDriverProvider testService = new TestServiceDriverProvider())
{
ScriptingParams requestParams = new ScriptingParams
{
ScriptDestination = "ToEditor",
ConnectionString = this.Northwind.ConnectionString,
ScriptOptions = new ScriptOptions
{
TypeOfDataToScript = "SchemaAndData",
}
};
var result = Task.Run(() => testService.Script(requestParams));
ScriptingProgressNotificationParams progressParams = await testService.Driver.WaitForEvent(ScriptingProgressNotificationEvent.Type, TimeSpan.FromSeconds(10));
Task.Run(() => testService.CancelScript(progressParams.OperationId));
ScriptingCompleteParams cancelEvent = await testService.Driver.WaitForEvent(ScriptingCompleteEvent.Type, TimeSpan.FromSeconds(10));
Assert.True(cancelEvent.Canceled);
}
}
public async Task ScriptSchemaCancel()
{
using (TestServiceDriverProvider testService = new TestServiceDriverProvider())
using (SelfCleaningTempFile tempFile = new SelfCleaningTempFile())
{
ScriptingParams requestParams = new ScriptingParams
{
FilePath = tempFile.FilePath,
ConnectionString = this.Northwind.ConnectionString,
ScriptOptions = new ScriptOptions
{
TypeOfDataToScript = "SchemaAndData",
},
};
ScriptingResult result = await testService.Script(requestParams);
ScriptingCancelResult cancelResult = await testService.CancelScript(result.OperationId);
ScriptingCompleteParams cancelEvent = await testService.Driver.WaitForEvent(ScriptingCompleteEvent.Type, TimeSpan.FromSeconds(10));
Assert.True(cancelEvent.Canceled);
}
}
public async Task ScriptTableAndData()
{
using (TestServiceDriverProvider testService = new TestServiceDriverProvider())
using (SelfCleaningTempFile tempFile = new SelfCleaningTempFile())
{
ScriptingParams requestParams = new ScriptingParams
{
FilePath = tempFile.FilePath,
ConnectionString = this.Northwind.ConnectionString,
ScriptOptions = new ScriptOptions
{
TypeOfDataToScript = "SchemaAndData",
},
ScriptingObjects = new List <ScriptingObject>
{
new ScriptingObject
{
Type = "Table",
Schema = "dbo",
Name = "Customers",
},
}
};
ScriptingResult result = await testService.Script(requestParams);
ScriptingPlanNotificationParams planEvent = await testService.Driver.WaitForEvent(ScriptingPlanNotificationEvent.Type, TimeSpan.FromSeconds(30));
ScriptingCompleteParams parameters = await testService.Driver.WaitForEvent(ScriptingCompleteEvent.Type, TimeSpan.FromSeconds(30));
Assert.True(parameters.Success);
Assert.Equal <int>(1, planEvent.Count);
Assert.True(File.Exists(tempFile.FilePath));
Assert.True(new FileInfo(tempFile.FilePath).Length > 0);
}
}
public ScriptingScriptOperation(ScriptingParams parameters, string azureAccessToken) : base(parameters)
{
this.azureAccessToken = azureAccessToken;
}
public ScriptAsScriptingOperation(ScriptingParams parameters, ServerConnection serverConnection) : base(parameters)
{
Validate.IsNotNull("serverConnection", serverConnection);
ServerConnection = serverConnection;
}
public ScriptingScriptOperation(ScriptingParams parameters)
{
Validate.IsNotNull("parameters", parameters);
this.Parameters = parameters;
}
public ScriptAsScriptingOperation(ScriptingParams parameters) : base(parameters)
{
}
public async Task <ScriptingResult> Script(ScriptingParams parameters)
{
return(await Driver.SendRequest(ScriptingRequest.Type, parameters));
}
public async Task <ScriptingResult> RequestScript(ScriptingParams scriptingParams, int timeoutMilliseconds = 5000)
{
var result = await Driver.SendRequest(ScriptingRequest.Type, scriptingParams);
return(result);
}
