public void TestSimpleExpressionWithOperatorInQuotes()
{
CriteriaExpression tree = new CriteriaExpression("Name = 'Peter = is not cool'");
Assert.AreEqual("=", tree.Expression);
Assert.AreEqual("Name", tree.Left.Expression);
Assert.AreEqual("Peter = is not cool", tree.Right.Expression);
}
public void TestSimpleExpression_NotLike()
{
CriteriaExpression tree = new CriteriaExpression("Name not like 'Pet%'");
Assert.AreEqual(" NOT LIKE", tree.Expression);
Assert.AreEqual("Name", tree.Left.Expression);
Assert.AreEqual("Pet%", tree.Right.Expression);
}
private static Criteria GetCriteriaLeaf(CriteriaExpression criteriaExpression)
{
string propName = criteriaExpression.Left.Expression;
string operatorString = criteriaExpression.Expression;
object value = criteriaExpression.Right.Expression;
Criteria.ComparisonOp comparisonOp = CreateComparisonOperator(operatorString);
if ((comparisonOp == Criteria.ComparisonOp.In || comparisonOp == Criteria.ComparisonOp.NotIn) && value is string)
{
string inValuesString = value.ToString().TrimStart('(').TrimEnd(')');
HabaneroStringBuilder valueStringBuilder = new HabaneroStringBuilder(inValuesString);
valueStringBuilder.RemoveQuotedSections();
List<string> finalStrings = new List<string>();
int commaIndex;
int lastIndex = 0;
commaIndex = valueStringBuilder.IndexOf(",");
while (commaIndex != -1) {
HabaneroStringBuilder oneValueSubstring = valueStringBuilder.Substring(lastIndex, commaIndex-lastIndex);
finalStrings.Add(oneValueSubstring.PutBackQuotedSections().ToString().Trim());
lastIndex = commaIndex+1;
commaIndex = valueStringBuilder.IndexOf(",", lastIndex);
}
HabaneroStringBuilder oneValueString = valueStringBuilder.Substring(lastIndex, valueStringBuilder.ToString().Length - lastIndex);
finalStrings.Add(oneValueString.PutBackQuotedSections().ToString().Trim());
value = new Criteria.CriteriaValues(finalStrings);
}
return new Criteria(propName, comparisonOp, value);
}
public void TestSimpleExpression_In()
{
CriteriaExpression tree = new CriteriaExpression("Name in ('Peter', 'Mark')");
Assert.AreEqual(" IN ", tree.Expression);
Assert.AreEqual("Name", tree.Left.Expression);
Assert.AreEqual("('Peter', 'Mark')", tree.Right.Expression);
}
/// <summary>
/// Private constructor to create a new expression using the
/// expression string provided
/// </summary>
/// <param name="expressionString">The expression string</param>
private Expression(string expressionString)
{
//TODO: Error check valid inputs
CriteriaExpression c = new CriteriaExpression(expressionString, _operators);
//Create left expression
if (c.IsLeaf())
{
}
if (c.Left.IsLeaf())
{
_leftExpression = new Parameter(c.Left.CompleteExpression);
}
else
{
_leftExpression = new Expression(c.Left.CompleteExpression);
}
//Create operator
_sqlOperator = new SqlOperator(c.Expression);
//Create right expression
if (c.Right.IsLeaf())
{
_rightExpression = new Parameter(c.Right.CompleteExpression);
}
else
{
_rightExpression = new Expression(c.Right.CompleteExpression);
}
}
private static Criteria GetCriteriaLeaf(CriteriaExpression criteriaExpression)
{
string propName = criteriaExpression.Left.Expression;
string operatorString = criteriaExpression.Expression;
object value = criteriaExpression.Right.Expression;
Criteria.ComparisonOp comparisonOp = CreateComparisonOperator(operatorString);
if ((comparisonOp == Criteria.ComparisonOp.In || comparisonOp == Criteria.ComparisonOp.NotIn) && value is string)
{
string inValuesString = value.ToString().TrimStart('(').TrimEnd(')');
HabaneroStringBuilder valueStringBuilder = new HabaneroStringBuilder(inValuesString);
valueStringBuilder.RemoveQuotedSections();
List <string> finalStrings = new List <string>();
int commaIndex;
int lastIndex = 0;
commaIndex = valueStringBuilder.IndexOf(",");
while (commaIndex != -1)
{
HabaneroStringBuilder oneValueSubstring = valueStringBuilder.Substring(lastIndex, commaIndex - lastIndex);
finalStrings.Add(oneValueSubstring.PutBackQuotedSections().ToString().Trim());
lastIndex = commaIndex + 1;
commaIndex = valueStringBuilder.IndexOf(",", lastIndex);
}
HabaneroStringBuilder oneValueString = valueStringBuilder.Substring(lastIndex, valueStringBuilder.ToString().Length - lastIndex);
finalStrings.Add(oneValueString.PutBackQuotedSections().ToString().Trim());
value = new Criteria.CriteriaValues(finalStrings);
}
return(new Criteria(propName, comparisonOp, value));
}
/// <summary>
/// Constructor that creates a parameter based on the parameter clause
/// provided
/// </summary>
/// <param name="parameterClause">A clause for a single parameter.
/// This must have the syntax:<br/>
/// <code>parameterName sqlOperator parameterValue</code>
/// (e.g. <code>Field1 >= 3</code>)<br/>
/// NOTE_: The parameter value should not be enclosed in inverted
/// commas.</param>
public Parameter(string parameterClause)
{
CriteriaExpression c = new CriteriaExpression(parameterClause);
_parameterName = c.Left.Expression;
_fieldName = _parameterName;
_sqlOperator = c.Expression;
_parameterValue = c.Right.Expression;
}
public void TestSimpleExpression()
{
CriteriaExpression tree = new CriteriaExpression("Name = 'Peter'");
Assert.AreEqual("=", tree.Expression);
Assert.AreEqual("Name", tree.Left.Expression);
Assert.AreEqual("Peter", tree.Right.Expression);
tree = new CriteriaExpression("Amount >= 0");
Assert.AreEqual(">=", tree.Expression);
Assert.AreEqual("Amount", tree.Left.Expression);
Assert.AreEqual("0", tree.Right.Expression);
}
///<summary>
/// Creates a criteria object by parsing the criteriaString into a criteria
/// expression object.
///</summary>
///<param name="criteriaString">The Criteria string that is being parsed.</param>
public static Criteria CreateCriteria(string criteriaString)
{
if (string.IsNullOrEmpty(criteriaString)) return null;
CriteriaExpression criteriaExpression = new CriteriaExpression(criteriaString);
if (criteriaExpression == null || criteriaExpression.Left == null || criteriaExpression.Right == null || criteriaExpression.Expression == null)
{
throw new HabaneroDeveloperException("There is an application error please contact your system administrator",
"The criteria string " + criteriaString + " is not a valid criteria string");
}
Criteria criteria = GetCriteria(criteriaExpression);
return criteria;
}
private static Criteria GetCriteria(CriteriaExpression criteriaExpression)
{
Criteria criteria;
if (criteriaExpression.Left.IsLeaf())//I.e. the left is a prop name.
{
criteria = GetCriteriaLeaf(criteriaExpression);
}
else
{
Criteria leftCriteria = GetCriteria(criteriaExpression.Left);
Criteria rightCriteria = GetCriteria(criteriaExpression.Right);
Criteria.LogicalOp logicalOp = CreateLogicalOperator(criteriaExpression.Expression);
criteria = new Criteria(leftCriteria, logicalOp, rightCriteria);
}
return(criteria);
}
private static Criteria GetCriteria(CriteriaExpression criteriaExpression)
{
Criteria criteria;
if (criteriaExpression.Left.IsLeaf())//I.e. the left is a prop name.
{
criteria = GetCriteriaLeaf(criteriaExpression);
}
else
{
Criteria leftCriteria = GetCriteria(criteriaExpression.Left);
Criteria rightCriteria = GetCriteria(criteriaExpression.Right);
Criteria.LogicalOp logicalOp = CreateLogicalOperator(criteriaExpression.Expression);
criteria = new Criteria(leftCriteria, logicalOp, rightCriteria);
}
return criteria;
}
///<summary>
/// Creates a criteria object by parsing the criteriaString into a criteria
/// expression object.
///</summary>
///<param name="criteriaString">The Criteria string that is being parsed.</param>
public static Criteria CreateCriteria(string criteriaString)
{
if (string.IsNullOrEmpty(criteriaString))
{
return(null);
}
CriteriaExpression criteriaExpression = new CriteriaExpression(criteriaString);
if (criteriaExpression == null || criteriaExpression.Left == null || criteriaExpression.Right == null || criteriaExpression.Expression == null)
{
throw new HabaneroDeveloperException("There is an application error please contact your system administrator",
"The criteria string " + criteriaString + " is not a valid criteria string");
}
Criteria criteria = GetCriteria(criteriaExpression);
return(criteria);
}
public void TestWithAndOR()
{
String[] operators = new String[]
{
"OR",
"AND"
};
CriteriaExpression tree =
new CriteriaExpression("((Name = 'Te' '(st' and Field1 >= 1) OR Field2 <= 2)", operators);
Assert.AreEqual("(Name = 'Te' '(st' AND Field1 >= 1)", tree.Left.CompleteExpression);
Assert.AreEqual("((Name = 'Te' '(st' AND Field1 >= 1) OR Field2 <= 2)", tree.CompleteExpression);
}
public static CriteriaExpression CreateInExpression(string expression)
{
CriteriaExpression criteriaExpression = new CriteriaExpression("");
criteriaExpression._expression = expression;
return criteriaExpression;
}
public void TestComplexExpression()
{
CriteriaExpression tree = new CriteriaExpression("Name = 'Peter' AND Age < 30");
Assert.AreEqual(" AND ", tree.Expression);
Assert.AreEqual("=", tree.Left.Expression);
Assert.AreEqual("Name", tree.Left.Left.Expression);
Assert.AreEqual("(Name = Peter)", tree.Left.CompleteExpression);
Assert.AreEqual("Peter", tree.Left.Right.Expression);
Assert.AreEqual("<", tree.Right.Expression);
Assert.AreEqual("Age", tree.Right.Left.Expression);
Assert.AreEqual("30", tree.Right.Right.Expression);
}
/// <summary>
/// Parses the expression into a linked list of expression objects
/// </summary>
/// <param name="expression">The expression to parse</param>
private void parseExpression(HabaneroStringBuilder expression)
{
HabaneroStringBuilder quotesRemovedExpression = expression;
//Remove any sections sorounded by opening and closing ' (single quote)
quotesRemovedExpression.RemoveQuotedSections();
//Upper case such that the operator or Or and OR etc are identical.
String expressionWithoutQuotes = quotesRemovedExpression.ToString().ToUpper();
//Check if the first character is an opening bracket if it is then find the closing bracket
// and set this as the left expression.
if (expressionWithoutQuotes.IndexOf("(") == 0)
{
int bracketCount = 1;
int bracketSearchPos = 0;
while ((bracketCount > 0) && (bracketSearchPos < expressionWithoutQuotes.Length - 1))
{
bracketSearchPos++;
switch (expressionWithoutQuotes[bracketSearchPos])
{
case '(':
bracketCount++;
break;
case ')':
bracketCount--;
break;
}
}
if (bracketSearchPos == expressionWithoutQuotes.Length - 1)
{
parseExpression(
quotesRemovedExpression.Substring(1, expressionWithoutQuotes.Length - 2).PutBackQuotedSections());
return;
}
_left = new CriteriaExpression(quotesRemovedExpression.Substring(1, bracketSearchPos - 1)
.PutBackQuotedSections().ToString().Trim(), _operators);
int pos = -1;
string foundOperator = "";
foreach (String op in _operators)
{
int thisPos = expressionWithoutQuotes.IndexOf(op, bracketSearchPos);
if ((thisPos == -1 || thisPos >= pos) && pos != -1) continue;
pos = thisPos;
foundOperator = op;
}
if (pos != -1)
{
_right = new CriteriaExpression(quotesRemovedExpression.Substring(pos + foundOperator.Length)
.PutBackQuotedSections().ToString().Trim(), _operators);
_expression = foundOperator;
}
}
else
{
foreach (String op in _operators)
{
int pos = expressionWithoutQuotes.IndexOf(op);
if (pos == -1 || IsPosInsideBrackets(expressionWithoutQuotes, pos)) continue;
_left = new CriteriaExpression(quotesRemovedExpression.Substring(0, pos)
.PutBackQuotedSections().ToString().Trim(), _operators);
if (op.Trim() == "IN" || op.Trim() == "NOT IN")
{
var criteriaExpression = new CriteriaExpression("");
criteriaExpression._expression = quotesRemovedExpression.Substring(pos + op.Length)
.PutBackQuotedSections().ToString().Trim();
_right = criteriaExpression;
}
else
{
_right = new CriteriaExpression(quotesRemovedExpression.Substring(pos + op.Length)
.PutBackQuotedSections().ToString().Trim(), _operators);
}
_expression = op;
break;
}
}
//If this was a terminal criteria i.e. it has no more children then
// this is the expression there will be no right and left expression.
if (string.IsNullOrEmpty(_expression))
{
_expression = quotesRemovedExpression.PutBackQuotedSections().DropOuterQuotes().ToString();
}
}
public void TestCompleteExpression()
{
CriteriaExpression tree = new CriteriaExpression("Name = 'Peter'");
Assert.AreEqual("(Name = Peter)", tree.CompleteExpression);
}
public void TestSettingOperators()
{
String[] operators = new String[]
{
"OR",
"AND"
};
CriteriaExpression tree = new CriteriaExpression("Name = 'Test'", operators);
Assert.AreEqual("Name = 'Test'", tree.CompleteExpression);
tree = new CriteriaExpression("Name = 'Test' and Field1 >= 1", operators);
Assert.AreEqual("Name = 'Test'", tree.Left.CompleteExpression);
Assert.AreEqual("Field1 >= 1", tree.Right.CompleteExpression);
tree = new CriteriaExpression("A = 1 and B = 2 or C = 3", operators);
Assert.AreEqual("((A = 1 AND B = 2) OR C = 3)", tree.CompleteExpression);
}
// public string SqlExpressionString(string tableNameFieldNameLeftSeperator,
// string tableNameFieldNameRightSeperator,
// string dateTimeLeftSeperator,
// string dateTimeRightSeperator)
// {
// return "(" + _leftExpression.SqlExpressionString(tableNameFieldNameLeftSeperator,
// tableNameFieldNameRightSeperator, dateTimeLeftSeperator, dateTimeRightSeperator) +
// " " + _sqlOperator.ExpressionString() +
// " " + _rightExpression.SqlExpressionString(tableNameFieldNameLeftSeperator,
// tableNameFieldNameRightSeperator, dateTimeLeftSeperator, dateTimeRightSeperator) + ")";
// }
#endregion //IExpression Interface Implementation
#region Static Members
/// <summary>
/// Creates a new IExpression object using the expression string provided.
/// </summary>
/// <param name="expressionClause">The expression string</param>
/// <returns>If the expression is a leaf object (it has no left and right
/// expressions), then a Parameter object is returned, otherwise an
/// Expression object is returned.</returns>
public static IExpression CreateExpression(string expressionClause)
{
CriteriaExpression c = new CriteriaExpression(expressionClause, _operators);
if (c.IsLeaf())
{
return new Parameter(expressionClause);
}
return new Expression(expressionClause);
}
public void TestWithDoubleBrackets()
{
String[] operators = new String[]
{
"OR",
"AND"
};
CriteriaExpression tree =
new CriteriaExpression("(Name = 'Test' OR Field1 >= 1) AND (Field2 <= 2 OR Name = 'Test2')", operators);
//Test left side
CriteriaExpression leftExpression = tree.Left;
Assert.AreEqual("(Name = 'Test' OR Field1 >= 1)", leftExpression.CompleteExpression);
Assert.AreEqual("Name = 'Test'", leftExpression.Left.CompleteExpression);
Assert.AreEqual("OR", leftExpression.Expression);
Assert.AreEqual("Field1 >= 1", leftExpression.Right.CompleteExpression);
//Tes operator
Assert.AreEqual("AND", tree.Expression);
//Test right side
CriteriaExpression rightExpression = tree.Right;
Assert.AreEqual("(Field2 <= 2 OR Name = 'Test2')", rightExpression.CompleteExpression);
Assert.AreEqual("Field2 <= 2", rightExpression.Left.CompleteExpression);
Assert.AreEqual("OR", rightExpression.Expression);
Assert.AreEqual("Name = 'Test2'", rightExpression.Right.CompleteExpression);
//Test complete
Assert.AreEqual("((Name = 'Test' OR Field1 >= 1) AND (Field2 <= 2 OR Name = 'Test2'))", tree.CompleteExpression);
}
public void TestSetLeftAndRight()
{
CriteriaExpression tree = new CriteriaExpression("Name = 'Peter' AND Age < 30");
tree.Left = new CriteriaExpression("Height > 20");
tree.Right = new CriteriaExpression("Town = 'Durban'");
Assert.AreEqual("(Height > 20)", tree.Left.CompleteExpression);
Assert.AreEqual("(Town = Durban)", tree.Right.CompleteExpression);
}
/// <summary>
/// Parses the expression into a linked list of expression objects
/// </summary>
/// <param name="expression">The expression to parse</param>
private void parseExpression(HabaneroStringBuilder expression)
{
HabaneroStringBuilder quotesRemovedExpression = expression;
//Remove any sections sorounded by opening and closing ' (single quote)
quotesRemovedExpression.RemoveQuotedSections();
//Upper case such that the operator or Or and OR etc are identical.
String expressionWithoutQuotes = quotesRemovedExpression.ToString().ToUpper();
//Check if the first character is an opening bracket if it is then find the closing bracket
// and set this as the left expression.
if (expressionWithoutQuotes.IndexOf("(") == 0)
{
int bracketCount = 1;
int bracketSearchPos = 0;
while ((bracketCount > 0) && (bracketSearchPos < expressionWithoutQuotes.Length - 1))
{
bracketSearchPos++;
switch (expressionWithoutQuotes[bracketSearchPos])
{
case '(':
bracketCount++;
break;
case ')':
bracketCount--;
break;
}
}
if (bracketSearchPos == expressionWithoutQuotes.Length - 1)
{
parseExpression(
quotesRemovedExpression.Substring(1, expressionWithoutQuotes.Length - 2).PutBackQuotedSections());
return;
}
_left = new CriteriaExpression(quotesRemovedExpression.Substring(1, bracketSearchPos - 1)
.PutBackQuotedSections().ToString().Trim(), _operators);
int pos = -1;
string foundOperator = "";
foreach (String op in _operators)
{
int thisPos = expressionWithoutQuotes.IndexOf(op, bracketSearchPos);
if ((thisPos == -1 || thisPos >= pos) && pos != -1)
{
continue;
}
pos = thisPos;
foundOperator = op;
}
if (pos != -1)
{
_right = new CriteriaExpression(quotesRemovedExpression.Substring(pos + foundOperator.Length)
.PutBackQuotedSections().ToString().Trim(), _operators);
_expression = foundOperator;
}
}
else
{
foreach (String op in _operators)
{
int pos = expressionWithoutQuotes.IndexOf(op);
if (pos == -1 || IsPosInsideBrackets(expressionWithoutQuotes, pos))
{
continue;
}
_left = new CriteriaExpression(quotesRemovedExpression.Substring(0, pos)
.PutBackQuotedSections().ToString().Trim(), _operators);
if (op.Trim() == "IN" || op.Trim() == "NOT IN")
{
var criteriaExpression = new CriteriaExpression("");
criteriaExpression._expression = quotesRemovedExpression.Substring(pos + op.Length)
.PutBackQuotedSections().ToString().Trim();
_right = criteriaExpression;
}
else
{
_right = new CriteriaExpression(quotesRemovedExpression.Substring(pos + op.Length)
.PutBackQuotedSections().ToString().Trim(), _operators);
}
_expression = op;
break;
}
}
//If this was a terminal criteria i.e. it has no more children then
// this is the expression there will be no right and left expression.
if (string.IsNullOrEmpty(_expression))
{
_expression = quotesRemovedExpression.PutBackQuotedSections().DropOuterQuotes().ToString();
}
}
