mondrian.spi.impl

Class MySqlDialect

java.lang.Object

mondrian.spi.impl.JdbcDialectImpl

mondrian.spi.impl.MySqlDialect

All Implemented Interfaces:

Dialect

Direct Known Subclasses:

InfobrightDialect, MariaDBDialect

public class MySqlDialect
extends JdbcDialectImpl

Implementation of Dialect for the MySQL database.

Since:

Nov 23, 2008

Author:

jhyde

Nested Class Summary

Nested classes/interfaces inherited from interface mondrian.spi.Dialect

Dialect.DatabaseProduct, Dialect.Datatype

Field Summary

Fields

Modifier and Type	Field and Description
static JdbcDialectFactory	FACTORY

Fields inherited from class mondrian.spi.impl.JdbcDialectImpl

databaseProduct, permitsSelectNotInGroupBy, productVersion

Constructor Summary

Constructors

Constructor and Description
MySqlDialect(Connection connection) Creates a MySqlDialect.

Method Summary

Modifier and Type	Method and Description
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	allowsFromQuery() Returns whether this Dialect allows a subquery in the from clause, for example SELECT * FROM (SELECT * FROM t) AS x
boolean	allowsRegularExpressionInWhereClause() Informs Mondrian if the dialect supports regular expressions when creating the 'where' or the 'having' 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.
protected String	deduceIdentifierQuoteString(DatabaseMetaData databaseMetaData)
protected String	deduceProductName(DatabaseMetaData databaseMetaData)
protected boolean	deduceSupportsSelectNotInGroupBy(Connection connection) Detects whether the database is configured to permit queries that include columns in the SELECT that are not also in the GROUP BY.
String	generateInline(List<String> columnNames, List<String> columnTypes, List<String[]> valueList) Generates a SQL statement to represent an inline dataset.
protected String	generateOrderByNulls(String expr, boolean ascending, boolean collateNullsLast) Generates SQL to force null values to collate last.
String	generateRegularExpression(String source, String javaRegex) Must generate a String representing a regular expression match operation between a string literal and a Java regular expression.
static boolean	isInfobright(DatabaseMetaData databaseMetaData) Detects whether this database is Infobright.
void	quoteStringLiteral(StringBuilder buf, String s) Appends to a buffer a single-quoted SQL string.
boolean	requiresAliasForFromQuery() Returns whether this Dialect requires subqueries in the FROM clause to have an alias.
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.
boolean	requiresOrderByAlias() Required for MySQL 5.7+, where SQL_MODE include ONLY_FULL_GROUP_BY by default.
boolean	supportsMultiValueInExpr() Returns true if this dialect supports multi-value IN expressions.

Methods inherited from class mondrian.spi.impl.JdbcDialectImpl

allowsAs, allowsCountDistinct, allowsCountDistinctWithOtherAggs, allowsDdl, allowsDialectSharing, allowsJoinOn, allowsMultipleCountDistinct, allowsMultipleDistinctSqlMeasures, allowsOrderByAlias, allowsSelectNotInGroupBy, caseWhenElse, computeStatisticsProviders, deduceMaxColumnNameLength, deduceProductVersion, deduceReadOnly, deduceSupportedResultSetStyles, generateCountExpression, generateInlineForAnsi, generateInlineGeneric, generateOrderByNullsAnsi, generateOrderItem, getDatabaseProduct, getMaxColumnNameLength, getProduct, getQuoteIdentifierString, getStatisticsProviders, getType, isDatabase, needsExponent, quote, quoteBooleanLiteral, quoteDateLiteral, quoteDateLiteral, quoteIdentifier, quoteIdentifier, quoteIdentifier, quoteIdentifier, quoteNumericLiteral, quoteTimeLiteral, quoteTimestampLiteral, requiresGroupByAlias, requiresUnionOrderByExprToBeInSelectClause, requiresUnionOrderByOrdinal, supportsGroupByExpressions, supportsGroupingSets, supportsResultSetConcurrency, supportsUnlimitedValueList, toString, toUpper

Methods inherited from class java.lang.Object

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

Field Detail

FACTORY

public static final JdbcDialectFactory FACTORY

Constructor Detail

MySqlDialect

public MySqlDialect(Connection connection)
             throws SQLException

Creates a MySqlDialect.

Parameters:

connection - Connection

Throws:

SQLException - on error

Method Detail

isInfobright

public static boolean isInfobright(DatabaseMetaData databaseMetaData)

Detects whether this database is Infobright.

Infobright uses the MySQL driver and appears to be a MySQL instance. The only difference is the presence of the BRIGHTHOUSE engine.

Parameters:

databaseMetaData - Database metadata

Returns:

Whether this is Infobright

deduceProductName

protected String deduceProductName(DatabaseMetaData databaseMetaData)

Overrides:

deduceProductName in class JdbcDialectImpl

deduceIdentifierQuoteString

protected String deduceIdentifierQuoteString(DatabaseMetaData databaseMetaData)

Overrides:

deduceIdentifierQuoteString in class JdbcDialectImpl

deduceSupportsSelectNotInGroupBy

protected boolean deduceSupportsSelectNotInGroupBy(Connection connection)
                                            throws SQLException

Description copied from class: JdbcDialectImpl

Detects whether the database is configured to permit queries that include columns in the SELECT that are not also in the GROUP BY. MySQL is an example of one that does, though this is configurable.

The expectation is that this will not change while Mondrian is running, though some databases (MySQL) allow changing it on the fly.

Overrides:

deduceSupportsSelectNotInGroupBy in class JdbcDialectImpl

Parameters:

connection - The database connection

Returns:

Whether the feature is enabled.

