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);
 }
Exemple #2
0
        public string GetJoinDeclaration(Namespace ns)
        {
            StringBuilder result = new StringBuilder(100);

            result.Append(_joinType);
            result.Append(" ");
            result.Append(_table.GetDeclarationWithAlias(ns));
            if (_conditionPairs.Count > 0)
            {
                result.Append(" ON ");
                bool firstCondition = true;
                foreach (var condition in _conditionPairs)
                {
                    if (!firstCondition)
                    {
                        result.Append(" AND ");
                    }

                    RequestedAttribute ra = condition.Key as RequestedAttribute;
                    if (ra != null)
                    {
                        result.Append(ra.GetQualifiedName(ns));
                    }
                    else
                    {
                        result.Append(condition.Key);
                    }
                    result.Append(" = ");
                    ra = condition.Value as RequestedAttribute;
                    if (ra != null)
                    {
                        result.Append(ra.GetQualifiedName(ns));
                    }
                    else
                    {
                        result.Append(condition.Value);
                    }

                    firstCondition = false;
                }
            }
            return(result.ToString());
        }
        protected bool AddChildAggregateAttributeSortInformation(ISortInformation sortInformation, Namespace ns, BindVarNamespace bvNamespace, RequestedAttribute miaIdAttribute, IList <TableJoin> tableJoins,
                                                                 IList <CompiledSortInformation> compiledSortInformation, IList <BindVar> bindVars)
        {
            ChildAggregateAttributeSortInformation childAttributeSort = sortInformation as ChildAggregateAttributeSortInformation;

            if (childAttributeSort != null)
            {
                if (childAttributeSort.ChildAttributeType.ParentMIAM.IsTransientAspect)
                {
                    return(false);
                }

                var relation = _miaManagement.LocallyKnownRelationshipTypes.FirstOrDefault(r => (r.ChildRole == childAttributeSort.ChildRole && r.ParentRole == childAttributeSort.ParentRole));
                if (relation == null)
                {
                    return(false);
                }

                if (!_miaManagement.ManagedMediaItemAspectTypes.TryGetValue(relation.ChildAspectId, out var childMetadata))
                {
                    return(false);
                }

                BindVar roleVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), childAttributeSort.ParentRole, typeof(Guid));
                bindVars.Add(roleVar);
                BindVar linkedRoleVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), childAttributeSort.ChildRole, typeof(Guid));
                bindVars.Add(linkedRoleVar);

                var table     = new TableQueryData($"({BuildRelationshipPart(childAttributeSort, childMetadata, false, roleVar, linkedRoleVar)} UNION {BuildRelationshipPart(childAttributeSort, childMetadata, true, roleVar, linkedRoleVar)})");
                var tableName = table.GetAlias(ns);
                tableJoins.Add(new TableJoin("INNER JOIN", table, $"{tableName}.ID", miaIdAttribute.GetQualifiedName(ns)));

                RequestedAttribute ra = new RequestedAttribute(table, "SORT");
                compiledSortInformation.Add(new CompiledSortInformation(ra, childAttributeSort.Direction));
                return(true);
            }
            return(false);
        }
 public CompiledSortInformation(RequestedAttribute sortAttribute, SortDirection direction)
 {
     _sortAttribute = sortAttribute;
     _direction     = direction;
 }
        protected void GenerateSqlStatement(bool groupByValues,
                                            IDictionary <MediaItemAspectMetadata, string> miamAliases,
                                            out string mediaItemIdOrGroupSizeAlias,
                                            out IDictionary <QueryAttribute, string> attributeAliases,
                                            out string statementStr, out IList <BindVar> bindVars)
        {
            Namespace        ns          = new Namespace();
            BindVarNamespace bvNamespace = new BindVarNamespace();

            // Contains a mapping of each queried (=selected or filtered) attribute to its request attribute instance
            // data (which holds its requested query table instance)
            IDictionary <QueryAttribute, RequestedAttribute> requestedAttributes = new Dictionary <QueryAttribute, RequestedAttribute>();

            attributeAliases = new Dictionary <QueryAttribute, string>();

            // Contains a list of qualified attribute names for all select attributes - needed for GROUP BY-expressions
            ICollection <string> qualifiedGroupByAliases = new List <string>();

            // Contains a list of compiled select attribute declarations. We need this in a separate list (in contrast to using
            // the selectAttributes list together with the compiledAttributes map) because it might be the case that
            // an attribute is requested twice. In that rare case, we need a new alias name for it.
            IList <string> selectAttributeDeclarations = new List <string>();

            // Dictionary containing as key the requested MIAM instance OR attribute specification of cardinality MTO,
            // mapped to the table query data to request its contents.
            IDictionary <object, TableQueryData> tableQueries = new Dictionary <object, TableQueryData>();

            // Contains the same tables as the tableQueries variable, but in order and enriched with table join data
            IList <TableJoin> tableJoins = new List <TableJoin>();

            // Contains all table query data for MIA type tables
            IDictionary <MediaItemAspectMetadata, TableQueryData> miaTypeTableQueries = new Dictionary <MediaItemAspectMetadata, TableQueryData>();

            // Albert, 2012-01-29: Optimized query, don't join with media items table, if we have necessary requested MIAs. In that case,
            // we can use one of their media item ids.
            //// First create the request table query data for the MIA main table and the request attribute for the MIA ID.
            //// We'll need the requested attribute as join attribute soon.
            //TableQueryData miaTableQuery = new TableQueryData(MediaLibrary_SubSchema.MEDIA_ITEMS_TABLE_NAME);
            //RequestedAttribute miaIdAttribute = new RequestedAttribute(miaTableQuery, MediaLibrary_SubSchema.MEDIA_ITEMS_ITEM_ID_COL_NAME);

            RequestedAttribute miaIdAttribute = null; // Lazy initialized below

            // Contains CompiledSortInformation instances for each sort information instance
            IList <CompiledSortInformation> compiledSortInformation = null;

            List <BindVar> sqlVars = new List <BindVar>();

            // Ensure that the tables for all necessary MIAs are requested first (INNER JOIN)
            foreach (MediaItemAspectMetadata miaType in _necessaryRequestedMIAs)
            {
                if (miaType.IsTransientAspect)
                {
                    continue;
                }
                if (tableQueries.ContainsKey(miaType))
                {
                    // We only come here if miaType was already queried as necessary MIA, so optimize redundant entry
                    continue;
                }

                TableQueryData tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType);
                miaTypeTableQueries.Add(miaType, tqd);
                RequestedAttribute ra;
                // The first table join has invalid join attributes because miaIdAttribute is still null - but only the join table attribute is necessary
                // for the the first table - see below
                tableJoins.Add(new TableJoin("INNER JOIN", tqd,
                                             ra = new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute));
                if (miaIdAttribute == null)
                {
                    miaIdAttribute = ra;
                }
            }

            if (miaIdAttribute == null)
            {                                                                           // If we didn't request any necessary MIA types, we have to add an artificial table for the miaIdAttribute
                TableQueryData miaTableQuery = new TableQueryData(MediaLibrary_SubSchema.MEDIA_ITEMS_TABLE_NAME);
                tableJoins.Add(new TableJoin("INNER JOIN", miaTableQuery, null, null)); // First table join has invalid join attributes - not needed for first table
                miaIdAttribute = new RequestedAttribute(miaTableQuery, MediaLibrary_SubSchema.MEDIA_ITEMS_ITEM_ID_COL_NAME);
            }

            // Ensure that the tables for all optional MIAs are requested first (LEFT OUTER JOIN)
            // That is necessary to make empty optional MIA types available in the result
            foreach (MediaItemAspectMetadata miaType in _optionalRequestedMIAs)
            {
                if (miaType.IsTransientAspect)
                {
                    continue;
                }
                if (tableQueries.ContainsKey(miaType))
                {
                    // We only come here if miaType was already queried as necessary or optional MIA, so optimize redundant entry
                    continue;
                }
                TableQueryData tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType);
                miaTypeTableQueries.Add(miaType, tqd);
                tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqd,
                                             new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute));
            }

            // Build table query data for each selected Inline and MTO attribute
            // + select query attribute
            // + add alias to selectAttributeDeclarations
            foreach (QueryAttribute attr in _selectAttributes)
            {
                if (attr.Attr.ParentMIAM.IsTransientAspect)
                {
                    continue;
                }
                RequestedAttribute ra;
                RequestSimpleAttribute(attr, tableQueries, tableJoins, "LEFT OUTER JOIN", requestedAttributes, miaTypeTableQueries,
                                       miaIdAttribute, out ra);
                string alias;
                selectAttributeDeclarations.Add(_selectProjectionFunction == null ?
                                                ra.GetDeclarationWithAlias(ns, out alias) :
                                                ra.GetDeclarationWithAlias(ns, _selectProjectionFunction, out alias));
                attributeAliases.Add(attr, alias);
                qualifiedGroupByAliases.Add(ra.GetAlias(ns));
            }

            CompiledFilter compiledFilter = CreateCompiledFilter(ns, bvNamespace, miaIdAttribute.GetQualifiedName(ns), tableJoins);

            // Build table query data for each Inline attribute which is part of a filter
            // + compile query attribute
            foreach (QueryAttribute attr in compiledFilter.RequiredAttributes)
            {
                if (attr.Attr.ParentMIAM.IsTransientAspect)
                {
                    continue;
                }
                if (attr.Attr.Cardinality != Cardinality.Inline && attr.Attr.Cardinality != Cardinality.ManyToOne)
                {
                    continue;
                }
                // Tables of Inline and MTO attributes, which are part of a filter, are joined with main table
                RequestedAttribute ra;
                RequestSimpleAttribute(attr, tableQueries, tableJoins, "LEFT OUTER JOIN", requestedAttributes,
                                       miaTypeTableQueries, miaIdAttribute, out ra);
            }
            // Build table query data for each sort attribute
            if (_sortInformation != null)
            {
                compiledSortInformation = new List <CompiledSortInformation>();
                BindVar userVar = null;
                foreach (ISortInformation sortInformation in _sortInformation)
                {
                    AttributeSortInformation attributeSort = sortInformation as AttributeSortInformation;
                    if (attributeSort != null)
                    {
                        if (attributeSort.AttributeType.ParentMIAM.IsTransientAspect)
                        {
                            continue;
                        }
                        MediaItemAspectMetadata.AttributeSpecification attr = attributeSort.AttributeType;
                        if (attr.Cardinality != Cardinality.Inline && attr.Cardinality != Cardinality.ManyToOne)
                        {
                            // Sorting can only be done for Inline and MTO attributes
                            continue;
                        }
                        RequestedAttribute ra;
                        RequestSimpleAttribute(new QueryAttribute(attr), tableQueries, tableJoins, "LEFT OUTER JOIN", requestedAttributes,
                                               miaTypeTableQueries, miaIdAttribute, out ra);
                        compiledSortInformation.Add(new CompiledSortInformation(ra, attributeSort.Direction));
                        continue;
                    }

                    DataSortInformation dataSort = sortInformation as DataSortInformation;
                    if (dataSort != null && _userProfileId.HasValue)
                    {
                        TableQueryData     tqd = new TableQueryData(UserProfileDataManagement.UserProfileDataManagement_SubSchema.USER_MEDIA_ITEM_DATA_TABLE_NAME);
                        RequestedAttribute ra  = new RequestedAttribute(tqd, UserProfileDataManagement.UserProfileDataManagement_SubSchema.USER_DATA_VALUE_COL_NAME);
                        compiledSortInformation.Add(new CompiledSortInformation(ra, dataSort.Direction));

                        if (userVar == null)
                        {
                            userVar = new BindVar("UID", _userProfileId.Value, typeof(Guid));
                            sqlVars.Add(userVar);
                        }
                        TableJoin join = new TableJoin("LEFT OUTER JOIN", tqd, new RequestedAttribute(tqd, UserProfileDataManagement.UserProfileDataManagement_SubSchema.USER_PROFILE_ID_COL_NAME), "@" + userVar.Name);
                        join.AddCondition(new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute);
                        join.AddCondition(new RequestedAttribute(tqd, UserProfileDataManagement.UserProfileDataManagement_SubSchema.USER_DATA_KEY_COL_NAME), $"'{dataSort.UserDataKey}'");
                        tableJoins.Add(join);
                    }
                }
            }
            StringBuilder result = new StringBuilder("SELECT ");

            string groupClause = StringUtils.Join(", ", qualifiedGroupByAliases.Select(alias => "V." + alias));

            if (groupByValues)
            {
                mediaItemIdOrGroupSizeAlias = "C";
                // Create an additional COUNT expression for the MEDIA_ITEMS.MEDIA_ITEM_ID in the GROUP BY-statement
                result.Append("COUNT(V.C) ");
                result.Append(mediaItemIdOrGroupSizeAlias);
                if (!string.IsNullOrWhiteSpace(groupClause))
                {
                    result.Append(", ");
                    result.Append(groupClause);
                }
                result.Append(" FROM (");
                result.Append("SELECT DISTINCT ");
                result.Append(miaIdAttribute.GetQualifiedName(ns));
                result.Append(" C");
            }
            else
            {
                // Append plain attribute MEDIA_ITEMS.MEDIA_ITEM_ID if no GROUP BY-statement is requested
                result.Append(miaIdAttribute.GetDeclarationWithAlias(ns, out mediaItemIdOrGroupSizeAlias));

                // System attributes: Necessary to evaluate if a requested MIA is present for the media item
                foreach (KeyValuePair <MediaItemAspectMetadata, TableQueryData> kvp in miaTypeTableQueries)
                {
                    result.Append(", ");
                    string miamColumn = kvp.Value.GetAlias(ns) + "." + MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME;
                    result.Append(miamColumn);
                    string miamAlias = ns.GetOrCreate(miamColumn, "A");
                    result.Append(" ");
                    result.Append(miamAlias);
                    if (miamAlias != null)
                    {
                        miamAliases.Add(kvp.Key, miamAlias);
                    }
                }
            }

            // Selected attributes
            foreach (string selectAttr in selectAttributeDeclarations)
            {
                result.Append(", ");
                result.Append(selectAttr);
            }

            string whereStr = compiledFilter.CreateSqlFilterCondition(ns, requestedAttributes, out bindVars);

            foreach (BindVar bv in sqlVars)
            {
                bindVars.Add(bv);
            }

            result.Append(" FROM ");

            bool firstJoinTable = true;

            // Other joined tables
            foreach (TableJoin tableJoin in tableJoins)
            {
                if (firstJoinTable)
                {
                    result.Append(tableJoin.JoinedTable.GetDeclarationWithAlias(ns));
                    firstJoinTable = false;
                }
                else
                {
                    result.Append(tableJoin.GetJoinDeclaration(ns));
                }
                result.Append(' ');
            }

            if (!string.IsNullOrEmpty(whereStr))
            {
                result.Append(" WHERE ");
                result.Append(whereStr);
            }
            if (groupByValues)
            {
                result.Append(") V");
                if (qualifiedGroupByAliases.Count > 0)
                {
                    result.Append(" GROUP BY ");
                    result.Append(groupClause);
                }
            }
            else
            {
                if (compiledSortInformation != null && compiledSortInformation.Count > 0)
                {
                    IEnumerable <string> sortCriteria = compiledSortInformation.Select(csi => csi.GetSortDeclaration(ns));
                    result.Append(" ORDER BY ");
                    result.Append(StringUtils.Join(", ", sortCriteria));
                }
            }
            statementStr = result.ToString();
        }
