public void RangeMath()
{
const string input = "[location] in ([Moscow], [St. Petersburg]) and [country] in ([ru], [us])";
const string expectedSql = "where location in ( @3 , @2 ) and country in ( @1 , @0 )";
var expectedParameters = new object[]
{
"us", "ru", "St. Petersburg", "Moscow",
};
var tokens = TokenizationService.Tokenizate(input);
var tree = AstService.Convert(tokens);
var(actualSql, actualParameters) = SqlProvider.Convert(tree,
SqlProvider.DefineProperty()
.From("location")
.ToSql("location"),
SqlProvider.DefineProperty()
.From("country")
.ToSql("country"));
Assert.AreEqual(expectedSql, actualSql.ToString());
Assert.AreEqual(expectedParameters.Length, actualParameters.Length);
for (var i = 0; i < actualParameters.Length; i++)
{
var expectedParameter = expectedParameters[i];
var actualParameter = actualParameters[i];
Assert.AreEqual(expectedParameter, actualParameter);
}
}
public void Escape()
{
const string allowedProperty = "name";
var equations = (from internalOperator in InternalGrammarService.Operators
from internalOperatorDenotation in internalOperator.Denotations
join sqlOperator in SqlGrammarService.Operators
on internalOperator.OperatorType equals sqlOperator.OperatorType
where internalOperator.Arity == Arity.Binary && sqlOperator.Arity == Arity.Binary
select new
{
Input = $"[{allowedProperty}] {internalOperatorDenotation} [{allowedProperty}]",
Sql = $"where {allowedProperty} {sqlOperator.MainDenotation} @0",
})
.ToArray();
foreach (var equation in equations)
{
var inputEquation = equation.Input;
var expectedSql = equation.Sql;
var tokens = TokenizationService.Tokenizate(inputEquation);
var tree = AstService.Convert(tokens);
var(actualSql, actualParameters) = SqlProvider.Convert(tree,
SqlProvider.DefineProperty()
.From(allowedProperty)
.ToSql(allowedProperty));
Assert.AreEqual(expectedSql, actualSql.ToString());
Assert.AreEqual(1, actualParameters.Length);
Assert.AreEqual(allowedProperty, actualParameters[0]);
}
}
public void StringLike()
{
const string input = "[name] contains [and] or ([name] startsWith [Alex] and [name]endsWith[ndr])";
const string expectedSql = "where name like @2 or name like @1 and name like @0";
var expectedParameters = new object[]
{
"%ndr", "Alex%", "%and%",
};
var tokens = TokenizationService.Tokenizate(input);
var tree = AstService.Convert(tokens);
var(actualSql, actualParameters) = SqlProvider.Convert(tree,
SqlProvider.DefineProperty()
.From("name")
.ToSql("name"));
Assert.AreEqual(expectedSql, actualSql.ToString());
Assert.AreEqual(expectedParameters.Length, actualParameters.Length);
for (var i = 0; i < actualParameters.Length; i++)
{
var expectedParameter = expectedParameters[i];
var actualParameter = actualParameters[i];
Assert.AreEqual(expectedParameter, actualParameter);
}
}
public void SimpleMath()
{
const string input = "([age] >= 16 and [country] == [ru]) or ([age] >= 13 and [country] === [ja])";
const string expectedSql = "where age >= @3 and country = @2 or age >= @1 and country = @0";
var expectedParameters = new object[]
{
"ja", 13, "ru", 16,
};
var tokens = TokenizationService.Tokenizate(input);
var tree = AstService.Convert(tokens);
var(actualSql, actualParameters) = SqlProvider.Convert(tree,
SqlProvider.DefineProperty()
.From("age")
.ToSql("age"),
SqlProvider.DefineProperty()
.From("country")
.ToSql("country"));
Assert.AreEqual(expectedSql, actualSql.ToString());
Assert.AreEqual(expectedParameters.Length, actualParameters.Length);
for (var i = 0; i < actualParameters.Length; i++)
{
var expectedParameter = expectedParameters[i];
var actualParameter = actualParameters[i];
Assert.AreEqual(expectedParameter, actualParameter);
}
}
public void PageCount()
{
const string equation = "left + right";
const string expectedSql = "where left + right limit 2 offset 2";
var tokens = TokenizationService.Tokenizate(equation);
var tree = AstService.Convert(tokens);
var(where, parameters) = SqlProvider.Convert(tree,
SqlProvider.DefineProperty().From("left").ToSql("left"),
SqlProvider.DefineProperty().From("right").ToSql("right"));
where.AddPage(1).AddCount(2);
Assert.AreEqual(expectedSql, where.ToString());
Assert.AreEqual(0, parameters.Length);
}
public void Mess()
{
const string input =
"[id] === [1690ffef-7249-4384-8cba-58842e8d48df] and (([length] + 1) * 2 <= 14 or [email] = [email])";
const string expectedSql = "where id = @4 and ( ( table.length + @3 ) * @2 <= @1 or table2.email = @0 )";
var expectedParameters = new object[]
{
"email", 14, 2, 1, Guid.Parse("1690ffef-7249-4384-8cba-58842e8d48df"),
};
var tokens = TokenizationService.Tokenizate(input);
var tree = AstService.Convert(tokens);
var(actualSql, actualParameters) = SqlProvider.Convert(tree,
SqlProvider.DefineProperty().From("id").ToSql("id"),
SqlProvider.DefineProperty()
.From("length")
.ToSql("table.length"),
SqlProvider.DefineProperty()
.From("email")
.ToSql("table2.email"));
Assert.AreEqual(expectedSql, actualSql.ToString());
Assert.AreEqual(expectedParameters.Length, actualParameters.Length);
for (var i = 0; i < actualParameters.Length; i++)
{
var expectedParameter = expectedParameters[i];
var actualParameter = actualParameters[i];
Assert.AreEqual(expectedParameter, actualParameter);
}
}
public void PropertyRemapping()
{
const string input = "[storesCount] > 3 or [salaryCostsPerMonth] > 10000 * [dollarCourse]";
const string expectedSql = "where storesCount > @1 or salarySlice.perMonth > @0 * bank.currentDollarCourse";
var expectedParameters = new object[]
{
10000, 3,
};
var tokens = TokenizationService.Tokenizate(input);
var tree = AstService.Convert(tokens);
var(actualSql, actualParameters) = SqlProvider.Convert(tree,
SqlProvider.DefineProperty()
.From("storesCount")
.ToSql("storesCount"),
SqlProvider.DefineProperty()
.From("salaryCostsPerMonth")
.ToSql("salarySlice.perMonth"),
SqlProvider.DefineProperty()
.From("dollarCourse")
.ToSql("bank.currentDollarCourse"));
Assert.AreEqual(expectedSql, actualSql.ToString());
Assert.AreEqual(expectedParameters.Length, actualParameters.Length);
for (var i = 0; i < actualParameters.Length; i++)
{
var expectedParameter = expectedParameters[i];
var actualParameter = actualParameters[i];
Assert.AreEqual(expectedParameter, actualParameter);
}
}