Example #1
0
        public string GetJoinDeclaration(Namespace ns)
        {
            StringBuilder result = new StringBuilder(100);

            result.Append(_joinType);
            result.Append(" ");
            result.Append(_table.GetDeclarationWithAlias(ns));
            result.Append(" ON ");
            RequestedAttribute ra = _joinAttr1 as RequestedAttribute;

            if (ra != null)
            {
                result.Append(ra.GetQualifiedName(ns));
            }
            else
            {
                result.Append(_joinAttr1);
            }
            result.Append(" = ");
            ra = _joinAttr2 as RequestedAttribute;
            if (ra != null)
            {
                result.Append(ra.GetQualifiedName(ns));
            }
            else
            {
                result.Append(_joinAttr2);
            }
            return(result.ToString());
        }
Example #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());
        }
Example #3
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();
        }
    /// <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();
    }