Package mondrian.spi

Interface Dialect

All Known Implementing Classes:
AccessDialect, Db2Dialect, Db2OldAs400Dialect, DerbyDialect, FirebirdDialect, GoogleBigQueryDialect, GreenplumDialect, HiveDialect, HsqldbDialect, ImpalaDialect, InfobrightDialect, InformixDialect, IngresDialect, InterbaseDialect, JdbcDialectImpl, LucidDbDialect, MariaDBDialect, MicrosoftSqlServerDialect, MonetDbDialect, MySqlDialect, NeoviewDialect, NetezzaDialect, NuoDbDialect, OracleDialect, PdiDataServiceDialect, PostgreSqlDialect, RedshiftDialect, SnowflakeDialect, SqlStreamDialect, SybaseDialect, TeradataDialect, VectorwiseDialect, VerticaDialect
public interface Dialect
Description of an SQL dialect.

Instantiating a dialect

A dialect is instantiated via a DialectFactory.

In JDBC terms, a dialect is analogous to a Connection, and a dialect factory is analogous to a Driver, in the sense that the JDBC driver manager maintains a chain of registered drivers, and each driver in turn is given the opportunity to create a connection that can handle a particular JDBC connect string. For dialects, each registered dialect factory is given the chance to create a dialect that matches a particular connection.

A dialect factory may be explicit or implicit:

  • An explicit factory is declared by creating a public static final member in the dialect class called "FACTORY".
  • If there is no explicit factory, Mondrian requires that the class has a public constructor that takes a Connection as its sole parameter, and the DialectManager creates an implicit factory that calls that constructor.

Dialect factories can also be the means for caching or pooling dialects. See allowsDialectSharing() and DialectFactory for more details.

Registering dialects

A dialect needs to be registered with the system in order to be used. Call DialectManager.register(DialectFactory) to register a dialect factory, or DialectManager.register(Class) to register a dialect class.

Mondrian can load dialects on startup. To enable this for your dialect,

  1. Place your dialect class in a JAR file.
  2. Include in the JAR file a file called "META-INF/services/mondrian.spi.Dialect", containing the name of your dialect class.
  3. Ensure that the JAR file is on the class path.

Writing a dialect

To implement a dialect, write a class that implements the Dialect interface. It is recommended that you subclass JdbcDialectImpl, to help to make your dialect is forwards compatible, but it is not mandatory.

A dialects should be immutable. Mondrian assumes that dialects can safely be shared between threads that use the same JDBC connection without synchronization. If allowsDialectSharing() returns true, Mondrian may use the same dialect for different connections from the same JDBC data source.

Load the FoodMart data set into your database, and run Mondrian's suite of regression tests. In particular, get mondrian.test.DialectTest to run cleanly first; this will ensure that the dialect's claims are consistent with the actual behavior of your database.

