internal static async Task DeleteNotebook(
AgentService agentServiceInstance,
string ownerUri,
AgentNotebookInfo notebook,
RunType runType)
{
ConnectionInfo connInfo;
agentServiceInstance.ConnectionServiceInstance.TryFindConnection(ownerUri, out connInfo);
// deleting job from sql agent
var deleteJobResult = await agentServiceInstance.ConfigureAgentJob(
ownerUri,
notebook.Name,
notebook,
ConfigAction.Drop,
runType);
if (!deleteJobResult.Item1)
{
throw new Exception(deleteJobResult.Item2);
}
// deleting notebook metadata from target database
await DeleteNotebookMetadata(
connInfo,
notebook.JobId,
notebook.TargetDatabase);
}
internal async Task TestDuplicateJobCreation()
{
using (SelfCleaningTempFile queryTempFile = new SelfCleaningTempFile())
{
var service = new AgentService();
var connectionResult = await LiveConnectionHelper.InitLiveConnectionInfoAsync("master", queryTempFile.FilePath);
AgentNotebookInfo notebook = AgentTestUtils.GetTestNotebookInfo("myTestNotebookJob" + Guid.NewGuid().ToString(), "master");
var createNotebookContext = new Mock <RequestContext <CreateAgentNotebookResult> >();
createNotebookContext.Setup(x => x.SendResult(It.IsAny <CreateAgentNotebookResult>())).Returns(Task.FromResult(new object()));
await service.HandleCreateAgentNotebookRequest(new CreateAgentNotebookParams()
{
OwnerUri = connectionResult.ConnectionInfo.OwnerUri,
Notebook = notebook,
TemplateFilePath = AgentTestUtils.CreateTemplateNotebookFile()
}, createNotebookContext.Object);
createNotebookContext.Verify(x => x.SendResult(It.Is <CreateAgentNotebookResult>(p => p.Success == true)));
await service.HandleCreateAgentNotebookRequest(new CreateAgentNotebookParams()
{
OwnerUri = connectionResult.ConnectionInfo.OwnerUri,
Notebook = notebook,
TemplateFilePath = AgentTestUtils.CreateTemplateNotebookFile()
}, createNotebookContext.Object);
createNotebookContext.Verify(x => x.SendResult(It.Is <CreateAgentNotebookResult>(p => p.Success == false)));
await AgentTestUtils.CleanupNotebookJob(connectionResult, notebook);
}
}
internal async Task TestDeleteAgentNotebookHandler()
{
using (SelfCleaningTempFile queryTempFile = new SelfCleaningTempFile())
{
var service = new AgentService();
var connectionResult = await LiveConnectionHelper.InitLiveConnectionInfoAsync("master", queryTempFile.FilePath);
//creating a notebook job
AgentNotebookInfo notebook = AgentTestUtils.SetupNotebookJob(connectionResult).Result;
//verifying it's getting created
Assert.Equal(true, AgentTestUtils.VerifyNotebook(connectionResult, notebook));
//deleting the notebook job
var deleteNotebookContext = new Mock <RequestContext <ResultStatus> >();
deleteNotebookContext.Setup(x => x.SendResult(It.IsAny <ResultStatus>())).Returns(Task.FromResult(new object()));
await service.HandleDeleteAgentNotebooksRequest(new DeleteAgentNotebookParams()
{
OwnerUri = connectionResult.ConnectionInfo.OwnerUri,
Notebook = notebook
}, deleteNotebookContext.Object);
deleteNotebookContext.Verify(x => x.SendResult(It.Is <ResultStatus>(p => p.Success == true)));
//verifying if the job is deleted
Assert.Equal(false, AgentTestUtils.VerifyNotebook(connectionResult, notebook));
}
}
public static async Task <AgentNotebookInfo> SetupNotebookJob(
TestConnectionResult connectionResult,
AgentNotebookInfo notebook = null)
{
var service = new AgentService();
if (notebook == null)
{
notebook = GetTestNotebookInfo("myTestNotebookJob" + Guid.NewGuid().ToString(), "master");
}
string tempNotebookPath = CreateTemplateNotebookFile();
await AgentNotebookHelper.CreateNotebook(
service,
connectionResult.ConnectionInfo.OwnerUri,
notebook,
tempNotebookPath,
ManagementUtils.asRunType(0)
);
var createdNotebook = GetNotebook(connectionResult, notebook.Name);
File.Delete(tempNotebookPath);
return(createdNotebook);
}
static bool NotebookObjectEquals(AgentNotebookInfo expectedNotebook, AgentNotebookInfo actualNotebook)
{
return(
expectedNotebook.Name == actualNotebook.Name
&&
expectedNotebook.Description == actualNotebook.Description
);
}
internal async Task TestAgentNotebookCreateHelper()
{
using (SelfCleaningTempFile queryTempFile = new SelfCleaningTempFile())
{
var connectionResult = await LiveConnectionHelper.InitLiveConnectionInfoAsync("master", queryTempFile.FilePath);
AgentNotebookInfo notebook = AgentTestUtils.GetTestNotebookInfo("myTestNotebookJob" + Guid.NewGuid().ToString(), "master");
Assert.Equal(false, AgentTestUtils.VerifyNotebook(connectionResult, notebook));
notebook = AgentTestUtils.SetupNotebookJob(connectionResult).Result;
Assert.Equal(true, AgentTestUtils.VerifyNotebook(connectionResult, notebook));
await AgentTestUtils.CleanupNotebookJob(connectionResult, notebook);
}
}
public static bool VerifyNotebook(TestConnectionResult connectionResult, AgentNotebookInfo notebook)
{
var notebookList = AgentNotebookHelper.GetAgentNotebooks(connectionResult.ConnectionInfo).Result;
foreach (AgentNotebookInfo n in notebookList)
{
if (NotebookObjectEquals(notebook, n))
{
return(true);
}
}
return(false);
}
public static async Task <AgentNotebookInfo> CreateNotebook(
AgentService agentServiceInstance,
string ownerUri,
AgentNotebookInfo notebook,
string templatePath,
RunType runType)
{
if (!File.Exists(templatePath))
{
throw new FileNotFoundException();
}
AgentNotebookInfo result;
ConnectionInfo connInfo;
agentServiceInstance.ConnectionServiceInstance.TryFindConnection(ownerUri, out connInfo);
// creating notebook job step
notebook.JobSteps = CreateNotebookPowerShellStep(notebook.Name, notebook.TargetDatabase);
// creating sql agent job
var jobCreationResult = await agentServiceInstance.ConfigureAgentJob(
ownerUri,
notebook.Name,
notebook,
ConfigAction.Create,
runType);
if (jobCreationResult.Item1 == false)
{
throw new Exception(jobCreationResult.Item2);
}
// creating notebook metadata for the job
string jobId =
await SetUpNotebookAndGetJobId(
connInfo,
notebook.Name,
notebook.TargetDatabase,
templatePath,
notebook.ExecuteDatabase);
notebook.JobId = jobId;
result = notebook;
return(result);
}
internal static async Task UpdateNotebook(
AgentService agentServiceInstance,
string ownerUri,
string originalNotebookName,
AgentNotebookInfo notebook,
string templatePath,
RunType runType)
{
ConnectionInfo connInfo;
agentServiceInstance.ConnectionServiceInstance.TryFindConnection(ownerUri, out connInfo);
if (!string.IsNullOrEmpty(templatePath) && !File.Exists(templatePath))
{
throw new FileNotFoundException();
}
// updating notebook agent job
var updateJobResult =
await agentServiceInstance.ConfigureAgentJob(
ownerUri,
originalNotebookName,
notebook,
ConfigAction.Update,
runType);
if (!updateJobResult.Item1)
{
throw new Exception(updateJobResult.Item2);
}
// update notebook metadata
UpdateNotebookInfo(
connInfo,
notebook.JobId,
templatePath,
notebook.ExecuteDatabase,
notebook.TargetDatabase);
}
internal async Task TestUpdateNonExistentJob()
{
using (SelfCleaningTempFile queryTempFile = new SelfCleaningTempFile())
{
var service = new AgentService();
var connectionResult = await LiveConnectionHelper.InitLiveConnectionInfoAsync("master", queryTempFile.FilePath);
//getting a test notebook object
AgentNotebookInfo notebook = AgentTestUtils.GetTestNotebookInfo("myTestNotebookJob" + Guid.NewGuid().ToString(), "master");
var updateNotebookContext = new Mock <RequestContext <UpdateAgentNotebookResult> >();
updateNotebookContext.Setup(x => x.SendResult(It.IsAny <UpdateAgentNotebookResult>())).Returns(Task.FromResult(new object()));
await service.HandleUpdateAgentNotebookRequest(new UpdateAgentNotebookParams()
{
OwnerUri = connectionResult.ConnectionInfo.OwnerUri,
Notebook = notebook,
TemplateFilePath = AgentTestUtils.CreateTemplateNotebookFile()
}, updateNotebookContext.Object);
// enpoint should error out
updateNotebookContext.Verify(x => x.SendResult(It.Is <UpdateAgentNotebookResult>(p => p.Success == false)));
}
}
/// <summary>
/// a function which fetches notebooks jobs accessible to the user
/// </summary>
/// <param name="connInfo">connectionInfo generated from OwnerUri</param>
/// <returns>array of agent notebooks</returns>
public static async Task <AgentNotebookInfo[]> GetAgentNotebooks(ConnectionInfo connInfo)
{
AgentNotebookInfo[] result;
// Fetching all agent Jobs accessible to the user
var serverConnection = ConnectionService.OpenServerConnection(connInfo);
var fetcher = new JobFetcher(serverConnection);
var filter = new JobActivityFilter();
var jobs = fetcher.FetchJobs(filter);
Dictionary <Guid, JobProperties> allJobsHashTable = new Dictionary <Guid, JobProperties>();
if (jobs != null)
{
foreach (var job in jobs.Values)
{
allJobsHashTable.Add(job.JobID, job);
}
}
// Fetching notebooks across all databases accessible by the user
string getJobIdsFromDatabaseQueryString =
@"
DECLARE @script AS VARCHAR(MAX)
SET @script =
'
USE [?];
IF EXISTS
(
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = N''notebooks''
AND
TABLE_NAME = N''nb_template''
)
BEGIN
SELECT
[notebooks].[nb_template].job_id,
[notebooks].[nb_template].template_id,
[notebooks].[nb_template].last_run_notebook_error,
[notebooks].[nb_template].execute_database,
DB_NAME() AS db_name
FROM [?].notebooks.nb_template
INNER JOIN
msdb.dbo.sysjobs
ON
[?].notebooks.nb_template.job_id = msdb.dbo.sysjobs.job_id
END
'
EXEC sp_MSforeachdb @script";
var agentNotebooks = new List <AgentNotebookInfo>();
DataSet jobIdsDataSet = await ExecuteSqlQueries(connInfo, getJobIdsFromDatabaseQueryString);
foreach (DataTable templateTable in jobIdsDataSet.Tables)
{
foreach (DataRow templateRow in templateTable.Rows)
{
AgentNotebookInfo notebookJob =
AgentUtilities.ConvertToAgentNotebookInfo(allJobsHashTable[(Guid)templateRow["job_id"]]);
notebookJob.TemplateId = templateRow["template_id"] as string;
notebookJob.TargetDatabase = templateRow["db_name"] as string;
notebookJob.LastRunNotebookError = templateRow["last_run_notebook_error"] as string;
notebookJob.ExecuteDatabase = templateRow["execute_database"] as string;
agentNotebooks.Add(notebookJob);
}
}
result = agentNotebooks.ToArray();
return(result);
}
public static async Task CleanupNotebookJob(TestConnectionResult connectionResult, AgentNotebookInfo notebook)
{
var service = new AgentService();
await AgentNotebookHelper.DeleteNotebook(
service,
connectionResult.ConnectionInfo.OwnerUri,
notebook,
ManagementUtils.asRunType(0)
);
}