Exemple #6
0
        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);
        }
    protected void CompileStatementParts(MIA_Management miaManagement, IFilter filter, Namespace ns, BindVarNamespace bvNamespace,
        ICollection<MediaItemAspectMetadata> requiredMIATypes, string outerMIIDJoinVariable, ICollection<TableJoin> tableJoins,
        IList<object> resultParts, IList<BindVar> resultBindVars)
    {
      if (filter == null)
        return;

      MediaItemIdFilter mediaItemIdFilter = filter as MediaItemIdFilter;
      if (mediaItemIdFilter != null)
      {
        ICollection<Guid> mediaItemIds = mediaItemIdFilter.MediaItemIds;
        if (mediaItemIds.Count == 0)
          resultParts.Add("1 = 2");
        else
        {
          if (mediaItemIds.Count == 1)
          {
            resultParts.Add(outerMIIDJoinVariable);
            BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), mediaItemIds.First(), typeof(Guid));
            resultParts.Add(" = @" + bindVar.Name);
            resultBindVars.Add(bindVar);
          }
          else
          {
            bool first = true;
            ICollection<string> clusterExpressions = new List<string>();
            foreach (IList<Guid> mediaItemIdsCluster in CollectionUtils.Cluster(mediaItemIds, MAX_IN_VALUES_SIZE))
            {
              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(outerMIIDJoinVariable);
              resultParts.Add(" IN (" + StringUtils.Join(", ", bindVarRefs) + ")");
            }
            resultParts.Add(StringUtils.Join(" OR ", clusterExpressions));
          }
        }
        return;
      }

      BooleanCombinationFilter boolFilter = filter as BooleanCombinationFilter;
      if (boolFilter != null)
      {
        int numOperands = boolFilter.Operands.Count;
        IEnumerator enumOperands = boolFilter.Operands.GetEnumerator();
        if (!enumOperands.MoveNext())
          return;
        if (numOperands > 1)
          resultParts.Add("(");
        CompileStatementParts(miaManagement, (IFilter) enumOperands.Current, ns, bvNamespace,
            requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
        while (enumOperands.MoveNext())
        {
          switch (boolFilter.Operator)
          {
            case BooleanOperator.And:
              resultParts.Add(" AND ");
              break;
            case BooleanOperator.Or:
              resultParts.Add(" OR ");
              break;
            default:
              throw new NotImplementedException(string.Format(
                  "Boolean filter operator '{0}' isn't supported by the media library", boolFilter.Operator));
          }
          CompileStatementParts(miaManagement, (IFilter) enumOperands.Current, ns, bvNamespace,
              requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
        }
        if (numOperands > 1)
          resultParts.Add(")");
        return;
      }

      NotFilter notFilter = filter as NotFilter;
      if (notFilter != null)
      {
        resultParts.Add("NOT (");
        CompileStatementParts(miaManagement, notFilter.InnerFilter, ns, bvNamespace,
            requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
        resultParts.Add(")");
        return;
      }

      FalseFilter falseFilter = filter as FalseFilter;
      if (falseFilter != null)
      {
        resultParts.Add("1 = 2");
        return;
      }

      // Must be done before checking IAttributeFilter - EmptyFilter is also an IAttributeFilter but must be
      // compiled in a different way
      EmptyFilter emptyFilter = filter as EmptyFilter;
      if (emptyFilter != null)
      {
        MediaItemAspectMetadata.AttributeSpecification attributeType = emptyFilter.AttributeType;
        requiredMIATypes.Add(attributeType.ParentMIAM);
        Cardinality cardinality = attributeType.Cardinality;
        if (cardinality == Cardinality.Inline || cardinality == Cardinality.ManyToOne)
        {
          resultParts.Add(new QueryAttribute(attributeType));
          resultParts.Add(" IS NULL"); // MTO attributes are joined with left outer joins and thus can also be checked for NULL
        }
        else if (cardinality == Cardinality.OneToMany)
        {
          resultParts.Add("NOT EXISTS(");
          resultParts.Add("SELECT V.");
          resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
          resultParts.Add(" FROM ");
          resultParts.Add(miaManagement.GetMIACollectionAttributeTableName(attributeType));
          resultParts.Add(" V WHERE V.");
          resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
          resultParts.Add("=");
          resultParts.Add(outerMIIDJoinVariable);
          resultParts.Add(")");
        }
        else if (cardinality == Cardinality.ManyToMany)
        {
          resultParts.Add("NOT EXISTS(");
          resultParts.Add("SELECT NM.");
          resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
          resultParts.Add(" FROM ");
          resultParts.Add(miaManagement.GetMIACollectionAttributeNMTableName(attributeType));
          resultParts.Add(" NM INNER JOIN ");
          resultParts.Add(miaManagement.GetMIACollectionAttributeTableName(attributeType));
          resultParts.Add(" V ON NM.");
          resultParts.Add(MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME);
          resultParts.Add(" = V.");
          resultParts.Add(MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME);
          resultParts.Add(" WHERE NM.");
          resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
          resultParts.Add("=");
          resultParts.Add(outerMIIDJoinVariable);
          resultParts.Add(")");
        }
        return;
      }

      IAttributeFilter attributeFilter = filter as IAttributeFilter;
      if (attributeFilter != null)
      {
        // For attribute filters, we have to create different kinds of expressions, depending on the
        // cardinality of the attribute to be filtered.
        // For Inline and MTO attributes, we simply create
        //
        // QA [Operator] [Comparison-Value]
        //
        // for OTM attributes, we create
        //
        // INNER JOIN [OTM-Value-Table] V ON V.MEDIA_ITEM_ID=[Outer-Join-Variable-Placeholder]
        // WHERE [...] and V.VALUE [Operator] [Comparison-Value])
        //
        // for MTM attributes, we create
        //
        // INNER JOIN [MTM-NM-Table] NM ON NM.MEDIA_ITEM_ID=[Outer-Join-Variable-Placeholder]
        // INNER JOIN [MTM-Value-Table] V ON NM.ID = V.ID
        // WHERE [...] AND V.VALUE [Operator] [Comparison-Value])

        MediaItemAspectMetadata.AttributeSpecification attributeType = attributeFilter.AttributeType;
        requiredMIATypes.Add(attributeType.ParentMIAM);
        Cardinality cardinality = attributeType.Cardinality;
        if (cardinality == Cardinality.Inline || cardinality == Cardinality.ManyToOne)
          BuildAttributeFilterExpression(attributeFilter, new QueryAttribute(attributeType), bvNamespace,
              resultParts, resultBindVars);
        else if (cardinality == Cardinality.OneToMany)
        {
          string joinTable = miaManagement.GetMIACollectionAttributeTableName(attributeType);
          string attrName;
          if (!_innerJoinedTables.TryGetValue(joinTable, out attrName))
          {
            TableQueryData tqd = new TableQueryData(joinTable);

            tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqd,
                new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), outerMIIDJoinVariable));
            attrName = new RequestedAttribute(tqd, MIA_Management.COLL_ATTR_VALUE_COL_NAME).GetQualifiedName(ns);
            _innerJoinedTables.Add(joinTable, attrName);
          }
          BuildAttributeFilterExpression(attributeFilter, attrName, bvNamespace, resultParts, resultBindVars);
        }
        else if (cardinality == Cardinality.ManyToMany)
        {
          string miaCollectionAttributeNMTableName = miaManagement.GetMIACollectionAttributeNMTableName(attributeType);
          string attrName;
          if (!_innerJoinedTables.TryGetValue(miaCollectionAttributeNMTableName, out attrName))
          {
            TableQueryData tqdMiaCollectionAttributeNMTable = new TableQueryData(miaCollectionAttributeNMTableName);

            tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqdMiaCollectionAttributeNMTable,
                new RequestedAttribute(tqdMiaCollectionAttributeNMTable, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), outerMIIDJoinVariable));

            TableQueryData tqdMiaCollectionAttributeTable = new TableQueryData(miaManagement.GetMIACollectionAttributeTableName(attributeType));

            tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqdMiaCollectionAttributeTable,
                new RequestedAttribute(tqdMiaCollectionAttributeNMTable, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME),
                new RequestedAttribute(tqdMiaCollectionAttributeTable, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME)));
            attrName = tqdMiaCollectionAttributeTable.GetAlias(ns) + "." + MIA_Management.COLL_ATTR_VALUE_COL_NAME;
            _innerJoinedTables.Add(miaCollectionAttributeNMTableName, attrName);
          }
          BuildAttributeFilterExpression(attributeFilter, attrName, bvNamespace, resultParts, resultBindVars);
        }
        return;
      }
      throw new InvalidDataException("Filter type '{0}' isn't supported by the media library", filter.GetType().Name);
    }
