private void AddMultipleMIAs(ISQLDatabase database, ITransaction transaction, IEnumerable <MediaItemAspectMetadata> selectedMIAs, IList <Guid> ids, IDictionary <Guid, ICollection <MultipleMediaItemAspect> > multipleMiaValues)
{
ILogger logger = ServiceRegistration.Get <ILogger>();
foreach (MultipleMediaItemAspectMetadata miam in selectedMIAs.Where(x => x is MultipleMediaItemAspectMetadata))
{
//logger.Debug("Getting {0} rows for {1}", ids.Count, miam.Name);
IDictionary <MediaItemAspectMetadata.AttributeSpecification, QueryAttribute> attributes =
new Dictionary <MediaItemAspectMetadata.AttributeSpecification, QueryAttribute>();
foreach (MediaItemAspectMetadata.AttributeSpecification attr in miam.AttributeSpecifications.Values)
{
if (attr.Cardinality == Cardinality.Inline || attr.Cardinality == Cardinality.ManyToOne)
{
attributes[attr] = new QueryAttribute(attr);
}
}
AddMultipleMIAResults(database, transaction, miam, attributes, new MultipleMIAQueryBuilder(_miaManagement, attributes.Values, miam, ids.ToArray()), multipleMiaValues);
if (miam.AspectId == RelationshipAspect.ASPECT_ID)
{
// Special case for relationships where the IDs being processed could be at the linked end
AddMultipleMIAResults(database, transaction, miam, attributes, new InverseRelationshipQueryBuilder(_miaManagement, attributes.Values, ids.ToArray()), multipleMiaValues);
}
}
}
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) + ")");
}
}
public HomogenousMap Execute()
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
using (IDbCommand command = transaction.CreateCommand())
{
string valueAlias;
string groupSizeAlias;
string statementStr;
IList <BindVar> bindVars;
if (_selectAttribute.Cardinality == Cardinality.Inline || _selectAttribute.Cardinality == Cardinality.ManyToOne)
{
QueryAttribute selectAttributeQA = new QueryAttribute(_selectAttribute);
MainQueryBuilder builder = new MainQueryBuilder(_miaManagement,
new QueryAttribute[] { selectAttributeQA }, _selectProjectionFunction,
_necessaryRequestedMIATypes, new MediaItemAspectMetadata[] {}, _filter, null);
IDictionary <QueryAttribute, string> qa2a;
builder.GenerateSqlGroupByStatement(out groupSizeAlias, out qa2a, out statementStr, out bindVars);
valueAlias = qa2a[selectAttributeQA];
}
else
{
ComplexAttributeQueryBuilder builder = new ComplexAttributeQueryBuilder(_miaManagement, _selectAttribute,
_selectProjectionFunction, _necessaryRequestedMIATypes, _filter);
builder.GenerateSqlGroupByStatement(_selectAttributeFilter, out valueAlias, out groupSizeAlias,
out statementStr, out bindVars);
}
command.CommandText = statementStr;
foreach (BindVar bindVar in bindVars)
{
database.AddParameter(command, bindVar.Name, bindVar.Value, bindVar.VariableType);
}
Type valueType = _projectionValueType ?? _selectAttribute.AttributeType;
HomogenousMap result = new HomogenousMap(valueType, typeof(int));
using (IDataReader reader = command.ExecuteReader())
{
int valueCol = reader.GetOrdinal(valueAlias);
int groupSizeCol = reader.GetOrdinal(groupSizeAlias);
while (reader.Read())
{
result.Add(database.ReadDBValue(valueType, reader, valueCol),
database.ReadDBValue <int>(reader, groupSizeCol));
}
}
return(result);
}
}
finally
{
transaction.Dispose();
}
}
protected void RequestSimpleAttribute(QueryAttribute queryAttribute,
IDictionary<object, TableQueryData> tableQueries, IList<TableJoin> tableJoins, string miaJoinType,
IDictionary<QueryAttribute, RequestedAttribute> requestedAttributes,
IDictionary<MediaItemAspectMetadata, TableQueryData> miaTypeTableQueries,
RequestedAttribute miaIdAttribute, out RequestedAttribute requestedAttribute)
{
if (requestedAttributes.TryGetValue(queryAttribute, out requestedAttribute))
// Already requested
return;
MediaItemAspectMetadata.AttributeSpecification spec = queryAttribute.Attr;
MediaItemAspectMetadata miaType = spec.ParentMIAM;
TableQueryData tqd;
switch (spec.Cardinality)
{
case Cardinality.Inline:
// For Inline queries, we request the Inline attribute's column name at the MIA main table, which gets joined
// with the MIA ID
if (!tableQueries.TryGetValue(miaType, out tqd))
{
tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType);
if (miaTypeTableQueries != null)
miaTypeTableQueries.Add(miaType, tqd);
tableJoins.Add(new TableJoin(miaJoinType, tqd, miaIdAttribute,
new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME)));
}
requestedAttribute = new RequestedAttribute(tqd, _miaManagement.GetMIAAttributeColumnName(queryAttribute.Attr));
break;
case Cardinality.ManyToOne:
// For MTO queries, we request both the MIA main table and the MTO table
TableQueryData miaTqd;
if (!tableQueries.TryGetValue(miaType, out miaTqd))
{
miaTqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType);
if (miaTypeTableQueries != null)
miaTypeTableQueries.Add(miaType, miaTqd);
// Add MIA main table to list of table joins
tableJoins.Add(new TableJoin(miaJoinType, miaTqd, miaIdAttribute,
new RequestedAttribute(miaTqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME)));
}
if (!tableQueries.TryGetValue(spec, out tqd))
{
tqd = tableQueries[spec] = TableQueryData.CreateTableQueryOfMTOTable(_miaManagement, spec);
// We must use left outer joins for MTO value tables, because if the value is null, the association FK is null
tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqd,
new RequestedAttribute(miaTqd, _miaManagement.GetMIAAttributeColumnName(queryAttribute.Attr)),
new RequestedAttribute(tqd, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME)));
}
requestedAttribute = new RequestedAttribute(tqd, MIA_Management.COLL_ATTR_VALUE_COL_NAME);
break;
default:
throw new IllegalCallException("Attributes of cardinality '{0}' cannot be queried via the {1}", spec.Cardinality, GetType().Name);
}
requestedAttributes.Add(queryAttribute, requestedAttribute);
}
public override bool Equals(object obj)
{
if (!(obj is QueryAttribute))
{
return(false);
}
QueryAttribute qa = (QueryAttribute)obj;
return(Attr.Equals(qa.Attr));
}
/// <summary>
/// Creates a simple SQL filter term from the given precompiled <paramref name="statementParts"/>.
/// The given parts must have been compiled by method <see cref="BuildAttributeFilterExpression"/>.
/// </summary>
/// <param name="ns">Namespace in which the query is generated.</param>
/// <param name="statementParts">Precompiled filter statement parts created by method <see cref="BuildAttributeFilterExpression"/>.</param>
/// <param name="requestedAttributes">Dictionary containing all attributes which have been requested in the query which will accommodate
/// the result filter. This dictionary must contain all attributes contained in <see cref="RequiredAttributes"/>.</param>
/// <returns>Created filter string to be placed behind the <c>WHERE</c> keyword.</returns>
public static string CreateSimpleSqlFilterCondition(Namespace ns, IList <object> statementParts,
IDictionary <QueryAttribute, RequestedAttribute> requestedAttributes)
{
StringBuilder filterBuilder = new StringBuilder(1000);
foreach (object statementPart in statementParts)
{
QueryAttribute qa = statementPart as QueryAttribute;
filterBuilder.Append(qa == null ? statementPart.ToString() : requestedAttributes[qa].GetQualifiedName(ns));
}
return(filterBuilder.ToString());
}
// outerMIIDJoinVariable is MEDIA_ITEMS.MEDIA_ITEM_ID (or its alias) for simple selects,
// MIAM_TABLE_XXX.MEDIA_ITEM_ID (or alias) for complex selects, used for join conditions in complex filters
public string CreateSqlFilterCondition(Namespace ns, IDictionary <QueryAttribute, RequestedAttribute> requestedAttributes,
out IList <BindVar> bindVars)
{
StringBuilder filterBuilder = new StringBuilder(1000);
foreach (object statementPart in _statementParts)
{
QueryAttribute qa = statementPart as QueryAttribute;
filterBuilder.Append(qa == null || !requestedAttributes.ContainsKey(qa) ? statementPart.ToString() : requestedAttributes[qa].GetQualifiedName(ns));
}
bindVars = _statementBindVars;
return(filterBuilder.ToString());
}
public static CompiledMediaItemQuery Compile(MIA_Management miaManagement, MediaItemQuery query)
{
IDictionary <Guid, MediaItemAspectMetadata> availableMIATypes = miaManagement.ManagedMediaItemAspectTypes;
ICollection <MediaItemAspectMetadata> necessaryMIATypes = new List <MediaItemAspectMetadata>();
ICollection <MediaItemAspectMetadata> optionalMIATypes = new List <MediaItemAspectMetadata>();
// Raise exception if necessary MIA types are not present
foreach (Guid miaTypeID in query.NecessaryRequestedMIATypeIDs)
{
MediaItemAspectMetadata miam;
if (!availableMIATypes.TryGetValue(miaTypeID, out miam))
{
throw new InvalidDataException("Necessary requested MIA type '{0}' is not present in the media library", miaTypeID);
}
necessaryMIATypes.Add(miam);
}
// For optional MIA types, we don't raise an exception if the type is not present
foreach (Guid miaTypeID in query.OptionalRequestedMIATypeIDs)
{
MediaItemAspectMetadata miam;
if (!availableMIATypes.TryGetValue(miaTypeID, out miam))
{
continue;
}
optionalMIATypes.Add(miam);
}
// Maps (all selected main) MIAM.Attributes to QueryAttributes
IDictionary <MediaItemAspectMetadata.AttributeSpecification, QueryAttribute> mainSelectedAttributes =
new Dictionary <MediaItemAspectMetadata.AttributeSpecification, QueryAttribute>();
// Attributes selected in explicit queries
ICollection <MediaItemAspectMetadata.AttributeSpecification> explicitSelectAttributes =
new List <MediaItemAspectMetadata.AttributeSpecification>();
// Allocate selected attributes to main query and explicit selects
ICollection <Guid> requestedMIATypeIDs = CollectionUtils.UnionSet(
query.NecessaryRequestedMIATypeIDs, query.OptionalRequestedMIATypeIDs);
foreach (Guid miaTypeID in requestedMIATypeIDs)
{
MediaItemAspectMetadata miam;
if (!availableMIATypes.TryGetValue(miaTypeID, out miam))
{
// If one of the necessary MIA types is not available, an exception was raised above. So we only
// come to here if an optional MIA type is not present - simply ignore that.
continue;
}
foreach (MediaItemAspectMetadata.AttributeSpecification attr in miam.AttributeSpecifications.Values)
{
if (attr.Cardinality == Cardinality.Inline || attr.Cardinality == Cardinality.ManyToOne)
{
mainSelectedAttributes[attr] = new QueryAttribute(attr);
}
else
{
explicitSelectAttributes.Add(attr);
}
}
}
return(new CompiledMediaItemQuery(miaManagement, necessaryMIATypes, optionalMIATypes,
mainSelectedAttributes, explicitSelectAttributes, query.Filter, query.SortInformation));
}
public MediaItem QueryMediaItem()
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
MediaItem result = null;
// 1. Main query
MainQueryBuilder mainQueryBuilder = new MainQueryBuilder(_miaManagement,
_mainSelectAttributes.Values, null, _necessaryRequestedMIAs, _optionalRequestedMIAs, _filter, _sortInformation);
using (IDbCommand mainQueryCommand = transaction.CreateCommand())
{
string mediaItemIdAlias2;
IDictionary <MediaItemAspectMetadata, string> miamAliases;
// Maps (selected and filtered) QueryAttributes to CompiledQueryAttributes in the SQL query
IDictionary <QueryAttribute, string> qa2a;
string statementStr;
IList <BindVar> bindVars;
mainQueryBuilder.GenerateSqlStatement(out mediaItemIdAlias2, out miamAliases, out qa2a,
out statementStr, out bindVars);
mainQueryCommand.CommandText = statementStr;
foreach (BindVar bindVar in bindVars)
{
database.AddParameter(mainQueryCommand, bindVar.Name, bindVar.Value, bindVar.VariableType);
}
IEnumerable <MediaItemAspectMetadata> selectedMIAs = _necessaryRequestedMIAs.Union(_optionalRequestedMIAs);
using (IDataReader mainReader = mainQueryCommand.ExecuteReader())
{
if (mainReader.Read())
{
Guid mediaItemId = database.ReadDBValue <Guid>(mainReader, mainReader.GetOrdinal(mediaItemIdAlias2));
result = new MediaItem(mediaItemId);
// Request complex attributes using media item ID
IFilter modifiedFilter = BooleanCombinationFilter.CombineFilters(BooleanOperator.And, _filter, new MediaItemIdFilter(mediaItemId));
IDictionary <MediaItemAspectMetadata.AttributeSpecification, ICollection <object> > complexAttributeValues =
new Dictionary <MediaItemAspectMetadata.AttributeSpecification, ICollection <object> >();
foreach (MediaItemAspectMetadata.AttributeSpecification attr in _explicitSelectAttributes)
{
ComplexAttributeQueryBuilder complexAttributeQueryBuilder = new ComplexAttributeQueryBuilder(
_miaManagement, attr, null, null, modifiedFilter);
using (IDbCommand complexQueryCommand = transaction.CreateCommand())
{
string mediaItemIdAlias;
string valueAlias;
complexAttributeQueryBuilder.GenerateSqlStatement(out mediaItemIdAlias, out valueAlias,
out statementStr, out bindVars);
complexQueryCommand.CommandText = statementStr;
foreach (BindVar bindVar in bindVars)
{
database.AddParameter(complexQueryCommand, bindVar.Name, bindVar.Value, bindVar.VariableType);
}
Type valueType = attr.AttributeType;
using (IDataReader reader = complexQueryCommand.ExecuteReader())
{
if (reader.Read())
{
object value = database.ReadDBValue(valueType, reader, reader.GetOrdinal(valueAlias));
ICollection <object> attrValues;
if (!complexAttributeValues.TryGetValue(attr, out attrValues))
{
attrValues = complexAttributeValues[attr] = new List <object>();
}
attrValues.Add(value);
}
}
}
}
// Put together all attributes
foreach (MediaItemAspectMetadata miam in selectedMIAs)
{
if (mainReader.IsDBNull(mainReader.GetOrdinal(miamAliases[miam])))
{
// MIAM is not available for current media item
continue;
}
MediaItemAspect mia = new MediaItemAspect(miam);
foreach (MediaItemAspectMetadata.AttributeSpecification attr in miam.AttributeSpecifications.Values)
{
if (attr.Cardinality == Cardinality.Inline)
{
QueryAttribute qa = _mainSelectAttributes[attr];
string alias = qa2a[qa];
mia.SetAttribute(attr, database.ReadDBValue(attr.AttributeType, mainReader, mainReader.GetOrdinal(alias)));
}
else
{
ICollection <object> attrValues;
if (complexAttributeValues.TryGetValue(attr, out attrValues))
{
mia.SetCollectionAttribute(attr, attrValues);
}
}
}
result.Aspects[miam.AspectId] = mia;
}
}
return(result);
}
}
}
finally
{
transaction.Dispose();
}
}
public IList <MediaItem> QueryList()
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
string statementStr;
IList <BindVar> bindVars;
// 1. Request all complex attributes
IDictionary <Guid, IDictionary <MediaItemAspectMetadata.AttributeSpecification, ICollection <object> > > complexAttributeValues =
new Dictionary <Guid, IDictionary <MediaItemAspectMetadata.AttributeSpecification, ICollection <object> > >();
foreach (MediaItemAspectMetadata.AttributeSpecification attr in _explicitSelectAttributes)
{
ComplexAttributeQueryBuilder complexAttributeQueryBuilder = new ComplexAttributeQueryBuilder(
_miaManagement, attr, null, _necessaryRequestedMIAs, _filter);
using (IDbCommand command = transaction.CreateCommand())
{
string mediaItemIdAlias;
string valueAlias;
complexAttributeQueryBuilder.GenerateSqlStatement(out mediaItemIdAlias, out valueAlias,
out statementStr, out bindVars);
command.CommandText = statementStr;
foreach (BindVar bindVar in bindVars)
{
database.AddParameter(command, bindVar.Name, bindVar.Value, bindVar.VariableType);
}
Type valueType = attr.AttributeType;
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Guid mediaItemId = database.ReadDBValue <Guid>(reader, reader.GetOrdinal(mediaItemIdAlias));
object value = database.ReadDBValue(valueType, reader, reader.GetOrdinal(valueAlias));
IDictionary <MediaItemAspectMetadata.AttributeSpecification, ICollection <object> > attributeValues;
if (!complexAttributeValues.TryGetValue(mediaItemId, out attributeValues))
{
attributeValues = complexAttributeValues[mediaItemId] =
new Dictionary <MediaItemAspectMetadata.AttributeSpecification, ICollection <object> >();
}
ICollection <object> attrValues;
if (!attributeValues.TryGetValue(attr, out attrValues))
{
attrValues = attributeValues[attr] = new List <object>();
}
attrValues.Add(value);
}
}
}
}
// 2. Main query
MainQueryBuilder mainQueryBuilder = new MainQueryBuilder(_miaManagement,
_mainSelectAttributes.Values, null, _necessaryRequestedMIAs, _optionalRequestedMIAs, _filter, _sortInformation);
using (IDbCommand command = transaction.CreateCommand())
{
string mediaItemIdAlias2;
IDictionary <MediaItemAspectMetadata, string> miamAliases;
// Maps (selected and filtered) QueryAttributes to CompiledQueryAttributes in the SQL query
IDictionary <QueryAttribute, string> qa2a;
mainQueryBuilder.GenerateSqlStatement(out mediaItemIdAlias2, out miamAliases, out qa2a,
out statementStr, out bindVars);
command.CommandText = statementStr;
foreach (BindVar bindVar in bindVars)
{
database.AddParameter(command, bindVar.Name, bindVar.Value, bindVar.VariableType);
}
IEnumerable <MediaItemAspectMetadata> selectedMIAs = _necessaryRequestedMIAs.Union(_optionalRequestedMIAs);
ICollection <Guid> mediaItems = new HashSet <Guid>();
using (IDataReader reader = command.ExecuteReader())
{
IList <MediaItem> result = new List <MediaItem>();
while (reader.Read())
{
Guid mediaItemId = database.ReadDBValue <Guid>(reader, reader.GetOrdinal(mediaItemIdAlias2));
if (mediaItems.Contains(mediaItemId))
{
// Media item was already added to result - query results are not always unique because of JOINs used for filtering
continue;
}
mediaItems.Add(mediaItemId);
IDictionary <MediaItemAspectMetadata.AttributeSpecification, ICollection <object> > attributeValues;
if (!complexAttributeValues.TryGetValue(mediaItemId, out attributeValues))
{
attributeValues = null;
}
MediaItem mediaItem = new MediaItem(mediaItemId);
foreach (MediaItemAspectMetadata miam in selectedMIAs)
{
if (reader.IsDBNull(reader.GetOrdinal(miamAliases[miam])))
{
// MIAM is not available for current media item
continue;
}
MediaItemAspect mia = new MediaItemAspect(miam);
foreach (MediaItemAspectMetadata.AttributeSpecification attr in miam.AttributeSpecifications.Values)
{
if (attr.Cardinality == Cardinality.Inline)
{
QueryAttribute qa = _mainSelectAttributes[attr];
string alias = qa2a[qa];
mia.SetAttribute(attr, database.ReadDBValue(attr.AttributeType, reader, reader.GetOrdinal(alias)));
}
else
{
ICollection <object> attrValues;
if (attributeValues != null && attributeValues.TryGetValue(attr, out attrValues))
{
mia.SetCollectionAttribute(attr, attrValues);
}
}
}
mediaItem.Aspects[miam.AspectId] = mia;
}
result.Add(mediaItem);
}
return(result);
}
}
}
finally
{
transaction.Dispose();
}
}
public static CompiledMediaItemQuery Compile(MIA_Management miaManagement, MediaItemQuery query)
{
IDictionary<Guid, MediaItemAspectMetadata> availableMIATypes = miaManagement.ManagedMediaItemAspectTypes;
ICollection<MediaItemAspectMetadata> necessaryMIATypes = new List<MediaItemAspectMetadata>();
ICollection<MediaItemAspectMetadata> optionalMIATypes = new List<MediaItemAspectMetadata>();
// Raise exception if necessary MIA types are not present
foreach (Guid miaTypeID in query.NecessaryRequestedMIATypeIDs)
{
MediaItemAspectMetadata miam;
if (!availableMIATypes.TryGetValue(miaTypeID, out miam))
throw new InvalidDataException("Necessary requested MIA type '{0}' is not present in the media library", miaTypeID);
necessaryMIATypes.Add(miam);
}
// For optional MIA types, we don't raise an exception if the type is not present
foreach (Guid miaTypeID in query.OptionalRequestedMIATypeIDs)
{
MediaItemAspectMetadata miam;
if (!availableMIATypes.TryGetValue(miaTypeID, out miam))
continue;
optionalMIATypes.Add(miam);
}
// Maps (all selected main) MIAM.Attributes to QueryAttributes
IDictionary<MediaItemAspectMetadata.AttributeSpecification, QueryAttribute> mainSelectedAttributes =
new Dictionary<MediaItemAspectMetadata.AttributeSpecification, QueryAttribute>();
// Attributes selected in explicit queries
ICollection<MediaItemAspectMetadata.AttributeSpecification> explicitSelectAttributes =
new List<MediaItemAspectMetadata.AttributeSpecification>();
// Allocate selected attributes to main query and explicit selects
ICollection<Guid> requestedMIATypeIDs = CollectionUtils.UnionSet(
query.NecessaryRequestedMIATypeIDs, query.OptionalRequestedMIATypeIDs);
foreach (Guid miaTypeID in requestedMIATypeIDs)
{
MediaItemAspectMetadata miam;
if (!availableMIATypes.TryGetValue(miaTypeID, out miam))
// If one of the necessary MIA types is not available, an exception was raised above. So we only
// come to here if an optional MIA type is not present - simply ignore that.
continue;
foreach (MediaItemAspectMetadata.AttributeSpecification attr in miam.AttributeSpecifications.Values)
{
if (attr.Cardinality == Cardinality.Inline || attr.Cardinality == Cardinality.ManyToOne)
mainSelectedAttributes[attr] = new QueryAttribute(attr);
else
explicitSelectAttributes.Add(attr);
}
}
return new CompiledMediaItemQuery(miaManagement, necessaryMIATypes, optionalMIATypes,
mainSelectedAttributes, explicitSelectAttributes, query.Filter, query.SortInformation);
}
public HomogenousMap Execute()
{
ISQLDatabase database = ServiceRegistration.Get<ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
using (IDbCommand command = transaction.CreateCommand())
{
string valueAlias;
string groupSizeAlias;
string statementStr;
IList<BindVar> bindVars;
if (_selectAttribute.Cardinality == Cardinality.Inline || _selectAttribute.Cardinality == Cardinality.ManyToOne)
{
QueryAttribute selectAttributeQA = new QueryAttribute(_selectAttribute);
MainQueryBuilder builder = new MainQueryBuilder(_miaManagement,
new QueryAttribute[] {selectAttributeQA}, _selectProjectionFunction,
_necessaryRequestedMIATypes, new MediaItemAspectMetadata[] {}, _filter, null);
IDictionary<QueryAttribute, string> qa2a;
builder.GenerateSqlGroupByStatement(out groupSizeAlias, out qa2a, out statementStr, out bindVars);
valueAlias = qa2a[selectAttributeQA];
}
else
{
ComplexAttributeQueryBuilder builder = new ComplexAttributeQueryBuilder(_miaManagement, _selectAttribute,
_selectProjectionFunction, _necessaryRequestedMIATypes, _filter);
builder.GenerateSqlGroupByStatement(_selectAttributeFilter, out valueAlias, out groupSizeAlias,
out statementStr, out bindVars);
}
command.CommandText = statementStr;
foreach (BindVar bindVar in bindVars)
database.AddParameter(command, bindVar.Name, bindVar.Value, bindVar.VariableType);
Type valueType = _projectionValueType ?? _selectAttribute.AttributeType;
HomogenousMap result = new HomogenousMap(valueType, typeof(int));
using (IDataReader reader = command.ExecuteReader())
{
int valueCol = reader.GetOrdinal(valueAlias);
int groupSizeCol = reader.GetOrdinal(groupSizeAlias);
while (reader.Read())
result.Add(database.ReadDBValue(valueType, reader, valueCol),
database.ReadDBValue<int>(reader, groupSizeCol));
}
return result;
}
}
finally
{
transaction.Dispose();
}
}
private IList <MediaItem> GetMediaItems(ISQLDatabase database, ITransaction transaction, bool singleMode, IEnumerable <MediaItemAspectMetadata> selectedMIAs, out IList <Guid> mediaItemIds, out IDictionary <Guid, IList <Guid> > complexMediaItems)
{
string statementStr;
IList <BindVar> bindVars;
MIAQueryBuilder builder = new MIAQueryBuilder(_miaManagement,
_mainSelectAttributes.Values, null, _necessaryRequestedMIAs, _optionalRequestedMIAs, _filter, _subqueryFilter, _sortInformation);
using (IDbCommand command = transaction.CreateCommand())
{
string mediaItemIdAlias2;
IDictionary <MediaItemAspectMetadata, string> miamAliases;
// Maps (selected and filtered) QueryAttributes to CompiledQueryAttributes in the SQL query
IDictionary <QueryAttribute, string> qa2a;
builder.GenerateSqlStatement(out mediaItemIdAlias2, out miamAliases, out qa2a,
out statementStr, out bindVars);
// Try to use SQL side paging, which gives best performance if supported
ISQLDatabasePaging paging = database as ISQLDatabasePaging;
if (paging != null)
{
paging.Process(ref statementStr, ref bindVars, ref _offset, ref _limit);
}
command.CommandText = statementStr;
foreach (BindVar bindVar in bindVars)
{
database.AddParameter(command, bindVar.Name, bindVar.Value, bindVar.VariableType);
}
using (IDataReader fullReader = command.ExecuteReader())
{
IList <MediaItem> result = new List <MediaItem>();
mediaItemIds = new List <Guid>();
complexMediaItems = new Dictionary <Guid, IList <Guid> >();
var records = fullReader.AsEnumerable();
if (_offset.HasValue)
{
records = records.Skip((int)_offset.Value);
}
if (_limit.HasValue)
{
records = records.Take((int)_limit.Value);
}
foreach (var reader in records)
{
Guid mediaItemId = database.ReadDBValue <Guid>(reader, reader.GetOrdinal(mediaItemIdAlias2));
if (mediaItemIds.Contains(mediaItemId))
{
// Media item was already added to result - query results are not always unique because of JOINs used for filtering
continue;
}
mediaItemIds.Add(mediaItemId);
MediaItem mediaItem = new MediaItem(mediaItemId);
foreach (SingleMediaItemAspectMetadata miam in selectedMIAs.Where(x => x is SingleMediaItemAspectMetadata))
{
string name;
if (!miamAliases.TryGetValue(miam, out name) || reader.IsDBNull(reader.GetOrdinal(name)))
{
// MIAM is not available for current media item
continue;
}
IList <Guid> complexIds;
if (!complexMediaItems.TryGetValue(miam.AspectId, out complexIds))
{
complexMediaItems[miam.AspectId] = complexIds = new List <Guid>();
}
complexIds.Add(mediaItemId);
SingleMediaItemAspect mia = new SingleMediaItemAspect(miam);
foreach (MediaItemAspectMetadata.AttributeSpecification attr in miam.AttributeSpecifications.Values)
{
if (attr.Cardinality == Cardinality.Inline)
{
QueryAttribute qa = _mainSelectAttributes[attr];
string alias = qa2a[qa];
mia.SetAttribute(attr, database.ReadDBValue(attr.AttributeType, reader, reader.GetOrdinal(alias)));
}
}
MediaItemAspect.SetAspect(mediaItem.Aspects, mia);
}
result.Add(mediaItem);
if (singleMode)
{
break;
}
}
return(result);
}
}
}
protected bool Equals(QueryAttribute other)
{
return(Equals(_attr, other._attr));
}
protected void RequestSimpleAttribute(QueryAttribute queryAttribute,
IDictionary <object, TableQueryData> tableQueries, IList <TableJoin> tableJoins, string miaJoinType,
IDictionary <QueryAttribute, RequestedAttribute> requestedAttributes,
IDictionary <MediaItemAspectMetadata, TableQueryData> miaTypeTableQueries,
RequestedAttribute miaIdAttribute, out RequestedAttribute requestedAttribute)
{
if (requestedAttributes.TryGetValue(queryAttribute, out requestedAttribute))
{
// Already requested
return;
}
MediaItemAspectMetadata.AttributeSpecification spec = queryAttribute.Attr;
MediaItemAspectMetadata miaType = spec.ParentMIAM;
TableQueryData tqd;
switch (spec.Cardinality)
{
case Cardinality.Inline:
// For Inline queries, we request the Inline attribute's column name at the MIA main table, which gets joined
// with the MIA ID
if (!tableQueries.TryGetValue(miaType, out tqd))
{
tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType);
if (miaTypeTableQueries != null)
{
miaTypeTableQueries.Add(miaType, tqd);
}
tableJoins.Add(new TableJoin(miaJoinType, tqd, miaIdAttribute,
new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME)));
}
requestedAttribute = new RequestedAttribute(tqd, _miaManagement.GetMIAAttributeColumnName(queryAttribute.Attr));
break;
case Cardinality.ManyToOne:
// For MTO queries, we request both the MIA main table and the MTO table
TableQueryData miaTqd;
if (!tableQueries.TryGetValue(miaType, out miaTqd))
{
miaTqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType);
if (miaTypeTableQueries != null)
{
miaTypeTableQueries.Add(miaType, miaTqd);
}
// Add MIA main table to list of table joins
tableJoins.Add(new TableJoin(miaJoinType, miaTqd, miaIdAttribute,
new RequestedAttribute(miaTqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME)));
}
if (!tableQueries.TryGetValue(spec, out tqd))
{
tqd = tableQueries[spec] = TableQueryData.CreateTableQueryOfMTOTable(_miaManagement, spec);
// We must use left outer joins for MTO value tables, because if the value is null, the association FK is null
tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqd,
new RequestedAttribute(miaTqd, _miaManagement.GetMIAAttributeColumnName(queryAttribute.Attr)),
new RequestedAttribute(tqd, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME)));
}
requestedAttribute = new RequestedAttribute(tqd, MIA_Management.COLL_ATTR_VALUE_COL_NAME);
break;
default:
throw new IllegalCallException("Attributes of cardinality '{0}' cannot be queried via the {1}", spec.Cardinality, GetType().Name);
}
requestedAttributes.Add(queryAttribute, requestedAttribute);
}
public Tuple <HomogenousMap, HomogenousMap> Execute()
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
using (IDbCommand command = transaction.CreateCommand())
{
string keyAlias = null;
string valueAlias = null;
string groupSizeAlias = null;
string statementStr = null;
IList <BindVar> bindVars = null;
if (_selectValueAttribute.Cardinality == Cardinality.Inline || _selectValueAttribute.Cardinality == Cardinality.ManyToOne)
{
List <QueryAttribute> qAttributes = new List <QueryAttribute>();
QueryAttribute selectValueAttributeQA = new QueryAttribute(_selectValueAttribute);
qAttributes.Add(selectValueAttributeQA);
QueryAttribute selectKeyAttributeQA = null;
if (_selectKeyAttribute != null)
{
selectKeyAttributeQA = new QueryAttribute(_selectKeyAttribute);
qAttributes.Add(selectKeyAttributeQA);
}
MIAQueryBuilder builder = new MIAQueryBuilder(_miaManagement, qAttributes, _selectProjectionFunction,
_necessaryRequestedMIATypes, new MediaItemAspectMetadata[] {}, _filter, _subqueryFilter, null);
IDictionary <QueryAttribute, string> qa2a;
builder.GenerateSqlGroupByStatement(out groupSizeAlias, out qa2a, out statementStr, out bindVars);
valueAlias = qa2a[selectValueAttributeQA];
if (_selectKeyAttribute != null)
{
keyAlias = qa2a[selectKeyAttributeQA];
}
}
else
{
if (_selectKeyAttribute != null)
{
throw new InvalidDataException("Value attribute '{0}' does not support key value grouping", _selectValueAttribute.AttributeName);
}
ComplexAttributeQueryBuilder builder = new ComplexAttributeQueryBuilder(_miaManagement, _selectValueAttribute,
_selectProjectionFunction, _necessaryRequestedMIATypes, _filter, _subqueryFilter);
builder.GenerateSqlGroupByStatement(_selectAttributeFilter, out valueAlias, out groupSizeAlias,
out statementStr, out bindVars);
}
command.CommandText = statementStr;
foreach (BindVar bindVar in bindVars)
{
database.AddParameter(command, bindVar.Name, bindVar.Value, bindVar.VariableType);
}
Tuple <HomogenousMap, HomogenousMap> result = null;
if (_selectKeyAttribute != null)
{
Type valueType = _projectionValueType ?? _selectValueAttribute.AttributeType;
Type keyType = _selectKeyAttribute.AttributeType;
HomogenousMap valueMap = new HomogenousMap(valueType, typeof(int));
HomogenousMap keyMap = new HomogenousMap(valueType, keyType);
using (IDataReader reader = command.ExecuteReader())
{
int keyCol = reader.GetOrdinal(keyAlias);
int valueCol = reader.GetOrdinal(valueAlias);
int groupSizeCol = reader.GetOrdinal(groupSizeAlias);
while (reader.Read())
{
if (!keyMap.ContainsKey(database.ReadDBValue(valueType, reader, valueCol)))
{
keyMap.Add(database.ReadDBValue(valueType, reader, valueCol), database.ReadDBValue(keyType, reader, keyCol));
valueMap.Add(database.ReadDBValue(valueType, reader, valueCol), database.ReadDBValue <int>(reader, groupSizeCol));
}
}
}
result = new Tuple <HomogenousMap, HomogenousMap>(valueMap, keyMap);
}
else
{
Type valueType = _projectionValueType ?? _selectValueAttribute.AttributeType;
HomogenousMap valueMap = new HomogenousMap(valueType, typeof(int));
using (IDataReader reader = command.ExecuteReader())
{
int valueCol = reader.GetOrdinal(valueAlias);
int groupSizeCol = reader.GetOrdinal(groupSizeAlias);
while (reader.Read())
{
valueMap.Add(database.ReadDBValue(valueType, reader, valueCol),
database.ReadDBValue <int>(reader, groupSizeCol));
}
}
result = new Tuple <HomogenousMap, HomogenousMap>(valueMap, null);
}
return(result);
}
}
finally
{
transaction.Dispose();
}
}
private void AddMultipleMIAResults(ISQLDatabase database, ITransaction transaction, MultipleMediaItemAspectMetadata miam,
IDictionary <MediaItemAspectMetadata.AttributeSpecification, QueryAttribute> attributes, MainQueryBuilder builder,
IDictionary <Guid, ICollection <MultipleMediaItemAspect> > multipleMiaValues)
{
if (miam.IsTransientAspect)
{
return;
}
using (IDbCommand command = transaction.CreateCommand())
{
string mediaItemIdAlias;
IDictionary <MediaItemAspectMetadata, string> miamAliases;
// Maps (selected and filtered) QueryAttributes to CompiledQueryAttributes in the SQL query
IDictionary <QueryAttribute, string> qa2a;
string statementStr;
IList <BindVar> bindVars;
builder.GenerateSqlStatement(out mediaItemIdAlias, out miamAliases, out qa2a, out statementStr, out bindVars);
command.CommandText = statementStr;
foreach (BindVar bindVar in bindVars)
{
database.AddParameter(command, bindVar.Name, bindVar.Value, bindVar.VariableType);
}
//logger.Debug("Get multiple MIAs for {0}", string.Join(",", ids));
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Guid itemId = database.ReadDBValue <Guid>(reader, reader.GetOrdinal(mediaItemIdAlias));
//logger.Debug("Read record for {0}", itemId);
MultipleMediaItemAspect mia = new MultipleMediaItemAspect(miam);
foreach (MediaItemAspectMetadata.AttributeSpecification attr in miam.AttributeSpecifications.Values)
{
if (attr.Cardinality == Cardinality.Inline)
{
QueryAttribute qa = attributes[attr];
string alias = qa2a[qa];
//logger.Debug("Reading multiple MIA attibute " + attr.AttributeName + " #" + index + " from column " + alias);
mia.SetAttribute(attr, database.ReadDBValue(attr.AttributeType, reader, reader.GetOrdinal(alias)));
}
}
if (builder is InverseRelationshipQueryBuilder)
{
/*
* Swap the ID / role <--> linked role / linked ID
* "A is a movie starting actor B"
* becomes
* "B is an actor starting in movie A"
*/
Guid id = itemId;
Guid role = mia.GetAttributeValue <Guid>(RelationshipAspect.ATTR_ROLE);
itemId = mia.GetAttributeValue <Guid>(RelationshipAspect.ATTR_LINKED_ID);
mia.SetAttribute(RelationshipAspect.ATTR_ROLE, mia.GetAttributeValue <Guid>(RelationshipAspect.ATTR_LINKED_ROLE));
mia.SetAttribute(RelationshipAspect.ATTR_LINKED_ROLE, role);
mia.SetAttribute(RelationshipAspect.ATTR_LINKED_ID, id);
}
ICollection <MultipleMediaItemAspect> values;
if (!multipleMiaValues.TryGetValue(itemId, out values))
{
values = new List <MultipleMediaItemAspect>();
multipleMiaValues[itemId] = values;
}
values.Add(mia);
}
}
}
}