/// <summary>
/// Builds the field expression.
/// </summary>
/// <param name="fieldExpression">The field expression.</param>
/// <param name="context"></param>
/// <returns>ResourceDataColumn.</returns>
private static ResourceDataColumn BuildFieldExpression(Model.FieldExpression fieldExpression, FromEntityContext context)
{
DatabaseType castType = TypedValueHelper.GetDatabaseType(fieldExpression.ReportExpressionResultType);
if (fieldExpression.FieldExpressionField == null)
{
EventLog.Application.WriteWarning(context.DebugInfo + "field was null");
return(null);
}
ResourceDataColumn rdc = new ResourceDataColumn
{
FieldId = fieldExpression.FieldExpressionField,
CastType = castType, ExpressionId = Guid.NewGuid(),
SourceNodeEntityId = fieldExpression.As <NodeExpression>().SourceNode.Id
};
return(rdc);
}
protected override ScalarExpression OnBuildQuery(QueryBuilderContext context)
{
// Currently this must be a literal
var field = Right as ConstantEntityNode;
if (field == null)
{
throw new ParseException("Field required.");
}
var queryNode = context.GetNode(Left);
// Start workaround for #27148
// If an aggregate operation is performed on a choice field, then we try to treat it like an entity
// then we have a scenario that can't currently be represented in structured queries.
// (Because sq aggregate operations get declared as expressions, but if it's an expression we can no longer use it as a sq node for other expressions).
// This can happen if we use the choice-field in a comparison (e.g. max(choice)>whatever) because the compiler
// will inject a field lookup to redirect to the ordering field.
// For now, detect this specific scenario, and pass the aggregate expression directly through because the query engine
// will defer to the ordering version of the SQL, which will in turn look up the enumOrder field.
// However, ideally we should also be able to do things like max(choice).someOtherRelationshipOrField
bool nodeIsAggregate = Left is AggregateNode || Left is EntityTypeCast && Left.Arguments[0] is AggregateNode;
bool fieldIsEnumOrdering = field.Instance.Id == WellKnownAliases.CurrentTenant.EnumOrder;
if (nodeIsAggregate && fieldIsEnumOrdering)
{
return(Left.BuildQuery(context));
}
// End workaround
var result = new ResourceDataColumn
{
FieldId = field.Instance,
NodeId = queryNode.NodeId
};
return(result);
}
/// <summary>
/// This is a hacky function to convert condition which under aggregated node and operator is IsNull
/// for normal query, if the condition is under aggregated node, the queryBuilder will add this condition to child aggregate table.
/// however for IsNull condition is little special, it should be in main where clause and the condition node should be current aggreagate node id.
/// </summary>
/// <param name="conditions">structuredQuery conditions</param>
/// <param name="aggregateEntity">current aggregated entity</param>
private void ConvertConditionExpressionToAggregate(List <QueryCondition> conditions,
AggregateEntity aggregateEntity)
{
if (conditions.Any(c => c.Operator == ConditionType.IsNull &&
c.Expression is ResourceDataColumn &&
MatchGroupedEntityNode(((ResourceDataColumn)c.Expression).NodeId, aggregateEntity.GroupedEntity)
))
{
foreach (QueryCondition condition in conditions)
{
ResourceDataColumn resourceExpression = condition.Expression as ResourceDataColumn;
if (resourceExpression != null)
{
AggregateExpression aggregateExpression = new AggregateExpression
{
AggregateMethod = AggregateMethod.Min,
NodeId = aggregateEntity.NodeId,
Expression = resourceExpression
};
condition.Expression = aggregateExpression;
}
}
}
}
/// <summary>
/// Gets the type of the column database.
/// </summary>
/// <param name="sourceQuery">The source query.</param>
/// <param name="selectColumn">The select column.</param>
/// <returns></returns>
private static DatabaseType GetColumnDatabaseType(StructuredQuery sourceQuery, SelectColumn selectColumn)
{
DatabaseType columnType = DatabaseType.UnknownType;
var field = selectColumn.Expression as ResourceDataColumn;
if (field != null)
{
ResourceDataColumn dataField = field;
if (dataField.CastType != null && dataField.CastType.GetDisplayName( ) != DatabaseType.UnknownType.GetDisplayName( ))
{
columnType = dataField.CastType;
}
else
{
columnType = GetFieldDataType(dataField.FieldId);
}
}
else if (selectColumn.Expression is StructureViewExpression)
{
columnType = DatabaseType.StructureLevelsType;
}
else
{
var expression = selectColumn.Expression as CalculationExpression;
if (expression != null)
{
//TODO hack now
if (expression.DisplayType != null && expression.DisplayType != DatabaseType.UnknownType)
{
return(expression.DisplayType);
}
}
else
{
var reference = selectColumn.Expression as ColumnReference;
if (reference != null)
{
ColumnReference columnReference = reference;
// Sanity check. Prevent infinite recursion
if (columnReference.ColumnId != selectColumn.ColumnId)
{
SelectColumn referencedColumn = sourceQuery.SelectColumns.FirstOrDefault(c => c.ColumnId == columnReference.ColumnId);
columnType = GetColumnDatabaseType(sourceQuery, referencedColumn);
}
}
else
{
var aggregateExpression = selectColumn.Expression as AggregateExpression;
if (aggregateExpression != null)
{
if (aggregateExpression.Expression is StructureViewExpression)
{
columnType = DatabaseType.StructureLevelsType;
}
else
{
columnType = (aggregateExpression.Expression is ResourceDataColumn) ? ((ResourceDataColumn)(aggregateExpression.Expression)).CastType : DatabaseType.StringType;
}
}
else if (selectColumn.Expression is IdExpression)
{
columnType = DatabaseType.IdentifierType;
}
}
}
}
return(columnType);
}
/// <summary>
/// Remove any columns from a report that are not required to achieve a rollup result.
/// </summary>
/// <remarks>
/// If columns get removed here then the query optimiser will later remove various joins.
/// This can affect whether some rows are repeated.
/// Some aggregate types (e.g. max/min) are unaffected by this.
/// Some types (e.g. Count) are very affected by this, so we replace columns with simpler ones, rather than removing them completely.
/// Some types (e.g. Sum) in principle could be affected by this, but in practice are OK because they will reference the relationship branch that is relevant to them anyway.
/// </remarks>
/// <param name="query">The original query.</param>
/// <param name="clientAggregate">Aggregate settings that are used to determine what columns are used.</param>
/// <returns>A clone of the query, with unused columns removed.</returns>
public static StructuredQuery RemoveUnusedColumns(StructuredQuery query, ClientAggregate clientAggregate, bool supportQuickSearch = false)
{
StructuredQuery queryCopy = query.DeepCopy( );
// Determine columns that are used by the rollup
HashSet <Guid> referencedColumns = new HashSet <Guid>(clientAggregate.AggregatedColumns.Select(a => a.ReportColumnId)
.Concat(clientAggregate.GroupedColumns.Select(g => g.ReportColumnId)));
// Also include columns that are referenced by analyzer conditions
foreach (QueryCondition condition in query.Conditions)
{
ColumnReference colRefExpr = condition.Expression as ColumnReference;
if (colRefExpr == null)
{
continue;
}
referencedColumns.Add(colRefExpr.ColumnId);
}
// Ensure that the inner report returns at least something. (This will typically be the ID column).
if (referencedColumns.Count == 0 && query.SelectColumns.Count > 0)
{
referencedColumns.Add(query.SelectColumns [0].ColumnId);
}
// There are two types of optimisations. Either we can just pull out all unused columns, and let the structured query optimiser
// remove the subsequent relationship joins - which is OK for things like max & min in particular.
// But in some cases (e.g. for Count) we need to ensure we capture all relationships to get the true fanout.
// This is safer, but less efficient.
bool strictlyMaintainRowPresence = clientAggregate.AggregatedColumns.Any(ag => ag.AggregateMethod == AggregateMethod.Count);
if (!strictlyMaintainRowPresence)
{
// Remove all unused columns
queryCopy.SelectColumns.RemoveAll(column => !referencedColumns.Contains(column.ColumnId));
}
else
{
// Visit each column and determine if it can be removed, or converted to a simpler type
List <SelectColumn> columns = queryCopy.SelectColumns;
List <Guid> toRemove = new List <Guid>( );
for (int i = 0; i < columns.Count; i++)
{
SelectColumn column = columns [i];
if (referencedColumns.Contains(column.ColumnId))
{
continue;
}
// Replace field lookups with an equivalent ID column .. to maintain the relationship join, but remove the field join.
ResourceDataColumn fieldColumnExpr = column.Expression as ResourceDataColumn;
if (fieldColumnExpr != null)
{
// TODO: we could delete this column entire IF the entire relationship path to it is 'to one' relationships, without any advance properties to enforce rows.
// UPDATE: for quick serach purpose, the column expression should be ResourceDataColumn but skip in select clause.
// however for performance reasons, if without quick search, still change to IdExpression
if (supportQuickSearch)
{
column.IsHidden = true;
}
else
{
column.Expression = new IdExpression {
NodeId = fieldColumnExpr.NodeId
}
};
continue;
}
// Remove aggregate expressions if they don't have group-bys. (A group-by would cause the aggregate to return more than one row).
AggregateExpression aggExpr = column.Expression as AggregateExpression;
if (aggExpr != null)
{
AggregateEntity aggNode = StructuredQueryHelper.FindNode(queryCopy.RootEntity, aggExpr.NodeId) as AggregateEntity;
if (aggNode != null)
{
if (aggNode.GroupBy == null || aggNode.GroupBy.Count == 0)
{
toRemove.Add(column.ColumnId);
}
}
}
// Would be nice to remove calculated columns .. but probably too risky
}
queryCopy.SelectColumns.RemoveAll(column => toRemove.Contains(column.ColumnId));
}
// Remove any obsolete order-by instructions
queryCopy.OrderBy.RemoveAll(orderBy =>
{
ColumnReference colRefExpr = orderBy.Expression as ColumnReference;
if (colRefExpr == null)
{
return(false);
}
bool colStillPresent = queryCopy.SelectColumns.Any(column => column.ColumnId == colRefExpr.ColumnId);
return(!colStillPresent);
});
return(queryCopy);
}
}
protected override ScalarExpression OnBuildQuery(QueryBuilderContext context)
{
ScalarExpression arg = Argument.BuildQuery(context);
if (ResultType.Type == DataType.Entity)
{
return(arg);
}
// Special case for AggregateExpression, because if we aggregate a choicefield, then it presents
// as an Entity type, therefore requesting this cast - but the query builder wants to receive the aggregate expression directly.
if (arg is EDC.ReadiNow.Metadata.Query.Structured.AggregateExpression)
{
var result = new MutateExpression {
Expression = arg
};
switch (ResultType.Type)
{
case DataType.String:
result.MutateType = MutateType.DisplaySql;
break;
case DataType.Bool:
result.MutateType = MutateType.BoolSql;
break;
default:
throw new InvalidOperationException(ResultType.Type.ToString( ));
}
return(result);
}
// Just refer to the node
var queryNode = context.GetNode(Argument);
switch (ResultType.Type)
{
case DataType.String:
var nameResult = new ResourceDataColumn
{
FieldId = "core:name",
NodeId = queryNode.NodeId
};
return(nameResult);
case DataType.Bool:
var boolResult = new CalculationExpression
{
Operator = CalculationOperator.IsNull,
Expressions = new List <ScalarExpression>
{
new IdExpression {
NodeId = queryNode.NodeId
}
}
};
return(boolResult);
default:
throw new InvalidOperationException(ResultType.Type.ToString());
}
}
public HttpResponseMessage <ReportDataDefinition> Query([FromBody] JsonStructuredQuery jsonQuery)
{
if (jsonQuery == null)
{
throw new WebArgumentNullException("jsonQuery");
}
if (jsonQuery.Root == null)
{
throw new WebArgumentNullException("jsonQuery.Root");
}
try
{
var query = new StructuredQuery( );
var nodeIds = new Dictionary <string, Guid>( );
string rootAs = jsonQuery.Root.As ?? "";
nodeIds.Add(rootAs, Guid.NewGuid( ));
query.RootEntity = new ResourceEntity
{
EntityTypeId = WebApiHelpers.GetId(jsonQuery.Root.Id),
NodeId = nodeIds[rootAs]
};
query.RootEntity.RelatedEntities.AddRange(RelatedEntities(jsonQuery.Root.Related, nodeIds));
query.SelectColumns.Add(new SelectColumn
{
ColumnId = Guid.NewGuid( ),
ColumnName = "_Id",
IsHidden = true,
Expression = new IdExpression
{
NodeId = query.RootEntity.NodeId
}
});
if (jsonQuery.Selects != null)
{
foreach (JsonSelectInQuery f in jsonQuery.Selects)
{
if (!string.IsNullOrEmpty(f.Field) && f.Field.ToLower( ) == "_id" && f.On != null)
{
if (f.On != null)
{
query.SelectColumns.Add(new SelectColumn
{
ColumnId = Guid.NewGuid( ),
ColumnName = f.DisplayAs ?? f.On + "_Id",
IsHidden = true,
Expression = new IdExpression
{
NodeId = nodeIds[f.On]
}
});
}
}
else
{
var field = new ResourceDataColumn
{
FieldId = WebApiHelpers.GetId(f.Field),
NodeId = nodeIds[f.On ?? rootAs]
};
string name = field.FieldId.Entity.Cast <Resource>( ).Name;
var sc = new SelectColumn
{
ColumnId = Guid.NewGuid( ),
DisplayName = f.DisplayAs ?? name,
ColumnName = name,
Expression = field
};
query.SelectColumns.Add(sc);
}
}
}
if (jsonQuery.Conds != null)
{
foreach (JsonCondition c in jsonQuery.Conds)
{
var qc = new QueryCondition
{
Operator = GetConditionOperator(c.Operation),
Argument = new TypedValue(c.Value)
};
if (!string.IsNullOrEmpty(c.Expression.Field) && c.Expression.Field.ToLower( ) == "_id")
{
qc.Expression = new IdExpression
{
NodeId = nodeIds[c.Expression.On ?? rootAs]
};
}
else
{
qc.Expression = new ResourceDataColumn
{
FieldId = WebApiHelpers.GetId(c.Expression.Field),
NodeId = nodeIds[c.Expression.On ?? rootAs]
};
}
query.Conditions.Add(qc);
}
}
QueryResult result = Factory.QueryRunner.ExecuteQuery(query, new QuerySettings
{
SecureQuery = true
});
// debug
LogResult(result);
return(new HttpResponseMessage <ReportDataDefinition>(PackReportResponse(result)));
}
catch (HttpResponseException)
{
throw;
}
catch (PlatformSecurityException)
{
throw;
}
catch (Exception e)
{
if (e is ArgumentException) // would be better if there was a more specific exception for 'not found'
{
throw new HttpResponseException(HttpStatusCode.NotFound);
}
throw;
}
}