Exemple #8
0
        /// <summary>
        /// Generates a statement to query the values of the complex <see cref="QueryAttribute"/>.
        /// </summary>
        /// <param name="valueAlias">Alias for the value column.</param>
        /// <param name="mediaItemIdAlias">Alias for the ID of the media item to which the value belongs.</param>
        /// <param name="statementStr">Statement which was built by this method.</param>
        /// <param name="bindVars">Bind variables to be inserted into placeholders in the returned <paramref name="statementStr"/>.</param>
        public void GenerateSqlStatement(out string mediaItemIdAlias, out string valueAlias,
                                         out string statementStr, out IList <BindVar> bindVars)
        {
            Namespace        ns          = new Namespace();
            BindVarNamespace bvNamespace = new BindVarNamespace();

            // Contains a mapping of each queried (=selected or filtered) attribute to its request attribute instance
            // data (which holds its requested query table instance)
            IDictionary <QueryAttribute, RequestedAttribute> requestedAttributes = new Dictionary <QueryAttribute, RequestedAttribute>();

            // Dictionary containing as key the requested MIAM instance OR attribute specification of cardinality MTO,
            // mapped to the table query data to request its contents.
            IDictionary <object, TableQueryData> tableQueries = new Dictionary <object, TableQueryData>();

            // Contains the same tables as the tableQueries variable, but in order and enriched with table join data
            IList <TableJoin> tableJoins = new List <TableJoin>();

            // First create the request table query data for the external attribute table, which contains the foreign key
            // to the MIA ID, and the request attribute for that MIA ID.
            // We'll need the requested attribute as join attribute soon.
            TableQueryData     mainJoinTableQuery;
            RequestedAttribute miaIdAttribute;
            RequestedAttribute valueAttribute;
            RequestedAttribute orderAttribute;

            // Build main join table
            switch (_queryAttribute.Cardinality)
            {
            case Cardinality.OneToMany:
                mainJoinTableQuery = new TableQueryData(_miaManagement.GetMIACollectionAttributeTableName(_queryAttribute));
                miaIdAttribute     = new RequestedAttribute(mainJoinTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                valueAttribute     = new RequestedAttribute(mainJoinTableQuery, MIA_Management.COLL_ATTR_VALUE_COL_NAME);
                orderAttribute     = new RequestedAttribute(mainJoinTableQuery, MIA_Management.COLL_ATTR_VALUE_ORDER_COL_NAME);
                break;

            case Cardinality.ManyToMany:
                mainJoinTableQuery = new TableQueryData(_miaManagement.GetMIACollectionAttributeNMTableName(_queryAttribute));
                miaIdAttribute     = new RequestedAttribute(mainJoinTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                TableQueryData collAttrTableQuery = new TableQueryData(_miaManagement.GetMIACollectionAttributeTableName(_queryAttribute));
                tableJoins.Add(new TableJoin("INNER JOIN", collAttrTableQuery,
                                             new RequestedAttribute(mainJoinTableQuery, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME),
                                             new RequestedAttribute(collAttrTableQuery, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME)));
                valueAttribute = new RequestedAttribute(collAttrTableQuery, MIA_Management.COLL_ATTR_VALUE_COL_NAME);
                orderAttribute = new RequestedAttribute(mainJoinTableQuery, MIA_Management.COLL_ATTR_VALUE_ORDER_COL_NAME);
                break;

            default:
                throw new IllegalCallException("Media item aspect attributes of cardinality '{0}' cannot be requested via the {1}",
                                               _queryAttribute.Cardinality, GetType().Name);
            }
            tableJoins.Insert(0, new TableJoin("INNER JOIN", mainJoinTableQuery, null, null)); // The first table join doesn't need the join attributes - see below

            // Ensure that the tables for all necessary MIAs are requested first (INNER JOIN)
            foreach (MediaItemAspectMetadata miaType in _necessaryRequestedMIAs)
            {
                if (tableQueries.ContainsKey(miaType))
                {
                    // We only come here if miaType is the MIA of the requested attribute itself or if it was already queried as necessary MIA, so optimize redundant entry
                    continue;
                }
                //If any MultipleMediaItemAspect are requested, the joining below will cause duplicate values
                //TableQueryData tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType);
                //tableJoins.Add(new TableJoin("INNER JOIN", tqd, new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute));
            }

            CompiledFilter compiledFilter = new CompiledFilter(_miaManagement, _filter, _subqueryFilter, ns, bvNamespace, miaIdAttribute.GetQualifiedName(ns), tableJoins);

            // Build table query data for each Inline attribute which is part of a filter
            // + compile query attribute
            foreach (QueryAttribute attr in compiledFilter.RequiredAttributes)
            {
                if (attr.Attr.Cardinality != Cardinality.Inline && attr.Attr.Cardinality != Cardinality.ManyToOne)
                {
                    continue;
                }
                // Tables of Inline and MTO attributes, which are part of a filter, are joined with main table
                RequestedAttribute ra;
                RequestSimpleAttribute(attr, tableQueries, tableJoins, "LEFT OUTER JOIN", requestedAttributes,
                                       null, miaIdAttribute, out ra);
            }
            StringBuilder result = new StringBuilder("SELECT ");

            // Append MIA ID attribute only if no DISTINCT query is made
            result.Append(_selectProjectionFunction == null ?
                          miaIdAttribute.GetDeclarationWithAlias(ns, out mediaItemIdAlias) :
                          miaIdAttribute.GetDeclarationWithAlias(ns, _selectProjectionFunction, out mediaItemIdAlias));
            result.Append(", ");

            // Selected attributes
            result.Append(valueAttribute.GetDeclarationWithAlias(ns, out valueAlias));

            string whereStr = compiledFilter.CreateSqlFilterCondition(ns, requestedAttributes, out bindVars);

            result.Append(" FROM ");

            bool firstJoinTable = true;

            // Other joined tables
            foreach (TableJoin tableJoin in tableJoins)
            {
                if (firstJoinTable)
                {
                    result.Append(tableJoin.JoinedTable.GetDeclarationWithAlias(ns));
                    firstJoinTable = false;
                }
                else
                {
                    result.Append(tableJoin.GetJoinDeclaration(ns));
                }
                result.Append(' ');
            }

            if (!string.IsNullOrEmpty(whereStr))
            {
                result.Append("WHERE ");
                result.Append(whereStr);
            }

            if (orderAttribute != null)
            {
                result.Append(" ORDER BY ");
                result.Append(orderAttribute.GetQualifiedName(ns));
            }

            statementStr = result.ToString();
        }
    /// <summary>
    /// Generates a statement to query the distinct values of the complex <see cref="QueryAttribute"/>, together with their
    /// occurence count.
    /// </summary>
    /// <param name="queryAttributeFilter">Additional filter which is defined on the <see cref="QueryAttribute"/>.
    /// That filter COULD be part of the <see cref="Filter"/> but this method can highly optimize a filter on the
    /// query attribute, so a filter on that attribute should be given in this parameter and should not be part of the <see cref="Filter"/>.
    /// </param>
    /// <param name="valueAlias">Alias for the value column.</param>
    /// <param name="groupSizeAlias">Alias for the column containing the number of items in each group.</param>
    /// <param name="statementStr">Statement which was built by this method.</param>
    /// <param name="bindVars">Bind variables to be inserted into placeholders in the returned <paramref name="statementStr"/>.</param>
    public void GenerateSqlGroupByStatement(IAttributeFilter queryAttributeFilter,
        out string valueAlias, out string groupSizeAlias, out string statementStr, out IList<BindVar> bindVars)
    {
      Namespace ns = new Namespace();
      BindVarNamespace bvNamespace = new BindVarNamespace();

      // Contains a mapping of each queried (=selected or filtered) attribute to its request attribute instance
      // data (which holds its requested query table instance)
      IDictionary<QueryAttribute, RequestedAttribute> requestedAttributes = new Dictionary<QueryAttribute, RequestedAttribute>();

      // Dictionary containing as key the requested MIAM instance OR attribute specification of cardinality MTO,
      // mapped to the table query data to request its contents.
      IDictionary<object, TableQueryData> tableQueries = new Dictionary<object, TableQueryData>();

      // Contains the same tables as the tableQueries variable, but in order and enriched with table join data
      IList<TableJoin> tableJoins = new List<TableJoin>();

      // First create the request table query data for the MIA main table and the request attribute for the MIA ID.
      // We'll need the requested attribute as join attribute soon.
      MediaItemAspectMetadata queryMIAM = _queryAttribute.ParentMIAM;
      TableQueryData miaTableQuery = new TableQueryData(_miaManagement.GetMIATableName(queryMIAM));
      tableQueries.Add(queryMIAM, miaTableQuery);

      RequestedAttribute miaIdAttribute = new RequestedAttribute(miaTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);

      // Ensure that the tables for all necessary MIAs are requested first (INNER JOIN)
      foreach (MediaItemAspectMetadata miaType in _necessaryRequestedMIAs)
      {
        TableQueryData tqd;
        if (!tableQueries.TryGetValue(miaType, out tqd))
          tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType);
        if (miaType != queryMIAM)
          tableJoins.Add(new TableJoin("INNER JOIN", tqd,
              new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute));
      }

      CompiledFilter compiledFilter = new CompiledFilter(_miaManagement, _filter, ns, bvNamespace, miaIdAttribute.GetQualifiedName(ns), tableJoins);

      // Build table query data for each Inline attribute which is part of a filter
      // + compile query attribute
      foreach (QueryAttribute attr in compiledFilter.RequiredAttributes)
      {
        if (attr.Attr.Cardinality != Cardinality.Inline && attr.Attr.Cardinality != Cardinality.ManyToOne)
          continue;
        // Tables of Inline and MTO attributes, which are part of a filter, are joined with main table
        RequestedAttribute ra;
        RequestSimpleAttribute(attr, tableQueries, tableJoins, "LEFT OUTER JOIN", requestedAttributes,
            null, miaIdAttribute, out ra);
      }

      TableQueryData joinTableQuery;
      RequestedAttribute valueAttribute;

      // Build join table for value attribute
      switch (_queryAttribute.Cardinality)
      {
        case Cardinality.OneToMany:
          joinTableQuery = new TableQueryData(_miaManagement.GetMIACollectionAttributeTableName(_queryAttribute));
          tableJoins.Add(new TableJoin("LEFT OUTER JOIN", joinTableQuery,
              new RequestedAttribute(joinTableQuery, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME),
              new RequestedAttribute(miaTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME)));
          valueAttribute = new RequestedAttribute(joinTableQuery, MIA_Management.COLL_ATTR_VALUE_COL_NAME);
          break;
        case Cardinality.ManyToMany:
          joinTableQuery = new TableQueryData(_miaManagement.GetMIACollectionAttributeNMTableName(_queryAttribute));
          tableJoins.Add(new TableJoin("LEFT OUTER JOIN", joinTableQuery,
              new RequestedAttribute(joinTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME),
              new RequestedAttribute(miaTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME)));
          TableQueryData collAttrTableQuery = new TableQueryData(_miaManagement.GetMIACollectionAttributeTableName(_queryAttribute));
          tableJoins.Add(new TableJoin("LEFT OUTER JOIN", collAttrTableQuery,
              new RequestedAttribute(joinTableQuery, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME),
              new RequestedAttribute(collAttrTableQuery, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME)));
          valueAttribute = new RequestedAttribute(collAttrTableQuery, MIA_Management.COLL_ATTR_VALUE_COL_NAME);
          break;
        default:
          throw new IllegalCallException("Media item aspect attributes of cardinality '{0}' cannot be requested via the {1}",
              _queryAttribute.Cardinality, GetType().Name);
      }

      // Selected attributes
      string valueDeclaration = _selectProjectionFunction == null ?
          valueAttribute.GetDeclarationWithAlias(ns, out valueAlias) :
          valueAttribute.GetDeclarationWithAlias(ns, _selectProjectionFunction, out valueAlias);
      groupSizeAlias = "C";
      StringBuilder result = new StringBuilder("SELECT COUNT(V.ID) ");
      result.Append(groupSizeAlias);
      result.Append(", V.");
      result.Append(valueAlias);
      result.Append(" FROM (");
      result.Append("SELECT DISTINCT ");
      result.Append(miaIdAttribute.GetQualifiedName(ns));
      result.Append(" ID, ");
      result.Append(valueDeclaration);

      string whereStr = compiledFilter.CreateSqlFilterCondition(ns, requestedAttributes, out bindVars);

      result.Append(" FROM ");

      // Always request the mia table
      result.Append(miaTableQuery.GetDeclarationWithAlias(ns));
      result.Append(' ');

      // Other joined tables
      foreach (TableJoin tableJoin in tableJoins)
      {
        result.Append(tableJoin.GetJoinDeclaration(ns));
        result.Append(' ');
      }

      if (!string.IsNullOrEmpty(whereStr) || queryAttributeFilter != null)
      {
        result.Append("WHERE ");
        IList<string> filters = new List<string>(2);
        if (!string.IsNullOrEmpty(whereStr))
          filters.Add(whereStr);
        if (queryAttributeFilter != null)
        {
          IList<object> resultParts = new List<object>();
          CompiledFilter.BuildAttributeFilterExpression(queryAttributeFilter, valueAttribute.GetQualifiedName(ns), bvNamespace,
              resultParts, bindVars);
          string filterStr = CompiledFilter.CreateSimpleSqlFilterCondition(ns, resultParts, requestedAttributes);
          filters.Add(filterStr);
        }
        result.Append(StringUtils.Join(" AND ", filters));
      }

      result.Append(") V GROUP BY V.");
      result.Append(valueAlias);

      statementStr = result.ToString();
    }
    /// <summary>
    /// Generates a statement to query the values of the complex <see cref="QueryAttribute"/>.
    /// </summary>
    /// <param name="valueAlias">Alias for the value column.</param>
    /// <param name="mediaItemIdAlias">Alias for the ID of the media item to which the value belongs.</param>
    /// <param name="statementStr">Statement which was built by this method.</param>
    /// <param name="bindVars">Bind variables to be inserted into placeholders in the returned <paramref name="statementStr"/>.</param>
    public void GenerateSqlStatement(out string mediaItemIdAlias, out string valueAlias,
        out string statementStr, out IList<BindVar> bindVars)
    {
      Namespace ns = new Namespace();
      BindVarNamespace bvNamespace = new BindVarNamespace();

      // Contains a mapping of each queried (=selected or filtered) attribute to its request attribute instance
      // data (which holds its requested query table instance)
      IDictionary<QueryAttribute, RequestedAttribute> requestedAttributes = new Dictionary<QueryAttribute, RequestedAttribute>();

      // Dictionary containing as key the requested MIAM instance OR attribute specification of cardinality MTO,
      // mapped to the table query data to request its contents.
      IDictionary<object, TableQueryData> tableQueries = new Dictionary<object, TableQueryData>();

      // Contains the same tables as the tableQueries variable, but in order and enriched with table join data
      IList<TableJoin> tableJoins = new List<TableJoin>();

      // First create the request table query data for the external attribute table, which contains the foreign key
      // to the MIA ID, and the request attribute for that MIA ID.
      // We'll need the requested attribute as join attribute soon.
      TableQueryData mainJoinTableQuery;
      RequestedAttribute miaIdAttribute;
      RequestedAttribute valueAttribute;

      // Build main join table
      switch (_queryAttribute.Cardinality)
      {
        case Cardinality.OneToMany:
          mainJoinTableQuery = new TableQueryData(_miaManagement.GetMIACollectionAttributeTableName(_queryAttribute));
          miaIdAttribute = new RequestedAttribute(mainJoinTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
          valueAttribute = new RequestedAttribute(mainJoinTableQuery, MIA_Management.COLL_ATTR_VALUE_COL_NAME);
          break;
        case Cardinality.ManyToMany:
          mainJoinTableQuery = new TableQueryData(_miaManagement.GetMIACollectionAttributeNMTableName(_queryAttribute));
          miaIdAttribute = new RequestedAttribute(mainJoinTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
          TableQueryData collAttrTableQuery = new TableQueryData(_miaManagement.GetMIACollectionAttributeTableName(_queryAttribute));
          tableJoins.Add(new TableJoin("INNER JOIN", collAttrTableQuery,
              new RequestedAttribute(mainJoinTableQuery, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME),
              new RequestedAttribute(collAttrTableQuery, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME)));
          valueAttribute = new RequestedAttribute(collAttrTableQuery, MIA_Management.COLL_ATTR_VALUE_COL_NAME);
          break;
        default:
          throw new IllegalCallException("Media item aspect attributes of cardinality '{0}' cannot be requested via the {1}",
              _queryAttribute.Cardinality, GetType().Name);
      }
      tableJoins.Insert(0, new TableJoin("INNER JOIN", mainJoinTableQuery, null, null)); // The first table join doesn't need the join attributes - see below

      // Ensure that the tables for all necessary MIAs are requested first (INNER JOIN)
      foreach (MediaItemAspectMetadata miaType in _necessaryRequestedMIAs)
      {
        if (tableQueries.ContainsKey(miaType))
          // We only come here if miaType is the MIA of the requested attribute itself or if it was already queried as necessary MIA, so optimize redundant entry
          continue;
        TableQueryData tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType);
        tableJoins.Add(new TableJoin("INNER JOIN", tqd,
            new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute));
      }

      CompiledFilter compiledFilter = new CompiledFilter(_miaManagement, _filter, ns, bvNamespace, miaIdAttribute.GetQualifiedName(ns), tableJoins);

      // Build table query data for each Inline attribute which is part of a filter
      // + compile query attribute
      foreach (QueryAttribute attr in compiledFilter.RequiredAttributes)
      {
        if (attr.Attr.Cardinality != Cardinality.Inline && attr.Attr.Cardinality != Cardinality.ManyToOne)
          continue;
        // Tables of Inline and MTO attributes, which are part of a filter, are joined with main table
        RequestedAttribute ra;
        RequestSimpleAttribute(attr, tableQueries, tableJoins, "LEFT OUTER JOIN", requestedAttributes,
            null, miaIdAttribute, out ra);
      }
      StringBuilder result = new StringBuilder("SELECT ");

      // Append MIA ID attribute only if no DISTINCT query is made
      result.Append(_selectProjectionFunction == null ?
          miaIdAttribute.GetDeclarationWithAlias(ns, out mediaItemIdAlias) :
          miaIdAttribute.GetDeclarationWithAlias(ns, _selectProjectionFunction, out mediaItemIdAlias));
      result.Append(", ");

      // Selected attributes
      result.Append(valueAttribute.GetDeclarationWithAlias(ns, out valueAlias));

      string whereStr = compiledFilter.CreateSqlFilterCondition(ns, requestedAttributes, out bindVars);

      result.Append(" FROM ");

      bool firstJoinTable = true;
      // Other joined tables
      foreach (TableJoin tableJoin in tableJoins)
      {
        if (firstJoinTable)
        {
          result.Append(tableJoin.JoinedTable.GetDeclarationWithAlias(ns));
          firstJoinTable = false;
        }
        else
          result.Append(tableJoin.GetJoinDeclaration(ns));
        result.Append(' ');
      }

      if (!string.IsNullOrEmpty(whereStr))
      {
        result.Append("WHERE ");
        result.Append(whereStr);
      }

      statementStr = result.ToString();
    }
 public CompiledSortInformation(RequestedAttribute sortAttribute, SortDirection direction)
 {
   _sortAttribute = sortAttribute;
   _direction = direction;
 }
    protected void GenerateSqlStatement(bool groupByValues,
        IDictionary<MediaItemAspectMetadata, string> miamAliases,
        out string mediaItemIdOrGroupSizeAlias,
        out IDictionary<QueryAttribute, string> attributeAliases,
        out string statementStr, out IList<BindVar> bindVars)
    {
      Namespace ns = new Namespace();
      BindVarNamespace bvNamespace = new BindVarNamespace();

      // Contains a mapping of each queried (=selected or filtered) attribute to its request attribute instance
      // data (which holds its requested query table instance)
      IDictionary<QueryAttribute, RequestedAttribute> requestedAttributes = new Dictionary<QueryAttribute, RequestedAttribute>();
      attributeAliases = new Dictionary<QueryAttribute, string>();

      // Contains a list of qualified attribute names for all select attributes - needed for GROUP BY-expressions
      ICollection<string> qualifiedGroupByAliases = new List<string>();

      // Contains a list of compiled select attribute declarations. We need this in a separate list (in contrast to using
      // the selectAttributes list together with the compiledAttributes map) because it might be the case that
      // an attribute is requested twice. In that rare case, we need a new alias name for it.
      IList<string> selectAttributeDeclarations = new List<string>();

      // Dictionary containing as key the requested MIAM instance OR attribute specification of cardinality MTO,
      // mapped to the table query data to request its contents.
      IDictionary<object, TableQueryData> tableQueries = new Dictionary<object, TableQueryData>();

      // Contains the same tables as the tableQueries variable, but in order and enriched with table join data
      IList<TableJoin> tableJoins = new List<TableJoin>();

      // Contains all table query data for MIA type tables
      IDictionary<MediaItemAspectMetadata, TableQueryData> miaTypeTableQueries = new Dictionary<MediaItemAspectMetadata, TableQueryData>();

      // Albert, 2012-01-29: Optimized query, don't join with media items table, if we have necessary requested MIAs. In that case,
      // we can use one of their media item ids.
      //// First create the request table query data for the MIA main table and the request attribute for the MIA ID.
      //// We'll need the requested attribute as join attribute soon.
      //TableQueryData miaTableQuery = new TableQueryData(MediaLibrary_SubSchema.MEDIA_ITEMS_TABLE_NAME);
      //RequestedAttribute miaIdAttribute = new RequestedAttribute(miaTableQuery, MediaLibrary_SubSchema.MEDIA_ITEMS_ITEM_ID_COL_NAME);

      RequestedAttribute miaIdAttribute = null; // Lazy initialized below

      // Contains CompiledSortInformation instances for each sort information instance
      IList<CompiledSortInformation> compiledSortInformation = null;

      // Ensure that the tables for all necessary MIAs are requested first (INNER JOIN)
      foreach (MediaItemAspectMetadata miaType in _necessaryRequestedMIAs)
      {
        if (tableQueries.ContainsKey(miaType))
          // We only come here if miaType was already queried as necessary MIA, so optimize redundant entry
          continue;
        TableQueryData tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType);
        miaTypeTableQueries.Add(miaType, tqd);
        RequestedAttribute ra;
        // The first table join has invalid join attributes because miaIdAttribute is still null - but only the join table attribute is necessary
        // for the the first table - see below
        tableJoins.Add(new TableJoin("INNER JOIN", tqd,
            ra = new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute));
        if (miaIdAttribute == null)
          miaIdAttribute = ra;
      }

      if (miaIdAttribute == null)
      { // If we didn't request any necessary MIA types, we have to add an artificial table for the miaIdAttribute
        TableQueryData miaTableQuery = new TableQueryData(MediaLibrary_SubSchema.MEDIA_ITEMS_TABLE_NAME);
        tableJoins.Add(new TableJoin("INNER JOIN", miaTableQuery, null, null)); // First table join has invalid join attributes - not needed for first table
        miaIdAttribute = new RequestedAttribute(miaTableQuery, MediaLibrary_SubSchema.MEDIA_ITEMS_ITEM_ID_COL_NAME);
      }

      // Ensure that the tables for all optional MIAs are requested first (LEFT OUTER JOIN)
      // That is necessary to make empty optional MIA types available in the result
      foreach (MediaItemAspectMetadata miaType in _optionalRequestedMIAs)
      {
        if (tableQueries.ContainsKey(miaType))
          // We only come here if miaType was already queried as necessary or optional MIA, so optimize redundant entry
          continue;
        TableQueryData tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType);
        miaTypeTableQueries.Add(miaType, tqd);
        tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqd,
            new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute));
      }

      // Build table query data for each selected Inline and MTO attribute
      // + select query attribute
      // + add alias to selectAttributeDeclarations
      foreach (QueryAttribute attr in _selectAttributes)
      {
        RequestedAttribute ra;
        RequestSimpleAttribute(attr, tableQueries, tableJoins, "LEFT OUTER JOIN", requestedAttributes, miaTypeTableQueries,
            miaIdAttribute, out ra);
        string alias;
        selectAttributeDeclarations.Add(_selectProjectionFunction == null ?
            ra.GetDeclarationWithAlias(ns, out alias) :
            ra.GetDeclarationWithAlias(ns, _selectProjectionFunction, out alias));
        attributeAliases.Add(attr, alias);
        qualifiedGroupByAliases.Add(ra.GetAlias(ns));
      }

      CompiledFilter compiledFilter = new CompiledFilter(_miaManagement, _filter, ns, bvNamespace, miaIdAttribute.GetQualifiedName(ns), tableJoins);

      // Build table query data for each Inline attribute which is part of a filter
      // + compile query attribute
      foreach (QueryAttribute attr in compiledFilter.RequiredAttributes)
      {
        if (attr.Attr.Cardinality != Cardinality.Inline && attr.Attr.Cardinality != Cardinality.ManyToOne)
          continue;
        // Tables of Inline and MTO attributes, which are part of a filter, are joined with main table
        RequestedAttribute ra;
        RequestSimpleAttribute(attr, tableQueries, tableJoins, "LEFT OUTER JOIN", requestedAttributes,
            miaTypeTableQueries, miaIdAttribute, out ra);
      }
      // Build table query data for each sort attribute
      if (_sortInformation != null)
      {
        compiledSortInformation = new List<CompiledSortInformation>();
        foreach (SortInformation sortInformation in _sortInformation)
        {
          MediaItemAspectMetadata.AttributeSpecification attr = sortInformation.AttributeType;
          if (attr.Cardinality != Cardinality.Inline && attr.Cardinality != Cardinality.ManyToOne)
            // Sorting can only be done for Inline and MTO attributes
            continue;
          RequestedAttribute ra;
          RequestSimpleAttribute(new QueryAttribute(attr), tableQueries, tableJoins, "LEFT OUTER JOIN", requestedAttributes,
              miaTypeTableQueries, miaIdAttribute, out ra);
          compiledSortInformation.Add(new CompiledSortInformation(ra, sortInformation.Direction));
        }
      }
      StringBuilder result = new StringBuilder("SELECT ");

      string groupClause = StringUtils.Join(", ", qualifiedGroupByAliases.Select(alias => "V." + alias));
      if (groupByValues)
      {
        mediaItemIdOrGroupSizeAlias = "C";
        // Create an additional COUNT expression for the MEDIA_ITEMS.MEDIA_ITEM_ID in the GROUP BY-statement
        result.Append("COUNT(V.C) ");
        result.Append(mediaItemIdOrGroupSizeAlias);
        if (!string.IsNullOrWhiteSpace(groupClause))
        {
          result.Append(", ");
          result.Append(groupClause);
        }
        result.Append(" FROM (");
        result.Append("SELECT DISTINCT ");
        result.Append(miaIdAttribute.GetQualifiedName(ns));
        result.Append(" C");
      }
      else
      {
        // Append plain attribute MEDIA_ITEMS.MEDIA_ITEM_ID if no GROUP BY-statement is requested
        result.Append(miaIdAttribute.GetDeclarationWithAlias(ns, out mediaItemIdOrGroupSizeAlias));

        // System attributes: Necessary to evaluate if a requested MIA is present for the media item
        foreach (KeyValuePair<MediaItemAspectMetadata, TableQueryData> kvp in miaTypeTableQueries)
        {
          result.Append(", ");
          string miamColumn = kvp.Value.GetAlias(ns) + "." + MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME;
          result.Append(miamColumn);
          string miamAlias = ns.GetOrCreate(miamColumn, "A");
          result.Append(" ");
          result.Append(miamAlias);
          if (miamAlias != null)
            miamAliases.Add(kvp.Key, miamAlias);
        }
      }

      // Selected attributes
      foreach (string selectAttr in selectAttributeDeclarations)
      {
        result.Append(", ");
        result.Append(selectAttr);
      }

      string whereStr = compiledFilter.CreateSqlFilterCondition(ns, requestedAttributes, out bindVars);

      result.Append(" FROM ");

      bool firstJoinTable = true;
      // Other joined tables
      foreach (TableJoin tableJoin in tableJoins)
      {
        if (firstJoinTable)
        {
          result.Append(tableJoin.JoinedTable.GetDeclarationWithAlias(ns));
          firstJoinTable = false;
        }
        else
          result.Append(tableJoin.GetJoinDeclaration(ns));
        result.Append(' ');
      }

      if (!string.IsNullOrEmpty(whereStr))
      {
        result.Append("WHERE ");
        result.Append(whereStr);
      }
      if (groupByValues)
      {
        result.Append(") V");
        if (qualifiedGroupByAliases.Count > 0)
        {
          result.Append(" GROUP BY ");
          result.Append(groupClause);
        }
      }
      else
      {
        if (compiledSortInformation != null && compiledSortInformation.Count > 0)
        {
          IEnumerable<string> sortCriteria = compiledSortInformation.Select(csi => csi.GetSortDeclaration(ns));
          result.Append("ORDER BY ");
          result.Append(StringUtils.Join(", ", sortCriteria));
        }
      }

      statementStr = result.ToString();
    }
