Skip to content

Commit 0eae5f1

Browse files
author
Jelte Fennema
committed
Add more extensive tests for graphql query generation
1 parent 04ec286 commit 0eae5f1

4 files changed

Lines changed: 168 additions & 63 deletions

File tree

‎DataGateway.Service.Tests/MsSqlTests/MsSqlGraphQLQueryTests.cs‎

Lines changed: 151 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -35,16 +35,6 @@ public static void InitializeTestFixture(TestContext context)
3535
_graphQLController = new GraphQLController(_graphQLService);
3636
}
3737

38-
/// <summary>
39-
/// Cleans up querying table used for Tests in this class. Only to be run once at
40-
/// conclusion of test run, as defined by MSTest decorator.
41-
/// </summary>
42-
[ClassCleanup]
43-
public static void CleanupTestFixture()
44-
{
45-
CleanupTestFixture(_integrationTableName);
46-
}
47-
4838
#endregion
4939

5040
#region Tests
@@ -70,6 +60,157 @@ public async Task MultipleResultQuery()
7060
Assert.AreEqual(actual, expected);
7161
}
7262

63+
/// <summary>
64+
/// Gets array of results for querying more than one item.
65+
/// </summary>
66+
/// <returns></returns>
67+
[TestMethod]
68+
public async Task MultipleResultJoinQuery()
69+
{
70+
string graphQLQueryName = "getBooks";
71+
string graphQLQuery = @"{
72+
getBooks(first: 100) {
73+
id
74+
title
75+
publisher_id
76+
publisher {
77+
id
78+
name
79+
}
80+
reviews(first: 100) {
81+
id
82+
content
83+
}
84+
}
85+
}";
86+
string msSqlQuery = @"
87+
SELECT TOP 100 [table0].[id] AS [id],
88+
[table0].[title] AS [title],
89+
[table0].[publisher_id] AS [publisher_id],
90+
JSON_QUERY([table1_subq].[data]) AS [publisher],
91+
JSON_QUERY(COALESCE([table2_subq].[data], '[]')) AS [reviews]
92+
FROM [books] AS [table0]
93+
OUTER APPLY (
94+
SELECT TOP 1 [table1].[id] AS [id],
95+
[table1].[name] AS [name]
96+
FROM [publishers] AS [table1]
97+
WHERE [table0].[publisher_id] = [table1].[id]
98+
ORDER BY [id]
99+
FOR JSON PATH,
100+
INCLUDE_NULL_VALUES,
101+
WITHOUT_ARRAY_WRAPPER
102+
) AS [table1_subq]([data])
103+
OUTER APPLY (
104+
SELECT TOP 100 [table2].[id] AS [id],
105+
[table2].[content] AS [content]
106+
FROM [reviews] AS [table2]
107+
WHERE [table0].[id] = [table2].[book_id]
108+
ORDER BY [id]
109+
FOR JSON PATH,
110+
INCLUDE_NULL_VALUES
111+
) AS [table2_subq]([data])
112+
WHERE 1 = 1
113+
ORDER BY [id]
114+
FOR JSON PATH,
115+
INCLUDE_NULL_VALUES";
116+
117+
string actual = await GetGraphQLResultAsync(graphQLQuery, graphQLQueryName);
118+
string expected = await GetDatabaseResultAsync(msSqlQuery);
119+
120+
Assert.AreEqual(actual, expected);
121+
}
122+
123+
/// <summary>
124+
/// Gets array of results for querying more than one item.
125+
/// </summary>
126+
/// <returns></returns>
127+
[TestMethod]
128+
public async Task DeeplyNestedJoinQuery()
129+
{
130+
string graphQLQueryName = "getBooks";
131+
string graphQLQuery = @"{
132+
getBooks(first: 100) {
133+
title
134+
publisher {
135+
name
136+
books(first: 100) {
137+
title
138+
publisher {
139+
name
140+
books(first: 100) {
141+
title
142+
publisher {
143+
name
144+
}
145+
}
146+
}
147+
}
148+
}
149+
}
150+
}";
151+
string msSqlQuery = @"
152+
SELECT TOP 100 [table0].[title] AS [title],
153+
JSON_QUERY([table1_subq].[data]) AS [publisher]
154+
FROM [books] AS [table0]
155+
OUTER APPLY (
156+
SELECT TOP 1 [table1].[name] AS [name],
157+
JSON_QUERY(COALESCE([table2_subq].[data], '[]')) AS [books]
158+
FROM [publishers] AS [table1]
159+
OUTER APPLY (
160+
SELECT TOP 100 [table2].[title] AS [title],
161+
JSON_QUERY([table3_subq].[data]) AS [publisher]
162+
FROM [books] AS [table2]
163+
OUTER APPLY (
164+
SELECT TOP 1 [table3].[name] AS [name],
165+
JSON_QUERY(COALESCE([table4_subq].[data], '[]')) AS [books]
166+
FROM [publishers] AS [table3]
167+
OUTER APPLY (
168+
SELECT TOP 100 [table4].[title] AS [title],
169+
JSON_QUERY([table5_subq].[data]) AS [publisher]
170+
FROM [books] AS [table4]
171+
OUTER APPLY (
172+
SELECT TOP 1 [table5].[name] AS [name]
173+
FROM [publishers] AS [table5]
174+
WHERE [table4].[publisher_id] = [table5].[id]
175+
ORDER BY [id]
176+
FOR JSON PATH,
177+
INCLUDE_NULL_VALUES,
178+
WITHOUT_ARRAY_WRAPPER
179+
) AS [table5_subq]([data])
180+
WHERE [table3].[id] = [table4].[publisher_id]
181+
ORDER BY [id]
182+
FOR JSON PATH,
183+
INCLUDE_NULL_VALUES
184+
) AS [table4_subq]([data])
185+
WHERE [table2].[publisher_id] = [table3].[id]
186+
ORDER BY [id]
187+
FOR JSON PATH,
188+
INCLUDE_NULL_VALUES,
189+
WITHOUT_ARRAY_WRAPPER
190+
) AS [table3_subq]([data])
191+
WHERE [table1].[id] = [table2].[publisher_id]
192+
ORDER BY [id]
193+
FOR JSON PATH,
194+
INCLUDE_NULL_VALUES
195+
) AS [table2_subq]([data])
196+
WHERE [table0].[publisher_id] = [table1].[id]
197+
ORDER BY [id]
198+
FOR JSON PATH,
199+
INCLUDE_NULL_VALUES,
200+
WITHOUT_ARRAY_WRAPPER
201+
) AS [table1_subq]([data])
202+
WHERE 1 = 1
203+
ORDER BY [id]
204+
FOR JSON PATH,
205+
INCLUDE_NULL_VALUES
206+
";
207+
208+
string actual = await GetGraphQLResultAsync(graphQLQuery, graphQLQueryName);
209+
string expected = await GetDatabaseResultAsync(msSqlQuery);
210+
211+
Assert.AreEqual(actual, expected);
212+
}
213+
73214
#endregion
74215

