Package 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

  • Field Details

  • Constructor Details

    • MySqlDialect

      public MySqlDialect(Connection connection) throws SQLException
      Creates a MySqlDialect.
      Parameters:
      connection - Connection
      Throws:
      SQLException - on error

  • Method Details

    • 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:

    • 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:

    • 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:

    • 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

    • quoteBooleanLiteral

      public void quoteBooleanLiteral(StringBuilder buf, String value)
      Description copied from interface: Dialect
      Appends to a buffer a boolean literal.

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

      Specified by:
      quoteBooleanLiteral in interface Dialect
      Overrides:
      quoteBooleanLiteral in class JdbcDialectImpl
      Parameters:
      buf - Buffer to append to
      value - 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