Exemple #13
0
        protected void GenerateSubSelectSqlStatement(BindVarNamespace bvNamespace,
                                                     out string statementStr, out IList <BindVar> bindVars)
        {
            Namespace ns = new Namespace();
            IDictionary <MediaItemAspectMetadata, string> miamAliases = new Dictionary <MediaItemAspectMetadata, string>();

            // Contains a mapping of each queried (=selected or filtered) attribute to its request attribute instance
            // data (which holds its requested query table instance)
            IDictionary <QueryAttribute, RequestedAttribute> requestedAttributes = new Dictionary <QueryAttribute, RequestedAttribute>();

            // Dictionary containing as key the requested MIAM instance OR attribute specification of cardinality MTO,
            // mapped to the table query data to request its contents.
            IDictionary <object, TableQueryData> tableQueries = new Dictionary <object, TableQueryData>();

            // Contains the same tables as the tableQueries variable, but in order and enriched with table join data
            IList <TableJoin> tableJoins = new List <TableJoin>();

            // Contains all table query data for MIA type tables
            IDictionary <MediaItemAspectMetadata, TableQueryData> miaTypeTableQueries = new Dictionary <MediaItemAspectMetadata, TableQueryData>();

            RequestedAttribute miaIdAttribute = null; // Lazy initialized below

            // Ensure that the tables for all necessary MIAs are requested first (INNER JOIN)
            foreach (MediaItemAspectMetadata miaType in _necessaryRequestedMIAs)
            {
                if (miaType.IsTransientAspect)
                {
                    continue;
                }
                if (tableQueries.ContainsKey(miaType))
                {
                    // We only come here if miaType was already queried as necessary MIA, so optimize redundant entry
                    continue;
                }

                TableQueryData tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType);
                miaTypeTableQueries.Add(miaType, tqd);
                RequestedAttribute ra;
                // The first table join has invalid join attributes because miaIdAttribute is still null - but only the join table attribute is necessary
                // for the the first table - see below
                tableJoins.Add(new TableJoin("INNER JOIN", tqd,
                                             ra = new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute));
                if (miaIdAttribute == null)
                {
                    miaIdAttribute = ra;
                }
            }

            if (miaIdAttribute == null)
            {                                                                           // If we didn't request any necessary MIA types, we have to add an artificial table for the miaIdAttribute
                TableQueryData miaTableQuery = new TableQueryData(MediaLibrary_SubSchema.MEDIA_ITEMS_TABLE_NAME);
                tableJoins.Add(new TableJoin("INNER JOIN", miaTableQuery, null, null)); // First table join has invalid join attributes - not needed for first table
                miaIdAttribute = new RequestedAttribute(miaTableQuery, MediaLibrary_SubSchema.MEDIA_ITEMS_ITEM_ID_COL_NAME);
            }

            // Ensure that the tables for all optional MIAs are requested first (LEFT OUTER JOIN)
            // That is necessary to make empty optional MIA types available in the result
            foreach (MediaItemAspectMetadata miaType in _optionalRequestedMIAs)
            {
                if (miaType.IsTransientAspect)
                {
                    continue;
                }
                if (tableQueries.ContainsKey(miaType))
                {
                    // We only come here if miaType was already queried as necessary or optional MIA, so optimize redundant entry
                    continue;
                }
                TableQueryData tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType);
                miaTypeTableQueries.Add(miaType, tqd);
                tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqd,
                                             new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute));
            }

            CompiledFilter compiledFilter = CreateCompiledFilter(ns, bvNamespace, miaIdAttribute.GetQualifiedName(ns), tableJoins);

            // Build table query data for each Inline attribute which is part of a filter
            // + compile query attribute
            foreach (QueryAttribute attr in compiledFilter.RequiredAttributes)
            {
                if (attr.Attr.ParentMIAM.IsTransientAspect)
                {
                    continue;
                }
                if (attr.Attr.Cardinality != Cardinality.Inline && attr.Attr.Cardinality != Cardinality.ManyToOne)
                {
                    continue;
                }
                // Tables of Inline and MTO attributes, which are part of a filter, are joined with main table
                RequestedAttribute ra;
                RequestSimpleAttribute(attr, tableQueries, tableJoins, "LEFT OUTER JOIN", requestedAttributes,
                                       miaTypeTableQueries, miaIdAttribute, out ra);
            }

            StringBuilder result = new StringBuilder("SELECT ");

            result.Append(miaIdAttribute.GetQualifiedName(ns));

            string whereStr = compiledFilter.CreateSqlFilterCondition(ns, requestedAttributes, out bindVars);

            result.Append(" FROM ");

            bool firstJoinTable = true;

            // Other joined tables
            foreach (TableJoin tableJoin in tableJoins)
            {
                if (firstJoinTable)
                {
                    result.Append(tableJoin.JoinedTable.GetDeclarationWithAlias(ns));
                    firstJoinTable = false;
                }
                else
                {
                    result.Append(tableJoin.GetJoinDeclaration(ns));
                }
                result.Append(' ');
            }

            if (!string.IsNullOrEmpty(whereStr))
            {
                result.Append(" WHERE ");
                result.Append(whereStr);
            }

            statementStr = result.ToString();
        }
