private static string Operator(ComparisonExpression expression)
{
if (expression.Operator == ComparisonOperator.GreaterOrEqual)
{
return(">=");
}
else if (expression.Operator == ComparisonOperator.Like || expression.Operator == ComparisonOperator.Equal)
{
return("=");
}
else if (expression.Operator == ComparisonOperator.NotLike || expression.Operator == ComparisonOperator.NotEqual)
{
return("!=");
}
else if (expression.Operator == ComparisonOperator.LessOrEqual)
{
return("<=");
}
else if (expression.Operator == ComparisonOperator.Greater)
{
return(">");
}
else if (expression.Operator == ComparisonOperator.Less)
{
return("<");
}
else
{
throw new Exception("Operator not provided by HRNX");
}
}
public void DeleteValidTest()
{
//create a new data entity
DataEntity entity = new DataEntity();
//create a new operation input with a new entity array for the input property
OperationInput operationInput = new OperationInput {
Input = new DataEntity[1]
};
//set the first item in the input property
operationInput.Input[0] = entity;
operationInput.Input[0].ObjectDefinitionFullName = "ScribeChangeHistory";
//set the name of the operation
operationInput.Name = "Delete";
//create the comparison experssion for selecting the records to delete
ComparisonExpression comparisonExpression = new ComparisonExpression();
comparisonExpression.ExpressionType = ExpressionType.Comparison;
comparisonExpression.Operator = ComparisonOperator.Less;
comparisonExpression.LeftValue = new ComparisonValue {
ValueType = ComparisonValueType.Constant, Value = "ModifiedOn"
};
comparisonExpression.RightValue = new ComparisonValue {
ValueType = ComparisonValueType.Variable, Value = DateTime.UtcNow
};
operationInput.LookupCondition[0] = comparisonExpression;
//execute the operation from the connector
OperationResult operationResult = _rsTargetConnector.ExecuteOperation(operationInput);
//validate that the operation was a success
Assert.IsTrue(operationResult.Success[0]);
}
public override bool Equals(object obj)
{
if (this == obj)
{
return(true);
}
if (obj == null || this.GetType() != obj.GetType())
{
return(false);
}
ComparisonExpression other = (ComparisonExpression)obj;
if (left != null ? !left.Equals(other.left) : other.left != null)
{
return(false);
}
if (op != null ? !op.Equals(other.op) : other.op != null)
{
return(false);
}
if (right != null ? !right.Equals(other.right) : other.right != null)
{
return(false);
}
return(true);
}
/// <summary>
/// Creates a query with a constraint and ordering to filter our result set
/// </summary>
/// <returns></returns>
private Query CreateFilterQuery()
{
var query = new Query();
var constraint = new ComparisonExpression
{
ExpressionType = ExpressionType.Comparison,
LeftValue = new ComparisonValue {
Value = "WebinarKey", ValueType = ComparisonValueType.Property
},
RightValue = new ComparisonValue {
Value = "1235813", ValueType = ComparisonValueType.Constant
}
};
var sequence = new Sequence
{
PropertyName = "FirstName",
Direction = SequenceDirection.Descending
};
query.Constraints = constraint;
query.RootEntity.ObjectDefinitionFullName = "Registrant";
query.RootEntity.SequenceList = new List <Sequence>();
query.RootEntity.SequenceList.Add(sequence);
return(query);
}
/// <summary>
/// Parse through the expression to convert it to a query
/// </summary>
/// <param name="expression">Comparison Expression from OperationInpit.LookupCondition</param>
/// <returns>return the converted expression</returns>
private string ParseComparisionExpression(ComparisonExpression expression)
{
string comparisonString = string.Empty;
//parse the expression and throw an exception if it is unsupported
switch (expression.Operator)
{
case ComparisonOperator.Equal:
comparisonString = "=";
break;
case ComparisonOperator.Less:
comparisonString = "<";
break;
case ComparisonOperator.LessOrEqual:
comparisonString = "<=";
break;
default:
throw new NotSupportedException(string.Format("Operation Not Supported : {0}", expression.Operator));
}
//Get the last sync date from the expression right value
DateTime lastSyncDate = Convert.ToDateTime(expression.RightValue.Value);
//return the converted expression
return(string.Format("WHERE ModifiedOn {0} '{1}'", comparisonString, lastSyncDate.ToString("u").TrimEnd('Z')));
}
private static void EnsureValidComparisonExpression(ComparisonExpression comparisonExpression)
{
if (comparisonExpression == null)
{
throw new InvalidExecuteQueryException(StringMessages.ErrorInvalidComparisionExpression);
}
if (comparisonExpression.LeftValue?.Value == null)
{
throw new InvalidExecuteQueryException(StringMessages.ErrorNullLeftValue);
}
if (comparisonExpression.Operator != ComparisonOperator.Equal)
{
string operatorString;
switch (comparisonExpression.Operator)
{
case ComparisonOperator.Greater:
operatorString = "Greater Than";
break;
case ComparisonOperator.Less:
operatorString = "Less Than";
break;
default:
operatorString = comparisonExpression.Operator.ToString();
break;
}
throw new InvalidExecuteQueryException(string.Format(StringMessages.ErrorInvalidLogicalExpression, operatorString, comparisonExpression.LeftValue?.Value));
}
}
/// <summary>Optimizations: !(Bool)->(!Bool), !!X->X, !(X==Bool)->(X==!Bool)
/// </summary>
public virtual IExpression Not(IExpression expr)
{
if (expr.Equals(BoolConstExpression.True))
{
return(BoolConstExpression.False);
}
if (expr.Equals(BoolConstExpression.False))
{
return(BoolConstExpression.True);
}
if (expr is NotExpression)
{
return(((NotExpression)expr).Expr());
}
if (expr is ComparisonExpression)
{
ComparisonExpression cmpExpr = (ComparisonExpression)expr;
if (cmpExpr.Right() is ConstValue)
{
ConstValue rightConst = (ConstValue)cmpExpr.Right();
if (rightConst.Value() is bool)
{
bool boolVal = (bool)rightConst.Value();
// new Boolean() instead of Boolean.valueOf() for .NET conversion
return(new ComparisonExpression(cmpExpr.Left(), new ConstValue(!boolVal), cmpExpr
.Op()));
}
}
}
return(new NotExpression(expr));
}
public override void deserializeFromMocaTree(Serialization.MocaTree.MocaNode actNode)
{
MocaNode constExpNode = actNode.getChildNodeWithName("constraintExpression");
this.ConstraintExpression = Expression.createExpression(constExpNode.getAttributeOrCreate("type").Value, Repository) as ComparisonExpression;
this.ConstraintExpression.deserializeFromMocaTree(constExpNode);
}
public virtual void Visit(ComparisonExpression expression)
{
this.LoadQuery();
this.Descend(this.FieldNames(expression.Left()));
expression.Right().Accept(this.ComparisonEmitter());
this.Constrain(expression.Op());
}
// Comparison is the most nested level
protected override void Visit(ComparisonExpression exp)
{
var model = new ComparisonModel
{
ParamName = exp.Param.ParamName,
Value = exp.Value.Value,
Operator = exp.Operator
};
var op = _comparisonOperators.Find(exp.Operator);
if (op != null)
{
model.OperatorDisplayName = op.Name;
if (!String.IsNullOrWhiteSpace(op.Alias))
{
model.OperatorDisplayName = op.Alias;
}
}
var param = _parameters.Find(x => x.Name.Equals(model.ParamName, StringComparison.OrdinalIgnoreCase));
if (param != null)
{
model.ValueType = param.ValueType.FullName;
model.IsNumberValue = param.ValueType.IsNumericType();
}
_stack.Push(model);
}
public void DeleteInvalidDatePropertyTest()
{
//create a new data entity
DataEntity entity = new DataEntity();
//create a new operation input with a new entity array for the input property
OperationInput operationInput = new OperationInput { Input = new DataEntity[1] };
//set the first item in the input property
operationInput.Input[0] = entity;
//set the name of the operation
operationInput.Name = "Delete";
//create the comparison experssion for selecting the records to delete
ComparisonExpression comparisonExpression = new ComparisonExpression();
comparisonExpression.ExpressionType = ExpressionType.Comparison;
comparisonExpression.Operator = ComparisonOperator.Less;
comparisonExpression.LeftValue = new ComparisonValue { ValueType = ComparisonValueType.Constant, Value = "ModifiedOn" };
//note: the invalid property where the date should be
comparisonExpression.RightValue = new ComparisonValue { ValueType = ComparisonValueType.Variable, Value = InvalidPropertyValue };
operationInput.LookupCondition[0] = comparisonExpression;
//execute the operation from the connector
OperationResult operationResult = _rsSourceConnector.ExecuteOperation(operationInput);
//validate that the operation was not a success
Assert.IsFalse(operationResult.Success[0]);
//validate that the error info is filled in
Assert.IsNotNull(operationResult.ErrorInfo[0]);
}
private FilterExpression CreateFilterByIds <TId>(ICollection <TId> ids, AttrAttribute idAttribute, FilterExpression existingFilter)
{
var idChain = new ResourceFieldChainExpression(idAttribute);
FilterExpression filter = null;
if (ids.Count == 1)
{
var constant = new LiteralConstantExpression(ids.Single().ToString());
filter = new ComparisonExpression(ComparisonOperator.Equals, idChain, constant);
}
else if (ids.Count > 1)
{
var constants = ids.Select(id => new LiteralConstantExpression(id.ToString())).ToList();
filter = new EqualsAnyOfExpression(idChain, constants);
}
// @formatter:keep_existing_linebreaks true
return(filter == null
? existingFilter
: existingFilter == null
? filter
: new LogicalExpression(LogicalOperator.And, ArrayFactory.Create(filter, existingFilter)));
// @formatter:keep_existing_linebreaks restore
}
private void ParseNullOperators(ComparisonExpression comparisonExpression)
{
//check for any form of a null right expression
if (comparisonExpression.RightValue == null ||
comparisonExpression.RightValue.Value == null)
{
//set the appropriate operator
switch (comparisonExpression.Operator)
{
case ComparisonOperator.Equal:
comparisonExpression.Operator = ComparisonOperator.IsNull;
break;
case ComparisonOperator.NotEqual:
comparisonExpression.Operator = ComparisonOperator.IsNotNull;
break;
case ComparisonOperator.IsNull:
case ComparisonOperator.IsNotNull:
break;
//default:
//throw new NotSupportedException(string.Format(ErrorCodes.NullOperatorNotValid.Description, comparisonExpression.Operator));
}
}
}
public override QueryExpression VisitComparison(ComparisonExpression expression, object argument)
{
if (expression?.Left is ResourceFieldChainExpression && expression.Right is ResourceFieldChainExpression)
{
throw new AttributeComparisonInFilterNotSupportedException();
}
return(base.VisitComparison(expression, argument));
}
private static KeyValuePair <string, object> ParseComparisonExpression(ComparisonExpression comparisonExpression)
{
var fieldName = GetFieldName(comparisonExpression.LeftValue.Value.ToString());
var dataValue = comparisonExpression.RightValue.Value;
var filter = new KeyValuePair <string, object>(fieldName, dataValue);
return(filter);
}
/// <summary>
/// Creates a regex based on the left and right values in the ComparisonExpression for a 'Is Like' type comparison
/// </summary>
/// <param name="comparisonExpression"></param>
/// <returns></returns>
private static string BuildLike(ComparisonExpression comparisonExpression)
{
const string format = "[{0}] LIKE {1}";
string returnString = string.Format(format, comparisonExpression.LeftValue.Value.ToString().Split('.')[1],
QuoteSingle(comparisonExpression.RightValue.Value.ToString()));
return(returnString);
}
/// <summary>
/// Creates a regex based on the left and right values in the ComparisonExpression for a 'Is Like' type comparison
/// </summary>
/// <param name="comparisonExpression"></param>
/// <returns></returns>
private static string BuildLike(ComparisonExpression comparisonExpression)
{
const string format = "[{0}] LIKE {1}";
string returnString = string.Format(format, comparisonExpression.LeftValue.Value.ToString().Split('.')[1],
QuoteSingle(comparisonExpression.RightValue.Value.ToString()));
return returnString;
}
/// <summary>
/// Creates a regex based on the left and right values in the ComparisonExpression for a 'Is Like' type comparison
/// </summary>
/// <param name="comparisonExpression"></param>
/// <returns></returns>
private static string BuildLike(ComparisonExpression comparisonExpression)
{
const string format = "Regex.IsMatch({0} != null ? {0} :\"\", {1}, RegexOptions.IgnoreCase)";
string returnString = string.Format(format, comparisonExpression.LeftValue.Value.ToString().Split('.')[1],
Quote(string.Format("^{0}$",
comparisonExpression.RightValue.Value.ToString().Replace("%", ".*"))));
return returnString;
}
/// <summary>
/// Creates a regex based on the left and right values in the ComparisonExpression for a 'Is Like' type comparison
/// </summary>
/// <param name="comparisonExpression"></param>
/// <returns></returns>
private static string BuildLike(ComparisonExpression comparisonExpression)
{
const string format = "Regex.IsMatch({0} != null ? {0} :\"\", {1}, RegexOptions.IgnoreCase)";
string returnString = string.Format(format, comparisonExpression.LeftValue.Value.ToString().Split('.')[1],
Quote(string.Format("^{0}$",
comparisonExpression.RightValue.Value.ToString().Replace("%", ".*"))));
return(returnString);
}
public void TestQueryWithEnumConstrain()
{
IExpression expression = ExpressionFromMethod("MatchEnumConstrain");
IExpression expected = new ComparisonExpression(
NewFieldValue(CandidateFieldRoot.Instance, "_priority", typeof(MessagePriority)),
new ConstValue(MessagePriority.High),
ComparisonOperator.ValueEquality);
Assert.AreEqual(expected, expression);
}
public override void ExitComparisonExpressionWithOperator([NotNull] RuleSetGrammarParser.ComparisonExpressionWithOperatorContext context)
{
// popping order matters
IComparisonOperand right = this.comparisonOperands.Pop();
IComparisonOperand left = this.comparisonOperands.Pop();
string op = context.GetChild(1).GetText();
ComparisonExpression expr = new ComparisonExpression(op, left, right);
this.logicalExpressions.Push(expr);
}
private FilterExpression IncludeFilterById(TId id, FilterExpression existingFilter)
{
var primaryIdAttribute = _request.PrimaryResource.Attributes.Single(a => a.Property.Name == nameof(Identifiable.Id));
FilterExpression filterById = new ComparisonExpression(ComparisonOperator.Equals,
new ResourceFieldChainExpression(primaryIdAttribute), new LiteralConstantExpression(id.ToString()));
return(existingFilter == null
? filterById
: new LogicalExpression(LogicalOperator.And, new[] { filterById, existingFilter }));
}
public void TestAppendString(ComparisonOperation op, string expected)
{
var variable = new VariableExpression("variable");
var value = new IntegerConstantExpression(99);
var expr = new ComparisonExpression(variable, op, value);
var builder = new StringBuilder();
expr.AppendString(builder);
Assert.That(builder.ToString(), Is.EqualTo(expected));
}
private Expression Equality()
{
Expression lhs = Relational();
while (look.Tag == Tags.EQ || look.Tag == Tags.NE)
{
Move();
lhs = new ComparisonExpression(prev, lhs, Relational());
}
return(lhs);
}
public override FilterExpression OnApplyFilter(FilterExpression existingFilter)
{
var resourceContext = _resourceGraph.GetResourceContext <Company>();
var isSoftDeletedAttribute = resourceContext.Attributes.Single(attribute => attribute.Property.Name == nameof(Company.IsSoftDeleted));
var isNotSoftDeleted = new ComparisonExpression(ComparisonOperator.Equals,
new ResourceFieldChainExpression(isSoftDeletedAttribute), new LiteralConstantExpression("false"));
return(existingFilter == null
? (FilterExpression)isNotSoftDeleted
: new LogicalExpression(LogicalOperator.And, new[] { isNotSoftDeleted, existingFilter }));
}
public void listBoxSelectedIndexChangedNew(object sender, EventArgs e)
{
if (listboxEntries.SelectedIndex != -1)
{
Object assignmentOrConstraint = this.assignmentsAndConstraints[listboxEntries.SelectedIndex];
if (assignmentOrConstraint is AttributeAssignment)
{
AttributeAssignment attAss = assignmentOrConstraint as AttributeAssignment;
String leftAttributeName = attAss.AttributeName;
String expressionName = attAss.ValueExpression.GetType().Name;
cmbAttributes.Text = leftAttributeName;
radioSet.Checked = true;
}
else if (assignmentOrConstraint is EAEcoreAddin.Modeling.SDMModeling.SDMExportWrapper.patterns.Constraint)
{
EAEcoreAddin.Modeling.SDMModeling.SDMExportWrapper.patterns.Constraint constraint = assignmentOrConstraint as EAEcoreAddin.Modeling.SDMModeling.SDMExportWrapper.patterns.Constraint;
String leftAttributeName = ((constraint.ConstraintExpression as ComparisonExpression).LeftExpression as AttributeValueExpression).AttributeName;
String expressionName = (constraint.ConstraintExpression as ComparisonExpression).RightExpression.GetType().Name;
cmbAttributes.Text = leftAttributeName;
ComparisonExpression compExp = constraint.ConstraintExpression as ComparisonExpression;
if (compExp.Operator == ComparingOperator.EQUAL)
{
radioEqual.Checked = true;
}
else if (compExp.Operator == ComparingOperator.GREATER)
{
radioGreater.Checked = true;
}
else if (compExp.Operator == ComparingOperator.GREATER_OR_EQUAL)
{
radioGreaterEqual.Checked = true;
}
else if (compExp.Operator == ComparingOperator.LESS)
{
radioLess.Checked = true;
}
else if (compExp.Operator == ComparingOperator.LESS_OR_EQUAL)
{
radioLessEqual.Checked = true;
}
else if (compExp.Operator == ComparingOperator.UNEQUAL)
{
radioNotEqual.Checked = true;
}
}
this.ConstraintExpressionProvider = new AttributeConstraintExpressionProvider(assignmentOrConstraint, objectVariable, repository, this.OvExpressionProvider);
expressionControl.setVisualOutput(this.ConstraintExpressionProvider, ConstraintExpressionProvider.getProviderExpression());
}
}
public void TestRebalance()
{
var variable = new VariableExpression("variable");
var value = new IntegerConstantExpression(99);
var expr = new ComparisonExpression(variable, ComparisonOperation.LessThan, value);
var result = expr.Rebalance() as ComparisonExpression;
Assert.That(result, Is.Not.Null);
Assert.That(result.Left, Is.EqualTo(expr.Left));
Assert.That(result.Operation, Is.EqualTo(expr.Operation));
Assert.That(result.Right, Is.EqualTo(expr.Right));
}
protected override ScalarExpression OnBuildQuery(QueryBuilderContext context)
{
var result = new ComparisonExpression
{
Operator = InvertResult ? ComparisonOperator.IsNotNull : ComparisonOperator.IsNull,
Expressions = new List <ScalarExpression>
{
Left.BuildQuery(context)
}
};
return(result);
}
public void Evaluate_greater_less_than(
string left, string right, ComparisonOperator comparisonOperator, bool expected)
{
var variables = new Dictionary <string, IVariableValueResolver>();
var expression = new ComparisonExpression(
new ConstantExpression(left),
new ConstantExpression(right),
comparisonOperator,
BooleanOperator.And);
var actual = ExpressionEvaluator.Evaluate(expression, variables);
actual.Should().Be.EqualTo(expected);
}
public static void assignmentsToConstraints(ObjectVariable ov, SQLRepository repository)
{
foreach (AttributeAssignment aAssignment in ov.AttributeAssignments)
{
Constraint constraint = new Constraint(repository);
ComparisonExpression compExp = new ComparisonExpression(repository);
compExp.Operator = ComparingOperator.EQUAL;
compExp.LeftExpression = new AttributeValueExpression(repository, repository.GetAttributeByGuid(aAssignment.AttributeGUID), ov.sqlElement);
compExp.RightExpression = aAssignment.ValueExpression;
constraint.ConstraintExpression = compExp;
ov.Constraints.Add(constraint);
}
ov.AttributeAssignments.Clear();
}
public override FilterExpression OnApplyFilter(FilterExpression existingFilter)
{
// Use case: automatically exclude deleted resources for all requests.
ResourceContext resourceContext = ResourceGraph.GetResourceContext <CallableResource>();
AttrAttribute isDeletedAttribute = resourceContext.Attributes.Single(attribute => attribute.Property.Name == nameof(CallableResource.IsDeleted));
var isNotDeleted = new ComparisonExpression(ComparisonOperator.Equals, new ResourceFieldChainExpression(isDeletedAttribute),
new LiteralConstantExpression(bool.FalseString));
return(existingFilter == null
? (FilterExpression)isNotDeleted
: new LogicalExpression(LogicalOperator.And, ArrayFactory.Create(isNotDeleted, existingFilter)));
}
/// <summary>
/// Convert the expression tree into a structured query expression.
/// </summary>
/// <param name="context">Context information about this query building session, including the target structured query object.</param>
/// <returns>A scalar expression that can be used within the query.</returns>
protected virtual ScalarExpression OnBuildQuery(QueryBuilderContext context)
{
// Reflect for attributes
Type type = GetType();
var queryEngAttribute = GetQueryEngineOperator(type);
if (queryEngAttribute == null)
{
throw new NotImplementedException(type.Name);
}
// Build arguments
var arguments = new List <ScalarExpression>();
foreach (ExpressionNode argument in Arguments)
{
ScalarExpression queryExpr = argument.BuildQuery(context);
arguments.Add(queryExpr);
}
// Generic Calculation Expression
if (queryEngAttribute.CalculationOperator != null)
{
var result = new CalculationExpression();
result.Operator = queryEngAttribute.CalculationOperator.Value;
result.Expressions = arguments;
return(result);
}
// Generic Comparison Expression
if (queryEngAttribute.ComparisonOperator != null)
{
var result = new ComparisonExpression();
result.Operator = queryEngAttribute.ComparisonOperator.Value;
result.Expressions = arguments;
return(result);
}
// Generic Comparison Expression
if (queryEngAttribute.LogicalOperator != null)
{
var result = new LogicalExpression();
result.Operator = queryEngAttribute.LogicalOperator.Value;
result.Expressions = arguments;
return(result);
}
throw new InvalidOperationException(type.Name);
}
private static void addCompEprToConstraints(ComparisonExpression exp, ref Dictionary <string, object> constraints)
{
if (exp.Operator != ComparisonOperator.Equal)
{
throw new InvalidExecuteQueryException(string.Format(StringMessages.OnlyEqualsOperatorAllowed, exp.Operator.ToString(), exp.LeftValue.Value));
}
var constraintKey = exp.LeftValue.Value.ToString();
if (constraintKey.LastIndexOf(".") > -1)
{
// need to remove "objectname." if present
constraintKey = constraintKey.Substring(constraintKey.LastIndexOf(".") + 1);
}
constraints.Add(constraintKey, exp.RightValue.Value.ToString());
}
public void DeleteValidTest()
{
//create a new data entity
DataEntity entity = new DataEntity();
//create a new operation input with a new entity array for the input property
OperationInput operationInput = new OperationInput {Input = new DataEntity[1]};
//set the first item in the input property
operationInput.Input[0] = entity;
//set the name of the operation
operationInput.Name = "Delete";
//create the comparison experssion for selecting the records to delete
ComparisonExpression comparisonExpression = new ComparisonExpression();
comparisonExpression.ExpressionType = ExpressionType.Comparison;
comparisonExpression.Operator = ComparisonOperator.Less;
comparisonExpression.LeftValue = new ComparisonValue { ValueType = ComparisonValueType.Constant, Value = "ModifiedOn" };
comparisonExpression.RightValue = new ComparisonValue { ValueType = ComparisonValueType.Variable, Value = DateTime.Now };
operationInput.LookupCondition[0] = comparisonExpression;
//execute the operation from the connector
OperationResult operationResult = _rsSourceConnector.ExecuteOperation(operationInput);
//validate that the operation was a success
Assert.IsTrue(operationResult.Success[0]);
}
protected virtual void WriteComparison(ComparisonExpression exp)
{
Visit(exp.Param);
WriteSpace();
var op = OperatorManager.Find(exp.Operator);
var opText = exp.Operator;
if (op != null && !String.IsNullOrWhiteSpace(op.Alias))
{
opText = op.Alias;
}
WriteKeyword(opText);
WriteSpace();
Visit(exp.Value);
}
private Query BuildComparisonQuery()
{
var sequence1 = new Sequence
{
PropertyName = "Name",
Direction = SequenceDirection.Ascending
};
var sequence2 = new Sequence
{
PropertyName = "Address",
Direction = SequenceDirection.Descending
};
var rootEntity = new QueryEntity
{
SequenceList = new List<Sequence>()
};
rootEntity.SequenceList.Add(sequence1);
rootEntity.SequenceList.Add(sequence2);
var constraints = new ComparisonExpression
{
Operator = ComparisonOperator.Equal,
ExpressionType = ExpressionType.Comparison,
LeftValue = new ComparisonValue { Value = "Name", ValueType = ComparisonValueType.Property },
RightValue = new ComparisonValue { Value = "Bobby", ValueType = ComparisonValueType.Property }
};
var query = new Query
{
Constraints = constraints,
RootEntity = rootEntity
};
return query;
}
protected override sealed void Visit(ComparisonExpression exp)
{
WriteComparison(exp);
}
private Query CreateDateFilterQuery()
{
var query = new Query();
query.RootEntity.ObjectDefinitionFullName = "Webinar";
//Build the expressions
var leftExpression = new ComparisonExpression
{
ExpressionType = ExpressionType.Comparison,
LeftValue = new ComparisonValue { Value = "Webinar.StartTime", ValueType = ComparisonValueType.Property },
RightValue = new ComparisonValue { Value = new DateTime(2010, 01, 01), ValueType = ComparisonValueType.Property },
Operator = ComparisonOperator.Equal
};
var rightExpression = new ComparisonExpression
{
ExpressionType = ExpressionType.Comparison,
LeftValue = new ComparisonValue { Value = "Webinar.EndTime", ValueType = ComparisonValueType.Property },
RightValue = new ComparisonValue { Value = new DateTime(2013, 01, 01), ValueType = ComparisonValueType.Property },
Operator = ComparisonOperator.Equal
};
var logical = new LogicalExpression
{
ExpressionType = ExpressionType.Logical,
LeftExpression = leftExpression,
RightExpression = rightExpression,
Operator = LogicalOperator.And
};
query.Constraints = logical;
//we're adding the logical expression:
// StartTime <= 1/1/2010 AND EndTime >= 1/1/2013
return query;
}
private Query BuildLogicalQuery()
{
LogicalExpression logicalExpression1 = new LogicalExpression();
logicalExpression1.Operator = LogicalOperator.And;
//Top left
LogicalExpression leftExpression1 = new LogicalExpression();
leftExpression1.Operator = LogicalOperator.And;
ComparisonExpression comparisonLeft1 = new ComparisonExpression();
comparisonLeft1.ExpressionType = ExpressionType.Comparison;
comparisonLeft1.Operator = ComparisonOperator.Greater;
comparisonLeft1.LeftValue = new ComparisonValue { Value = "Account.CreditMax" };
comparisonLeft1.RightValue = new ComparisonValue { Value = 5000 };
//nested in left
ComparisonExpression comparisonRight1 = new ComparisonExpression();
comparisonRight1.ExpressionType = ExpressionType.Comparison;
comparisonRight1.Operator = ComparisonOperator.Greater;
comparisonRight1.LeftValue = new ComparisonValue { Value = "Account.Profit" };
comparisonRight1.RightValue = new ComparisonValue { Value = 100 };
leftExpression1.LeftExpression = comparisonLeft1;
leftExpression1.RightExpression = comparisonRight1;
//Top Right
LogicalExpression rightExpression = new LogicalExpression();
rightExpression.Operator = LogicalOperator.And;
ComparisonExpression comparisonLeft2 = new ComparisonExpression();
comparisonLeft2.ExpressionType = ExpressionType.Comparison;
comparisonLeft2.Operator = ComparisonOperator.Greater;
comparisonLeft2.LeftValue = new ComparisonValue { Value = "Account.Revenue" };
comparisonLeft2.RightValue = new ComparisonValue { Value = 100 };
//nested in right
ComparisonExpression comparisonRight2 = new ComparisonExpression();
comparisonRight2.ExpressionType = ExpressionType.Comparison;
comparisonRight2.Operator = ComparisonOperator.Greater;
comparisonRight2.LeftValue = new ComparisonValue { Value = "Account.CreditMin" };
comparisonRight2.RightValue = new ComparisonValue { Value = 100 };
rightExpression.LeftExpression = comparisonLeft2;
rightExpression.RightExpression = comparisonRight2;
logicalExpression1.LeftExpression = leftExpression1;
logicalExpression1.RightExpression = rightExpression;
var query = new Query
{
Constraints = logicalExpression1,
};
return query;
}
public void SimpleFilterWithOrderByValidTest()
{
string objectName = "ProductPriceLists";
//Create a basic root query entity,
//Set the name property and the object definition full name property
//Note: 'Name' property is unique and is set by the user
// 'ObjectDefinitionFullName' property will be the name of the table referenced
var rootEntity = new QueryEntity { Name = objectName, ObjectDefinitionFullName = objectName };
//Create a list of properties for the root entity, not these are column names
rootEntity.PropertyList.Add("RecordId");
rootEntity.PropertyList.Add("ProductNumber");
rootEntity.PropertyList.Add("UnitPrice");
rootEntity.PropertyList.Add("BaseUoMQuantity");
//set the sequence direction and field to order by
rootEntity.SequenceList.Add(new Sequence("UnitPrice", SequenceDirection.Descending));
//create a new query object, and set the root entity
var query = new Query { IsTestQuery = false, RootEntity = rootEntity };
//create a new comparison expression object
// consider **** SELECT [QueryEntity.PropertyList] FROM [QueryEntity.ObjectDefinitionFullName]
// **** WHERE [ComparisonExpression.LeftValue] [ComparisonExpression.Operator] [ComparisonExpression.RightValue]
var comparisionExpression = new ComparisonExpression
{
ExpressionType = ExpressionType.Comparison,
Operator = ComparisonOperator.Equal,
LeftValue = new ComparisonValue(ComparisonValueType.Property, "ProductPriceLists.ProductNumber"),
RightValue = new ComparisonValue(ComparisonValueType.Constant, "CAT5CBL")
};
//set the contraints in the query to the comparison expression
query.Constraints = comparisionExpression;
var queryResults = _sysConnector.ExecuteQuery(query);
//force a check of the query results
foreach (var queryResult in queryResults)
{
break;
}
//validate that results have been returned
Assert.IsNotNull(queryResults);
Assert.AreNotEqual(0, queryResults.Count());
//validate that data has been returned
Assert.AreNotEqual(0, queryResults.ElementAt(0).Properties.Count);
//Verify that the highest price is returned in the first element of the results
Assert.AreEqual("49.95", queryResults.First().Properties["UnitPrice"].ToString());
//Verify that the lowest price is returned in the last element of the results
Assert.AreEqual("0.7", queryResults.Last().Properties["UnitPrice"].ToString());
}
public void DeleteBulkRowsValidTest()
{
//create a new data entities
DataEntity micronesia = new DataEntity("Picklists");
DataEntity samoa = new DataEntity("Picklists");
DataEntity doesNotExist = new DataEntity("Picklists");
DataEntity alberta = new DataEntity("Picklists");
//add the data entities to the input list
var input = new List<DataEntity> {micronesia, samoa, doesNotExist, alberta};
//create a new operation input and set the name of the operation
OperationInput operationInput = new OperationInput("Delete");
//*** this allows multiple rows to be processed with one query
operationInput.AllowMultipleObject = false;
//set the input property
operationInput.Input = input.ToArray();
//create the comparison experssion for selecting the micronesia data entitiy
ComparisonExpression micronesiaExpression = new ComparisonExpression();
micronesiaExpression.ExpressionType = ExpressionType.Comparison;
micronesiaExpression.Operator = ComparisonOperator.Equal;
micronesiaExpression.LeftValue = new ComparisonValue
{ ValueType = ComparisonValueType.Property, Value = "Description" };
micronesiaExpression.RightValue = new ComparisonValue
{ ValueType = ComparisonValueType.Constant, Value = "Federated States of Micronesia" };
//create the comparison experssion for selecting the samoa data entitiy
ComparisonExpression samoaExpression = new ComparisonExpression();
samoaExpression.ExpressionType = ExpressionType.Comparison;
samoaExpression.Operator = ComparisonOperator.Equal;
samoaExpression.LeftValue = new ComparisonValue
{ ValueType = ComparisonValueType.Property, Value = "Description" };
samoaExpression.RightValue = new ComparisonValue
{ ValueType = ComparisonValueType.Constant, Value = "American Samoa" };
//create the comparison experssion for selecting the doesNotExist data entitiy
ComparisonExpression doesNotExistExpression = new ComparisonExpression();
doesNotExistExpression.ExpressionType = ExpressionType.Comparison;
doesNotExistExpression.Operator = ComparisonOperator.Equal;
doesNotExistExpression.LeftValue = new ComparisonValue
{ ValueType = ComparisonValueType.Property, Value = "Description" };
//since this value does not exist in the table it will result in an error
doesNotExistExpression.RightValue = new ComparisonValue
{ ValueType = ComparisonValueType.Constant, Value = "Does Not Exist" };
//create the comparison experssion for selecting the alberta data entitiy
ComparisonExpression albertaExpression = new ComparisonExpression();
albertaExpression.ExpressionType = ExpressionType.Comparison;
albertaExpression.Operator = ComparisonOperator.Equal;
albertaExpression.LeftValue = new ComparisonValue
{ ValueType = ComparisonValueType.Property, Value = "Description" };
albertaExpression.RightValue = new ComparisonValue
{ ValueType = ComparisonValueType.Constant, Value = "Alberta" };
// Create a list to hold the expressions.
// Notice the expressions will be in the same order as the data entities.
// The number of expressions will always be the same as the number of data entities.
var expressions = new List<Expression>();
expressions.Add(micronesiaExpression);
expressions.Add(samoaExpression);
expressions.Add(doesNotExistExpression);
expressions.Add(albertaExpression);
operationInput.LookupCondition = expressions.ToArray();
//execute the operation from the connector
OperationResult operationResult = _sysConnector.ExecuteOperation(operationInput);
// *** Validate the results of the operation, note the number of results
// *** must be equal to the number of inputs and must also share the same index.
//validate the micronesion delete operation
Assert.IsTrue(operationResult.Success[0]);
Assert.AreEqual(1, operationResult.ObjectsAffected[0]);
//validate the samoa delete operation
Assert.IsTrue(operationResult.Success[1]);
Assert.AreEqual(1, operationResult.ObjectsAffected[1]);
//validate the does not exist delete operation
Assert.IsTrue(operationResult.Success[2]);
Assert.AreEqual(0, operationResult.ObjectsAffected[2]);
Assert.IsNotNull(operationResult.ErrorInfo[2].Description);
//validate the micronesion delete operaiton
Assert.IsTrue(operationResult.Success[3]);
Assert.AreEqual(1, operationResult.ObjectsAffected[3]);
}
public void DeleteTooManyRowsInvalidTest()
{
//create a new data entity
DataEntity entity = new DataEntity();
//create a new operation input with a new entity array for the input property
OperationInput operationInput = new OperationInput { Input = new DataEntity[1] };
//*** this does not allow multiple rows to be processed with one query
// Note: this is the Default value
operationInput.AllowMultipleObject = false;
//set the first item in the input property
operationInput.Input[0] = entity;
operationInput.Input[0].ObjectDefinitionFullName = "PickLists";
//set the name of the operation
operationInput.Name = "Delete";
//create the comparison experssion for selecting the records to delete
ComparisonExpression comparisonExpression = new ComparisonExpression();
comparisonExpression.ExpressionType = ExpressionType.Comparison;
comparisonExpression.Operator = ComparisonOperator.Equal;
comparisonExpression.LeftValue = new ComparisonValue { ValueType = ComparisonValueType.Property, Value = "Description" };
comparisonExpression.RightValue = new ComparisonValue { ValueType = ComparisonValueType.Constant, Value = null };
operationInput.LookupCondition[0] = comparisonExpression;
//execute the operation from the connector
OperationResult operationResult = _sysConnector.ExecuteOperation(operationInput);
//validate that the operation was a success
Assert.IsFalse(operationResult.Success[0]);
}
public void UpdateMultipleRowsWithComparisonValidTest()
{
//create a new data entity
DataEntity entity = new DataEntity();
//create a new operation input with a new entity array for the input property
OperationInput operationInput = new OperationInput { Input = new DataEntity[1] };
//*** this allows multiple rows to be processed with one query
operationInput.AllowMultipleObject = true;
//set the first item in the input property
entity.ObjectDefinitionFullName = "Addresses";
entity.Properties.Add("Country", "USA");
operationInput.Input[0] = entity;
//set the name of the operation
operationInput.Name = "update";
//create the right comparison experssion for selecting the records to update
ComparisonExpression leftComparisonExpression = new ComparisonExpression();
leftComparisonExpression.ExpressionType = ExpressionType.Comparison;
leftComparisonExpression.Operator = ComparisonOperator.IsNull;
leftComparisonExpression.LeftValue = new ComparisonValue { ValueType = ComparisonValueType.Property, Value = "Phone" };
leftComparisonExpression.RightValue = null;
//create the left comparison experssion for selecting the records to update
ComparisonExpression rightComparisonExpression = new ComparisonExpression();
rightComparisonExpression.ExpressionType = ExpressionType.Comparison;
rightComparisonExpression.Operator = ComparisonOperator.IsNull;
rightComparisonExpression.LeftValue = new ComparisonValue { ValueType = ComparisonValueType.Property, Value = "AddressLine2" };
rightComparisonExpression.RightValue = null;
//create a logical expression which will combine the left and right comparison expressions using an AND operator
LogicalExpression logicalExpression = new LogicalExpression();
logicalExpression.ExpressionType = ExpressionType.Logical;
logicalExpression.LeftExpression = leftComparisonExpression;
logicalExpression.RightExpression = rightComparisonExpression;
logicalExpression.Operator = LogicalOperator.And;
//set the logical expression as the parent of the right and left comparison expressions
leftComparisonExpression.ParentExpression = logicalExpression;
rightComparisonExpression.ParentExpression = logicalExpression;
operationInput.LookupCondition[0] = logicalExpression;
//execute the operation from the connector
OperationResult operationResult = _sysConnector.ExecuteOperation(operationInput);
//validate that the operation was a success
Assert.IsTrue(operationResult.Success[0]);
//Validate the amount of rows that have been updated
//NOTE: this will only work with a clean ScribeSampleRSSource database
Assert.AreEqual(10, operationResult.ObjectsAffected[0]);
}
public void DeleteValidTest()
{
//create a new data entity
DataEntity entity = new DataEntity();
//create a new operation input with a new entity array for the input property
OperationInput operationInput = new OperationInput { Input = new DataEntity[1] };
//set the first item in the input property
operationInput.Input[0] = entity;
operationInput.Input[0].ObjectDefinitionFullName = "Picklists";
//set the name of the operation
operationInput.Name = "Delete";
//create the comparison experssion for selecting the records to delete
ComparisonExpression comparisonExpression = new ComparisonExpression();
comparisonExpression.ExpressionType = ExpressionType.Comparison;
comparisonExpression.Operator = ComparisonOperator.Equal;
comparisonExpression.LeftValue = new ComparisonValue { ValueType = ComparisonValueType.Property, Value = "Code" };
comparisonExpression.RightValue = new ComparisonValue { ValueType = ComparisonValueType.Constant, Value = "NH" };
operationInput.LookupCondition[0] = comparisonExpression;
//execute the operation from the connector
OperationResult operationResult = _sysConnector.ExecuteOperation(operationInput);
//validate that the operation was a success
Assert.IsTrue(operationResult.Success[0]);
//Validate that only one row of data has been deleted
//NOTE: this will only work with a clean ScribeSampleRSSource database
Assert.AreEqual(1, operationResult.ObjectsAffected[0]);
}
public void SimpleAndFilterValidTest()
{
string objectName = "Addresses";
//Create a basic root query entity,
//Set the name property and the object definition full name property
//Note: 'Name' property is unique and is set by the user
// 'ObjectDefinitionFullName' property will be the name of the table referenced
var rootEntity = new QueryEntity { Name = objectName, ObjectDefinitionFullName = objectName };
//Create a list of properties for the root entity, not these are column names
rootEntity.PropertyList.Add("RecordId");
rootEntity.PropertyList.Add("ContactName");
rootEntity.PropertyList.Add("ContactTitle");
rootEntity.PropertyList.Add("AddressType");
//create a new query object, and set the root entity
var query = new Query { IsTestQuery = false, RootEntity = rootEntity };
//create a new comparison expression object
// consider **** SELECT [QueryEntity.PropertyList] FROM [QueryEntity.ObjectDefinitionFullName]
// **** WHERE [ComparisonExpression.LeftValue] [ComparisonExpression.Operator] [ComparisonExpression.RightValue]
var leftComparisionExpression = new ComparisonExpression
{
ExpressionType = ExpressionType.Comparison,
Operator = ComparisonOperator.Equal,
LeftValue = new ComparisonValue(ComparisonValueType.Property, "Addresses.AddressType"),
RightValue = new ComparisonValue(ComparisonValueType.Constant, "Main")
};
//create another comparison expression to add on the right of the AND clause
var rightComparisionExpression = new ComparisonExpression
{
ExpressionType = ExpressionType.Comparison,
Operator = ComparisonOperator.Like,
LeftValue = new ComparisonValue(ComparisonValueType.Property, "Addresses.ContactTitle"),
RightValue = new ComparisonValue(ComparisonValueType.Constant, "President")
};
//create a new logical expression indicating an AND clause in filtering of the query
LogicalExpression logicalExpression = new LogicalExpression(
//Sets the opertor for the expression
LogicalOperator.And,
//add the expressions
leftComparisionExpression,
rightComparisionExpression,
//since this is the parent expressions there is no parent to indicate here so set it to null
null);
//set the contraints in the query
query.Constraints = logicalExpression;
//set the expression type for the query constraints
query.Constraints.ExpressionType = ExpressionType.Logical;
//set the parent expression for the right and left comparison expressions since they are now part of the logical expression
leftComparisionExpression.ParentExpression = query.Constraints;
rightComparisionExpression.ParentExpression = query.Constraints;
var queryResults = _sysConnector.ExecuteQuery(query);
//force a check of the query results
foreach (var queryResult in queryResults)
{
break;
}
//validate that results have been returned
Assert.IsNotNull(queryResults);
Assert.AreNotEqual(0, queryResults.Count());
//validate that data has been returned
Assert.AreNotEqual(0, queryResults.ElementAt(0).Properties.Count);
//validate that the proper values are returned in the DataEntity properties
Assert.AreEqual("President", queryResults.ElementAt(0).Properties["ContactTitle"].ToString());
Assert.AreEqual("Main", queryResults.ElementAt(0).Properties["AddressType"].ToString());
}
public void UpdateBulkRowsValidTest()
{
//create a new method input and use the appropriate operation name
OperationInput operationInput = new OperationInput { Name = "Update" };
var inputs = new List<DataEntity>();
var lookupConditions = new List<Expression>();
// Each update will consist of one DataEntity object and one LookupCondition object.
// The reletated objects have the same indexes in their corresponding arrays.
var retail = new DataEntity("Picklists");
//add the row data to the input
retail.Properties = new EntityProperties { { "Description", DateTime.UtcNow } };
//Generate the comparison expression for retail, this will define the where clause portion of the query.
var retailLookupCondition = new ComparisonExpression( ComparisonOperator.Equal,
new ComparisonValue(ComparisonValueType.Property, "Code"),
new ComparisonValue(ComparisonValueType.Constant, "Retail"),
null);
inputs.Add(retail);
lookupConditions.Add(retailLookupCondition);
var priceLists = new DataEntity("Picklists");
priceLists.Properties = new EntityProperties { { "Code", "Wholesale" } };
// Note: This record will return success but with not rows processed
// because 'PriceList' does not exist in the [Code] column
var priceListsLookupCondition = new ComparisonExpression(
ComparisonOperator.Equal,
new ComparisonValue(ComparisonValueType.Property, "Code"),
new ComparisonValue(ComparisonValueType.Constant, "PriceList"),
null);
inputs.Add(priceLists);
lookupConditions.Add(priceListsLookupCondition);
var finishGood = new DataEntity("Picklists");
// Note: This record will fail because null is not a valid value for PickListName
finishGood.Properties = new EntityProperties {{"PickListName", null}};
var finishGoodLookupCondition = new ComparisonExpression(
ComparisonOperator.Equal,
new ComparisonValue(ComparisonValueType.Property, "Code"),
new ComparisonValue(ComparisonValueType.Constant, "FinishGood"),
null);
inputs.Add(finishGood);
lookupConditions.Add(finishGoodLookupCondition);
var invoiceType = new DataEntity("Picklists");
invoiceType.Properties = new EntityProperties
{ {"Description", DateTime.UtcNow } };
var invoiceLookupCondition = new ComparisonExpression(
ComparisonOperator.Equal,
new ComparisonValue(ComparisonValueType.Property, "Code"),
new ComparisonValue(ComparisonValueType.Constant, "Invoice"),
null);
// Add the data entity and lookup condition.
// Note: This is the same order the results MUST be returned.
inputs.Add(invoiceType);
lookupConditions.Add(invoiceLookupCondition);
// Input and LookupCondition will be received by the connector as an array.
operationInput.Input = inputs.ToArray();
operationInput.LookupCondition = lookupConditions.ToArray();
//execute the selected operation
OperationResult operationResult = _sysConnector.ExecuteOperation(operationInput);
//verify the result is not a success
Assert.IsTrue(operationResult.Success[0]);
//validate that only 1 record was processed
Assert.AreEqual(1, operationResult.ObjectsAffected[0]);
//verify the result is not a success
Assert.IsTrue(operationResult.Success[1]);
//validate that no records were processed
Assert.AreEqual(0, operationResult.ObjectsAffected[1]);
//verify that the second result was not a success
Assert.IsFalse(operationResult.Success[2]);
//validate that no rows were processed
Assert.AreEqual(0, operationResult.ObjectsAffected[2]);
//verify that the final update was a success
Assert.IsTrue(operationResult.Success[3]);
//validate that only 1 record was processed
Assert.AreEqual(1, operationResult.ObjectsAffected[3]);
}
public void SimpleLikeFilterValidTest()
{
string objectName = "Addresses";
//create a new comparison expression object
// consider **** SELECT [QueryEntity.PropertyList] FROM [QueryEntity.ObjectDefinitionFullName]
// **** WHERE [ComparisonExpression.LeftValue] [ComparisonExpression.Operator] [ComparisonExpression.RightValue]
var comparisionExpression = new ComparisonExpression
{
ExpressionType = ExpressionType.Comparison,
Operator = ComparisonOperator.Like,
LeftValue = new ComparisonValue(ComparisonValueType.Property, "Addresses.ContactName"),
RightValue = new ComparisonValue(ComparisonValueType.Constant, "Mr.%")
};
//Create a basic root query entity,
//Set the name property and the object definition full name property
//Note: 'Name' property is unique and is set by the user
// 'ObjectDefinitionFullName' property will be the name of the table referenced
var rootEntity = new QueryEntity { Name = objectName, ObjectDefinitionFullName = objectName };
//Create a list of properties for the root entity, not these are column names
rootEntity.PropertyList.Add("RecordId");
rootEntity.PropertyList.Add("ContactName");
rootEntity.PropertyList.Add("Phone");
rootEntity.PropertyList.Add("LocationName");
//create a new query object
Query query = new Query();
//indicate whether or not this is a test query
query.IsTestQuery = true;
//set the root entity
query.RootEntity = rootEntity;
//set the constraints for the query
query.Constraints = comparisionExpression;
var queryResults = _sysConnector.ExecuteQuery(query);
//force a check of the query results
foreach (var queryResult in queryResults)
{
break;
}
//validate that results have been returned
Assert.IsNotNull(queryResults);
Assert.AreNotEqual(0, queryResults.Count());
//validate that data has been returned
Assert.AreNotEqual(0, queryResults.ElementAt(0).Properties.Count);
//validate that the proper values are returned in the DataEntity properties
Assert.IsTrue(queryResults.ElementAt(0).Properties["ContactName"].ToString().Contains("Mr."));
}
/// <summary>
/// Flips the comparison operator if the query operator is null
/// </summary>
/// <param name="comparisonExpression"></param>
private static void ParseNullOperators(ComparisonExpression comparisonExpression)
{
if (comparisonExpression.RightValue == null ||
comparisonExpression.RightValue.Value == null)
{
switch (comparisonExpression.Operator)
{
case ComparisonOperator.Equal:
comparisonExpression.Operator = ComparisonOperator.IsNull;
break;
case ComparisonOperator.NotEqual:
comparisonExpression.Operator = ComparisonOperator.IsNotNull;
break;
case ComparisonOperator.IsNotNull:
case ComparisonOperator.IsNull:
break;
default:
throw new NotSupportedException("This operation is not supported");
}
}
}
protected override void WriteComparison(ComparisonExpression exp)
{
BeginSpan("flat-condition");
base.WriteComparison(exp);
EndSpan();
}
/// <summary>
/// Creates a query with a constraint and ordering to filter our result set
/// </summary>
/// <returns></returns>
private Query CreateFilterQuery()
{
var query = new Query();
var constraint = new ComparisonExpression
{
ExpressionType = ExpressionType.Comparison,
LeftValue = new ComparisonValue { Value = "WebinarKey", ValueType = ComparisonValueType.Property },
RightValue = new ComparisonValue { Value = "1235813", ValueType = ComparisonValueType.Constant }
};
var sequence = new Sequence
{
PropertyName = "FirstName",
Direction = SequenceDirection.Descending
};
query.Constraints = constraint;
query.RootEntity.ObjectDefinitionFullName = "Registrant";
query.RootEntity.SequenceList = new List<Sequence>();
query.RootEntity.SequenceList.Add(sequence);
return query;
}
public void DeleteNoRowsFoundInValidTest()
{
//create a new data entity
DataEntity entity = new DataEntity();
//create a new operation input with a new entity array for the input property
OperationInput operationInput = new OperationInput { Input = new DataEntity[1] };
//set the first item in the input property
operationInput.Input[0] = entity;
operationInput.Input[0].ObjectDefinitionFullName = "Products";
//set the name of the operation
operationInput.Name = "Delete";
//create the comparison experssion for selecting the records to delete
ComparisonExpression comparisonExpression = new ComparisonExpression();
comparisonExpression.ExpressionType = ExpressionType.Comparison;
comparisonExpression.Operator = ComparisonOperator.Greater;
comparisonExpression.LeftValue = new ComparisonValue { ValueType = ComparisonValueType.Property, Value = "Products.ModifiedOn" };
comparisonExpression.RightValue = new ComparisonValue { ValueType = ComparisonValueType.Constant, Value = DateTime.UtcNow };
operationInput.LookupCondition[0] = comparisonExpression;
//execute the operation from the connector
OperationResult operationResult = _sysConnector.ExecuteOperation(operationInput);
//validate that the operation was a success
Assert.IsTrue(operationResult.Success[0]);
//validate that no rows have been found
Assert.AreEqual(0, operationResult.ObjectsAffected[0]);
//validate the proper error code has been returned
//Note: this error code can be found in the connector in ErrorCodes.cs
Assert.AreEqual(17, operationResult.ErrorInfo[0].Number);
}
public void DeleteManyRowsValidTest()
{
//create a new data entity
DataEntity entity = new DataEntity();
//create a new operation input with a new entity array for the input property
OperationInput operationInput = new OperationInput { Input = new DataEntity[1] };
//*** this allows multiple rows to be processed with one query
operationInput.AllowMultipleObject = true;
//set the first item in the input property
operationInput.Input[0] = entity;
operationInput.Input[0].ObjectDefinitionFullName = "Picklists";
//set the name of the operation
operationInput.Name = "Delete";
//create the comparison experssion for selecting the records to delete
ComparisonExpression comparisonExpression = new ComparisonExpression();
comparisonExpression.ExpressionType = ExpressionType.Comparison;
comparisonExpression.Operator = ComparisonOperator.Like;
comparisonExpression.LeftValue = new ComparisonValue
{
ValueType = ComparisonValueType.Property,
Value = "Description"
};
comparisonExpression.RightValue = new ComparisonValue { ValueType = ComparisonValueType.Constant, Value = "%east%" };
operationInput.LookupCondition[0] = comparisonExpression;
//execute the operation from the connector
OperationResult operationResult = _sysConnector.ExecuteOperation(operationInput);
//validate that the operation was a success
Assert.IsTrue(operationResult.Success[0]);
//Validate the amount of rows that have been delete
//NOTE: this will only work with a clean ScribeSampleRSSource database
Assert.AreEqual(2, operationResult.ObjectsAffected[0]);
}
public void QueryMultipleFilterWithOrderByValidTest()
{
string objectName = "ProductPriceLists";
//Create a basic root query entity,
//Set the name property and the object definition full name property
//Note: 'Name' property is unique and is set by the user
// 'ObjectDefinitionFullName' property will be the name of the table referenced
var rootEntity = new QueryEntity { Name = objectName, ObjectDefinitionFullName = objectName };
//Create a list of properties for the root entity, not these are column names
rootEntity.PropertyList.Add("RecordId");
rootEntity.PropertyList.Add("ProductNumber");
rootEntity.PropertyList.Add("UnitPrice");
rootEntity.PropertyList.Add("BaseUoMQuantity");
//set the sequence direction and field to order by
rootEntity.SequenceList.Add(new Sequence("UnitPrice", SequenceDirection.Descending));
//create a new comparison expression object
// consider **** SELECT [QueryEntity.PropertyList] FROM [QueryEntity.ObjectDefinitionFullName]
// **** WHERE [ComparisonExpression.LeftValue] [ComparisonExpression.Operator] [ComparisonExpression.RightValue]
var leftComparisionExpression = new ComparisonExpression
{
ExpressionType = ExpressionType.Comparison,
Operator = ComparisonOperator.Equal,
LeftValue = new ComparisonValue(ComparisonValueType.Property, "ProductPriceLists.ProductNumber"),
RightValue = new ComparisonValue(ComparisonValueType.Constant, "CONSULT")
};
//create another comparison expression to add on the right of the AND clause
var rightComparisionExpression = new ComparisonExpression
{
ExpressionType = ExpressionType.Comparison,
Operator = ComparisonOperator.Less,
LeftValue = new ComparisonValue(ComparisonValueType.Property, "ProductPriceLists.BaseUoMQuantity"),
RightValue = new ComparisonValue(ComparisonValueType.Constant, 50)
};
//create a new query object, and set the root entity
var query = new Query { IsTestQuery = false, RootEntity = rootEntity };
//create a new logical expression indicating an AND clause in filtering of the query
LogicalExpression logicalExpression = new LogicalExpression(
//Sets the opertor for the expression
LogicalOperator.And,
//add the expressions
leftComparisionExpression,
rightComparisionExpression,
//since this is the parent expressions there is no parent to indicate here so set it to null
null);
//set the contraints in the query
query.Constraints = logicalExpression;
//set the expression type for the query constraints
query.Constraints.ExpressionType = ExpressionType.Logical;
//set the parent expression for the right and left comparison expressions since they are now part of the logical expression
leftComparisionExpression.ParentExpression = query.Constraints;
rightComparisionExpression.ParentExpression = query.Constraints;
var queryResults = _sysConnector.ExecuteQuery(query);
//force a check of the query results
foreach (var queryResult in queryResults)
{
break;
}
//validate that results have been returned
Assert.IsNotNull(queryResults);
Assert.AreNotEqual(0, queryResults.Count());
//validate that data has been returned
Assert.AreNotEqual(0, queryResults.ElementAt(0).Properties.Count);
//Verify the proper first an last values returned by the query
Assert.AreEqual("5500", queryResults.First().Properties["UnitPrice"].ToString());
Assert.AreEqual("150", queryResults.Last().Properties["UnitPrice"].ToString());
}
/// <summary>
/// Parse through the expression to convert it to a query
/// </summary>
/// <param name="expression">Comparison Expression from OperationInpit.LookupCondition</param>
/// <returns>return the converted expression</returns>
private string ParseComparisionExpression(ComparisonExpression expression)
{
string comparisonString = string.Empty;
//parse the expression and throw an exception if it is unsupported
switch (expression.Operator)
{
case ComparisonOperator.Equal:
comparisonString = "=";
break;
case ComparisonOperator.Less:
comparisonString = "<";
break;
case ComparisonOperator.LessOrEqual:
comparisonString = "<=";
break;
default:
throw new NotSupportedException(string.Format("Operation Not Supported : {0}", expression.Operator));
}
//Get the last sync date from the expression right value
DateTime lastSyncDate = Convert.ToDateTime(expression.RightValue.Value);
//return the converted expression
return string.Format("WHERE ModifiedOn {0} '{1}'", comparisonString, lastSyncDate.ToString("u").TrimEnd('Z'));
}
/// <summary>
/// Check if the expression is searing for a null value or is a null object and change the operator to handle this appropriatly
/// </summary>
/// <param name="comparisonExpression"></param>
private void ParseNullOperators(ComparisonExpression comparisonExpression)
{
//check for any form of a null right expression
if (comparisonExpression.RightValue == null ||
comparisonExpression.RightValue.Value == null)
{
//set the appropriate operator
switch (comparisonExpression.Operator)
{
case ComparisonOperator.Equal:
comparisonExpression.Operator = ComparisonOperator.IsNull;
break;
case ComparisonOperator.NotEqual:
comparisonExpression.Operator = ComparisonOperator.IsNotNull;
break;
case ComparisonOperator.IsNull:
case ComparisonOperator.IsNotNull:
break;
default:
throw new NotSupportedException(string.Format(ErrorCodes.NullOperatorNotValid.Description, comparisonExpression.Operator));
}
}
}
public void DeleteNoRowsFoundInValidTest()
{
//create a new data entity
DataEntity entity = new DataEntity();
//create a new operation input with a new entity array for the input property
OperationInput operationInput = new OperationInput { Input = new DataEntity[1] };
//set the first item in the input property
operationInput.Input[0] = entity;
operationInput.Input[0].ObjectDefinitionFullName = "ScribeChangeHistory";
//set the name of the operation
operationInput.Name = "Delete";
//create the comparison experssion for selecting the records to delete
ComparisonExpression comparisonExpression = new ComparisonExpression();
comparisonExpression.ExpressionType = ExpressionType.Comparison;
comparisonExpression.Operator = ComparisonOperator.Less;
comparisonExpression.LeftValue = new ComparisonValue { ValueType = ComparisonValueType.Constant, Value = "ModifiedOn" };
comparisonExpression.RightValue = new ComparisonValue { ValueType = ComparisonValueType.Variable, Value = DateTime.MinValue };
operationInput.LookupCondition[0] = comparisonExpression;
//execute the operation from the connector
OperationResult operationResult = _rsTargetConnector.ExecuteOperation(operationInput);
//validate that the operation was not a success
Assert.IsFalse(operationResult.Success[0]);
//validate that no rows have been found
Assert.AreEqual(0, operationResult.ObjectsAffected[0]);
}