mondrian.rolap.sql
Class SqlQuery.Dialect

java.lang.Object
  mondrian.rolap.sql.SqlQuery.Dialect

Enclosing class:

SqlQuery

public static class SqlQuery.Dialect
extends Object

Description of a SQL dialect. It is immutable.

Constructor Summary

SqlQuery.Dialect(String quoteIdentifierString, String productName, String productVersion, Set<List<Integer>> supportedResultSetTypes, boolean readOnly, int maxColumnNameLength)

Method Summary

protected 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	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.
String	caseWhenElse(String cond, String thenExpr, String elseExpr)
static SqlQuery.Dialect	create(DatabaseMetaData databaseMetaData) Creates a SqlQuery.Dialect from a DatabaseMetaData.
static SqlQuery.Dialect	create(DataSource dataSource) Creates a SqlQuery.Dialect from a DataSource.
String	forceNullsCollateLast(String expr) Modifies an expression in the ORDER BY clause to ensure that NULL values collate after all non-NULL values.
String	generateInline(List<String> columnNames, List<String> columnTypes, List<String[]> valueList) Generates a SQL statement to represent an inline dataset.
int	getMaxColumnNameLength() Returns the maximum length of the name of a database column or query alias allowed by this dialect.
String	getQuoteIdentifierString() Returns the character which is used to quote identifiers, or null if quoting is not supported.
boolean	isAccess()
boolean	isAS400()
boolean	isCloudscape()
boolean	isDB2()
boolean	isDerby()
boolean	isFirebird() Returns whether the underlying database is Firebird.
boolean	isInformix() Returns whether the underlying database is Informix.
boolean	isIngres() Returns whether the underlying database is Ingres.
boolean	isInterbase() Returns whether the underlying database is Interbase.
boolean	isLucidDB() Returns whether the underlying database is LucidDB.
boolean	isMSSQL() Returns whether the underlying database is Microsoft SQL Server.
boolean	isMySQL() Returns whether the underlying database is MySQL.
boolean	isNullsCollateLast() Returns whether NULL values appear last when sorted using ORDER BY.
boolean	isOldAS400()
boolean	isOracle() Returns whether the underlying database is Oracle.
boolean	isPostgres() Returns whether the underlying database is Postgres.
boolean	isSybase() Returns whether the underlying database is Sybase.
boolean	isTeradata() Returns whether the underlying database is Teradata.
void	quote(StringBuilder buf, Object value, SqlQuery.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 the current SQL 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	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	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	toString()
String	toUpper(String expr)

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

Constructor Detail

SqlQuery.Dialect

SqlQuery.Dialect(String quoteIdentifierString,
                 String productName,
                 String productVersion,
                 Set<List<Integer>> supportedResultSetTypes,
                 boolean readOnly,
                 int maxColumnNameLength)

Method Detail

create

public static SqlQuery.Dialect create(DatabaseMetaData databaseMetaData)

Creates a SqlQuery.Dialect from a DatabaseMetaData.

create

public static SqlQuery.Dialect create(DataSource dataSource)

Creates a SqlQuery.Dialect from a DataSource.

NOTE: This method is not cheap. The implementation gets a connection from the connection pool.

Returns:

Dialect

isAccess

public boolean isAccess()

isDerby

public boolean isDerby()

isCloudscape

public boolean isCloudscape()

isDB2

public boolean isDB2()

isAS400

public boolean isAS400()

isOldAS400

public boolean isOldAS400()

toUpper

public String toUpper(String expr)

Returns:

SQL syntax that converts expr into upper case.

caseWhenElse

public String caseWhenElse(String cond,
                           String thenExpr,
                           String elseExpr)

quoteIdentifier

public String quoteIdentifier(String val)

Encloses an identifier in quotation marks appropriate for the current SQL dialect. For example, quoteIdentifier("emp") yields a string containing "emp" in Oracle, and a string containing [emp] in Access.

quoteIdentifier

public 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

public 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.

quoteIdentifier

public 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

public String getQuoteIdentifierString()

Returns the character which is used to quote identifiers, or null if quoting is not supported.

quoteStringLiteral

public 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.

quoteNumericLiteral

public void quoteNumericLiteral(StringBuilder buf,
                                String value)

Appends to a buffer a numeric literal.

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

quoteBooleanLiteral

public void quoteBooleanLiteral(StringBuilder buf,
                                String value)

Appends to a buffer a boolean literal.

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

quoteDateLiteral

public 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'.

quoteTimeLiteral

public 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'.

quoteTimestampLiteral

public 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'.

isFirebird

public boolean isFirebird()

Returns whether the underlying database is Firebird.

isInformix

public boolean isInformix()

Returns whether the underlying database is Informix.

isIngres

public boolean isIngres()

Returns whether the underlying database is Ingres.

isInterbase

public boolean isInterbase()

Returns whether the underlying database is Interbase.

isLucidDB

public boolean isLucidDB()

Returns whether the underlying database is LucidDB.

isMSSQL

public boolean isMSSQL()

Returns whether the underlying database is Microsoft SQL Server.

isOracle

public boolean isOracle()

Returns whether the underlying database is Oracle.

isPostgres

public boolean isPostgres()

Returns whether the underlying database is Postgres.

isMySQL

public boolean isMySQL()

Returns whether the underlying database is MySQL.

isSybase

public boolean isSybase()

Returns whether the underlying database is Sybase.

isTeradata

public boolean isTeradata()

Returns whether the underlying database is Teradata.

requiresAliasForFromQuery

public boolean requiresAliasForFromQuery()

Returns whether this Dialect requires subqueries in the FROM clause to have an alias.

See Also:

allowsFromQuery()

allowsAs

protected boolean allowsAs()

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

allowsFromQuery

public boolean allowsFromQuery()

Returns whether this Dialect allows a subquery in the from clause, for example

SELECT * FROM (SELECT * FROM t) AS x

See Also:

requiresAliasForFromQuery()

allowsCompoundCountDistinct

public boolean allowsCompoundCountDistinct()

Returns whether this Dialect allows multiple arguments to the COUNT(DISTINCT ...) aggregate function, for example

SELECT COUNT(DISTINCT x, y) FROM t

See Also:

allowsCountDistinct(), allowsMultipleCountDistinct()

allowsCountDistinct

public boolean allowsCountDistinct()

Returns whether this Dialect supports distinct aggregations.

For example, Access does not allow

select count(distinct x) from t

allowsMultipleCountDistinct

public 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

public 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

public 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

quote

public void quote(StringBuilder buf,
                  Object value,
                  SqlQuery.Datatype datatype)

Appends to a buffer a value quoted for its type.

allowsDdl

public 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.

See Also:

DatabaseMetaData.isReadOnly()

isNullsCollateLast

public boolean isNullsCollateLast()

Returns whether NULL values appear last when sorted using ORDER BY. According to the SQL standard, this is implementation-specific.

forceNullsCollateLast

public String forceNullsCollateLast(String expr)

Modifies an expression in the ORDER BY clause to ensure that NULL values collate after all non-NULL values. If isNullsCollateLast() is true, there's nothing to do.

supportsGroupByExpressions

public boolean supportsGroupByExpressions()

Returns whether this Dialect supports expressions in the GROUP BY clause. Derby/Cloudscape do not.

Returns:

Whether this Dialect allows expressions in the GROUP BY clause

supportsGroupingSets

public 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

public 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

requiresOrderByAlias

public 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

public 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.

supportsMultiValueInExpr

public 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

public 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.

Throws:

SQLException - if a database access error occurs

Parameters:

type - defined in ResultSet

concurrency - type defined in ResultSet

Returns:

true if so; false otherwise

toString

public String toString()

Overrides:

toString in class Object

getMaxColumnNameLength

public 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()