public QueryExpression MakeQueryExpression(GlazeConnection cnn,
string sourceCommandText)
{
// not allowing multiple SELECT clauses (does anybody need them?),
// but shouldn't be that difficult to generalize...
MacroScopeParser parser = Factory.CreateParser(sourceCommandText);
return(parser.queryExpression());
}
public void TestSelectOrderBy()
{
CheckSelect("select * from area order by area_id ASC",
"SELECT *\r\nFROM area\r\nORDER BY area_id");
StringBuilder expected = new StringBuilder();
expected.Append("SELECT area.area_id, province.province_id, ");
expected.Append("district.district_id, city.city_id\r\n");
expected.Append("FROM area ");
expected.Append("INNER JOIN province ON area.area_id = province.area_id ");
expected.Append("INNER JOIN district ON province.province_id = district.province_id ");
expected.Append("INNER JOIN city ON district.city_id = city.city_id\r\n");
expected.Append("ORDER BY area.area_id, province.province_id, ");
expected.Append("district.district_id, city.city_id");
CheckSelect(@"select area.area_id, province.province_id, district.district_id, city.city_id
from area
join province on area.area_id=province.area_id
join district on province.province_id=district.province_id
join city on district.city_id=city.city_id
order by area.area_id,province.province_id,district.district_id,city.city_id",
expected.ToString());
SelectStatement statement = Factory.CreateStatement(
"select area.* from area") as SelectStatement;
Assert.IsNotNull(statement);
QueryExpression queryExpression = statement.SingleQueryExpression;
Assert.IsNotNull(queryExpression);
Assert.IsNull(queryExpression.OrderBy);
MacroScopeParser parser = Factory.CreateParser("area.area_id");
IExpression expression = parser.expression();
Assert.IsNotNull(expression);
OrderExpression orderExpression = new OrderExpression(expression);
queryExpression.OrderBy = orderExpression;
string sql = TestUtil.Stringify(statement);
Assert.AreEqual("SELECT area.*\r\nFROM area\r\nORDER BY area.area_id",
sql);
CheckSelect("SELECT * FROM fact ORDER BY subject, predicate desc, [object]",
"SELECT *\r\nFROM fact\r\nORDER BY subject, predicate DESC, [object]");
CheckSelect(@"SELECT *
FROM fact
ORDER BY
subject ASC, predicate DESC, [object] ASC",
"SELECT *\r\nFROM fact\r\nORDER BY subject, predicate DESC, [object]");
}
public void TestSearchCondition()
{
MacroScopeParser parser = Factory.CreateParser("a != b");
Expression expression = parser.searchCondition() as Expression;
Assert.IsNotNull(expression);
Assert.AreEqual("a <> b", TestUtil.Stringify(expression));
parser = Factory.CreateParser("a + b > c");
expression = parser.searchCondition() as Expression;
Assert.IsNotNull(expression);
Assert.AreEqual("(a + b) > c", TestUtil.Stringify(expression));
parser = Factory.CreateParser("m != all (select n from x)");
PredicateExpression predicateExpression = parser.searchCondition()
as PredicateExpression;
Assert.IsNotNull(predicateExpression);
Assert.AreEqual("m <> ALL ( SELECT n\r\nFROM x )",
TestUtil.Stringify(predicateExpression));
parser = Factory.CreateParser("c is not null");
expression = parser.searchCondition() as Expression;
Assert.IsNotNull(expression);
Assert.AreEqual("c IS NOT NULL", TestUtil.Stringify(expression));
parser = Factory.CreateParser("1<>1 and 1<>1 or 1=1");
expression = parser.searchCondition() as Expression;
Assert.IsNotNull(expression);
Assert.AreEqual("((1 <> 1) AND (1 <> 1)) OR (1 = 1)",
TestUtil.Stringify(expression));
parser = Factory.CreateParser("1 = 1 or 1 <> 1 and 1 <> 1");
expression = parser.searchCondition() as Expression;
Assert.IsNotNull(expression);
Assert.AreEqual("(1 = 1) OR ((1 <> 1) AND (1 <> 1))",
TestUtil.Stringify(expression));
CheckSearchCondition("(1 = 1)", "1 = 1");
CheckSearchCondition("NULL IS NULL");
StringBuilder expected = new StringBuilder();
expected.Append("((address.building_id = ");
expected.Append("building.building_id) ");
expected.Append("AND (building.settlement_id = settlement.settlement_id)) ");
expected.Append("AND (settlement.village_id = village.village_id)");
CheckSearchCondition(expected.ToString());
CheckSearchCondition("updated >= 1");
CheckSearchCondition("updated >= @threshold");
CheckSearchCondition("updated >= :threshold");
CheckSearchCondition("updated >= DATE()");
}
public void TestSelectItem()
{
MacroScopeParser parser = Factory.CreateParser("*");
AliasedItem selectItem = parser.selectItem();
Assert.IsInstanceOfType(typeof(Wildcard), selectItem.Item);
parser = Factory.CreateParser("Author = Originator");
selectItem = parser.selectItem();
Assert.IsNotNull(selectItem);
Identifier alias = selectItem.Alias;
Assert.IsNotNull(alias);
Assert.AreEqual("Author", alias.ID);
Assert.AreEqual("Originator", TestUtil.Stringify(selectItem.Item));
parser = Factory.CreateParser("x = 1 + 2");
selectItem = parser.selectItem();
INode core = selectItem.Item;
Assert.IsInstanceOfType(typeof(Expression), core);
Assert.AreEqual("1 + 2", TestUtil.Stringify(core));
parser = Factory.CreateParser("x = y + z");
selectItem = parser.selectItem();
Assert.IsNotNull(selectItem);
alias = selectItem.Alias;
Assert.IsNotNull(alias);
Assert.AreEqual("x", alias.ID);
core = selectItem.Item;
Assert.IsInstanceOfType(typeof(Expression), core);
Assert.AreEqual("y + z", TestUtil.Stringify(core));
parser = Factory.CreateParser("address.*");
selectItem = parser.selectItem();
core = selectItem.Item;
Assert.IsInstanceOfType(typeof(TableWildcard), core);
Assert.AreEqual("address.*", TestUtil.Stringify(core));
parser = Factory.CreateParser("1 + 2 * 3");
selectItem = parser.selectItem();
Assert.IsNotNull(selectItem);
Assert.IsNull(selectItem.Alias);
core = selectItem.Item;
Assert.IsInstanceOfType(typeof(Expression), core);
Assert.AreEqual("1 + (2 * 3)", TestUtil.Stringify(core));
}
void CheckSearchCondition(string from, string to)
{
if (from == null)
{
throw new ArgumentNullException("from");
}
if (to == null)
{
throw new ArgumentNullException("to");
}
MacroScopeParser parser = Factory.CreateParser(from);
IExpression expression = parser.searchCondition();
Assert.IsNotNull(expression);
Assert.AreEqual(to, TestUtil.Stringify(expression));
}
string TailorName(string name)
{
if (name == null)
{
throw new ArgumentNullException("name");
}
MacroScopeParser parser = Factory.CreateParser(name);
Variable variable = parser.variableReference();
IVisitor tailor = Factory.CreateTailor(m_owner.DatabaseProvider);
variable.Traverse(tailor);
Stringifier stringifier = new Stringifier();
variable.Traverse(stringifier);
return(stringifier.ToSql());
}
void CheckFunction(string from, string to)
{
if (from == null)
{
throw new ArgumentNullException("from");
}
if (to == null)
{
throw new ArgumentNullException("to");
}
MacroScopeParser parser = Factory.CreateParser(from);
IExpression functionCall = parser.function();
Assert.IsNotNull(functionCall);
Assert.AreEqual(to, TestUtil.Stringify(functionCall));
}
void CheckJoinedTable(string from, string to)
{
if (from == null)
{
throw new ArgumentNullException("from");
}
if (to == null)
{
throw new ArgumentNullException("to");
}
MacroScopeParser parser = Factory.CreateParser(from);
Table table = parser.joinedTable();
Assert.IsNotNull(table);
Assert.AreEqual(to, TestUtil.Stringify(table));
}
/// <summary>
/// Parses the argument as a SQL statement and returns its tree.
/// </summary>
/// <param name="commandText">
/// The SQL. Must not be null.
/// </param>
/// <returns>
/// A statement instance (never null).
/// </returns>
public static IStatement CreateStatement(string commandText)
{
MacroScopeParser parser = CreateParser(commandText);
return(parser.statement());
}
public void TestPredicate()
{
MacroScopeParser parser = Factory.CreateParser("a >= b");
Expression expression = parser.predicate() as Expression;
Assert.IsNotNull(expression);
Assert.AreEqual("a", TestUtil.Stringify(expression.Left));
Assert.AreSame(ExpressionOperator.GreaterOrEqual,
expression.Operator);
Assert.AreEqual("b", TestUtil.Stringify(expression.Right));
parser = Factory.CreateParser("c is null");
expression = parser.predicate() as Expression;
Assert.IsNotNull(expression);
Assert.AreEqual("c", TestUtil.Stringify(expression.Left));
Assert.AreSame(ExpressionOperator.IsNull,
expression.Operator);
Assert.IsNull(expression.Right);
CheckPredicate("c IS NOT NULL");
parser = Factory.CreateParser("d like 'Smith%'");
expression = parser.predicate() as Expression;
Assert.IsNotNull(expression);
Assert.AreEqual("d", TestUtil.Stringify(expression.Left));
Assert.AreSame(ExpressionOperator.Like,
expression.Operator);
PatternExpression patternExpression = expression.Right as PatternExpression;
Assert.IsNotNull(patternExpression);
Assert.AreEqual("'Smith%'", TestUtil.Stringify(patternExpression.Expression));
Assert.IsNull(patternExpression.Escape);
parser = Factory.CreateParser("comment LIKE '%30!%%' ESCAPE '!'");
expression = parser.predicate() as Expression;
Assert.IsNotNull(expression);
patternExpression = expression.Right as PatternExpression;
Assert.IsNotNull(patternExpression);
Assert.AreEqual("'%30!%%'", TestUtil.Stringify(patternExpression.Expression));
Assert.AreEqual("'!'", TestUtil.Stringify(patternExpression.Escape));
parser = Factory.CreateParser("e between 1 and 10");
expression = parser.predicate() as Expression;
Assert.IsNotNull(expression);
Assert.AreSame(ExpressionOperator.Between, expression.Operator);
Assert.AreEqual("e BETWEEN 1 AND 10", TestUtil.Stringify(expression));
parser = Factory.CreateParser("f in(+2, +3, +5, +7)");
expression = parser.predicate() as Expression;
Assert.IsNotNull(expression);
Assert.AreEqual("f IN (2, 3, 5, 7)", TestUtil.Stringify(expression));
string idempotent = "g1 NOT IN (SELECT name\r\nFROM week)";
parser = Factory.CreateParser(idempotent);
expression = parser.predicate() as Expression;
Assert.IsNotNull(expression);
Assert.AreEqual(idempotent, TestUtil.Stringify(expression));
CheckPredicate("EXISTS (SELECT *\r\nFROM warehouse)");
}
