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); }