Class MySqlDialect

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