Exemple #14
0
 public TableJoin(string joinType, TableQueryData table, RequestedAttribute joinAttr1, RequestedAttribute joinAttr2) :
     this(joinType, table, (object) joinAttr1, (object) joinAttr2) { }
Exemple #15
0
 public TableJoin(string joinType, TableQueryData table, RequestedAttribute joinAttr1, RequestedAttribute joinAttr2) :
     this(joinType, table, (object)joinAttr1, (object)joinAttr2)
 {
 }
Exemple #16
0
        protected virtual 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)
        {
            if (filter == null)
            {
                return;
            }

            MediaItemIdFilter mediaItemIdFilter = filter as MediaItemIdFilter;

            if (mediaItemIdFilter != null)
            {
                ICollection <Guid> mediaItemIds = mediaItemIdFilter.MediaItemIds;
                if (mediaItemIds.Count == 0)
                {
                    resultParts.Add("1 = 2");
                }
                else
                {
                    if (mediaItemIds.Count == 1)
                    {
                        resultParts.Add(outerMIIDJoinVariable);
                        BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), mediaItemIds.First(), typeof(Guid));
                        resultParts.Add(" = @" + bindVar.Name);
                        resultBindVars.Add(bindVar);
                    }
                    else
                    {
                        bool first = true;
                        ICollection <string> clusterExpressions = new List <string>();
                        foreach (IList <Guid> mediaItemIdsCluster in CollectionUtils.Cluster(mediaItemIds, MAX_IN_VALUES_SIZE))
                        {
                            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(outerMIIDJoinVariable);
                            resultParts.Add(" IN (" + StringUtils.Join(", ", bindVarRefs) + ")");
                        }
                        resultParts.Add(StringUtils.Join(" OR ", clusterExpressions));
                    }
                }
                return;
            }

            BooleanCombinationFilter boolFilter     = filter as BooleanCombinationFilter;
            ICollection <IFilter>    filterOperands = boolFilter?.Operands; //Work on collection reference to avoid chaning original

            if (boolFilter != null && boolFilter.Operator == BooleanOperator.And && boolFilter.Operands.Count > 1 && boolFilter.Operands.ToList().All(x => x is IAttributeFilter))
            {
                ICollection <IFilter> remainingOperands = new List <IFilter>();

                // Special case to do multiple MIA boolean logic first
                IDictionary <Guid, ICollection <IAttributeFilter> > multiGroups = new Dictionary <Guid, ICollection <IAttributeFilter> >();
                foreach (IAttributeFilter operand in filterOperands)
                {
                    MultipleMediaItemAspectMetadata mmiam = operand.AttributeType.ParentMIAM as MultipleMediaItemAspectMetadata;
                    if (mmiam != null)
                    {
                        Guid key = operand.AttributeType.ParentMIAM.AspectId;
                        if (!multiGroups.ContainsKey(key))
                        {
                            multiGroups[key] = new List <IAttributeFilter>();
                        }
                        multiGroups[key].Add(operand);
                    }
                    else
                    {
                        remainingOperands.Add(operand);
                    }
                }

                if (multiGroups.Keys.Count > 0)
                {
                    bool firstGroup = true;
                    foreach (ICollection <IAttributeFilter> filterGroup in multiGroups.Values)
                    {
                        if (firstGroup)
                        {
                            firstGroup = false;
                        }
                        else
                        {
                            resultParts.Add(" AND ");
                        }

                        bool firstItem = true;
                        foreach (IAttributeFilter filterItem in filterGroup)
                        {
                            MediaItemAspectMetadata.AttributeSpecification attributeType = filterItem.AttributeType;
                            if (firstItem)
                            {
                                resultParts.Add(outerMIIDJoinVariable);
                                resultParts.Add(" IN(");
                                resultParts.Add("SELECT ");
                                resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                                resultParts.Add(" FROM ");
                                resultParts.Add(miaManagement.GetMIATableName(attributeType.ParentMIAM));
                                resultParts.Add(" WHERE ");

                                firstItem = false;
                            }
                            else
                            {
                                resultParts.Add(" AND ");
                            }
                            //Empty filter needs to be handled differently to other IAttribute filters
                            if (filterItem is EmptyFilter)
                            {
                                resultParts.Add(miaManagement.GetMIAAttributeColumnName(attributeType));
                                resultParts.Add(" IS NULL");
                            }
                            else
                            {
                                BuildAttributeFilterExpression(filterItem, miaManagement.GetMIAAttributeColumnName(attributeType), bvNamespace, resultParts, resultBindVars);
                            }
                        }
                        resultParts.Add(")");
                    }

                    // Process remaining operands ?
                    if (remainingOperands.Count == 0)
                    {
                        return;
                    }

                    resultParts.Add(" AND ");
                    filterOperands = remainingOperands;
                }
            }
            if (boolFilter != null)
            {
                int         numOperands  = filterOperands.Count;
                IEnumerator enumOperands = filterOperands.GetEnumerator();
                if (!enumOperands.MoveNext())
                {
                    return;
                }
                if (numOperands > 1)
                {
                    resultParts.Add("(");
                }
                CompileStatementParts(miaManagement, (IFilter)enumOperands.Current, subqueryFilter, ns, bvNamespace,
                                      requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
                while (enumOperands.MoveNext())
                {
                    switch (boolFilter.Operator)
                    {
                    case BooleanOperator.And:
                        resultParts.Add(" AND ");
                        break;

                    case BooleanOperator.Or:
                        resultParts.Add(" OR ");
                        break;

                    default:
                        throw new NotImplementedException(string.Format(
                                                              "Boolean filter operator '{0}' isn't supported by the media library", boolFilter.Operator));
                    }
                    CompileStatementParts(miaManagement, (IFilter)enumOperands.Current, subqueryFilter, ns, bvNamespace,
                                          requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
                }
                if (numOperands > 1)
                {
                    resultParts.Add(")");
                }
                return;
            }

            NotFilter notFilter = filter as NotFilter;

            if (notFilter != null)
            {
                resultParts.Add("NOT (");
                CompileStatementParts(miaManagement, notFilter.InnerFilter, subqueryFilter, ns, bvNamespace,
                                      requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
                resultParts.Add(")");
                return;
            }

            FalseFilter falseFilter = filter as FalseFilter;

            if (falseFilter != null)
            {
                resultParts.Add("1 = 2");
                return;
            }

            // Must be done before checking IAttributeFilter - EmptyFilter is also an IAttributeFilter but must be
            // compiled in a different way
            EmptyFilter emptyFilter = filter as EmptyFilter;

            if (emptyFilter != null)
            {
                MediaItemAspectMetadata.AttributeSpecification attributeType = emptyFilter.AttributeType;
                requiredMIATypes.Add(attributeType.ParentMIAM);
                Cardinality cardinality = attributeType.Cardinality;
                if (cardinality == Cardinality.Inline || cardinality == Cardinality.ManyToOne)
                {
                    resultParts.Add(new QueryAttribute(attributeType));
                    resultParts.Add(" IS NULL"); // MTO attributes are joined with left outer joins and thus can also be checked for NULL
                }
                else if (cardinality == Cardinality.OneToMany)
                {
                    resultParts.Add("NOT EXISTS(");
                    resultParts.Add("SELECT 1");
                    resultParts.Add(" FROM ");
                    resultParts.Add(miaManagement.GetMIACollectionAttributeTableName(attributeType));
                    resultParts.Add(" V WHERE V.");
                    resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                    resultParts.Add("=");
                    resultParts.Add(outerMIIDJoinVariable);
                    resultParts.Add(")");
                }
                else if (cardinality == Cardinality.ManyToMany)
                {
                    resultParts.Add("NOT EXISTS(");
                    resultParts.Add("SELECT 1");
                    resultParts.Add(" FROM ");
                    resultParts.Add(miaManagement.GetMIACollectionAttributeNMTableName(attributeType));
                    resultParts.Add(" NM INNER JOIN ");
                    resultParts.Add(miaManagement.GetMIACollectionAttributeTableName(attributeType));
                    resultParts.Add(" V ON NM.");
                    resultParts.Add(MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME);
                    resultParts.Add(" = V.");
                    resultParts.Add(MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME);
                    resultParts.Add(" WHERE NM.");
                    resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                    resultParts.Add("=");
                    resultParts.Add(outerMIIDJoinVariable);
                    resultParts.Add(")");
                }
                return;
            }

            // Must be done before checking IAttributeFilter - EmptyUserDataFilter is also an IAttributeFilter but must be
            // compiled in a different way
            EmptyUserDataFilter emptyUserDataFilter = filter as EmptyUserDataFilter;

            if (emptyUserDataFilter != null)
            {
                BindVar userIdVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), emptyUserDataFilter.UserProfileId, typeof(Guid));

                resultParts.Add("NOT EXISTS(");
                resultParts.Add("SELECT 1");
                resultParts.Add(" FROM ");
                resultParts.Add(UserProfileDataManagement_SubSchema.USER_MEDIA_ITEM_DATA_TABLE_NAME);
                resultParts.Add(" WHERE ");
                resultParts.Add(UserProfileDataManagement_SubSchema.USER_PROFILE_ID_COL_NAME);
                resultParts.Add(" = @" + userIdVar.Name);
                resultBindVars.Add(userIdVar);
                resultParts.Add(" AND ");
                resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_KEY_COL_NAME);
                resultParts.Add(" = '");
                resultParts.Add(emptyUserDataFilter.UserDataKey);
                resultParts.Add("' AND ");
                resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_VALUE_COL_NAME);
                resultParts.Add(" IS NOT NULL ");
                resultParts.Add(" AND ");
                resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_VALUE_COL_NAME);
                resultParts.Add(" <> '' ");
                resultParts.Add(" AND ");
                resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                resultParts.Add("=");
                resultParts.Add(outerMIIDJoinVariable);
                resultParts.Add(")");

                return;
            }

            AbstractRelationshipFilter relationshipFilter = filter as AbstractRelationshipFilter;

            if (relationshipFilter != null)
            {
                resultParts.Add(outerMIIDJoinVariable);
                resultParts.Add(" IN(");
                BuildRelationshipSubquery(relationshipFilter, subqueryFilter, miaManagement, bvNamespace, resultParts, resultBindVars);
                resultParts.Add(")");
                return;
            }

            RelationalUserDataFilter relationalUserDataFilter = filter as RelationalUserDataFilter;

            if (relationalUserDataFilter != null)
            {
                BindVar userIdVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), relationalUserDataFilter.UserProfileId, typeof(Guid));
                BindVar bindVar   = new BindVar(bvNamespace.CreateNewBindVarName("V"), relationalUserDataFilter.FilterValue, typeof(string));

                resultParts.Add("(EXISTS(");
                resultParts.Add("SELECT 1");
                resultParts.Add(" FROM ");
                resultParts.Add(UserProfileDataManagement_SubSchema.USER_MEDIA_ITEM_DATA_TABLE_NAME);
                resultParts.Add(" WHERE ");
                resultParts.Add(UserProfileDataManagement_SubSchema.USER_PROFILE_ID_COL_NAME);
                resultParts.Add(" = @" + userIdVar.Name);
                resultBindVars.Add(userIdVar);
                resultParts.Add(" AND ");
                resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_KEY_COL_NAME);
                resultParts.Add(" = '");
                resultParts.Add(relationalUserDataFilter.UserDataKey);
                resultParts.Add("' AND (");
                resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_VALUE_COL_NAME);
                switch (relationalUserDataFilter.Operator)
                {
                case RelationalOperator.EQ:
                    resultParts.Add(" = ");
                    break;

                case RelationalOperator.NEQ:
                    resultParts.Add(" <> ");
                    break;

                case RelationalOperator.LT:
                    resultParts.Add(" < ");
                    break;

                case RelationalOperator.LE:
                    resultParts.Add(" <= ");
                    break;

                case RelationalOperator.GT:
                    resultParts.Add(" > ");
                    break;

                case RelationalOperator.GE:
                    resultParts.Add(" >= ");
                    break;

                default:
                    throw new NotImplementedException(string.Format(
                                                          "Relational user data filter operator '{0}' isn't supported by the media library", relationalUserDataFilter.Operator));
                }
                resultParts.Add("@" + bindVar.Name);
                resultBindVars.Add(bindVar);
                if (relationalUserDataFilter.IncludeEmpty)
                {
                    resultParts.Add(" OR ");
                    resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_VALUE_COL_NAME);
                    resultParts.Add(" IS NULL ");
                }
                resultParts.Add(") AND ");
                resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                resultParts.Add("=");
                resultParts.Add(outerMIIDJoinVariable);
                resultParts.Add(")");
                if (relationalUserDataFilter.IncludeEmpty)
                {
                    resultParts.Add(" OR NOT EXISTS(");
                    resultParts.Add("SELECT 1");
                    resultParts.Add(" FROM ");
                    resultParts.Add(UserProfileDataManagement_SubSchema.USER_MEDIA_ITEM_DATA_TABLE_NAME);
                    resultParts.Add(" WHERE ");
                    resultParts.Add(UserProfileDataManagement_SubSchema.USER_PROFILE_ID_COL_NAME);
                    resultParts.Add(" = @" + userIdVar.Name);
                    resultParts.Add(" AND ");
                    resultParts.Add(UserProfileDataManagement_SubSchema.USER_DATA_KEY_COL_NAME);
                    resultParts.Add(" = '");
                    resultParts.Add(relationalUserDataFilter.UserDataKey);
                    resultParts.Add("'");
                    resultParts.Add(" AND ");
                    resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                    resultParts.Add("=");
                    resultParts.Add(outerMIIDJoinVariable);
                    resultParts.Add(")");
                }
                resultParts.Add(")");
                return;
            }

            IAttributeFilter attributeFilter = filter as IAttributeFilter;

            if (attributeFilter != null)
            {
                MediaItemAspectMetadata.AttributeSpecification attributeType = attributeFilter.AttributeType;
                if (attributeType.ParentMIAM is MultipleMediaItemAspectMetadata)
                {
                    resultParts.Add(outerMIIDJoinVariable);
                    resultParts.Add(" IN(");
                    resultParts.Add("SELECT ");
                    resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                    resultParts.Add(" FROM ");
                    resultParts.Add(miaManagement.GetMIATableName(attributeType.ParentMIAM));
                    resultParts.Add(" WHERE ");
                    BuildAttributeFilterExpression(attributeFilter, miaManagement.GetMIAAttributeColumnName(attributeType), bvNamespace, resultParts, resultBindVars);
                    resultParts.Add(")");

                    return;
                }

                // For attribute filters, we have to create different kinds of expressions, depending on the
                // cardinality of the attribute to be filtered.
                // For Inline and MTO attributes, we simply create
                //
                // QA [Operator] [Comparison-Value]
                //
                // for OTM attributes, we create
                //
                // INNER JOIN [OTM-Value-Table] V ON V.MEDIA_ITEM_ID=[Outer-Join-Variable-Placeholder]
                // WHERE [...] and V.VALUE [Operator] [Comparison-Value])
                //
                // for MTM attributes, we create
                //
                // INNER JOIN [MTM-NM-Table] NM ON NM.MEDIA_ITEM_ID=[Outer-Join-Variable-Placeholder]
                // INNER JOIN [MTM-Value-Table] V ON NM.ID = V.ID
                // WHERE [...] AND V.VALUE [Operator] [Comparison-Value])

                requiredMIATypes.Add(attributeType.ParentMIAM);
                Cardinality cardinality = attributeType.Cardinality;
                if (cardinality == Cardinality.Inline || cardinality == Cardinality.ManyToOne)
                {
                    BuildAttributeFilterExpression(attributeFilter, new QueryAttribute(attributeType), bvNamespace,
                                                   resultParts, resultBindVars);
                }
                else if (cardinality == Cardinality.OneToMany)
                {
                    string joinTable = miaManagement.GetMIACollectionAttributeTableName(attributeType);
                    string attrName;
                    if (!_innerJoinedTables.TryGetValue(joinTable, out attrName))
                    {
                        TableQueryData tqd = new TableQueryData(joinTable);

                        tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqd,
                                                     new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), outerMIIDJoinVariable));
                        attrName = new RequestedAttribute(tqd, MIA_Management.COLL_ATTR_VALUE_COL_NAME).GetQualifiedName(ns);
                        _innerJoinedTables.Add(joinTable, attrName);
                    }
                    BuildAttributeFilterExpression(attributeFilter, attrName, bvNamespace, resultParts, resultBindVars);
                }
                else if (cardinality == Cardinality.ManyToMany)
                {
                    string miaCollectionAttributeNMTableName = miaManagement.GetMIACollectionAttributeNMTableName(attributeType);
                    string attrName;
                    if (!_innerJoinedTables.TryGetValue(miaCollectionAttributeNMTableName, out attrName))
                    {
                        TableQueryData tqdMiaCollectionAttributeNMTable = new TableQueryData(miaCollectionAttributeNMTableName);

                        tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqdMiaCollectionAttributeNMTable,
                                                     new RequestedAttribute(tqdMiaCollectionAttributeNMTable, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), outerMIIDJoinVariable));

                        TableQueryData tqdMiaCollectionAttributeTable = new TableQueryData(miaManagement.GetMIACollectionAttributeTableName(attributeType));

                        tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqdMiaCollectionAttributeTable,
                                                     new RequestedAttribute(tqdMiaCollectionAttributeNMTable, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME),
                                                     new RequestedAttribute(tqdMiaCollectionAttributeTable, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME)));
                        attrName = tqdMiaCollectionAttributeTable.GetAlias(ns) + "." + MIA_Management.COLL_ATTR_VALUE_COL_NAME;
                        _innerJoinedTables.Add(miaCollectionAttributeNMTableName, attrName);
                    }
                    BuildAttributeFilterExpression(attributeFilter, attrName, bvNamespace, resultParts, resultBindVars);
                }
                return;
            }
            throw new InvalidDataException("Filter type '{0}' isn't supported by the media library", filter.GetType().Name);
        }
