/// <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();
}
/// <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();
}
/// <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();
}
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;
// 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 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 (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)
{
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 = 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,
miaTypeTableQueries, miaIdAttribute, out ra);
}
// Build table query data for each sort attribute
if (_sortInformation != null)
{
compiledSortInformation = new List<CompiledSortInformation>();
foreach (SortInformation sortInformation in _sortInformation)
{
MediaItemAspectMetadata.AttributeSpecification attr = sortInformation.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, sortInformation.Direction));
}
}
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);
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 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();
}