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