public int Execute()
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.CreateTransaction();
try
{
using (IDbCommand command = transaction.CreateCommand())
{
string countAlias;
string statementStr;
IList <BindVar> bindVars;
MIAQueryBuilder builder = new MIAQueryBuilder(_miaManagement, new QueryAttribute[] { }, null,
_necessaryRequestedMIATypes, new MediaItemAspectMetadata[] {}, _filter, null);
IDictionary <QueryAttribute, string> qa2a;
builder.GenerateSqlGroupByStatement(out countAlias, out qa2a, out statementStr, out bindVars);
command.CommandText = statementStr;
foreach (BindVar bindVar in bindVars)
{
database.AddParameter(command, bindVar.Name, bindVar.Value, bindVar.VariableType);
}
return(Convert.ToInt32(command.ExecuteScalar()));
}
}
finally
{
transaction.Dispose();
}
}
public override string ToString()
{
StringBuilder result = new StringBuilder();
result.Append("CompiledMediaItemQuery\r\n");
foreach (MediaItemAspectMetadata.AttributeSpecification attr in _explicitSelectAttributes)
{
ComplexAttributeQueryBuilder complexAttributeQueryBuilder = new ComplexAttributeQueryBuilder(
_miaManagement, attr, null, _necessaryRequestedMIAs, _filter, _subqueryFilter);
result.Append("External attribute query for ");
result.Append(attr.ParentMIAM.Name);
result.Append(".");
result.Append(attr.AttributeName);
result.Append(":\r\n");
result.Append(complexAttributeQueryBuilder.ToString());
result.Append("\r\n\r\n");
}
result.Append("Main query:\r\n");
MIAQueryBuilder mainQueryBuilder = new MIAQueryBuilder(_miaManagement,
_mainSelectAttributes.Values, null, _necessaryRequestedMIAs, _optionalRequestedMIAs, _filter, _subqueryFilter, _sortInformation);
result.Append(mainQueryBuilder.ToString());
return(result.ToString());
}
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 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);
}
}
}
