public IAsyncEnumerable <BsonDocument> SearchAsync(DataApiSqlQuery parsedQuery, uint?maxResults = null)
{
var dataType = parsedQuery.FromArguments;
var query = queryBuilder.Build(parsedQuery, dataType);
return(sqlQueryExecutor.ExecuteReaderAsync(query).Select(reader => BsonDocumentBuilder.BuildFromDelimitedColumns((SqlDataReader)reader)));
}
public async Task CanSearchComponents()
{
var sut = CreateMssqlRdDataStorage(out _);
var query = new DataApiSqlQuery(
fromArguments: "Component",
selectArguments: "*",
whereArguments: "Data.business_name <> ''",
limitArguments: "10");
var components = sut.SearchAsync(query);
await foreach (var component in components)
{
Assert.That(component, Is.Not.Null);
Assert.That(component["Data"], Is.Not.Null.And.Not.EqualTo(BsonNull.Value));
}
query = new DataApiSqlQuery(
fromArguments: "Component",
selectArguments: "Data.id as ID, Data.source_system AS SourceSystem, Data.source_id",
limitArguments: "3",
whereArguments: "Data.created_by = 'auser'");
components = sut.SearchAsync(query);
await foreach (var component in components)
{
Assert.That(component, Is.Not.Null);
Assert.That(component["ID"], Is.Not.Null.And.Not.EqualTo(BsonNull.Value));
Assert.That(component["SourceSystem"], Is.Not.Null.And.Not.EqualTo(BsonNull.Value));
}
}
public async IAsyncEnumerable <GenericDataContainer> GetManyAsync(
string dataType,
string whereArguments,
string orderByArguments,
uint?limit = null)
{
if (whereArguments != null && (whereArguments.Contains("{") || whereArguments.Contains("}")))
{
throw new FormatException(); // SECURITY NOTE: This is crucial in order to prevent SQL-injection like attacks (only with MongoDB-syntax instead of SQL)
}
var collection = rdDataClient.DataDatabase.GetCollection <BsonDocument>(dataType);
var parsedQuery = new DataApiSqlQuery(
fromArguments: dataType,
whereArguments: whereArguments,
orderByArguments: orderByArguments,
limitArguments: limit >= 0 ? limit.ToString() : null);
var pipeline = AggregatePipelineBuilder.Build(parsedQuery, limit);
using var cursor = await collection.AggregateAsync(pipeline);
while (await cursor.MoveNextAsync())
{
foreach (var result in cursor.Current)
{
yield return(BsonSerializer.Deserialize <GenericDataContainer>(result));
}
}
}
public void DuplicateWhereKeywordIsParsed()
{
var query = "SELECT * FROM Component WHERE Data.business_name LIKE '%arla%' WHERE Data.source_id = '3'";
DataApiSqlQuery actual = null;
Assert.That(() => actual = DataApiSqlQueryParser.Parse(query), Throws.Nothing);
Assert.That(actual.WhereArguments, Is.EqualTo("(Data.business_name LIKE '%arla%') AND (Data.source_id = '3')"));
}
private BsonDocument MapContainerUsingSelect(GenericDataContainer container, DataApiSqlQuery parsedQuery)
{
var containerJObject = JObject.FromObject(container);
var dataJObject = JObject.Parse(DataEncoder.DecodeToJson(container.Data));
var fieldMappings = parsedQuery.SelectArguments.Split(',').Select(x => x.Trim());
var bsonDocument = new BsonDocument();
foreach (var fieldMapping in fieldMappings)
{
var match = Regex.Match(fieldMapping, "^(?<Path>[^\\s]+)(?<Map>\\s+AS\\s+(?<NewName>[^\\s]+))?", RegexOptions.IgnoreCase);
if (!match.Success)
{
throw new FormatException($"Invalid SELECT statement '{fieldMapping}'");
}
var fieldPath = match.Groups["Path"].Value;
string newName;
var hasMap = match.Groups["Map"].Success;
if (hasMap)
{
newName = match.Groups["NewName"].Value;
}
else
{
newName = fieldPath.Replace('.', '_');
}
JToken jToken;
if (fieldPath.StartsWith("Data."))
{
var token = hasMap ? newName : fieldPath.Substring("Data.".Length);
jToken = dataJObject.SelectToken(token);
}
else if (fieldPath == "Data")
{
jToken = dataJObject;
}
else
{
jToken = containerJObject.SelectToken(fieldPath);
}
BsonValue bsonValue;
if (jToken == null)
{
bsonValue = BsonNull.Value;
}
else if (jToken is JValue jValue)
{
bsonValue = BsonValue.Create(jValue.Value);
}
else
{
bsonValue = BsonDocument.Parse(jToken.ToString());
}
bsonDocument.Add(new BsonElement(newName, bsonValue));
}
return(bsonDocument);
}
public string Build(DataApiSqlQuery parsedQuery, string tablePath, uint?hardLimitMaxResults = null)
{
if (!string.IsNullOrWhiteSpace(parsedQuery.SkipArguments))
{
throw new NotSupportedException("SKIP is not supported for SQL backends");
}
var queryParts = new List <string>();
var limit = hardLimitMaxResults;
if (!string.IsNullOrWhiteSpace(parsedQuery.LimitArguments))
{
var softLimit = int.Parse(parsedQuery.LimitArguments);
if (softLimit >= 0)
{
if (!hardLimitMaxResults.HasValue || softLimit < hardLimitMaxResults.Value)
{
limit = (uint)softLimit;
}
}
}
var limitStatement = limit.HasValue ? $"TOP ({limit})" : string.Empty;
if (!string.IsNullOrWhiteSpace(parsedQuery.SelectArguments))
{
var fieldNameReplacedSelectArgument = fieldNameManipulationFunc(parsedQuery.SelectArguments.Replace("COUNT()", "COUNT(*)"));
queryParts.Add($"SELECT {limitStatement} {fieldNameReplacedSelectArgument}");
}
else
{
queryParts.Add($"SELECT {limitStatement} *");
}
queryParts.Add($"FROM {tablePath}");
if (!string.IsNullOrWhiteSpace(parsedQuery.JoinArguments))
{
queryParts.Add($"JOIN {fieldNameManipulationFunc(parsedQuery.JoinArguments)}");
}
if (!string.IsNullOrWhiteSpace(parsedQuery.WhereArguments))
{
var sqlWhereArguments = whereClauseParser.Parse(parsedQuery.WhereArguments);
queryParts.Add($"WHERE {sqlWhereArguments}");
}
if (!string.IsNullOrWhiteSpace(parsedQuery.GroupByArguments))
{
queryParts.Add($"GROUP BY {fieldNameManipulationFunc(parsedQuery.GroupByArguments)}");
}
if (!string.IsNullOrWhiteSpace(parsedQuery.OrderByArguments))
{
queryParts.Add($"ORDER BY {fieldNameManipulationFunc(parsedQuery.OrderByArguments)}");
}
return(string.Join(' ', queryParts));
}
public async IAsyncEnumerable <BsonDocument> SearchAsync(DataApiSqlQuery parsedQuery, uint?maxResults = null)
{
var pipeline = AggregatePipelineBuilder.Build(parsedQuery, maxResults);
var collection = rdDataClient.DataDatabase.GetCollection <BsonDocument>(parsedQuery.FromArguments);
using var cursor = await collection.AggregateAsync(pipeline);
while (await cursor.MoveNextAsync())
{
foreach (var result in cursor.Current)
{
yield return(result);
}
}
}
public void SqlLikeQueryHandlesDifferentWhitespaces()
{
var query = "SELECT Product.Name From Products where timestamp >= '2018-01-01' AND Product.Price.Netto < 300\n" +
"ORDER BY timestamp ASC SKIP 5 LIMIT 10";
DataApiSqlQuery actual = null;
Assert.That(() => actual = DataApiSqlQueryParser.Parse(query), Throws.Nothing);
Assert.That(actual.SelectArguments, Is.EqualTo("Product.Name"));
Assert.That(actual.FromArguments, Is.EqualTo("Products"));
Assert.That(actual.WhereArguments, Is.EqualTo("timestamp >= '2018-01-01' AND Product.Price.Netto < 300"));
Assert.That(actual.OrderByArguments, Is.EqualTo("timestamp ASC"));
Assert.That(actual.SkipArguments, Is.EqualTo("5"));
Assert.That(actual.LimitArguments, Is.EqualTo("10"));
Assert.That(actual.GroupByArguments, Is.Null);
Assert.That(actual.JoinArguments, Is.Null);
}
public async IAsyncEnumerable <BsonDocument> SearchAsync(DataApiSqlQuery parsedQuery, uint?maxResults = null)
{
var dataType = parsedQuery.FromArguments;
var tableSetup = tableSetups[dataType];
var hasProjection = parsedQuery.SelectArguments != null && parsedQuery.SelectArguments != "*";
var query = queryBuilder.Build(parsedQuery, tableSetup.TableName);
var searchResults = tableSetup.QueryExecutor.ExecuteReaderAsync(query);
await foreach (var reader in searchResults)
{
var container = await BuildContainer((SqlDataReader)reader, tableSetup);
var bsonDocument = container.ToBsonDocument();
if (!hasProjection)
{
yield return(bsonDocument);
}
else
{
yield return(MapContainerUsingSelect(container, parsedQuery));
}
}
}
public static PipelineDefinition <BsonDocument, BsonDocument> Build(DataApiSqlQuery parsedQuery, uint?hardLimitMaxResults = null)
{
if (parsedQuery.JoinArguments != null)
{
throw new NotSupportedException("Joining is not supported");
}
if (parsedQuery.FromArguments == null)
{
throw new FormatException("Missing FROM statement");
}
if (parsedQuery.FromArguments == string.Empty)
{
throw new FormatException("Missing arguments for FROM statement");
}
var stages = new List <JsonPipelineStageDefinition <BsonDocument, BsonDocument> >();
var filterDefinition = FilterDefinitionBuilder.Build <BsonDocument>(parsedQuery.WhereArguments);
if (filterDefinition != null)
{
stages.Add(filterDefinition);
}
var groupDefinition = GroupDefinitionBuilder.Build <BsonDocument>(parsedQuery.GroupByArguments);
if (groupDefinition != null)
{
stages.Add(groupDefinition);
}
var selectArguments = parsedQuery.SelectArguments ?? "*";
if (Regex.IsMatch(selectArguments, "^count\\([^()]*\\)$", RegexOptions.IgnoreCase))
{
var countDefinition = AggregateFunctionDefinitionBuilder.Count <BsonDocument>();
stages.Add(countDefinition);
}
else if (Regex.IsMatch(selectArguments, "^sum\\([^()]+\\)$", RegexOptions.IgnoreCase))
{
var match = Regex.Match(selectArguments, "^sum\\((?<PropertyPath>[^()]+)\\)$", RegexOptions.IgnoreCase);
var propertyPath = match.Groups["PropertyPath"].Value;
if (string.IsNullOrWhiteSpace(propertyPath))
{
throw new FormatException("Property path is invalid in SUM(<propertyPath>)-function");
}
var sumDefinition = AggregateFunctionDefinitionBuilder.Sum <BsonDocument>(propertyPath);
stages.Add(sumDefinition);
}
else
{
if (selectArguments.Contains("(") || selectArguments.Contains(")"))
{
throw new FormatException("SELECT-argument contains invalid aggregate function");
}
var sortDefinition = SortDefinitionBuilder.Build <BsonDocument>(parsedQuery.OrderByArguments);
if (sortDefinition != null)
{
stages.Add(sortDefinition);
}
var unwindDefinitions = UnwindDefinitionBuilder.Build <BsonDocument>(selectArguments);
stages.AddRange(unwindDefinitions);
var projectionDefinition = ProjectionDefinitionBuilder.Build <BsonDocument>(selectArguments);
if (projectionDefinition != null)
{
stages.Add(projectionDefinition);
}
var skipDefinition = SkipDefinitionBuilder.Build <BsonDocument>(parsedQuery.SkipArguments);
if (skipDefinition != null)
{
stages.Add(skipDefinition);
}
if (hardLimitMaxResults.HasValue)
{
var hardLimitDefinition = LimitDefinitionBuilder.Build <BsonDocument>(hardLimitMaxResults.Value);
stages.Add(hardLimitDefinition);
}
var limitDefinition = LimitDefinitionBuilder.Build <BsonDocument>(parsedQuery.LimitArguments);
if (limitDefinition != null)
{
stages.Add(limitDefinition);
}
// Re-run sort
if (sortDefinition != null)
{
stages.Add(sortDefinition);
}
}
return(PipelineDefinition <BsonDocument, BsonDocument> .Create(stages));
}
public IAsyncEnumerable <BsonDocument> SearchAsync(DataApiSqlQuery parsedQuery, uint?maxResults = null)
{
return(metadataStorage.SearchAsync(parsedQuery, maxResults));
}
