Package mondrian.rolap.sql

Class SqlQuery

java.lang.Object
mondrian.rolap.sql.SqlQuery
public class SqlQuery extends Object
SqlQuery allows us to build a select statement and generate it in database-specific SQL syntax.

Notable differences in database syntax are:

Identifier quoting
Oracle (and all JDBC-compliant drivers) uses double-quotes, for example select * from "emp". Access prefers brackets, for example select * from [emp]. mySQL allows single- and double-quotes for string literals, and therefore does not allow identifiers to be quoted, for example select 'foo', "bar" from emp.
AS in from clause
Oracle doesn't like AS in the from * clause, for example select from emp as e vs. select * from emp e.
Column aliases
Some databases require that every column in the select list has a valid alias. If the expression is an expression containing non-alphanumeric characters, an explicit alias is needed. For example, Oracle will barfs at select empno + 1 from emp.
Parentheses around table names
Oracle doesn't like select * from (emp)
Queries in FROM clause
PostgreSQL and hsqldb don't allow, for example, select * from (select * from emp) as e.
Uniqueness of index names
In PostgreSQL and Oracle, index names must be unique within the database; in Access and hsqldb, they must merely be unique within their table
Datatypes
In Oracle, BIT is CHAR(1), TIMESTAMP is DATE. In PostgreSQL, DOUBLE is DOUBLE PRECISION, BIT is BOOL.

NOTE: Instances of this class are NOT thread safe so the user must make sure this is accessed by only one thread at a time.

Author:
jhyde

  • Constructor Details

    • SqlQuery

      public SqlQuery(Dialect dialect, boolean formatted)
      Base constructor used by all other constructors to create an empty instance.
      Parameters:
      dialect - Dialect
      formatted - Whether to generate SQL formatted on multiple lines

    • SqlQuery

      public SqlQuery(Dialect dialect)
      Creates a SqlQuery using a given dialect and inheriting the formatting preferences from MondrianProperties.GenerateFormattedSql property.
      Parameters:
      dialect - Dialect

  • Method Details

    • cloneEmpty

      public SqlQuery cloneEmpty()
      Creates an empty SqlQuery with the same environment as this one. (As per the Gang of Four 'prototype' pattern.)

    • setDistinct

      public void setDistinct(boolean distinct)

    • setAllowHints

      public void setAllowHints(boolean t)
      Chooses whether table optimization hints may be used (assuming the dialect supports it).
      Parameters:
      t - True to allow hints to be used, false otherwise

    • addFromQuery

      public boolean addFromQuery(String query, String alias, boolean failIfExists)
      Adds a subquery to the FROM clause of this Query with a given alias. If the query already exists it either, depending on failIfExists, throws an exception or does not add the query and returns false.
      Parameters:
      query - Subquery
      alias - (if not null, must not be zero length).
      failIfExists - if true, throws exception if alias already exists
      Returns:
      true if query *was* added

    • addFrom

      public void addFrom(SqlQuery sqlQuery, String alias, boolean failIfExists)

    • addFrom

      public boolean addFrom(MondrianDef.RelationOrJoin relation, String alias, boolean failIfExists)
      Adds a relation to a query, adding appropriate join conditions, unless it is already present.

      Returns whether the relation was added to the query.

      Parameters:
      relation - Relation to add
      alias - Alias of relation. If null, uses relation's alias.
      failIfExists - Whether to fail if relation is already present
      Returns:
      true, if relation *was* added to query

    • addSelect

      public String addSelect(String expression, SqlStatement.Type type)
      Adds an expression to the select clause, automatically creating a column alias.

    • addSelectGroupBy

      public String addSelectGroupBy(String expression, SqlStatement.Type type)
      Adds an expression to the SELECT and GROUP BY clauses. Uses the alias in the GROUP BY clause, if the dialect requires it.
      Parameters:
      expression - Expression
      Returns:
      Alias of expression

    • getCurrentSelectListSize

      public int getCurrentSelectListSize()

    • nextColumnAlias

      public String nextColumnAlias()

    • addSelect

      public String addSelect(String expression, SqlStatement.Type type, String alias)
      Adds an expression to the select clause, with a specified type and column alias.
      Parameters:
      expression - Expression
      type - Java type to be used to hold cursor column
      alias - Column alias (or null for no alias)
      Returns:
      Column alias

    • getAlias

      public String getAlias(String expression)

    • addWhere

      public void addWhere(String exprLeft, String exprMid, String exprRight)

    • addWhere

      public void addWhere(RolapStar.Condition joinCondition)

    • addWhere

      public void addWhere(String expression)

    • addGroupBy

      public void addGroupBy(String expression)

    • addGroupBy

      public void addGroupBy(String expression, String alias)

    • addHaving

      public void addHaving(String expression)

    • addOrderBy

      public void addOrderBy(String expr, boolean ascending, boolean prepend, boolean nullable)
      Adds an item to the ORDER BY clause.
      Parameters:
      expr - the expr to order by
      ascending - sort direction
      prepend - whether to prepend to the current list of items
      nullable - whether the expression might be null

    • addOrderBy

      public void addOrderBy(String expr, String alias, boolean ascending, boolean prepend, boolean nullable, boolean collateNullsLast)
      Adds an item to the ORDER BY clause.
      Parameters:
      expr - the expr to order by
      alias - the alias of the column, as returned by addSelect
      ascending - sort direction
      prepend - whether to prepend to the current list of items
      nullable - whether the expression might be null
      collateNullsLast - whether null values should appear first or last.

    • toString

      public String toString()
      Overrides:
      toString in class Object

    • toBuffer

      public void toBuffer(StringBuilder buf, String prefix)
      Writes this SqlQuery to a StringBuilder with each clause on a separate line, and with the specified indentation prefix.
      Parameters:
      buf - String builder
      prefix - Prefix for each line

    • getDialect

      public Dialect getDialect()

    • newQuery

      public static SqlQuery newQuery(DataSource dataSource, String err)

    • addGroupingSet

      public void addGroupingSet(List<String> groupingColumnsExpr)

    • addGroupingFunction

      public void addGroupingFunction(String columnExpr)

    • toSqlAndTypes

      public Pair<String,List<SqlStatement.Type>> toSqlAndTypes()

    • registerRootRelation

      public void registerRootRelation(MondrianDef.RelationOrJoin root)

    • isSupported

      public boolean isSupported()

    • setSupported

      public void setSupported(boolean supported)