public override async Task <ICollection <FilterValue> > GetAvailableValuesAsync(IEnumerable <Guid> necessaryMIATypeIds, IFilter selectAttributeFilter, IFilter filter)
{
IContentDirectory cd = ServiceRegistration.Get <IServerConnectionManager>().ContentDirectory;
if (cd == null)
{
throw new NotConnectedException("The MediaLibrary is not connected");
}
Guid? userProfile = null;
IUserManagement userProfileDataManagement = ServiceRegistration.Get <IUserManagement>();
if (userProfileDataManagement != null && userProfileDataManagement.IsValidUser)
{
userProfile = userProfileDataManagement.CurrentUser.ProfileId;
}
IEnumerable <Guid> mias = _necessaryMIATypeIds ?? necessaryMIATypeIds;
IEnumerable <Guid> optMias = _optionalMIATypeIds != null?_optionalMIATypeIds.Except(mias) : null;
bool showVirtual = VirtualMediaHelper.ShowVirtualMedia(necessaryMIATypeIds);
IFilter queryFilter = new FilteredRelationshipFilter(_role, _linkedRole, filter);
MediaItemQuery query = new MediaItemQuery(mias, optMias, queryFilter);
if (_sortInformation != null)
{
query.SortInformation = new List <ISortInformation> {
_sortInformation
}
}
;
IList <MediaItem> items = await cd.SearchAsync(query, true, userProfile, showVirtual);
CertificationHelper.ConvertCertifications(items);
IList <FilterValue> result = new List <FilterValue>(items.Count);
foreach (MediaItem item in items)
{
string name;
MediaItemAspect.TryGetAttribute(item.Aspects, MediaAspect.ATTR_TITLE, out name);
result.Add(new FilterValue(name,
new FilterTreePath(_role),
null,
item,
this));
}
//ToDo: Add support for an empty entry for all filtered items that don't have this relationship
//The below works OK in simple cases but results in an extra, relatively long running query. Maybe this should be handled
//at the server for all relationship queries...
//IFilter emptyRelationshipFilter = new NotFilter(new RelationshipFilter(_linkedRole, _role, Guid.Empty));
//queryFilter = filter != null ? BooleanCombinationFilter.CombineFilters(BooleanOperator.And, filter, emptyRelationshipFilter) : emptyRelationshipFilter;
//int numEmptyEntries = cd.CountMediaItems(necessaryMIATypeIds, queryFilter, true, showVirtual);
//if(numEmptyEntries > 0)
// result.Insert(0, new FilterValue(Consts.RES_VALUE_EMPTY_TITLE, emptyRelationshipFilter, null, this));
return(result);
}
/// <summary>
/// Builds a subquery that returns the ids of the media items returned by the <paramref name="filter"/>.
/// </summary>
/// <param name="filter">Relationship filter instance to create the sub query for.</param>
/// <param name="subqueryFilter">Additional filter to apply to all subqueries.</param>
/// <param name="miaManagement">MIA_Management instance to generate attribute column names.</param>
/// <param name="bvNamespace">Namespace used to build bind var names.</param>
/// <param name="resultParts">Statement parts for the filter.</param>
/// <param name="resultBindVars">Bind variables for the filter.</param>
public static void BuildRelationshipSubquery(AbstractRelationshipFilter filter, IFilter subqueryFilter, MIA_Management miaManagement,
BindVarNamespace bvNamespace, IList <object> resultParts, IList <BindVar> resultBindVars)
{
//Simple relationship filter with linked id
BindVar linkedIdVar = null;
RelationshipFilter relationshipFilter = filter as RelationshipFilter;
if (relationshipFilter != null && relationshipFilter.LinkedMediaItemId != Guid.Empty)
{
linkedIdVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), relationshipFilter.LinkedMediaItemId, typeof(Guid));
resultBindVars.Add(linkedIdVar);
}
//Role
BindVar roleVar = null;
if (filter.Role != Guid.Empty)
{
roleVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), filter.Role, typeof(Guid));
resultBindVars.Add(roleVar);
}
//Linked role
BindVar linkedRoleVar = null;
if (filter.LinkedRole != Guid.Empty)
{
linkedRoleVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), filter.LinkedRole, typeof(Guid));
resultBindVars.Add(linkedRoleVar);
}
//Complex relationship filter with linked filter
string sqlStatement = null;
IList <BindVar> bindVars = null;
FilteredRelationshipFilter filteredRelationshipFilter = filter as FilteredRelationshipFilter;
if (filteredRelationshipFilter != null && filteredRelationshipFilter.Filter != null)
{
//Build a sub query for the linked filter
string idAlias = null;
ICollection <QueryAttribute> requiredAttributes = new List <QueryAttribute>();
SubQueryBuilder filterBuilder = new SubQueryBuilder(miaManagement, requiredAttributes,
new List <MediaItemAspectMetadata>(), filteredRelationshipFilter.Filter, subqueryFilter, bvNamespace.BindVarCounter);
filterBuilder.GenerateSqlStatement(out idAlias, out sqlStatement, out bindVars);
sqlStatement = " SELECT TS." + idAlias + " FROM (" + sqlStatement + ") TS";
bvNamespace.BindVarCounter += bindVars.Count;
CollectionUtils.AddAll(resultBindVars, bindVars);
}
//Relationships are only stored for one party in the relationship so we need to union the query with a query
//that reverses the relationship to ensure that all relationships are selected
BuildRelationshipSubqueryPart(roleVar, linkedRoleVar, linkedIdVar, sqlStatement, false, miaManagement, resultParts);
resultParts.Add(" UNION ");
BuildRelationshipSubqueryPart(roleVar, linkedRoleVar, linkedIdVar, sqlStatement, true, miaManagement, resultParts);
}
public void TestFilteredRelationshipQueryBuilder()
{
// Use the real RelationshipFilter because CompiledFilter is hard coded to look for it
MockCore.AddMediaItemAspectStorage(RelationshipAspect.Metadata);
SingleTestMIA mia1 = TestBackendUtils.CreateSingleMIA("Meta1", Cardinality.Inline, true, true);
SingleTestMIA mia2 = TestBackendUtils.CreateSingleMIA("Meta2", Cardinality.Inline, true, true);
SingleTestMIA mia3 = TestBackendUtils.CreateSingleMIA("Meta3", Cardinality.Inline, true, true);
ICollection <MediaItemAspectMetadata> requiredMIATypes = new List <MediaItemAspectMetadata>();
requiredMIATypes.Add(mia1.Metadata);
requiredMIATypes.Add(mia2.Metadata);
IFilter linkedMovieFilter = BooleanCombinationFilter.CombineFilters(
BooleanOperator.And,
new RelationalFilter(mia3.ATTR_INTEGER, RelationalOperator.EQ, 1),
new RelationalFilter(mia3.ATTR_STRING, RelationalOperator.EQ, "test"));
Guid movieType = new Guid("bbbbbbbb-2222-2222-2222-bbbbbbbbbbbb");
Guid actorType = new Guid("cccccccc-3333-3333-3333-cccccccccccc");
IFilter filter = new FilteredRelationshipFilter(actorType, movieType, linkedMovieFilter);
MIAQueryBuilder builder = new MIAQueryBuilder(MockCore.Management, new List <QueryAttribute>(), null, requiredMIATypes, new List <MediaItemAspectMetadata>(), filter, null, null);
string mediaItemIdAlias = null;
IDictionary <MediaItemAspectMetadata, string> miamAliases = null;
IDictionary <QueryAttribute, string> attributeAliases = null;
string statementStr = null;
IList <BindVar> bindVars = null;
builder.GenerateSqlStatement(out mediaItemIdAlias, out miamAliases, out attributeAliases, out statementStr, out bindVars);
Console.WriteLine("mediaItemIdAlias: {0}", mediaItemIdAlias);
Console.WriteLine("miamAliases: [{0}]", string.Join(",", miamAliases));
Console.WriteLine("attributeAliases: [{0}]", string.Join(",", attributeAliases));
Console.WriteLine("statementStr: {0}", statementStr);
Console.WriteLine("bindVars: [{0}]", string.Join(",", bindVars));
Assert.AreEqual("A0", mediaItemIdAlias, "Media item ID alias");
Assert.AreEqual(CreateMIAMAliases(mia1.Metadata, "A1", mia2.Metadata, "A2"), miamAliases, "MIAM aliases");
Assert.AreEqual(new Dictionary <QueryAttribute, string>(), attributeAliases, "Attribute aliases");
Assert.AreEqual("SELECT T0.MEDIA_ITEM_ID A0, T0.MEDIA_ITEM_ID A1, T1.MEDIA_ITEM_ID A2 FROM M_META1 T0 INNER JOIN M_META2 T1 ON T1.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID " +
" WHERE T0.MEDIA_ITEM_ID IN(SELECT R1.MEDIA_ITEM_ID FROM M_RELATIONSHIP R1 WHERE R1.ROLE=@V0 AND R1.LINKEDROLE=@V1 AND R1.LINKEDID IN( " +
"SELECT TS.A0 FROM (SELECT T0.MEDIA_ITEM_ID A0, T1.MEDIA_ITEM_ID A1 FROM MEDIA_ITEMS T0 LEFT OUTER JOIN M_META3 T1 ON T0.MEDIA_ITEM_ID = T1.MEDIA_ITEM_ID " +
" WHERE (T1.ATTR_INTEGER = @V2 AND T1.ATTR_STRING = @V3)) TS) " +
"UNION SELECT R1.LINKEDID FROM M_RELATIONSHIP R1 WHERE R1.LINKEDROLE=@V0 AND R1.ROLE=@V1 AND R1.MEDIA_ITEM_ID IN( SELECT TS.A0 FROM (" +
"SELECT T0.MEDIA_ITEM_ID A0, T1.MEDIA_ITEM_ID A1 FROM MEDIA_ITEMS T0 LEFT OUTER JOIN M_META3 T1 ON T0.MEDIA_ITEM_ID = T1.MEDIA_ITEM_ID " +
" WHERE (T1.ATTR_INTEGER = @V2 AND T1.ATTR_STRING = @V3)) TS))", statementStr, "Statement");
Assert.AreEqual(new List <BindVar>
{
new BindVar("V0", actorType, typeof(Guid)),
new BindVar("V1", movieType, typeof(Guid)),
new BindVar("V2", 1, typeof(int)),
new BindVar("V3", "test", typeof(string))
}, bindVars, "Bind vars");
}
/// <summary>
/// Combines the <paramref name="currentFilter"/> with a <see cref="FilteredRelationshipFilter"/> with
/// the specified <paramref name="role"/>, <paramref name="linkedRole"/> and <paramref name="subFilter"/>.
/// </summary>
/// <param name="currentFilter">The filter to combine.</param>
/// <param name="role">The role to use for the <see cref="RelationshipFilter"/>.</param>
/// <param name="linkedRole">The linked role to use for the <see cref="RelationshipFilter"/>.</param>
/// <param name="subFilter">The filter to use for the <see cref="RelationshipFilter"/>.</param>
/// <param name="ignoreNullSubFilter">Whether to not combine the filters if <paramref name="subFilter"/> is <c>null</c>.</param>
/// <returns></returns>
protected static IFilter CombineWithFilteredRelationship(IFilter currentFilter, Guid role, Guid linkedRole, IFilter subFilter, bool ignoreNullSubFilter)
{
if (subFilter == null && ignoreNullSubFilter)
{
return(currentFilter);
}
IFilter filteredRelationship = new FilteredRelationshipFilter(role, linkedRole, subFilter);
return(BooleanCombinationFilter.CombineFilters(BooleanOperator.And, currentFilter, filteredRelationship));
}
public override ICollection <FilterValue> GetAvailableValues(IEnumerable <Guid> necessaryMIATypeIds, IFilter selectAttributeFilter, IFilter filter)
{
IContentDirectory cd = ServiceRegistration.Get <IServerConnectionManager>().ContentDirectory;
if (cd == null)
{
throw new NotConnectedException("The MediaLibrary is not connected");
}
Guid? userProfile = null;
IUserManagement userProfileDataManagement = ServiceRegistration.Get <IUserManagement>();
if (userProfileDataManagement != null && userProfileDataManagement.IsValidUser)
{
userProfile = userProfileDataManagement.CurrentUser.ProfileId;
}
IEnumerable <Guid> mias = _necessaryMIATypeIds ?? necessaryMIATypeIds;
IEnumerable <Guid> optMias = _optionalMIATypeIds != null?_optionalMIATypeIds.Except(mias) : null;
IFilter queryFilter;
if (filter != null)
{
queryFilter = new FilteredRelationshipFilter(_role, filter);
}
else
{
queryFilter = new RelationshipFilter(_role, _linkedRole, Guid.Empty);
}
MediaItemQuery query = new MediaItemQuery(mias, optMias, queryFilter);
if (_sortInformation != null)
{
query.SortInformation = new List <SortInformation> {
_sortInformation
}
}
;
IList <MediaItem> items = cd.Search(query, true, userProfile, ShowVirtualSetting.ShowVirtualMedia(necessaryMIATypeIds));
IList <FilterValue> result = new List <FilterValue>(items.Count);
foreach (MediaItem item in items)
{
string name;
MediaItemAspect.TryGetAttribute(item.Aspects, MediaAspect.ATTR_TITLE, out name);
result.Add(new FilterValue(name,
new RelationshipFilter(_linkedRole, _role, item.MediaItemId),
null,
item,
this));
}
return(result);
}
private IList <MediaItem> GetItems(string sortCriteria)
{
IMediaLibrary library = ServiceRegistration.Get <IMediaLibrary>();
IFilter filter = new FilteredRelationshipFilter(_role, _linkedRole, AppendUserFilter(null, _necessaryMIAs));
MediaItemQuery query = new MediaItemQuery(NECESSARY_PERSON_MIA_TYPE_IDS, filter)
{
SortInformation = new List <ISortInformation> {
new AttributeSortInformation(PersonAspect.ATTR_PERSON_NAME, SortDirection.Ascending)
},
};
return(library.Search(query, true, UserId, false));
}
protected virtual void CompileStatementParts(MIA_Management miaManagement, IFilter filter, Namespace ns, BindVarNamespace bvNamespace,
ICollection <MediaItemAspectMetadata> requiredMIATypes, string outerMIIDJoinVariable, ICollection <TableJoin> tableJoins,
IList <object> resultParts, IList <BindVar> resultBindVars)
{
if (filter == null)
{
return;
}
MediaItemIdFilter mediaItemIdFilter = filter as MediaItemIdFilter;
if (mediaItemIdFilter != null)
{
ICollection <Guid> mediaItemIds = mediaItemIdFilter.MediaItemIds;
if (mediaItemIds.Count == 0)
{
resultParts.Add("1 = 2");
}
else
{
if (mediaItemIds.Count == 1)
{
resultParts.Add(outerMIIDJoinVariable);
BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), mediaItemIds.First(), typeof(Guid));
resultParts.Add(" = @" + bindVar.Name);
resultBindVars.Add(bindVar);
}
else
{
bool first = true;
ICollection <string> clusterExpressions = new List <string>();
foreach (IList <Guid> mediaItemIdsCluster in CollectionUtils.Cluster(mediaItemIds, MAX_IN_VALUES_SIZE))
{
IList <string> bindVarRefs = new List <string>(MAX_IN_VALUES_SIZE);
foreach (Guid mediaItemId in mediaItemIdsCluster)
{
BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), mediaItemId, typeof(Guid));
bindVarRefs.Add("@" + bindVar.Name);
resultBindVars.Add(bindVar);
}
if (!first)
{
resultParts.Add(" OR ");
}
first = false;
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(" IN (" + StringUtils.Join(", ", bindVarRefs) + ")");
}
resultParts.Add(StringUtils.Join(" OR ", clusterExpressions));
}
}
return;
}
BooleanCombinationFilter boolFilter = filter as BooleanCombinationFilter;
if (boolFilter != null && boolFilter.Operator == BooleanOperator.And && boolFilter.Operands.Count > 1 && boolFilter.Operands.ToList().All(x => x is IAttributeFilter))
{
ICollection <IFilter> remainingOperands = new List <IFilter>();
// Special case to do multiple MIA boolean logic first
IDictionary <Guid, ICollection <IAttributeFilter> > multiGroups = new Dictionary <Guid, ICollection <IAttributeFilter> >();
foreach (IAttributeFilter operand in boolFilter.Operands)
{
MultipleMediaItemAspectMetadata mmiam = operand.AttributeType.ParentMIAM as MultipleMediaItemAspectMetadata;
if (mmiam != null)
{
Guid key = operand.AttributeType.ParentMIAM.AspectId;
if (!multiGroups.ContainsKey(key))
{
multiGroups[key] = new List <IAttributeFilter>();
}
multiGroups[key].Add(operand);
}
else
{
remainingOperands.Add(operand);
}
}
if (multiGroups.Keys.Count > 0)
{
bool firstGroup = true;
foreach (ICollection <IAttributeFilter> filterGroup in multiGroups.Values)
{
if (firstGroup)
{
firstGroup = false;
}
else
{
resultParts.Add(" AND ");
}
bool firstItem = true;
foreach (IAttributeFilter filterItem in filterGroup)
{
MediaItemAspectMetadata.AttributeSpecification attributeType = filterItem.AttributeType;
if (firstItem)
{
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(" IN(");
resultParts.Add("SELECT ");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add(" FROM ");
resultParts.Add(miaManagement.GetMIATableName(attributeType.ParentMIAM));
resultParts.Add(" WHERE ");
firstItem = false;
}
else
{
resultParts.Add(" AND ");
}
//Empty filter needs to be handled differently to other IAttribute filters
if (filterItem is EmptyFilter)
{
resultParts.Add(miaManagement.GetMIAAttributeColumnName(attributeType));
resultParts.Add(" IS NULL");
}
else
{
BuildAttributeFilterExpression(filterItem, miaManagement.GetMIAAttributeColumnName(attributeType), bvNamespace, resultParts, resultBindVars);
}
}
resultParts.Add(")");
}
// Process remaining operands ?
if (remainingOperands.Count == 0)
{
return;
}
resultParts.Add(" AND ");
boolFilter.Operands = remainingOperands;
}
}
if (boolFilter != null)
{
int numOperands = boolFilter.Operands.Count;
IEnumerator enumOperands = boolFilter.Operands.GetEnumerator();
if (!enumOperands.MoveNext())
{
return;
}
if (numOperands > 1)
{
resultParts.Add("(");
}
CompileStatementParts(miaManagement, (IFilter)enumOperands.Current, ns, bvNamespace,
requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
while (enumOperands.MoveNext())
{
switch (boolFilter.Operator)
{
case BooleanOperator.And:
resultParts.Add(" AND ");
break;
case BooleanOperator.Or:
resultParts.Add(" OR ");
break;
default:
throw new NotImplementedException(string.Format(
"Boolean filter operator '{0}' isn't supported by the media library", boolFilter.Operator));
}
CompileStatementParts(miaManagement, (IFilter)enumOperands.Current, ns, bvNamespace,
requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
}
if (numOperands > 1)
{
resultParts.Add(")");
}
return;
}
NotFilter notFilter = filter as NotFilter;
if (notFilter != null)
{
resultParts.Add("NOT (");
CompileStatementParts(miaManagement, notFilter.InnerFilter, ns, bvNamespace,
requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
resultParts.Add(")");
return;
}
FalseFilter falseFilter = filter as FalseFilter;
if (falseFilter != null)
{
resultParts.Add("1 = 2");
return;
}
// Must be done before checking IAttributeFilter - EmptyFilter is also an IAttributeFilter but must be
// compiled in a different way
EmptyFilter emptyFilter = filter as EmptyFilter;
if (emptyFilter != null)
{
MediaItemAspectMetadata.AttributeSpecification attributeType = emptyFilter.AttributeType;
requiredMIATypes.Add(attributeType.ParentMIAM);
Cardinality cardinality = attributeType.Cardinality;
if (cardinality == Cardinality.Inline || cardinality == Cardinality.ManyToOne)
{
resultParts.Add(new QueryAttribute(attributeType));
resultParts.Add(" IS NULL"); // MTO attributes are joined with left outer joins and thus can also be checked for NULL
}
else if (cardinality == Cardinality.OneToMany)
{
resultParts.Add("NOT EXISTS(");
resultParts.Add("SELECT V.");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add(" FROM ");
resultParts.Add(miaManagement.GetMIACollectionAttributeTableName(attributeType));
resultParts.Add(" V WHERE V.");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add("=");
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(")");
}
else if (cardinality == Cardinality.ManyToMany)
{
resultParts.Add("NOT EXISTS(");
resultParts.Add("SELECT NM.");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add(" FROM ");
resultParts.Add(miaManagement.GetMIACollectionAttributeNMTableName(attributeType));
resultParts.Add(" NM INNER JOIN ");
resultParts.Add(miaManagement.GetMIACollectionAttributeTableName(attributeType));
resultParts.Add(" V ON NM.");
resultParts.Add(MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME);
resultParts.Add(" = V.");
resultParts.Add(MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME);
resultParts.Add(" WHERE NM.");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add("=");
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(")");
}
return;
}
// Must be done before checking IAttributeFilter - EmptyUserDataFilter is also an IAttributeFilter but must be
// compiled in a different way
EmptyUserDataFilter emptyUserDataFilter = filter as EmptyUserDataFilter;
if (emptyUserDataFilter != null)
{
BindVar userIdVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), emptyUserDataFilter.UserProfileId, typeof(Guid));
resultParts.Add("NOT EXISTS(");
resultParts.Add("SELECT ");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add(" FROM ");
resultParts.Add(UserProfileDataManagement_SubSchema.USER_MEDIA_ITEM_DATA_TABLE_NAME);
resultParts.Add(" WHERE ");
resultParts.Add(UserProfileDataManagement_SubSchema.USER_PROFILE_ID_COL_NAME);
resultParts.Add(" = @" + userIdVar.Name);
resultBindVars.Add(userIdVar);
resultParts.Add(" AND ");
resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_KEY_COL_NAME);
resultParts.Add(" = '");
resultParts.Add(emptyUserDataFilter.UserDataKey);
resultParts.Add("' AND ");
resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_VALUE_COL_NAME);
resultParts.Add(" IS NOT NULL ");
resultParts.Add(" AND ");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add("=");
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(")");
return;
}
RelationshipFilter relationshipFilter = filter as RelationshipFilter;
if (relationshipFilter != null)
{
BindVar linkedMediaItemVar = null;
if (relationshipFilter.LinkedMediaItemId != Guid.Empty)
{
linkedMediaItemVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), relationshipFilter.LinkedMediaItemId, typeof(Guid));
resultBindVars.Add(linkedMediaItemVar);
}
BindVar roleVar = null;
if (relationshipFilter.Role != Guid.Empty)
{
roleVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), relationshipFilter.Role, typeof(Guid));
resultBindVars.Add(roleVar);
}
BindVar linkedRoleVar = null;
if (relationshipFilter.LinkedRole != Guid.Empty)
{
linkedRoleVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), relationshipFilter.LinkedRole, typeof(Guid));
resultBindVars.Add(linkedRoleVar);
}
if (roleVar != null || linkedRoleVar != null)
{
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(" IN(");
resultParts.Add("SELECT R1.");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add(" FROM ");
resultParts.Add(miaManagement.GetMIATableName(RelationshipAspect.Metadata));
resultParts.Add(" R1");
if (linkedMediaItemVar != null)
{
resultParts.Add(" WHERE R1." + miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ID));
resultParts.Add("=@" + linkedMediaItemVar.Name);
}
else
{
resultParts.Add(" WHERE 1=1");
}
if (roleVar != null)
{
resultParts.Add(" AND R1.");
resultParts.Add(miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_ROLE));
resultParts.Add("=@" + roleVar.Name);
}
if (linkedRoleVar != null)
{
resultParts.Add(" AND R1.");
resultParts.Add(miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ROLE));
resultParts.Add("=@" + linkedRoleVar.Name);
}
resultParts.Add(" UNION ");
resultParts.Add("SELECT R2.");
resultParts.Add(miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ID));
resultParts.Add(" FROM ");
resultParts.Add(miaManagement.GetMIATableName(RelationshipAspect.Metadata));
resultParts.Add(" R2");
if (linkedMediaItemVar != null)
{
resultParts.Add(" WHERE R2." + MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add("=@" + linkedMediaItemVar.Name);
}
else
{
resultParts.Add(" WHERE 1=1");
}
if (roleVar != null)
{
resultParts.Add(" AND R2.");
resultParts.Add(miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ROLE));
resultParts.Add("=@" + roleVar.Name);
}
if (linkedRoleVar != null)
{
resultParts.Add(" AND R2.");
resultParts.Add(miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_ROLE));
resultParts.Add("=@" + linkedRoleVar.Name);
}
resultParts.Add(")");
}
else if (linkedMediaItemVar != null)
{
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(" IN(");
resultParts.Add("@" + linkedMediaItemVar.Name);
resultParts.Add(")");
}
return;
}
FilteredRelationshipFilter filteredRelationshipFilter = filter as FilteredRelationshipFilter;
if (filteredRelationshipFilter != null)
{
BindVar roleVar = null;
if (filteredRelationshipFilter.Role != Guid.Empty)
{
roleVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), filteredRelationshipFilter.Role, typeof(Guid));
resultBindVars.Add(roleVar);
}
string idAlias;
string sqlStatement;
IList <BindVar> bindVars = null;
ICollection <QueryAttribute> requiredAttributes = new List <QueryAttribute>();
RelationshipQueryBuilder filterBuilder = new RelationshipQueryBuilder(miaManagement, requiredAttributes, requiredMIATypes, filteredRelationshipFilter.Filter, bvNamespace.BindVarCounter);
filterBuilder.GenerateSqlStatement(out idAlias, out sqlStatement, out bindVars);
if (roleVar != null)
{
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(" IN(");
resultParts.Add("SELECT R1.");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add(" FROM ");
resultParts.Add(miaManagement.GetMIATableName(RelationshipAspect.Metadata));
resultParts.Add(" R1");
if (!string.IsNullOrEmpty(sqlStatement))
{
resultParts.Add(" WHERE R1." + miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ID));
resultParts.Add(" IN(SELECT TS." + idAlias + " FROM (");
resultParts.Add(sqlStatement);
resultParts.Add(") TS)");
}
else
{
resultParts.Add(" WHERE 1=1");
}
resultParts.Add(" AND R1.");
resultParts.Add(miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_ROLE));
resultParts.Add("=@" + roleVar.Name);
resultParts.Add(" UNION ");
resultParts.Add("SELECT R2.");
resultParts.Add(miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ID));
resultParts.Add(" FROM ");
resultParts.Add(miaManagement.GetMIATableName(RelationshipAspect.Metadata));
resultParts.Add(" R2");
if (!string.IsNullOrEmpty(sqlStatement))
{
resultParts.Add(" WHERE R2." + MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add(" IN(SELECT TS." + idAlias + " FROM (");
resultParts.Add(sqlStatement);
resultParts.Add(") TS)");
}
else
{
resultParts.Add(" WHERE 1=1");
}
resultParts.Add(" AND R2.");
resultParts.Add(miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ROLE));
resultParts.Add("=@" + roleVar.Name);
resultParts.Add(" UNION ");
resultParts.Add("SELECT R1.");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add(" FROM ");
resultParts.Add(miaManagement.GetMIATableName(RelationshipAspect.Metadata));
resultParts.Add(" R1");
if (!string.IsNullOrEmpty(sqlStatement))
{
resultParts.Add(" WHERE R1." + MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add(" IN(SELECT TS." + idAlias + " FROM (");
resultParts.Add(sqlStatement);
resultParts.Add(") TS)");
}
else
{
resultParts.Add(" WHERE 1=1");
}
resultParts.Add(" AND R1.");
resultParts.Add(miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_ROLE));
resultParts.Add("=@" + roleVar.Name);
resultParts.Add(" UNION ");
resultParts.Add("SELECT R2.");
resultParts.Add(miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ID));
resultParts.Add(" FROM ");
resultParts.Add(miaManagement.GetMIATableName(RelationshipAspect.Metadata));
resultParts.Add(" R2");
if (!string.IsNullOrEmpty(sqlStatement))
{
resultParts.Add(" WHERE R2." + miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ID));
resultParts.Add(" IN(SELECT TS." + idAlias + " FROM (");
resultParts.Add(sqlStatement);
resultParts.Add(") TS)");
}
else
{
resultParts.Add(" WHERE 1=1");
}
resultParts.Add(" AND R2.");
resultParts.Add(miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ROLE));
resultParts.Add("=@" + roleVar.Name);
resultParts.Add(")");
}
else if (!string.IsNullOrEmpty(sqlStatement))
{
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(" IN(");
resultParts.Add("SELECT TS." + idAlias + " FROM (");
resultParts.Add(sqlStatement);
resultParts.Add(") TS)");
}
if (bindVars != null)
{
bvNamespace.BindVarCounter += bindVars.Count;
foreach (BindVar bindVar in bindVars)
{
resultBindVars.Add(bindVar);
}
}
return;
}
RelationalUserDataFilter relationalUserDataFilter = filter as RelationalUserDataFilter;
if (relationalUserDataFilter != null)
{
BindVar userIdVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), relationalUserDataFilter.UserProfileId, typeof(Guid));
BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), relationalUserDataFilter.FilterValue, typeof(string));
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(" IN(");
resultParts.Add("SELECT ");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add(" FROM ");
resultParts.Add(UserProfileDataManagement_SubSchema.USER_MEDIA_ITEM_DATA_TABLE_NAME);
resultParts.Add(" WHERE ");
resultParts.Add(UserProfileDataManagement_SubSchema.USER_PROFILE_ID_COL_NAME);
resultParts.Add(" = @" + userIdVar.Name);
resultBindVars.Add(userIdVar);
resultParts.Add(" AND ");
resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_KEY_COL_NAME);
resultParts.Add(" = '");
resultParts.Add(relationalUserDataFilter.UserDataKey);
resultParts.Add("' AND ");
resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_VALUE_COL_NAME);
switch (relationalUserDataFilter.Operator)
{
case RelationalOperator.EQ:
resultParts.Add(" = ");
break;
case RelationalOperator.NEQ:
resultParts.Add(" <> ");
break;
case RelationalOperator.LT:
resultParts.Add(" < ");
break;
case RelationalOperator.LE:
resultParts.Add(" <= ");
break;
case RelationalOperator.GT:
resultParts.Add(" > ");
break;
case RelationalOperator.GE:
resultParts.Add(" >= ");
break;
default:
throw new NotImplementedException(string.Format(
"Relational user data filter operator '{0}' isn't supported by the media library", relationalUserDataFilter.Operator));
}
resultParts.Add("@" + bindVar.Name);
resultBindVars.Add(bindVar);
resultParts.Add(")");
return;
}
IAttributeFilter attributeFilter = filter as IAttributeFilter;
if (attributeFilter != null)
{
MediaItemAspectMetadata.AttributeSpecification attributeType = attributeFilter.AttributeType;
if (attributeType.ParentMIAM is MultipleMediaItemAspectMetadata)
{
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(" IN(");
resultParts.Add("SELECT ");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add(" FROM ");
resultParts.Add(miaManagement.GetMIATableName(attributeType.ParentMIAM));
resultParts.Add(" WHERE ");
BuildAttributeFilterExpression(attributeFilter, miaManagement.GetMIAAttributeColumnName(attributeType), bvNamespace, resultParts, resultBindVars);
resultParts.Add(")");
return;
}
// For attribute filters, we have to create different kinds of expressions, depending on the
// cardinality of the attribute to be filtered.
// For Inline and MTO attributes, we simply create
//
// QA [Operator] [Comparison-Value]
//
// for OTM attributes, we create
//
// INNER JOIN [OTM-Value-Table] V ON V.MEDIA_ITEM_ID=[Outer-Join-Variable-Placeholder]
// WHERE [...] and V.VALUE [Operator] [Comparison-Value])
//
// for MTM attributes, we create
//
// INNER JOIN [MTM-NM-Table] NM ON NM.MEDIA_ITEM_ID=[Outer-Join-Variable-Placeholder]
// INNER JOIN [MTM-Value-Table] V ON NM.ID = V.ID
// WHERE [...] AND V.VALUE [Operator] [Comparison-Value])
requiredMIATypes.Add(attributeType.ParentMIAM);
Cardinality cardinality = attributeType.Cardinality;
if (cardinality == Cardinality.Inline || cardinality == Cardinality.ManyToOne)
{
BuildAttributeFilterExpression(attributeFilter, new QueryAttribute(attributeType), bvNamespace,
resultParts, resultBindVars);
}
else if (cardinality == Cardinality.OneToMany)
{
string joinTable = miaManagement.GetMIACollectionAttributeTableName(attributeType);
string attrName;
if (!_innerJoinedTables.TryGetValue(joinTable, out attrName))
{
TableQueryData tqd = new TableQueryData(joinTable);
tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqd,
new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), outerMIIDJoinVariable));
attrName = new RequestedAttribute(tqd, MIA_Management.COLL_ATTR_VALUE_COL_NAME).GetQualifiedName(ns);
_innerJoinedTables.Add(joinTable, attrName);
}
BuildAttributeFilterExpression(attributeFilter, attrName, bvNamespace, resultParts, resultBindVars);
}
else if (cardinality == Cardinality.ManyToMany)
{
string miaCollectionAttributeNMTableName = miaManagement.GetMIACollectionAttributeNMTableName(attributeType);
string attrName;
if (!_innerJoinedTables.TryGetValue(miaCollectionAttributeNMTableName, out attrName))
{
TableQueryData tqdMiaCollectionAttributeNMTable = new TableQueryData(miaCollectionAttributeNMTableName);
tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqdMiaCollectionAttributeNMTable,
new RequestedAttribute(tqdMiaCollectionAttributeNMTable, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), outerMIIDJoinVariable));
TableQueryData tqdMiaCollectionAttributeTable = new TableQueryData(miaManagement.GetMIACollectionAttributeTableName(attributeType));
tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqdMiaCollectionAttributeTable,
new RequestedAttribute(tqdMiaCollectionAttributeNMTable, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME),
new RequestedAttribute(tqdMiaCollectionAttributeTable, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME)));
attrName = tqdMiaCollectionAttributeTable.GetAlias(ns) + "." + MIA_Management.COLL_ATTR_VALUE_COL_NAME;
_innerJoinedTables.Add(miaCollectionAttributeNMTableName, attrName);
}
BuildAttributeFilterExpression(attributeFilter, attrName, bvNamespace, resultParts, resultBindVars);
}
return;
}
throw new InvalidDataException("Filter type '{0}' isn't supported by the media library", filter.GetType().Name);
}
