public void Can_Read_Between_Expression_With_Identifiers()
{
var tokenizer = NewTokenizer( "A.Field BETWEEN 10 AND 20" );
tokenizer.ReadNextToken();
ExpressionParser parser = new ExpressionParser( tokenizer );
// exercise
Expression expression = parser.Execute();
// verify
Assert.IsNotNull( expression );
Assert.IsTrue( expression is BetweenExpression );
BetweenExpression betweenExpression = (BetweenExpression) expression;
Expression expr = betweenExpression.Expression;
Assert.IsTrue( expr is IdentifierExpression );
Assert.AreEqual( "A.Field", expr.Value );
Expression from = betweenExpression.From;
Assert.IsTrue( from is IdentifierExpression );
Assert.AreEqual( "10", from.Value );
Expression to = betweenExpression.To;
Assert.IsTrue( to is IdentifierExpression );
Assert.AreEqual( "20", to.Value );
}
protected string GetIdentifier()
{
if (!Tokenizer.HasMoreTokens)
{
throw new SyntaxException("Identifier expected");
}
string identifier = String.Empty;
switch (Tokenizer.Current.Type)
{
case TokenType.SingleQuote:
var parser = new ExpressionParser(Tokenizer);
var expression = parser.Execute();
if (!(expression is StringExpression))
{
throw new SyntaxException("Transaction name must be a string");
}
identifier = expression.Value;
break;
default:
identifier = CurrentToken;
ReadNextToken();
break;
}
return(identifier);
}
protected string GetIdentifier()
{
if ( !Tokenizer.HasMoreTokens )
throw new SyntaxException( "Identifier expected" );
string identifier = String.Empty;
switch (Tokenizer.Current.Type)
{
case TokenType.SingleQuote:
var parser = new ExpressionParser(Tokenizer);
var expression = parser.Execute();
if (!(expression is StringExpression))
throw new SyntaxException("Transaction name must be a string");
identifier = expression.Value;
break;
default:
identifier = CurrentToken;
ReadNextToken();
break;
}
return identifier;
}
public List <VariableDefinition> Execute()
{
do
{
string name = Tokenizer.Current.Value;
Tokenizer.ReadNextToken();
if (!Tokenizer.HasMoreTokens)
{
throw new SyntaxException(String.Format("type missing for declaration of variable '{0}'", name));
}
string type = GetSqlType();
var definition = new VariableDefinition(name, type);
if (Tokenizer.TokenEquals(Constants.Assignment))
{
var parser = new ExpressionParser(Tokenizer);
definition.DefaultValue = parser.Execute();
}
_statement.Add(definition);
}while (Tokenizer.TokenEquals(Constants.Comma));
return(_statement);
}
public void CanCastVarchar()
{
// setup
var tokenizer = NewTokenizer("CAST (@Age AS VARCHAR(50) )");
tokenizer.ReadNextToken();
ExpressionParser parser = new ExpressionParser(tokenizer);
// exercise
Expression expression = parser.Execute();
// verify
var function = expression as CastExpression;
Assert.IsNotNull(function);
Assert.AreEqual(1, function.Arguments.Count);
Assert.AreEqual("CAST", function.Name);
Assert.AreEqual("@Age", function.Arguments.First().Value);
Assert.AreEqual("VARCHAR(50)", function.OutputType.ToString());
Assert.AreEqual("CAST(@Age AS VARCHAR(50))", function.Value);
}
protected Top GetTop()
{
Tokenizer.ExpectToken(Constants.Top);
Top top;
if (Tokenizer.IsNextToken(Constants.OpenBracket))
{
using (Tokenizer.ExpectBrackets())
{
var parser = new ExpressionParser(Tokenizer);
var expression = parser.Execute();
if (expression == null)
{
throw new SyntaxException("TOP clause requires an expression");
}
var brackets = new NestedExpression(null);
brackets.Expression = expression;
top = new Top(brackets);
}
}
else
{
if (Tokenizer.Current.Type != TokenType.Numeric || Tokenizer.Current.Value.Contains("."))
{
throw new SyntaxException(String.Format("Expected integer but found: '{0}'", Tokenizer.Current.Value));
}
top = new Top(new StringExpression(Tokenizer.Current.Value, null));
ReadNextToken();
}
if (Tokenizer.TokenEquals(Constants.Percent))
{
top.Percent = true;
}
return(top);
}
public void Can_Read_Case_Expression_With_Nested_Case_Expression()
{
// setup
var tokenizer = NewTokenizer( @"
CASE A.Field1
WHEN 1 THEN
CASE A.Field2
WHEN 1 THEN 'Y'
ELSE
'U'
END
WHEN 2 THEN 'N'
ELSE
'U'
END" );
tokenizer.ReadNextToken();
ExpressionParser parser = new ExpressionParser( tokenizer );
// exercise
Expression expression = parser.Execute();
// verify
Assert.IsNotNull( expression );
Assert.IsTrue( expression is CaseSwitchExpression );
CaseSwitchExpression caseSwitch = (CaseSwitchExpression) expression;
Assert.AreEqual( "A.Field1", caseSwitch.Switch.Value );
Assert.AreEqual( 2, caseSwitch.Cases.Count );
Assert.AreEqual( "1", caseSwitch.Cases[ 0 ].When.Value );
Assert.IsTrue( caseSwitch.Cases[ 0 ].Then is CaseExpression );
Assert.AreEqual( "2", caseSwitch.Cases[ 1 ].When.Value );
Assert.AreEqual( "'N'", caseSwitch.Cases[ 1 ].Then.Value );
Assert.AreEqual( "'U'", caseSwitch.Else.Value );
}
public void ExpressionWithLowerCast()
{
// setup
var tokenizer = NewTokenizer("CASE WHEN CostCenter > 0 THEN cast(CostCenter as varchar(10)) ELSE CostCenterDesc END ");
tokenizer.ReadNextToken();
ExpressionParser parser = new ExpressionParser(tokenizer);
// exercise
Expression expression = parser.Execute();
// verify
var function = expression as CaseWhenExpression;
Assert.IsNotNull(function, "not a case statement");
var cast = function.Cases[0].Then as CastExpression;
Assert.IsNotNull(cast, "first case is not a cast");
Assert.AreEqual("CAST", cast.Name);
Assert.AreEqual("CostCenter", cast.Arguments.First().Value);
Assert.AreEqual("varchar(10)", cast.OutputType.ToString());
Assert.AreEqual("CAST(CostCenter AS varchar(10))", cast.Value);
}
protected Expression ProcessExpression()
{
var parser = new ExpressionParser( Tokenizer );
return parser.Execute();
}
protected Top GetTop()
{
// consume 'TOP' token first
Tokenizer.ExpectToken( Constants.Top );
Top top;
if ( Tokenizer.IsNextToken( Constants.OpenBracket ) )
{
using ( Tokenizer.ExpectBrackets() )
{
var parser = new ExpressionParser( Tokenizer );
var expression = parser.Execute();
if ( expression != null )
{
top = new Top( expression, true );
return top;
}
else
throw new SyntaxException( "TOP clause requires an expression" );
}
}
else
{
if ( Tokenizer.Current.Type != TokenType.Numeric || Tokenizer.Current.Value.Contains( "." ) )
throw new SyntaxException( String.Format( "Expected integer but found: '{0}'", Tokenizer.Current.Value ) );
top = new Top( new StringExpression( Tokenizer.Current.Value, null ), false );
ReadNextToken();
}
if ( Tokenizer.TokenEquals( Constants.Percent ) )
top.Percent = true;
return top;
}
public void Simple_Criteria_Expression( string op )
{
// setup
var tokenizer = NewTokenizer( String.Format( "A.Field1 {0} B.Field2", op ) );
tokenizer.ReadNextToken();
ExpressionParser parser = new ExpressionParser( tokenizer );
// exercise
Expression expression = parser.Execute();
// verify
Assert.IsNotNull( expression );
Assert.IsTrue( expression is CriteriaExpression );
CriteriaExpression criteria = (CriteriaExpression) expression;
Assert.AreEqual( "A.Field1", criteria.Left.Value );
Assert.AreEqual( op, criteria.Operator );
Assert.AreEqual( "B.Field2", criteria.Right.Value );
}
public void Nested_Criteria_Expression()
{
// setup
var tokenizer = NewTokenizer( "A.Field1 = (2 + B.Field2)" );
tokenizer.ReadNextToken();
ExpressionParser parser = new ExpressionParser( tokenizer );
// exercise
Expression expression = parser.Execute();
// verify
Assert.IsNotNull( expression );
Assert.IsTrue( expression is CriteriaExpression );
CriteriaExpression criteria = (CriteriaExpression) expression;
Assert.AreEqual( "A.Field1", criteria.Left.Value );
Assert.AreEqual( "=", criteria.Operator );
Assert.IsTrue( criteria.Right is NestedExpression );
NestedExpression nestedCriteria = (NestedExpression) criteria.Right;
Assert.IsTrue( nestedCriteria.Expression is OperatorExpression );
OperatorExpression operatorExpression = (OperatorExpression) nestedCriteria.Expression;
Assert.AreEqual( "2", operatorExpression.Left.Value );
Assert.AreEqual( "B.Field2", operatorExpression.Right.Value );
}
public void Expression_Reads_Encoded_Strings(string input, string output)
{
// setup
var tokenizer = NewTokenizer(input);
tokenizer.ReadNextToken();
ExpressionParser parser = new ExpressionParser(tokenizer);
// exercise
Expression expression = parser.Execute();
// verify
Assert.IsNotNull(expression);
Assert.AreEqual(output, expression.Value);
Assert.IsTrue(expression is StringExpression);
}
public void Can_Read_Case_When_Expression()
{
// setup
var tokenizer = NewTokenizer( @"
CASE
WHEN A.Field1 < 1 THEN 'Y'
WHEN A.Field2 >= 2 + A.Field3 THEN 15 / (A.Field4 * 2)
ELSE
'U'
END
"
);
tokenizer.ReadNextToken();
ExpressionParser parser = new ExpressionParser( tokenizer );
// exercise
Expression expression = parser.Execute();
// verify
Assert.IsNotNull( expression );
Assert.IsTrue( expression is CaseWhenExpression );
CaseWhenExpression caseWhen = (CaseWhenExpression) expression;
Assert.AreEqual( 2, caseWhen.Cases.Count );
Assert.AreEqual( "A.Field1 < 1", caseWhen.Cases[ 0 ].When.Value );
Assert.AreEqual( "'Y'", caseWhen.Cases[ 0 ].Then.Value );
Assert.AreEqual( "A.Field2 >= 2 + A.Field3", caseWhen.Cases[ 1 ].When.Value );
Assert.AreEqual( "15 / (A.Field4 * 2)", caseWhen.Cases[ 1 ].Then.Value );
Assert.AreEqual( "'U'", caseWhen.Else.Value );
}
public void Can_Read_Natural_Negated_In_Expression()
{
var tokenizer = NewTokenizer( "A.Field NOT IN (1, 2, 3)" );
tokenizer.ReadNextToken();
ExpressionParser parser = new ExpressionParser( tokenizer );
// exercise
Expression expression = parser.Execute();
Assert.IsTrue( expression is CriteriaExpression );
CriteriaExpression criteria = (CriteriaExpression) expression;
Assert.AreEqual( "A.Field", criteria.Left.Value );
Assert.AreEqual( "NOT IN", criteria.Operator );
Assert.AreEqual( "(1, 2, 3)", criteria.Right.Value );
}
public void Expression_Reads_Quoted_String_Around_Two_Part_Identifier()
{
// setup
var tokenizer = NewTokenizer( " 'Some Owner' " );
tokenizer.ReadNextToken();
ExpressionParser parser = new ExpressionParser( tokenizer );
// exercise
Expression expression = parser.Execute();
// verify
Assert.IsNotNull( expression );
Assert.AreEqual( "'Some Owner'", expression.Value );
Assert.IsTrue( expression is StringExpression );
}
public void Expression_With_Add_Operator( string op )
{
// setup
var tokenizer = NewTokenizer( String.Format( "A.Field1 {0} 120", op ) );
tokenizer.ReadNextToken();
ExpressionParser parser = new ExpressionParser( tokenizer );
// exercise
Expression expression = parser.Execute();
// verify
Assert.IsNotNull( expression );
Assert.IsTrue( expression is OperatorExpression );
var operatorExpression = (OperatorExpression) expression;
Assert.AreEqual( op, operatorExpression.Operator );
Assert.IsTrue( operatorExpression.Left is IdentifierExpression );
Assert.IsTrue( operatorExpression.Right is IdentifierExpression );
Assert.AreEqual( "A.Field1", operatorExpression.Left.Value );
Assert.AreEqual( "120", operatorExpression.Right.Value );
}
public void Expression_Reads_Multi_Part_Identifier_With_Square_Brackets_Around_Two_Part_Identifier()
{
// setup
var tokenizer = NewTokenizer( " Database.[Some Owner].Table " );
tokenizer.ReadNextToken();
ExpressionParser parser = new ExpressionParser( tokenizer );
// exercise
Expression expression = parser.Execute();
// verify
Assert.IsNotNull( expression );
Assert.AreEqual( "Database.[Some Owner].Table", expression.Value );
Assert.IsTrue( expression is IdentifierExpression );
}
public void Expression_Reads_Function_Expression_With_Multiple_Params()
{
// setup
var tokenizer = NewTokenizer( "Max(120, A)" );
tokenizer.ReadNextToken();
ExpressionParser parser = new ExpressionParser( tokenizer );
// exercise
Expression expression = parser.Execute();
// verify
Assert.IsNotNull( expression );
Assert.IsTrue( expression is FunctionExpression );
var function = (FunctionExpression) expression;
Assert.AreEqual( 2, function.Arguments.Count );
Assert.AreEqual( "Max", function.Name );
Assert.AreEqual( "120", function.Arguments[ 0 ].Value );
Assert.AreEqual( "A", function.Arguments[ 1 ].Value );
}
public void Expression_Reads_Function_Expression_Without_Params()
{
// setup
var tokenizer = NewTokenizer( "SomeFunction()" );
tokenizer.ReadNextToken();
ExpressionParser parser = new ExpressionParser( tokenizer );
// exercise
Expression expression = parser.Execute();
// verify
Assert.IsNotNull( expression );
var function = (FunctionExpression) expression;
Assert.AreEqual( 0, function.Arguments.Count );
Assert.AreEqual( "SomeFunction", function.Name );
}
public void Can_Read_Negated_Expression_Without_Brackets()
{
var tokenizer = NewTokenizer( "NOT EXISTS(SELECT 1 FROM dbo.Table)" );
tokenizer.ReadNextToken();
ExpressionParser parser = new ExpressionParser( tokenizer );
// exercise
Expression expression = parser.Execute();
// verify
Assert.IsNotNull( expression );
Assert.IsTrue( expression is NegationExpression );
NegationExpression negationExpression = (NegationExpression) expression;
Expression criteria = negationExpression.Expression;
Assert.IsTrue( criteria is FunctionExpression );
FunctionExpression functionExpression = (FunctionExpression) criteria;
Assert.AreEqual( 1, functionExpression.Arguments.Count );
Expression arg = functionExpression.Arguments.First();
Assert.IsTrue( arg is SelectExpression );
}
public void Expression_With_Multiple_Operators_With_Brackets_Around_Addition()
{
// setup
var tokenizer = NewTokenizer( "(A.Field1 + 120) * 50" );
tokenizer.ReadNextToken();
ExpressionParser parser = new ExpressionParser( tokenizer );
// exercise
Expression expression = parser.Execute();
// verify
Assert.IsNotNull( expression );
Assert.IsTrue( expression is OperatorExpression );
var operatorExpression = (OperatorExpression) expression;
Assert.AreEqual( "*", operatorExpression.Operator );
Assert.IsTrue( operatorExpression.Left is NestedExpression );
Assert.IsTrue( operatorExpression.Right is IdentifierExpression );
NestedExpression leftExpression = (NestedExpression) operatorExpression.Left;
Assert.IsTrue( leftExpression.Expression is OperatorExpression );
OperatorExpression leftOperatorExpression = (OperatorExpression) leftExpression.Expression;
Assert.AreEqual( "A.Field1", leftOperatorExpression.Left.Value );
Assert.AreEqual( "+", leftOperatorExpression.Operator );
Assert.AreEqual( "120", leftOperatorExpression.Right.Value );
Assert.AreEqual( "50", operatorExpression.Right.Value );
}
public void Can_Read_Negated_Expression()
{
var tokenizer = NewTokenizer( "NOT ( A.Field = 1 OR A.Other IS NULL )" );
tokenizer.ReadNextToken();
ExpressionParser parser = new ExpressionParser( tokenizer );
// exercise
Expression expression = parser.Execute();
// verify
Assert.IsNotNull( expression );
Assert.IsTrue( expression is NegationExpression );
NegationExpression negationExpression = (NegationExpression) expression;
Expression criteria = negationExpression.Expression;
Assert.IsTrue( criteria is NestedExpression );
NestedExpression nestedExpression = (NestedExpression) criteria;
Assert.IsTrue( nestedExpression.Expression is CriteriaExpression );
CriteriaExpression criteriaExpression = (CriteriaExpression) nestedExpression.Expression;
Assert.AreEqual( "A.Field = 1", criteriaExpression.Left.Value );
Assert.AreEqual( "OR", criteriaExpression.Operator );
Assert.AreEqual( "A.Other IS NULL", criteriaExpression.Right.Value );
}
public void Expression_With_Multiple_Operators_With_Multiplication_First()
{
// setup
var tokenizer = NewTokenizer( "A.Field1 * 120 + 50" );
tokenizer.ReadNextToken();
ExpressionParser parser = new ExpressionParser( tokenizer );
// exercise
Expression expression = parser.Execute();
// verify
Assert.IsNotNull( expression );
Assert.IsTrue( expression is OperatorExpression );
var operatorExpression = (OperatorExpression) expression;
Assert.AreEqual( "*", operatorExpression.Operator );
Assert.IsTrue( operatorExpression.Left is IdentifierExpression );
Assert.IsTrue( operatorExpression.Right is OperatorExpression );
Assert.AreEqual( "A.Field1", operatorExpression.Left.Value );
var rightExpression = (OperatorExpression) operatorExpression.Right;
Assert.AreEqual( "120", rightExpression.Left.Value );
Assert.AreEqual( "+", rightExpression.Operator );
Assert.AreEqual( "50", rightExpression.Right.Value );
}
protected Expression ProcessExpression()
{
var parser = new ExpressionParser(Tokenizer);
return(parser.Execute());
}
public void Expression_With_Nested_Select_Statement()
{
// setup
var tokenizer = NewTokenizer( "( SELECT A.Field1 FROM Table ) * ( 50 + ( 20 * F.ID ) )" );
tokenizer.ReadNextToken();
ExpressionParser parser = new ExpressionParser( tokenizer );
// exercise
Expression expression = parser.Execute();
// verify
Assert.IsNotNull( expression );
Assert.IsTrue( expression is OperatorExpression );
var operatorExpression = (OperatorExpression) expression;
Assert.AreEqual( "*", operatorExpression.Operator );
Assert.IsTrue( operatorExpression.Left is NestedExpression );
NestedExpression leftExpression = (NestedExpression) operatorExpression.Left;
Assert.IsTrue( leftExpression.Expression is SelectExpression );
SelectExpression leftOperatorExpression = (SelectExpression) leftExpression.Expression;
Assert.AreEqual( "A.Field1", leftOperatorExpression.Statement.Fields[ 0 ].Expression.Value );
Assert.AreEqual( "Table", leftOperatorExpression.Statement.From[ 0 ].Name );
}
private void ProcessFieldDefinition()
{
Nullability nullability = Nullability.Nullable;
bool isPrimaryKey = false;
Identity identity = null;
string fieldName = GetIdentifier();
SqlType type = ProcessType();
if (type == null)
{
FieldDefinition calcExpression = new FieldDefinition()
{
Name = fieldName, Nullability = Nullability.Nullable, Type = null
};
ExpressionParser parser = new ExpressionParser(Tokenizer);
calcExpression.CalculatedValue = parser.Execute();
_statement.Fields.Add(calcExpression);
return;
}
if (Tokenizer.TokenEquals(Constants.Identity))
{
identity = ProcessIdentity();
}
if (Tokenizer.TokenEquals(Constants.Collate))
{
type.Collation = CurrentToken;
ReadNextToken();
}
if (Tokenizer.TokenEquals(Constants.Null))
{
nullability = Nullability.Nullable;
}
if (Tokenizer.TokenEquals(Constants.Not))
{
Tokenizer.ExpectToken(Constants.Null);
nullability = Nullability.NotNullable;
}
if (Tokenizer.TokenEquals(Constants.Identity))
{
identity = ProcessIdentity();
}
if (Tokenizer.TokenEquals(Constants.Primary))
{
Tokenizer.ExpectToken(Constants.Key);
nullability = Nullability.NotNullable;
isPrimaryKey = true;
}
if (Tokenizer.TokenEquals(Constants.Constraint))
{
// TODO: process column constraint
string name = GetIdentifier();
Tokenizer.ExpectToken(Constants.Default);
using (Tokenizer.ExpectBrackets())
{
Expression expression = ProcessExpression();
string defaultValue = expression.Value;
}
}
if (Tokenizer.TokenEquals(Constants.Default))
{
// TODO: process column constraint
Expression expression = ProcessExpression();
string defaultValue = expression.Value;
}
_statement.Fields.Add(
new FieldDefinition()
{
Name = fieldName,
Type = type,
Nullability = nullability,
IsPrimaryKey = isPrimaryKey,
Identity = identity
}
);
}
public void Can_Read_Not_Null_Expression()
{
var tokenizer = NewTokenizer( "A.Field IS NOT NULL" );
tokenizer.ReadNextToken();
ExpressionParser parser = new ExpressionParser( tokenizer );
// exercise
Expression expression = parser.Execute();
// verify
Assert.IsNotNull( expression );
Assert.IsTrue( expression is CriteriaExpression );
CriteriaExpression criteria = (CriteriaExpression) expression;
Assert.AreEqual( "A.Field", criteria.Left.Value );
Assert.AreEqual( "IS", criteria.Operator );
Assert.AreEqual( "NOT NULL", criteria.Right.Value );
}