Exemple #17
0
        protected void CompileStatementParts(MIA_Management miaManagement, IFilter filter, Namespace ns, BindVarNamespace bvNamespace,
                                             ICollection <MediaItemAspectMetadata> requiredMIATypes, string outerMIIDJoinVariable, ICollection <TableJoin> tableJoins,
                                             IList <object> resultParts, IList <BindVar> resultBindVars)
        {
            if (filter == null)
            {
                return;
            }

            MediaItemIdFilter mediaItemIdFilter = filter as MediaItemIdFilter;

            if (mediaItemIdFilter != null)
            {
                ICollection <Guid> mediaItemIds = mediaItemIdFilter.MediaItemIds;
                if (mediaItemIds.Count == 0)
                {
                    resultParts.Add("1 = 2");
                }
                else
                {
                    if (mediaItemIds.Count == 1)
                    {
                        resultParts.Add(outerMIIDJoinVariable);
                        BindVar bindVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), mediaItemIds.First(), typeof(Guid));
                        resultParts.Add(" = @" + bindVar.Name);
                        resultBindVars.Add(bindVar);
                    }
                    else
                    {
                        bool first = true;
                        ICollection <string> clusterExpressions = new List <string>();
                        foreach (IList <Guid> mediaItemIdsCluster in CollectionUtils.Cluster(mediaItemIds, MAX_IN_VALUES_SIZE))
                        {
                            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(outerMIIDJoinVariable);
                            resultParts.Add(" IN (" + StringUtils.Join(", ", bindVarRefs) + ")");
                        }
                        resultParts.Add(StringUtils.Join(" OR ", clusterExpressions));
                    }
                }
                return;
            }

            BooleanCombinationFilter boolFilter = filter as BooleanCombinationFilter;

            if (boolFilter != null)
            {
                int         numOperands  = boolFilter.Operands.Count;
                IEnumerator enumOperands = boolFilter.Operands.GetEnumerator();
                if (!enumOperands.MoveNext())
                {
                    return;
                }
                if (numOperands > 1)
                {
                    resultParts.Add("(");
                }
                CompileStatementParts(miaManagement, (IFilter)enumOperands.Current, ns, bvNamespace,
                                      requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
                while (enumOperands.MoveNext())
                {
                    switch (boolFilter.Operator)
                    {
                    case BooleanOperator.And:
                        resultParts.Add(" AND ");
                        break;

                    case BooleanOperator.Or:
                        resultParts.Add(" OR ");
                        break;

                    default:
                        throw new NotImplementedException(string.Format(
                                                              "Boolean filter operator '{0}' isn't supported by the media library", boolFilter.Operator));
                    }
                    CompileStatementParts(miaManagement, (IFilter)enumOperands.Current, ns, bvNamespace,
                                          requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
                }
                if (numOperands > 1)
                {
                    resultParts.Add(")");
                }
                return;
            }

            NotFilter notFilter = filter as NotFilter;

            if (notFilter != null)
            {
                resultParts.Add("NOT (");
                CompileStatementParts(miaManagement, notFilter.InnerFilter, ns, bvNamespace,
                                      requiredMIATypes, outerMIIDJoinVariable, tableJoins, resultParts, resultBindVars);
                resultParts.Add(")");
                return;
            }

            FalseFilter falseFilter = filter as FalseFilter;

            if (falseFilter != null)
            {
                resultParts.Add("1 = 2");
                return;
            }

            // Must be done before checking IAttributeFilter - EmptyFilter is also an IAttributeFilter but must be
            // compiled in a different way
            EmptyFilter emptyFilter = filter as EmptyFilter;

            if (emptyFilter != null)
            {
                MediaItemAspectMetadata.AttributeSpecification attributeType = emptyFilter.AttributeType;
                requiredMIATypes.Add(attributeType.ParentMIAM);
                Cardinality cardinality = attributeType.Cardinality;
                if (cardinality == Cardinality.Inline || cardinality == Cardinality.ManyToOne)
                {
                    resultParts.Add(new QueryAttribute(attributeType));
                    resultParts.Add(" IS NULL"); // MTO attributes are joined with left outer joins and thus can also be checked for NULL
                }
                else if (cardinality == Cardinality.OneToMany)
                {
                    resultParts.Add("NOT EXISTS(");
                    resultParts.Add("SELECT V.");
                    resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                    resultParts.Add(" FROM ");
                    resultParts.Add(miaManagement.GetMIACollectionAttributeTableName(attributeType));
                    resultParts.Add(" V WHERE V.");
                    resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                    resultParts.Add("=");
                    resultParts.Add(outerMIIDJoinVariable);
                    resultParts.Add(")");
                }
                else if (cardinality == Cardinality.ManyToMany)
                {
                    resultParts.Add("NOT EXISTS(");
                    resultParts.Add("SELECT NM.");
                    resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                    resultParts.Add(" FROM ");
                    resultParts.Add(miaManagement.GetMIACollectionAttributeNMTableName(attributeType));
                    resultParts.Add(" NM INNER JOIN ");
                    resultParts.Add(miaManagement.GetMIACollectionAttributeTableName(attributeType));
                    resultParts.Add(" V ON NM.");
                    resultParts.Add(MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME);
                    resultParts.Add(" = V.");
                    resultParts.Add(MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME);
                    resultParts.Add(" WHERE NM.");
                    resultParts.Add(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                    resultParts.Add("=");
                    resultParts.Add(outerMIIDJoinVariable);
                    resultParts.Add(")");
                }
                return;
            }

            IAttributeFilter attributeFilter = filter as IAttributeFilter;

            if (attributeFilter != null)
            {
                // For attribute filters, we have to create different kinds of expressions, depending on the
                // cardinality of the attribute to be filtered.
                // For Inline and MTO attributes, we simply create
                //
                // QA [Operator] [Comparison-Value]
                //
                // for OTM attributes, we create
                //
                // INNER JOIN [OTM-Value-Table] V ON V.MEDIA_ITEM_ID=[Outer-Join-Variable-Placeholder]
                // WHERE [...] and V.VALUE [Operator] [Comparison-Value])
                //
                // for MTM attributes, we create
                //
                // INNER JOIN [MTM-NM-Table] NM ON NM.MEDIA_ITEM_ID=[Outer-Join-Variable-Placeholder]
                // INNER JOIN [MTM-Value-Table] V ON NM.ID = V.ID
                // WHERE [...] AND V.VALUE [Operator] [Comparison-Value])

                MediaItemAspectMetadata.AttributeSpecification attributeType = attributeFilter.AttributeType;
                requiredMIATypes.Add(attributeType.ParentMIAM);
                Cardinality cardinality = attributeType.Cardinality;
                if (cardinality == Cardinality.Inline || cardinality == Cardinality.ManyToOne)
                {
                    BuildAttributeFilterExpression(attributeFilter, new QueryAttribute(attributeType), bvNamespace,
                                                   resultParts, resultBindVars);
                }
                else if (cardinality == Cardinality.OneToMany)
                {
                    string joinTable = miaManagement.GetMIACollectionAttributeTableName(attributeType);
                    string attrName;
                    if (!_innerJoinedTables.TryGetValue(joinTable, out attrName))
                    {
                        TableQueryData tqd = new TableQueryData(joinTable);

                        tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqd,
                                                     new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), outerMIIDJoinVariable));
                        attrName = new RequestedAttribute(tqd, MIA_Management.COLL_ATTR_VALUE_COL_NAME).GetQualifiedName(ns);
                        _innerJoinedTables.Add(joinTable, attrName);
                    }
                    BuildAttributeFilterExpression(attributeFilter, attrName, bvNamespace, resultParts, resultBindVars);
                }
                else if (cardinality == Cardinality.ManyToMany)
                {
                    string miaCollectionAttributeNMTableName = miaManagement.GetMIACollectionAttributeNMTableName(attributeType);
                    string attrName;
                    if (!_innerJoinedTables.TryGetValue(miaCollectionAttributeNMTableName, out attrName))
                    {
                        TableQueryData tqdMiaCollectionAttributeNMTable = new TableQueryData(miaCollectionAttributeNMTableName);

                        tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqdMiaCollectionAttributeNMTable,
                                                     new RequestedAttribute(tqdMiaCollectionAttributeNMTable, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), outerMIIDJoinVariable));

                        TableQueryData tqdMiaCollectionAttributeTable = new TableQueryData(miaManagement.GetMIACollectionAttributeTableName(attributeType));

                        tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqdMiaCollectionAttributeTable,
                                                     new RequestedAttribute(tqdMiaCollectionAttributeNMTable, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME),
                                                     new RequestedAttribute(tqdMiaCollectionAttributeTable, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME)));
                        attrName = tqdMiaCollectionAttributeTable.GetAlias(ns) + "." + MIA_Management.COLL_ATTR_VALUE_COL_NAME;
                        _innerJoinedTables.Add(miaCollectionAttributeNMTableName, attrName);
                    }
                    BuildAttributeFilterExpression(attributeFilter, attrName, bvNamespace, resultParts, resultBindVars);
                }
                return;
            }
            throw new InvalidDataException("Filter type '{0}' isn't supported by the media library", filter.GetType().Name);
        }
