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]); }