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