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); } } }