Throws:

SQLException - on error

appendHintsAfterFromClause

public void appendHintsAfterFromClause(StringBuilder buf,
                                       Map<String,String> hints)

Description copied from interface: Dialect

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.

Specified by:

appendHintsAfterFromClause in interface Dialect

Overrides:

appendHintsAfterFromClause in class JdbcDialectImpl

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

requiresAliasForFromQuery

public boolean requiresAliasForFromQuery()

Description copied from interface: Dialect

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

Specified by:

requiresAliasForFromQuery in interface Dialect

Overrides:

requiresAliasForFromQuery in class JdbcDialectImpl

Returns:

whether dialewct requires subqueries to have an alias

See Also:

Dialect.allowsFromQuery()

allowsFromQuery

public boolean allowsFromQuery()

Description copied from interface: Dialect

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

SELECT * FROM (SELECT * FROM t) AS x

Specified by:

allowsFromQuery in interface Dialect

Overrides:

allowsFromQuery in class JdbcDialectImpl

Returns:

whether Dialect allows subquery in FROM clause

See Also:

Dialect.requiresAliasForFromQuery()

allowsCompoundCountDistinct

public boolean allowsCompoundCountDistinct()

Description copied from interface: Dialect

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

SELECT COUNT(DISTINCT x, y) FROM t

Specified by:

allowsCompoundCountDistinct in interface Dialect

Overrides:

allowsCompoundCountDistinct in class JdbcDialectImpl

Returns:

whether Dialect allows multiple arguments to COUNT DISTINCT

See Also:

Dialect.allowsCountDistinct(), Dialect.allowsMultipleCountDistinct()

quoteStringLiteral

public void quoteStringLiteral(StringBuilder buf,
                               String s)

Description copied from interface: Dialect

Appends to a buffer a single-quoted SQL string.

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

Specified by:

quoteStringLiteral in interface Dialect

Overrides:

quoteStringLiteral in class JdbcDialectImpl

Parameters:

buf - Buffer to append to

s - Literal

generateInline

public String generateInline(List<String> columnNames,
                             List<String> columnTypes,
                             List<String[]> valueList)

Description copied from interface: Dialect

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')
 

Specified by:

generateInline in interface Dialect

Overrides:

generateInline in class JdbcDialectImpl

Parameters:

columnNames - List of column names

columnTypes - List of column types ("String" or "Numeric")

valueList - List of rows values

Returns:

SQL string

generateOrderByNulls

protected String generateOrderByNulls(String expr,
                                      boolean ascending,
                                      boolean collateNullsLast)

Description copied from class: JdbcDialectImpl

Generates SQL to force null values to collate last.

This default implementation makes use of the ANSI SQL 1999 CASE-WHEN-THEN-ELSE in conjunction with IS NULL syntax. The resulting SQL will look something like this:

CASE WHEN "expr" IS NULL THEN 0 ELSE 1 END

You can override this method for a particular database to use something more efficient, like ISNULL().

ANSI SQL provides the syntax "ASC/DESC NULLS LAST" and "ASC/DESC NULLS FIRST". If your database supports the ANSI syntax, implement this method by calling JdbcDialectImpl.generateOrderByNullsAnsi(java.lang.String, boolean, boolean).

This method is only called from JdbcDialectImpl.generateOrderItem(String, boolean, boolean, boolean). Some dialects override that method and therefore never call this method.

Overrides:

generateOrderByNulls in class JdbcDialectImpl

Parameters:

expr - Expression.

ascending - Whether ascending.

collateNullsLast - Whether nulls should appear first or last.

Returns:

Expression to force null values to collate last or first.

requiresHavingAlias

public boolean requiresHavingAlias()

Description copied from interface: Dialect

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.

Specified by:

requiresHavingAlias in interface Dialect

Overrides:

requiresHavingAlias in class JdbcDialectImpl

Returns:

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

supportsMultiValueInExpr

public boolean supportsMultiValueInExpr()

Description copied from interface: Dialect

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

Specified by:

supportsMultiValueInExpr in interface Dialect

Overrides:

supportsMultiValueInExpr in class JdbcDialectImpl

Returns:

true if the dialect supports multi-value IN expressions

allowsRegularExpressionInWhereClause

public boolean allowsRegularExpressionInWhereClause()

Description copied from interface: Dialect

Informs Mondrian if the dialect supports regular expressions when creating the 'where' or the 'having' clause.

Specified by:

allowsRegularExpressionInWhereClause in interface Dialect

Overrides:

allowsRegularExpressionInWhereClause in class JdbcDialectImpl

Returns:

True if regular expressions are supported.

generateRegularExpression

public String generateRegularExpression(String source,
                                        String javaRegex)

Description copied from interface: Dialect

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.

Specified by:

generateRegularExpression in interface Dialect

Overrides:

generateRegularExpression in class JdbcDialectImpl

Parameters:

source - A String identifying the column to match against.

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

requiresOrderByAlias

public boolean requiresOrderByAlias()

Required for MySQL 5.7+, where SQL_MODE include ONLY_FULL_GROUP_BY by default. This prevent expressions like ISNULL(RTRIM(`promotion_name`)) ASC from being used in ORDER BY section. ISNULL(`c0`) ASC will be used, where `c0` is an alias of the RTRIM(`promotion_name`). And this is important for the cases where we're using SQL expressions in a Level definition. Jira ticket, that describes the issue: http://jira.pentaho.com/browse/MONDRIAN-2451

Specified by:

requiresOrderByAlias in interface Dialect

Overrides:

requiresOrderByAlias in class JdbcDialectImpl

Returns:

true when MySQL version is 5.7 or larger