/// <summary> /// Assigns a proxy predicate to a multi-attributed expression. /// </summary> private static IProxyPredicate GetMultiAttributePredicate(ComparisonPredicate expression, ICollection <IIndex> indices, IQueryStore queryStore) { var candidatesList = new OrderedList <int, IIndex>(); foreach (var index in indices) { List <string> indexAttributes = new List <string>(); indexAttributes.Add(index.Attributes.Name); if (ContainsRange(indexAttributes, expression.AttributeNames)) { candidatesList.Add(index.KeyCount, index); } } if (candidatesList.Count > 0) { var predicate = new FilterPredicate(expression, candidatesList.FirstValues[0]); predicate.AddChildPredicate(new AllPredicate(candidatesList.FirstValues[0])); return(new ProxyPredicate(predicate, expression)); } return(new ProxyPredicate(new StorePredicate(expression, queryStore), expression)); }
/// <summary> /// Determines whether a comparison expression is reduceable or not. /// </summary> private static bool ReducableExpression(ComparisonPredicate expression) { switch (expression.Condition) { case Condition.NotEquals: return(false); case Condition.In: return(false); case Condition.NotIn: return(false); case Condition.NotBetween: return(false); case Condition.ContainsAny: return(false); case Condition.NotContainsAny: return(false); case Condition.ContainsAll: return(false); case Condition.NotContainsAll: return(false); case Condition.ArraySize: return(false); case Condition.NotArraySize: return(false); } foreach (var constant in expression.ConstantValues) { if (constant is ValueList || constant.Value is ArrayJsonValue || constant.Value is ObjectJsonValue) { return(false); } } return(true); }
public void RenderComparisonPredicate() { Column column = new Column("Column1"); StringLiteralValue stringLiteralValue = new StringLiteralValue("Value1"); ComparisonPredicate comparisonPredicate = new ComparisonPredicate(column, ComparisonPredicateType.Equals, stringLiteralValue); Assert.AreEqual("[Column1] = 'Value1'", sqlClientRenderer.Render(comparisonPredicate)); }
public void CreateHavingClause() { Column column = new Column("Column1"); StringLiteralValue stringLiteralValue = new StringLiteralValue("Value1"); ComparisonPredicate comparisonPredicate = new ComparisonPredicate(column, ComparisonPredicateType.Equals, stringLiteralValue); HavingClause havingClause = new HavingClause(comparisonPredicate); Assert.AreEqual(comparisonPredicate, havingClause.Predicate); }
public void CreateComparisonPredicate() { Column column = new Column("Column1"); StringLiteralValue stringLiteralValue = new StringLiteralValue("Value1"); ComparisonPredicate comparisonPredicate = new ComparisonPredicate(column, ComparisonPredicateType.Equals, stringLiteralValue); Assert.AreEqual(column, comparisonPredicate.LeftExpression); Assert.AreEqual(ComparisonPredicateType.Equals, comparisonPredicate.ComparisonType); Assert.AreEqual(stringLiteralValue, comparisonPredicate.RightExpression); }
private void OperatorBox_SelectionChanged(object sender, System.Windows.Controls.SelectionChangedEventArgs e) { if (operatorBox.SelectedItem is ComboBoxItem item) { if (item.Tag is ComparisonPredicate op) { this.Operator = op; } } }
private static bool ReducablePredicate(ComparisonPredicate compareExpr) { if (!compareExpr.PredicateType.Equals(PredicateType.SingleVariable) || compareExpr.Condition != Condition.Equals || compareExpr.ConstantValues[0] is ArrayJsonValue || compareExpr.ConstantValues[0] is ObjectJsonValue) { return(false); } return(true); }
public void AddHavingClauseToSelectQuery() { SelectQuery selectQuery = new SelectQuery(); Column column = new Column("Column1"); StringLiteralValue stringLiteralValue = new StringLiteralValue("Value1"); ComparisonPredicate comparisonPredicate = new ComparisonPredicate(column, ComparisonPredicateType.Equals, stringLiteralValue); HavingClause havingClause = new HavingClause(comparisonPredicate); selectQuery.Having(havingClause); Assert.AreEqual(havingClause, selectQuery.HavingClause); }
public void AddWhereClauseToSelectQuery() { SelectQuery selectQuery = new SelectQuery(); Column column = new Column("Column1"); StringLiteralValue stringLiteralValue = new StringLiteralValue("LiteralValue1"); ComparisonPredicate comparisonPredicate = new ComparisonPredicate(column, ComparisonPredicateType.Equals, stringLiteralValue); WhereClause whereClause = new WhereClause(comparisonPredicate); selectQuery.Where(whereClause); Assert.AreEqual(whereClause, selectQuery.WhereClause); }
public void RenderHavingClausesOr() { Column column1 = new Column("Column1"); Column column2 = new Column("Column2"); StringLiteralValue stringLiteralValue1 = new StringLiteralValue("Value1"); StringLiteralValue stringLiteralValue2 = new StringLiteralValue("Value2"); ComparisonPredicate comparisonPredicate1 = new ComparisonPredicate(column1, ComparisonPredicateType.Equals, stringLiteralValue1); ComparisonPredicate comparisonPredicate2 = new ComparisonPredicate(column2, ComparisonPredicateType.Equals, stringLiteralValue2); HavingClause havingClause1 = new HavingClause(comparisonPredicate1); HavingClause havingClause2 = new HavingClause(comparisonPredicate2); HavingClauses havingClauses = new HavingClauses(havingClause1, BooleanOperatorType.Or, havingClause2); Assert.AreEqual("([Column1] = 'Value1' OR [Column2] = 'Value2')", sqlClientRenderer.Render(havingClauses)); }
public void RenderWhereClausesAnd() { Column column1 = new Column("Column1"); Column column2 = new Column("Column2"); StringLiteralValue stringLiteralValue1 = new StringLiteralValue("Value1"); StringLiteralValue stringLiteralValue2 = new StringLiteralValue("Value2"); ComparisonPredicate comparisonPredicate1 = new ComparisonPredicate(column1, ComparisonPredicateType.Equals, stringLiteralValue1); ComparisonPredicate comparisonPredicate2 = new ComparisonPredicate(column2, ComparisonPredicateType.Equals, stringLiteralValue2); WhereClause whereClause1 = new WhereClause(comparisonPredicate1); WhereClause whereClause2 = new WhereClause(comparisonPredicate2); WhereClauses whereClauses = new WhereClauses(whereClause1, BooleanOperatorType.And, whereClause2); Assert.AreEqual("([Column1] = 'Value1' AND [Column2] = 'Value2')", sqlClientRenderer.Render(whereClauses)); }
public void AddHavingClauses() { Column column1 = new Column("Column1"); Column column2 = new Column("Column2"); StringLiteralValue stringLiteralValue1 = new StringLiteralValue("Value1"); StringLiteralValue stringLiteralValue2 = new StringLiteralValue("Value2"); ComparisonPredicate comparisonPredicate1 = new ComparisonPredicate(column1, ComparisonPredicateType.Equals, stringLiteralValue1); ComparisonPredicate comparisonPredicate2 = new ComparisonPredicate(column2, ComparisonPredicateType.Equals, stringLiteralValue2); HavingClause havingClause1 = new HavingClause(comparisonPredicate1); HavingClause havingClause2 = new HavingClause(comparisonPredicate2); HavingClauses havingClauses = new HavingClauses(havingClause1, BooleanOperatorType.And, havingClause2); Assert.AreEqual(havingClause1, havingClauses.LeftClause); Assert.AreEqual(BooleanOperatorType.And, havingClauses.OperatorType); Assert.AreEqual(havingClause2, havingClauses.RightClause); }
public override string Render(ComparisonPredicate comparisonPredicate) { StringBuilder text = new StringBuilder(); text.AppendFormat("{0} ", comparisonPredicate.LeftExpression.Render(this)); switch (comparisonPredicate.ComparisonType) { case ComparisonPredicateType.Equals: text.Append("= "); break; case ComparisonPredicateType.NotEquals: text.Append("<> "); break; case ComparisonPredicateType.LessThan: text.Append("< "); break; case ComparisonPredicateType.GreaterThan: text.Append("> "); break; case ComparisonPredicateType.LessOrEqualsThan: text.Append("<= "); break; case ComparisonPredicateType.GreaterOrEqualsThan: text.Append(">= "); break; } if (comparisonPredicate.RightExpression is SelectQuery) { text.AppendFormat("({0}) ", comparisonPredicate.RightExpression.Render(this)); } else { text.AppendFormat("{0} ", comparisonPredicate.RightExpression.Render(this)); } return(text.ToString().Trim()); }
public override ISqlFragment Visit(ComparisonPredicate e) { Check.NotNull(e, "e"); SqlBuilder result; switch (e.PredicateType) { case PredicateType.Equals: result = VisitComparisonExpression(" = ", e.Left, e.Right); break; case PredicateType.LessThan: result = VisitComparisonExpression(" < ", e.Left, e.Right); break; case PredicateType.LessThanOrEquals: result = VisitComparisonExpression(" <= ", e.Left, e.Right); break; case PredicateType.GreaterThan: result = VisitComparisonExpression(" > ", e.Left, e.Right); break; case PredicateType.GreaterThanOrEquals: result = VisitComparisonExpression(" >= ", e.Left, e.Right); break; case PredicateType.NotEquals: result = VisitComparisonExpression(" <> ", e.Left, e.Right); break; default: throw new InvalidOperationException(String.Empty); } _forceNonUnicode = false; return(result); }
public static JoinFromClause Join(TableFromClause leftTable, TableFromClause rightTable, ComparisonPredicate predicate) { return new JoinFromClause(leftTable, rightTable, predicate); }
public abstract string Render(ComparisonPredicate comparisonPredicate);
public JoinFromClause(TableFromClause leftTable, TableFromClause rightTable, ComparisonPredicate predicate) { LeftTable = leftTable; RightTable = rightTable; Predicate = predicate; }
/// <summary> /// Tries to assign compound indexes to expressions if applicable... /// </summary> private static List <ProxyAndPredicate> AssignCompoundIndices(ProxyAndPredicate set, IEnumerable <IIndex> indexes, IQueryStore queryStore) { var attribExprList = new OrderedList <string, ComparisonPredicate>(); var proxyPredicates = new List <ProxyAndPredicate>(); //For every expression in the set ready for assignment. foreach (var expresson in set.TreePredicates) { var expression = (ComparisonPredicate)expresson; if (expression.IsBinaryExpression) { continue; } attribExprList.Add(expression.AttributeNames[0], expression); } //Each compound index will create a new state of the expression. foreach (var index in indexes) { int matchedNumber = 0; var matchedAttribs = new List <string>(); //If keys (attributes list) does not contain the first prefix of the index //Then the index is not usable. if (attribExprList.ContainsKey(index.Attributes.Name)) { matchedAttribs.Add(index.Attributes.Name); matchedNumber += attribExprList[index.Attributes.Name].Count; } //if matched attributes are < 2 no need of compound assignment. if (matchedAttribs.Count < 2) { continue; } //Assign each of them an index and get them to the predicate, and get the one with the lowest cost. ComparisonPredicate cheapestExpression = attribExprList[matchedAttribs[0]][0]; IPredicate cheapestPredicate = cheapestExpression.AssignIndexPredicate(index, queryStore); for (int i = 1; i < attribExprList[matchedAttribs[0]].Count; i++) { ComparisonPredicate tempExpression = attribExprList[matchedAttribs[0]][i]; IPredicate tempPredicate = tempExpression.AssignIndexPredicate(index, queryStore); if (tempPredicate.Statistics[Statistic.ExpectedIO] < cheapestPredicate.Statistics[Statistic.ExpectedIO]) { cheapestExpression = tempExpression; cheapestPredicate = tempPredicate; } } //For filteration and adding rest of expressions to the index. ITreePredicate assingedExpression = null; if (!matchedNumber.Equals(2)) { AndTreePredicate assingedAnd = new AndTreePredicate(); foreach (var attribute in matchedAttribs) { foreach (var expression in attribExprList[attribute]) { if (expression != cheapestExpression) { assingedAnd.Add(expression); } } } assingedExpression = assingedAnd; } else { foreach (var expression in attribExprList[matchedAttribs[1]]) { assingedExpression = expression; } } FilterPredicate filterPredicate = new FilterPredicate(assingedExpression, index); filterPredicate.AddChildPredicate(cheapestPredicate); ProxyAndPredicate proxyAnd = (ProxyAndPredicate)set.Clone(); //Removing assigned expressions. foreach (var attribute in matchedAttribs) { foreach (var expression in attribExprList[attribute]) { proxyAnd.TreePredicates.Remove(expression); } } proxyAnd.AddChildPredicate(new ProxyPredicate(filterPredicate, null)); proxyPredicates.Add(proxyAnd); } foreach (var proxyAnd in proxyPredicates) { //Recursive call for each ProxyAndPredicate. if (proxyAnd.TreePredicates.Count > 1) { var values = AssignCompoundIndices(proxyAnd, indexes, queryStore); foreach (var value in values) { proxyPredicates.Add(value); } } } return(proxyPredicates); }
public IProxyPredicate GetProxyExecutionPredicate(IIndexProvider indexManager, IQueryStore queryStore, IEnumerable <long> rowsEnumerator) { ProxyAndPredicate proxyPredicate = new ProxyAndPredicate(); proxyPredicate.AddTreePredicates(_predciates); if (!IsTerminal) { foreach (var predicate in _predciates) { proxyPredicate.AddChildPredicate( predicate.GetProxyExecutionPredicate(indexManager, queryStore, rowsEnumerator)); } return(proxyPredicate); } OrderedList <int, IIndex> indexes = indexManager.OrderedIndexList; ArrayList sets = new ArrayList(); ProxyAndPredicate singleSet = new ProxyAndPredicate(); foreach (var value in proxyPredicate.TreePredicates) { ComparisonPredicate predicate = (ComparisonPredicate)value; if (predicate.PredicateType.Equals(PredicateType.Constant)) { //When any constant expression is false in an AND Expression it will be resulting into 0 rowIds. if (!predicate.IsTrue(null)) { return(new ProxyPredicate(new EmptyPredicate(), predicate)); } } else if (predicate.PredicateType.Equals(PredicateType.SingleVariable)) { singleSet.AddTreePredicate(predicate); } else if (predicate.PredicateType.Equals(PredicateType.MultiVariable)) { sets.Add(predicate); } else if (predicate.PredicateType.Equals(PredicateType.AllVariable)) { proxyPredicate.AddChildPredicate( predicate.GetProxyExecutionPredicate(indexManager, queryStore, rowsEnumerator)); } } if (singleSet.TreePredicates.Count > 0) { sets.Add(singleSet); } foreach (var set in sets) { if (set is ITreePredicate) { proxyPredicate.AddChildPredicate( GetMultiAttributePredicate((ComparisonPredicate)set, indexes.Values, queryStore)); } else if (set is ProxyAndPredicate) { var singleAttributeSet = (ProxyAndPredicate)set; ReduceRecuringRanges(ref singleAttributeSet); List <ProxyAndPredicate> proxyAnds = AssignCompoundIndices(singleAttributeSet, indexes.Values, queryStore); if (proxyAnds.Count == 0) { proxyAnds.Add(singleAttributeSet); } foreach (var proxyAnd in proxyAnds) { if (proxyAnd.TreePredicates.Count == 0) { continue; } AssignSingleAttributesPredicate(proxyAnd, indexes.Values, queryStore); } if (proxyAnds.Count.Equals(1)) { foreach (var predicate in proxyAnds[0].Predicates) { proxyPredicate.AddChildPredicate(predicate); proxyPredicate.TreePredicates = _predciates; } } else { //Getting the ProxyANDPredicate with the lowest cost... OrderedList <double, ProxyAndPredicate> orderedPredicates = new OrderedList <double, ProxyAndPredicate>(); foreach (var proxyAnd in proxyAnds) { orderedPredicates.Add(proxyAnd.Statistics[Statistic.ExpectedIO], proxyAnd); } foreach (var predicate in orderedPredicates.FirstValues[0].Predicates) { proxyPredicate.AddChildPredicate(predicate); proxyPredicate.TreePredicates = _predciates; } proxyPredicate.TreePredicates = _predciates; } } } return(proxyPredicate); }
public IProxyPredicate GetProxyExecutionPredicate(IIndexProvider indexManager, IQueryStore queryStore, IEnumerable <long> rowsEnumerator) { //Get reducable terminal expressions in the ORExpression... List <ComparisonPredicate> terminalPreds = new List <ComparisonPredicate>(); foreach (var predciate in _predciates) { ComparisonPredicate comparePred = predciate as ComparisonPredicate; if (comparePred != null && comparePred.PredicateType.Equals(PredicateType.SingleVariable)) { terminalPreds.Add(comparePred); } } IEnumerable <Attribute> repAttributes = new List <Attribute>(); List <ITreePredicate> compPreds = new List <ITreePredicate>(); foreach (var predciate in _predciates) { if (predciate is ComparisonPredicate) { compPreds.Add(predciate); } } if (terminalPreds.Count > 0 && compPreds.Count > 0) { repAttributes = PredicateHelper.GetRepeatedAttributes(compPreds); } foreach (var attribute in repAttributes) { Dictionary <int, ComparisonPredicate> reducablePreds = new Dictionary <int, ComparisonPredicate>(); for (int i = 0; i < _predciates.Count; i++) { if (_predciates[i] is ComparisonPredicate) { ComparisonPredicate comparePred = _predciates[i] as ComparisonPredicate; if (ReducablePredicate(comparePred) && comparePred.Attributes[0] == attribute) { reducablePreds.Add(i, comparePred); } } } if (reducablePreds.Count < 2) { continue; } ValueList values = new ValueList(); foreach (var pair in reducablePreds) { values.Add(PredicateHelper.GetConstant(pair.Value.ConstantValues[0])); } ComparisonPredicate reducedPred = new ComparisonPredicate(attribute, Condition.In, values); for (int i = 0; i < _predciates.Count; i++) { if (reducablePreds.Keys.Contains(i)) { _predciates.RemoveAt(i); } } _predciates.Add(reducedPred); } var predicate = new ProxyOrPredicate(); foreach (var predciate in _predciates) { predicate.AddExpression(predciate); predicate.AddChildPredicate(predciate.GetProxyExecutionPredicate(indexManager, queryStore, rowsEnumerator)); } return(predicate); }