public bool TryMatch(Expression expression, MainQueryParts queryParts, Action <Expression> visitExpression)
{
var sq = expression as SubQueryExpression;
return(sq != null &&
sq.QueryModel.ResultOperators.Count == 1 &&
sq.QueryModel.ResultOperators[0] is SumResultOperator &&
CheckShortCircuitSum(sq, queryParts, visitExpression));
}
public static void ProcessExpression(Expression linqExpression, MainQueryParts query, QueryModel queryModel)
{
var visitor =
new SelectGeneratorExpressionTreeVisitor(
query,
queryModel.ResultOperators.Any(it => it is AllResultOperator));
visitor.VisitExpression(linqExpression);
}
public bool TryMatch(Expression expression, MainQueryParts queryParts, Action <Expression> visitExpression, QueryContext context)
{
var sq = expression as SubQueryExpression;
return(sq != null &&
sq.QueryModel.ResultOperators.Count == 1 &&
(sq.QueryModel.ResultOperators[0] is CountResultOperator || sq.QueryModel.ResultOperators[0] is LongCountResultOperator) &&
CheckShortCircuitCount(sq, queryParts, visitExpression));
}
public SqlCommandData(MainQueryParts query)
{
this.Query = query;
Statement = query.BuildSqlString();
var mainIndex = query.Selects.FindIndex(it => it.QuerySource == query.MainFrom);
if (mainIndex > 0)
{
var main = query.Selects[mainIndex];
query.Selects.RemoveAt(mainIndex);
query.Selects.Insert(0, main);
}
}
private static Func <string, T> ProjectExpression <T>(SubQueryExpression sqe, QueryParts parts)
{
var mqp =
new MainQueryParts(
parts.Locator,
parts.ConverterFactory,
parts.Simplifications,
parts.ExpressionMatchers,
parts.MemberMatchers,
parts.ProjectionMatchers);
var subquery = SubqueryGeneratorQueryModelVisitor.ParseSubquery(sqe.QueryModel, mqp, parts.ContextName, parts.Context.Select());
var newExpression = sqe.QueryModel.SelectClause.Selector as NewExpression;
if (newExpression != null)
{
return(ProjectNew <T>(newExpression, parts, subquery));
}
return(ProjectMapping <T>(sqe, parts, subquery));
}
private static bool CheckShortCircuitCount(SubQueryExpression expression, MainQueryParts queryParts, Action <Expression> visitExpression)
{
var subquery = SubqueryGeneratorQueryModelVisitor.ParseSubquery(expression.QueryModel, queryParts, true);
if (subquery.ShouldQueryInMemory)
{
throw new NotImplementedException("Unsupported subquery. Please provide more info about query.");
//return false;
}
var cnt = queryParts.CurrentSelectIndex;
var mq = subquery.MainFrom.FromExpression as QuerySourceReferenceExpression;
if (mq != null && subquery.Joins.Count == 0 && subquery.AdditionalJoins.Count == 0 && subquery.Conditions.Count == 0)
{
if (mq.ReferencedQuerySource.ItemType.IsGrouping())
{
queryParts.AddSelectPart(
expression.QueryModel.MainFromClause,
"ARRAY_UPPER(\"{0}\".\"Values\", 1) AS \"_count_helper_{1}\"".With(mq.ReferencedQuerySource.ItemName, cnt),
"_count_helper_" + cnt,
expression.QueryModel.ResultTypeOverride,
(_, dr) => dr.IsDBNull(cnt) ? 0 : Convert.ChangeType(dr.GetValue(cnt), expression.QueryModel.ResultTypeOverride));
return(true);
}
}
var sql = subquery.BuildSqlString(false);
queryParts.AddSelectPart(
expression.QueryModel.MainFromClause,
@"(SELECT COUNT(""{1}"") FROM ({2}) ""{1}"") AS ""{0}"" ".With(
"_subquery_" + cnt,
expression.QueryModel.MainFromClause.ItemName,
sql),
"_count_" + cnt,
expression.QueryModel.ResultTypeOverride,
(_, dr) => dr.IsDBNull(cnt) ? 0 : Convert.ChangeType(dr.GetValue(cnt), expression.QueryModel.ResultTypeOverride));
return(true);
}
private static bool CheckShortCircuitSum(SubQueryExpression expression, MainQueryParts queryParts, Action <Expression> visitExpression)
{
var subquery = SubqueryGeneratorQueryModelVisitor.ParseSubquery(expression.QueryModel, queryParts, true);
if (subquery.ShouldQueryInMemory)
{
throw new NotImplementedException("Unsupported subquery. Please provide more info about query.");
//return false;
}
var sql = subquery.BuildSqlString(false);
var cnt = queryParts.CurrentSelectIndex;
var selector = expression.QueryModel.SelectClause.Selector as MemberExpression;
if (selector == null)
{
return(false);
}
var type = expression.QueryModel.ResultTypeOverride;
if (type.IsNullable())
{
type = type.GetGenericArguments()[0];
}
queryParts.AddSelectPart(
expression.QueryModel.MainFromClause,
@"(SELECT SUM((""{1}"").""{3}"") FROM ({2}) ""{1}"") AS ""{0}""".With(
"_subquery_" + cnt,
expression.QueryModel.MainFromClause.ItemName,
sql,
selector.Member.Name),
"_sum_" + cnt,
expression.QueryModel.ResultTypeOverride,
(_, __, dr) => dr.IsDBNull(cnt) ? 0 : Convert.ChangeType(dr.GetValue(cnt), type));
return(true);
}
public SqlGeneratorQueryModelVisitor(MainQueryParts queryParts)
{
Contract.Requires(queryParts != null);
this.QueryParts = queryParts;
}
public DataTable Analyze(
IEnumerable <string> dimensions,
IEnumerable <string> facts,
IEnumerable <KeyValuePair <string, bool> > order,
ISpecification <TSource> filter,
int?limit,
int?offset)
{
var usedDimensions = new List <string>();
var usedFacts = new List <string>();
var customOrder = new List <KeyValuePair <string, bool> >();
if (dimensions != null)
{
usedDimensions.AddRange(dimensions);
}
if (facts != null)
{
usedFacts.AddRange(facts);
}
if (order != null)
{
foreach (var o in order)
{
if (o.Key != null)
{
customOrder.Add(new KeyValuePair <string, bool>(o.Key, o.Value));
}
}
}
ValidateInput(usedDimensions, usedFacts, customOrder.Select(it => it.Key));
var sb = new StringBuilder();
var alias = filter != null?filter.IsSatisfied.Parameters.First().Name : "it";
if (offset != null)
{
sb.Append("SELECT ");
sb.Append(string.Join(", ", usedDimensions.UnionAll(usedFacts).Select(it => "\"" + it + "\"")));
sb.AppendLine(" FROM (");
}
if (limit != null || offset != null)
{
sb.Append("SELECT /*+ FIRST_ROWS(n) */ ");
sb.Append(string.Join(", ", usedDimensions.UnionAll(usedFacts).Select(it => "\"" + it + "\"")));
if (offset != null)
{
sb.Append(", RowNum rn$");
}
sb.AppendLine(" FROM (");
}
sb.Append("SELECT ");
foreach (var d in usedDimensions)
{
sb.AppendFormat("{0} AS \"{1}\", ", CubeDimensions[d](alias), d);
}
foreach (var f in usedFacts)
{
sb.AppendFormat("{0} AS \"{1}\", ", CubeFacts[f](alias), f);
}
sb.Length -= 2;
sb.AppendLine();
sb.AppendFormat("FROM {0} \"{1}\"", Source, alias);
sb.AppendLine();
var command = DatabaseQuery.NewCommand();
if (filter != null)
{
var cf = Locator.Resolve <IOracleConverterFactory>();
var ep = Locator.Resolve <IExtensibilityProvider>();
var qp =
new MainQueryParts(
Locator,
cf,
ep.ResolvePlugins <IQuerySimplification>(),
ep.ResolvePlugins <IExpressionMatcher>(),
ep.ResolvePlugins <IMemberMatcher>(),
new IProjectionMatcher[0]);
var linq = new Queryable <TSource>(new QueryExecutor(DatabaseQuery, Locator, cf, ep)).Filter(filter);
var parser = QueryParser.CreateDefault();
var model = parser.GetParsedQuery(linq.Expression);
if (model.BodyClauses.Count > 0)
{
sb.AppendLine("WHERE");
for (int i = 0; i < model.BodyClauses.Count; i++)
{
var wc = model.BodyClauses[i] as WhereClause;
sb.Append(" ");
if (i > 0)
{
sb.Append("AND ");
}
sb.Append(qp.GetSqlExpression(wc.Predicate));
}
}
foreach (var p in qp.Parameters.Parameters)
{
command.Parameters.Add(p);
}
}
sb.AppendLine();
if (usedDimensions.Count > 0)
{
sb.Append("GROUP BY ");
sb.AppendLine(string.Join(", ", usedDimensions.Select(it => CubeDimensions[it](alias))));
}
if (customOrder.Count > 0)
{
sb.Append("ORDER BY ");
sb.AppendLine(string.Join(", ", customOrder.Select(it => "\"{0}\" {1}".With(it.Key, it.Value ? string.Empty : "DESC"))));
}
if (limit != null || offset != null)
{
sb.AppendLine(") sq$");
if (limit != null)
{
sb.Append("WHERE RowNum <= ");
sb.Append(limit.Value + (offset != null ? offset.Value : 0));
}
}
if (offset != null)
{
sb.AppendLine(") sq$");
sb.Append("WHERE sq$.rn$ > ");
sb.Append(offset.Value);
}
command.CommandText = sb.ToString();
return(DatabaseQuery.Fill(command));
}
private SelectGeneratorExpressionTreeVisitor(MainQueryParts query, bool simple)
{
this.Query = query;
this.IsSimple = simple;
}
internal string PrepareSql(
List <string> usedDimensions,
List <string> usedFacts,
IEnumerable <KeyValuePair <string, bool> > order,
ISpecification <TSource> filter,
int?limit,
int?offset)
{
var customOrder = new List <KeyValuePair <string, bool> >();
if (order != null)
{
foreach (var o in order)
{
if (o.Key != null)
{
customOrder.Add(new KeyValuePair <string, bool>(o.Key, o.Value));
}
}
}
ValidateInput(usedDimensions, usedFacts, customOrder.Select(it => it.Key));
var sb = new StringBuilder();
var alias = filter != null?filter.IsSatisfied.Parameters.First().Name : "it";
sb.Append("SELECT ROW(");
foreach (var d in usedDimensions)
{
sb.Append(CubeDimensions[d](alias));
sb.Append(',');
}
foreach (var f in usedFacts)
{
sb.Append(CubeFacts[f](alias));
sb.Append(',');
}
sb.Length--;
sb.AppendFormat(") FROM {0} \"{1}\" ", Source, alias);
if (filter != null)
{
MainQueryParts.AddFilter(Locator, DatabaseQuery, filter, sb);
}
if (usedDimensions.Count > 0)
{
sb.Append(" GROUP BY ");
foreach (var d in usedDimensions)
{
sb.Append(CubeDimensions[d](alias));
sb.Append(',');
}
sb.Length--;
}
if (customOrder.Count > 0)
{
sb.Append(" ORDER BY ");
foreach (var kv in customOrder)
{
if (CubeDimensions.ContainsKey(kv.Key))
{
sb.Append(CubeDimensions[kv.Key](alias));
}
else if (CubeFacts.ContainsKey(kv.Key))
{
sb.Append(CubeFacts[kv.Key](alias));
}
else
{
sb.AppendFormat("\"{0}\"", kv.Key);
}
if (kv.Value == false)
{
sb.Append(" DESC");
}
sb.Append(", ");
}
sb.Length -= 2;
}
if (limit != null)
{
sb.Append(" LIMIT ").Append(limit.Value);
}
if (offset != null)
{
sb.Append(" OFFSET ").Append(offset.Value);
}
return(sb.ToString());
}
