public void should_create_select_query_with_complex_filter_and_sort()
{
// Setup
var filter1 = new BlBinaryExpression
{
Operator = BlOperator.Eq, IsLeaf = true, PropName = "doors", Value = 4
};
var filter2 = new BlBinaryExpression
{
Operator = BlOperator.Eq, IsLeaf = true, PropName = "doors", Value = 2
};
var filter = new BlBinaryExpression
{
IsLeaf = false,
Left = filter1,
Right = filter2,
Operator = BlOperator.And
};
var sqlBuilder = new SqlQueryBuilder();
// Act
var query = sqlBuilder.SelectPawnsFromTable("cars", 200, 200, filter, "model");
// Assert
Assert.Equal("SELECT * FROM cars WHERE ((doors = 4) AND (doors = 2)) ORDER BY model Asc LIMIT 200 OFFSET 200", query);
}
public void should_create_select_query_with_simple_filter_no_sort()
{
// Setup
var filter = new BlBinaryExpression
{
Operator = BlOperator.Eq, IsLeaf = true, PropName = "doors", Value = 4
};
var sqlBuilder = new SqlQueryBuilder();
// Act
var query = sqlBuilder.SelectPawnsFromTable("cars", 0, 200, filter);
// Assert
Assert.Equal("SELECT * FROM cars WHERE (doors = 4) ORDER BY Id LIMIT 200 OFFSET 0", query);
}
public void should_be_able_to_find_pawns_based_on_single_literal_filter()
{
// Setup
var basicPawnContainer = new BlGraphContainer
{
BlContainerName = "BasicPawn",
StorageContainerName = "BasicPawn",
Properties = new List <BlContainerProp>()
};
var containerList = new List <BlGraphContainer> {
basicPawnContainer
};
var graphMock = new Mock <IBlGraph>();
graphMock.Setup(graph => graph.GetStorageContainerNameForPawn(It.IsAny <BasicPawn>())).Returns("BasicPawn");
graphMock.Setup(graph => graph.CompiledCollections).Returns(containerList);
var cursor = new StorageCursor <BasicPawn>();
var storageProviderMock = new Mock <IBlStorageProvider>();
BlBinaryExpression filterExpression = null;
storageProviderMock
.Setup(provider =>
provider.FindInContainer <BasicPawn>(It.IsAny <string>(), It.IsAny <BlBinaryExpression>(), null,
"Asc", 200))
.Callback <string, BlBinaryExpression, string, string, int>((container, exp, sort, order, batchSize) =>
filterExpression = exp)
.Returns(cursor);
var bls = new Bls(storageProviderMock.Object, graphMock.Object);
bls.RegisterBlPawns(new BasicPawn());
// Act
var resultCursor = bls.Find <BasicPawn>(filter: (bp) => bp.Name == "some name");
// Assert
Assert.NotNull(resultCursor);
Assert.NotNull(filterExpression);
Assert.Equal("Name", filterExpression.PropName);
Assert.Equal(BlOperator.Eq, filterExpression.Operator);
Assert.Equal("some name", filterExpression.Value);
Assert.Null(filterExpression.Left);
Assert.Null(filterExpression.Right);
Assert.True(filterExpression.IsLeaf);
}
internal string SelectPawnsFromTable(
string tableName,
int offset,
int howMany,
BlBinaryExpression filter = null,
string sortColumn = null,
Sort sort = Sort.Asc)
{
string selectClause = $"SELECT * FROM {tableName} ";
string whereClause = string.Empty;
if (filter != null)
{
BuildWhereClauseFromFilter(filter, ref whereClause);
whereClause = "WHERE " + whereClause;
}
string sortClause = sortColumn == null ? " ORDER BY Id" : $" ORDER BY {sortColumn} {sort.ToString()}";
string limitClause = $" LIMIT {howMany} OFFSET {offset}";
return(selectClause + whereClause + sortClause + limitClause);
}
private void BuildWhereClauseFromFilter(BlBinaryExpression filter, ref string str)
{
if (filter == null)
{
return;
}
string left = string.Empty;
string right = string.Empty;
string opr = TranslateOperator(filter.Operator);
if (filter.IsLeaf)
{
left = filter.PropName;
right = filter.Value.ToString();
}
else
{
BuildWhereClauseFromFilter(filter.Left, ref left);
BuildWhereClauseFromFilter(filter.Right, ref right);
}
str = $"({left} {opr} {right})";
}
public StorageCursor <T> GetByRelation <T>(string fromId, string relationName, string containerName = null,
BlBinaryExpression filter = null, string sortProperty = null, Sort sortDir = Sort.Asc, int batchSize = 200) where T : BlsPawn
{
throw new NotImplementedException();
}
public int GetContainerCount(string containerName, BlBinaryExpression filter = null)
{
throw new NotImplementedException();
}
public StorageCursor <T> SearchInContainer <T>(string containerName, List <string> propertiesToSearch, string term,
BlBinaryExpression filter = null, string sortProperty = null, string sortOrder = null, int batchSize = 200) where T : BlsPawn
{
throw new NotImplementedException();
}