Exemple #18
0
        /// <summary>
        /// Generates a statement to query the distinct values of the complex <see cref="QueryAttribute"/>, together with their
        /// occurence count.
        /// </summary>
        /// <param name="queryAttributeFilter">Additional filter which is defined on the <see cref="QueryAttribute"/>.
        /// That filter COULD be part of the <see cref="Filter"/> but this method can highly optimize a filter on the
        /// query attribute, so a filter on that attribute should be given in this parameter and should not be part of the <see cref="Filter"/>.
        /// </param>
        /// <param name="valueAlias">Alias for the value column.</param>
        /// <param name="groupSizeAlias">Alias for the column containing the number of items in each group.</param>
        /// <param name="statementStr">Statement which was built by this method.</param>
        /// <param name="bindVars">Bind variables to be inserted into placeholders in the returned <paramref name="statementStr"/>.</param>
        public void GenerateSqlGroupByStatement(IAttributeFilter queryAttributeFilter,
                                                out string valueAlias, out string groupSizeAlias, out string statementStr, out IList <BindVar> bindVars)
        {
            Namespace        ns          = new Namespace();
            BindVarNamespace bvNamespace = new BindVarNamespace();

            // Contains a mapping of each queried (=selected or filtered) attribute to its request attribute instance
            // data (which holds its requested query table instance)
            IDictionary <QueryAttribute, RequestedAttribute> requestedAttributes = new Dictionary <QueryAttribute, RequestedAttribute>();

            // Dictionary containing as key the requested MIAM instance OR attribute specification of cardinality MTO,
            // mapped to the table query data to request its contents.
            IDictionary <object, TableQueryData> tableQueries = new Dictionary <object, TableQueryData>();

            // Contains the same tables as the tableQueries variable, but in order and enriched with table join data
            IList <TableJoin> tableJoins = new List <TableJoin>();

            // First create the request table query data for the MIA main table and the request attribute for the MIA ID.
            // We'll need the requested attribute as join attribute soon.
            MediaItemAspectMetadata queryMIAM     = _queryAttribute.ParentMIAM;
            TableQueryData          miaTableQuery = new TableQueryData(_miaManagement.GetMIATableName(queryMIAM));

            tableQueries.Add(queryMIAM, miaTableQuery);

            RequestedAttribute miaIdAttribute = new RequestedAttribute(miaTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);

            // Ensure that the tables for all necessary MIAs are requested first (INNER JOIN)
            foreach (MediaItemAspectMetadata miaType in _necessaryRequestedMIAs)
            {
                TableQueryData tqd;
                if (!tableQueries.TryGetValue(miaType, out tqd))
                {
                    tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType);
                }
                if (miaType != queryMIAM)
                {
                    tableJoins.Add(new TableJoin("INNER JOIN", tqd,
                                                 new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute));
                }
            }

            CompiledFilter compiledFilter = new CompiledFilter(_miaManagement, _filter, _subqueryFilter, ns, bvNamespace, miaIdAttribute.GetQualifiedName(ns), tableJoins);

            // Build table query data for each Inline attribute which is part of a filter
            // + compile query attribute
            foreach (QueryAttribute attr in compiledFilter.RequiredAttributes)
            {
                if (attr.Attr.Cardinality != Cardinality.Inline && attr.Attr.Cardinality != Cardinality.ManyToOne)
                {
                    continue;
                }
                // Tables of Inline and MTO attributes, which are part of a filter, are joined with main table
                RequestedAttribute ra;
                RequestSimpleAttribute(attr, tableQueries, tableJoins, "LEFT OUTER JOIN", requestedAttributes,
                                       null, miaIdAttribute, out ra);
            }

            TableQueryData     joinTableQuery;
            RequestedAttribute valueAttribute;

            // Build join table for value attribute
            switch (_queryAttribute.Cardinality)
            {
            case Cardinality.OneToMany:
                joinTableQuery = new TableQueryData(_miaManagement.GetMIACollectionAttributeTableName(_queryAttribute));
                tableJoins.Add(new TableJoin("LEFT OUTER JOIN", joinTableQuery,
                                             new RequestedAttribute(joinTableQuery, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME),
                                             new RequestedAttribute(miaTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME)));
                valueAttribute = new RequestedAttribute(joinTableQuery, MIA_Management.COLL_ATTR_VALUE_COL_NAME);
                break;

            case Cardinality.ManyToMany:
                joinTableQuery = new TableQueryData(_miaManagement.GetMIACollectionAttributeNMTableName(_queryAttribute));
                tableJoins.Add(new TableJoin("LEFT OUTER JOIN", joinTableQuery,
                                             new RequestedAttribute(joinTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME),
                                             new RequestedAttribute(miaTableQuery, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME)));
                TableQueryData collAttrTableQuery = new TableQueryData(_miaManagement.GetMIACollectionAttributeTableName(_queryAttribute));
                tableJoins.Add(new TableJoin("LEFT OUTER JOIN", collAttrTableQuery,
                                             new RequestedAttribute(joinTableQuery, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME),
                                             new RequestedAttribute(collAttrTableQuery, MIA_Management.FOREIGN_COLL_ATTR_ID_COL_NAME)));
                valueAttribute = new RequestedAttribute(collAttrTableQuery, MIA_Management.COLL_ATTR_VALUE_COL_NAME);
                break;

            default:
                throw new IllegalCallException("Media item aspect attributes of cardinality '{0}' cannot be requested via the {1}",
                                               _queryAttribute.Cardinality, GetType().Name);
            }

            // Selected attributes
            string valueDeclaration = _selectProjectionFunction == null?
                                      valueAttribute.GetDeclarationWithAlias(ns, out valueAlias) :
                                          valueAttribute.GetDeclarationWithAlias(ns, _selectProjectionFunction, out valueAlias);

            groupSizeAlias = "C";
            StringBuilder result = new StringBuilder("SELECT COUNT(V.ID) ");

            result.Append(groupSizeAlias);
            result.Append(", V.");
            result.Append(valueAlias);
            result.Append(" FROM (");
            result.Append("SELECT DISTINCT ");
            result.Append(miaIdAttribute.GetQualifiedName(ns));
            result.Append(" ID, ");
            result.Append(valueDeclaration);

            string whereStr = compiledFilter.CreateSqlFilterCondition(ns, requestedAttributes, out bindVars);

            result.Append(" FROM ");

            // Always request the mia table
            result.Append(miaTableQuery.GetDeclarationWithAlias(ns));
            result.Append(' ');

            // Other joined tables
            foreach (TableJoin tableJoin in tableJoins)
            {
                result.Append(tableJoin.GetJoinDeclaration(ns));
                result.Append(' ');
            }

            if (!string.IsNullOrEmpty(whereStr) || queryAttributeFilter != null)
            {
                result.Append("WHERE ");
                IList <string> filters = new List <string>(2);
                if (!string.IsNullOrEmpty(whereStr))
                {
                    filters.Add(whereStr);
                }
                if (queryAttributeFilter != null)
                {
                    IList <object> resultParts = new List <object>();
                    CompiledFilter.BuildAttributeFilterExpression(queryAttributeFilter, valueAttribute.GetQualifiedName(ns), bvNamespace,
                                                                  resultParts, bindVars);
                    string filterStr = CompiledFilter.CreateSimpleSqlFilterCondition(ns, resultParts, requestedAttributes);
                    filters.Add(filterStr);
                }
                result.Append(StringUtils.Join(" AND ", filters));
            }

            result.Append(") V GROUP BY V.");
            result.Append(valueAlias);

            statementStr = result.ToString();
        }
