001/*
002// This software is subject to the terms of the Eclipse Public License v1.0
003// Agreement, available at the following URL:
004// http://www.eclipse.org/legal/epl-v10.html.
005// You must accept the terms of that agreement to use this software.
006//
007// Copyright (C) 2008-2011 Pentaho
008// All Rights Reserved.
009*/
010package mondrian.spi.impl;
011
012import mondrian.olap.Util;
013
014import java.sql.*;
015import java.util.List;
016import java.util.Map;
017import java.util.regex.*;
018
019/**
020 * Implementation of {@link mondrian.spi.Dialect} for the MySQL database.
021 *
022 * @author jhyde
023 * @since Nov 23, 2008
024 */
025public class MySqlDialect extends JdbcDialectImpl {
026
027    private final String flagsRegexp = "^(\\(\\?([a-zA-Z])\\)).*$";
028    private final Pattern flagsPattern = Pattern.compile(flagsRegexp);
029    private final String escapeRegexp = "(\\\\Q([^\\\\Q]+)\\\\E)";
030    private final Pattern escapePattern = Pattern.compile(escapeRegexp);
031
032    public static final JdbcDialectFactory FACTORY =
033        new JdbcDialectFactory(
034            MySqlDialect.class,
035            DatabaseProduct.MYSQL)
036        {
037            protected boolean acceptsConnection(Connection connection) {
038                try {
039                    // Infobright looks a lot like MySQL. If this is an
040                    // Infobright connection, yield to the Infobright dialect.
041                    return super.acceptsConnection(connection)
042                        && !isInfobright(connection.getMetaData());
043                } catch (SQLException e) {
044                    throw Util.newError(
045                        e, "Error while instantiating dialect");
046                }
047            }
048        };
049
050    /**
051     * Creates a MySqlDialect.
052     *
053     * @param connection Connection
054     *
055     * @throws SQLException on error
056     */
057    public MySqlDialect(Connection connection) throws SQLException {
058        super(connection);
059    }
060
061    /**
062     * Detects whether this database is Infobright.
063     *
064     * <p>Infobright uses the MySQL driver and appears to be a MySQL instance.
065     * The only difference is the presence of the BRIGHTHOUSE engine.
066     *
067     * @param databaseMetaData Database metadata
068     *
069     * @return Whether this is Infobright
070     */
071    public static boolean isInfobright(
072        DatabaseMetaData databaseMetaData)
073    {
074        Statement statement = null;
075        try {
076            String productVersion =
077                databaseMetaData.getDatabaseProductVersion();
078            if (productVersion.compareTo("5.1") >= 0) {
079                statement = databaseMetaData.getConnection().createStatement();
080                final ResultSet resultSet =
081                    statement.executeQuery(
082                        "select * from INFORMATION_SCHEMA.engines "
083                        + "where ENGINE = 'BRIGHTHOUSE'");
084                if (resultSet.next()) {
085                    return true;
086                }
087            }
088            return false;
089        } catch (SQLException e) {
090            throw Util.newInternal(
091                e,
092                "while running query to detect Brighthouse engine");
093        } finally {
094            if (statement != null) {
095                try {
096                    statement.close();
097                } catch (SQLException e) {
098                    // ignore
099                }
100            }
101        }
102    }
103
104    @Override
105    protected String deduceProductName(DatabaseMetaData databaseMetaData) {
106        final String productName = super.deduceProductName(databaseMetaData);
107        if (isInfobright(databaseMetaData)) {
108            return "MySQL (Infobright)";
109        }
110        return productName;
111    }
112
113    protected String deduceIdentifierQuoteString(
114        DatabaseMetaData databaseMetaData)
115    {
116        String quoteIdentifierString =
117            super.deduceIdentifierQuoteString(databaseMetaData);
118
119        if (quoteIdentifierString == null) {
120            // mm.mysql.2.0.4 driver lies. We know better.
121            quoteIdentifierString = "`";
122        }
123        return quoteIdentifierString;
124    }
125
126    protected boolean deduceSupportsSelectNotInGroupBy(Connection connection)
127        throws SQLException
128    {
129        boolean supported = false;
130        String sqlmode = getCurrentSqlMode(connection);
131        if (sqlmode == null) {
132            supported = true;
133        } else {
134            if (!sqlmode.contains("ONLY_FULL_GROUP_BY")) {
135                supported = true;
136            }
137        }
138        return supported;
139    }
140
141    private String getCurrentSqlMode(Connection connection)
142        throws SQLException
143    {
144        return getSqlMode(connection, Scope.SESSION);
145    }
146
147    private String getSqlMode(Connection connection, Scope scope)
148        throws SQLException
149    {
150        String sqlmode = null;
151        Statement s = null;
152        ResultSet rs = null;
153        try {
154            s = connection.createStatement();
155            if (s.execute("SELECT @@" + scope + ".sql_mode")) {
156                rs = s.getResultSet();
157                if (rs.next()) {
158                    sqlmode = rs.getString(1);
159                }
160            }
161        } finally {
162            if (rs != null) {
163                try {
164                    rs.close();
165                } catch (SQLException e) {
166                    // ignore
167                }
168            }
169            if (s != null) {
170                try {
171                    s.close();
172                } catch (SQLException e) {
173                    // ignore
174                }
175            }
176        }
177        return sqlmode;
178    }
179
180
181    public void appendHintsAfterFromClause(
182        StringBuilder buf,
183        Map<String, String> hints)
184    {
185        if (hints != null) {
186            String forcedIndex = hints.get("force_index");
187            if (forcedIndex != null) {
188                buf.append(" FORCE INDEX (");
189                buf.append(forcedIndex);
190                buf.append(")");
191            }
192        }
193    }
194
195    public boolean requiresAliasForFromQuery() {
196        return true;
197    }
198
199    public boolean allowsFromQuery() {
200        // MySQL before 4.0 does not allow FROM
201        // subqueries in the FROM clause.
202        return productVersion.compareTo("4.") >= 0;
203    }
204
205    public boolean allowsCompoundCountDistinct() {
206        return true;
207    }
208
209    @Override
210    public void quoteStringLiteral(StringBuilder buf, String s) {
211        // Go beyond Util.singleQuoteString; also quote backslash.
212        buf.append('\'');
213        String s0 = Util.replace(s, "'", "''");
214        String s1 = Util.replace(s0, "\\", "\\\\");
215        buf.append(s1);
216        buf.append('\'');
217    }
218
219    public String generateInline(
220        List<String> columnNames,
221        List<String> columnTypes,
222        List<String[]> valueList)
223    {
224        return generateInlineGeneric(
225            columnNames, columnTypes, valueList, null, false);
226    }
227
228    @Override
229    protected String generateOrderByNulls(
230        String expr,
231        boolean ascending,
232        boolean collateNullsLast)
233    {
234        // In MYSQL, Null values are worth negative infinity.
235        if (collateNullsLast) {
236            if (ascending) {
237                return "ISNULL(" + expr + ") ASC, " + expr + " ASC";
238            } else {
239                return expr + " DESC";
240            }
241        } else {
242            if (ascending) {
243                return expr + " ASC";
244            } else {
245                return "ISNULL(" + expr + ") DESC, " + expr + " DESC";
246            }
247        }
248    }
249
250    public boolean requiresOrderByAlias() {
251        return true;
252    }
253
254    public boolean requiresHavingAlias() {
255        return true;
256    }
257
258    public boolean supportsMultiValueInExpr() {
259        return true;
260    }
261
262    private enum Scope {
263        SESSION,
264        GLOBAL
265    }
266
267    public boolean allowsRegularExpressionInWhereClause() {
268        return true;
269    }
270
271    public String generateRegularExpression(
272        String source,
273        String javaRegex)
274    {
275        try {
276            Pattern.compile(javaRegex);
277        } catch (PatternSyntaxException e) {
278            // Not a valid Java regex. Too risky to continue.
279            return null;
280        }
281
282        // We might have to use case-insensitive matching
283        final Matcher flagsMatcher = flagsPattern.matcher(javaRegex);
284        boolean caseSensitive = true;
285        if (flagsMatcher.matches()) {
286            final String flags = flagsMatcher.group(2);
287            if (flags.contains("i")) {
288                caseSensitive = false;
289            }
290        }
291        if (flagsMatcher.matches()) {
292            javaRegex =
293                javaRegex.substring(0, flagsMatcher.start(1))
294                + javaRegex.substring(flagsMatcher.end(1));
295        }
296        final Matcher escapeMatcher = escapePattern.matcher(javaRegex);
297        while (escapeMatcher.find()) {
298            javaRegex =
299                javaRegex.replace(
300                    escapeMatcher.group(1),
301                    escapeMatcher.group(2));
302        }
303        final StringBuilder sb = new StringBuilder();
304
305        // Now build the string.
306        if (caseSensitive) {
307            sb.append(source);
308        } else {
309            sb.append("UPPER(");
310            sb.append(source);
311            sb.append(")");
312        }
313        sb.append(" REGEXP ");
314        if (caseSensitive) {
315            quoteStringLiteral(sb, javaRegex);
316        } else {
317            quoteStringLiteral(sb, javaRegex.toUpperCase());
318        }
319        return sb.toString();
320    }
321}
322
323// End MySqlDialect.java