Since:
Oct 10, 2008
Author:
jhyde
See Also:

  • Method Details

    • toUpper

      String toUpper(String expr)
      Converts an expression to upper case.

      For example, for MySQL, toUpper("foo.bar") returns "UPPER(foo.bar)".

      Parameters:
      expr - SQL expression
      Returns:
      SQL syntax that converts expr into upper case.

    • caseWhenElse

      String caseWhenElse(String cond, String thenExpr, String elseExpr)
      Generates a conditional statement in this dialect's syntax.

      For example, caseWhenElse("b", "1", "0") returns "case when b then 1 else 0 end" on Oracle, "Iif(b, 1, 0)" on Access.

      Parameters:
      cond - Predicate expression
      thenExpr - Expression if condition is true
      elseExpr - Expression if condition is false
      Returns:
      Conditional expression

    • quoteIdentifier

      String quoteIdentifier(String val)
      Encloses an identifier in quotation marks appropriate for this Dialect.

      For example, quoteIdentifier("emp") yields a string containing "emp" in Oracle, and a string containing [emp] in Access.

      Parameters:
      val - Identifier
      Returns:
      Quoted identifier

    • quoteIdentifier

      void quoteIdentifier(String val, StringBuilder buf)
      Appends to a buffer an identifier, quoted appropriately for this Dialect.
      Parameters:
      val - identifier to quote (must not be null).
      buf - Buffer

    • quoteIdentifier

      String quoteIdentifier(String qual, String name)
      Encloses an identifier in quotation marks appropriate for the current SQL dialect. For example, in Oracle, where the identifiers are quoted using double-quotes, quoteIdentifier("schema","table") yields a string containing "schema"."table".
      Parameters:
      qual - Qualifier. If it is not null, "qual". is prepended.
      name - Name to be quoted.
      Returns:
      Quoted identifier

    • quoteIdentifier

      void quoteIdentifier(StringBuilder buf, String... names)
      Appends to a buffer a list of identifiers, quoted appropriately for this Dialect.

      Names in the list may be null, but there must be at least one non-null name in the list.

      Parameters:
      buf - Buffer
      names - List of names to be quoted

    • getQuoteIdentifierString

      String getQuoteIdentifierString()
      Returns the character which is used to quote identifiers, or null if quoting is not supported.
      Returns:
      identifier quote

    • quoteStringLiteral

      void quoteStringLiteral(StringBuilder buf, String s)
      Appends to a buffer a single-quoted SQL string.

      For example, in the default dialect, quoteStringLiteral(buf, "Can't") appends "'Can''t'" to buf.

      Parameters:
      buf - Buffer to append to
      s - Literal

    • quoteNumericLiteral

      void quoteNumericLiteral(StringBuilder buf, String value)
      Appends to a buffer a numeric literal.

      In the default dialect, numeric literals are printed as is.

      Parameters:
      buf - Buffer to append to
      value - Literal

    • quoteBooleanLiteral

      void quoteBooleanLiteral(StringBuilder buf, String value)
      Appends to a buffer a boolean literal.

      In the default dialect, boolean literals are printed as is.

      Parameters:
      buf - Buffer to append to
      value - Literal

    • quoteDateLiteral

      void quoteDateLiteral(StringBuilder buf, String value)
      Appends to a buffer a date literal.

      For example, in the default dialect, quoteStringLiteral(buf, "1969-03-17") appends DATE '1969-03-17'.

      Parameters:
      buf - Buffer to append to
      value - Literal

    • quoteTimeLiteral

      void quoteTimeLiteral(StringBuilder buf, String value)
      Appends to a buffer a time literal.

      For example, in the default dialect, quoteStringLiteral(buf, "12:34:56") appends TIME '12:34:56'.

      Parameters:
      buf - Buffer to append to
      value - Literal

    • quoteTimestampLiteral

      void quoteTimestampLiteral(StringBuilder buf, String value)
      Appends to a buffer a timestamp literal.

      For example, in the default dialect, quoteStringLiteral(buf, "1969-03-17 12:34:56") appends TIMESTAMP '1969-03-17 12:34:56'.

      Parameters:
      buf - Buffer to append to
      value - Literal

    • requiresAliasForFromQuery

      boolean requiresAliasForFromQuery()
      Returns whether this Dialect requires subqueries in the FROM clause to have an alias.
      Returns:
      whether dialewct requires subqueries to have an alias
      See Also:

    • allowsAs

      boolean allowsAs()
      Returns whether the SQL dialect allows "AS" in the FROM clause. If so, "SELECT * FROM t AS alias" is a valid query.
      Returns:
      whether dialect allows AS in FROM clause

    • allowsFromQuery

      boolean allowsFromQuery()
      Returns whether this Dialect allows a subquery in the from clause, for example
      SELECT * FROM (SELECT * FROM t) AS x
      Returns:
      whether Dialect allows subquery in FROM clause
      See Also:

    • allowsCompoundCountDistinct

      boolean allowsCompoundCountDistinct()
      Returns whether this Dialect allows multiple arguments to the COUNT(DISTINCT ...) aggregate function, for example
      SELECT COUNT(DISTINCT x, y) FROM t
      Returns:
      whether Dialect allows multiple arguments to COUNT DISTINCT
      See Also:

    • allowsCountDistinct

      boolean allowsCountDistinct()
      Returns whether this Dialect supports distinct aggregations.

      For example, Access does not allow

      select count(distinct x) from t
      Returns:
      whether Dialect allows COUNT DISTINCT

    • allowsMultipleCountDistinct

      boolean allowsMultipleCountDistinct()
      Returns whether this Dialect supports more than one distinct aggregation in the same query.

      In Derby 10.1,

      select couunt(distinct x) from t
      is OK, but
      select couunt(distinct x), count(distinct y) from t
      gives "Multiple DISTINCT aggregates are not supported at this time."
      Returns:
      whether this Dialect supports more than one distinct aggregation in the same query

    • allowsMultipleDistinctSqlMeasures

      boolean allowsMultipleDistinctSqlMeasures()
      Returns whether this Dialect has performant support of distinct SQL measures in the same query.
      Returns:
      whether this dialect supports multiple count(distinct subquery) measures in one query.

    • allowsCountDistinctWithOtherAggs

      boolean allowsCountDistinctWithOtherAggs()
      Returns whether this Dialect supports distinct aggregations with other aggregations in the same query. This may be enabled for performance reasons (Vertica)
      Returns:
      whether this Dialect supports more than one distinct aggregation in the same query

    • generateInline

      String generateInline(List<String> columnNames, List<String> columnTypes, List<String[]> valueList)
      Generates a SQL statement to represent an inline dataset.

      For example, for Oracle, generates 

       SELECT 1 AS FOO, 'a' AS BAR FROM dual
 UNION ALL
 SELECT 2 AS FOO, 'b' AS BAR FROM dual

      For ANSI SQL, generates: 

       VALUES (1, 'a'), (2, 'b')
      Parameters:
      columnNames - List of column names
      columnTypes - List of column types ("String" or "Numeric")
      valueList - List of rows values
      Returns:
      SQL string

    • needsExponent

      boolean needsExponent(Object value, String valueString)
      If Double values need to include additional exponent in its string represenation. This is to make sure that Double literals will be interpreted as doubles by LucidDB.
      Parameters:
      value - Double value to generate string for
      valueString - java string representation for this value.
      Returns:
      whether an additional exponent "E0" needs to be appended

    • quote

      void quote(StringBuilder buf, Object value, Dialect.Datatype datatype)
      Appends to a buffer a value quoted for its type.
      Parameters:
      buf - Buffer to append to
      value - Value
      datatype - Datatype of value

    • allowsDdl

      boolean allowsDdl()
      Returns whether this dialect supports common SQL Data Definition Language (DDL) statements such as CREATE TABLE and DROP INDEX.

      Access seems to allow DDL iff the .mdb file is writeable.

      Returns:
      whether this Dialect supports DDL
      See Also:

    • generateOrderItem

      String generateOrderItem(String expr, boolean nullable, boolean ascending, boolean collateNullsLast)
      Generates an item for an ORDER BY clause, sorting in the required direction, and ensuring that NULL values collate either before or after all non-NULL values, depending on the collateNullsLast parameter.
      Parameters:
      expr - Expression
      nullable - Whether expression may have NULL values
      ascending - Whether to sort expression ascending
      collateNullsLast - Whether the null values should be sorted first or last.
      Returns:
      Expression modified so that NULL values collate last

    • supportsGroupByExpressions

      boolean supportsGroupByExpressions()
      Returns whether this Dialect supports expressions in the GROUP BY clause. Derby/Cloudscape and Infobright do not.
      Returns:
      Whether this Dialect allows expressions in the GROUP BY clause

    • supportsGroupingSets

      boolean supportsGroupingSets()
      Returns whether this Dialect allows the GROUPING SETS construct in the GROUP BY clause. Currently Greenplum, IBM DB2, Oracle, and Teradata.
      Returns:
      Whether this Dialect allows GROUPING SETS clause

    • supportsUnlimitedValueList

      boolean supportsUnlimitedValueList()
      Returns whether this Dialect places no limit on the number of rows which can appear as elements of an IN or VALUES expression.
      Returns:
      whether value list length is unlimited

    • requiresGroupByAlias

      boolean requiresGroupByAlias()
      Returns true if this Dialect can include expressions in the GROUP BY clause only by adding an expression to the SELECT clause and using its alias.

      For example, in such a dialect,

      SELECT x, x FROM t GROUP BY x
      would be illegal, but
      SELECT x AS a, x AS b FROM t ORDER BY a, b
      would be legal.

      Infobright is the only such dialect.

      Returns:
      Whether this Dialect can include expressions in the GROUP BY clause only by adding an expression to the SELECT clause and using its alias

    • requiresOrderByAlias

      boolean requiresOrderByAlias()
      Returns true if this Dialect can include expressions in the ORDER BY clause only by adding an expression to the SELECT clause and using its alias.

      For example, in such a dialect,

      SELECT x FROM t ORDER BY x + y
      would be illegal, but
      SELECT x, x + y AS z FROM t ORDER BY z
      would be legal.

      MySQL, DB2 and Ingres are examples of such dialects.

      Returns:
      Whether this Dialect can include expressions in the ORDER BY clause only by adding an expression to the SELECT clause and using its alias

    • requiresHavingAlias

      boolean requiresHavingAlias()
      Returns true if this Dialect can include expressions in the HAVING clause only by adding an expression to the SELECT clause and using its alias.

      For example, in such a dialect,

      SELECT CONCAT(x) as foo FROM t HAVING CONCAT(x) LIKE "%"
      would be illegal, but
      SELECT CONCAT(x) as foo FROM t HAVING foo LIKE "%"
      would be legal.

      MySQL is an example of such dialects.

      Returns:
      Whether this Dialect can include expressions in the HAVING clause only by adding an expression to the SELECT clause and using its alias

    • allowsOrderByAlias

      boolean allowsOrderByAlias()
      Returns true if aliases defined in the SELECT clause can be used as expressions in the ORDER BY clause.

      For example, in such a dialect,

      SELECT x, x + y AS z FROM t ORDER BY z
      would be legal.

      MySQL, DB2 and Ingres are examples of dialects where this is true; Access is a dialect where this is false.

      Returns:
      Whether aliases defined in the SELECT clause can be used as expressions in the ORDER BY clause.

    • requiresUnionOrderByOrdinal

      boolean requiresUnionOrderByOrdinal()
      Returns true if this dialect allows only integers in the ORDER BY clause of a UNION (or other set operation) query.

      For example, SELECT x, y + z FROM t
      UNION ALL
      SELECT x, y + z FROM t
      ORDER BY 1, 2       is allowed but SELECT x, y, z FROM t
      UNION ALL
      SELECT x, y, z FROM t
      ORDER BY x       is not.

      Teradata is an example of a dialect with this restriction.

      Returns:
      whether this dialect allows only integers in the ORDER BY clause of a UNION (or other set operation) query

    • requiresUnionOrderByExprToBeInSelectClause

      boolean requiresUnionOrderByExprToBeInSelectClause()
      Returns true if this dialect allows an expression in the ORDER BY clause of a UNION (or other set operation) query only if it occurs in the SELECT clause.

      For example, SELECT x, y + z FROM t
      UNION ALL
      SELECT x, y + z FROM t
      ORDER BY y + z       is allowed but SELECT x, y, z FROM t
      UNION ALL
      SELECT x, y, z FROM t
      ORDER BY y + z       SELECT x, y, z FROM t ORDER BY y + z is not.

      Access is an example of a dialect with this restriction.

      Returns:
      whether this dialect allows an expression in the ORDER BY clause of a UNION (or other set operation) query only if it occurs in the SELECT clause

    • supportsMultiValueInExpr

      boolean supportsMultiValueInExpr()
      Returns true if this dialect supports multi-value IN expressions. E.g., WHERE (col1, col2) IN ((val1a, val2a), (val1b, val2b))
      Returns:
      true if the dialect supports multi-value IN expressions

    • supportsResultSetConcurrency

      boolean supportsResultSetConcurrency(int type, int concurrency)
      Returns whether this Dialect supports the given concurrency type in combination with the given result set type.

      The result is similar to DatabaseMetaData.supportsResultSetConcurrency(int, int), except that the JdbcOdbc bridge in JDK 1.6 overstates its abilities. See bug 1690406.

      Parameters:
      type - defined in ResultSet
      concurrency - type defined in ResultSet
      Returns:
      true if so; false otherwise

    • getMaxColumnNameLength

      int getMaxColumnNameLength()
      Returns the maximum length of the name of a database column or query alias allowed by this dialect.
      Returns:
      maximum number of characters in a column name
      See Also:

    • getDatabaseProduct

      Dialect.DatabaseProduct getDatabaseProduct()
      Returns the database for this Dialect, or Dialect.DatabaseProduct.UNKNOWN if the database is not a common database.
      Returns:
      Database

    • appendHintsAfterFromClause

      void appendHintsAfterFromClause(StringBuilder buf, Map<String,String> hints)
      Assembles and returns a string containing any hints that should be appended after the FROM clause in a SELECT statement, based on any hints provided. Any unrecognized or unsupported hints will be ignored.
      Parameters:
      buf - The Stringbuffer to which the dialect-specific syntax for any relevant table hints may be appended. Must not be null.
      hints - A map of table hints provided in the schema definition

    • allowsDialectSharing

      boolean allowsDialectSharing()
      Returns whether this Dialect object can be used for all connections from the same data source.

      The default implementation returns true, and this allows dialects to be cached and reused in environments where connections are allocated from a pool based on the same data source.

      Data sources are deemed 'equal' by the same criteria used by Java collections, namely the Object.equals(Object) and Object.hashCode() methods.

      Returns:
      Whether this dialect can be used for other connections created from the same data source
      See Also:

    • allowsSelectNotInGroupBy

      boolean allowsSelectNotInGroupBy()
      Returns whether the database currently permits queries to include in the SELECT clause expressions that are not listed in the GROUP BY clause. The SQL standard allows this if the database can deduce that the expression is functionally dependent on columns in the GROUP BY clause.

      For example, SELECT empno, first_name || ' ' || last_name FROM emps GROUP BY empno is valid because empno is the primary key of the emps table, and therefore all columns are dependent on it. For a given value of empno, first_name || ' ' || last_name has a unique value.

      Most databases do not, MySQL is an example of one that does (if the functioality is enabled).

      Returns:
      Whether this Dialect allows SELECT clauses to contain columns that are not in the GROUP BY clause

    • allowsJoinOn

      boolean allowsJoinOn()
      Returns whether this dialect supports "ANSI-style JOIN syntax", FROM leftTable JOIN rightTable ON conditon.
      Returns:
      Whether this dialect supports FROM-JOIN-ON syntax.

    • allowsRegularExpressionInWhereClause

      boolean allowsRegularExpressionInWhereClause()
      Informs Mondrian if the dialect supports regular expressions when creating the 'where' or the 'having' clause.
      Returns:
      True if regular expressions are supported.

    • generateCountExpression

      String generateCountExpression(String exp)
      Some databases, like Greenplum, don't include nulls as part of the results of a COUNT sql call. This allows dialects to wrap the count expression in something before it is used in the query.
      Parameters:
      exp - The expression to wrap.
      Returns:
      A valid expression to use for a count operation.

    • generateRegularExpression

      String generateRegularExpression(String source, String javaRegExp)
      Must generate a String representing a regular expression match operation between a string literal and a Java regular expression. The string literal might be a column identifier or some other identifier, but the implementation must presume that it is already escaped and fit for use. The regular expression is not escaped and must be adapted to the proper dialect rules.

      Postgres / Greenplum example:

      generateRegularExpression( "'foodmart'.'customer_name'", "(?i).*oo.*") -> 'foodmart'.'customer_name' ~ "(?i).*oo.*"

      Oracle example:

      generateRegularExpression( "'foodmart'.'customer_name'", ".*oo.*") -> REGEXP_LIKE('foodmart'.'customer_name', ".*oo.*")

      Dialects are allowed to return null if the dialect cannot convert that particular regular expression into something that the database would support.

      Parameters:
      source - A String identifying the column to match against.
      javaRegExp - A Java regular expression to match against.
      Returns:
      A dialect specific matching operation, or null if the dialect cannot convert that particular regular expression into something that the database would support.

    • getStatisticsProviders

      List<StatisticsProvider> getStatisticsProviders()
      Returns a list of statistics providers for this dialect.

      The default implementation looks for the value of the property mondrian.statistics.providers.PRODUCT where product is the current dialect's product name (for example "MYSQL"). If that property has no value, looks at the property mondrian.statistics.providers. The property value should be a comma-separated list of names of classes that implement the StatisticsProvider interface. For each statistic required, Mondrian will call the method each statistics provider in turn, until one of them returns a non-negative value.

    • getType

      SqlStatement.Type getType(ResultSetMetaData metadata, int columnIndex) throws SQLException

      Chooses the most appropriate type for accessing the values of a column in a result set for a dialect.

      Dialect-specific nuances involving type representation should be encapsulated in implementing methods. For example, if a dialect has implicit rules involving scale or precision, they should be handled within this method so the client can simply retrieve the "best fit" SqlStatement.Type for the column.

      Parameters:
      metadata - Results set metadata
      columnIndex - Column ordinal (0-based)
      Returns:
      the most appropriate SqlStatement.Type for the column
      Throws:
      SQLException