Exemple #19
0
        protected void GenerateSingleMIASqlStatement(bool groupByValues,
                                                     IDictionary <MediaItemAspectMetadata, string> miamAliases,
                                                     out string mediaItemIdOrGroupSizeAlias,
                                                     out IDictionary <QueryAttribute, string> attributeAliases,
                                                     out string statementStr, out IList <BindVar> bindVars)
        {
            Namespace        ns          = new Namespace();
            BindVarNamespace bvNamespace = new BindVarNamespace();

            // Contains a mapping of each queried (=selected or filtered) attribute to its request attribute instance
            // data (which holds its requested query table instance)
            IDictionary <QueryAttribute, RequestedAttribute> requestedAttributes = new Dictionary <QueryAttribute, RequestedAttribute>();

            attributeAliases = new Dictionary <QueryAttribute, string>();

            // Contains a list of qualified attribute names for all select attributes - needed for GROUP BY-expressions
            ICollection <string> qualifiedGroupByAliases = new List <string>();

            // Contains a list of compiled select attribute declarations. We need this in a separate list (in contrast to using
            // the selectAttributes list together with the compiledAttributes map) because it might be the case that
            // an attribute is requested twice. In that rare case, we need a new alias name for it.
            IList <string> selectAttributeDeclarations = new List <string>();

            // Dictionary containing as key the requested MIAM instance OR attribute specification of cardinality MTO,
            // mapped to the table query data to request its contents.
            IDictionary <object, TableQueryData> tableQueries = new Dictionary <object, TableQueryData>();

            // Contains the same tables as the tableQueries variable, but in order and enriched with table join data
            IList <TableJoin> tableJoins = new List <TableJoin>();

            // Contains all table query data for MIA type tables
            IDictionary <MediaItemAspectMetadata, TableQueryData> miaTypeTableQueries = new Dictionary <MediaItemAspectMetadata, TableQueryData>();

            //Used to check whether a required MulpleMIA exists
            IList <TableQueryData> miaExistsTables = new List <TableQueryData>();

            RequestedAttribute miaIdAttribute = null; // Lazy initialized below

            // Contains CompiledSortInformation instances for each sort information instance
            IList <CompiledSortInformation> compiledSortInformation = null;

            // Ensure that the tables for all necessary MIAs are requested first (INNER JOIN)
            foreach (MediaItemAspectMetadata miaType in _necessaryRequestedMIAs)
            {
                if (miaType.IsTransientAspect)
                {
                    continue;
                }
                if (tableQueries.ContainsKey(miaType))
                {
                    // We only come here if miaType was already queried as necessary MIA, so optimize redundant entry
                    continue;
                }
                if (miaType is MultipleMediaItemAspectMetadata)
                {
                    miaExistsTables.Add(TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType));
                    continue;
                }

                TableQueryData tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType);
                miaTypeTableQueries.Add(miaType, tqd);
                RequestedAttribute ra;
                // The first table join has invalid join attributes because miaIdAttribute is still null - but only the join table attribute is necessary
                // for the the first table - see below
                tableJoins.Add(new TableJoin("INNER JOIN", tqd,
                                             ra = new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute));
                if (miaIdAttribute == null)
                {
                    miaIdAttribute = ra;
                }
            }

            if (miaIdAttribute == null)
            {                                                                           // If we didn't request any necessary MIA types, we have to add an artificial table for the miaIdAttribute
                TableQueryData miaTableQuery = new TableQueryData(MediaLibrary_SubSchema.MEDIA_ITEMS_TABLE_NAME);
                tableJoins.Add(new TableJoin("INNER JOIN", miaTableQuery, null, null)); // First table join has invalid join attributes - not needed for first table
                miaIdAttribute = new RequestedAttribute(miaTableQuery, MediaLibrary_SubSchema.MEDIA_ITEMS_ITEM_ID_COL_NAME);
            }

            // Ensure that the tables for all optional MIAs are requested first (LEFT OUTER JOIN)
            // That is necessary to make empty optional MIA types available in the result
            foreach (MediaItemAspectMetadata miaType in _optionalRequestedMIAs)
            {
                if (miaType.IsTransientAspect)
                {
                    continue;
                }
                if (tableQueries.ContainsKey(miaType))
                {
                    // We only come here if miaType was already queried as necessary or optional MIA, so optimize redundant entry
                    continue;
                }
                if (miaType is MultipleMediaItemAspectMetadata)
                {
                    continue;
                }

                TableQueryData tqd = tableQueries[miaType] = TableQueryData.CreateTableQueryOfMIATable(_miaManagement, miaType);
                miaTypeTableQueries.Add(miaType, tqd);
                tableJoins.Add(new TableJoin("LEFT OUTER JOIN", tqd,
                                             new RequestedAttribute(tqd, MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME), miaIdAttribute));
            }

            // Build table query data for each selected Inline and MTO attribute
            // + select query attribute
            // + add alias to selectAttributeDeclarations
            foreach (QueryAttribute attr in _selectAttributes)
            {
                if (attr.Attr.ParentMIAM.IsTransientAspect)
                {
                    continue;
                }
                //Multiple MIAs will be handled later
                if (attr.Attr.ParentMIAM is MultipleMediaItemAspectMetadata)
                {
                    continue;
                }

                RequestedAttribute ra;
                RequestSimpleAttribute(attr, tableQueries, tableJoins, "LEFT OUTER JOIN", requestedAttributes, miaTypeTableQueries,
                                       miaIdAttribute, out ra);
                string alias;
                selectAttributeDeclarations.Add(_selectProjectionFunction == null ?
                                                ra.GetDeclarationWithAlias(ns, out alias) :
                                                ra.GetDeclarationWithAlias(ns, _selectProjectionFunction, out alias));
                attributeAliases.Add(attr, alias);
                qualifiedGroupByAliases.Add(ra.GetAlias(ns));
            }

            string subSql;

            GenerateSubSelectSqlStatement(bvNamespace, out subSql, out bindVars);

            // Build table query data for each sort attribute
            if (_sortInformation != null)
            {
                compiledSortInformation = new List <CompiledSortInformation>();
                BindVar userVar = null;
                foreach (ISortInformation sortInformation in _sortInformation)
                {
                    if (AddAttributeSortInformation(sortInformation, miaIdAttribute, requestedAttributes, miaTypeTableQueries, tableQueries, tableJoins, compiledSortInformation))
                    {
                        continue;
                    }
                    if (AddChildAggregateAttributeSortInformation(sortInformation, ns, bvNamespace, miaIdAttribute, tableJoins, compiledSortInformation, bindVars))
                    {
                        continue;
                    }
                    if (AddDataSortInformation(sortInformation, miaIdAttribute, tableJoins, compiledSortInformation, bindVars, ref userVar))
                    {
                        continue;
                    }
                }
            }

            //Build sql statement
            StringBuilder result = new StringBuilder("SELECT ");

            string groupClause = StringUtils.Join(", ", qualifiedGroupByAliases.Select(alias => "V." + alias));

            if (groupByValues)
            {
                mediaItemIdOrGroupSizeAlias = "C";
                // Create an additional COUNT expression for the MEDIA_ITEMS.MEDIA_ITEM_ID in the GROUP BY-statement
                result.Append("COUNT(V.C) ");
                result.Append(mediaItemIdOrGroupSizeAlias);
                if (!string.IsNullOrWhiteSpace(groupClause))
                {
                    result.Append(", ");
                    result.Append(groupClause);
                }
                result.Append(" FROM (");
                result.Append("SELECT DISTINCT ");
                result.Append(miaIdAttribute.GetQualifiedName(ns));
                result.Append(" C");
            }
            else
            {
                // Append plain attribute MEDIA_ITEMS.MEDIA_ITEM_ID if no GROUP BY-statement is requested
                result.Append(miaIdAttribute.GetDeclarationWithAlias(ns, out mediaItemIdOrGroupSizeAlias));

                // System attributes: Necessary to evaluate if a requested MIA is present for the media item
                foreach (KeyValuePair <MediaItemAspectMetadata, TableQueryData> kvp in miaTypeTableQueries)
                {
                    result.Append(", ");
                    string miamColumn = kvp.Value.GetAlias(ns) + "." + MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME;
                    result.Append(miamColumn);
                    string miamAlias = ns.GetOrCreate(miamColumn, "A");
                    result.Append(" ");
                    result.Append(miamAlias);
                    if (miamAlias != null)
                    {
                        miamAliases.Add(kvp.Key, miamAlias);
                    }
                }
            }

            // Selected attributes
            foreach (string selectAttr in selectAttributeDeclarations)
            {
                result.Append(", ");
                result.Append(selectAttr);
            }

            result.Append(" FROM ");

            bool firstJoinTable = true;

            // Other joined tables
            foreach (TableJoin tableJoin in tableJoins)
            {
                if (firstJoinTable)
                {
                    result.Append(tableJoin.JoinedTable.GetDeclarationWithAlias(ns));
                    firstJoinTable = false;
                }
                else
                {
                    result.Append(tableJoin.GetJoinDeclaration(ns));
                }
                result.Append(' ');
            }

            bool whereAdded = false;

            if (!string.IsNullOrEmpty(subSql))
            {
                result.Append(" WHERE ");
                result.Append(miaIdAttribute.GetQualifiedName(ns));
                result.Append($" IN({subSql}) ");
                whereAdded = true;
            }

            if (miaExistsTables.Count > 0)
            {
                foreach (TableQueryData tqd in miaExistsTables)
                {
                    if (!whereAdded)
                    {
                        result.Append(" WHERE ");
                    }
                    else
                    {
                        result.Append(" AND ");
                    }
                    whereAdded = true;
                    result.Append("EXISTS(SELECT 1 FROM ");
                    result.Append(tqd.TableName);
                    result.Append(" WHERE ");
                    result.Append(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
                    result.Append("=");
                    result.Append(miaIdAttribute.GetQualifiedName(ns));
                    result.Append(")");
                }
            }

            if (groupByValues)
            {
                result.Append(") V");
                if (qualifiedGroupByAliases.Count > 0)
                {
                    result.Append(" GROUP BY ");
                    result.Append(groupClause);
                }
            }
            else
            {
                if (compiledSortInformation != null && compiledSortInformation.Count > 0)
                {
                    IEnumerable <string> sortCriteria = compiledSortInformation.Select(csi => csi.GetSortDeclaration(ns));
                    result.Append(" ORDER BY ");
                    result.Append(StringUtils.Join(", ", sortCriteria));
                }
            }
            statementStr = result.ToString();
        }