mondrian.spi
Interface Dialect

All Known Implementing Classes:
AccessDialect, Db2Dialect, Db2OldAs400Dialect, DerbyDialect, FirebirdDialect, GreenplumDialect, HsqldbDialect, InfobrightDialect, InformixDialect, IngresDialect, InterbaseDialect, JdbcDialectImpl, LucidDbDialect, MicrosoftSqlServerDialect, MySqlDialect, NeoviewDialect, NetezzaDialect, OracleDialect, PostgreSqlDialect, SqlStreamDialect, SybaseDialect, TeradataDialect, 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:

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
Version:
$Id: //open/mondrian-release/3.2/src/main/mondrian/spi/Dialect.java#1 $
Author:
jhyde
See Also:
DialectFactory, DialectManager

Nested Class Summary
static class Dialect.DatabaseProduct
          Enumeration of common database types.
static class Dialect.Datatype
          Datatype of a column.
static class Dialect.NullCollation
          Description of how NULL values are ordered in an ORDER BY clause.
 
Method Summary
 boolean allowsAs()
          Returns whether the SQL dialect allows "AS" in the FROM clause.
 boolean allowsCompoundCountDistinct()
          Returns whether this Dialect allows multiple arguments to the COUNT(DISTINCT ...) aggregate function, for example SELECT COUNT(DISTINCT x, y) FROM t
 boolean allowsCountDistinct()
          Returns whether this Dialect supports distinct aggregations.
 boolean allowsDdl()
          Returns whether this dialect supports common SQL Data Definition Language (DDL) statements such as CREATE TABLE and DROP INDEX.
 boolean allowsDialectSharing()
          Returns whether this Dialect object can be used for all connections from the same data source.
 boolean allowsFromQuery()
          Returns whether this Dialect allows a subquery in the from clause, for example SELECT * FROM (SELECT * FROM t) AS x
 boolean allowsMultipleCountDistinct()
          Returns whether this Dialect supports more than one distinct aggregation in the same query.
 boolean allowsMultipleDistinctSqlMeasures()
          Returns whether this Dialect has performant support of distinct SQL measures in the same query.
 boolean allowsOrderByAlias()
          Returns true if aliases defined in the SELECT clause can be used as expressions in the ORDER BY clause.
 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.
 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.
 String caseWhenElse(String cond, String thenExpr, String elseExpr)
          Generates a conditional statement in this dialect's syntax.
 String generateInline(List<String> columnNames, List<String> columnTypes, List<String[]> valueList)
          Generates a SQL statement to represent an inline dataset.
 String generateOrderItem(String expr, boolean nullable, boolean ascending)
          Generates an item for an ORDER BY clause, sorting in the required direction, and ensuring that NULL values collate after all non-NULL values.
 Dialect.DatabaseProduct getDatabaseProduct()
          Returns the database for this Dialect, or Dialect.DatabaseProduct.UNKNOWN if the database is not a common database.
 int getMaxColumnNameLength()
          Returns the maximum length of the name of a database column or query alias allowed by this dialect.
 Dialect.NullCollation getNullCollation()
          Returns the rule which determines whether NULL values appear first or last when sorted using ORDER BY.
 String getQuoteIdentifierString()
          Returns the character which is used to quote identifiers, or null if quoting is not supported.
 boolean needsExponent(Object value, String valueString)
          If Double values need to include additional exponent in its string represenation.
 void quote(StringBuilder buf, Object value, Dialect.Datatype datatype)
          Appends to a buffer a value quoted for its type.
 void quoteBooleanLiteral(StringBuilder buf, String value)
          Appends to a buffer a boolean literal.
 void quoteDateLiteral(StringBuilder buf, String value)
          Appends to a buffer a date literal.
 String quoteIdentifier(String val)
          Encloses an identifier in quotation marks appropriate for this Dialect.
 void quoteIdentifier(StringBuilder buf, String... names)
          Appends to a buffer a list of identifiers, quoted appropriately for this Dialect.
 String quoteIdentifier(String qual, String name)
          Encloses an identifier in quotation marks appropriate for the current SQL dialect.
 void quoteIdentifier(String val, StringBuilder buf)
          Appends to a buffer an identifier, quoted appropriately for this Dialect.
 void quoteNumericLiteral(StringBuilder buf, String value)
          Appends to a buffer a numeric literal.
 void quoteStringLiteral(StringBuilder buf, String s)
          Appends to a buffer a single-quoted SQL string.
 void quoteTimeLiteral(StringBuilder buf, String value)
          Appends to a buffer a time literal.
 void quoteTimestampLiteral(StringBuilder buf, String value)
          Appends to a buffer a timestamp literal.
 boolean requiresAliasForFromQuery()
          Returns whether this Dialect requires subqueries in the FROM clause to have an alias.
 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.
 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.
 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.
 boolean requiresUnionOrderByOrdinal()
          Returns true if this dialect allows only integers in the ORDER BY clause of a UNION (or other set operation) query.
 boolean supportsGroupByExpressions()
          Returns whether this Dialect supports expressions in the GROUP BY clause.
 boolean supportsGroupingSets()
          Returns whether this Dialect allows the GROUPING SETS construct in the GROUP BY clause.
 boolean supportsMultiValueInExpr()
          Returns true if this dialect supports multi-value IN expressions.
 boolean supportsResultSetConcurrency(int type, int concurrency)
          Returns whether this Dialect supports the given concurrency type in combination with the given result set type.
 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.
 String toUpper(String expr)
          Converts an expression to upper case.
 

Method Detail

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:
allowsFromQuery()

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:
requiresAliasForFromQuery()

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(), allowsMultipleCountDistinct()

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.

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:
DatabaseMetaData.isReadOnly()

getNullCollation

Dialect.NullCollation getNullCollation()
Returns the rule which determines whether NULL values appear first or last when sorted using ORDER BY.

According to the SQL standard, this is implementation-specific. The default behavior is Dialect.NullCollation.POSINF.

Returns:
Rule which determines whether NULL values collate first or last

generateOrderItem

String generateOrderItem(String expr,
                         boolean nullable,
                         boolean ascending)
Generates an item for an ORDER BY clause, sorting in the required direction, and ensuring that NULL values collate after all non-NULL values.

By default, generateOrderItem(expr, true) generates "expr ASC" and generateOrderItem(expr, false) generates "expr DESC". But depending on getNullCollation() and ascending, there may need to be additional code.

For example, on Oracle, where NULLs collate higher than all other values, generateOrderItem(expr, true) generates "expr ASC" and generateOrderItem(expr, false) generates "expr DESC NULLS LAST".

On MySQL, where NULLs collate lower than all other values, generateOrderItem(expr, true) generates "ISNULL(expr), expr ASC" and generateOrderItem(expr, false) generates "expr DESC".

Parameters:
expr - Expression
nullable - Whether expression may have NULL values
ascending - Whether to sort expression ascending
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 Oracle, DB2 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

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:
DatabaseMetaData.getMaxColumnNameLength()

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:
DialectFactory.createDialect(javax.sql.DataSource, java.sql.Connection)

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

Get Mondrian at SourceForge.net. Fast, secure and free Open Source software downloads