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_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_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));
}
public void getOutputSchemaInner(string sql)
{
reader = new ArrowStreamReader(this.outputBuffer, leaveOpen: false);
// reader one batch to get the arrow schema first
reader.ReadNextRecordBatch();
this.schema = ArrowSchemaToASARecordSchema(reader.Schema);
var result =
SqlCompiler.Compile(
sql,
new QueryBindings(
new Dictionary <string, InputDescription> {
{ "input", new InputDescription(this.schema, InputType.Stream) }
}));
var step = result.Steps.First();
Schema.Builder builder = new Schema.Builder();
foreach (KeyValuePair <string, int> kv in step.Output.PayloadSchema.Ordinals.OrderBy(kv => kv.Value))
{
builder = builder.Field(f => f.Name(kv.Key).DataType(ASATypeToArrowType(step.Output.PayloadSchema[kv.Value].Schema)).Nullable(false));
}
this.outputArrowSchema = builder.Build();
this.writer = new ArrowStreamWriter(this.inputBuffer, this.outputArrowSchema);
//Write empty batch to send the schema to Java side
var emptyRecordBatch = createOutputRecordBatch(new List <IRecord>());
WriteRecordBatch(emptyRecordBatch);
}
public void Compile_WhenContextIsNull_ThrowsException()
{
var compiler = new SqlCompiler(new SqlDialectStub());
Func <SqlResult> action = () => compiler.Compile(new SqlNoop(), null);
action.Should()
.Throw <ArgumentNullException>()
.And.ParamName.Should()
.Be("context");
}
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");
}
/// <summary>
/// Query data from two tables.
/// </summary>
/// <typeparam name="T1">The type of the first table.</typeparam>
/// <typeparam name="T2">The type of the second table.</typeparam>
/// <typeparam name="T3">The type of the third table.</typeparam>
/// <typeparam name="T4">The type of the fourth table.</typeparam>
public static FromClause <T1, T2, T3, T4> From <T1, T2, T3, T4>() =>
new FromClause <T1, T2, T3, T4>(
SqlCompiler.CompileFromClause(typeof(T1), typeof(T2), typeof(T3), typeof(T4)));
/// <summary>
/// Query data from two tables.
/// </summary>
/// <typeparam name="T1">The type of the first table.</typeparam>
/// <typeparam name="T2">The type of the second table.</typeparam>
public static FromClause <T1, T2> From <T1, T2>() =>
new FromClause <T1, T2>(
SqlCompiler.CompileFromClause(typeof(T1), typeof(T2)));
/// <summary>
/// Query data from a single table.
/// </summary>
/// <typeparam name="T">The type of the table.</typeparam>
public static FromClause <T> From <T>() =>
new FromClause <T>(
SqlCompiler.CompileFromClause(typeof(T)));
static async Task Main(string[] args)
{
SQLiteConnection.CreateFile("starwars.db");
await using var connection = new SQLiteConnection("Data Source=starwars.db");
await connection.OpenAsync();
await PopulateDatabase(connection);
var compiler = new SqlCompiler(new SQLiteDialect());
var hydrator = new Hydrator();
var joinMonster = new JoinMonsterExecuter(
new QueryToSqlConverter(new DefaultAliasGenerator()),
compiler,
new BatchPlanner(compiler, hydrator),
hydrator
);
var serviceProvider = new FuncServiceProvider(type =>
{
if (type == typeof(StarWarsQuery))
{
return(new StarWarsQuery(joinMonster));
}
return(Activator.CreateInstance(type));
});
var schema = new StarWarsSchema(serviceProvider);
var query = @"
{
human(id: ""1"") {
name
id
homePlanet {
id
name
}
friends {
name
}
}
humans {
name
}
}";
var start = DateTime.UtcNow;
var documentExecuter = new DocumentExecuter();
var data = await documentExecuter.ExecuteAsync(options =>
{
options.Schema = schema;
options.FieldMiddleware.Use <InstrumentFieldsMiddleware>();
options.EnableMetrics = true;
options.Query = query;
options.UserContext = new Dictionary <string, object>
{
{ nameof(IDbConnection), connection }
};
});
// data.EnrichWithApolloTracing(start);
var writer = new DocumentWriter(true);
Console.WriteLine(await writer.WriteToStringAsync(data));
}
