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