|
public static Parse ( string s ) : |
||
| s | string | The string to parse. |
| return | ||
public static void ParseGroupMaxQuery()
{
const string sql = "SELECT COUNT(*) FROM a GROUP MAX a.b";
var exp = SqlExpression.Parse(sql);
Assert.NotNull(exp);
Assert.IsType <SqlQueryExpression>(exp);
var query = (SqlQueryExpression)exp;
Assert.NotEmpty(query.Items);
Assert.Equal(1, query.Items.Count);
Assert.Null(query.Where);
Assert.NotNull(query.GroupMax);
Assert.NotNull(query.From);
Assert.NotEmpty(query.From.Sources);
Assert.Equal(1, query.From.Sources.Count());
}
public void ParseSimpleQuery()
{
const string sql = "SELECT col1 AS a FROM table1";
SqlExpression expression = null;
Assert.DoesNotThrow(() => expression = SqlExpression.Parse(sql));
Assert.IsNotNull(expression);
Assert.IsInstanceOf <SqlQueryExpression>(expression);
var queryExpression = (SqlQueryExpression)expression;
Assert.IsNotEmpty(queryExpression.SelectColumns);
Assert.IsInstanceOf <SqlReferenceExpression>(queryExpression.SelectColumns.First().Expression);
Assert.AreEqual("a", queryExpression.SelectColumns.First().Alias);
Assert.IsNotNull(queryExpression.FromClause);
Assert.AreEqual(1, queryExpression.FromClause.AllTables.Count());
Assert.AreEqual("table1", queryExpression.FromClause.AllTables.First().Name);
}
public static void ParseSubquery()
{
const string sql = "SELECT * FROM (SELECT a FROM b) q WHERE q.a > 5";
var exp = SqlExpression.Parse(sql);
Assert.NotNull(exp);
Assert.IsType <SqlQueryExpression>(exp);
var query = (SqlQueryExpression)exp;
Assert.NotEmpty(query.Items);
Assert.Equal(1, query.Items.Count);
Assert.NotNull(query.Where);
Assert.NotNull(query.From);
Assert.NotEmpty(query.From.Sources);
Assert.Equal(1, query.From.Sources.Count());
Assert.IsType <SqlQueryExpressionSource>(query.From.Sources.First());
}
public void ParseSelectGroupBy()
{
const string sql = "SELECT col1 AS a, AVG(col2) b FROM table1 WHERE b > 2 GROUP BY a";
SqlExpression expression = null;
Assert.DoesNotThrow(() => expression = SqlExpression.Parse(sql));
Assert.IsNotNull(expression);
Assert.IsInstanceOf <SqlQueryExpression>(expression);
var queryExpression = (SqlQueryExpression)expression;
Assert.IsNotEmpty(queryExpression.SelectColumns);
var groupBy = queryExpression.GroupBy;
Assert.IsNotNull(groupBy);
Assert.IsNotEmpty(groupBy);
Assert.AreEqual(1, groupBy.Count());
}
public void ParseGroupByQuery()
{
const string sql = "SELECT COUNT(*) FROM a GROUP BY a.b";
var exp = SqlExpression.Parse(context, sql);
Assert.NotNull(exp);
Assert.IsType <SqlQueryExpression>(exp);
var query = (SqlQueryExpression)exp;
Assert.NotEmpty(query.Items);
Assert.Equal(1, query.Items.Count);
Assert.Null(query.Where);
Assert.NotNull(query.GroupBy);
Assert.NotEmpty(query.GroupBy);
Assert.Equal(1, query.GroupBy.Count);
Assert.NotNull(query.From);
Assert.NotEmpty(query.From.Sources);
Assert.Single(query.From.Sources);
}
public void ParseSelectWithNaturalJoin()
{
const string sql = "SELECT a.col1, b.col2 FROM table1 a, table2 b";
SqlExpression expression = null;
Assert.DoesNotThrow(() => expression = SqlExpression.Parse(sql));
Assert.IsNotNull(expression);
Assert.IsInstanceOf <SqlQueryExpression>(expression);
var queryExpression = (SqlQueryExpression)expression;
Assert.IsNotEmpty(queryExpression.SelectColumns);
Assert.IsInstanceOf <SqlReferenceExpression>(queryExpression.SelectColumns.First().Expression);
Assert.IsInstanceOf <SqlReferenceExpression>(queryExpression.SelectColumns.Skip(1).First().Expression);
Assert.IsNotNull(queryExpression.FromClause);
Assert.IsNotEmpty(queryExpression.FromClause.AllTables);
Assert.AreEqual(2, queryExpression.FromClause.AllTables.Count());
Assert.AreEqual(1, queryExpression.FromClause.JoinPartCount);
Assert.IsNotNull(queryExpression.FromClause.GetJoinPart(0));
Assert.AreEqual(JoinType.Inner, queryExpression.FromClause.GetJoinPart(0).JoinType);
}
public void BetweenDates()
{
const string sql = "TODATE('2001-02-12') BETWEEN TODATE('2000-01-20') AND TODATE('2003-01-01')";
SqlExpression expression = null;
Assert.DoesNotThrow(() => expression = SqlExpression.Parse(sql));
Assert.IsNotNull(expression);
Assert.IsInstanceOf <SqlBinaryExpression>(expression);
Assert.AreEqual(SqlExpressionType.And, expression.ExpressionType);
SqlExpression resultExpression = null;
Assert.DoesNotThrow(() => resultExpression = expression.Evaluate());
Assert.IsNotNull(resultExpression);
Assert.AreEqual(SqlExpressionType.Constant, resultExpression.ExpressionType);
var value = ((SqlConstantExpression)resultExpression).Value;
Assert.IsInstanceOf <BooleanType>(value.Type);
Assert.AreEqual(SqlBoolean.True, (SqlBoolean)value.Value);
}
public void BetweenNumerics()
{
const string sql = "22 BETWEEN 10 AND 54";
SqlExpression expression = null;
Assert.DoesNotThrow(() => expression = SqlExpression.Parse(sql));
Assert.IsNotNull(expression);
Assert.IsInstanceOf <SqlBinaryExpression>(expression);
Assert.AreEqual(SqlExpressionType.And, expression.ExpressionType);
SqlExpression resultExpression = null;
Assert.DoesNotThrow(() => resultExpression = expression.Evaluate());
Assert.IsNotNull(resultExpression);
Assert.AreEqual(SqlExpressionType.Constant, resultExpression.ExpressionType);
var value = ((SqlConstantExpression)resultExpression).Value;
Assert.IsInstanceOf <BooleanType>(value.Type);
Assert.AreEqual(SqlBoolean.True, (SqlBoolean)value.Value);
}
public void DateSmallerThanOtherDate()
{
const string text = "TODATE('2013-03-01') < TODATE('2013-05-02')";
SqlExpression expression = null;
Assert.DoesNotThrow(() => expression = SqlExpression.Parse(text));
Assert.IsNotNull(expression);
Assert.IsInstanceOf <SqlBinaryExpression>(expression);
SqlExpression evaluated = null;
Assert.DoesNotThrow(() => evaluated = expression.Evaluate());
Assert.IsNotNull(evaluated);
Assert.IsInstanceOf <SqlConstantExpression>(evaluated);
var value = ((SqlConstantExpression)evaluated).Value;
Assert.IsInstanceOf <SqlBoolean>(value.Value);
Assert.AreEqual(SqlBoolean.True, value.Value);
}
public void ParseFunctionWithAnonParameter(string s, string funcName, string param1Type,
object paramValue1, string param2Type, object paramValue2)
{
var exp = SqlExpression.Parse(context, s);
Assert.NotNull(exp);
Assert.Equal(SqlExpressionType.Function, exp.ExpressionType);
Assert.IsType <SqlFunctionExpression>(exp);
var function = (SqlFunctionExpression)exp;
Assert.Equal(ObjectName.Parse(funcName), function.FunctionName);
Assert.NotEmpty(function.Arguments);
Assert.Equal(2, function.Arguments.Length);
var param1 = function.Arguments[0];
var param2 = function.Arguments[1];
Assert.NotNull(param1);
Assert.NotNull(param2);
Assert.False(param1.IsNamed);
Assert.False(param2.IsNamed);
Assert.IsType <SqlConstantExpression>(param1.Value);
Assert.IsType <SqlConstantExpression>(param2.Value);
var pType1 = SqlType.Parse(context, param1Type);
var pType2 = SqlType.Parse(context, param2Type);
var value1 = new SqlObject(pType1, SqlValueUtil.FromObject(paramValue1));
var value2 = new SqlObject(pType2, SqlValueUtil.FromObject(paramValue2));
Assert.Equal(value1, ((SqlConstantExpression)param1.Value).Value);
Assert.Equal(value2, ((SqlConstantExpression)param2.Value).Value);
}
