public void Compile_WithJunctionOrderBy_SqlShouldIncludeJoinAndOrderByClause()
{
var schema = CreateSimpleSchema(builder =>
{
builder.Types.For("Product")
.SqlTable("products", "id");
builder.Types.For("Product")
.FieldFor("relatedProducts", null)
.SqlJunction("productRelations",
(join, _, __, ___) => join.On("id", "productId"),
(join, _, __, ___) => join.On("relatedProductId", "id"))
.OrderBy((order, _, __, ___) => order.By("productId"));
});
var query = "{ product { name, relatedProducts { name } } }";
var context = CreateResolveFieldContext(schema, query);
var converter = new QueryToSqlConverter(new DefaultAliasGenerator());
var compiler = new SqlCompiler(new SqlDialectStub());
var node = converter.Convert(context);
var sql = compiler.Compile(node, context);
sql.Sql.Should().Be("SELECT\n \"product\".\"id\" AS \"id\",\n \"product\".\"name\" AS \"name\",\n \"relatedProducts\".\"id\" AS \"relatedProducts__id\",\n \"relatedProducts\".\"name\" AS \"relatedProducts__name\"\nFROM \"products\" AS \"product\"\nLEFT JOIN \"productRelations\" \"productRelations\" ON \"product\".\"id\" = \"productRelations\".\"productId\"\nLEFT JOIN \"products\" \"relatedProducts\" ON \"productRelations\".\"relatedProductId\" = \"relatedProducts\".\"id\"\nORDER BY \"relatedProducts\".\"productId\" ASC");
}
public void Compile_WithJoinAndPaginate_SqlShouldIncludePaginationClause()
{
var schema = CreateSimpleSchema(builder =>
{
builder.Types.For("Product")
.SqlTable("products", "id");
builder.Types.For("ProductVariant")
.SqlTable("productVariants", "id");
builder.Types.For("Product")
.FieldFor("variants", null)
.SqlJoin((join, _, __, ___) => join.On("id", "productId"))
.SqlPaginate(true)
.SqlOrder((order, _, __, ___) => order.By("id"));
});
var query = "{ product { name, variants { edges { node { name } } } } }";
var context = CreateResolveFieldContext(schema, query);
var joinedOneToManyPaginatedSql = "LEFT JOIN LATERAL (\n SELECT \"variants\".*, COUNT(*) OVER () AS \"$total\"\n FROM \"productVariants\" \"variants\"\n WHERE \"products\".\"id\" = \"variants\".\"productId\"\n ORDER BY \"variants\".\"id\" ASC\n LIMIT ALL OFFSET 0\n) \"variants\" ON \"products\".\"id\" = \"variants\".\"productId\"";
var converter = new QueryToSqlConverter(new DefaultAliasGenerator());
var dialect = new SqlDialectStub(joinedOneToManyPaginatedSql: joinedOneToManyPaginatedSql);
var compiler = new SqlCompiler(dialect);
var node = converter.Convert(context);
var sql = compiler.Compile(node, context);
sql.Sql.Should().Be($"SELECT\n \"product\".\"id\" AS \"id\",\n \"product\".\"name\" AS \"name\",\n \"variants\".\"id\" AS \"variants__id\",\n \"variants\".\"name\" AS \"variants__name\",\n \"variants\".\"$total\" AS \"variants__$total\"\nFROM \"products\" AS \"product\"\n{joinedOneToManyPaginatedSql}\nORDER BY \"variants\".\"id\" ASC");
}
public void Compile_WithWhereAndOrderBy_GeneratesValidSQL()
{
var schema = CreateSimpleSchema(builder =>
{
builder.Types.For("Product")
.SqlTable("products", "id");
builder.Types.For("Query")
.FieldFor("products", null)
.SqlWhere((where, _, __, ___) => where.Column("id", 0, "<>"))
.SqlOrder((order, _, __, ___) => order.By("name").ThenByDescending("price"));
});
var query = "{ products { name } }";
var context = CreateResolveFieldContext(schema, query);
var converter = new QueryToSqlConverter(new DefaultAliasGenerator());
var compiler = new SqlCompiler(new SqlDialectStub());
var node = converter.Convert(context);
var sql = compiler.Compile(node, context);
var expectedSql =
"SELECT\n \"products\".\"id\" AS \"id\",\n \"products\".\"name\" AS \"name\"\nFROM \"products\" AS \"products\"\nWHERE \"products\".\"id\" <> @p0\nORDER BY \"products\".\"name\" ASC, \"products\".\"price\" DESC";
var expectedParameters = new Dictionary <string, object> {
{ "@p0", 0 }
};
sql.Should()
.BeEquivalentTo(new SqlResult(expectedSql, expectedParameters));
}
public void Compile_WithRawJoinCondition_SqlShouldIncludeJoinCondition()
{
var schema = CreateSimpleSchema(builder =>
{
builder.Types.For("Query")
.FieldFor("product", null);
builder.Types.For("Product")
.SqlTable("products", "id");
builder.Types.For("Product")
.FieldFor("variants", null)
.SqlJoin((join, _, __, ___) => join.Raw($"{join.ParentTableAlias}.\"id\" = {@join.ChildTableAlias}.\"productId\"", @from: $"LEFT JOIN {join.ChildTableName} {join.ChildTableAlias}"));
builder.Types.For("ProductVariant")
.SqlTable("productVariants", "id");
});
var query = "{ product { name, variants { edges { node { name } } } } }";
var context = CreateResolveFieldContext(schema, query);
var converter = new QueryToSqlConverter(new DefaultAliasGenerator());
var compiler = new SqlCompiler(new SqlDialectStub());
var node = converter.Convert(context);
var sql = compiler.Compile(node, context);
sql.Sql.Should().Be("SELECT\n \"product\".\"id\" AS \"id\",\n \"product\".\"name\" AS \"name\",\n \"variants\".\"id\" AS \"variants__id\",\n \"variants\".\"name\" AS \"variants__name\"\nFROM \"products\" AS \"product\"\nLEFT JOIN \"productVariants\" \"variants\" ON \"product\".\"id\" = \"variants\".\"productId\"");
}
public void Compile_WithWhereQueryAndArguments_PassesArgumentsToWhereDelegate()
{
var schema = CreateSimpleSchema(builder =>
{
builder.Types.For("Product")
.SqlTable("products", "id");
builder.Types.For("Query")
.FieldFor("product", null)
.SqlWhere((where, arguments, _, __) => where.Column("id", arguments["id"]));
});
var query = "{ product(id: \"3\") { name } }";
var context = CreateResolveFieldContext(schema, query);
var converter = new QueryToSqlConverter(new DefaultAliasGenerator());
var compiler = new SqlCompiler(new SqlDialectStub());
var node = converter.Convert(context);
var sql = compiler.Compile(node, context);
var expectedSql = "SELECT\n \"product\".\"id\" AS \"id\",\n \"product\".\"name\" AS \"name\"\nFROM \"products\" AS \"product\"\nWHERE \"product\".\"id\" = @p0";
var expectedParameters = new Dictionary <string, object> {
{ "@p0", "3" }
};
sql.Should().BeEquivalentTo(new SqlResult(expectedSql, expectedParameters));
}
/// <summary>
/// Takes a <see cref="IResolveFieldContext"/> and returns a hydrated object with the data.
/// </summary>
/// <param name="context">The <see cref="IResolveFieldContext"/>.</param>
/// <param name="databaseCall">A <see cref="DatabaseCallDelegate"/> that is passed the compiled SQL and calls the database and returns a <see cref="IDataReader"/>.</param>
/// <param name="cancellationToken">The <see cref="CancellationToken"/>.</param>
/// <returns>The correctly nested data from the database.</returns>
/// <exception cref="ArgumentNullException">If <c>context</c> or <c>databaseCall</c> is null.</exception>
public async Task <object?> ExecuteAsync(IResolveFieldContext context, DatabaseCallDelegate databaseCall,
CancellationToken cancellationToken)
{
if (context == null)
{
throw new ArgumentNullException(nameof(context));
}
if (databaseCall == null)
{
throw new ArgumentNullException(nameof(databaseCall));
}
var sqlAst = _converter.Convert(context);
var sqlResult = _compiler.Compile(sqlAst, context);
var data = new List <Dictionary <string, object?> >();
using (var reader = await databaseCall(sqlResult.Sql, sqlResult.Parameters).ConfigureAwait(false))
{
while (await reader.ReadAsync(cancellationToken))
{
var item = new Dictionary <string, object?>();
for (var i = 0; i < reader.FieldCount; ++i)
{
var value = await reader.IsDBNullAsync(i, cancellationToken)
? null
: await reader.GetFieldValueAsync <object>(i, cancellationToken);
item[reader.GetName(i)] = value;
}
data.Add(item);
}
}
var objectShape = _objectShaper.DefineObjectShape(sqlAst);
#pragma warning disable 8620
var nested = _hydrator.Nest(data, objectShape);
var result = _arrayToConnectionConverter.Convert(nested, sqlAst, context);
#pragma warning restore 8620
if (result == null)
{
return(null);
}
await _batchPlanner.NextBatch(sqlAst, result, databaseCall, context, cancellationToken).ConfigureAwait(false);
return(result);
}
public void Compile_WhenTableConfigHasCompositeUniqueKey_KeysAreIncludedInGeneratedSql()
{
var schema = CreateSimpleSchema(builder =>
{
builder.Types.For("Product")
.SqlTable("products", new [] { "id", "firstName", "lastName" });
});
var query = "{ product { name } }";
var context = CreateResolveFieldContext(schema, query);
var converter = new QueryToSqlConverter(new DefaultAliasGenerator());
var compiler = new SqlCompiler(new SqlDialectStub());
var node = converter.Convert(context);
var sql = compiler.Compile(node, context);
sql.Sql.Should().Be("SELECT\n CONCAT(\"product\".\"id\", \"product\".\"firstName\", \"product\".\"lastName\") AS \"id#firstName#lastName\",\n \"product\".\"name\" AS \"name\"\nFROM \"products\" AS \"product\"");
}
public void Compile_WhenQueryIncludesUniqueKey_ColumnIsOnlySelectedOnce()
{
var schema = CreateSimpleSchema(builder =>
{
builder.Types.For("Product")
.SqlTable("products", "id");
});
var query = "{ product { id, name } }";
var context = CreateResolveFieldContext(schema, query);
var converter = new QueryToSqlConverter(new DefaultAliasGenerator());
var compiler = new SqlCompiler(new SqlDialectStub());
var node = converter.Convert(context);
var sql = compiler.Compile(node, context);
sql.Sql.Should().Be("SELECT\n \"product\".\"id\" AS \"id\",\n \"product\".\"name\" AS \"name\"\nFROM \"products\" AS \"product\"");
}
public void Compile_WithMinifyAliasGenerator_SqlHasMinifiedTableAndColumnNames()
{
var schema = CreateSimpleSchema(builder =>
{
builder.Types.For("Product")
.SqlTable("products", "id");
});
var query = "{ product { id, name } }";
var context = CreateResolveFieldContext(schema, query);
var converter = new QueryToSqlConverter(new MinifyAliasGenerator());
var compiler = new SqlCompiler(new SqlDialectStub());
var node = converter.Convert(context);
var sql = compiler.Compile(node, context);
sql.Sql.Should().Be("SELECT\n \"a\".\"id\" AS \"b\",\n \"a\".\"name\" AS \"c\"\nFROM \"products\" AS \"a\"");
}
public void Compile_WithOrderBy_SqlShouldIncludeOrderByClause()
{
var schema = CreateSimpleSchema(builder =>
{
builder.Types.For("Product")
.SqlTable("products", "id");
builder.Types.For("Query")
.FieldFor("products", null)
.SqlOrder((order, _, __, ___) => order.By("name").ThenByDescending("price"));
});
var query = "{ products { name } }";
var context = CreateResolveFieldContext(schema, query);
var converter = new QueryToSqlConverter(new DefaultAliasGenerator());
var compiler = new SqlCompiler(new SqlDialectStub());
var node = converter.Convert(context);
var sql = compiler.Compile(node, context);
sql.Sql.Should().Be("SELECT\n \"products\".\"id\" AS \"id\",\n \"products\".\"name\" AS \"name\"\nFROM \"products\" AS \"products\"\nORDER BY \"products\".\"name\" ASC, \"products\".\"price\" DESC");
}