public void EntityGetMatchesWithRelationship( )
{
var query = new StructuredQuery
{
RootEntity = new ResourceEntity(Field.Field_Type)
};
// Root query type is 'EntityType'
// Follow 'Fields' relationship
var relatedResource = new RelatedResource
{
RelationshipDirection = RelationshipDirection.Forward,
RelationshipTypeId = new EntityRef(Field.FieldIsOnType_Field.Id),
ResourceMustExist = false
};
query.RootEntity.RelatedEntities.Add(relatedResource);
query.Conditions.Add(new QueryCondition
{
Expression = new ResourceDataColumn(relatedResource, EntityType.Name_Field),
Operator = ConditionType.Equal,
Argument = new TypedValue("Person")
});
IEnumerable <Field> personFields = Entity.GetMatches <Field>(query);
Assert.IsTrue(personFields.Any( ));
}
public void EntityGetMatchesWithRelationship2( )
{
var stringField = Entity.Get <EntityType>(new EntityRef("core", "stringField"));
var fieldIsOnTypeRel = Entity.Get <Relationship>(new EntityRef("core", "fieldIsOnType"));
var query = new StructuredQuery
{
RootEntity = new ResourceEntity(stringField)
};
// Root query type is 'EntityType'
// Follow 'Fields' relationship
var relatedResource = new RelatedResource
{
RelationshipDirection = RelationshipDirection.Forward,
RelationshipTypeId = fieldIsOnTypeRel.Id,
ResourceMustExist = false
};
query.RootEntity.RelatedEntities.Add(relatedResource);
//// check the condition
query.Conditions.Add(new QueryCondition
{
Expression = new ResourceDataColumn(relatedResource, EntityType.Name_Field),
Operator = ConditionType.Equal,
Argument = new TypedValue("Person")
});
IEnumerable <Field> descriptionStringFields = Entity.GetMatches <Field>(query);
Assert.IsTrue(descriptionStringFields.Any( ), "There should be at least one person type");
}
/// <summary>
/// Related entities.
/// </summary>
/// <param name="related">The related.</param>
/// <param name="nodeIds">The node ids.</param>
/// <returns></returns>
private IEnumerable <RelatedResource> RelatedEntities(IEnumerable <JsonRelatedEntityInQuery> related, Dictionary <string, Guid> nodeIds)
{
var relatedEntities = new List <RelatedResource>( );
if (related != null)
{
foreach (JsonRelatedEntityInQuery r in related)
{
string asId = r.Related.As ?? "";
if (nodeIds.ContainsKey(asId))
{
continue;
}
nodeIds.Add(asId, Guid.NewGuid( ));
var re = new RelatedResource
{
NodeId = nodeIds[asId],
RelationshipTypeId = WebApiHelpers.GetId(r.Related.Id),
RelationshipDirection = r.Forward ? RelationshipDirection.Forward : RelationshipDirection.Reverse,
ResourceMustExist = r.MustExist
};
re.RelatedEntities.AddRange(RelatedEntities(r.Related.Related, nodeIds));
relatedEntities.Add(re);
}
}
return(relatedEntities);
}
public void Test_FindNodePath_FourLevel()
{
ResourceEntity rootEntity;
ResourceEntity firstLevelEntity;
ResourceEntity secondLevelEntity;
ResourceEntity thirdLevelEntity;
Guid rootEntityNodeId = Guid.NewGuid();
Guid firstLevelEntityNodeId = Guid.NewGuid();
Guid secondLevelEntityNodeId = Guid.NewGuid();
Guid thirdLevelEntityNodeId = Guid.NewGuid();
rootEntity = new ResourceEntity {
NodeId = rootEntityNodeId
};
firstLevelEntity = new RelatedResource {
NodeId = firstLevelEntityNodeId
};
secondLevelEntity = new RelatedResource {
NodeId = secondLevelEntityNodeId
};
thirdLevelEntity = new RelatedResource {
NodeId = thirdLevelEntityNodeId
};
secondLevelEntity.RelatedEntities.Add(thirdLevelEntity);
firstLevelEntity.RelatedEntities.Add(secondLevelEntity);
rootEntity.RelatedEntities.Add(firstLevelEntity);
List <Entity> findNodePath = StructuredQueryHelper.FindNodePath(thirdLevelEntityNodeId, rootEntity);
Assert.AreEqual(findNodePath.Count, 3);
}
public void Test_WalkNodes_Related( )
{
StructuredQuery sq = new StructuredQuery( );
var root = sq.RootEntity = new ResourceEntity( );
var related1 = new RelatedResource( );
root.RelatedEntities.Add(related1);
AssertNodes(sq, root, related1);
}
/// <summary>
/// All <see cref="UserAccount"/>s in the <see cref="Role"/> with the name
/// <paramref name="roleName"/>.
/// </summary>
/// <param name="roleName">
/// The role name. This cannot be null, empty or whitespace.
/// </param>
/// <returns>
/// The query.
/// </returns>
/// <exception cref="ArgumentNullException">
/// <paramref name="roleName"/> cannot be null, empty or whitespace.
/// </exception>
public static StructuredQuery ActiveUsersInRole(string roleName)
{
ResourceEntity rootEntity;
ResourceEntity relatedRoleEntity;
ResourceEntity activeStatusEntity;
StructuredQuery structuredQuery;
activeStatusEntity = new RelatedResource(new EntityRef("core:accountStatus"))
{
NodeId = Guid.NewGuid()
};
relatedRoleEntity = new RelatedResource(new EntityRef("core:userHasRole"))
{
NodeId = Guid.NewGuid(),
Recursive = RecursionMode.RecursiveWithSelf
};
rootEntity = new ResourceEntity
{
EntityTypeId = Entity.GetId("core:userAccount"),
ExactType = false,
NodeId = Guid.NewGuid(),
RelatedEntities = new List <ReadiNow.Metadata.Query.Structured.Entity>()
{
relatedRoleEntity,
activeStatusEntity
}
};
structuredQuery = new StructuredQuery
{
RootEntity = rootEntity,
SelectColumns = new List <SelectColumn>()
};
structuredQuery.Conditions.Add(new QueryCondition
{
Expression = new ResourceDataColumn(relatedRoleEntity, new EntityRef("core:name")),
Operator = ConditionType.Equal,
Argument = new TypedValue(roleName),
});
structuredQuery.Conditions.Add(new QueryCondition
{
Expression = new ResourceDataColumn(activeStatusEntity, new EntityRef("core:alias")),
Operator = ConditionType.Equal,
Argument = new TypedValue("active"),
});
structuredQuery.SelectColumns.Add(new SelectColumn
{
Expression = new IdExpression()
{
NodeId = rootEntity.NodeId
}
});
return(structuredQuery);
}
/// <summary>
/// All <see cref="EntityType"/>s that have access rules/security queries
/// from <see cref="Subject"/>s with the name <see cref="subjectName"/>.
/// </summary>
/// <param name="subjectName">
/// The role name. This cannot be null, empty or whitespace.
/// </param>
/// <returns>
/// The query.
/// </returns>
/// <exception cref="ArgumentNullException">
/// <paramref name="subjectName"/> cannot be null, empty or whitespace.
/// </exception>
public static StructuredQuery TypesSecuredBySubjects(string subjectName)
{
ResourceEntity rootEntity;
ResourceEntity allowAccessEntity;
ResourceEntity controlAccessEntity;
StructuredQuery structuredQuery;
allowAccessEntity = new RelatedResource(new EntityRef("core:allowAccess"), RelationshipDirection.Reverse)
{
NodeId = Guid.NewGuid()
};
controlAccessEntity = new RelatedResource(new EntityRef("core:controlAccess"), RelationshipDirection.Reverse)
{
NodeId = Guid.NewGuid(),
RelatedEntities = new List <ReadiNow.Metadata.Query.Structured.Entity>()
{
allowAccessEntity
}
};
rootEntity = new ResourceEntity
{
EntityTypeId = Entity.GetId("core:type"),
ExactType = false,
NodeId = Guid.NewGuid(),
RelatedEntities = new List <ReadiNow.Metadata.Query.Structured.Entity>()
{
controlAccessEntity
}
};
structuredQuery = new StructuredQuery
{
RootEntity = rootEntity,
SelectColumns = new List <SelectColumn>()
};
structuredQuery.Conditions.Add(new QueryCondition
{
Expression = new ResourceDataColumn(allowAccessEntity, new EntityRef("core:name")),
Operator = ConditionType.Equal,
Argument = new TypedValue(subjectName),
});
structuredQuery.SelectColumns.Add(new SelectColumn
{
Expression = new IdExpression()
{
NodeId = rootEntity.NodeId
}
});
structuredQuery.SelectColumns.Add(new SelectColumn
{
Expression = new ResourceDataColumn(rootEntity, new EntityRef("core:name"))
});
return(structuredQuery);
}
/// <summary>
/// Entities of <paramref name="fromType"/> related to entities of a second type
/// by <paramref name="relationship"/>. The destination (second) entity must have
/// a non-null name.
/// </summary>
/// <param name="fromType">
/// The source or from type. This cannot be null.
/// </param>
/// <param name="relationship">
/// The relationship from <paramref name="fromType"/>. This cannot be null and
/// its <see cref="Relationship.FromType"/> must be <paramref name="fromType"/>.
/// </param>
/// <returns>
/// The query.
/// </returns>
/// <exception cref="ArgumentNullException">
/// No argument can be null.
/// </exception>
/// <exception cref="ArgumentException">
/// <paramref name="relationship"/> must have <paramref name="fromType"/> as
/// its <see cref="Relationship.FromType"/>.
/// </exception>
public static StructuredQuery ToNamed(EntityType fromType, Relationship relationship)
{
if (fromType == null)
{
throw new ArgumentNullException("fromType");
}
if (relationship == null)
{
throw new ArgumentNullException("relationship");
}
if (!EntityRefComparer.Instance.Equals(relationship.FromType, fromType))
{
throw new ArgumentException("Not a relationship from fromType", "relationship");
}
ResourceEntity rootEntity;
ResourceEntity relatedType;
StructuredQuery structuredQuery;
relatedType = new RelatedResource(new EntityRef(relationship))
{
NodeId = Guid.NewGuid()
};
rootEntity = new ResourceEntity
{
EntityTypeId = fromType,
ExactType = false,
NodeId = Guid.NewGuid(),
RelatedEntities = new List <ReadiNow.Metadata.Query.Structured.Entity>()
{
relatedType
}
};
structuredQuery = new StructuredQuery
{
RootEntity = rootEntity,
SelectColumns = new List <SelectColumn>()
};
structuredQuery.Conditions.Add(new QueryCondition
{
Expression = new ResourceDataColumn(relatedType, new EntityRef("core:name")),
Operator = ConditionType.IsNotNull
});
structuredQuery.SelectColumns.Add(new SelectColumn
{
Expression = new IdExpression()
{
NodeId = rootEntity.NodeId
}
});
return(structuredQuery);
}
/// <summary>
/// Find all enabled <see cref="AccessRule"/> entities related to the
/// <see cref="Permission"/> named <paramref name="name"/>.
/// </summary>
/// <param name="name">
/// The name to check. This cannot be null, empty or whitespace.
/// </param>
/// <returns>
/// The query.
/// </returns>
/// <exception cref="ArgumentNullException">
/// <paramref name="name"/> cannot be null, empty or whitespace.
/// </exception>
public static StructuredQuery AccessRulesWithNamedPermission(string name)
{
if (string.IsNullOrWhiteSpace(name))
{
throw new ArgumentNullException("name");
}
ResourceEntity rootEntity;
ResourceEntity relatedPermissionEntity;
StructuredQuery structuredQuery;
relatedPermissionEntity = new RelatedResource(new EntityRef("core:permissionAccess"))
{
NodeId = Guid.NewGuid()
};
rootEntity = new ResourceEntity
{
EntityTypeId = Entity.GetId("core:accessRule"),
ExactType = false,
NodeId = Guid.NewGuid(),
RelatedEntities = new List <ReadiNow.Metadata.Query.Structured.Entity>()
{
relatedPermissionEntity
}
};
structuredQuery = new StructuredQuery
{
RootEntity = rootEntity,
SelectColumns = new List <SelectColumn>()
};
structuredQuery.Conditions.Add(new QueryCondition
{
Expression = new ResourceDataColumn(relatedPermissionEntity, new EntityRef("core:name")),
Operator = ConditionType.Equal,
Argument = new TypedValue(name),
});
structuredQuery.Conditions.Add(new QueryCondition
{
Expression = new ResourceDataColumn(rootEntity, new EntityRef("core:accessRuleEnabled")),
Operator = ConditionType.Equal,
Argument = new TypedValue(1),
});
structuredQuery.SelectColumns.Add(new SelectColumn
{
Expression = new IdExpression()
{
NodeId = rootEntity.NodeId
}
});
return(structuredQuery);
}
public void Test_WalkNodes_RootAggregate( )
{
StructuredQuery sq = new StructuredQuery( );
var agg = new AggregateEntity( );
sq.RootEntity = agg;
var related2 = new RelatedResource( );
agg.GroupedEntity = related2;
var related3 = new RelatedResource( );
related2.RelatedEntities.Add(related3);
AssertNodes(sq, agg, related2, related3);
}
public void Pruned_UnusedFirstRelationship( )
{
StructuredQuery sq = new StructuredQuery( );
var root = sq.RootEntity = new ResourceEntity( );
var related1 = new RelatedResource( );
root.RelatedEntities.Add(related1);
AddColumn(sq, new IdExpression {
NodeId = root.NodeId
});
var pruned = StructuredQueryHelper.PruneQueryTree(sq);
Assert.That(pruned, Is.Not.SameAs(sq));
AssertNodes(pruned, root);
}
public void NotPruned_ColumnReferesToRelated( )
{
StructuredQuery sq = new StructuredQuery( );
var root = sq.RootEntity = new ResourceEntity( );
var related1 = new RelatedResource( );
root.RelatedEntities.Add(related1);
AddColumn(sq, new IdExpression {
NodeId = related1.NodeId
});
var pruned = StructuredQueryHelper.PruneQueryTree(sq);
Assert.That(pruned, Is.SameAs(sq));
AssertNodes(pruned, root, related1);
}
public static StructuredQuery Entities(EntityType rootEntityType, IEnumerable <Relationship> relationshipsToFollow, bool relationshipsAreForward = true)
{
List <Guid> nodeIds = new List <Guid>();
StructuredQuery structuredQuery = new StructuredQuery
{
RootEntity = new ResourceEntity
{
EntityTypeId = rootEntityType.Id,
ExactType = false,
NodeId = Guid.NewGuid()
},
SelectColumns = new List <SelectColumn>()
};
ResourceEntity parentNode = structuredQuery.RootEntity as ResourceEntity;
nodeIds.Add(parentNode.NodeId);
foreach (Relationship relationship in relationshipsToFollow)
{
RelatedResource relatedResource = new RelatedResource
{
EntityTypeId = relationship.ToType.Id,
NodeId = Guid.NewGuid(),
RelationshipDirection = relationshipsAreForward ? RelationshipDirection.Forward : RelationshipDirection.Reverse,
RelationshipTypeId = relationship.Id
};
parentNode.RelatedEntities.Add(relatedResource);
parentNode = relatedResource;
nodeIds.Add(relatedResource.NodeId);
}
foreach (Guid nodeId in nodeIds)
{
structuredQuery.SelectColumns.Add(new SelectColumn
{
Expression = new IdExpression()
{
NodeId = nodeId
}
});
}
return(structuredQuery);
}
public void NotPruned_UnusedRootAggregate( )
{
StructuredQuery sq = new StructuredQuery( );
var agg = new AggregateEntity( );
sq.RootEntity = agg;
var related2 = new RelatedResource( );
agg.GroupedEntity = related2;
var related3 = new RelatedResource( );
related2.RelatedEntities.Add(related3);
var pruned = StructuredQueryHelper.PruneQueryTree(sq);
Assert.That(pruned, Is.SameAs(sq));
AssertNodes(pruned, agg, related2, related3);
}
/// <summary>
/// Return true if we cannot
/// </summary>
/// <param name="node"></param>
/// <returns></returns>
private static bool NodeMightCauseSomeRowsToNotAppear(SQ.Entity node)
{
// Check flags on the node
RelatedResource relNode = node as RelatedResource;
if (relNode != null)
{
if (relNode.ResourceMustExist || relNode.CheckExistenceOnly || relNode.ConstrainParent)
{
return(true);
}
}
else
{
DownCastResource derivedNode = node as DownCastResource;
if (derivedNode != null)
{
if (derivedNode.MustExist)
{
return(true);
}
}
else
{
if (node is AggregateEntity)
{
return(false); // aggregates will always give us something
}
else
{
return(true);
}
}
}
// Check children
if (node.RelatedEntities.Any(NodeMightCauseSomeRowsToNotAppear))
{
return(true);
}
return(false);
}
public void Test_FindNodePath_TwoLevel()
{
ResourceEntity rootEntity;
ResourceEntity firstLevelEntity;
Guid rootEntityNodeId = Guid.NewGuid();
Guid firstLevelEntityNodeId = Guid.NewGuid();
rootEntity = new ResourceEntity {
NodeId = rootEntityNodeId
};
firstLevelEntity = new RelatedResource {
NodeId = firstLevelEntityNodeId
};
rootEntity.RelatedEntities.Add(firstLevelEntity);
List <Entity> findNodePath = StructuredQueryHelper.FindNodePath(firstLevelEntityNodeId, rootEntity);
Assert.AreEqual(findNodePath.Count, 1);
}
public void NotPruned_ChildrenOfUsedAggregate( )
{
StructuredQuery sq = new StructuredQuery( );
var root = sq.RootEntity = new ResourceEntity( );
var agg = new AggregateEntity( );
root.RelatedEntities.Add(agg);
var related2 = new RelatedResource( );
agg.GroupedEntity = related2;
var related3 = new RelatedResource( );
related2.RelatedEntities.Add(related3);
AddColumn(sq, new IdExpression {
NodeId = agg.NodeId
});
var pruned = StructuredQueryHelper.PruneQueryTree(sq);
Assert.That(pruned, Is.SameAs(sq));
AssertNodes(pruned, root, agg, related2, related3);
}
public override EDC.ReadiNow.Metadata.Query.Structured.Entity OnBuildQueryNode(QueryBuilderContext context, bool allowReuse)
{
if (context.ParentNode == null)
{
throw new Exception("No context.");
}
var relationshipTypeId = RelationshipId;
var direction = Direction == Direction.Forward ? RelationshipDirection.Forward : RelationshipDirection.Reverse;
// Look for an existing relationship node in the tree that we can reuse
// TODO : We shouldn't really match existing nodes if they were layed down as part of this build, as the expression tree should contain the intended target structure
RelatedResource result = !allowReuse ? null :
context.ParentNode
.RelatedEntities
.OfType <RelatedResource>()
.FirstOrDefault(
rr => rr.RelationshipTypeId.Id == relationshipTypeId.Id &&
rr.RelationshipDirection == direction &&
(rr.Conditions == null || rr.Conditions.Count == 0));
// Or create one
if (result == null)
{
// New node
result = new RelatedResource
{
RelationshipTypeId = relationshipTypeId,
RelationshipDirection = direction
};
context.ParentNode.RelatedEntities.Add(result);
}
AddChildNodes(context, result, allowReuse);
return(result);
}
/// <summary>
/// Create a structured query that will return types/objects matching a particular name.
/// </summary>
/// <remarks>
/// Returns objects, system types, enums, and activity types. But not other things that derive from type (such as fieldType).
/// </remarks>
/// <returns>
/// StructuredQuery with a @scriptName query parameter.
/// </returns>
private static StructuredQuery BuildTypeByNameQuery()
{
// List of types that we allow to be referenced by names
long[] allowedTypeTypes = new[] {
Definition.Definition_Type.Id,
ManagedType.ManagedType_Type.Id,
EntityType.EntityType_Type.Id,
EnumType.EnumType_Type.Id,
ActivityType.ActivityType_Type.Id
};
// Create the structured query
var rootEntity = new ResourceEntity(new EntityRef(WellKnownAliases.CurrentTenant.Type));
var typeType = new RelatedResource(new EntityRef(WellKnownAliases.CurrentTenant.IsOfType));
rootEntity.RelatedEntities.Add(typeType);
var query = new StructuredQuery()
{
RootEntity = rootEntity
};
var col = new SelectColumn {
Expression = new SQ.IdExpression {
NodeId = rootEntity.NodeId
}
};
query.SelectColumns.Add(col);
// Allowed-type condition
var typeCondition = new QueryCondition
{
Expression = new SQ.IdExpression {
NodeId = typeType.NodeId
},
Operator = ConditionType.AnyOf,
Arguments = allowedTypeTypes.Select(id => new TypedValue()
{
Value = id, Type = DatabaseType.IdentifierType
}).ToList()
};
query.Conditions.Add(typeCondition);
// Script-name condition
var calcExpr = new CalculationExpression
{
Operator = CalculationOperator.IsNull,
Expressions = new List <ScalarExpression>
{
new ResourceDataColumn(rootEntity, new EntityRef("core:typeScriptName")),
new ResourceDataColumn(rootEntity, new EntityRef("core:name")),
}
};
var nameCondition = new QueryCondition
{
Expression = calcExpr,
Operator = ConditionType.Equal,
Parameter = "@scriptName"
};
query.Conditions.Add(nameCondition);
return(query);
}
/// <summary>
/// Builds the relationship report node.
/// </summary>
/// <param name="reportNode">The report node.</param>
/// <param name="context">The context.</param>
/// <returns>RelatedResource.</returns>
private static RelatedResource BuildRelationshipReportNode(RelationshipReportNode reportNode, FromEntityContext context)
{
RelatedResource relatedResource = new RelatedResource
{
ResourceMustExist = reportNode.TargetMustExist ?? false,
ResourceNeedNotExist = reportNode.TargetNeedNotExist ?? false,
ParentNeedNotExist = reportNode.ParentNeedNotExist ?? false,
ExactType = reportNode.ExactType ?? false
};
if (reportNode.ResourceReportNodeType != null)
{
relatedResource.EntityTypeId = reportNode.ResourceReportNodeType.Id;
}
if (reportNode.FollowInReverse ?? false)
{
relatedResource.RelationshipDirection = RelationshipDirection.Reverse;
}
else
{
relatedResource.RelationshipDirection = RelationshipDirection.Forward;
}
if (reportNode.FollowRecursive == true)
{
if (reportNode.IncludeSelfInRecursive == true)
{
relatedResource.Recursive = RecursionMode.RecursiveWithSelf;
}
else
{
relatedResource.Recursive = RecursionMode.Recursive;
}
}
else
{
relatedResource.Recursive = RecursionMode.None;
}
Guid nodeId;
if (!context.ReportNodeMap.TryGetValue(reportNode.Id, out nodeId))
{
nodeId = Guid.NewGuid();
context.ReportNodeMap[reportNode.Id] = nodeId;
}
relatedResource.NodeId = nodeId;
relatedResource.EntityId = reportNode.Id;
if (reportNode.FollowRelationship != null)
{
relatedResource.RelationshipTypeId = reportNode.FollowRelationship.Id;
}
else
{
context.ReportInvalidNodes [reportNode.Id] = reportNode.ResourceReportNodeType != null?Model.Entity.GetName(reportNode.ResourceReportNodeType.Id) : "";
EventLog.Application.WriteWarning(context.DebugInfo + "reportNode.FollowRelationship was null");
return(null);
}
return(relatedResource);
}
/// <summary>
/// Generates SqlTables for a related resource.
/// </summary>
/// <param name="relationship">The relationship.</param>
/// <param name="parentTable">The parent table.</param>
/// <param name="sqlQuery">The SQL query that the table will be created in.</param>
/// <returns>The table to use for upward joins.</returns>
private EntityTables RegisterRelatedResourceEntity(RelatedResource relationship, SqlTable parentTable, SqlQuery sqlQuery)
{
const string fromColumnName = "FromId";
const string toColumnName = "ToId";
bool forward = relationship.RelationshipDirection == RelationshipDirection.Forward;
string relationshipTableName;
// Determine if its a virtual relationship
IEntity relationshipDefinition = Model.Entity.Get(relationship.RelationshipTypeId);
bool isRecursive = relationship.Recursive != RecursionMode.None;
bool isNormal = !isRecursive;
// Ensure resource type is set
var relEnt = relationshipDefinition.As <Model.Relationship>();
if (relEnt == null)
{
EventLog.Application.WriteError(@"Invalid relationship specified or invalid, Relationship entityId = {0}", relationship.RelationshipTypeId);
throw new InvalidOperationException("Relationship not specified or invalid.");
}
// Get endpoint type.
EntityType entityType = relationship.RelationshipDirection == RelationshipDirection.Forward ? relEnt.ToType : relEnt.FromType;
if (entityType == null)
{
throw new Exception("Could not load relationship endpoint type for relationship " + relationship.RelationshipTypeId);
}
relationship.EntityTypeId = entityType.Id;
// Determine table name
if (isRecursive)
{
long?fromTypeId = relEnt.FromType?.Id;
long?toTypeId = relEnt.ToType?.Id;
if (fromTypeId == null || toTypeId == null)
{
throw new Exception($"Relationship {relEnt.Id} is missing endpoint type details.");
}
string relationshipTypeIdParamName = RegisterSharedParameter(System.Data.DbType.Int64, relationship.RelationshipTypeId.Id.ToString(CultureInfo.InvariantCulture));
string fromTypeIdParamName = RegisterSharedParameter(System.Data.DbType.Int64, fromTypeId.Value.ToString(CultureInfo.InvariantCulture));
string toTypeIdParamName = RegisterSharedParameter(System.Data.DbType.Int64, toTypeId.Value.ToString(CultureInfo.InvariantCulture));
relationshipTableName = sqlQuery.AliasManager.CreateAlias("#rec");
string formattedRelEntityId = FormatEntity(relationship.RelationshipTypeId, true, relationshipTypeIdParamName);
string formattedFromEntityId = FormatEntity(relEnt.FromType, true, fromTypeIdParamName);
string formattedToEntityId = FormatEntity(relEnt.ToType, true, toTypeIdParamName);
relationshipTableName += $"_{formattedRelEntityId}_{formattedFromEntityId}_{formattedToEntityId}";
string create = $"\nCREATE TABLE {relationshipTableName} ( FromId BIGINT, ToId BIGINT, Depth INT, TypeId BIGINT, TenantId BIGINT )\nINSERT INTO {relationshipTableName} SELECT * FROM dbo.fnGetRelationshipRecAndSelf({FormatEntity( relationship.RelationshipTypeId, relationshipTypeIdParamName )}, @tenant, {( relationship.Recursive == RecursionMode.Recursive ? 0 : 1 )}, {FormatEntity( relEnt.FromType, fromTypeIdParamName )}, {FormatEntity( relEnt.ToType, toTypeIdParamName )})";
string drop = $"\nDROP TABLE {relationshipTableName}";
if (_querySettings.UseSharedSql)
{
// Return the sql used to create/drop the tables.
// Do not emit it as part of the sql for the query.
_sharedSqlPreamble.Add(create);
_sharedSqlPostamble.Add(drop);
}
else
{
// Check if the shared sql exists.
// This is an optimization.
bool isSharedSqlAvailable = IsSharedSqlPreambleAvailable(create);
if (!isSharedSqlAvailable)
{
// Emit the sql as part of the query
_sqlBatch.SqlPreamble = string.Concat(Environment.NewLine, create, _sqlBatch.SqlPreamble);
_sqlBatch.SqlPostamble = string.Concat(_sqlBatch.SqlPostamble, Environment.NewLine, drop);
}
}
}
else
{
relationshipTableName = "dbo.Relationship";
}
// Determine relationship
// TODO: this is suboptimal. Ideally, if ResourceMustExist=false, then JoinHint should be 'Unspecified'
// then if any ResourceDataColumn expressions actually point to it and its still Unspecified then it should be flipped to 'Optional' at that time.
// This will result in more efficient joins.
JoinHint joinHint;
if (relationship.CheckExistenceOnly)
{
joinHint = JoinHint.ConstrainWithExists;
}
else if (relationship.ResourceMustExist)
{
joinHint = JoinHint.Required;
}
else if (relationship.ResourceNeedNotExist)
{
joinHint = JoinHint.DontConstrainParent;
}
else if (relationship.ConstrainParent)
{
joinHint = JoinHint.Constrain;
}
else
{
joinHint = JoinHint.Unspecified;
}
// Create the relationship table
SqlTable relationshipTable =
sqlQuery.CreateJoinedTable(relationshipTableName, "rel", parentTable, joinHint, forward ? fromColumnName : toColumnName, parentTable.IdColumn);
relationshipTable.JoinNotConstrainedByParent = relationship.ParentNeedNotExist;
// Column that child tables should join to
//if (isRecursive)
//{
// relationshipTable.IdColumn = "Id";
//}
//else
//{
relationshipTable.IdColumn = forward ? toColumnName : fromColumnName;
//}
if (isRecursive)
{
//relationshipTable.NameContainsSql = true;
// Specify depth constraint
if (relationship.Recursive == RecursionMode.Recursive)
{
relationshipTable.Conditions.Add("$.Depth > 0");
}
}
if (isNormal)
{
// Filter tenant
relationshipTable.FilterByTenant = true;
}
string relationshipTypeIdParamName1 = RegisterSharedParameter(System.Data.DbType.Int64, relationship.RelationshipTypeId.Id.ToString(CultureInfo.InvariantCulture));
// Specify relationship type
relationshipTable.Conditions.Add("$.TypeId = " + FormatEntity(relationship.RelationshipTypeId, relationshipTypeIdParamName1));
// Secure the join
relationshipTable.SecureResources = _querySettings.SecureQuery;
if (relationshipTable.SecureResources)
{
// Note: A relationship cannot implicitly secure a full join because
// target entities will be viewed even in the absence of a securing relationship
bool implicitlySecured = !relationshipTable.JoinNotConstrainedByParent &&
DoesRelationshipImplicitlySecureResources(relEnt, relationship.RelationshipDirection);
relationshipTable.IsImplicitlySecured = implicitlySecured;
}
// Explicitly manufacture relationships (edge case)
if (relationship.FauxRelationships != null)
{
ApplyFauxRelationships(relationship, relationshipTable, sqlQuery);
// Ordinarily we could inner (or whatever) join the entity table direct to the relationship table, but if we are manufacturing a faux relationship
// and the related entity doesn't exist yet (e.g. because it's new and not yet saved) then we must left join, to get the null row, otherwise we don't get the relationship row.
// It's a bit hacky, but at least means we're not disturbing the table tree.
if (!relationship.FauxRelationships.HasTargetResource)
{
relationshipTable.JoinHint = JoinHint.Optional;
}
}
return(new EntityTables
{
EntityTable = relationshipTable,
HeadTable = relationshipTable
});
}
/// <summary>
/// Applies the related resource condition to the structured query.
/// </summary>
/// <param name="structuredQuery">The structured query.</param>
/// <param name="relationshipSettings">The relationship settings.</param>
private static void ApplyRelatedResourceCondition(StructuredQuery structuredQuery, ReportRelationshipSettings relationshipSettings, QuerySettings querySettings)
{
RelationshipDirection direction = relationshipSettings.Direction == ReportRelationshipSettings.ReportRelationshipDirection.Forward ? RelationshipDirection.Forward : RelationshipDirection.Reverse;
// Find existing equivalent relation.
RelatedResource relation = (
from related in structuredQuery.RootEntity.RelatedEntities
let rr = related as RelatedResource
where rr != null && rr.RelationshipTypeId.Id == relationshipSettings.RelationshipId && rr.RelationshipDirection == direction && rr.Recursive == RecursionMode.None
select rr).FirstOrDefault( );
if (relation == null)
{
// Add if not already found
relation = new RelatedResource
{
NodeId = Guid.NewGuid( ),
RelationshipDirection = direction,
RelationshipTypeId = new Model.EntityRef(relationshipSettings.RelationshipId),
ResourceMustExist = true,
};
List <Entity> relatedEntities = new List <Entity>( );
if (structuredQuery.RootEntity.RelatedEntities != null)
{
relatedEntities.AddRange(structuredQuery.RootEntity.RelatedEntities);
}
relatedEntities.Add(relation);
structuredQuery.RootEntity.RelatedEntities = relatedEntities;
}
// Add in the unsaved changes prior to the SQL generation and report run
if (relationshipSettings.EntityId != 0 ||
(relationshipSettings.IncludedEntityIds != null && relationshipSettings.IncludedEntityIds.Count > 0) ||
(relationshipSettings.ExcludedEntityIds != null && relationshipSettings.ExcludedEntityIds.Count > 0))
{
relation.FauxRelationships = new FauxRelationships
{
HasTargetResource = relationshipSettings.EntityId != 0,
IsTargetResourceTemporary = Model.EntityId.IsTemporary(relationshipSettings.EntityId),
HasIncludedResources = relationshipSettings.IncludedEntityIds != null && relationshipSettings.IncludedEntityIds.Count > 0,
HasExcludedResources = relationshipSettings.ExcludedEntityIds != null && relationshipSettings.ExcludedEntityIds.Count > 0
};
}
// Set execute-time information into query settings object
querySettings.TargetResource = relationshipSettings.EntityId;
querySettings.IncludeResources = relationshipSettings.IncludedEntityIds?.Distinct();
querySettings.ExcludeResources = relationshipSettings.ExcludedEntityIds?.Distinct();
// In the event that the entity that is passed into us has yet to be actually created then it will be 0 and should not be used in the query condition.
if (relationshipSettings.EntityId != 0)
{
var condition = new QueryCondition
{
Expression = new IdExpression {
NodeId = relation.NodeId
},
Operator = ConditionType.Equal,
Parameter = "@targetResource"
};
List <QueryCondition> queryConditions = new List <QueryCondition>( );
if (structuredQuery.Conditions != null)
{
queryConditions.AddRange(structuredQuery.Conditions);
}
queryConditions.Add(condition);
structuredQuery.Conditions = queryConditions;
}
}
/// <summary>
/// Builds the structured query for a non system identity provider.
/// </summary>
/// <returns>StructuredQuery.</returns>
private StructuredQuery BuildStructuredQueryForNonSystemIdentityProvider()
{
var query = new StructuredQuery();
// User account root entity
var rootEntity = new ResourceEntity
{
EntityTypeId = new EntityRef("core:userAccount")
};
query.RootEntity = rootEntity;
// Account status
var userAccountStatus = new RelatedResource
{
RelationshipTypeId = new EntityRef("core:accountStatus"),
RelationshipDirection = RelationshipDirection.Forward
};
rootEntity.RelatedEntities.Add(userAccountStatus);
// Identity provider user associated with user account
var identityProviderUser = new RelatedResource
{
RelationshipTypeId = new EntityRef("core:associatedUserAccount"),
RelationshipDirection = RelationshipDirection.Reverse,
ResourceMustExist = true
};
rootEntity.RelatedEntities.Add(identityProviderUser);
// Identity provider associated with identity provider user
var identityProvider = new RelatedResource
{
RelationshipTypeId = new EntityRef("core:identityProviderForUser"),
RelationshipDirection = RelationshipDirection.Forward,
ResourceMustExist = true
};
identityProviderUser.RelatedEntities.Add(identityProvider);
// Identity provider type
var identityProviderType = new RelatedResource
{
RelationshipTypeId = new EntityRef("core:isOfType"),
RelationshipDirection = RelationshipDirection.Forward,
ResourceMustExist = true
};
identityProvider.RelatedEntities.Add(identityProviderType);
// User account id column
var userAccountIdColumn = new SelectColumn
{
Expression = new IdExpression
{
NodeId = rootEntity.NodeId
}
};
query.SelectColumns.Add(userAccountIdColumn);
// User account name column
var userAccountNameColumn = new SelectColumn
{
Expression = new ResourceDataColumn
{
NodeId = rootEntity.NodeId,
FieldId = new EntityRef("core:name")
}
};
query.SelectColumns.Add(userAccountNameColumn);
// Identity provider id
var identityProviderIdColumn = new SelectColumn
{
Expression = new IdExpression
{
NodeId = identityProvider.NodeId
}
};
query.SelectColumns.Add(identityProviderIdColumn);
// Identity provider user id
var identityProviderUserIdColumn = new SelectColumn
{
Expression = new IdExpression
{
NodeId = identityProviderUser.NodeId
}
};
query.SelectColumns.Add(identityProviderUserIdColumn);
// Identity provider type
var identityProviderTypeAliasColumn = new SelectColumn
{
Expression = new ResourceDataColumn
{
NodeId = identityProviderType.NodeId,
FieldId = new EntityRef("core:alias")
}
};
query.SelectColumns.Add(identityProviderTypeAliasColumn);
// Account status column
var userAccountStatusColumn = new SelectColumn
{
Expression = new IdExpression
{
NodeId = userAccountStatus.NodeId
}
};
query.SelectColumns.Add(userAccountStatusColumn);
// Identity provider user name condition
var identityProviderUserNameCondition = new QueryCondition
{
Expression = new ResourceDataColumn
{
NodeId = identityProviderUser.NodeId,
FieldId = new EntityRef("core:name")
},
Operator = ConditionType.Equal,
Parameter = "@identityProviderUser"
};
query.Conditions.Add(identityProviderUserNameCondition);
// Identity provider condition
var identityProviderCondition = new QueryCondition
{
Expression = new IdExpression
{
NodeId = identityProvider.NodeId
},
Operator = ConditionType.Equal,
Parameter = "@identityProviderId"
};
query.Conditions.Add(identityProviderCondition);
return(query);
}
/// <summary>
/// Builds the structured query for system identity provider.
/// </summary>
/// <returns>StructuredQuery.</returns>
private StructuredQuery BuildStructuredQueryForSystemIdentityProvider()
{
var query = new StructuredQuery();
// User account root entity
var rootEntity = new ResourceEntity
{
EntityTypeId = new EntityRef("core:userAccount")
};
query.RootEntity = rootEntity;
// Account status
var userAccountStatus = new RelatedResource
{
RelationshipTypeId = new EntityRef("core:accountStatus"),
RelationshipDirection = RelationshipDirection.Forward
};
rootEntity.RelatedEntities.Add(userAccountStatus);
// User account id column
var userAccountIdColumn = new SelectColumn
{
Expression = new IdExpression
{
NodeId = rootEntity.NodeId
}
};
query.SelectColumns.Add(userAccountIdColumn);
// User account name column
var userAccountNameColumn = new SelectColumn
{
Expression = new ResourceDataColumn
{
NodeId = rootEntity.NodeId,
FieldId = new EntityRef("core:name")
}
};
query.SelectColumns.Add(userAccountNameColumn);
// Account status column
var userAccountStatusColumn = new SelectColumn
{
Expression = new IdExpression
{
NodeId = userAccountStatus.NodeId
}
};
query.SelectColumns.Add(userAccountStatusColumn);
// Identity provider user name condition
var identityProviderUserNameCondition = new QueryCondition
{
Expression = new ResourceDataColumn
{
NodeId = rootEntity.NodeId,
FieldId = new EntityRef("core:name")
},
Operator = ConditionType.Equal,
Parameter = "@identityProviderUser"
};
query.Conditions.Add(identityProviderUserNameCondition);
return(query);
}
/// <summary>
/// Constrains the query by the specified relationships.
/// </summary>
/// <param name="structuredQuery">The structured query.</param>
/// <param name="reportRelationshipFilters">The report relationship filters.</param>
private static void ApplyRelatedEntityFilters(StructuredQuery structuredQuery, IEnumerable <RelatedEntityFilterSettings> reportRelationshipFilters)
{
var newQueryConditions = new List <QueryCondition>();
foreach (RelatedEntityFilterSettings filter in reportRelationshipFilters)
{
// We do not have an entity value for this control
if (filter.RelatedEntityIds.Count <= 0)
{
continue;
}
// Find existing equivalent relation
// Note: we need to ensure that we accurately match an existing relationship if it's already there, otherwise we can't show fields on relationships within relationship tab reports properly.
RelatedResource relation = (
from related in structuredQuery.RootEntity.RelatedEntities
let rr = related as RelatedResource
where rr != null &&
rr.RelationshipTypeId.Id == filter.RelationshipId &&
rr.RelationshipDirection == filter.RelationshipDirection &&
rr.Recursive == RecursionMode.None
select rr).FirstOrDefault();
if (relation == null)
{
// Add if not already found
relation = new RelatedResource
{
NodeId = Guid.NewGuid(),
RelationshipDirection = filter.RelationshipDirection,
RelationshipTypeId = new Model.EntityRef(filter.RelationshipId),
ResourceMustExist = true,
};
var relatedEntities = new List <Entity>();
if (structuredQuery.RootEntity.RelatedEntities != null)
{
relatedEntities.AddRange(structuredQuery.RootEntity.RelatedEntities);
}
relatedEntities.Add(relation);
structuredQuery.RootEntity.RelatedEntities = relatedEntities;
}
// Add condition
var condition = new QueryCondition
{
Expression = new IdExpression {
NodeId = relation.NodeId
},
Operator = filter.RelatedEntityIds.Count == 1 ? ConditionType.Equal : ConditionType.AnyOf,
Arguments = filter.RelatedEntityIds.Select(id => new TypedValue
{
Type = new IdentifierType(),
Value = id
}).ToList()
};
newQueryConditions.Add(condition);
}
if (newQueryConditions.Count <= 0)
{
return;
}
var queryConditions = new List <QueryCondition>();
if (structuredQuery.Conditions != null)
{
// Add existing conditions
queryConditions.AddRange(structuredQuery.Conditions);
}
// Add new conditions
queryConditions.AddRange(newQueryConditions);
structuredQuery.Conditions = queryConditions;
}
/// <summary>
/// Apply over a ResourceRelationship to handle any simulated relationships that should be included or excluded.
/// Used in resource editor where relationships must be added/removed prior to the resource being saved.
/// </summary>
private void ApplyFauxRelationships(RelatedResource relationship, SqlTable relationshipTable, SqlQuery sqlQuery)
{
FauxRelationships faux = relationship.FauxRelationships;
// Detect if the target of the faux relationship even exists on the database. E.g if creating a new resource, and adding new relations to it, the parent may not yet be saved.
bool isNewTarget = !faux.HasTargetResource;
bool isTempTargetResource = faux.IsTargetResourceTemporary;
// Explicitly include related resources
if (faux.HasIncludedResources)
{
// And union it into the relationship query
// First, union in existing relationships (not applicable if the target is being newly created)
string unionSql = "";
if (!isNewTarget)
{
unionSql = "select TenantId, FromId, ToId, TypeId from dbo.Relationship union ";
}
// Then add in any faux relationships
if (relationship.RelationshipDirection == RelationshipDirection.Forward)
{
unionSql = "( " + unionSql + "select @tenant TenantId, ir.Id FromId, {0} ToId, {1} TypeId from @includeResources ir )";
}
else
{
unionSql = "( " + unionSql + "select @tenant TenantId, {0} FromId, ir.Id ToId, {1} TypeId from @includeResources ir )";
}
string relationshipTypeIdParamName = RegisterSharedParameter(System.Data.DbType.Int64, relationship.RelationshipTypeId.Id.ToString(CultureInfo.InvariantCulture));
// A bit hacky - overwrite the relationship table name with the entire union clause
relationshipTable.Name = string.Format(
unionSql,
isNewTarget ? "0" : "@targetResource",
FormatEntity(relationship.RelationshipTypeId, relationshipTypeIdParamName)
);
relationshipTable.NameContainsSql = true;
// Only secure the join if the query is being secured
if (_querySettings.SecureQuery)
{
relationshipTable.SecureResources = !isTempTargetResource;
}
}
else if (isNewTarget)
{
// This is a completely new resource, and does not yet have any included rows.
relationshipTable.Conditions.Add("1=2");
}
// Explicitly exclude related resources
if (faux.HasExcludedResources)
{
if (relationship.RelationshipDirection == RelationshipDirection.Forward)
{
relationshipTable.Conditions.Add("$.FromId not in ( select Id from @excludeResources )");
}
else
{
relationshipTable.Conditions.Add("$.ToId not in ( select Id from @excludeResources )");
}
}
}
