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 void AddComplexAttributes(ISQLDatabase database, ITransaction transaction, IEnumerable <Guid> ids, IDictionary <Guid, IDictionary <MediaItemAspectMetadata.AttributeSpecification, IList> > complexAttributeValues, IDictionary <Guid, IList <Guid> > complexMediaItemIds) { string statementStr; IList <BindVar> bindVars; foreach (MediaItemAspectMetadata.AttributeSpecification attr in _explicitSelectAttributes) { // Skip this attribute if no media items were found for the aspect if (!complexMediaItemIds.ContainsKey(attr.ParentMIAM.AspectId)) { continue; } ComplexAttributeQueryBuilder builder = new ComplexAttributeQueryBuilder( _miaManagement, attr, null, _necessaryRequestedMIAs, new MediaItemIdFilter(ids), _subqueryFilter); using (IDbCommand command = transaction.CreateCommand()) { string mediaItemIdAlias; string valueAlias; builder.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, IList> attributeValues; if (!complexAttributeValues.TryGetValue(mediaItemId, out attributeValues)) { attributeValues = complexAttributeValues[mediaItemId] = new Dictionary <MediaItemAspectMetadata.AttributeSpecification, IList>(); } IList attrValues; if (!attributeValues.TryGetValue(attr, out attrValues)) { attrValues = attributeValues[attr] = CreateGenericList(attr.AttributeType); } attrValues.Add(value); } } } } }
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); 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"); MainQueryBuilder mainQueryBuilder = new MainQueryBuilder(_miaManagement, _mainSelectAttributes.Values, null, _necessaryRequestedMIAs, _optionalRequestedMIAs, _filter, _sortInformation); result.Append(mainQueryBuilder.ToString()); return(result.ToString()); }
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 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); 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"); MainQueryBuilder mainQueryBuilder = new MainQueryBuilder(_miaManagement, _mainSelectAttributes.Values, null, _necessaryRequestedMIAs, _optionalRequestedMIAs, _filter, _sortInformation); result.Append(mainQueryBuilder.ToString()); return result.ToString(); }
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 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(); } }
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(); } }