public string GetJoinDeclaration(Namespace ns) { StringBuilder result = new StringBuilder(100); result.Append(_joinType); result.Append(" "); result.Append(_table.GetDeclarationWithAlias(ns)); result.Append(" ON "); RequestedAttribute ra = _joinAttr1 as RequestedAttribute; if (ra != null) { result.Append(ra.GetQualifiedName(ns)); } else { result.Append(_joinAttr1); } result.Append(" = "); ra = _joinAttr2 as RequestedAttribute; if (ra != null) { result.Append(ra.GetQualifiedName(ns)); } else { result.Append(_joinAttr2); } return(result.ToString()); }
public string GetJoinDeclaration(Namespace ns) { StringBuilder result = new StringBuilder(100); result.Append(_joinType); result.Append(" "); result.Append(_table.GetDeclarationWithAlias(ns)); if (_conditionPairs.Count > 0) { result.Append(" ON "); bool firstCondition = true; foreach (var condition in _conditionPairs) { if (!firstCondition) { result.Append(" AND "); } RequestedAttribute ra = condition.Key as RequestedAttribute; if (ra != null) { result.Append(ra.GetQualifiedName(ns)); } else { result.Append(condition.Key); } result.Append(" = "); ra = condition.Value as RequestedAttribute; if (ra != null) { result.Append(ra.GetQualifiedName(ns)); } else { result.Append(condition.Value); } firstCondition = false; } } return(result.ToString()); }
protected bool AddChildAggregateAttributeSortInformation(ISortInformation sortInformation, Namespace ns, BindVarNamespace bvNamespace, RequestedAttribute miaIdAttribute, IList <TableJoin> tableJoins, IList <CompiledSortInformation> compiledSortInformation, IList <BindVar> bindVars) { ChildAggregateAttributeSortInformation childAttributeSort = sortInformation as ChildAggregateAttributeSortInformation; if (childAttributeSort != null) { if (childAttributeSort.ChildAttributeType.ParentMIAM.IsTransientAspect) { return(false); } var relation = _miaManagement.LocallyKnownRelationshipTypes.FirstOrDefault(r => (r.ChildRole == childAttributeSort.ChildRole && r.ParentRole == childAttributeSort.ParentRole)); if (relation == null) { return(false); } if (!_miaManagement.ManagedMediaItemAspectTypes.TryGetValue(relation.ChildAspectId, out var childMetadata)) { return(false); } BindVar roleVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), childAttributeSort.ParentRole, typeof(Guid)); bindVars.Add(roleVar); BindVar linkedRoleVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), childAttributeSort.ChildRole, typeof(Guid)); bindVars.Add(linkedRoleVar); var table = new TableQueryData($"({BuildRelationshipPart(childAttributeSort, childMetadata, false, roleVar, linkedRoleVar)} UNION {BuildRelationshipPart(childAttributeSort, childMetadata, true, roleVar, linkedRoleVar)})"); var tableName = table.GetAlias(ns); tableJoins.Add(new TableJoin("INNER JOIN", table, $"{tableName}.ID", miaIdAttribute.GetQualifiedName(ns))); RequestedAttribute ra = new RequestedAttribute(table, "SORT"); compiledSortInformation.Add(new CompiledSortInformation(ra, childAttributeSort.Direction)); return(true); } return(false); }
/// <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(); }
/// <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; RequestedAttribute orderAttribute; // 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); orderAttribute = new RequestedAttribute(mainJoinTableQuery, MIA_Management.COLL_ATTR_VALUE_ORDER_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); orderAttribute = new RequestedAttribute(mainJoinTableQuery, MIA_Management.COLL_ATTR_VALUE_ORDER_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; } //If any MultipleMediaItemAspect are requested, the joining below will cause duplicate values //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, _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); } 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); } if (orderAttribute != null) { result.Append(" ORDER BY "); result.Append(orderAttribute.GetQualifiedName(ns)); } statementStr = result.ToString(); }
public string GetSortDeclaration(Namespace ns) { return(_sortAttribute.GetQualifiedName(ns) + " " + ToSqlOrderByDirection(_direction)); }
/// <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(); }
/// <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(); }