public static async Task <string> GetTemplateFile(
ConnectionInfo connInfo,
string job_id,
string targetDatabase,
string templateFileContents)
{
String getNotebookTemplateQuery =
@"
SELECT notebook
from
notebooks.nb_template
where
job_id = @jobId;
";
List <SqlParameter> getNotebookTemplateQueryParams = new List <SqlParameter>();
getNotebookTemplateQueryParams.Add(new SqlParameter("job_id", getNotebookTemplateQueryParams));
DataSet templateDataSet = await AgentNotebookHelper.ExecuteSqlQueries(
connInfo,
getNotebookTemplateQuery,
getNotebookTemplateQueryParams,
targetDatabase);
DataTable templateDataTable = templateDataSet.Tables[0];
DataRow templateDataRow = templateDataTable.Rows[0];
return(templateDataRow["notebook"] as string);
}
/// <summary>
/// Delete a particular run of the job. Deletion mainly including clearing out the notebook,
/// and notebook-error. The API doesn't delete the row because some notebook runs that have job
/// error in them don't have an entry in the materialized table. For keeping track of those notebook
/// runs the entry is added into the table with is_delete set to 1.
/// </summary>
/// <param name="connInfo">connectionInfo generated from OwnerUri</param>
/// <param name="agentNotebookHistory">Actual history item to be deleted</param>
/// <param name="targetDatabase">database on which the notebook history is stored</param>
/// <returns></returns>
public static async Task DeleteMaterializedNotebook(
ConnectionInfo connInfo,
AgentNotebookHistoryInfo agentNotebookHistory,
string targetDatabase)
{
string deleteMaterializedNotebookQuery =
@"
IF EXISTS
(SELECT * FROM notebooks.nb_materialized
WHERE job_id = @jobId AND run_time = @startTime AND run_date = @startDate)
BEGIN
UPDATE notebooks.nb_materialized
SET is_deleted = 1,
notebook = '',
notebook_error = ''
WHERE
job_id = @jobId AND run_time = @startTime AND run_date = @startDate
END
ELSE
BEGIN
INSERT INTO notebooks.nb_materialized (job_id, run_time, run_date, notebook, notebook_error, is_deleted)
VALUES
(@jobID, @startTime, @startDate, '', '', 1)
END
";
List <SqlParameter> deleteMaterializedNotebookParams = new List <SqlParameter>();
deleteMaterializedNotebookParams.Add(new SqlParameter("jobID", agentNotebookHistory.JobId));
deleteMaterializedNotebookParams.Add(new SqlParameter("startTime", agentNotebookHistory.RunDate.ToString("HHmmss")));
deleteMaterializedNotebookParams.Add(new SqlParameter("startDate", agentNotebookHistory.RunDate.ToString("yyyyMMdd")));
deleteMaterializedNotebookParams.Add(new SqlParameter("materializedId", agentNotebookHistory.MaterializedNotebookId));
await AgentNotebookHelper.ExecuteSqlQueries(
connInfo,
deleteMaterializedNotebookQuery,
deleteMaterializedNotebookParams,
targetDatabase);
}
/// <summary>
/// Changing the pin state of materialized notebook runs. Special case is handled where new row is
/// added for failed jobs which do not have an entry into the materialized table
/// </summary>
/// <param name="connInfo">connectionInfo generated from OwnerUri</param>
/// <param name="agentNotebookHistory">actual history item to be pinned</param>
/// <param name="targetDatabase">database on which the notebook history is stored</param>
/// <param name="pin">pin state for the history</param>
/// <returns></returns>
public static async Task UpdateMaterializedNotebookPin(
ConnectionInfo connInfo,
AgentNotebookHistoryInfo agentNotebookHistory,
string targetDatabase,
bool pin)
{
string updateMaterializedNotebookPinQuery =
@"
IF EXISTS
(SELECT * FROM notebooks.nb_materialized
WHERE job_id = @jobId AND run_time = @startTime AND run_date = @startDate)
BEGIN
UPDATE notebooks.nb_materialized
SET
pin = @notebookPin
WHERE
job_id = @jobId AND run_time = @startTime AND run_date = @startDate
END
ELSE
BEGIN
INSERT INTO notebooks.nb_materialized (job_id, run_time, run_date, notebook, notebook_error, pin)
VALUES
(@jobID, @startTime, @startDate, '', '', @notebookPin)
END
";
List <SqlParameter> updateMaterializedNotebookPinParams = new List <SqlParameter>();
updateMaterializedNotebookPinParams.Add(new SqlParameter("jobID", agentNotebookHistory.JobId));
updateMaterializedNotebookPinParams.Add(new SqlParameter("startTime", agentNotebookHistory.RunDate.ToString("HHmmss")));
updateMaterializedNotebookPinParams.Add(new SqlParameter("startDate", agentNotebookHistory.RunDate.ToString("yyyyMMdd")));
updateMaterializedNotebookPinParams.Add(new SqlParameter("notebookPin", pin));
await AgentNotebookHelper.ExecuteSqlQueries(
connInfo,
updateMaterializedNotebookPinQuery,
updateMaterializedNotebookPinParams,
targetDatabase);
}
