public void BuildInsert_JsonItemsHasSingleQuote_ReturnCorrectSqlStatementWithDoubleSingleQuotes()
{
// Arrange
var jsonItems = FakeJson.CreateMultiple(2).AsJsonString().FixSingleQuotes();
var correctSqlStatement =
$@"insert into ""{_schema}"".""{_tableName}"" (
""{_tableColumns[0].Name}""
, ""{_tableColumns[1].Name}""
, ""{_tableColumns[2].Name}""
, ""{_tableColumns[3].Name}""
)
select
""{_tableColumns[0].Name}""
, ""{_tableColumns[1].Name}""
, ""{_tableColumns[2].Name}""
, ""{_tableColumns[3].Name}""
from json_to_recordset('
{jsonItems.Replace("'", "''")}
') as x(
""{_tableColumns[0].Name}"" {_tableColumns[0].Type}
, ""{_tableColumns[1].Name}"" {_tableColumns[1].Type}
, ""{_tableColumns[2].Name}"" {_tableColumns[2].Type}
, ""{_tableColumns[3].Name}"" {_tableColumns[3].Type}
);"
.Replace("\r\n", "\n");
// Act
var sqlStatement = _testModule.BuildInsert(jsonItems);
// Assert
Assert.Equal(correctSqlStatement, sqlStatement);
}
public void BuildInsert_SkipAndLimitIsNotNull_ReturnCorrectCountOfJsonItemsInSqlStatement(int total, int skip,
int limit)
{
// Arrange
var jsonItemWithNestedObject1 = FakeJson.Create().WithNestedObject();
var jsonItemWithNestedObject2 = FakeJson.Create().WithNestedObject();
var jsonItemWithNestedObject3 = FakeJson.Create().WithNestedObject();
var jsonItems = new List <string>(FakeJson.CreateMultiple(total - 3))
{
jsonItemWithNestedObject1, jsonItemWithNestedObject2, jsonItemWithNestedObject3
};
var filteredJsonItems = jsonItems.Skip(skip).Take(limit).ToList();
var stringBuilder = new StringBuilder();
stringBuilder.Append($@"insert into ""{_schema}"".""{_tableName}"" (
""{_tableColumns[0].Name}""
, ""{_tableColumns[1].Name}""
, ""{_tableColumns[2].Name}""
, ""{_tableColumns[3].Name}""
)
select
""{_tableColumns[0].Name}""
, ""{_tableColumns[1].Name}""
, ""{_tableColumns[2].Name}""
, ""{_tableColumns[3].Name}""
from json_to_recordset('
[
");
for (int i = 0; i < filteredJsonItems.Count; i++)
{
stringBuilder.Append($"{filteredJsonItems[i].Replace("'", "''")}");
if (i < filteredJsonItems.Count - 1)
{
stringBuilder.Append(",\n");
}
}
stringBuilder.Append($@"
]
') as x(
""{_tableColumns[0].Name}"" {_tableColumns[0].Type}
, ""{_tableColumns[1].Name}"" {_tableColumns[1].Type}
, ""{_tableColumns[2].Name}"" {_tableColumns[2].Type}
, ""{_tableColumns[3].Name}"" {_tableColumns[3].Type}
);");
var correctSqlStatement = stringBuilder.ToString().Replace("\r\n", "\n");
var jsonItemsAsString = jsonItems.AsJsonString();
// Act
var sqlStatement = _testModule.BuildInsert(jsonItemsAsString, skip, limit);
// Assert
Assert.Equal(correctSqlStatement, sqlStatement);
}
ExecuteAsync_SetMaxLinesPer1InsertValuesSqlFile_SqlStatementOfInsertValuesWasCreatedCorrectTimes()
{
// Arrange
var maxLinesPer1InsertValuesSqlFile = 2;
var jsonItemsCount = 7;
var jsonContent = FakeJson.CreateMultiple(jsonItemsCount).AsJsonString();
_fileReader.ReadAllTextAsync(_transformOptions.SourceJsonFilePath).Returns(jsonContent);
_transformOptions.MaxLinesPer1InsertValuesSqlFile = maxLinesPer1InsertValuesSqlFile;
// Act
await _testModule.ExecuteAsync(_transformOptions);
// Assert
_sqlBuilder.Received(1).BuildInsert(jsonContent, skip: 0, limit: maxLinesPer1InsertValuesSqlFile);
_sqlBuilder.Received(1).BuildInsert(jsonContent, skip: 2, limit: maxLinesPer1InsertValuesSqlFile);
_sqlBuilder.Received(1).BuildInsert(jsonContent, skip: 4, limit: maxLinesPer1InsertValuesSqlFile);
_sqlBuilder.Received(1).BuildInsert(jsonContent, skip: 6, limit: maxLinesPer1InsertValuesSqlFile);
}
ExecuteAsync_SetMaxLinesPer1InsertValuesSqlFile_FileWithSqlStatementOfInsertValuesWasCreatedCorrectTimes(
int maxLinesPer1InsertValuesSqlFile, int jsonItemsCount, int correctInsertValuesSqlFilesCount)
{
// Arrange
var jsonContent = FakeJson.CreateMultiple(jsonItemsCount).AsJsonString();
_fileReader.ReadAllTextAsync(_transformOptions.SourceJsonFilePath).Returns(jsonContent);
var correctInsertSqlStatement = "some insert values";
_sqlBuilder.BuildInsert(jsonContent, Arg.Any <int>(), Arg.Any <int>())
.Returns(correctInsertSqlStatement);
_transformOptions.MaxLinesPer1InsertValuesSqlFile = maxLinesPer1InsertValuesSqlFile;
// Act
await _testModule.ExecuteAsync(_transformOptions);
// Assert
await _fileWriter.Received(correctInsertValuesSqlFilesCount)
.WriteAllTextAsync(Arg.Any <string>(), correctInsertSqlStatement);
}
public async Task ExecuteAsync_JsonContentHasItemsWithNestedObjects_SqlStatementOfInsertValuesWasCreatedCorrectTimes()
{
// Arrange
var maxLinesPer1InsertValuesSqlFile = 2;
var jsonItem1 = FakeJson.Create();
var jsonItem2 = FakeJson.Create();
var jsonItem3 = $@"{{
""firstName"": ""{_faker.Person.FirstName}"",
""lastName"": ""{_faker.Person.LastName}"",
""isClient"": {_faker.Random.Bool()},
""emails"": [{{ ""item"": ""{_faker.Person.Email}"" }},{{ ""item"": ""{_faker.Person.Email}"" }}]
}}";
var jsonItem4 = $@"{{
""firstName"": ""{_faker.Person.FirstName}"",
""lastName"": ""{_faker.Person.LastName}"",
""isClient"": {_faker.Random.Bool()},
""emails"": [{{ ""item"": ""{_faker.Person.Email}"" }},{{ ""item"": ""{_faker.Person.Email}"" }}]
}}";
var jsonItem5 = $@"{{
""firstName"": ""{_faker.Person.FirstName}"",
""lastName"": ""{_faker.Person.LastName}"",
""isClient"": {_faker.Random.Bool()},
""emails"": [{{ ""item"": ""{_faker.Person.Email}"" }},{{ ""item"": ""{_faker.Person.Email}"" }}]
}}";
var jsonContent = new List <string>()
{
jsonItem1, jsonItem2, jsonItem3, jsonItem4, jsonItem5
}.AsJsonString();
_fileReader.ReadAllTextAsync(_transformOptions.SourceJsonFilePath).Returns(jsonContent);
_transformOptions.MaxLinesPer1InsertValuesSqlFile = maxLinesPer1InsertValuesSqlFile;
// Act
await _testModule.ExecuteAsync(_transformOptions);
// Assert
_sqlBuilder.Received(3).BuildInsert(jsonContent, skip: Arg.Any <int>(), limit: maxLinesPer1InsertValuesSqlFile);
}
