static bool ContainsTable(ISqlTableSource table, IQueryElement sql)
{
return(null != QueryVisitor.Find(sql, e =>
e == table ||
e.ElementType == QueryElementType.SqlField && table == ((SqlField)e).Table ||
e.ElementType == QueryElementType.Column && table == ((SelectQuery.Column)e).Parent));
}
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);
}
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);
});
}
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);
});