public async Task SaveResultsJsonNonExistentQuery()
{
// Given: A working query and workspace service
WorkspaceService <SqlToolsSettings> ws = Common.GetPrimedWorkspaceService(null);
QueryExecutionService qes = Common.GetPrimedExecutionService(null, false, false, ws);
// If: I attempt to save a result set from a query that doesn't exist
SaveResultsAsJsonRequestParams saveParams = new SaveResultsAsJsonRequestParams
{
OwnerUri = Common.OwnerUri // Won't exist because nothing has executed
};
object error = null;
var requestContext = RequestContextMocks.Create <SaveResultRequestResult>(null)
.AddErrorHandling(o => error = o);
await qes.HandleSaveResultsAsJsonRequest(saveParams, requestContext.Object);
// Then:
// ... An error event should have been fired
// ... No success event should have been fired
VerifyResponseCalls(requestContext, false, true);
Assert.IsType <SaveResultRequestError>(error);
Assert.NotNull(error);
Assert.NotNull(((SaveResultRequestError)error).message);
}
public void WriteRowWithSpecialTypesSuccess()
{
// Setup:
// ... Create a request params that has three different types of value
// ... Create a set of data to write
// ... Create storage for the output
SaveResultsAsJsonRequestParams saveParams = new SaveResultsAsJsonRequestParams();
List <DbCellValue> data = new List <DbCellValue>
{
new DbCellValue {
DisplayValue = "1", RawObject = 1
},
new DbCellValue {
DisplayValue = "1.234", RawObject = 1.234
},
new DbCellValue {
DisplayValue = "2017-07-08T00:00:00", RawObject = new DateTime(2017, 07, 08)
},
};
List <DbColumnWrapper> columns = new List <DbColumnWrapper>
{
new DbColumnWrapper(new TestDbColumn("numberCol", typeof(int))),
new DbColumnWrapper(new TestDbColumn("decimalCol", typeof(decimal))),
new DbColumnWrapper(new TestDbColumn("datetimeCol", typeof(DateTime)))
};
byte[] output = new byte[8192];
// If:
// ... I write two rows
var jsonWriter = new SaveAsJsonFileStreamWriter(new MemoryStream(output), saveParams);
using (jsonWriter)
{
jsonWriter.WriteRow(data, columns);
jsonWriter.WriteRow(data, columns);
}
// Then:
// ... Upon deserialization to an array of dictionaries
string outputString = Encoding.UTF8.GetString(output).TrimEnd('\0');
Dictionary <string, string>[] outputObject =
JsonConvert.DeserializeObject <Dictionary <string, string>[]>(outputString);
// ... There should be 2 items in the array,
// ... The item should have three fields, and three values, assigned appropriately
// ... The deserialized values should match the display value
Assert.Equal(2, outputObject.Length);
foreach (var item in outputObject)
{
Assert.Equal(3, item.Count);
for (int i = 0; i < columns.Count; i++)
{
Assert.True(item.ContainsKey(columns[i].ColumnName));
Assert.Equal(data[i].RawObject == null ? null : data[i].DisplayValue, item[columns[i].ColumnName]);
}
}
}
public void WriteRowWithoutColumnSelection()
{
// Setup:
// ... Create a request params that has no selection made
// ... Create a set of data to write
// ... Create storage for the output
SaveResultsAsJsonRequestParams saveParams = new SaveResultsAsJsonRequestParams();
List <DbCellValue> data = new List <DbCellValue>
{
new DbCellValue {
DisplayValue = "item1", RawObject = "item1"
},
new DbCellValue {
DisplayValue = "null", RawObject = null
}
};
List <DbColumnWrapper> columns = new List <DbColumnWrapper>
{
new DbColumnWrapper(new TestDbColumn("column1")),
new DbColumnWrapper(new TestDbColumn("column2"))
};
byte[] output = new byte[8192];
// If:
// ... I write two rows
var jsonWriter = new SaveAsJsonFileStreamWriter(new MemoryStream(output), saveParams);
using (jsonWriter)
{
jsonWriter.WriteRow(data, columns);
jsonWriter.WriteRow(data, columns);
}
// Then:
// ... Upon deserialization to an array of dictionaries
string outputString = Encoding.UTF8.GetString(output).TrimEnd('\0');
Dictionary <string, string>[] outputObject =
JsonConvert.DeserializeObject <Dictionary <string, string>[]>(outputString);
// ... There should be 2 items in the array,
// ... The item should have two fields, and two values, assigned appropriately
Assert.Equal(2, outputObject.Length);
foreach (var item in outputObject)
{
Assert.Equal(2, item.Count);
for (int i = 0; i < columns.Count; i++)
{
Assert.True(item.ContainsKey(columns[i].ColumnName));
Assert.Equal(data[i].RawObject == null ? null : data[i].DisplayValue, item[columns[i].ColumnName]);
}
}
}
/// <summary>
/// Process request to save a resultSet to a file in JSON format
/// </summary>
internal async Task HandleSaveResultsAsJsonRequest(SaveResultsAsJsonRequestParams saveParams,
RequestContext <SaveResultRequestResult> requestContext)
{
// Use the default JSON file factory if we haven't overridden it
IFileStreamFactory jsonFactory = JsonFileFactory ?? new SaveAsJsonFileStreamFactory
{
SaveRequestParams = saveParams,
QueryExecutionSettings = Settings.QueryExecutionSettings
};
await SaveResultsHelper(saveParams, requestContext, jsonFactory);
}
/// <summary>
/// Request to save query results as JSON
/// </summary>
public async Task <SaveResultRequestResult> SaveAsJson(string ownerUri, string filename, int batchIndex, int resultSetIndex)
{
var saveParams = new SaveResultsAsJsonRequestParams
{
OwnerUri = ownerUri,
BatchIndex = batchIndex,
ResultSetIndex = resultSetIndex,
FilePath = filename
};
var result = await Driver.SendRequest(SaveResultsAsJsonRequest.Type, saveParams);
return(result);
}
public async Task SaveResultAsJsonFailure()
{
// Given:
// ... A working query and workspace service
WorkspaceService <SqlToolsSettings> ws = Common.GetPrimedWorkspaceService(Common.StandardQuery);
Dictionary <string, byte[]> storage;
QueryExecutionService qes = Common.GetPrimedExecutionService(Common.StandardTestDataSet, true, false, ws, out storage);
// ... The query execution service has executed a query with results
var executeParams = new ExecuteDocumentSelectionParams {
QuerySelection = null, OwnerUri = Common.OwnerUri
};
var executeRequest = RequestContextMocks.Create <ExecuteRequestResult>(null);
await qes.HandleExecuteRequest(executeParams, executeRequest.Object);
await qes.ActiveQueries[Common.OwnerUri].ExecutionTask;
// If: I attempt to save a result set and get it to throw because of invalid column selection
SaveResultsAsJsonRequestParams saveParams = new SaveResultsAsJsonRequestParams
{
BatchIndex = 0,
FilePath = "qqq",
OwnerUri = Common.OwnerUri,
ResultSetIndex = 0,
ColumnStartIndex = -1,
ColumnEndIndex = 100,
RowStartIndex = 0,
RowEndIndex = 5
};
qes.JsonFileFactory = GetJsonStreamFactory(storage, saveParams);
object error = null;
var requestContext = RequestContextMocks.Create <SaveResultRequestResult>(null)
.AddErrorHandling(e => error = e);
await qes.HandleSaveResultsAsJsonRequest(saveParams, requestContext.Object);
await qes.ActiveQueries[saveParams.OwnerUri]
.Batches[saveParams.BatchIndex]
.ResultSets[saveParams.ResultSetIndex]
.SaveTasks[saveParams.FilePath];
// Then:
// ... An error event should have been fired
// ... No success event should have been fired
VerifyResponseCalls(requestContext, false, true);
Assert.IsType <SaveResultRequestError>(error);
Assert.NotNull(error);
Assert.NotNull(((SaveResultRequestError)error).message);
}
public async void SaveResultsAsJsonWithSelectionSuccessTest()
{
// Execute a query
var queryService = await Common.GetPrimedExecutionService(Common.CreateMockFactory(null, false), true, Common.GetPrimedWorkspaceService());
var executeParams = new QueryExecuteParams {
QuerySelection = Common.WholeDocument, OwnerUri = Common.OwnerUri
};
var executeRequest = RequestContextMocks.Create <QueryExecuteResult>(null);
await queryService.HandleExecuteRequest(executeParams, executeRequest.Object);
await queryService.ActiveQueries[Common.OwnerUri].ExecutionTask;
// Request to save the results as json with correct parameters
var saveParams = new SaveResultsAsJsonRequestParams
{
OwnerUri = Common.OwnerUri,
ResultSetIndex = 0,
BatchIndex = 0,
FilePath = "testwrite_5.json",
RowStartIndex = 0,
RowEndIndex = 1,
ColumnStartIndex = 0,
ColumnEndIndex = 1
};
SaveResultRequestResult result = null;
var saveRequest = GetSaveResultsContextMock(qcr => result = qcr, null);
queryService.ActiveQueries[Common.OwnerUri].Batches[0] = Common.GetBasicExecutedBatch();
// Call save results and wait on the save task
await queryService.HandleSaveResultsAsJsonRequest(saveParams, saveRequest.Object);
ResultSet selectedResultSet = queryService.ActiveQueries[saveParams.OwnerUri].Batches[saveParams.BatchIndex].ResultSets[saveParams.ResultSetIndex];
Task saveTask = selectedResultSet.GetSaveTask(saveParams.FilePath);
await saveTask;
// Expect to see a file successfully created in filepath and a success message
Assert.Null(result.Messages);
Assert.True(File.Exists(saveParams.FilePath));
VerifySaveResultsCallCount(saveRequest, Times.Once(), Times.Never());
// Delete temp file after test
if (File.Exists(saveParams.FilePath))
{
File.Delete(saveParams.FilePath);
}
}
public async Task SaveResultsAsJsonSuccess()
{
// Given:
// ... A working query and workspace service
WorkspaceService <SqlToolsSettings> ws = Common.GetPrimedWorkspaceService(Common.StandardQuery);
Dictionary <string, byte[]> storage;
QueryExecutionService qes = Common.GetPrimedExecutionService(Common.StandardTestDataSet, true, false, ws, out storage);
// ... The query execution service has executed a query with results
var executeParams = new ExecuteDocumentSelectionParams {
QuerySelection = null, OwnerUri = Common.OwnerUri
};
var executeRequest = RequestContextMocks.Create <ExecuteRequestResult>(null);
await qes.HandleExecuteRequest(executeParams, executeRequest.Object);
await qes.ActiveQueries[Common.OwnerUri].ExecutionTask;
// If: I attempt to save a result set from a query
SaveResultsAsJsonRequestParams saveParams = new SaveResultsAsJsonRequestParams
{
OwnerUri = Common.OwnerUri,
FilePath = "qqq",
BatchIndex = 0,
ResultSetIndex = 0
};
qes.JsonFileFactory = GetJsonStreamFactory(storage, saveParams);
SaveResultRequestResult result = null;
var requestContext = RequestContextMocks.Create <SaveResultRequestResult>(r => result = r);
await qes.HandleSaveResultsAsJsonRequest(saveParams, requestContext.Object);
await qes.ActiveQueries[saveParams.OwnerUri]
.Batches[saveParams.BatchIndex]
.ResultSets[saveParams.ResultSetIndex]
.SaveTasks[saveParams.FilePath];
// Then:
// ... I should have a successful result
// ... There should not have been an error
VerifyResponseCalls(requestContext, true, false);
Assert.NotNull(result);
Assert.Null(result.Messages);
}
public void ArrayWrapperTest()
{
// Setup:
// ... Create storage for the output
byte[] output = new byte[8192];
SaveResultsAsJsonRequestParams saveParams = new SaveResultsAsJsonRequestParams();
// If:
// ... I create and then destruct a json writer
var jsonWriter = new SaveAsJsonFileStreamWriter(new MemoryStream(output), saveParams);
jsonWriter.Dispose();
// Then:
// ... The output should be an empty array
string outputString = Encoding.UTF8.GetString(output).TrimEnd('\0');
object[] outputArray = JsonConvert.DeserializeObject <object[]>(outputString);
Assert.Equal(0, outputArray.Length);
}
/// <summary>
/// Process request to save a resultSet to a file in JSON format
/// </summary>
internal async Task HandleSaveResultsAsJsonRequest(SaveResultsAsJsonRequestParams saveParams,
RequestContext <SaveResultRequestResult> requestContext)
{
// retrieve query for OwnerUri
Query result;
if (!ActiveQueries.TryGetValue(saveParams.OwnerUri, out result))
{
await requestContext.SendResult(new SaveResultRequestResult
{
Messages = "Failed to save results, ID not found."
});
return;
}
ResultSet selectedResultSet = result.Batches[saveParams.BatchIndex].ResultSets[saveParams.ResultSetIndex];
if (!selectedResultSet.IsBeingDisposed)
{
// Create SaveResults object and add success and error handlers to respective events
SaveResults saveAsJson = new SaveResults();
SaveResults.AsyncSaveEventHandler successHandler = async message =>
{
selectedResultSet.RemoveSaveTask(saveParams.FilePath);
await requestContext.SendResult(new SaveResultRequestResult { Messages = message });
};
saveAsJson.SaveCompleted += successHandler;
SaveResults.AsyncSaveEventHandler errorHandler = async message =>
{
selectedResultSet.RemoveSaveTask(saveParams.FilePath);
await requestContext.SendError(new SaveResultRequestError { message = message });
};
saveAsJson.SaveFailed += errorHandler;
saveAsJson.SaveResultSetAsJson(saveParams, requestContext, result);
// Associate the ResultSet with the save task
selectedResultSet.AddSaveTask(saveParams.FilePath, saveAsJson.SaveTask);
}
}
public async Task SaveResultsJsonNonExistentQuery()
{
// Given: A working query and workspace service
WorkspaceService <SqlToolsSettings> ws = Common.GetPrimedWorkspaceService(null);
QueryExecutionService qes = Common.GetPrimedExecutionService(null, false, false, false, ws);
// If: I attempt to save a result set from a query that doesn't exist
SaveResultsAsJsonRequestParams saveParams = new SaveResultsAsJsonRequestParams
{
OwnerUri = Constants.OwnerUri // Won't exist because nothing has executed
};
var efv = new EventFlowValidator <SaveResultRequestResult>()
.AddStandardErrorValidation()
.Complete();
await qes.HandleSaveResultsAsJsonRequest(saveParams, efv.Object);
// Then:
// ... An error event should have been fired
// ... No success event should have been fired
efv.Validate();
}
public async void SaveResultsAsJsonExceptionTest()
{
// Execute a query
var queryService = await Common.GetPrimedExecutionService(Common.CreateMockFactory(null, false), true, Common.GetPrimedWorkspaceService());
var executeParams = new QueryExecuteParams {
QuerySelection = Common.WholeDocument, OwnerUri = Common.OwnerUri
};
var executeRequest = RequestContextMocks.Create <QueryExecuteResult>(null);
await queryService.HandleExecuteRequest(executeParams, executeRequest.Object);
await queryService.ActiveQueries[Common.OwnerUri].ExecutionTask;
// Request to save the results as json with incorrect filepath
var saveParams = new SaveResultsAsJsonRequestParams
{
OwnerUri = Common.OwnerUri,
ResultSetIndex = 0,
BatchIndex = 0,
FilePath = RuntimeInformation.IsOSPlatform(OSPlatform.Windows) ? "G:\\test.json" : "/test.json"
};
SaveResultRequestError errMessage = null;
var saveRequest = GetSaveResultsContextMock(null, err => errMessage = (SaveResultRequestError)err);
queryService.ActiveQueries[Common.OwnerUri].Batches[0] = Common.GetBasicExecutedBatch();
// Call save results and wait on the save task
await queryService.HandleSaveResultsAsJsonRequest(saveParams, saveRequest.Object);
ResultSet selectedResultSet = queryService.ActiveQueries[saveParams.OwnerUri].Batches[saveParams.BatchIndex].ResultSets[saveParams.ResultSetIndex];
Task saveTask = selectedResultSet.GetSaveTask(saveParams.FilePath);
await saveTask;
// Expect to see error message
Assert.NotNull(errMessage);
VerifySaveResultsCallCount(saveRequest, Times.Never(), Times.Once());
Assert.False(File.Exists(saveParams.FilePath));
}
public async Task SaveResultsAsJsonQueryNotFoundTest()
{
// Create a query service
var workspaceService = Common.GetPrimedWorkspaceService(Common.StandardQuery);
var queryService = Common.GetPrimedExecutionService(null, true, false, workspaceService);
// Request to save the results as json with query that is no longer active
var saveParams = new SaveResultsAsJsonRequestParams
{
OwnerUri = "falseuri",
ResultSetIndex = 0,
BatchIndex = 0,
FilePath = "testwrite_6.json"
};
SaveResultRequestResult result = null;
var saveRequest = GetSaveResultsContextMock(qcr => result = qcr, null);
await queryService.HandleSaveResultsAsJsonRequest(saveParams, saveRequest.Object);
// Expect message that save failed
Assert.Equal("Failed to save results, ID not found.", result.Messages);
Assert.False(File.Exists(saveParams.FilePath));
VerifySaveResultsCallCount(saveRequest, Times.Once(), Times.Never());
}
private static IFileStreamFactory GetJsonStreamFactory(IDictionary <string, byte[]> storage, SaveResultsAsJsonRequestParams saveParams)
{
Mock <IFileStreamFactory> mock = new Mock <IFileStreamFactory>();
mock.Setup(fsf => fsf.GetReader(It.IsAny <string>()))
.Returns <string>(output => new ServiceBufferFileStreamReader(new MemoryStream(storage[output]), new QueryExecutionSettings()));
mock.Setup(fsf => fsf.GetWriter(It.IsAny <string>()))
.Returns <string>(output =>
{
storage.Add(output, new byte[8192]);
return(new SaveAsJsonFileStreamWriter(new MemoryStream(storage[output]), saveParams));
});
return(mock.Object);
}
public void WriteRowWithColumnSelection()
{
// Setup:
// ... Create a request params that selects n-1 columns from the front and back
// ... Create a set of data to write
// ... Create a memory location to store the data
var saveParams = new SaveResultsAsJsonRequestParams
{
ColumnStartIndex = 1,
ColumnEndIndex = 2,
RowStartIndex = 0, // Including b/c it is required to be a "save selection"
RowEndIndex = 10
};
List <DbCellValue> data = new List <DbCellValue>
{
new DbCellValue {
DisplayValue = "item1", RawObject = "item1"
},
new DbCellValue {
DisplayValue = "item2", RawObject = "item2"
},
new DbCellValue {
DisplayValue = "null", RawObject = null
},
new DbCellValue {
DisplayValue = "null", RawObject = null
}
};
List <DbColumnWrapper> columns = new List <DbColumnWrapper>
{
new DbColumnWrapper(new TestDbColumn("column1")),
new DbColumnWrapper(new TestDbColumn("column2")),
new DbColumnWrapper(new TestDbColumn("column3")),
new DbColumnWrapper(new TestDbColumn("column4"))
};
byte[] output = new byte[8192];
// If: I write two rows
var jsonWriter = new SaveAsJsonFileStreamWriter(new MemoryStream(output), saveParams);
using (jsonWriter)
{
jsonWriter.WriteRow(data, columns);
jsonWriter.WriteRow(data, columns);
}
// Then:
// ... Upon deserialization to an array of dictionaries
string outputString = Encoding.UTF8.GetString(output).Trim('\0');
Dictionary <string, string>[] outputObject =
JsonConvert.DeserializeObject <Dictionary <string, string>[]>(outputString);
// ... There should be 2 items in the array
// ... The items should have 2 fields and values
Assert.Equal(2, outputObject.Length);
foreach (var item in outputObject)
{
Assert.Equal(2, item.Count);
for (int i = 1; i <= 2; i++)
{
Assert.True(item.ContainsKey(columns[i].ColumnName));
Assert.Equal(data[i].RawObject == null ? null : data[i].DisplayValue, item[columns[i].ColumnName]);
}
}
}
/// <summary>
/// Save results as JSON format to the file specified in saveParams
/// </summary>
/// <param name="saveParams"> Parameters from the request </param>
/// <param name="requestContext"> Request context for save results </param>
/// <param name="result"> Result query object </param>
/// <returns></returns>
internal void SaveResultSetAsJson(SaveResultsAsJsonRequestParams saveParams, RequestContext <SaveResultRequestResult> requestContext, Query result)
{
// Run in a separate thread
SaveTask = Task.Run(async() =>
{
try
{
using (StreamWriter jsonFile = new StreamWriter(File.Open(saveParams.FilePath, FileMode.Create, FileAccess.ReadWrite, FileShare.Read)))
using (JsonWriter jsonWriter = new JsonTextWriter(jsonFile))
{
int rowCount = 0;
int rowStartIndex = 0;
int columnStartIndex = 0;
int columnEndIndex = 0;
jsonWriter.Formatting = Formatting.Indented;
jsonWriter.WriteStartArray();
// Get the requested resultSet from query
Batch selectedBatch = result.Batches[saveParams.BatchIndex];
ResultSet selectedResultSet = selectedBatch.ResultSets[saveParams.ResultSetIndex];
// Set column, row counts depending on whether save request is for entire result set or a subset
if (IsSaveSelection(saveParams))
{
rowCount = saveParams.RowEndIndex.Value - saveParams.RowStartIndex.Value + 1;
rowStartIndex = saveParams.RowStartIndex.Value;
columnStartIndex = saveParams.ColumnStartIndex.Value;
columnEndIndex = saveParams.ColumnEndIndex.Value + 1; // include the last column
}
else
{
rowCount = (int)selectedResultSet.RowCount;
columnEndIndex = selectedResultSet.Columns.Length;
}
// Split rows into batches
for (int count = 0; count < (rowCount / BatchSize) + 1; count++)
{
int numberOfRows = (count < rowCount / BatchSize) ? BatchSize : (rowCount % BatchSize);
if (numberOfRows == 0)
{
break;
}
// Retrieve rows and write as json
ResultSetSubset resultSubset = await result.GetSubset(saveParams.BatchIndex, saveParams.ResultSetIndex, rowStartIndex + count * BatchSize, numberOfRows);
foreach (var row in resultSubset.Rows)
{
jsonWriter.WriteStartObject();
for (int i = columnStartIndex; i < columnEndIndex; i++)
{
// Write columnName, value pair
DbColumnWrapper col = selectedResultSet.Columns[i];
string val = row[i]?.ToString();
jsonWriter.WritePropertyName(col.ColumnName);
if (val == null)
{
jsonWriter.WriteNull();
}
else
{
jsonWriter.WriteValue(val);
}
}
jsonWriter.WriteEndObject();
}
}
jsonWriter.WriteEndArray();
}
// Successfully wrote file, send success result
if (SaveCompleted != null)
{
await SaveCompleted(null);
}
}
catch (Exception ex)
{
// Delete file when exception occurs
if (FileUtils.SafeFileExists(saveParams.FilePath))
{
FileUtils.SafeFileDelete(saveParams.FilePath);
}
if (SaveFailed != null)
{
await SaveFailed(ex.Message);
}
}
});
}