public static CompiledGroupedAttributeValueQuery Compile(MIA_Management miaManagement, IEnumerable <Guid> necessaryRequestedMIATypeIDs, MediaItemAspectMetadata.AttributeSpecification selectKeyAttribute, MediaItemAspectMetadata.AttributeSpecification selectValueAttribute, IAttributeFilter selectAttributeFilter, SelectProjectionFunction selectProjectionFunction, Type projectionValueType, IFilter filter, IFilter subqueryFilter) { IDictionary <Guid, MediaItemAspectMetadata> availableMIATypes = miaManagement.ManagedMediaItemAspectTypes; // If we're doing a complex query, we can optimize if we have an extra select attribute filter, i.e. a restriction // on the result set of values. See ComplexAttributeQueryBuilder.GenerateSqlGroupByStatement(). bool simpleQuery = selectValueAttribute.Cardinality == Cardinality.Inline || selectValueAttribute.Cardinality == Cardinality.ManyToOne; IFilter combinedFilter = simpleQuery ? BooleanCombinationFilter.CombineFilters(BooleanOperator.And, new IFilter[] { filter, selectAttributeFilter }) : filter; selectAttributeFilter = simpleQuery ? null : selectAttributeFilter; ICollection <MediaItemAspectMetadata> necessaryMIATypes = new List <MediaItemAspectMetadata>(); // Raise exception if necessary MIA types are not present foreach (Guid miaTypeID in necessaryRequestedMIATypeIDs) { MediaItemAspectMetadata miam; if (!availableMIATypes.TryGetValue(miaTypeID, out miam)) { throw new InvalidDataException("Necessary requested MIA type of ID '{0}' is not present in the media library", miaTypeID); } necessaryMIATypes.Add(miam); } return(new CompiledGroupedAttributeValueQuery(miaManagement, necessaryMIATypes, selectKeyAttribute, selectValueAttribute, selectAttributeFilter, selectProjectionFunction, projectionValueType, combinedFilter, subqueryFilter)); }
public static ICollection GetCustom(Type type, Type dataType, IAttributeFilter af) { Attribute[] custom = GetCustom(type); ArrayList list = new ArrayList(custom.Length); foreach (IIdentAttribute attribute in custom) { if (attribute.DataType.IsAssignableFrom(dataType) && ((af == null) || af.Filter(dataType, attribute))) { list.Add(attribute); } } return(list); }
public CompiledGroupedAttributeValueQuery( MIA_Management miaManagement, IEnumerable <MediaItemAspectMetadata> necessaryRequestedMIATypes, MediaItemAspectMetadata.AttributeSpecification selectedAttribute, IAttributeFilter selectAttributeFilter, SelectProjectionFunction selectProjectionFunction, Type projectionValueType, IFilter filter) { _miaManagement = miaManagement; _necessaryRequestedMIATypes = necessaryRequestedMIATypes; _selectAttribute = selectedAttribute; _selectAttributeFilter = selectAttributeFilter; _selectProjectionFunction = selectProjectionFunction; _projectionValueType = projectionValueType; _filter = filter; }
public CompiledGroupedAttributeValueQuery( MIA_Management miaManagement, IEnumerable<MediaItemAspectMetadata> necessaryRequestedMIATypes, MediaItemAspectMetadata.AttributeSpecification selectedAttribute, IAttributeFilter selectAttributeFilter, SelectProjectionFunction selectProjectionFunction, Type projectionValueType, IFilter filter) { _miaManagement = miaManagement; _necessaryRequestedMIATypes = necessaryRequestedMIATypes; _selectAttribute = selectedAttribute; _selectAttributeFilter = selectAttributeFilter; _selectProjectionFunction = selectProjectionFunction; _projectionValueType = projectionValueType; _filter = filter; }
/// <summary> /// Generates a statement to query the distinct values of the complex <see cref="QueryAttribute"/>, together with their /// occurence count. /// </summary> /// <param name="queryAttributeFilter">Additional filter which is defined on the <see cref="QueryAttribute"/>. /// That filter COULD be part of the <see cref="Filter"/> but this method can highly optimize a filter on the /// query attribute, so a filter on that attribute should be given in this parameter and should not be part of the <see cref="Filter"/>. /// </param> /// <param name="valueAlias">Alias for the value column.</param> /// <param name="groupSizeAlias">Alias for the column containing the number of items in each group.</param> /// <param name="statementStr">Statement which was built by this method.</param> /// <param name="bindVars">Bind variables to be inserted into placeholders in the returned <paramref name="statementStr"/>.</param> public void GenerateSqlGroupByStatement(IAttributeFilter queryAttributeFilter, out string valueAlias, out string groupSizeAlias, out string statementStr, out IList <BindVar> bindVars) { Namespace ns = new Namespace(); BindVarNamespace bvNamespace = new BindVarNamespace(); // Contains a mapping of each queried (=selected or filtered) attribute to its request attribute instance // data (which holds its requested query table instance) IDictionary <QueryAttribute, RequestedAttribute> requestedAttributes = new Dictionary <QueryAttribute, RequestedAttribute>(); // Dictionary containing as key the requested MIAM instance OR attribute specification of cardinality MTO, // mapped to the table query data to request its contents. IDictionary <object, TableQueryData> tableQueries = new Dictionary <object, TableQueryData>(); // Contains the same tables as the tableQueries variable, but in order and enriched with table join data IList <TableJoin> tableJoins = new List <TableJoin>(); // First create the request table query data for the MIA main table and the request attribute for the MIA ID. // We'll need the requested attribute as join attribute soon. MediaItemAspectMetadata queryMIAM = _queryAttribute.ParentMIAM; TableQueryData miaTableQuery = new TableQueryData(_miaManagement.GetMIATableName(queryMIAM)); tableQueries.Add(queryMIAM, miaTableQuery); RequestedAttribute miaIdAttribute = new RequestedAttribute(miaTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME); // Ensure that the tables for all necessary MIAs are requested first (INNER JOIN) foreach (MediaItemAspectMetadata miaType in _necessaryRequestedMIAs) { TableQueryData tqd; if (!tableQueries.TryGetValue(miaType, out tqd)) { tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType); } if (miaType != queryMIAM) { tableJoins.Add(new TableJoin("INNER JOIN", tqd, new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute)); } } CompiledFilter compiledFilter = new CompiledFilter(_miaManagement, _filter, _subqueryFilter, ns, bvNamespace, miaIdAttribute.GetQualifiedName(ns), tableJoins); // Build table query data for each Inline attribute which is part of a filter // + compile query attribute foreach (QueryAttribute attr in compiledFilter.RequiredAttributes) { if (attr.Attr.Cardinality != Cardinality.Inline && attr.Attr.Cardinality != Cardinality.ManyToOne) { continue; } // Tables of Inline and MTO attributes, which are part of a filter, are joined with main table RequestedAttribute ra; RequestSimpleAttribute(attr, tableQueries, tableJoins, "LEFT OUTER JOIN", requestedAttributes, null, miaIdAttribute, out ra); } TableQueryData joinTableQuery; RequestedAttribute valueAttribute; // Build join table for value attribute switch (_queryAttribute.Cardinality) { case Cardinality.OneToMany: joinTableQuery = new TableQueryData(_miaManagement.GetMIACollectionAttributeTableName(_queryAttribute)); tableJoins.Add(new TableJoin("LEFT OUTER JOIN", joinTableQuery, new RequestedAttribute(joinTableQuery, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME), new RequestedAttribute(miaTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME))); valueAttribute = new RequestedAttribute(joinTableQuery, MIA_Management.COLL_ATTR_VALUE_COL_NAME); break; case Cardinality.ManyToMany: joinTableQuery = new TableQueryData(_miaManagement.GetMIACollectionAttributeNMTableName(_queryAttribute)); tableJoins.Add(new TableJoin("LEFT OUTER JOIN", joinTableQuery, new RequestedAttribute(joinTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), new RequestedAttribute(miaTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME))); TableQueryData collAttrTableQuery = new TableQueryData(_miaManagement.GetMIACollectionAttributeTableName(_queryAttribute)); tableJoins.Add(new TableJoin("LEFT OUTER JOIN", collAttrTableQuery, new RequestedAttribute(joinTableQuery, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME), new RequestedAttribute(collAttrTableQuery, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME))); valueAttribute = new RequestedAttribute(collAttrTableQuery, MIA_Management.COLL_ATTR_VALUE_COL_NAME); break; default: throw new IllegalCallException("Media item aspect attributes of cardinality '{0}' cannot be requested via the {1}", _queryAttribute.Cardinality, GetType().Name); } // Selected attributes string valueDeclaration = _selectProjectionFunction == null? valueAttribute.GetDeclarationWithAlias(ns, out valueAlias) : valueAttribute.GetDeclarationWithAlias(ns, _selectProjectionFunction, out valueAlias); groupSizeAlias = "C"; StringBuilder result = new StringBuilder("SELECT COUNT(V.ID) "); result.Append(groupSizeAlias); result.Append(", V."); result.Append(valueAlias); result.Append(" FROM ("); result.Append("SELECT DISTINCT "); result.Append(miaIdAttribute.GetQualifiedName(ns)); result.Append(" ID, "); result.Append(valueDeclaration); string whereStr = compiledFilter.CreateSqlFilterCondition(ns, requestedAttributes, out bindVars); result.Append(" FROM "); // Always request the mia table result.Append(miaTableQuery.GetDeclarationWithAlias(ns)); result.Append(' '); // Other joined tables foreach (TableJoin tableJoin in tableJoins) { result.Append(tableJoin.GetJoinDeclaration(ns)); result.Append(' '); } if (!string.IsNullOrEmpty(whereStr) || queryAttributeFilter != null) { result.Append("WHERE "); IList <string> filters = new List <string>(2); if (!string.IsNullOrEmpty(whereStr)) { filters.Add(whereStr); } if (queryAttributeFilter != null) { IList <object> resultParts = new List <object>(); CompiledFilter.BuildAttributeFilterExpression(queryAttributeFilter, valueAttribute.GetQualifiedName(ns), bvNamespace, resultParts, bindVars); string filterStr = CompiledFilter.CreateSimpleSqlFilterCondition(ns, resultParts, requestedAttributes); filters.Add(filterStr); } result.Append(StringUtils.Join(" AND ", filters)); } result.Append(") V GROUP BY V."); result.Append(valueAlias); statementStr = result.ToString(); }
protected virtual void CompileStatementParts(MIA_Management miaManagement, IFilter filter, IFilter subqueryFilter, 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; ICollection <IFilter> filterOperands = boolFilter?.Operands; //Work on collection reference to avoid chaning original 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 filterOperands) { 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 "); filterOperands = remainingOperands; } } if (boolFilter != null) { int numOperands = filterOperands.Count; IEnumerator enumOperands = filterOperands.GetEnumerator(); if (!enumOperands.MoveNext()) { return; } if (numOperands > 1) { resultParts.Add("("); } CompileStatementParts(miaManagement, (IFilter)enumOperands.Current, subqueryFilter, 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, subqueryFilter, 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, subqueryFilter, 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 1"); 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 1"); 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 1"); 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(UserProfileDataManagement_SubSchema.USER_DATA_VALUE_COL_NAME); resultParts.Add(" <> '' "); resultParts.Add(" AND "); resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME); resultParts.Add("="); resultParts.Add(outerMIIDJoinVariable); resultParts.Add(")"); return; } AbstractRelationshipFilter relationshipFilter = filter as AbstractRelationshipFilter; if (relationshipFilter != null) { resultParts.Add(outerMIIDJoinVariable); resultParts.Add(" IN("); BuildRelationshipSubquery(relationshipFilter, subqueryFilter, miaManagement, bvNamespace, resultParts, resultBindVars); resultParts.Add(")"); 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("(EXISTS("); resultParts.Add("SELECT 1"); 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); if (relationalUserDataFilter.IncludeEmpty) { resultParts.Add(" OR "); resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_VALUE_COL_NAME); resultParts.Add(" IS NULL "); } resultParts.Add(") AND "); resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME); resultParts.Add("="); resultParts.Add(outerMIIDJoinVariable); resultParts.Add(")"); if (relationalUserDataFilter.IncludeEmpty) { resultParts.Add(" OR NOT EXISTS("); resultParts.Add("SELECT 1"); 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); resultParts.Add(" AND "); resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_KEY_COL_NAME); resultParts.Add(" = '"); resultParts.Add(relationalUserDataFilter.UserDataKey); resultParts.Add("'"); resultParts.Add(" AND "); resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME); resultParts.Add("="); resultParts.Add(outerMIIDJoinVariable); resultParts.Add(")"); } 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); }
/// <summary> /// Builds the actual filter SQL expression <c>[Attribute-Operand] [Operator] [Comparison-Value]</c> for the given /// attribute <paramref name="filter"/>. /// </summary> /// <param name="filter">Attribute filter instance to create the SQL expression for.</param> /// <param name="attributeOperand">Comparison attribute to be used. Depending on the cardinality of the /// to-be-filtered attribute, this will be the inline attribute alias or the attribute alias of the collection /// attribute table.</param> /// <param name="bvNamespace">Namespace used to build bind var names.</param> /// <param name="resultParts">Statement parts for the attribute filter.</param> /// <param name="resultBindVars">Bind variables for the attribute filter.</param> public static void BuildAttributeFilterExpression(IAttributeFilter filter, object attributeOperand, BindVarNamespace bvNamespace, IList <object> resultParts, IList <BindVar> resultBindVars) { Type attributeType = filter.AttributeType.AttributeType; RelationalFilter relationalFilter = filter as RelationalFilter; if (relationalFilter != null) { resultParts.Add(attributeOperand); switch (relationalFilter.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 filter operator '{0}' isn't supported by the media library", relationalFilter.Operator)); } BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), relationalFilter.FilterValue, attributeType); resultParts.Add("@" + bindVar.Name); resultBindVars.Add(bindVar); return; } LikeFilter likeFilter = filter as LikeFilter; if (likeFilter != null) { if (!likeFilter.CaseSensitive) { resultParts.Add("UPPER("); } resultParts.Add(attributeOperand); if (!likeFilter.CaseSensitive) { resultParts.Add(")"); } resultParts.Add(" LIKE "); BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), likeFilter.Expression, attributeType); if (likeFilter.CaseSensitive) { resultParts.Add("@" + bindVar.Name); } else { resultParts.Add("UPPER(@" + bindVar.Name + ")"); } resultBindVars.Add(bindVar); if (likeFilter.EscapeChar.HasValue) { bindVar = new BindVar(bvNamespace.CreateNewBindVarName("E"), likeFilter.EscapeChar.ToString(), typeof(Char)); resultParts.Add(" ESCAPE @" + bindVar.Name); resultBindVars.Add(bindVar); } return; } BetweenFilter betweenFilter = filter as BetweenFilter; if (betweenFilter != null) { resultParts.Add(attributeOperand); resultParts.Add(" BETWEEN "); BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), betweenFilter.Value1, attributeType); resultParts.Add("@" + bindVar.Name); resultBindVars.Add(bindVar); resultParts.Add(" AND "); bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), betweenFilter.Value2, attributeType); resultParts.Add("@" + bindVar.Name); resultBindVars.Add(bindVar); return; } InFilter inFilter = filter as InFilter; if (inFilter != null) { if (inFilter.Values.Count == 0) { resultParts.Add("1 = 2"); // No comparison values means filter is always false return; } int clusterCount = 0; foreach (IList <object> valuesCluster in CollectionUtils.Cluster(inFilter.Values, MAX_IN_VALUES_SIZE)) { if (clusterCount > 0) { resultParts.Add(" OR "); } resultParts.Add(attributeOperand); IList <string> bindVarRefs = new List <string>(MAX_IN_VALUES_SIZE); foreach (object value in valuesCluster) { BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), value, attributeType); bindVarRefs.Add("@" + bindVar.Name); resultBindVars.Add(bindVar); } resultParts.Add(" IN (" + StringUtils.Join(", ", bindVarRefs) + ")"); clusterCount++; } return; } throw new InvalidDataException("Filter type '{0}' isn't supported by the media library", filter.GetType().Name); }
public static CompiledGroupedAttributeValueQuery Compile(MIA_Management miaManagement, IEnumerable<Guid> necessaryRequestedMIATypeIDs, MediaItemAspectMetadata.AttributeSpecification selectAttribute, IAttributeFilter selectAttributeFilter, SelectProjectionFunction selectProjectionFunction, Type projectionValueType, IFilter filter) { IDictionary<Guid, MediaItemAspectMetadata> availableMIATypes = miaManagement.ManagedMediaItemAspectTypes; // If we're doing a complex query, we can optimize if we have an extra select attribute filter, i.e. a restriction // on the result set of values. See ComplexAttributeQueryBuilder.GenerateSqlGroupByStatement(). bool simpleQuery = selectAttribute.Cardinality == Cardinality.Inline || selectAttribute.Cardinality == Cardinality.ManyToOne; IFilter combinedFilter = simpleQuery ? BooleanCombinationFilter.CombineFilters(BooleanOperator.And, new IFilter[] {filter, selectAttributeFilter}) : filter; selectAttributeFilter = simpleQuery ? null : selectAttributeFilter; ICollection<MediaItemAspectMetadata> necessaryMIATypes = new List<MediaItemAspectMetadata>(); // Raise exception if necessary MIA types are not present foreach (Guid miaTypeID in necessaryRequestedMIATypeIDs) { MediaItemAspectMetadata miam; if (!availableMIATypes.TryGetValue(miaTypeID, out miam)) throw new InvalidDataException("Necessary requested MIA type of ID '{0}' is not present in the media library", miaTypeID); necessaryMIATypes.Add(miam); } return new CompiledGroupedAttributeValueQuery(miaManagement, necessaryMIATypes, selectAttribute, selectAttributeFilter, selectProjectionFunction, projectionValueType, combinedFilter); }
/// <summary> /// Builds the actual filter SQL expression <c>[Attribute-Operand] [Operator] [Comparison-Value]</c> for the given /// attribute <paramref name="filter"/>. /// </summary> /// <param name="filter">Attribute filter instance to create the SQL expression for.</param> /// <param name="attributeOperand">Comparison attribute to be used. Depending on the cardinality of the /// to-be-filtered attribute, this will be the inline attribute alias or the attribute alias of the collection /// attribute table.</param> /// <param name="bvNamespace">Namespace used to build bind var names.</param> /// <param name="resultParts">Statement parts for the attribute filter.</param> /// <param name="resultBindVars">Bind variables for the attribute filter.</param> public static void BuildAttributeFilterExpression(IAttributeFilter filter, object attributeOperand, BindVarNamespace bvNamespace, IList<object> resultParts, IList<BindVar> resultBindVars) { Type attributeType = filter.AttributeType.AttributeType; RelationalFilter relationalFilter = filter as RelationalFilter; if (relationalFilter != null) { resultParts.Add(attributeOperand); switch (relationalFilter.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 filter operator '{0}' isn't supported by the media library", relationalFilter.Operator)); } BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), relationalFilter.FilterValue, attributeType); resultParts.Add("@" + bindVar.Name); resultBindVars.Add(bindVar); return; } LikeFilter likeFilter = filter as LikeFilter; if (likeFilter != null) { if (!likeFilter.CaseSensitive) resultParts.Add("UPPER("); resultParts.Add(attributeOperand); if (!likeFilter.CaseSensitive) resultParts.Add(")"); resultParts.Add(" LIKE "); BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), likeFilter.Expression, attributeType); if (likeFilter.CaseSensitive) resultParts.Add("@" + bindVar.Name); else resultParts.Add("UPPER(@" + bindVar.Name + ")"); resultBindVars.Add(bindVar); if (likeFilter.EscapeChar.HasValue) { bindVar = new BindVar(bvNamespace.CreateNewBindVarName("E"), likeFilter.EscapeChar.ToString(), typeof(Char)); resultParts.Add(" ESCAPE @" + bindVar.Name); resultBindVars.Add(bindVar); } return; } BetweenFilter betweenFilter = filter as BetweenFilter; if (betweenFilter != null) { resultParts.Add(attributeOperand); resultParts.Add(" BETWEEN "); BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), betweenFilter.Value1, attributeType); resultParts.Add("@" + bindVar.Name); resultBindVars.Add(bindVar); resultParts.Add(" AND "); bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), betweenFilter.Value2, attributeType); resultParts.Add("@" + bindVar.Name); resultBindVars.Add(bindVar); return; } InFilter inFilter = filter as InFilter; if (inFilter != null) { if (inFilter.Values.Count == 0) { resultParts.Add("1 = 2"); // No comparison values means filter is always false return; } ICollection<string> clusterExpressions = new List<string>(); foreach (IList<object> valuesCluster in CollectionUtils.Cluster(inFilter.Values, MAX_IN_VALUES_SIZE)) { IList<string> bindVarRefs = new List<string>(MAX_IN_VALUES_SIZE); foreach (object value in valuesCluster) { BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), value, attributeType); bindVarRefs.Add("@" + bindVar.Name); resultBindVars.Add(bindVar); } clusterExpressions.Add(" IN (" + StringUtils.Join(", ", bindVarRefs) + ")"); } resultParts.Add(StringUtils.Join(" OR ", clusterExpressions)); return; } throw new InvalidDataException("Filter type '{0}' isn't supported by the media library", filter.GetType().Name); }
protected 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) { 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; } IAttributeFilter attributeFilter = filter as IAttributeFilter; if (attributeFilter != null) { // 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]) MediaItemAspectMetadata.AttributeSpecification attributeType = attributeFilter.AttributeType; 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); }
/// <summary> /// Generates a statement to query the distinct values of the complex <see cref="QueryAttribute"/>, together with their /// occurence count. /// </summary> /// <param name="queryAttributeFilter">Additional filter which is defined on the <see cref="QueryAttribute"/>. /// That filter COULD be part of the <see cref="Filter"/> but this method can highly optimize a filter on the /// query attribute, so a filter on that attribute should be given in this parameter and should not be part of the <see cref="Filter"/>. /// </param> /// <param name="valueAlias">Alias for the value column.</param> /// <param name="groupSizeAlias">Alias for the column containing the number of items in each group.</param> /// <param name="statementStr">Statement which was built by this method.</param> /// <param name="bindVars">Bind variables to be inserted into placeholders in the returned <paramref name="statementStr"/>.</param> public void GenerateSqlGroupByStatement(IAttributeFilter queryAttributeFilter, out string valueAlias, out string groupSizeAlias, out string statementStr, out IList<BindVar> bindVars) { Namespace ns = new Namespace(); BindVarNamespace bvNamespace = new BindVarNamespace(); // Contains a mapping of each queried (=selected or filtered) attribute to its request attribute instance // data (which holds its requested query table instance) IDictionary<QueryAttribute, RequestedAttribute> requestedAttributes = new Dictionary<QueryAttribute, RequestedAttribute>(); // Dictionary containing as key the requested MIAM instance OR attribute specification of cardinality MTO, // mapped to the table query data to request its contents. IDictionary<object, TableQueryData> tableQueries = new Dictionary<object, TableQueryData>(); // Contains the same tables as the tableQueries variable, but in order and enriched with table join data IList<TableJoin> tableJoins = new List<TableJoin>(); // First create the request table query data for the MIA main table and the request attribute for the MIA ID. // We'll need the requested attribute as join attribute soon. MediaItemAspectMetadata queryMIAM = _queryAttribute.ParentMIAM; TableQueryData miaTableQuery = new TableQueryData(_miaManagement.GetMIATableName(queryMIAM)); tableQueries.Add(queryMIAM, miaTableQuery); RequestedAttribute miaIdAttribute = new RequestedAttribute(miaTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME); // Ensure that the tables for all necessary MIAs are requested first (INNER JOIN) foreach (MediaItemAspectMetadata miaType in _necessaryRequestedMIAs) { TableQueryData tqd; if (!tableQueries.TryGetValue(miaType, out tqd)) tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType); if (miaType != queryMIAM) tableJoins.Add(new TableJoin("INNER JOIN", tqd, new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute)); } CompiledFilter compiledFilter = new CompiledFilter(_miaManagement, _filter, ns, bvNamespace, miaIdAttribute.GetQualifiedName(ns), tableJoins); // Build table query data for each Inline attribute which is part of a filter // + compile query attribute foreach (QueryAttribute attr in compiledFilter.RequiredAttributes) { if (attr.Attr.Cardinality != Cardinality.Inline && attr.Attr.Cardinality != Cardinality.ManyToOne) continue; // Tables of Inline and MTO attributes, which are part of a filter, are joined with main table RequestedAttribute ra; RequestSimpleAttribute(attr, tableQueries, tableJoins, "LEFT OUTER JOIN", requestedAttributes, null, miaIdAttribute, out ra); } TableQueryData joinTableQuery; RequestedAttribute valueAttribute; // Build join table for value attribute switch (_queryAttribute.Cardinality) { case Cardinality.OneToMany: joinTableQuery = new TableQueryData(_miaManagement.GetMIACollectionAttributeTableName(_queryAttribute)); tableJoins.Add(new TableJoin("LEFT OUTER JOIN", joinTableQuery, new RequestedAttribute(joinTableQuery, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME), new RequestedAttribute(miaTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME))); valueAttribute = new RequestedAttribute(joinTableQuery, MIA_Management.COLL_ATTR_VALUE_COL_NAME); break; case Cardinality.ManyToMany: joinTableQuery = new TableQueryData(_miaManagement.GetMIACollectionAttributeNMTableName(_queryAttribute)); tableJoins.Add(new TableJoin("LEFT OUTER JOIN", joinTableQuery, new RequestedAttribute(joinTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), new RequestedAttribute(miaTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME))); TableQueryData collAttrTableQuery = new TableQueryData(_miaManagement.GetMIACollectionAttributeTableName(_queryAttribute)); tableJoins.Add(new TableJoin("LEFT OUTER JOIN", collAttrTableQuery, new RequestedAttribute(joinTableQuery, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME), new RequestedAttribute(collAttrTableQuery, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME))); valueAttribute = new RequestedAttribute(collAttrTableQuery, MIA_Management.COLL_ATTR_VALUE_COL_NAME); break; default: throw new IllegalCallException("Media item aspect attributes of cardinality '{0}' cannot be requested via the {1}", _queryAttribute.Cardinality, GetType().Name); } // Selected attributes string valueDeclaration = _selectProjectionFunction == null ? valueAttribute.GetDeclarationWithAlias(ns, out valueAlias) : valueAttribute.GetDeclarationWithAlias(ns, _selectProjectionFunction, out valueAlias); groupSizeAlias = "C"; StringBuilder result = new StringBuilder("SELECT COUNT(V.ID) "); result.Append(groupSizeAlias); result.Append(", V."); result.Append(valueAlias); result.Append(" FROM ("); result.Append("SELECT DISTINCT "); result.Append(miaIdAttribute.GetQualifiedName(ns)); result.Append(" ID, "); result.Append(valueDeclaration); string whereStr = compiledFilter.CreateSqlFilterCondition(ns, requestedAttributes, out bindVars); result.Append(" FROM "); // Always request the mia table result.Append(miaTableQuery.GetDeclarationWithAlias(ns)); result.Append(' '); // Other joined tables foreach (TableJoin tableJoin in tableJoins) { result.Append(tableJoin.GetJoinDeclaration(ns)); result.Append(' '); } if (!string.IsNullOrEmpty(whereStr) || queryAttributeFilter != null) { result.Append("WHERE "); IList<string> filters = new List<string>(2); if (!string.IsNullOrEmpty(whereStr)) filters.Add(whereStr); if (queryAttributeFilter != null) { IList<object> resultParts = new List<object>(); CompiledFilter.BuildAttributeFilterExpression(queryAttributeFilter, valueAttribute.GetQualifiedName(ns), bvNamespace, resultParts, bindVars); string filterStr = CompiledFilter.CreateSimpleSqlFilterCondition(ns, resultParts, requestedAttributes); filters.Add(filterStr); } result.Append(StringUtils.Join(" AND ", filters)); } result.Append(") V GROUP BY V."); result.Append(valueAlias); statementStr = result.ToString(); }