/// <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 void GenerateSqlStatement(bool groupByValues, IDictionary <MediaItemAspectMetadata, string> miamAliases, out string mediaItemIdOrGroupSizeAlias, out IDictionary <QueryAttribute, string> attributeAliases, 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>(); attributeAliases = new Dictionary <QueryAttribute, string>(); // Contains a list of qualified attribute names for all select attributes - needed for GROUP BY-expressions ICollection <string> qualifiedGroupByAliases = new List <string>(); // Contains a list of compiled select attribute declarations. We need this in a separate list (in contrast to using // the selectAttributes list together with the compiledAttributes map) because it might be the case that // an attribute is requested twice. In that rare case, we need a new alias name for it. IList <string> selectAttributeDeclarations = new List <string>(); // 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>(); // Contains all table query data for MIA type tables IDictionary <MediaItemAspectMetadata, TableQueryData> miaTypeTableQueries = new Dictionary <MediaItemAspectMetadata, TableQueryData>(); // Albert, 2012-01-29: Optimized query, don't join with media items table, if we have necessary requested MIAs. In that case, // we can use one of their media item ids. //// 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. //TableQueryData miaTableQuery = new TableQueryData(MediaLibrary_SubSchema.MEDIA_ITEMS_TABLE_NAME); //RequestedAttribute miaIdAttribute = new RequestedAttribute(miaTableQuery, MediaLibrary_SubSchema.MEDIA_ITEMS_ITEM_ID_COL_NAME); RequestedAttribute miaIdAttribute = null; // Lazy initialized below // Contains CompiledSortInformation instances for each sort information instance IList <CompiledSortInformation> compiledSortInformation = null; List <BindVar> sqlVars = new List <BindVar>(); // Ensure that the tables for all necessary MIAs are requested first (INNER JOIN) foreach (MediaItemAspectMetadata miaType in _necessaryRequestedMIAs) { if (miaType.IsTransientAspect) { continue; } if (tableQueries.ContainsKey(miaType)) { // We only come here if miaType was already queried as necessary MIA, so optimize redundant entry continue; } TableQueryData tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType); miaTypeTableQueries.Add(miaType, tqd); RequestedAttribute ra; // The first table join has invalid join attributes because miaIdAttribute is still null - but only the join table attribute is necessary // for the the first table - see below tableJoins.Add(new TableJoin("INNER JOIN", tqd, ra = new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute)); if (miaIdAttribute == null) { miaIdAttribute = ra; } } if (miaIdAttribute == null) { // If we didn't request any necessary MIA types, we have to add an artificial table for the miaIdAttribute TableQueryData miaTableQuery = new TableQueryData(MediaLibrary_SubSchema.MEDIA_ITEMS_TABLE_NAME); tableJoins.Add(new TableJoin("INNER JOIN", miaTableQuery, null, null)); // First table join has invalid join attributes - not needed for first table miaIdAttribute = new RequestedAttribute(miaTableQuery, MediaLibrary_SubSchema.MEDIA_ITEMS_ITEM_ID_COL_NAME); } // Ensure that the tables for all optional MIAs are requested first (LEFT OUTER JOIN) // That is necessary to make empty optional MIA types available in the result foreach (MediaItemAspectMetadata miaType in _optionalRequestedMIAs) { if (miaType.IsTransientAspect) { continue; } if (tableQueries.ContainsKey(miaType)) { // We only come here if miaType was already queried as necessary or optional MIA, so optimize redundant entry continue; } TableQueryData tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType); miaTypeTableQueries.Add(miaType, tqd); tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqd, new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute)); } // Build table query data for each selected Inline and MTO attribute // + select query attribute // + add alias to selectAttributeDeclarations foreach (QueryAttribute attr in _selectAttributes) { if (attr.Attr.ParentMIAM.IsTransientAspect) { continue; } RequestedAttribute ra; RequestSimpleAttribute(attr, tableQueries, tableJoins, "LEFT OUTER JOIN", requestedAttributes, miaTypeTableQueries, miaIdAttribute, out ra); string alias; selectAttributeDeclarations.Add(_selectProjectionFunction == null ? ra.GetDeclarationWithAlias(ns, out alias) : ra.GetDeclarationWithAlias(ns, _selectProjectionFunction, out alias)); attributeAliases.Add(attr, alias); qualifiedGroupByAliases.Add(ra.GetAlias(ns)); } CompiledFilter compiledFilter = CreateCompiledFilter(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.ParentMIAM.IsTransientAspect) { continue; } 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, miaTypeTableQueries, miaIdAttribute, out ra); } // Build table query data for each sort attribute if (_sortInformation != null) { compiledSortInformation = new List <CompiledSortInformation>(); BindVar userVar = null; foreach (ISortInformation sortInformation in _sortInformation) { AttributeSortInformation attributeSort = sortInformation as AttributeSortInformation; if (attributeSort != null) { if (attributeSort.AttributeType.ParentMIAM.IsTransientAspect) { continue; } MediaItemAspectMetadata.AttributeSpecification attr = attributeSort.AttributeType; if (attr.Cardinality != Cardinality.Inline && attr.Cardinality != Cardinality.ManyToOne) { // Sorting can only be done for Inline and MTO attributes continue; } RequestedAttribute ra; RequestSimpleAttribute(new QueryAttribute(attr), tableQueries, tableJoins, "LEFT OUTER JOIN", requestedAttributes, miaTypeTableQueries, miaIdAttribute, out ra); compiledSortInformation.Add(new CompiledSortInformation(ra, attributeSort.Direction)); continue; } DataSortInformation dataSort = sortInformation as DataSortInformation; if (dataSort != null && _userProfileId.HasValue) { TableQueryData tqd = new TableQueryData(UserProfileDataManagement.UserProfileDataManagement_SubSchema.USER_MEDIA_ITEM_DATA_TABLE_NAME); RequestedAttribute ra = new RequestedAttribute(tqd, UserProfileDataManagement.UserProfileDataManagement_SubSchema.USER_DATA_VALUE_COL_NAME); compiledSortInformation.Add(new CompiledSortInformation(ra, dataSort.Direction)); if (userVar == null) { userVar = new BindVar("UID", _userProfileId.Value, typeof(Guid)); sqlVars.Add(userVar); } TableJoin join = new TableJoin("LEFT OUTER JOIN", tqd, new RequestedAttribute(tqd, UserProfileDataManagement.UserProfileDataManagement_SubSchema.USER_PROFILE_ID_COL_NAME), "@" + userVar.Name); join.AddCondition(new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute); join.AddCondition(new RequestedAttribute(tqd, UserProfileDataManagement.UserProfileDataManagement_SubSchema.USER_DATA_KEY_COL_NAME), $"'{dataSort.UserDataKey}'"); tableJoins.Add(join); } } } StringBuilder result = new StringBuilder("SELECT "); string groupClause = StringUtils.Join(", ", qualifiedGroupByAliases.Select(alias => "V." + alias)); if (groupByValues) { mediaItemIdOrGroupSizeAlias = "C"; // Create an additional COUNT expression for the MEDIA_ITEMS.MEDIA_ITEM_ID in the GROUP BY-statement result.Append("COUNT(V.C) "); result.Append(mediaItemIdOrGroupSizeAlias); if (!string.IsNullOrWhiteSpace(groupClause)) { result.Append(", "); result.Append(groupClause); } result.Append(" FROM ("); result.Append("SELECT DISTINCT "); result.Append(miaIdAttribute.GetQualifiedName(ns)); result.Append(" C"); } else { // Append plain attribute MEDIA_ITEMS.MEDIA_ITEM_ID if no GROUP BY-statement is requested result.Append(miaIdAttribute.GetDeclarationWithAlias(ns, out mediaItemIdOrGroupSizeAlias)); // System attributes: Necessary to evaluate if a requested MIA is present for the media item foreach (KeyValuePair <MediaItemAspectMetadata, TableQueryData> kvp in miaTypeTableQueries) { result.Append(", "); string miamColumn = kvp.Value.GetAlias(ns) + "." + MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME; result.Append(miamColumn); string miamAlias = ns.GetOrCreate(miamColumn, "A"); result.Append(" "); result.Append(miamAlias); if (miamAlias != null) { miamAliases.Add(kvp.Key, miamAlias); } } } // Selected attributes foreach (string selectAttr in selectAttributeDeclarations) { result.Append(", "); result.Append(selectAttr); } string whereStr = compiledFilter.CreateSqlFilterCondition(ns, requestedAttributes, out bindVars); foreach (BindVar bv in sqlVars) { bindVars.Add(bv); } result.Append(" FROM "); bool firstJoinTable = true; // Other joined tables foreach (TableJoin tableJoin in tableJoins) { if (firstJoinTable) { result.Append(tableJoin.JoinedTable.GetDeclarationWithAlias(ns)); firstJoinTable = false; } else { result.Append(tableJoin.GetJoinDeclaration(ns)); } result.Append(' '); } if (!string.IsNullOrEmpty(whereStr)) { result.Append(" WHERE "); result.Append(whereStr); } if (groupByValues) { result.Append(") V"); if (qualifiedGroupByAliases.Count > 0) { result.Append(" GROUP BY "); result.Append(groupClause); } } else { if (compiledSortInformation != null && compiledSortInformation.Count > 0) { IEnumerable <string> sortCriteria = compiledSortInformation.Select(csi => csi.GetSortDeclaration(ns)); result.Append(" ORDER BY "); result.Append(StringUtils.Join(", ", sortCriteria)); } } statementStr = result.ToString(); }
protected void RequestSimpleAttribute(QueryAttribute queryAttribute, IDictionary <object, TableQueryData> tableQueries, IList <TableJoin> tableJoins, string miaJoinType, IDictionary <QueryAttribute, RequestedAttribute> requestedAttributes, IDictionary <MediaItemAspectMetadata, TableQueryData> miaTypeTableQueries, RequestedAttribute miaIdAttribute, out RequestedAttribute requestedAttribute) { if (requestedAttributes.TryGetValue(queryAttribute, out requestedAttribute)) { // Already requested return; } MediaItemAspectMetadata.AttributeSpecification spec = queryAttribute.Attr; MediaItemAspectMetadata miaType = spec.ParentMIAM; TableQueryData tqd; switch (spec.Cardinality) { case Cardinality.Inline: // For Inline queries, we request the Inline attribute's column name at the MIA main table, which gets joined // with the MIA ID if (!tableQueries.TryGetValue(miaType, out tqd)) { tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType); if (miaTypeTableQueries != null) { miaTypeTableQueries.Add(miaType, tqd); } tableJoins.Add(new TableJoin(miaJoinType, tqd, miaIdAttribute, new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME))); } requestedAttribute = new RequestedAttribute(tqd, _miaManagement.GetMIAAttributeColumnName(queryAttribute.Attr)); break; case Cardinality.ManyToOne: // For MTO queries, we request both the MIA main table and the MTO table TableQueryData miaTqd; if (!tableQueries.TryGetValue(miaType, out miaTqd)) { miaTqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType); if (miaTypeTableQueries != null) { miaTypeTableQueries.Add(miaType, miaTqd); } // Add MIA main table to list of table joins tableJoins.Add(new TableJoin(miaJoinType, miaTqd, miaIdAttribute, new RequestedAttribute(miaTqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME))); } if (!tableQueries.TryGetValue(spec, out tqd)) { tqd = tableQueries[spec] = TableQueryData.CreateTableQueryOfMTOTable(_miaManagement, spec); // We must use left outer joins for MTO value tables, because if the value is null, the association FK is null tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqd, new RequestedAttribute(miaTqd, _miaManagement.GetMIAAttributeColumnName(queryAttribute.Attr)), new RequestedAttribute(tqd, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME))); } requestedAttribute = new RequestedAttribute(tqd, MIA_Management.COLL_ATTR_VALUE_COL_NAME); break; default: throw new IllegalCallException("Attributes of cardinality '{0}' cannot be queried via the {1}", spec.Cardinality, GetType().Name); } requestedAttributes.Add(queryAttribute, requestedAttribute); }
/// <summary> /// Generates a statement to query the values of the complex <see cref="QueryAttribute"/>. /// </summary> /// <param name="valueAlias">Alias for the value column.</param> /// <param name="mediaItemIdAlias">Alias for the ID of the media item to which the value belongs.</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 GenerateSqlStatement(out string mediaItemIdAlias, out string valueAlias, 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 external attribute table, which contains the foreign key // to the MIA ID, and the request attribute for that MIA ID. // We'll need the requested attribute as join attribute soon. TableQueryData mainJoinTableQuery; RequestedAttribute miaIdAttribute; RequestedAttribute valueAttribute; // Build main join table switch (_queryAttribute.Cardinality) { case Cardinality.OneToMany: mainJoinTableQuery = new TableQueryData(_miaManagement.GetMIACollectionAttributeTableName(_queryAttribute)); miaIdAttribute = new RequestedAttribute(mainJoinTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME); valueAttribute = new RequestedAttribute(mainJoinTableQuery, MIA_Management.COLL_ATTR_VALUE_COL_NAME); break; case Cardinality.ManyToMany: mainJoinTableQuery = new TableQueryData(_miaManagement.GetMIACollectionAttributeNMTableName(_queryAttribute)); miaIdAttribute = new RequestedAttribute(mainJoinTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME); TableQueryData collAttrTableQuery = new TableQueryData(_miaManagement.GetMIACollectionAttributeTableName(_queryAttribute)); tableJoins.Add(new TableJoin("INNER JOIN", collAttrTableQuery, new RequestedAttribute(mainJoinTableQuery, 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); } tableJoins.Insert(0, new TableJoin("INNER JOIN", mainJoinTableQuery, null, null)); // The first table join doesn't need the join attributes - see below // Ensure that the tables for all necessary MIAs are requested first (INNER JOIN) foreach (MediaItemAspectMetadata miaType in _necessaryRequestedMIAs) { if (tableQueries.ContainsKey(miaType)) { // We only come here if miaType is the MIA of the requested attribute itself or if it was already queried as necessary MIA, so optimize redundant entry continue; } TableQueryData tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType); 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); } StringBuilder result = new StringBuilder("SELECT "); // Append MIA ID attribute only if no DISTINCT query is made result.Append(_selectProjectionFunction == null ? miaIdAttribute.GetDeclarationWithAlias(ns, out mediaItemIdAlias) : miaIdAttribute.GetDeclarationWithAlias(ns, _selectProjectionFunction, out mediaItemIdAlias)); result.Append(", "); // Selected attributes result.Append(valueAttribute.GetDeclarationWithAlias(ns, out valueAlias)); string whereStr = compiledFilter.CreateSqlFilterCondition(ns, requestedAttributes, out bindVars); result.Append(" FROM "); bool firstJoinTable = true; // Other joined tables foreach (TableJoin tableJoin in tableJoins) { if (firstJoinTable) { result.Append(tableJoin.JoinedTable.GetDeclarationWithAlias(ns)); firstJoinTable = false; } else { result.Append(tableJoin.GetJoinDeclaration(ns)); } result.Append(' '); } if (!string.IsNullOrEmpty(whereStr)) { result.Append("WHERE "); result.Append(whereStr); } statementStr = result.ToString(); }
protected void GenerateSubSelectSqlStatement(BindVarNamespace bvNamespace, out string statementStr, out IList <BindVar> bindVars) { Namespace ns = new Namespace(); IDictionary <MediaItemAspectMetadata, string> miamAliases = new Dictionary <MediaItemAspectMetadata, string>(); // 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>(); // Contains all table query data for MIA type tables IDictionary <MediaItemAspectMetadata, TableQueryData> miaTypeTableQueries = new Dictionary <MediaItemAspectMetadata, TableQueryData>(); RequestedAttribute miaIdAttribute = null; // Lazy initialized below // Ensure that the tables for all necessary MIAs are requested first (INNER JOIN) foreach (MediaItemAspectMetadata miaType in _necessaryRequestedMIAs) { if (miaType.IsTransientAspect) { continue; } if (tableQueries.ContainsKey(miaType)) { // We only come here if miaType was already queried as necessary MIA, so optimize redundant entry continue; } TableQueryData tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType); miaTypeTableQueries.Add(miaType, tqd); RequestedAttribute ra; // The first table join has invalid join attributes because miaIdAttribute is still null - but only the join table attribute is necessary // for the the first table - see below tableJoins.Add(new TableJoin("INNER JOIN", tqd, ra = new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute)); if (miaIdAttribute == null) { miaIdAttribute = ra; } } if (miaIdAttribute == null) { // If we didn't request any necessary MIA types, we have to add an artificial table for the miaIdAttribute TableQueryData miaTableQuery = new TableQueryData(MediaLibrary_SubSchema.MEDIA_ITEMS_TABLE_NAME); tableJoins.Add(new TableJoin("INNER JOIN", miaTableQuery, null, null)); // First table join has invalid join attributes - not needed for first table miaIdAttribute = new RequestedAttribute(miaTableQuery, MediaLibrary_SubSchema.MEDIA_ITEMS_ITEM_ID_COL_NAME); } // Ensure that the tables for all optional MIAs are requested first (LEFT OUTER JOIN) // That is necessary to make empty optional MIA types available in the result foreach (MediaItemAspectMetadata miaType in _optionalRequestedMIAs) { if (miaType.IsTransientAspect) { continue; } if (tableQueries.ContainsKey(miaType)) { // We only come here if miaType was already queried as necessary or optional MIA, so optimize redundant entry continue; } TableQueryData tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType); miaTypeTableQueries.Add(miaType, tqd); tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqd, new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute)); } CompiledFilter compiledFilter = CreateCompiledFilter(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.ParentMIAM.IsTransientAspect) { continue; } 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, miaTypeTableQueries, miaIdAttribute, out ra); } StringBuilder result = new StringBuilder("SELECT "); result.Append(miaIdAttribute.GetQualifiedName(ns)); string whereStr = compiledFilter.CreateSqlFilterCondition(ns, requestedAttributes, out bindVars); result.Append(" FROM "); bool firstJoinTable = true; // Other joined tables foreach (TableJoin tableJoin in tableJoins) { if (firstJoinTable) { result.Append(tableJoin.JoinedTable.GetDeclarationWithAlias(ns)); firstJoinTable = false; } else { result.Append(tableJoin.GetJoinDeclaration(ns)); } result.Append(' '); } if (!string.IsNullOrEmpty(whereStr)) { result.Append(" WHERE "); result.Append(whereStr); } statementStr = result.ToString(); }
protected void GenerateSingleMIASqlStatement(bool groupByValues, IDictionary <MediaItemAspectMetadata, string> miamAliases, out string mediaItemIdOrGroupSizeAlias, out IDictionary <QueryAttribute, string> attributeAliases, 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>(); attributeAliases = new Dictionary <QueryAttribute, string>(); // Contains a list of qualified attribute names for all select attributes - needed for GROUP BY-expressions ICollection <string> qualifiedGroupByAliases = new List <string>(); // Contains a list of compiled select attribute declarations. We need this in a separate list (in contrast to using // the selectAttributes list together with the compiledAttributes map) because it might be the case that // an attribute is requested twice. In that rare case, we need a new alias name for it. IList <string> selectAttributeDeclarations = new List <string>(); // 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>(); // Contains all table query data for MIA type tables IDictionary <MediaItemAspectMetadata, TableQueryData> miaTypeTableQueries = new Dictionary <MediaItemAspectMetadata, TableQueryData>(); //Used to check whether a required MulpleMIA exists IList <TableQueryData> miaExistsTables = new List <TableQueryData>(); RequestedAttribute miaIdAttribute = null; // Lazy initialized below // Contains CompiledSortInformation instances for each sort information instance IList <CompiledSortInformation> compiledSortInformation = null; // Ensure that the tables for all necessary MIAs are requested first (INNER JOIN) foreach (MediaItemAspectMetadata miaType in _necessaryRequestedMIAs) { if (miaType.IsTransientAspect) { continue; } if (tableQueries.ContainsKey(miaType)) { // We only come here if miaType was already queried as necessary MIA, so optimize redundant entry continue; } if (miaType is MultipleMediaItemAspectMetadata) { miaExistsTables.Add(TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType)); continue; } TableQueryData tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType); miaTypeTableQueries.Add(miaType, tqd); RequestedAttribute ra; // The first table join has invalid join attributes because miaIdAttribute is still null - but only the join table attribute is necessary // for the the first table - see below tableJoins.Add(new TableJoin("INNER JOIN", tqd, ra = new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute)); if (miaIdAttribute == null) { miaIdAttribute = ra; } } if (miaIdAttribute == null) { // If we didn't request any necessary MIA types, we have to add an artificial table for the miaIdAttribute TableQueryData miaTableQuery = new TableQueryData(MediaLibrary_SubSchema.MEDIA_ITEMS_TABLE_NAME); tableJoins.Add(new TableJoin("INNER JOIN", miaTableQuery, null, null)); // First table join has invalid join attributes - not needed for first table miaIdAttribute = new RequestedAttribute(miaTableQuery, MediaLibrary_SubSchema.MEDIA_ITEMS_ITEM_ID_COL_NAME); } // Ensure that the tables for all optional MIAs are requested first (LEFT OUTER JOIN) // That is necessary to make empty optional MIA types available in the result foreach (MediaItemAspectMetadata miaType in _optionalRequestedMIAs) { if (miaType.IsTransientAspect) { continue; } if (tableQueries.ContainsKey(miaType)) { // We only come here if miaType was already queried as necessary or optional MIA, so optimize redundant entry continue; } if (miaType is MultipleMediaItemAspectMetadata) { continue; } TableQueryData tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType); miaTypeTableQueries.Add(miaType, tqd); tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqd, new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute)); } // Build table query data for each selected Inline and MTO attribute // + select query attribute // + add alias to selectAttributeDeclarations foreach (QueryAttribute attr in _selectAttributes) { if (attr.Attr.ParentMIAM.IsTransientAspect) { continue; } //Multiple MIAs will be handled later if (attr.Attr.ParentMIAM is MultipleMediaItemAspectMetadata) { continue; } RequestedAttribute ra; RequestSimpleAttribute(attr, tableQueries, tableJoins, "LEFT OUTER JOIN", requestedAttributes, miaTypeTableQueries, miaIdAttribute, out ra); string alias; selectAttributeDeclarations.Add(_selectProjectionFunction == null ? ra.GetDeclarationWithAlias(ns, out alias) : ra.GetDeclarationWithAlias(ns, _selectProjectionFunction, out alias)); attributeAliases.Add(attr, alias); qualifiedGroupByAliases.Add(ra.GetAlias(ns)); } string subSql; GenerateSubSelectSqlStatement(bvNamespace, out subSql, out bindVars); // Build table query data for each sort attribute if (_sortInformation != null) { compiledSortInformation = new List <CompiledSortInformation>(); BindVar userVar = null; foreach (ISortInformation sortInformation in _sortInformation) { if (AddAttributeSortInformation(sortInformation, miaIdAttribute, requestedAttributes, miaTypeTableQueries, tableQueries, tableJoins, compiledSortInformation)) { continue; } if (AddChildAggregateAttributeSortInformation(sortInformation, ns, bvNamespace, miaIdAttribute, tableJoins, compiledSortInformation, bindVars)) { continue; } if (AddDataSortInformation(sortInformation, miaIdAttribute, tableJoins, compiledSortInformation, bindVars, ref userVar)) { continue; } } } //Build sql statement StringBuilder result = new StringBuilder("SELECT "); string groupClause = StringUtils.Join(", ", qualifiedGroupByAliases.Select(alias => "V." + alias)); if (groupByValues) { mediaItemIdOrGroupSizeAlias = "C"; // Create an additional COUNT expression for the MEDIA_ITEMS.MEDIA_ITEM_ID in the GROUP BY-statement result.Append("COUNT(V.C) "); result.Append(mediaItemIdOrGroupSizeAlias); if (!string.IsNullOrWhiteSpace(groupClause)) { result.Append(", "); result.Append(groupClause); } result.Append(" FROM ("); result.Append("SELECT DISTINCT "); result.Append(miaIdAttribute.GetQualifiedName(ns)); result.Append(" C"); } else { // Append plain attribute MEDIA_ITEMS.MEDIA_ITEM_ID if no GROUP BY-statement is requested result.Append(miaIdAttribute.GetDeclarationWithAlias(ns, out mediaItemIdOrGroupSizeAlias)); // System attributes: Necessary to evaluate if a requested MIA is present for the media item foreach (KeyValuePair <MediaItemAspectMetadata, TableQueryData> kvp in miaTypeTableQueries) { result.Append(", "); string miamColumn = kvp.Value.GetAlias(ns) + "." + MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME; result.Append(miamColumn); string miamAlias = ns.GetOrCreate(miamColumn, "A"); result.Append(" "); result.Append(miamAlias); if (miamAlias != null) { miamAliases.Add(kvp.Key, miamAlias); } } } // Selected attributes foreach (string selectAttr in selectAttributeDeclarations) { result.Append(", "); result.Append(selectAttr); } result.Append(" FROM "); bool firstJoinTable = true; // Other joined tables foreach (TableJoin tableJoin in tableJoins) { if (firstJoinTable) { result.Append(tableJoin.JoinedTable.GetDeclarationWithAlias(ns)); firstJoinTable = false; } else { result.Append(tableJoin.GetJoinDeclaration(ns)); } result.Append(' '); } bool whereAdded = false; if (!string.IsNullOrEmpty(subSql)) { result.Append(" WHERE "); result.Append(miaIdAttribute.GetQualifiedName(ns)); result.Append($" IN({subSql}) "); whereAdded = true; } if (miaExistsTables.Count > 0) { foreach (TableQueryData tqd in miaExistsTables) { if (!whereAdded) { result.Append(" WHERE "); } else { result.Append(" AND "); } whereAdded = true; result.Append("EXISTS(SELECT 1 FROM "); result.Append(tqd.TableName); result.Append(" WHERE "); result.Append(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME); result.Append("="); result.Append(miaIdAttribute.GetQualifiedName(ns)); result.Append(")"); } } if (groupByValues) { result.Append(") V"); if (qualifiedGroupByAliases.Count > 0) { result.Append(" GROUP BY "); result.Append(groupClause); } } else { if (compiledSortInformation != null && compiledSortInformation.Count > 0) { IEnumerable <string> sortCriteria = compiledSortInformation.Select(csi => csi.GetSortDeclaration(ns)); result.Append(" ORDER BY "); result.Append(StringUtils.Join(", ", sortCriteria)); } } statementStr = result.ToString(); }