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