コード例 #1
0
        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);
                }
            }
        }
コード例 #2
0
        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();
            }
        }