public static SqlJoinedTable FindJoin(this SelectQuery query,
Func <SqlJoinedTable, bool> match)
{
return(QueryVisitor.Find(query, e =>
{
if (e.ElementType == QueryElementType.JoinedTable)
{
if (match((SqlJoinedTable)e))
{
return true;
}
}
return false;
}) as SqlJoinedTable);
}
static bool CheckColumn(SelectQuery.Column column, ISqlExpression expr, SelectQuery query, bool optimizeValues, bool optimizeColumns)
{
if (expr is SqlField || expr is SelectQuery.Column)
{
return(false);
}
if (expr is SqlValue)
{
return(!optimizeValues && 1.Equals(((SqlValue)expr).Value));
}
if (expr is SqlBinaryExpression)
{
var e = (SqlBinaryExpression)expr;
if (e.Operation == "*" && e.Expr1 is SqlValue)
{
var value = (SqlValue)e.Expr1;
if (value.Value is int && (int)value.Value == -1)
{
return(CheckColumn(column, e.Expr2, query, optimizeValues, optimizeColumns));
}
}
}
var visitor = new QueryVisitor();
if (optimizeColumns &&
QueryVisitor.Find(expr, e => e is SelectQuery || IsAggregationFunction(e)) == null)
{
var n = 0;
var q = query.ParentSelect ?? query;
visitor.VisitAll(q, e => { if (e == column)
{
n++;
}
});
return(n > 2);
}
return(true);
}
internal void ResolveWeakJoins(List <ISqlTableSource> tables)
{
Func <SelectQuery.TableSource, bool> findTable = null; findTable = table =>
{
if (tables.Contains(table.Source))
{
return(true);
}
foreach (var join in table.Joins)
{
if (findTable(join.Table))
{
join.IsWeak = false;
return(true);
}
}
if (table.Source is SelectQuery)
{
foreach (var t in ((SelectQuery)table.Source).From.Tables)
{
if (findTable(t))
{
return(true);
}
}
}
return(false);
};
var areTablesCollected = false;
_selectQuery.ForEachTable(table =>
{
for (var i = 0; i < table.Joins.Count; i++)
{
var join = table.Joins[i];
if (join.IsWeak)
{
if (!areTablesCollected)
{
areTablesCollected = true;
Action <IQueryElement> tableCollector = expr =>
{
var field = expr as SqlField;
if (field != null && !tables.Contains(field.Table))
{
tables.Add(field.Table);
}
};
var visitor = new QueryVisitor();
visitor.VisitAll(_selectQuery.Select, tableCollector);
visitor.VisitAll(_selectQuery.Where, tableCollector);
visitor.VisitAll(_selectQuery.GroupBy, tableCollector);
visitor.VisitAll(_selectQuery.Having, tableCollector);
visitor.VisitAll(_selectQuery.OrderBy, tableCollector);
if (_selectQuery.IsInsert)
{
visitor.VisitAll(_selectQuery.Insert, tableCollector);
}
if (_selectQuery.IsUpdate)
{
visitor.VisitAll(_selectQuery.Update, tableCollector);
}
if (_selectQuery.IsDelete)
{
visitor.VisitAll(_selectQuery.Delete, tableCollector);
}
visitor.VisitAll(_selectQuery.From, expr =>
{
var tbl = expr as SqlTable;
if (tbl != null && tbl.TableArguments != null)
{
var v = new QueryVisitor();
foreach (var arg in tbl.TableArguments)
{
v.VisitAll(arg, tableCollector);
}
}
});
}
if (findTable(join.Table))
{
join.IsWeak = false;
}
else
{
table.Joins.RemoveAt(i);
i--;
}
}
}
}, new HashSet <SelectQuery>());
}
void OptimizeUnions()
{
var isAllUnion = QueryVisitor.Find(_selectQuery,
ne =>
{
var nu = ne as SelectQuery.Union;
if (nu != null && nu.IsAll == true)
{
return(true);
}
return(false);
});
var isNotAllUnion = QueryVisitor.Find(_selectQuery,
ne =>
{
var nu = ne as SelectQuery.Union;
if (nu != null && nu.IsAll == false)
{
return(true);
}
return(false);
});
if (isNotAllUnion != null && isAllUnion != null)
{
return;
}
var exprs = new Dictionary <ISqlExpression, ISqlExpression>();
new QueryVisitor().Visit(_selectQuery, e =>
{
var sql = e as SelectQuery;
if (sql == null || sql.From.Tables.Count != 1 || !sql.IsSimple || sql.IsInsert || sql.IsUpdate || sql.IsDelete)
{
return;
}
var table = sql.From.Tables[0];
if (table.Joins.Count != 0 || !(table.Source is SelectQuery))
{
return;
}
var union = (SelectQuery)table.Source;
if (!union.HasUnion)
{
return;
}
for (var i = 0; i < sql.Select.Columns.Count; i++)
{
var scol = sql.Select.Columns[i];
var ucol = union.Select.Columns[i];
if (scol.Expression != ucol)
{
return;
}
}
exprs.Add(union, sql);
for (var i = 0; i < sql.Select.Columns.Count; i++)
{
var scol = sql.Select.Columns[i];
var ucol = union.Select.Columns[i];
scol.Expression = ucol.Expression;
scol._alias = ucol._alias;
exprs.Add(ucol, scol);
}
for (var i = sql.Select.Columns.Count; i < union.Select.Columns.Count; i++)
{
sql.Select.Expr(union.Select.Columns[i].Expression);
}
sql.From.Tables.Clear();
sql.From.Tables.AddRange(union.From.Tables);
sql.Where.SearchCondition.Conditions.AddRange(union.Where.SearchCondition.Conditions);
sql.Having.SearchCondition.Conditions.AddRange(union.Having.SearchCondition.Conditions);
sql.GroupBy.Items.AddRange(union.GroupBy.Items);
sql.OrderBy.Items.AddRange(union.OrderBy.Items);
sql.Unions.InsertRange(0, union.Unions);
});
((ISqlExpressionWalkable)_selectQuery).Walk(false, expr =>
{
ISqlExpression e;
if (exprs.TryGetValue(expr, out e))
{
return(e);
}
return(expr);
});
}
public SqlStatement ProcessParameters(MappingSchema mappingSchema)
{
if (IsParameterDependent)
{
var statement = new QueryVisitor().Convert(this, e =>
{
switch (e.ElementType)
{
case QueryElementType.SqlParameter:
{
var p = (SqlParameter)e;
if (p.Value == null)
{
return(new SqlValue(null));
}
}
break;
case QueryElementType.ExprExprPredicate:
{
var ee = (SqlPredicate.ExprExpr)e;
if (ee.Operator == SqlPredicate.Operator.Equal || ee.Operator == SqlPredicate.Operator.NotEqual)
{
object value1;
object value2;
if (ee.Expr1 is SqlValue v1)
{
value1 = v1.Value;
}
else if (ee.Expr1 is SqlParameter p1)
{
value1 = p1.Value;
}
else
{
break;
}
if (ee.Expr2 is SqlValue v2)
{
value2 = v2.Value;
}
else if (ee.Expr2 is SqlParameter p2)
{
value2 = p2.Value;
}
else
{
break;
}
var value = Equals(value1, value2);
if (ee.Operator == SqlPredicate.Operator.NotEqual)
{
value = !value;
}
return(new SqlPredicate.Expr(new SqlValue(value), Precedence.Comparison));
}
}
break;
case QueryElementType.InListPredicate:
return(ConvertInListPredicate(mappingSchema, (SqlPredicate.InList)e));
}
return(null);
});
if (statement != this)
{
statement.Parameters.Clear();
new QueryVisitor().VisitAll(statement, expr =>
{
switch (expr.ElementType)
{
case QueryElementType.SqlParameter:
{
var p = (SqlParameter)expr;
if (p.IsQueryParameter)
{
statement.Parameters.Add(p);
}
break;
}
}
});
}
return(statement);
}
return(this);
}
void OptimizeUnions()
{
var isAllUnion = QueryVisitor.Find(_selectQuery,
ne => ne is SqlUnion nu && nu.IsAll);
var isNotAllUnion = QueryVisitor.Find(_selectQuery,
ne => ne is SqlUnion nu && !nu.IsAll);
if (isNotAllUnion != null && isAllUnion != null)
{
return;
}
var exprs = new Dictionary <ISqlExpression, ISqlExpression>();
new QueryVisitor().Visit(_selectQuery, e =>
{
if (!(e is SelectQuery sql) || sql.From.Tables.Count != 1 || !sql.IsSimple)
{
return;
}
var table = sql.From.Tables[0];
if (table.Joins.Count != 0 || !(table.Source is SelectQuery))
{
return;
}
var union = (SelectQuery)table.Source;
if (!union.HasUnion)
{
return;
}
for (var i = 0; i < sql.Select.Columns.Count; i++)
{
var scol = sql.Select.Columns[i];
var ucol = union.Select.Columns[i];
if (scol.Expression != ucol)
{
return;
}
}
exprs.Add(union, sql);
for (var i = 0; i < sql.Select.Columns.Count; i++)
{
var scol = sql.Select.Columns[i];
var ucol = union.Select.Columns[i];
scol.Expression = ucol.Expression;
scol.RawAlias = ucol.RawAlias;
exprs.Add(ucol, scol);
}
for (var i = sql.Select.Columns.Count; i < union.Select.Columns.Count; i++)
{
sql.Select.Expr(union.Select.Columns[i].Expression);
}
sql.From.Tables.Clear();
sql.From.Tables.AddRange(union.From.Tables);
sql.Where.SearchCondition.Conditions.AddRange(union.Where.SearchCondition.Conditions);
sql.Having.SearchCondition.Conditions.AddRange(union.Having.SearchCondition.Conditions);
sql.GroupBy.Items.AddRange(union.GroupBy.Items);
sql.OrderBy.Items.AddRange(union.OrderBy.Items);
sql.Unions.InsertRange(0, union.Unions);
});