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);
}
}
}
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 HashSet <object>();
}
attrValues.Add(value);
}
}
}
}
// 2. Main query
MainQueryBuilder mainQueryBuilder = new MainQueryBuilder(_miaManagement,
_mainSelectAttributes.Values, null, _necessaryRequestedMIAs, _optionalRequestedMIAs, _filter, _sortInformation, _limit, _offset);
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);
// 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);
}
IEnumerable <MediaItemAspectMetadata> selectedMIAs = _necessaryRequestedMIAs.Union(_optionalRequestedMIAs);
ICollection <Guid> mediaItems = new HashSet <Guid>();
using (IDataReader fullReader = command.ExecuteReader())
{
IList <MediaItem> result = new List <MediaItem>();
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 (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();
}
}