public void DefineObjectShape_WhenCalledWithObject_ReturnsObjectShapeDefinition()
{
var node = new SqlTable(null, null, "products", "products", "products", new Dictionary <string, object>(),
true);
node.AddColumn("id", "id", "id", true);
node.AddColumn("name", "name", "name");
var variantsTable = node.AddTable(null, "variants", "variants", "variants", new Dictionary <string, object>(), true);
variantsTable.AddColumn("id", "id", "id", true);
variantsTable.AddColumn("name", "name", "name");
variantsTable.SortKey = new SortKey("products", "sortOrder", "sortOrder", typeof(int), SortDirection.Ascending);
var colorsTable = variantsTable.AddTable(null, "colors", "color", "color", new Dictionary <string, object>(), true);
colorsTable.AddColumn("id", "id", "id", true);
colorsTable.AddColumn("color", "color", "color");
var objectShaper = new ObjectShaper(new SqlAstValidator());
var definition = objectShaper.DefineObjectShape(node);
var json = JsonConvert.SerializeObject(definition, new JsonSerializerSettings
{
ContractResolver = new CamelCasePropertyNamesContractResolver()
});
json.Should()
.Be("{\"properties\":[{\"name\":\"id\",\"column\":\"id\",\"isId\":true},{\"name\":\"name\",\"column\":\"name\",\"isId\":false},{\"name\":\"variants\",\"properties\":[{\"name\":\"sortOrder\",\"column\":\"variants__sortOrder\",\"isId\":false},{\"name\":\"id\",\"column\":\"variants__id\",\"isId\":true},{\"name\":\"name\",\"column\":\"variants__name\",\"isId\":false},{\"name\":\"color\",\"properties\":[{\"name\":\"id\",\"column\":\"variants__color__id\",\"isId\":true},{\"name\":\"color\",\"column\":\"variants__color__color\",\"isId\":false}]}]}]}");
}
public void HandleJoinedOneToManyPaginated_WhenCalledWithKeysetPagination_ReturnsPagedJoinString()
{
var dialect = new PostgresSqlDialect();
var compilerContext = new SqlCompilerContext(new SqlCompiler(dialect));
var parent = new SqlTable(null, null, "products", "products", "products", new Dictionary <string, object>(), true);
parent.AddColumn("id", "id", "id", true);
var node = new SqlTable(null, null, "variants", "variants", "variants", new Dictionary <string, object>(), true)
{
Join = (join, _, __, ___) => join.On("id", "productId"),
OrderBy = new OrderBy("products", "id", SortDirection.Ascending),
SortKey = new SortKey("products", "id", "id", typeof(int), SortDirection.Ascending),
Where = (where, _, __, ___) => where.Column("id", 1, "<>")
};
node.AddColumn("id", "id", "id", true);
var arguments = new Dictionary <string, object>();
var context = new ResolveFieldContext();
var tables = new List <string>();
dialect.HandleJoinedOneToManyPaginated(parent, node, arguments, context, tables, compilerContext,
"\"products\".\"id\" = \"variants\".\"productId\"");
tables.Should()
.Contain(@"LEFT JOIN LATERAL (
SELECT ""variants"".*
FROM ""variants"" ""variants""
WHERE ""products"".""id"" = ""variants"".""productId"" AND ""variants"".""id"" <> @p0
ORDER BY ""variants"".""id"" ASC
LIMIT ALL
) ""variants"" ON ""products"".""id"" = ""variants"".""productId""");
}
public void HandleJoinedOneToManyPaginated_WhenCalledWithOffsetPagination_ReturnsPagedJoinString()
{
var dialect = new PostgresSqlDialect();
var compilerContext = new SqlCompilerContext(new SqlCompiler(dialect));
var parent = new SqlTable(null, null, "products", "products", "products", new Dictionary <string, object>(), true);
parent.AddColumn("id", "id", "id", true);
var node = new SqlTable(null, null, "variants", "variants", "variants", new Dictionary <string, object>(), true)
{
Join = (join, _, __, ___) => join.On("id", "productId"),
OrderBy = new OrderBy("variants", "id", SortDirection.Ascending),
Where = (where, _, __, ___) => where.Column("id", 1, "<>")
};
node.AddColumn("id", "id", "id", true);
var arguments = new Dictionary <string, object>();
var context = new ResolveFieldContext();
var tables = new List <string>();
dialect.HandleJoinedOneToManyPaginated(parent, node, arguments, context, tables, compilerContext,
"\"products\".\"id\" = \"variants\".\"productId\"");
tables.Should()
.Contain("LEFT JOIN LATERAL (\n SELECT \"variants\".*, COUNT(*) OVER () AS \"$total\"\n FROM \"variants\" \"variants\"\n WHERE \"products\".\"id\" = \"variants\".\"productId\" AND \"variants\".\"id\" <> @p0\n ORDER BY \"variants\".\"id\" ASC\n LIMIT ALL OFFSET 0\n) \"variants\" ON \"products\".\"id\" = \"variants\".\"productId\"");
}
public void HandleJoinedOneToManyPaginated_WhenCalledWithKeysetPaginationAndFirstAndAfter_ReturnsPagedJoinString()
{
var dialect = new PostgresSqlDialect();
var compilerContext = new SqlCompilerContext(new SqlCompiler(dialect));
var parent = new SqlTable(null, null, "products", "products", "products", new Dictionary <string, object>(),
true);
parent.AddColumn("id", "id", "id", true);
var node = new SqlTable(null, null, "variants", "variants", "variants", new Dictionary <string, object>(),
true)
{
Join = (join, _, __, ___) => join.On("id", "productId"),
SortKey = new SortKey("products", "id", "id", typeof(int), SortDirection.Descending),
Where = (where, _, __, ___) => where.Column("id", 1, "<>")
};
node.AddColumn("id", "id", "id", true);
var arguments = new Dictionary <string, object>
{
{ "first", 2 },
{ "after", Convert.ToBase64String(Encoding.UTF8.GetBytes(JsonSerializer.Serialize(new { id = 1 }))) }
};
var context = new ResolveFieldContext();
var tables = new List <string>();
dialect.HandleJoinedOneToManyPaginated(parent, node, arguments, context, tables, compilerContext,
"\"products\".\"id\" = \"variants\".\"productId\"");
using (new AssertionScope())
{
tables.Should()
.Contain(@"LEFT JOIN LATERAL (
SELECT ""variants"".*
FROM ""variants"" ""variants""
WHERE ""products"".""id"" = ""variants"".""productId"" AND ""variants"".""id"" <> @p0 AND ""variants"".""id"" < @p1
ORDER BY ""variants"".""id"" DESC
LIMIT 3
) ""variants"" ON ""products"".""id"" = ""variants"".""productId""");
compilerContext.Parameters.Should()
.BeEquivalentTo(new Dictionary <string, object>
{
{ "@p0", 1 },
{ "@p1", 1 }
});
}
}
public void HandlePaginationAtRoot_WhenCalledWithKeysetPaginationAndMultipleSortKeysAndFirstAndAfter_ReturnsPagedJoinString()
{
var dialect = new PostgresSqlDialect();
var compilerContext = new SqlCompilerContext(new SqlCompiler(dialect));
var node = new SqlTable(null, null, "variants", "variants", "variants", new Dictionary <string, object>(), true)
{
SortKey = new SortKey("products", "price", "price", typeof(decimal), SortDirection.Descending)
{
ThenBy = new SortKey("products", "name", "name", typeof(string), SortDirection.Ascending)
},
Where = (where, _, __, ___) => where.Column("id", 1, "<>")
};
node.AddColumn("id", "id", "id", true);
var arguments = new Dictionary <string, object>
{
{ "first", 2 },
{ "after", Convert.ToBase64String(Encoding.UTF8.GetBytes(JsonSerializer.Serialize(new { price = 199, name = "Jacket" }))) }
};
var context = new ResolveFieldContext();
var tables = new List <string>();
dialect.HandlePaginationAtRoot(null, node, arguments, context, tables, compilerContext);
using (new AssertionScope())
{
tables.Should()
.Contain(@"FROM (
SELECT ""variants"".*
FROM variants ""variants""
WHERE (""variants"".""price"" < @p0 OR (""variants"".""price"" = @p1 AND ""variants"".""name"" > @p2)) AND ""variants"".""id"" <> @p3
ORDER BY ""variants"".""price"" DESC, ""variants"".""name"" ASC
LIMIT 3
) ""variants""");
compilerContext.Parameters.Should()
.BeEquivalentTo(new Dictionary <string, object>
{
{ "@p0", 199 },
{ "@p1", 199 },
{ "@p2", "Jacket" },
{ "@p3", 1 }
});
}
}
public void HandlePaginationAtRoot_WhenCalledWithKeysetPaginationAndLastAndBefore_ReturnsPagedJoinString()
{
var dialect = new PostgresSqlDialect();
var compilerContext = new SqlCompilerContext(new SqlCompiler(dialect));
var node = new SqlTable(null, null, "variants", "variants", "variants", new Dictionary <string, object>(), true)
{
OrderBy = new OrderBy("products", "id", SortDirection.Ascending),
SortKey = new SortKey("products", "id", "id", typeof(int), SortDirection.Ascending),
Where = (where, _, __, ___) => where.Column("id", 1, "<>")
};
node.AddColumn("id", "id", "id", true);
var arguments = new Dictionary <string, object>
{
{ "last", 5 },
{ "before", Convert.ToBase64String(Encoding.UTF8.GetBytes(JsonSerializer.Serialize(new { id = 2 }))) }
};
var context = new ResolveFieldContext();
var tables = new List <string>();
dialect.HandlePaginationAtRoot(null, node, arguments, context, tables, compilerContext);
using (new AssertionScope())
{
tables.Should()
.Contain(@"FROM (
SELECT ""variants"".*
FROM variants ""variants""
WHERE ""variants"".""id"" < @p0 AND ""variants"".""id"" <> @p1
ORDER BY ""variants"".""id"" DESC
LIMIT 6
) ""variants""");
compilerContext.Parameters.Should()
.BeEquivalentTo(new Dictionary <string, object>
{
{ "@p0", 2 },
{ "@p1", 1 }
});
}
}
public void HandlePaginationAtRoot_WhenCalledWithKeysetPagination_ReturnsPagedJoinString()
{
var dialect = new PostgresSqlDialect();
var compilerContext = new SqlCompilerContext(new SqlCompiler(dialect));
var node = new SqlTable(null, null, "variants", "variants", "variants", new Dictionary <string, object>(),
true)
{
OrderBy = new OrderBy("products", "id", SortDirection.Ascending),
SortKey = new SortKey("products", "id", "id", typeof(int), SortDirection.Ascending),
Where = (where, _, __, ___) => where.Column("id", 1, "<>")
};
node.AddColumn("id", "id", "id", true);
var arguments = new Dictionary <string, object>();
var context = new ResolveFieldContext();
var tables = new List <string>();
dialect.HandlePaginationAtRoot(null, node, arguments, context, tables, compilerContext);
using (new AssertionScope())
{
tables.Should()
.Contain(@"FROM (
SELECT ""variants"".*
FROM variants ""variants""
WHERE ""variants"".""id"" <> @p0
ORDER BY ""variants"".""id"" ASC
LIMIT ALL
) ""variants""");
compilerContext.Parameters.Should()
.BeEquivalentTo(new Dictionary <string, object>
{
{ "@p0", 1 }
});
}
}
private async Task NextBatchChild(SqlTable sqlTable, object?data, DatabaseCallDelegate databaseCall,
IResolveFieldContext context, CancellationToken cancellationToken)
{
var fieldName = sqlTable.FieldName;
data = data switch
{
List <object> objList => objList.Select(x => (IDictionary <string, object?>)x),
Connection <object> connection => connection.Items.Select(x => (IDictionary <string, object?>)x),
_ => data
};
// see if any begin a new batch
if (sqlTable.Batch != null || sqlTable.Junction?.Batch != null)
{
string thisKey = null !;
string parentKey = null !;
if (sqlTable.Batch != null)
{
// if so, we know we'll need to get the key for matching with the parent key
sqlTable.AddColumn(sqlTable.Batch.ThisKey);
thisKey = sqlTable.Batch.ThisKey.FieldName;
parentKey = sqlTable.Batch.ParentKey.FieldName;
}
else if (sqlTable.Junction?.Batch != null)
{
sqlTable.AddColumn(sqlTable.Junction.Batch.ThisKey);
thisKey = sqlTable.Junction.Batch.ThisKey.FieldName;
parentKey = sqlTable.Junction.Batch.ParentKey.FieldName;
}
if (data is IEnumerable <IDictionary <string, object?> > entries)
{
// the "batch scope" is the set of values to match this key against from the previous batch
var batchScope = new List <object>();
var entryList = entries.ToList();
foreach (var entry in entryList)
{
var values = PrepareValues(entry, parentKey);
batchScope.AddRange(values);
}
if (batchScope.Count == 0)
{
return;
}
// generate the SQL, with the batch scope values incorporated in a WHERE IN clause
var sqlResult = _compiler.Compile(sqlTable, context, SqlDialect.CastArray(batchScope));
var objectShape = _objectShaper.DefineObjectShape(sqlTable);
// grab the data
var newData = await HandleDatabaseCall(databaseCall, sqlResult, objectShape, cancellationToken);
// group the rows by the key so we can match them with the previous batch
var newDataGrouped = newData.GroupBy(x => x[thisKey])
.ToDictionary(x => x.Key, x => x.ToList());
// but if we paginate, we must convert to connection type first
if (sqlTable.Paginate)
{
//TODO: implement
// foreach (var group in newDataGrouped)
// newDataGrouped[group.Key] =
// (List<Dictionary<string, object?>>)
// _arrayToConnectionConverter.Convert(group.Value, sqlTable, context);
}
// if we they want many rows, give them an array
if (sqlTable.GrabMany)
{
foreach (var entry in entryList)
{
var values = PrepareValues(entry, parentKey);
var res = new List <Dictionary <string, object?> >();
foreach (var value in values)
{
if (newDataGrouped.TryGetValue(value, out var obj))
{
res.AddRange(obj);
}
}
entry[fieldName] = res;
}
}
else
{
var matchedData = new List <object>();
foreach (var entry in entryList)
{
if (entry.TryGetValue(parentKey, out var key) == false)
{
continue;
}
if (newDataGrouped.TryGetValue(key, out var list) && list.Count > 0)
{
entry[fieldName] = _arrayToConnectionConverter.Convert(list[0], sqlTable, context);
matchedData.Add(entry);
}
else
{
entry[fieldName] = null;
}
}
data = matchedData;
}
switch (data)
{
case IEnumerable <IDictionary <string, object?> > list:
{
var nextLevelData = list
.Where(x => x.Count > 0)
.Select(x =>
{
if (x.TryGetValue(fieldName, out var value) &&
value is List <Dictionary <string, object?> > dict)
{
return(dict);
}
return(null);
})
.Where(x => x is { Count: > 0 })
.SelectMany(x => x)
.Select(x => x.ToDictionary())
.ToList();
await NextBatch(sqlTable, nextLevelData, databaseCall, context, cancellationToken);
return;
}