Example #1
0
        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");
        }
Example #2
0
        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));
        }
Example #3
0
        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");
        }
Example #4
0
        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\"");
        }
Example #5
0
        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));
        }
Example #6
0
    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);
    }
Example #7
0
        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");
        }
Example #8
0
        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\"");
        }
Example #9
0
        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\"");
        }
Example #10
0
        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\"");
        }
Example #11
0
        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");
        }
Example #12
0
 /// <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)));
Example #13
0
 /// <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)));
Example #14
0
 /// <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)));
Example #15
0
        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));
        }