public void LikeClauseIsCorrectlyTranslated(string query)
{
var parsedQuery = DataApiSqlQueryParser.Parse(query);
var actual = AggregatePipelineBuilder.Build(parsedQuery);
Assert.That(actual.ToString(), Contains.Substring("$regex"));
}
public async Task <IActionResult> Search([FromBody] SearchBody body)
{
if (body == null)
{
return(BadRequest("No search body submitted"));
}
var query = body.Query;
DataApiSqlQuery parsedQuery;
try
{
parsedQuery = DataApiSqlQueryParser.Parse(query);
}
catch (FormatException formatException)
{
return(BadRequest(formatException.Message));
}
// Validate
var dataType = parsedQuery.FromArguments;
// Authorize
var loggedInUsername = UsernameNormalizer.Normalize(HttpContext.User.Identity.Name);
var resourceDescription = new SearchResourceDescription(dataType);
var authorizationResult = await authorizationModule.AuthorizeAsync(resourceDescription, loggedInUsername);
if (!authorizationResult.IsAuthorized)
{
return(StatusCode((int)HttpStatusCode.Unauthorized, "Not authorized"));
}
apiEventLogger.Log(LogLevel.Info, $"User '{authorizationResult.User.UserName}' submitted query '{query.RemoveLineBreaks()}'");
return(await SearchExecutor.PerformSearch(dataRouter, query, body.Format));
}
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 static string DetermineViewCollection(string query)
{
var placeholderReplacement = "#PLACEHOLDER#";
var placeholderRemovedQuery = Regex.Replace(query, "{[^}]+}", placeholderReplacement);
var parsedQuery = DataApiSqlQueryParser.Parse(placeholderRemovedQuery);
return(parsedQuery.FromArguments);
}
public void QueryAsExpected(string input, string expected)
{
var sut = new QueryBuilder(x => x.Replace("Data.", ""));
var parsedQuery = DataApiSqlQueryParser.Parse(input);
var actual = sut.Build(parsedQuery, parsedQuery.FromArguments);
var whitespaceNormalizedActual = Regex.Replace(actual, "\\s+", " ");
Assert.That(whitespaceNormalizedActual, Is.EqualTo(expected));
}
private static bool FromArgumentIsNullOrContainsPlaceholder(string query)
{
var placeholderReplacement = "#PLACEHOLDER#";
var placeholderRemovedQuery = Regex.Replace(query, "{[^}]+}", placeholderReplacement);
var parsedQuery = DataApiSqlQueryParser.Parse(placeholderRemovedQuery);
if (parsedQuery.FromArguments == null)
{
return(true);
}
return(parsedQuery.FromArguments.Contains(placeholderReplacement));
}
public static async Task <IActionResult> PerformSearch(IDataRouter dataRouter, string query, ResultFormat resultFormat)
{
try
{
var parsedQuery = DataApiSqlQueryParser.Parse(query);
var collection = parsedQuery.FromArguments.Trim();
var rdDataStorage = await dataRouter.GetSourceSystemAsync(collection);
var result = rdDataStorage.SearchAsync(parsedQuery);
switch (resultFormat)
{
case ResultFormat.Json:
return(BuildJsonResultAsync(result));
case ResultFormat.Csv:
return(BuildCsvResultAsync(result));
default:
throw new ArgumentOutOfRangeException(nameof(resultFormat), resultFormat, null);
}
}
catch (FormatException formatException)
{
return(new ContentResult
{
ContentType = "text/plain",
Content = formatException.Message,
StatusCode = (int)HttpStatusCode.BadRequest
});
}
catch (AggregateException aggregateException)
{
var innermostException = aggregateException.InnermostException();
if (innermostException is FormatException)
{
return(new ContentResult
{
ContentType = "text/plain",
Content = aggregateException.Message,
StatusCode = (int)HttpStatusCode.BadRequest
});
}
return(new ContentResult
{
Content = aggregateException.InnerException?.Message ?? aggregateException.Message,
ContentType = "text/plain",
StatusCode = (int)HttpStatusCode.InternalServerError
});
}
}
private async Task <bool> MatchesFilterAsync(string dataType, string dataObjectId, string filter)
{
if (string.IsNullOrWhiteSpace(dataType))
{
throw new ArgumentException("Value cannot be null or whitespace.", nameof(dataType));
}
if (string.IsNullOrEmpty(filter))
{
return(true);
}
var query = $"SELECT * FROM {dataType} WHERE _id = '{dataObjectId}' AND ({filter})";
var parsedQuery = DataApiSqlQueryParser.Parse(query);
var collection = await dataRouter.GetSourceSystemAsync(dataType);
return(await collection.SearchAsync(parsedQuery).AnyAsync());
}
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 void SqlLikeQueryIsUnderstood(string query)
{
Assert.That(() => DataApiSqlQueryParser.Parse(query), Throws.Nothing);
}
public void DuplicateFromKeywordThrowsException()
{
var query = "SELECT * FROM Component FROM Plate WHERE _id = '3'";
Assert.That(() => DataApiSqlQueryParser.Parse(query), Throws.TypeOf <FormatException>());
}
public async Task <IActionResult> DeleteMany([FromQuery] string dataType, [FromQuery] string whereArguments)
{
// Validate
if (string.IsNullOrWhiteSpace(dataType))
{
return(BadRequest("Data type not specified"));
}
IRdDataStorage rdDataStorage;
try
{
rdDataStorage = await dataRouter.GetSourceSystemAsync(dataType);
}
catch (KeyNotFoundException e)
{
return(BadRequest(e.Message));
}
var loggedInUsername = UsernameNormalizer.Normalize(HttpContext.User.Identity.Name);
var overwritingAllowed = await authorizationModule.IsOverwritingAllowedForCollectionAsync(dataType);
// Check existance
var query = $"SELECT _id, Submitter FROM {dataType} WHERE {whereArguments}";
var searchResult = rdDataStorage.SearchAsync(DataApiSqlQueryParser.Parse(query));
var deleteResults = new List <DeleteResult>();
await foreach (var doc in searchResult)
{
var submitter = doc["Submitter"].AsString;
var id = doc["_id"].AsString;
// Authorize
var resourceDescription = new DeleteDataResourceDescription(dataType, submitter, overwritingAllowed);
var authorizationResult = await authorizationModule.AuthorizeAsync(resourceDescription, loggedInUsername);
if (!authorizationResult.IsAuthorized)
{
deleteResults.Add(DeleteResult.Failed(dataType, id, "Not authorized"));
continue;
}
// Delete
if (await rdDataStorage.DeleteDataContainerAsync(dataType, id))
{
await subscriptionManager.NotifyDataChangedAsync(dataType, id, DataModificationType.Deleted);
deleteResults.Add(DeleteResult.Success(dataType, id));
}
else
{
deleteResults.Add(DeleteResult.Failed(dataType, id, "Not found"));
}
}
apiEventLogger.Log(LogLevel.Warning, $"User '{loggedInUsername}' has deleted data of type '{dataType}' with matching '{whereArguments}'");
return(new ContentResult
{
ContentType = Conventions.JsonContentType,
Content = JsonConvert.SerializeObject(deleteResults),
StatusCode = (int)HttpStatusCode.OK
});
}