protected override void CompileStatementParts(MIA_Management miaManagement, IFilter filter, IFilter subqueryFilter, Namespace ns, BindVarNamespace bvNamespace,
ICollection <MediaItemAspectMetadata> requiredMIATypes, string outerMIIDJoinVariable, ICollection <TableJoin> tableJoins,
IList <object> resultParts, IList <BindVar> resultBindVars)
{
MediaItemIdFilter mediaItemIdFilter = (MediaItemIdFilter)filter;
bool first = true;
foreach (IList <Guid> mediaItemIdsCluster in CollectionUtils.Cluster(mediaItemIdFilter.MediaItemIds, MAX_IN_VALUES_SIZE))
{
QueryAttribute qa = new QueryAttribute(RelationshipAspect.ATTR_LINKED_ID);
IList <string> bindVarRefs = new List <string>(MAX_IN_VALUES_SIZE);
foreach (Guid mediaItemId in mediaItemIdsCluster)
{
BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), mediaItemId, typeof(Guid));
bindVarRefs.Add("@" + bindVar.Name);
resultBindVars.Add(bindVar);
}
if (!first)
{
resultParts.Add(" OR ");
}
first = false;
resultParts.Add(qa);
resultParts.Add(" IN (" + StringUtils.Join(", ", bindVarRefs) + ")");
}
}
protected bool AddDataSortInformation(ISortInformation sortInformation, RequestedAttribute miaIdAttribute, IList <TableJoin> tableJoins, IList <CompiledSortInformation> compiledSortInformation,
IList <BindVar> bindVars, ref BindVar userVar)
{
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));
bindVars.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);
return(true);
}
return(false);
}
/// <summary>
/// Builds a subquery that returns the ids of the media items returned by the <paramref name="filter"/>.
/// </summary>
/// <param name="filter">Relationship filter instance to create the sub query for.</param>
/// <param name="subqueryFilter">Additional filter to apply to all subqueries.</param>
/// <param name="miaManagement">MIA_Management instance to generate attribute column names.</param>
/// <param name="bvNamespace">Namespace used to build bind var names.</param>
/// <param name="resultParts">Statement parts for the filter.</param>
/// <param name="resultBindVars">Bind variables for the filter.</param>
public static void BuildRelationshipSubquery(AbstractRelationshipFilter filter, IFilter subqueryFilter, MIA_Management miaManagement,
BindVarNamespace bvNamespace, IList <object> resultParts, IList <BindVar> resultBindVars)
{
//Simple relationship filter with linked id
BindVar linkedIdVar = null;
RelationshipFilter relationshipFilter = filter as RelationshipFilter;
if (relationshipFilter != null && relationshipFilter.LinkedMediaItemId != Guid.Empty)
{
linkedIdVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), relationshipFilter.LinkedMediaItemId, typeof(Guid));
resultBindVars.Add(linkedIdVar);
}
//Role
BindVar roleVar = null;
if (filter.Role != Guid.Empty)
{
roleVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), filter.Role, typeof(Guid));
resultBindVars.Add(roleVar);
}
//Linked role
BindVar linkedRoleVar = null;
if (filter.LinkedRole != Guid.Empty)
{
linkedRoleVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), filter.LinkedRole, typeof(Guid));
resultBindVars.Add(linkedRoleVar);
}
//Complex relationship filter with linked filter
string sqlStatement = null;
IList <BindVar> bindVars = null;
FilteredRelationshipFilter filteredRelationshipFilter = filter as FilteredRelationshipFilter;
if (filteredRelationshipFilter != null && filteredRelationshipFilter.Filter != null)
{
//Build a sub query for the linked filter
string idAlias = null;
ICollection <QueryAttribute> requiredAttributes = new List <QueryAttribute>();
SubQueryBuilder filterBuilder = new SubQueryBuilder(miaManagement, requiredAttributes,
new List <MediaItemAspectMetadata>(), filteredRelationshipFilter.Filter, subqueryFilter, bvNamespace.BindVarCounter);
filterBuilder.GenerateSqlStatement(out idAlias, out sqlStatement, out bindVars);
sqlStatement = " SELECT TS." + idAlias + " FROM (" + sqlStatement + ") TS";
bvNamespace.BindVarCounter += bindVars.Count;
CollectionUtils.AddAll(resultBindVars, bindVars);
}
//Relationships are only stored for one party in the relationship so we need to union the query with a query
//that reverses the relationship to ensure that all relationships are selected
BuildRelationshipSubqueryPart(roleVar, linkedRoleVar, linkedIdVar, sqlStatement, false, miaManagement, resultParts);
resultParts.Add(" UNION ");
BuildRelationshipSubqueryPart(roleVar, linkedRoleVar, linkedIdVar, sqlStatement, true, miaManagement, resultParts);
}
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);
}
public static void BuildRelationshipSubqueryPart(BindVar roleVar, BindVar linkedRoleVar, BindVar linkedIdVar, string sqlStatement, bool reverse,
MIA_Management miaManagement, IList <object> resultParts)
{
string selectColumn;
string roleColumn;
string linkedRoleColumn;
string linkedIdColumn;
if (reverse)
{
//if this is the reverse part, reverse the column names
selectColumn = miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ID);
roleColumn = miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ROLE);
linkedRoleColumn = miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_ROLE);
linkedIdColumn = MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME;
}
else
{
selectColumn = MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME;
roleColumn = miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_ROLE);
linkedRoleColumn = miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ROLE);
linkedIdColumn = miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ID);
}
resultParts.Add("SELECT R1.");
resultParts.Add(selectColumn);
resultParts.Add(" FROM ");
resultParts.Add(miaManagement.GetMIATableName(RelationshipAspect.Metadata));
resultParts.Add(" R1");
//Check if we actually have any conditions
if (roleVar == null && linkedRoleVar == null && linkedIdVar == null && string.IsNullOrEmpty(sqlStatement))
{
return;
}
resultParts.Add(" WHERE");
bool hasCondition = false;
//Linked id
if (linkedIdVar != null)
{
hasCondition = true;
resultParts.Add(" R1.");
resultParts.Add(linkedIdColumn);
resultParts.Add("=@" + linkedIdVar.Name);
}
//Role
if (roleVar != null)
{
if (hasCondition)
{
resultParts.Add(" AND");
}
hasCondition = true;
resultParts.Add(" R1.");
resultParts.Add(roleColumn);
resultParts.Add("=@" + roleVar.Name);
}
//Linked role
if (linkedRoleVar != null)
{
if (hasCondition)
{
resultParts.Add(" AND");
}
resultParts.Add(" R1.");
resultParts.Add(linkedRoleColumn);
resultParts.Add("=@" + linkedRoleVar.Name);
}
//Linked id subquery
if (!string.IsNullOrEmpty(sqlStatement))
{
if (hasCondition)
{
resultParts.Add(" AND");
}
hasCondition = true;
resultParts.Add(" R1." + linkedIdColumn);
resultParts.Add(" IN(");
resultParts.Add(sqlStatement);
resultParts.Add(")");
}
}
protected virtual void CompileStatementParts(MIA_Management miaManagement, IFilter filter, IFilter subqueryFilter, Namespace ns, BindVarNamespace bvNamespace,
ICollection <MediaItemAspectMetadata> requiredMIATypes, string outerMIIDJoinVariable, ICollection <TableJoin> tableJoins,
IList <object> resultParts, IList <BindVar> resultBindVars)
{
if (filter == null)
{
return;
}
MediaItemIdFilter mediaItemIdFilter = filter as MediaItemIdFilter;
if (mediaItemIdFilter != null)
{
ICollection <Guid> mediaItemIds = mediaItemIdFilter.MediaItemIds;
if (mediaItemIds.Count == 0)
{
resultParts.Add("1 = 2");
}
else
{
if (mediaItemIds.Count == 1)
{
resultParts.Add(outerMIIDJoinVariable);
BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), mediaItemIds.First(), typeof(Guid));
resultParts.Add(" = @" + bindVar.Name);
resultBindVars.Add(bindVar);
}
else
{
bool first = true;
ICollection <string> clusterExpressions = new List <string>();
foreach (IList <Guid> mediaItemIdsCluster in CollectionUtils.Cluster(mediaItemIds, MAX_IN_VALUES_SIZE))
{
IList <string> bindVarRefs = new List <string>(MAX_IN_VALUES_SIZE);
foreach (Guid mediaItemId in mediaItemIdsCluster)
{
BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), mediaItemId, typeof(Guid));
bindVarRefs.Add("@" + bindVar.Name);
resultBindVars.Add(bindVar);
}
if (!first)
{
resultParts.Add(" OR ");
}
first = false;
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(" IN (" + StringUtils.Join(", ", bindVarRefs) + ")");
}
resultParts.Add(StringUtils.Join(" OR ", clusterExpressions));
}
}
return;
}
BooleanCombinationFilter boolFilter = filter as BooleanCombinationFilter;
ICollection <IFilter> filterOperands = boolFilter?.Operands; //Work on collection reference to avoid chaning original
if (boolFilter != null && boolFilter.Operator == BooleanOperator.And && boolFilter.Operands.Count > 1 && boolFilter.Operands.ToList().All(x => x is IAttributeFilter))
{
ICollection <IFilter> remainingOperands = new List <IFilter>();
// Special case to do multiple MIA boolean logic first
IDictionary <Guid, ICollection <IAttributeFilter> > multiGroups = new Dictionary <Guid, ICollection <IAttributeFilter> >();
foreach (IAttributeFilter operand in filterOperands)
{
MultipleMediaItemAspectMetadata mmiam = operand.AttributeType.ParentMIAM as MultipleMediaItemAspectMetadata;
if (mmiam != null)
{
Guid key = operand.AttributeType.ParentMIAM.AspectId;
if (!multiGroups.ContainsKey(key))
{
multiGroups[key] = new List <IAttributeFilter>();
}
multiGroups[key].Add(operand);
}
else
{
remainingOperands.Add(operand);
}
}
if (multiGroups.Keys.Count > 0)
{
bool firstGroup = true;
foreach (ICollection <IAttributeFilter> filterGroup in multiGroups.Values)
{
if (firstGroup)
{
firstGroup = false;
}
else
{
resultParts.Add(" AND ");
}
bool firstItem = true;
foreach (IAttributeFilter filterItem in filterGroup)
{
MediaItemAspectMetadata.AttributeSpecification attributeType = filterItem.AttributeType;
if (firstItem)
{
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(" IN(");
resultParts.Add("SELECT ");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add(" FROM ");
resultParts.Add(miaManagement.GetMIATableName(attributeType.ParentMIAM));
resultParts.Add(" WHERE ");
firstItem = false;
}
else
{
resultParts.Add(" AND ");
}
//Empty filter needs to be handled differently to other IAttribute filters
if (filterItem is EmptyFilter)
{
resultParts.Add(miaManagement.GetMIAAttributeColumnName(attributeType));
resultParts.Add(" IS NULL");
}
else
{
BuildAttributeFilterExpression(filterItem, miaManagement.GetMIAAttributeColumnName(attributeType), bvNamespace, resultParts, resultBindVars);
}
}
resultParts.Add(")");
}
// Process remaining operands ?
if (remainingOperands.Count == 0)
{
return;
}
resultParts.Add(" AND ");
filterOperands = remainingOperands;
}
}
if (boolFilter != null)
{
int numOperands = filterOperands.Count;
IEnumerator enumOperands = filterOperands.GetEnumerator();
if (!enumOperands.MoveNext())
{
return;
}
if (numOperands > 1)
{
resultParts.Add("(");
}
CompileStatementParts(miaManagement, (IFilter)enumOperands.Current, subqueryFilter, ns, bvNamespace,
requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
while (enumOperands.MoveNext())
{
switch (boolFilter.Operator)
{
case BooleanOperator.And:
resultParts.Add(" AND ");
break;
case BooleanOperator.Or:
resultParts.Add(" OR ");
break;
default:
throw new NotImplementedException(string.Format(
"Boolean filter operator '{0}' isn't supported by the media library", boolFilter.Operator));
}
CompileStatementParts(miaManagement, (IFilter)enumOperands.Current, subqueryFilter, ns, bvNamespace,
requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
}
if (numOperands > 1)
{
resultParts.Add(")");
}
return;
}
NotFilter notFilter = filter as NotFilter;
if (notFilter != null)
{
resultParts.Add("NOT (");
CompileStatementParts(miaManagement, notFilter.InnerFilter, subqueryFilter, ns, bvNamespace,
requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
resultParts.Add(")");
return;
}
FalseFilter falseFilter = filter as FalseFilter;
if (falseFilter != null)
{
resultParts.Add("1 = 2");
return;
}
// Must be done before checking IAttributeFilter - EmptyFilter is also an IAttributeFilter but must be
// compiled in a different way
EmptyFilter emptyFilter = filter as EmptyFilter;
if (emptyFilter != null)
{
MediaItemAspectMetadata.AttributeSpecification attributeType = emptyFilter.AttributeType;
requiredMIATypes.Add(attributeType.ParentMIAM);
Cardinality cardinality = attributeType.Cardinality;
if (cardinality == Cardinality.Inline || cardinality == Cardinality.ManyToOne)
{
resultParts.Add(new QueryAttribute(attributeType));
resultParts.Add(" IS NULL"); // MTO attributes are joined with left outer joins and thus can also be checked for NULL
}
else if (cardinality == Cardinality.OneToMany)
{
resultParts.Add("NOT EXISTS(");
resultParts.Add("SELECT 1");
resultParts.Add(" FROM ");
resultParts.Add(miaManagement.GetMIACollectionAttributeTableName(attributeType));
resultParts.Add(" V WHERE V.");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add("=");
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(")");
}
else if (cardinality == Cardinality.ManyToMany)
{
resultParts.Add("NOT EXISTS(");
resultParts.Add("SELECT 1");
resultParts.Add(" FROM ");
resultParts.Add(miaManagement.GetMIACollectionAttributeNMTableName(attributeType));
resultParts.Add(" NM INNER JOIN ");
resultParts.Add(miaManagement.GetMIACollectionAttributeTableName(attributeType));
resultParts.Add(" V ON NM.");
resultParts.Add(MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME);
resultParts.Add(" = V.");
resultParts.Add(MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME);
resultParts.Add(" WHERE NM.");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add("=");
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(")");
}
return;
}
// Must be done before checking IAttributeFilter - EmptyUserDataFilter is also an IAttributeFilter but must be
// compiled in a different way
EmptyUserDataFilter emptyUserDataFilter = filter as EmptyUserDataFilter;
if (emptyUserDataFilter != null)
{
BindVar userIdVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), emptyUserDataFilter.UserProfileId, typeof(Guid));
resultParts.Add("NOT EXISTS(");
resultParts.Add("SELECT 1");
resultParts.Add(" FROM ");
resultParts.Add(UserProfileDataManagement_SubSchema.USER_MEDIA_ITEM_DATA_TABLE_NAME);
resultParts.Add(" WHERE ");
resultParts.Add(UserProfileDataManagement_SubSchema.USER_PROFILE_ID_COL_NAME);
resultParts.Add(" = @" + userIdVar.Name);
resultBindVars.Add(userIdVar);
resultParts.Add(" AND ");
resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_KEY_COL_NAME);
resultParts.Add(" = '");
resultParts.Add(emptyUserDataFilter.UserDataKey);
resultParts.Add("' AND ");
resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_VALUE_COL_NAME);
resultParts.Add(" IS NOT NULL ");
resultParts.Add(" AND ");
resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_VALUE_COL_NAME);
resultParts.Add(" <> '' ");
resultParts.Add(" AND ");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add("=");
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(")");
return;
}
AbstractRelationshipFilter relationshipFilter = filter as AbstractRelationshipFilter;
if (relationshipFilter != null)
{
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(" IN(");
BuildRelationshipSubquery(relationshipFilter, subqueryFilter, miaManagement, bvNamespace, resultParts, resultBindVars);
resultParts.Add(")");
return;
}
RelationalUserDataFilter relationalUserDataFilter = filter as RelationalUserDataFilter;
if (relationalUserDataFilter != null)
{
BindVar userIdVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), relationalUserDataFilter.UserProfileId, typeof(Guid));
BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), relationalUserDataFilter.FilterValue, typeof(string));
resultParts.Add("(EXISTS(");
resultParts.Add("SELECT 1");
resultParts.Add(" FROM ");
resultParts.Add(UserProfileDataManagement_SubSchema.USER_MEDIA_ITEM_DATA_TABLE_NAME);
resultParts.Add(" WHERE ");
resultParts.Add(UserProfileDataManagement_SubSchema.USER_PROFILE_ID_COL_NAME);
resultParts.Add(" = @" + userIdVar.Name);
resultBindVars.Add(userIdVar);
resultParts.Add(" AND ");
resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_KEY_COL_NAME);
resultParts.Add(" = '");
resultParts.Add(relationalUserDataFilter.UserDataKey);
resultParts.Add("' AND (");
resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_VALUE_COL_NAME);
switch (relationalUserDataFilter.Operator)
{
case RelationalOperator.EQ:
resultParts.Add(" = ");
break;
case RelationalOperator.NEQ:
resultParts.Add(" <> ");
break;
case RelationalOperator.LT:
resultParts.Add(" < ");
break;
case RelationalOperator.LE:
resultParts.Add(" <= ");
break;
case RelationalOperator.GT:
resultParts.Add(" > ");
break;
case RelationalOperator.GE:
resultParts.Add(" >= ");
break;
default:
throw new NotImplementedException(string.Format(
"Relational user data filter operator '{0}' isn't supported by the media library", relationalUserDataFilter.Operator));
}
resultParts.Add("@" + bindVar.Name);
resultBindVars.Add(bindVar);
if (relationalUserDataFilter.IncludeEmpty)
{
resultParts.Add(" OR ");
resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_VALUE_COL_NAME);
resultParts.Add(" IS NULL ");
}
resultParts.Add(") AND ");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add("=");
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(")");
if (relationalUserDataFilter.IncludeEmpty)
{
resultParts.Add(" OR NOT EXISTS(");
resultParts.Add("SELECT 1");
resultParts.Add(" FROM ");
resultParts.Add(UserProfileDataManagement_SubSchema.USER_MEDIA_ITEM_DATA_TABLE_NAME);
resultParts.Add(" WHERE ");
resultParts.Add(UserProfileDataManagement_SubSchema.USER_PROFILE_ID_COL_NAME);
resultParts.Add(" = @" + userIdVar.Name);
resultParts.Add(" AND ");
resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_KEY_COL_NAME);
resultParts.Add(" = '");
resultParts.Add(relationalUserDataFilter.UserDataKey);
resultParts.Add("'");
resultParts.Add(" AND ");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add("=");
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(")");
}
resultParts.Add(")");
return;
}
IAttributeFilter attributeFilter = filter as IAttributeFilter;
if (attributeFilter != null)
{
MediaItemAspectMetadata.AttributeSpecification attributeType = attributeFilter.AttributeType;
if (attributeType.ParentMIAM is MultipleMediaItemAspectMetadata)
{
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(" IN(");
resultParts.Add("SELECT ");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add(" FROM ");
resultParts.Add(miaManagement.GetMIATableName(attributeType.ParentMIAM));
resultParts.Add(" WHERE ");
BuildAttributeFilterExpression(attributeFilter, miaManagement.GetMIAAttributeColumnName(attributeType), bvNamespace, resultParts, resultBindVars);
resultParts.Add(")");
return;
}
// For attribute filters, we have to create different kinds of expressions, depending on the
// cardinality of the attribute to be filtered.
// For Inline and MTO attributes, we simply create
//
// QA [Operator] [Comparison-Value]
//
// for OTM attributes, we create
//
// INNER JOIN [OTM-Value-Table] V ON V.MEDIA_ITEM_ID=[Outer-Join-Variable-Placeholder]
// WHERE [...] and V.VALUE [Operator] [Comparison-Value])
//
// for MTM attributes, we create
//
// INNER JOIN [MTM-NM-Table] NM ON NM.MEDIA_ITEM_ID=[Outer-Join-Variable-Placeholder]
// INNER JOIN [MTM-Value-Table] V ON NM.ID = V.ID
// WHERE [...] AND V.VALUE [Operator] [Comparison-Value])
requiredMIATypes.Add(attributeType.ParentMIAM);
Cardinality cardinality = attributeType.Cardinality;
if (cardinality == Cardinality.Inline || cardinality == Cardinality.ManyToOne)
{
BuildAttributeFilterExpression(attributeFilter, new QueryAttribute(attributeType), bvNamespace,
resultParts, resultBindVars);
}
else if (cardinality == Cardinality.OneToMany)
{
string joinTable = miaManagement.GetMIACollectionAttributeTableName(attributeType);
string attrName;
if (!_innerJoinedTables.TryGetValue(joinTable, out attrName))
{
TableQueryData tqd = new TableQueryData(joinTable);
tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqd,
new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), outerMIIDJoinVariable));
attrName = new RequestedAttribute(tqd, MIA_Management.COLL_ATTR_VALUE_COL_NAME).GetQualifiedName(ns);
_innerJoinedTables.Add(joinTable, attrName);
}
BuildAttributeFilterExpression(attributeFilter, attrName, bvNamespace, resultParts, resultBindVars);
}
else if (cardinality == Cardinality.ManyToMany)
{
string miaCollectionAttributeNMTableName = miaManagement.GetMIACollectionAttributeNMTableName(attributeType);
string attrName;
if (!_innerJoinedTables.TryGetValue(miaCollectionAttributeNMTableName, out attrName))
{
TableQueryData tqdMiaCollectionAttributeNMTable = new TableQueryData(miaCollectionAttributeNMTableName);
tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqdMiaCollectionAttributeNMTable,
new RequestedAttribute(tqdMiaCollectionAttributeNMTable, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), outerMIIDJoinVariable));
TableQueryData tqdMiaCollectionAttributeTable = new TableQueryData(miaManagement.GetMIACollectionAttributeTableName(attributeType));
tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqdMiaCollectionAttributeTable,
new RequestedAttribute(tqdMiaCollectionAttributeNMTable, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME),
new RequestedAttribute(tqdMiaCollectionAttributeTable, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME)));
attrName = tqdMiaCollectionAttributeTable.GetAlias(ns) + "." + MIA_Management.COLL_ATTR_VALUE_COL_NAME;
_innerJoinedTables.Add(miaCollectionAttributeNMTableName, attrName);
}
BuildAttributeFilterExpression(attributeFilter, attrName, bvNamespace, resultParts, resultBindVars);
}
return;
}
throw new InvalidDataException("Filter type '{0}' isn't supported by the media library", filter.GetType().Name);
}
/// <summary>
/// Builds the actual filter SQL expression <c>[Attribute-Operand] [Operator] [Comparison-Value]</c> for the given
/// attribute <paramref name="filter"/>.
/// </summary>
/// <param name="filter">Attribute filter instance to create the SQL expression for.</param>
/// <param name="attributeOperand">Comparison attribute to be used. Depending on the cardinality of the
/// to-be-filtered attribute, this will be the inline attribute alias or the attribute alias of the collection
/// attribute table.</param>
/// <param name="bvNamespace">Namespace used to build bind var names.</param>
/// <param name="resultParts">Statement parts for the attribute filter.</param>
/// <param name="resultBindVars">Bind variables for the attribute filter.</param>
public static void BuildAttributeFilterExpression(IAttributeFilter filter, object attributeOperand, BindVarNamespace bvNamespace,
IList <object> resultParts, IList <BindVar> resultBindVars)
{
Type attributeType = filter.AttributeType.AttributeType;
RelationalFilter relationalFilter = filter as RelationalFilter;
if (relationalFilter != null)
{
resultParts.Add(attributeOperand);
switch (relationalFilter.Operator)
{
case RelationalOperator.EQ:
resultParts.Add(" = ");
break;
case RelationalOperator.NEQ:
resultParts.Add(" <> ");
break;
case RelationalOperator.LT:
resultParts.Add(" < ");
break;
case RelationalOperator.LE:
resultParts.Add(" <= ");
break;
case RelationalOperator.GT:
resultParts.Add(" > ");
break;
case RelationalOperator.GE:
resultParts.Add(" >= ");
break;
default:
throw new NotImplementedException(string.Format(
"Relational filter operator '{0}' isn't supported by the media library", relationalFilter.Operator));
}
BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), relationalFilter.FilterValue, attributeType);
resultParts.Add("@" + bindVar.Name);
resultBindVars.Add(bindVar);
return;
}
LikeFilter likeFilter = filter as LikeFilter;
if (likeFilter != null)
{
if (!likeFilter.CaseSensitive)
{
resultParts.Add("UPPER(");
}
resultParts.Add(attributeOperand);
if (!likeFilter.CaseSensitive)
{
resultParts.Add(")");
}
resultParts.Add(" LIKE ");
BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), likeFilter.Expression, attributeType);
if (likeFilter.CaseSensitive)
{
resultParts.Add("@" + bindVar.Name);
}
else
{
resultParts.Add("UPPER(@" + bindVar.Name + ")");
}
resultBindVars.Add(bindVar);
if (likeFilter.EscapeChar.HasValue)
{
bindVar = new BindVar(bvNamespace.CreateNewBindVarName("E"), likeFilter.EscapeChar.ToString(), typeof(Char));
resultParts.Add(" ESCAPE @" + bindVar.Name);
resultBindVars.Add(bindVar);
}
return;
}
BetweenFilter betweenFilter = filter as BetweenFilter;
if (betweenFilter != null)
{
resultParts.Add(attributeOperand);
resultParts.Add(" BETWEEN ");
BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), betweenFilter.Value1, attributeType);
resultParts.Add("@" + bindVar.Name);
resultBindVars.Add(bindVar);
resultParts.Add(" AND ");
bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), betweenFilter.Value2, attributeType);
resultParts.Add("@" + bindVar.Name);
resultBindVars.Add(bindVar);
return;
}
InFilter inFilter = filter as InFilter;
if (inFilter != null)
{
if (inFilter.Values.Count == 0)
{
resultParts.Add("1 = 2"); // No comparison values means filter is always false
return;
}
int clusterCount = 0;
foreach (IList <object> valuesCluster in CollectionUtils.Cluster(inFilter.Values, MAX_IN_VALUES_SIZE))
{
if (clusterCount > 0)
{
resultParts.Add(" OR ");
}
resultParts.Add(attributeOperand);
IList <string> bindVarRefs = new List <string>(MAX_IN_VALUES_SIZE);
foreach (object value in valuesCluster)
{
BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), value, attributeType);
bindVarRefs.Add("@" + bindVar.Name);
resultBindVars.Add(bindVar);
}
resultParts.Add(" IN (" + StringUtils.Join(", ", bindVarRefs) + ")");
clusterCount++;
}
return;
}
throw new InvalidDataException("Filter type '{0}' isn't supported by the media library", filter.GetType().Name);
}
protected void GenerateSqlStatement(bool groupByValues,
IDictionary <MediaItemAspectMetadata, string> miamAliases,
out string mediaItemIdOrGroupSizeAlias,
out IDictionary <QueryAttribute, string> attributeAliases,
out string statementStr, out IList <BindVar> bindVars)
{
Namespace ns = new Namespace();
BindVarNamespace bvNamespace = new BindVarNamespace();
// Contains a mapping of each queried (=selected or filtered) attribute to its request attribute instance
// data (which holds its requested query table instance)
IDictionary <QueryAttribute, RequestedAttribute> requestedAttributes = new Dictionary <QueryAttribute, RequestedAttribute>();
attributeAliases = new Dictionary <QueryAttribute, string>();
// Contains a list of qualified attribute names for all select attributes - needed for GROUP BY-expressions
ICollection <string> qualifiedGroupByAliases = new List <string>();
// Contains a list of compiled select attribute declarations. We need this in a separate list (in contrast to using
// the selectAttributes list together with the compiledAttributes map) because it might be the case that
// an attribute is requested twice. In that rare case, we need a new alias name for it.
IList <string> selectAttributeDeclarations = new List <string>();
// Dictionary containing as key the requested MIAM instance OR attribute specification of cardinality MTO,
// mapped to the table query data to request its contents.
IDictionary <object, TableQueryData> tableQueries = new Dictionary <object, TableQueryData>();
// Contains the same tables as the tableQueries variable, but in order and enriched with table join data
IList <TableJoin> tableJoins = new List <TableJoin>();
// Contains all table query data for MIA type tables
IDictionary <MediaItemAspectMetadata, TableQueryData> miaTypeTableQueries = new Dictionary <MediaItemAspectMetadata, TableQueryData>();
// Albert, 2012-01-29: Optimized query, don't join with media items table, if we have necessary requested MIAs. In that case,
// we can use one of their media item ids.
//// First create the request table query data for the MIA main table and the request attribute for the MIA ID.
//// We'll need the requested attribute as join attribute soon.
//TableQueryData miaTableQuery = new TableQueryData(MediaLibrary_SubSchema.MEDIA_ITEMS_TABLE_NAME);
//RequestedAttribute miaIdAttribute = new RequestedAttribute(miaTableQuery, MediaLibrary_SubSchema.MEDIA_ITEMS_ITEM_ID_COL_NAME);
RequestedAttribute miaIdAttribute = null; // Lazy initialized below
// Contains CompiledSortInformation instances for each sort information instance
IList <CompiledSortInformation> compiledSortInformation = null;
List <BindVar> sqlVars = new List <BindVar>();
// Ensure that the tables for all necessary MIAs are requested first (INNER JOIN)
foreach (MediaItemAspectMetadata miaType in _necessaryRequestedMIAs)
{
if (miaType.IsTransientAspect)
{
continue;
}
if (tableQueries.ContainsKey(miaType))
{
// We only come here if miaType was already queried as necessary MIA, so optimize redundant entry
continue;
}
TableQueryData tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType);
miaTypeTableQueries.Add(miaType, tqd);
RequestedAttribute ra;
// The first table join has invalid join attributes because miaIdAttribute is still null - but only the join table attribute is necessary
// for the the first table - see below
tableJoins.Add(new TableJoin("INNER JOIN", tqd,
ra = new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute));
if (miaIdAttribute == null)
{
miaIdAttribute = ra;
}
}
if (miaIdAttribute == null)
{ // If we didn't request any necessary MIA types, we have to add an artificial table for the miaIdAttribute
TableQueryData miaTableQuery = new TableQueryData(MediaLibrary_SubSchema.MEDIA_ITEMS_TABLE_NAME);
tableJoins.Add(new TableJoin("INNER JOIN", miaTableQuery, null, null)); // First table join has invalid join attributes - not needed for first table
miaIdAttribute = new RequestedAttribute(miaTableQuery, MediaLibrary_SubSchema.MEDIA_ITEMS_ITEM_ID_COL_NAME);
}
// Ensure that the tables for all optional MIAs are requested first (LEFT OUTER JOIN)
// That is necessary to make empty optional MIA types available in the result
foreach (MediaItemAspectMetadata miaType in _optionalRequestedMIAs)
{
if (miaType.IsTransientAspect)
{
continue;
}
if (tableQueries.ContainsKey(miaType))
{
// We only come here if miaType was already queried as necessary or optional MIA, so optimize redundant entry
continue;
}
TableQueryData tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType);
miaTypeTableQueries.Add(miaType, tqd);
tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqd,
new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute));
}
// Build table query data for each selected Inline and MTO attribute
// + select query attribute
// + add alias to selectAttributeDeclarations
foreach (QueryAttribute attr in _selectAttributes)
{
if (attr.Attr.ParentMIAM.IsTransientAspect)
{
continue;
}
RequestedAttribute ra;
RequestSimpleAttribute(attr, tableQueries, tableJoins, "LEFT OUTER JOIN", requestedAttributes, miaTypeTableQueries,
miaIdAttribute, out ra);
string alias;
selectAttributeDeclarations.Add(_selectProjectionFunction == null ?
ra.GetDeclarationWithAlias(ns, out alias) :
ra.GetDeclarationWithAlias(ns, _selectProjectionFunction, out alias));
attributeAliases.Add(attr, alias);
qualifiedGroupByAliases.Add(ra.GetAlias(ns));
}
CompiledFilter compiledFilter = CreateCompiledFilter(ns, bvNamespace, miaIdAttribute.GetQualifiedName(ns), tableJoins);
// Build table query data for each Inline attribute which is part of a filter
// + compile query attribute
foreach (QueryAttribute attr in compiledFilter.RequiredAttributes)
{
if (attr.Attr.ParentMIAM.IsTransientAspect)
{
continue;
}
if (attr.Attr.Cardinality != Cardinality.Inline && attr.Attr.Cardinality != Cardinality.ManyToOne)
{
continue;
}
// Tables of Inline and MTO attributes, which are part of a filter, are joined with main table
RequestedAttribute ra;
RequestSimpleAttribute(attr, tableQueries, tableJoins, "LEFT OUTER JOIN", requestedAttributes,
miaTypeTableQueries, miaIdAttribute, out ra);
}
// Build table query data for each sort attribute
if (_sortInformation != null)
{
compiledSortInformation = new List <CompiledSortInformation>();
BindVar userVar = null;
foreach (ISortInformation sortInformation in _sortInformation)
{
AttributeSortInformation attributeSort = sortInformation as AttributeSortInformation;
if (attributeSort != null)
{
if (attributeSort.AttributeType.ParentMIAM.IsTransientAspect)
{
continue;
}
MediaItemAspectMetadata.AttributeSpecification attr = attributeSort.AttributeType;
if (attr.Cardinality != Cardinality.Inline && attr.Cardinality != Cardinality.ManyToOne)
{
// Sorting can only be done for Inline and MTO attributes
continue;
}
RequestedAttribute ra;
RequestSimpleAttribute(new QueryAttribute(attr), tableQueries, tableJoins, "LEFT OUTER JOIN", requestedAttributes,
miaTypeTableQueries, miaIdAttribute, out ra);
compiledSortInformation.Add(new CompiledSortInformation(ra, attributeSort.Direction));
continue;
}
DataSortInformation dataSort = sortInformation as DataSortInformation;
if (dataSort != null && _userProfileId.HasValue)
{
TableQueryData tqd = new TableQueryData(UserProfileDataManagement.UserProfileDataManagement_SubSchema.USER_MEDIA_ITEM_DATA_TABLE_NAME);
RequestedAttribute ra = new RequestedAttribute(tqd, UserProfileDataManagement.UserProfileDataManagement_SubSchema.USER_DATA_VALUE_COL_NAME);
compiledSortInformation.Add(new CompiledSortInformation(ra, dataSort.Direction));
if (userVar == null)
{
userVar = new BindVar("UID", _userProfileId.Value, typeof(Guid));
sqlVars.Add(userVar);
}
TableJoin join = new TableJoin("LEFT OUTER JOIN", tqd, new RequestedAttribute(tqd, UserProfileDataManagement.UserProfileDataManagement_SubSchema.USER_PROFILE_ID_COL_NAME), "@" + userVar.Name);
join.AddCondition(new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute);
join.AddCondition(new RequestedAttribute(tqd, UserProfileDataManagement.UserProfileDataManagement_SubSchema.USER_DATA_KEY_COL_NAME), $"'{dataSort.UserDataKey}'");
tableJoins.Add(join);
}
}
}
StringBuilder result = new StringBuilder("SELECT ");
string groupClause = StringUtils.Join(", ", qualifiedGroupByAliases.Select(alias => "V." + alias));
if (groupByValues)
{
mediaItemIdOrGroupSizeAlias = "C";
// Create an additional COUNT expression for the MEDIA_ITEMS.MEDIA_ITEM_ID in the GROUP BY-statement
result.Append("COUNT(V.C) ");
result.Append(mediaItemIdOrGroupSizeAlias);
if (!string.IsNullOrWhiteSpace(groupClause))
{
result.Append(", ");
result.Append(groupClause);
}
result.Append(" FROM (");
result.Append("SELECT DISTINCT ");
result.Append(miaIdAttribute.GetQualifiedName(ns));
result.Append(" C");
}
else
{
// Append plain attribute MEDIA_ITEMS.MEDIA_ITEM_ID if no GROUP BY-statement is requested
result.Append(miaIdAttribute.GetDeclarationWithAlias(ns, out mediaItemIdOrGroupSizeAlias));
// System attributes: Necessary to evaluate if a requested MIA is present for the media item
foreach (KeyValuePair <MediaItemAspectMetadata, TableQueryData> kvp in miaTypeTableQueries)
{
result.Append(", ");
string miamColumn = kvp.Value.GetAlias(ns) + "." + MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME;
result.Append(miamColumn);
string miamAlias = ns.GetOrCreate(miamColumn, "A");
result.Append(" ");
result.Append(miamAlias);
if (miamAlias != null)
{
miamAliases.Add(kvp.Key, miamAlias);
}
}
}
// Selected attributes
foreach (string selectAttr in selectAttributeDeclarations)
{
result.Append(", ");
result.Append(selectAttr);
}
string whereStr = compiledFilter.CreateSqlFilterCondition(ns, requestedAttributes, out bindVars);
foreach (BindVar bv in sqlVars)
{
bindVars.Add(bv);
}
result.Append(" FROM ");
bool firstJoinTable = true;
// Other joined tables
foreach (TableJoin tableJoin in tableJoins)
{
if (firstJoinTable)
{
result.Append(tableJoin.JoinedTable.GetDeclarationWithAlias(ns));
firstJoinTable = false;
}
else
{
result.Append(tableJoin.GetJoinDeclaration(ns));
}
result.Append(' ');
}
if (!string.IsNullOrEmpty(whereStr))
{
result.Append(" WHERE ");
result.Append(whereStr);
}
if (groupByValues)
{
result.Append(") V");
if (qualifiedGroupByAliases.Count > 0)
{
result.Append(" GROUP BY ");
result.Append(groupClause);
}
}
else
{
if (compiledSortInformation != null && compiledSortInformation.Count > 0)
{
IEnumerable <string> sortCriteria = compiledSortInformation.Select(csi => csi.GetSortDeclaration(ns));
result.Append(" ORDER BY ");
result.Append(StringUtils.Join(", ", sortCriteria));
}
}
statementStr = result.ToString();
}
protected void CompileStatementParts(MIA_Management miaManagement, IFilter filter, Namespace ns, BindVarNamespace bvNamespace,
ICollection<MediaItemAspectMetadata> requiredMIATypes, string outerMIIDJoinVariable, ICollection<TableJoin> tableJoins,
IList<object> resultParts, IList<BindVar> resultBindVars)
{
if (filter == null)
return;
MediaItemIdFilter mediaItemIdFilter = filter as MediaItemIdFilter;
if (mediaItemIdFilter != null)
{
ICollection<Guid> mediaItemIds = mediaItemIdFilter.MediaItemIds;
if (mediaItemIds.Count == 0)
resultParts.Add("1 = 2");
else
{
if (mediaItemIds.Count == 1)
{
resultParts.Add(outerMIIDJoinVariable);
BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), mediaItemIds.First(), typeof(Guid));
resultParts.Add(" = @" + bindVar.Name);
resultBindVars.Add(bindVar);
}
else
{
bool first = true;
ICollection<string> clusterExpressions = new List<string>();
foreach (IList<Guid> mediaItemIdsCluster in CollectionUtils.Cluster(mediaItemIds, MAX_IN_VALUES_SIZE))
{
IList<string> bindVarRefs = new List<string>(MAX_IN_VALUES_SIZE);
foreach (Guid mediaItemId in mediaItemIdsCluster)
{
BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), mediaItemId, typeof(Guid));
bindVarRefs.Add("@" + bindVar.Name);
resultBindVars.Add(bindVar);
}
if (!first)
resultParts.Add(" OR ");
first = false;
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(" IN (" + StringUtils.Join(", ", bindVarRefs) + ")");
}
resultParts.Add(StringUtils.Join(" OR ", clusterExpressions));
}
}
return;
}
BooleanCombinationFilter boolFilter = filter as BooleanCombinationFilter;
if (boolFilter != null)
{
int numOperands = boolFilter.Operands.Count;
IEnumerator enumOperands = boolFilter.Operands.GetEnumerator();
if (!enumOperands.MoveNext())
return;
if (numOperands > 1)
resultParts.Add("(");
CompileStatementParts(miaManagement, (IFilter) enumOperands.Current, ns, bvNamespace,
requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
while (enumOperands.MoveNext())
{
switch (boolFilter.Operator)
{
case BooleanOperator.And:
resultParts.Add(" AND ");
break;
case BooleanOperator.Or:
resultParts.Add(" OR ");
break;
default:
throw new NotImplementedException(string.Format(
"Boolean filter operator '{0}' isn't supported by the media library", boolFilter.Operator));
}
CompileStatementParts(miaManagement, (IFilter) enumOperands.Current, ns, bvNamespace,
requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
}
if (numOperands > 1)
resultParts.Add(")");
return;
}
NotFilter notFilter = filter as NotFilter;
if (notFilter != null)
{
resultParts.Add("NOT (");
CompileStatementParts(miaManagement, notFilter.InnerFilter, ns, bvNamespace,
requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
resultParts.Add(")");
return;
}
FalseFilter falseFilter = filter as FalseFilter;
if (falseFilter != null)
{
resultParts.Add("1 = 2");
return;
}
// Must be done before checking IAttributeFilter - EmptyFilter is also an IAttributeFilter but must be
// compiled in a different way
EmptyFilter emptyFilter = filter as EmptyFilter;
if (emptyFilter != null)
{
MediaItemAspectMetadata.AttributeSpecification attributeType = emptyFilter.AttributeType;
requiredMIATypes.Add(attributeType.ParentMIAM);
Cardinality cardinality = attributeType.Cardinality;
if (cardinality == Cardinality.Inline || cardinality == Cardinality.ManyToOne)
{
resultParts.Add(new QueryAttribute(attributeType));
resultParts.Add(" IS NULL"); // MTO attributes are joined with left outer joins and thus can also be checked for NULL
}
else if (cardinality == Cardinality.OneToMany)
{
resultParts.Add("NOT EXISTS(");
resultParts.Add("SELECT V.");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add(" FROM ");
resultParts.Add(miaManagement.GetMIACollectionAttributeTableName(attributeType));
resultParts.Add(" V WHERE V.");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add("=");
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(")");
}
else if (cardinality == Cardinality.ManyToMany)
{
resultParts.Add("NOT EXISTS(");
resultParts.Add("SELECT NM.");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add(" FROM ");
resultParts.Add(miaManagement.GetMIACollectionAttributeNMTableName(attributeType));
resultParts.Add(" NM INNER JOIN ");
resultParts.Add(miaManagement.GetMIACollectionAttributeTableName(attributeType));
resultParts.Add(" V ON NM.");
resultParts.Add(MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME);
resultParts.Add(" = V.");
resultParts.Add(MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME);
resultParts.Add(" WHERE NM.");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add("=");
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(")");
}
return;
}
IAttributeFilter attributeFilter = filter as IAttributeFilter;
if (attributeFilter != null)
{
// For attribute filters, we have to create different kinds of expressions, depending on the
// cardinality of the attribute to be filtered.
// For Inline and MTO attributes, we simply create
//
// QA [Operator] [Comparison-Value]
//
// for OTM attributes, we create
//
// INNER JOIN [OTM-Value-Table] V ON V.MEDIA_ITEM_ID=[Outer-Join-Variable-Placeholder]
// WHERE [...] and V.VALUE [Operator] [Comparison-Value])
//
// for MTM attributes, we create
//
// INNER JOIN [MTM-NM-Table] NM ON NM.MEDIA_ITEM_ID=[Outer-Join-Variable-Placeholder]
// INNER JOIN [MTM-Value-Table] V ON NM.ID = V.ID
// WHERE [...] AND V.VALUE [Operator] [Comparison-Value])
MediaItemAspectMetadata.AttributeSpecification attributeType = attributeFilter.AttributeType;
requiredMIATypes.Add(attributeType.ParentMIAM);
Cardinality cardinality = attributeType.Cardinality;
if (cardinality == Cardinality.Inline || cardinality == Cardinality.ManyToOne)
BuildAttributeFilterExpression(attributeFilter, new QueryAttribute(attributeType), bvNamespace,
resultParts, resultBindVars);
else if (cardinality == Cardinality.OneToMany)
{
string joinTable = miaManagement.GetMIACollectionAttributeTableName(attributeType);
string attrName;
if (!_innerJoinedTables.TryGetValue(joinTable, out attrName))
{
TableQueryData tqd = new TableQueryData(joinTable);
tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqd,
new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), outerMIIDJoinVariable));
attrName = new RequestedAttribute(tqd, MIA_Management.COLL_ATTR_VALUE_COL_NAME).GetQualifiedName(ns);
_innerJoinedTables.Add(joinTable, attrName);
}
BuildAttributeFilterExpression(attributeFilter, attrName, bvNamespace, resultParts, resultBindVars);
}
else if (cardinality == Cardinality.ManyToMany)
{
string miaCollectionAttributeNMTableName = miaManagement.GetMIACollectionAttributeNMTableName(attributeType);
string attrName;
if (!_innerJoinedTables.TryGetValue(miaCollectionAttributeNMTableName, out attrName))
{
TableQueryData tqdMiaCollectionAttributeNMTable = new TableQueryData(miaCollectionAttributeNMTableName);
tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqdMiaCollectionAttributeNMTable,
new RequestedAttribute(tqdMiaCollectionAttributeNMTable, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), outerMIIDJoinVariable));
TableQueryData tqdMiaCollectionAttributeTable = new TableQueryData(miaManagement.GetMIACollectionAttributeTableName(attributeType));
tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqdMiaCollectionAttributeTable,
new RequestedAttribute(tqdMiaCollectionAttributeNMTable, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME),
new RequestedAttribute(tqdMiaCollectionAttributeTable, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME)));
attrName = tqdMiaCollectionAttributeTable.GetAlias(ns) + "." + MIA_Management.COLL_ATTR_VALUE_COL_NAME;
_innerJoinedTables.Add(miaCollectionAttributeNMTableName, attrName);
}
BuildAttributeFilterExpression(attributeFilter, attrName, bvNamespace, resultParts, resultBindVars);
}
return;
}
throw new InvalidDataException("Filter type '{0}' isn't supported by the media library", filter.GetType().Name);
}
/// <summary>
/// Builds the actual filter SQL expression <c>[Attribute-Operand] [Operator] [Comparison-Value]</c> for the given
/// attribute <paramref name="filter"/>.
/// </summary>
/// <param name="filter">Attribute filter instance to create the SQL expression for.</param>
/// <param name="attributeOperand">Comparison attribute to be used. Depending on the cardinality of the
/// to-be-filtered attribute, this will be the inline attribute alias or the attribute alias of the collection
/// attribute table.</param>
/// <param name="bvNamespace">Namespace used to build bind var names.</param>
/// <param name="resultParts">Statement parts for the attribute filter.</param>
/// <param name="resultBindVars">Bind variables for the attribute filter.</param>
public static void BuildAttributeFilterExpression(IAttributeFilter filter, object attributeOperand, BindVarNamespace bvNamespace,
IList<object> resultParts, IList<BindVar> resultBindVars)
{
Type attributeType = filter.AttributeType.AttributeType;
RelationalFilter relationalFilter = filter as RelationalFilter;
if (relationalFilter != null)
{
resultParts.Add(attributeOperand);
switch (relationalFilter.Operator)
{
case RelationalOperator.EQ:
resultParts.Add(" = ");
break;
case RelationalOperator.NEQ:
resultParts.Add(" <> ");
break;
case RelationalOperator.LT:
resultParts.Add(" < ");
break;
case RelationalOperator.LE:
resultParts.Add(" <= ");
break;
case RelationalOperator.GT:
resultParts.Add(" > ");
break;
case RelationalOperator.GE:
resultParts.Add(" >= ");
break;
default:
throw new NotImplementedException(string.Format(
"Relational filter operator '{0}' isn't supported by the media library", relationalFilter.Operator));
}
BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), relationalFilter.FilterValue, attributeType);
resultParts.Add("@" + bindVar.Name);
resultBindVars.Add(bindVar);
return;
}
LikeFilter likeFilter = filter as LikeFilter;
if (likeFilter != null)
{
if (!likeFilter.CaseSensitive)
resultParts.Add("UPPER(");
resultParts.Add(attributeOperand);
if (!likeFilter.CaseSensitive)
resultParts.Add(")");
resultParts.Add(" LIKE ");
BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), likeFilter.Expression, attributeType);
if (likeFilter.CaseSensitive)
resultParts.Add("@" + bindVar.Name);
else
resultParts.Add("UPPER(@" + bindVar.Name + ")");
resultBindVars.Add(bindVar);
if (likeFilter.EscapeChar.HasValue)
{
bindVar = new BindVar(bvNamespace.CreateNewBindVarName("E"), likeFilter.EscapeChar.ToString(), typeof(Char));
resultParts.Add(" ESCAPE @" + bindVar.Name);
resultBindVars.Add(bindVar);
}
return;
}
BetweenFilter betweenFilter = filter as BetweenFilter;
if (betweenFilter != null)
{
resultParts.Add(attributeOperand);
resultParts.Add(" BETWEEN ");
BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), betweenFilter.Value1, attributeType);
resultParts.Add("@" + bindVar.Name);
resultBindVars.Add(bindVar);
resultParts.Add(" AND ");
bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), betweenFilter.Value2, attributeType);
resultParts.Add("@" + bindVar.Name);
resultBindVars.Add(bindVar);
return;
}
InFilter inFilter = filter as InFilter;
if (inFilter != null)
{
if (inFilter.Values.Count == 0)
{
resultParts.Add("1 = 2"); // No comparison values means filter is always false
return;
}
ICollection<string> clusterExpressions = new List<string>();
foreach (IList<object> valuesCluster in CollectionUtils.Cluster(inFilter.Values, MAX_IN_VALUES_SIZE))
{
IList<string> bindVarRefs = new List<string>(MAX_IN_VALUES_SIZE);
foreach (object value in valuesCluster)
{
BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), value, attributeType);
bindVarRefs.Add("@" + bindVar.Name);
resultBindVars.Add(bindVar);
}
clusterExpressions.Add(" IN (" + StringUtils.Join(", ", bindVarRefs) + ")");
}
resultParts.Add(StringUtils.Join(" OR ", clusterExpressions));
return;
}
throw new InvalidDataException("Filter type '{0}' isn't supported by the media library", filter.GetType().Name);
}
protected void CompileStatementParts(MIA_Management miaManagement, IFilter filter, Namespace ns, BindVarNamespace bvNamespace,
ICollection <MediaItemAspectMetadata> requiredMIATypes, string outerMIIDJoinVariable, ICollection <TableJoin> tableJoins,
IList <object> resultParts, IList <BindVar> resultBindVars)
{
if (filter == null)
{
return;
}
MediaItemIdFilter mediaItemIdFilter = filter as MediaItemIdFilter;
if (mediaItemIdFilter != null)
{
ICollection <Guid> mediaItemIds = mediaItemIdFilter.MediaItemIds;
if (mediaItemIds.Count == 0)
{
resultParts.Add("1 = 2");
}
else
{
if (mediaItemIds.Count == 1)
{
resultParts.Add(outerMIIDJoinVariable);
BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), mediaItemIds.First(), typeof(Guid));
resultParts.Add(" = @" + bindVar.Name);
resultBindVars.Add(bindVar);
}
else
{
bool first = true;
ICollection <string> clusterExpressions = new List <string>();
foreach (IList <Guid> mediaItemIdsCluster in CollectionUtils.Cluster(mediaItemIds, MAX_IN_VALUES_SIZE))
{
IList <string> bindVarRefs = new List <string>(MAX_IN_VALUES_SIZE);
foreach (Guid mediaItemId in mediaItemIdsCluster)
{
BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), mediaItemId, typeof(Guid));
bindVarRefs.Add("@" + bindVar.Name);
resultBindVars.Add(bindVar);
}
if (!first)
{
resultParts.Add(" OR ");
}
first = false;
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(" IN (" + StringUtils.Join(", ", bindVarRefs) + ")");
}
resultParts.Add(StringUtils.Join(" OR ", clusterExpressions));
}
}
return;
}
BooleanCombinationFilter boolFilter = filter as BooleanCombinationFilter;
if (boolFilter != null)
{
int numOperands = boolFilter.Operands.Count;
IEnumerator enumOperands = boolFilter.Operands.GetEnumerator();
if (!enumOperands.MoveNext())
{
return;
}
if (numOperands > 1)
{
resultParts.Add("(");
}
CompileStatementParts(miaManagement, (IFilter)enumOperands.Current, ns, bvNamespace,
requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
while (enumOperands.MoveNext())
{
switch (boolFilter.Operator)
{
case BooleanOperator.And:
resultParts.Add(" AND ");
break;
case BooleanOperator.Or:
resultParts.Add(" OR ");
break;
default:
throw new NotImplementedException(string.Format(
"Boolean filter operator '{0}' isn't supported by the media library", boolFilter.Operator));
}
CompileStatementParts(miaManagement, (IFilter)enumOperands.Current, ns, bvNamespace,
requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
}
if (numOperands > 1)
{
resultParts.Add(")");
}
return;
}
NotFilter notFilter = filter as NotFilter;
if (notFilter != null)
{
resultParts.Add("NOT (");
CompileStatementParts(miaManagement, notFilter.InnerFilter, ns, bvNamespace,
requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
resultParts.Add(")");
return;
}
FalseFilter falseFilter = filter as FalseFilter;
if (falseFilter != null)
{
resultParts.Add("1 = 2");
return;
}
// Must be done before checking IAttributeFilter - EmptyFilter is also an IAttributeFilter but must be
// compiled in a different way
EmptyFilter emptyFilter = filter as EmptyFilter;
if (emptyFilter != null)
{
MediaItemAspectMetadata.AttributeSpecification attributeType = emptyFilter.AttributeType;
requiredMIATypes.Add(attributeType.ParentMIAM);
Cardinality cardinality = attributeType.Cardinality;
if (cardinality == Cardinality.Inline || cardinality == Cardinality.ManyToOne)
{
resultParts.Add(new QueryAttribute(attributeType));
resultParts.Add(" IS NULL"); // MTO attributes are joined with left outer joins and thus can also be checked for NULL
}
else if (cardinality == Cardinality.OneToMany)
{
resultParts.Add("NOT EXISTS(");
resultParts.Add("SELECT V.");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add(" FROM ");
resultParts.Add(miaManagement.GetMIACollectionAttributeTableName(attributeType));
resultParts.Add(" V WHERE V.");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add("=");
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(")");
}
else if (cardinality == Cardinality.ManyToMany)
{
resultParts.Add("NOT EXISTS(");
resultParts.Add("SELECT NM.");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add(" FROM ");
resultParts.Add(miaManagement.GetMIACollectionAttributeNMTableName(attributeType));
resultParts.Add(" NM INNER JOIN ");
resultParts.Add(miaManagement.GetMIACollectionAttributeTableName(attributeType));
resultParts.Add(" V ON NM.");
resultParts.Add(MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME);
resultParts.Add(" = V.");
resultParts.Add(MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME);
resultParts.Add(" WHERE NM.");
resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
resultParts.Add("=");
resultParts.Add(outerMIIDJoinVariable);
resultParts.Add(")");
}
return;
}
IAttributeFilter attributeFilter = filter as IAttributeFilter;
if (attributeFilter != null)
{
// For attribute filters, we have to create different kinds of expressions, depending on the
// cardinality of the attribute to be filtered.
// For Inline and MTO attributes, we simply create
//
// QA [Operator] [Comparison-Value]
//
// for OTM attributes, we create
//
// INNER JOIN [OTM-Value-Table] V ON V.MEDIA_ITEM_ID=[Outer-Join-Variable-Placeholder]
// WHERE [...] and V.VALUE [Operator] [Comparison-Value])
//
// for MTM attributes, we create
//
// INNER JOIN [MTM-NM-Table] NM ON NM.MEDIA_ITEM_ID=[Outer-Join-Variable-Placeholder]
// INNER JOIN [MTM-Value-Table] V ON NM.ID = V.ID
// WHERE [...] AND V.VALUE [Operator] [Comparison-Value])
MediaItemAspectMetadata.AttributeSpecification attributeType = attributeFilter.AttributeType;
requiredMIATypes.Add(attributeType.ParentMIAM);
Cardinality cardinality = attributeType.Cardinality;
if (cardinality == Cardinality.Inline || cardinality == Cardinality.ManyToOne)
{
BuildAttributeFilterExpression(attributeFilter, new QueryAttribute(attributeType), bvNamespace,
resultParts, resultBindVars);
}
else if (cardinality == Cardinality.OneToMany)
{
string joinTable = miaManagement.GetMIACollectionAttributeTableName(attributeType);
string attrName;
if (!_innerJoinedTables.TryGetValue(joinTable, out attrName))
{
TableQueryData tqd = new TableQueryData(joinTable);
tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqd,
new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), outerMIIDJoinVariable));
attrName = new RequestedAttribute(tqd, MIA_Management.COLL_ATTR_VALUE_COL_NAME).GetQualifiedName(ns);
_innerJoinedTables.Add(joinTable, attrName);
}
BuildAttributeFilterExpression(attributeFilter, attrName, bvNamespace, resultParts, resultBindVars);
}
else if (cardinality == Cardinality.ManyToMany)
{
string miaCollectionAttributeNMTableName = miaManagement.GetMIACollectionAttributeNMTableName(attributeType);
string attrName;
if (!_innerJoinedTables.TryGetValue(miaCollectionAttributeNMTableName, out attrName))
{
TableQueryData tqdMiaCollectionAttributeNMTable = new TableQueryData(miaCollectionAttributeNMTableName);
tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqdMiaCollectionAttributeNMTable,
new RequestedAttribute(tqdMiaCollectionAttributeNMTable, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), outerMIIDJoinVariable));
TableQueryData tqdMiaCollectionAttributeTable = new TableQueryData(miaManagement.GetMIACollectionAttributeTableName(attributeType));
tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqdMiaCollectionAttributeTable,
new RequestedAttribute(tqdMiaCollectionAttributeNMTable, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME),
new RequestedAttribute(tqdMiaCollectionAttributeTable, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME)));
attrName = tqdMiaCollectionAttributeTable.GetAlias(ns) + "." + MIA_Management.COLL_ATTR_VALUE_COL_NAME;
_innerJoinedTables.Add(miaCollectionAttributeNMTableName, attrName);
}
BuildAttributeFilterExpression(attributeFilter, attrName, bvNamespace, resultParts, resultBindVars);
}
return;
}
throw new InvalidDataException("Filter type '{0}' isn't supported by the media library", filter.GetType().Name);
}
private string BuildRelationshipPart(ChildAggregateAttributeSortInformation childAttributeSort, MediaItemAspectMetadata childMetadata, bool reverse, BindVar roleVar, BindVar linkedRoleVar)
{
string selectColumn;
string roleColumn;
string linkedRoleColumn;
string linkedIdColumn;
if (reverse)
{
//if this is the reverse part, reverse the column names
selectColumn = _miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ID);
roleColumn = _miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ROLE);
linkedRoleColumn = _miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_ROLE);
linkedIdColumn = MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME;
}
else
{
selectColumn = MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME;
roleColumn = _miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_ROLE);
linkedRoleColumn = _miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ROLE);
linkedIdColumn = _miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ID);
}
StringBuilder joinSql = new StringBuilder();
joinSql.Append($"SELECT R.{selectColumn} ID, ");
if (childAttributeSort.AggregateFunction == AggregateFunction.Avg)
{
joinSql.Append("AVG");
}
else if (childAttributeSort.AggregateFunction == AggregateFunction.Count)
{
joinSql.Append("COUNT");
}
else if (childAttributeSort.AggregateFunction == AggregateFunction.Max)
{
joinSql.Append("MAX");
}
else if (childAttributeSort.AggregateFunction == AggregateFunction.Min)
{
joinSql.Append("MIN");
}
else if (childAttributeSort.AggregateFunction == AggregateFunction.Sum)
{
joinSql.Append("SUM");
}
if (childMetadata.AspectId != childAttributeSort.ChildAttributeType.ParentMIAM.AspectId)
{
joinSql.Append($"(S.{_miaManagement.GetMIAAttributeColumnName(childAttributeSort.ChildAttributeType)}) SORT");
}
else
{
joinSql.Append($"(C.{_miaManagement.GetMIAAttributeColumnName(childAttributeSort.ChildAttributeType)}) SORT");
}
joinSql.Append(" FROM ");
joinSql.Append(_miaManagement.GetMIATableName(childMetadata));
joinSql.Append(" C");
joinSql.Append(" INNER JOIN ");
joinSql.Append(_miaManagement.GetMIATableName(RelationshipAspect.Metadata));
joinSql.Append(" R ON R.");
joinSql.Append(linkedIdColumn);
joinSql.Append("=C.");
joinSql.Append(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
joinSql.Append(" AND R.");
joinSql.Append(linkedRoleColumn);
joinSql.Append("=@" + linkedRoleVar.Name);
joinSql.Append(" AND R.");
joinSql.Append(roleColumn);
joinSql.Append("=@" + roleVar.Name);
//If sort property is from a different aspect we must also join the sort aspect
if (childMetadata.AspectId != childAttributeSort.ChildAttributeType.ParentMIAM.AspectId)
{
joinSql.Append(" LEFT OUTER JOIN ");
joinSql.Append(_miaManagement.GetMIATableName(childAttributeSort.ChildAttributeType.ParentMIAM));
joinSql.Append($" S ON S.");
joinSql.Append(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
joinSql.Append("=C.");
joinSql.Append(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
}
joinSql.Append($" GROUP BY R.{selectColumn}");
return(joinSql.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();
}