75216
#region Query Test Helper Functions

‎DataGateway.Service.Tests/MsSqlTests/MsSqlRestApiTests.cs‎

Lines changed: 13 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,7 @@ public class MsSqlRestApiTests : MsSqlTestBase
1818
#region Test Fixture Setup
1919
private static RestService _restService;
2020
private static RestController _restController;
21-
private static readonly string _integrationTableName = "characterTableForRestApi";
21+
private static readonly string _integrationTableName = "books";
2222

2323
/// <summary>
2424
/// Sets up test fixture for class, only to be run once per test run, as defined by
@@ -36,16 +36,6 @@ public static void InitializeTestFixture(TestContext context)
3636
_restController = new RestController(_restService);
3737
}
3838

39-
/// <summary>
40-
/// Cleans up querying table used for Tests in this class. Only to be run once at
41-
/// conclusion of test run, as defined by MSTest decorator.
42-
/// </summary>
43-
[ClassCleanup]
44-
public static void CleanupTestFixture()
45-
{
46-
CleanupTestFixture(_integrationTableName);
47-
}
48-
4939
#endregion
5040

5141
#region Positive Tests
@@ -74,8 +64,8 @@ await PerformTest(_restController.FindById,
7464
public async Task FindByIdTestWithQueryStringFields()
7565
{
7666
string primaryKeyRoute = "id/1";
77-
string queryStringWithFields = "?_f=id,name,type";
78-
string msSqlQuery = $"SELECT [id], [name], [type] FROM { _integrationTableName } " +
67+
string queryStringWithFields = "?_f=id,title";
68+
string msSqlQuery = $"SELECT [id], [title] FROM { _integrationTableName } " +
7969
$"WHERE id = 1 FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER";
8070

8171
await PerformTest(_restController.FindById,
@@ -97,7 +87,7 @@ await PerformTest(_restController.FindById,
9787
public async Task FindByIdTestWithInvalidFields()
9888
{
9989
string primaryKeyRoute = "id/1";
100-
string queryStringWithFields = "?_f=id,null,type";
90+
string queryStringWithFields = "?_f=id,null";
10191
string msSqlQuery = $"SELECT [id], [name], [type] FROM { _integrationTableName } " +
10292
$"WHERE id = 1 FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER";
10393

@@ -135,12 +125,20 @@ private static async Task PerformTest(Func<string, string, Task<JsonDocument>> a
135125
try
136126
{
137127
JsonDocument actualJson = await api(entityName, primaryKeyRoute);
128+
Assert.IsFalse(expectException);
138129
string expected = await GetDatabaseResultAsync(msSqlQuery);
139130
Assert.AreEqual(expected, ToJsonString(actualJson));
140131
}
141132
catch (Exception)
142133
{
143-
Assert.IsTrue(expectException);
134+
if (expectException)
135+
{
136+
Assert.IsTrue(expectException);
137+
}
138+
else
139+
{
140+
throw;
141+
}
144142
}
145143

146144
}

‎DataGateway.Service.Tests/MsSqlTests/MsSqlTestBase.cs‎

Lines changed: 3 additions & 37 deletions
Original file line numberDiff line numberDiff line change
@@ -43,41 +43,10 @@ protected static void InitializeTestFixture(TestContext context, string tableNam
4343
// Setup Integration DB Components
4444
//
4545
_databaseInteractor = new DatabaseInteractor(_queryExecutor);
46-
GetDatabaseResultAsync(File.ReadAllText("books.sql")).Wait();
47-
CreateTable(tableName);
48-
InsertData(tableName);
49-
}
50-
51-
/// <summary>
52-
/// Cleans up querying table used for Tests in this class. Only to be run once at
53-
/// conclusion of test run, as defined by MSTest decorator.
54-
/// </summary>
55-
[ClassCleanup]
56-
protected static void CleanupTestFixture(string tableName)
57-
{
58-
_databaseInteractor.DropTable(tableName);
46+
using DbDataReader _ = _databaseInteractor.QueryExecutor.ExecuteQueryAsync(File.ReadAllText("books.sql"), null).Result;
5947
}
6048

6149
#region Helper Functions
62-
/// <summary>
63-
/// Creates the given table.
64-
/// </summary>
65-
/// <param name="tableName">The table name.</param>
66-
private static void CreateTable(string tableName)
67-
{
68-
_databaseInteractor.CreateTable(tableName, "id int, name varchar(20), type varchar(20), homePlanet int, primaryFunction varchar(20)");
69-
}
70-
71-
/// <summary>
72-
/// Inserts some default data into the table.
73-
/// </summary>
74-
private static void InsertData(string tableName)
75-
{
76-
_databaseInteractor.InsertData(tableName, "'1', 'Mace', 'Jedi','1','Master'");
77-
_databaseInteractor.InsertData(tableName, "'2', 'Plo Koon', 'Jedi','2','Master'");
78-
_databaseInteractor.InsertData(tableName, "'3', 'Yoda', 'Jedi','3','Master'");
79-
}
80-
8150
/// <summary>
8251
/// returns httpcontext with body consisting of the given data.
8352
/// </summary>
@@ -115,13 +84,10 @@ protected static DefaultHttpContext GetHttpContextWithQueryString(string querySt
11584
/// <returns>string in JSON format</returns>
11685
public static async Task<string> GetDatabaseResultAsync(string queryText)
11786
{
118-
JsonDocument sqlResult = JsonDocument.Parse("{ }");
87+
_ = JsonDocument.Parse("{ }");
11988
using DbDataReader reader = await _databaseInteractor.QueryExecutor.ExecuteQueryAsync(queryText, parameters: null);
12089

121-
if (await reader.ReadAsync())
122-
{
123-
sqlResult = JsonDocument.Parse(reader.GetString(0));
124-
}
90+
JsonDocument sqlResult = JsonDocument.Parse(await SqlQueryEngine.GetJsonStringFromDbReader(reader));
12591

12692
JsonElement sqlResultData = sqlResult.RootElement;
12793

‎DataGateway.Service/Resolvers/SqlQueryEngine.cs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -38,7 +38,7 @@ public void RegisterResolver(GraphQLQueryResolver resolver)
3838
// no-op
3939
}
4040

41-
private static async Task<string> GetJsonStringFromDbReader(DbDataReader dbDataReader)
41+
public static async Task<string> GetJsonStringFromDbReader(DbDataReader dbDataReader)
4242
{
4343
StringBuilder jsonString = new();
4444
// Even though we only return a single cell, we need this loop for

0 commit comments

Comments
 (0)