public async Task <bool> LoadPlaylist()
{
IServerConnectionManager scm = ServiceRegistration.Get <IServerConnectionManager>();
IContentDirectory cd = scm.ContentDirectory;
if (cd == null)
{
ShowServerNotConnectedDialog();
return(false);
}
// Big playlists cannot be loaded in one single step. We have several problems if we try to do so:
// 1) Loading the playlist at once at the server results in one huge SQL IN statement which might break the SQL engine
// 2) The time to load the playlist might lead the UPnP call to break because of the timeout when calling methods
// 3) The resulting UPnP XML document might be too big to fit into memory
// For that reason, we load the playlist in two steps:
// 1) Load media item ids in the playlist
// 2) Load media items in clusters - for each cluster, an own query will be executed at the content directory
PlaylistRawData playlistData = await cd.ExportPlaylistAsync(_playlistId);
List <MediaItem> result = new List <MediaItem>();
foreach (IList <Guid> itemIds in CollectionUtils.Cluster(playlistData.MediaItemIds, 500))
{
result.AddRange(await cd.LoadCustomPlaylistAsync(itemIds, Consts.NECESSARY_AUDIO_MIAS, Consts.EMPTY_GUID_ENUMERATION));
}
_mediaItems = result;
return(true);
}
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) + ")");
}
}
internal static async Task <(string Name, IEnumerable <MediaItem> Items)> LoadPlayListAsync(Guid playlistId)
{
IContentDirectory cd = ServiceRegistration.Get <IServerConnectionManager>().ContentDirectory;
Guid[] necessaryMIATypes = new Guid[]
{
ProviderResourceAspect.ASPECT_ID,
MediaAspect.ASPECT_ID,
};
Guid[] optionalMIATypes = new Guid[]
{
AudioAspect.ASPECT_ID,
VideoAspect.ASPECT_ID,
ImageAspect.ASPECT_ID,
MovieAspect.ASPECT_ID,
EpisodeAspect.ASPECT_ID,
GenreAspect.ASPECT_ID,
VideoStreamAspect.ASPECT_ID,
};
PlaylistRawData playlistData = await cd.ExportPlaylistAsync(playlistId);
List <MediaItem> items = new List <MediaItem>();
foreach (var cluster in CollectionUtils.Cluster(playlistData.MediaItemIds, 1000))
{
items.AddRange(await cd.LoadCustomPlaylistAsync(cluster, necessaryMIATypes, optionalMIATypes));
}
return(playlistData.Name, items);
}
protected void SendMulticastEventNotification(DvService service, IEnumerable <DvStateVariable> variables)
{
DvDevice device = service.ParentDevice;
EventingState eventingState = _serverData.GetMulticastEventKey(service);
// First cluster variables by multicast event level so we can put variables of the same event level into a single message
IDictionary <string, ICollection <DvStateVariable> > variablesByLevel =
new Dictionary <string, ICollection <DvStateVariable> >();
foreach (DvStateVariable variable in variables)
{
ICollection <DvStateVariable> variablesCollection;
if (!variablesByLevel.TryGetValue(variable.MulticastEventLevel, out variablesCollection))
{
variablesByLevel[variable.MulticastEventLevel] = variablesCollection = new List <DvStateVariable>();
}
variablesCollection.Add(variable);
}
foreach (KeyValuePair <string, ICollection <DvStateVariable> > varByLevel in variablesByLevel)
{
// Use a maximum cluster size of GENA_MAX_MULTICAST_EVENT_VAR_COUNT to keep UDP message small
ICollection <IList <DvStateVariable> > variableClusters = CollectionUtils.Cluster(
varByLevel.Value, UPnPConsts.GENA_MAX_MULTICAST_EVENT_VAR_COUNT);
foreach (IList <DvStateVariable> cluster in variableClusters)
{
foreach (DvStateVariable variable in cluster)
{
eventingState.UpdateModerationData(variable);
}
eventingState.IncEventKey();
byte[] bodyData = UPnPConsts.UTF8_NO_BOM.GetBytes(GENAMessageBuilder.BuildEventNotificationMessage(
cluster, false)); // Albert TODO: Is it correct not to force the simple string equivalent for extended data types here?
SimpleHTTPRequest request = new SimpleHTTPRequest("NOTIFY", "*");
request.SetHeader("CONTENT-LENGTH", bodyData.Length.ToString());
request.SetHeader("CONTENT-TYPE", "text/xml; charset=\"utf-8\"");
request.SetHeader("USN", device.UDN + "::" + service.ServiceTypeVersion_URN);
request.SetHeader("SVCID", service.ServiceId);
request.SetHeader("NT", "upnp:event");
request.SetHeader("NTS", "upnp:propchange");
request.SetHeader("SEQ", eventingState.EventKey.ToString());
request.SetHeader("LVL", varByLevel.Key);
request.SetHeader("BOOTID.UPNP.ORG", _serverData.BootId.ToString());
foreach (EndpointConfiguration config in _serverData.UPnPEndPoints)
{
IPEndPoint ep = new IPEndPoint(config.GENAMulticastAddress, UPnPConsts.GENA_MULTICAST_PORT);
request.SetHeader("HOST", NetworkHelper.IPEndPointToString(ep));
request.MessageBody = bodyData;
byte[] bytes = request.Encode();
NetworkHelper.SendData(config.GENA_UDP_Socket, ep, bytes, 1);
}
}
}
}
public void LoadPlaylist()
{
IDialogManager dialogManager = ServiceRegistration.Get <IDialogManager>();
if (_playlist == null)
{
dialogManager.ShowDialog(SkinBase.General.Consts.RES_SYSTEM_ERROR, Consts.RES_PLAYLIST_LOAD_NO_PLAYLIST, DialogType.OkDialog, false, null);
return;
}
IContentDirectory cd = ServiceRegistration.Get <IServerConnectionManager>().ContentDirectory;
AVType? avType = ConvertPlaylistTypeToAVType(_playlist.PlaylistType);
if (cd == null || !avType.HasValue)
{
dialogManager.ShowDialog(SkinBase.General.Consts.RES_SYSTEM_ERROR, Consts.RES_PLAYLIST_LOAD_ERROR_LOADING, DialogType.OkDialog, false, null);
return;
}
Guid[] necessaryMIATypes = new Guid[]
{
ProviderResourceAspect.ASPECT_ID,
MediaAspect.ASPECT_ID,
};
Guid[] optionalMIATypes = new Guid[]
{
AudioAspect.ASPECT_ID,
VideoAspect.ASPECT_ID,
ImageAspect.ASPECT_ID,
};
// Big playlists cannot be loaded in one single step. We have several problems if we try to do so:
// 1) Loading the playlist at once at the server results in one huge SQL IN statement which might break the SQL engine
// 2) The time to load the playlist might lead the UPnP call to break because of the timeout when calling methods
// 3) The resulting UPnP XML document might be too big to fit into memory
// For that reason, we load the playlist in two steps:
// 1) Load media item ids in the playlist
// 2) Load media items in clusters - for each cluster, an own query will be executed at the content directory
PlaylistRawData playlistData = cd.ExportPlaylist(_playlist.PlaylistId);
PlayItemsModel.CheckQueryPlayAction(() => CollectionUtils.Cluster(playlistData.MediaItemIds, 1000).SelectMany(itemIds =>
cd.LoadCustomPlaylist(itemIds, necessaryMIATypes, optionalMIATypes)), avType.Value);
}
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 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);
}
public IList <MediaItem> Query(ISQLDatabase database, ITransaction transaction, bool singleMode)
{
ILogger logger = ServiceRegistration.Get <ILogger>();
try
{
IList <MediaItemAspectMetadata> selectedMIAs = new List <MediaItemAspectMetadata>(_necessaryRequestedMIAs.Union(_optionalRequestedMIAs));
IList <Guid> mediaItemIds;
IDictionary <Guid, IList <Guid> > complexMediaItemIds;
IList <MediaItem> mediaItems = GetMediaItems(database, transaction, singleMode, selectedMIAs, out mediaItemIds, out complexMediaItemIds);
//logger.Debug("CompiledMediaItemQuery::Query got media items IDs [{0}]", string.Join(",", mediaItemIds));
// TODO: Why bother looking for complex attributes on MIAs we don't have?
IDictionary <Guid, IDictionary <MediaItemAspectMetadata.AttributeSpecification, IList> > complexAttributeValues =
new Dictionary <Guid, IDictionary <MediaItemAspectMetadata.AttributeSpecification, IList> >();
ICollection <IList <Guid> > mediaItemIdsClusters = CollectionUtils.Cluster(mediaItemIds, CompiledFilter.MAX_IN_VALUES_SIZE);
foreach (IList <Guid> mediaItemIdsCluster in mediaItemIdsClusters.Where(x => x.Count > 0))
{
AddComplexAttributes(database, transaction, mediaItemIdsCluster, complexAttributeValues, complexMediaItemIds);
}
foreach (MediaItem mediaItem in mediaItems)
{
foreach (SingleMediaItemAspectMetadata miam in selectedMIAs.Where(x => x is SingleMediaItemAspectMetadata))
{
// Skip complex attributes for this MIA if it's not already in the media item
if (!mediaItem.Aspects.ContainsKey(miam.AspectId))
{
continue;
}
IDictionary <MediaItemAspectMetadata.AttributeSpecification, IList> attributeValues;
if (!complexAttributeValues.TryGetValue(mediaItem.MediaItemId, out attributeValues))
{
continue;
}
SingleMediaItemAspect mia = MediaItemAspect.GetOrCreateAspect(mediaItem.Aspects, miam);
foreach (MediaItemAspectMetadata.AttributeSpecification attr in miam.AttributeSpecifications.Values)
{
if (attr.Cardinality != Cardinality.Inline)
{
IList attrValues;
if (attributeValues != null && attributeValues.TryGetValue(attr, out attrValues))
{
mia.SetCollectionAttribute(attr, attrValues);
}
}
}
}
}
IDictionary <Guid, ICollection <MultipleMediaItemAspect> > multipleMiaValues =
new Dictionary <Guid, ICollection <MultipleMediaItemAspect> >();
foreach (IList <Guid> mediaItemIdsCluster in mediaItemIdsClusters.Where(x => x.Count > 0))
{
AddMultipleMIAs(database, transaction, _explicitRequestedMIAs, mediaItemIdsCluster, multipleMiaValues);
}
if (multipleMiaValues.Count > 0)
{
//logger.Debug("Got multiple MIAs [{0}]", string.Join(",", multipleMiaValues.Keys));
foreach (MediaItem mediaItem in mediaItems)
{
ICollection <MultipleMediaItemAspect> values;
if (!multipleMiaValues.TryGetValue(mediaItem.MediaItemId, out values))
{
continue;
}
foreach (MultipleMediaItemAspect value in values)
{
//logger.Debug("Adding MIA {0} #{1}", value.Metadata.Name, value.Index);
MediaItemAspect.AddOrUpdateAspect(mediaItem.Aspects, value);
}
}
}
return(mediaItems);
}
catch (Exception e)
{
logger.Error("Unable to query", e);
throw e;
}
}
