001/* 002 * Copyright (c) 2006-2011 Nuxeo SA (http://nuxeo.com/) and others. 003 * 004 * All rights reserved. This program and the accompanying materials 005 * are made available under the terms of the Eclipse Public License v1.0 006 * which accompanies this distribution, and is available at 007 * http://www.eclipse.org/legal/epl-v10.html 008 * 009 * Contributors: 010 * Florent Guillaume 011 */ 012 013package org.nuxeo.ecm.core.storage.sql.jdbc.dialect; 014 015import java.io.Serializable; 016import java.sql.DatabaseMetaData; 017import java.sql.PreparedStatement; 018import java.sql.ResultSet; 019import java.sql.SQLException; 020import java.sql.Types; 021import java.util.ArrayList; 022import java.util.Collections; 023import java.util.HashMap; 024import java.util.List; 025import java.util.Map; 026 027import org.nuxeo.common.utils.StringUtils; 028import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer; 029import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer.FulltextQuery; 030import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer.Op; 031import org.nuxeo.ecm.core.storage.sql.ColumnType; 032import org.nuxeo.ecm.core.storage.sql.Model; 033import org.nuxeo.ecm.core.storage.sql.RepositoryDescriptor; 034import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column; 035import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database; 036import org.nuxeo.ecm.core.storage.sql.jdbc.db.Join; 037import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table; 038 039/** 040 * MySQL-specific dialect. 041 * 042 * @author Florent Guillaume 043 */ 044public class DialectMySQL extends Dialect { 045 046 public DialectMySQL(DatabaseMetaData metadata, RepositoryDescriptor repositoryDescriptor) { 047 super(metadata, repositoryDescriptor); 048 } 049 050 @Override 051 public char openQuote() { 052 return '`'; 053 } 054 055 @Override 056 public char closeQuote() { 057 return '`'; 058 } 059 060 @Override 061 public String getAddForeignKeyConstraintString(String constraintName, String[] foreignKeys, String referencedTable, 062 String[] primaryKeys, boolean referencesPrimaryKey) { 063 String cols = StringUtils.join(foreignKeys, ", "); 064 String sql = String.format(" ADD INDEX %s (%s), ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s)", 065 constraintName, cols, constraintName, cols, referencedTable, StringUtils.join(primaryKeys, ", ")); 066 return sql; 067 } 068 069 @Override 070 public boolean qualifyIndexName() { 071 return false; 072 } 073 074 @Override 075 public boolean supportsIfExistsBeforeTableName() { 076 return true; 077 } 078 079 @Override 080 public JDBCInfo getJDBCTypeAndString(ColumnType type) { 081 switch (type.spec) { 082 case STRING: 083 if (type.isUnconstrained()) { 084 // don't use the max 65535 because this max is actually for the 085 // total size of all columns of a given table, so allow several 086 // varchar columns in the same table 087 // 255 is max for a column to be primary key in UTF8 088 return jdbcInfo("VARCHAR(255)", Types.VARCHAR); 089 } else if (type.isClob() || type.length > 65535) { 090 return jdbcInfo("LONGTEXT", Types.LONGVARCHAR); 091 } else { 092 return jdbcInfo("VARCHAR(%d)", type.length, Types.VARCHAR); 093 } 094 case BOOLEAN: 095 return jdbcInfo("BIT", Types.BIT); 096 case LONG: 097 return jdbcInfo("BIGINT", Types.BIGINT); 098 case DOUBLE: 099 return jdbcInfo("DOUBLE", Types.DOUBLE); 100 case TIMESTAMP: 101 return jdbcInfo("DATETIME", Types.TIMESTAMP); 102 case BLOBID: 103 return jdbcInfo("VARCHAR(250) BINARY", Types.VARCHAR); 104 // ----- 105 case NODEID: 106 case NODEIDFK: 107 case NODEIDFKNP: 108 case NODEIDFKMUL: 109 case NODEIDFKNULL: 110 case NODEIDPK: 111 case NODEVAL: 112 return jdbcInfo("VARCHAR(36) BINARY", Types.VARCHAR); 113 case SYSNAME: 114 case SYSNAMEARRAY: 115 // 255 is max for a column to have an index in UTF8 116 return jdbcInfo("VARCHAR(255) BINARY", Types.VARCHAR); 117 case TINYINT: 118 return jdbcInfo("TINYINT", Types.TINYINT); 119 case INTEGER: 120 return jdbcInfo("INTEGER", Types.INTEGER); 121 case AUTOINC: 122 return jdbcInfo("INTEGER AUTO_INCREMENT PRIMARY KEY", Types.INTEGER); 123 case FTINDEXED: 124 throw new AssertionError(type); 125 case FTSTORED: 126 return jdbcInfo("LONGTEXT", Types.LONGVARCHAR); 127 case CLUSTERNODE: 128 return jdbcInfo("BIGINT", Types.BIGINT); 129 case CLUSTERFRAGS: 130 return jdbcInfo("TEXT", Types.VARCHAR); 131 } 132 throw new AssertionError(type); 133 } 134 135 @Override 136 public boolean isAllowedConversion(int expected, int actual, String actualName, int actualSize) { 137 // LONGVARCHAR vs VARCHAR compatibility 138 if (expected == Types.VARCHAR && actual == Types.LONGVARCHAR) { 139 return true; 140 } 141 if (expected == Types.LONGVARCHAR && actual == Types.VARCHAR) { 142 return true; 143 } 144 // INTEGER vs BIGINT compatibility 145 if (expected == Types.BIGINT && actual == Types.INTEGER) { 146 return true; 147 } 148 if (expected == Types.INTEGER && actual == Types.BIGINT) { 149 return true; 150 } 151 return false; 152 } 153 154 @Override 155 public void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column) 156 throws SQLException { 157 switch (column.getJdbcType()) { 158 case Types.VARCHAR: 159 case Types.LONGVARCHAR: 160 setToPreparedStatementString(ps, index, value, column); 161 return; 162 case Types.BIT: 163 ps.setBoolean(index, ((Boolean) value).booleanValue()); 164 return; 165 case Types.TINYINT: 166 case Types.INTEGER: 167 case Types.BIGINT: 168 ps.setLong(index, ((Number) value).longValue()); 169 return; 170 case Types.DOUBLE: 171 ps.setDouble(index, ((Double) value).doubleValue()); 172 return; 173 case Types.TIMESTAMP: 174 setToPreparedStatementTimestamp(ps, index, value, column); 175 return; 176 default: 177 throw new SQLException("Unhandled JDBC type: " + column.getJdbcType()); 178 } 179 } 180 181 @Override 182 @SuppressWarnings("boxing") 183 public Serializable getFromResultSet(ResultSet rs, int index, Column column) throws SQLException { 184 switch (column.getJdbcType()) { 185 case Types.VARCHAR: 186 case Types.LONGVARCHAR: 187 return getFromResultSetString(rs, index, column); 188 case Types.BIT: 189 return rs.getBoolean(index); 190 case Types.TINYINT: 191 case Types.INTEGER: 192 case Types.BIGINT: 193 return rs.getLong(index); 194 case Types.DOUBLE: 195 return rs.getDouble(index); 196 case Types.TIMESTAMP: 197 return getFromResultSetTimestamp(rs, index, column); 198 } 199 throw new SQLException("Unhandled JDBC type: " + column.getJdbcType()); 200 } 201 202 @Override 203 protected int getMaxNameSize() { 204 return 64; 205 } 206 207 @Override 208 public String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table, 209 List<Column> columns, Model model) { 210 List<String> columnNames = new ArrayList<String>(columns.size()); 211 for (Column col : columns) { 212 columnNames.add(col.getQuotedName()); 213 } 214 return String.format("CREATE FULLTEXT INDEX %s ON %s (%s)", quotedIndexName, table.getQuotedName(), 215 StringUtils.join(columnNames, ", ")); 216 } 217 218 @Override 219 public String getDialectFulltextQuery(String query) { 220 query = query.replace("%", "*"); 221 FulltextQuery ft = FulltextQueryAnalyzer.analyzeFulltextQuery(query); 222 if (ft == null || ft.op == Op.NOTWORD) { 223 return "DONTMATCHANYTHINGFOREMPTYQUERY"; 224 } 225 StringBuilder buf = new StringBuilder(); 226 translateForMySQL(ft, null, buf); 227 return buf.toString(); 228 } 229 230 protected static void translateForMySQL(FulltextQuery ft, Op superOp, StringBuilder buf) { 231 if (ft.op == Op.AND || ft.op == Op.OR) { 232 if (superOp == Op.AND) { 233 buf.append('+'); 234 } 235 buf.append('('); 236 for (int i = 0; i < ft.terms.size(); i++) { 237 FulltextQuery term = ft.terms.get(i); 238 if (i != 0) { 239 buf.append(' '); 240 } 241 translateForMySQL(term, ft.op, buf); 242 } 243 buf.append(')'); 244 return; 245 } else { 246 if (ft.op == Op.NOTWORD) { 247 buf.append('-'); 248 } else { // Op.WORD 249 if (superOp == Op.AND) { 250 buf.append('+'); 251 } 252 } 253 boolean isPhrase = ft.word.contains(" "); 254 if (isPhrase) { 255 buf.append('"'); 256 } 257 buf.append(ft.word); 258 if (isPhrase) { 259 buf.append('"'); 260 } 261 } 262 } 263 264 // SELECT ..., (MATCH(`fulltext`.`simpletext`, `fulltext`.`binarytext`) 265 // .................. AGAINST (?) / 10) AS nxscore 266 // FROM ... LEFT JOIN `fulltext` ON ``fulltext`.`id` = `hierarchy`.`id` 267 // WHERE ... AND MATCH(`fulltext`.`simpletext`, `fulltext`.`binarytext`) 268 // ................... AGAINST (? IN BOOLEAN MODE) 269 // ORDER BY nxscore DESC 270 @Override 271 public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch, 272 Column mainColumn, Model model, Database database) { 273 String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch); 274 String indexSuffix = model.getFulltextIndexSuffix(indexName); 275 Table ft = database.getTable(model.FULLTEXT_TABLE_NAME); 276 Column ftMain = ft.getColumn(model.MAIN_KEY); 277 Column stColumn = ft.getColumn(model.FULLTEXT_SIMPLETEXT_KEY + indexSuffix); 278 Column btColumn = ft.getColumn(model.FULLTEXT_BINARYTEXT_KEY + indexSuffix); 279 String match = String.format("MATCH (%s, %s)", stColumn.getFullQuotedName(), btColumn.getFullQuotedName()); 280 FulltextMatchInfo info = new FulltextMatchInfo(); 281 if (nthMatch == 1) { 282 // Need only one JOIN involving the fulltext table 283 info.joins = Collections.singletonList(new Join(Join.INNER, ft.getQuotedName(), null, null, 284 ftMain.getFullQuotedName(), mainColumn.getFullQuotedName())); 285 } 286 info.whereExpr = String.format("%s AGAINST (? IN BOOLEAN MODE)", match); 287 info.whereExprParam = fulltextQuery; 288 // Note: using the boolean query in non-boolean mode gives approximate 289 // results but it's the best we have as MySQL does not provide a score 290 // in boolean mode. 291 // Note: dividing by 10 is arbitrary, but MySQL cannot really 292 // normalize scores. 293 info.scoreExpr = String.format("(%s AGAINST (?) / 10)", match); 294 info.scoreExprParam = fulltextQuery; 295 info.scoreAlias = "_nxscore" + nthSuffix; 296 info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null); 297 return info; 298 } 299 300 @Override 301 public boolean getMaterializeFulltextSyntheticColumn() { 302 return false; 303 } 304 305 @Override 306 public int getFulltextIndexedColumns() { 307 return 2; 308 } 309 310 @Override 311 public String getTableTypeString(Table table) { 312 if (table.hasFulltextIndex()) { 313 return " ENGINE=MyISAM"; 314 } else { 315 return " ENGINE=InnoDB"; 316 } 317 } 318 319 @Override 320 public boolean supportsUpdateFrom() { 321 return true; 322 } 323 324 @Override 325 public boolean doesUpdateFromRepeatSelf() { 326 return true; 327 } 328 329 @Override 330 public boolean needsOrderByKeysAfterDistinct() { 331 return false; 332 } 333 334 @Override 335 public boolean needsAliasForDerivedTable() { 336 return true; 337 } 338 339 @Override 340 public String getSecurityCheckSql(String idColumnName) { 341 return String.format("NX_ACCESS_ALLOWED(%s, ?, ?)", idColumnName); 342 } 343 344 @Override 345 public String getInTreeSql(String idColumnName, String id) { 346 return String.format("NX_IN_TREE(%s, ?)", idColumnName); 347 } 348 349 @Override 350 public String getSQLStatementsFilename() { 351 return "nuxeovcs/mysql.sql.txt"; 352 } 353 354 @Override 355 public String getTestSQLStatementsFilename() { 356 return "nuxeovcs/mysql.test.sql.txt"; 357 } 358 359 @Override 360 public Map<String, Serializable> getSQLStatementsProperties(Model model, Database database) { 361 Map<String, Serializable> properties = new HashMap<String, Serializable>(); 362 properties.put("idType", "varchar(36)"); 363 properties.put("fulltextEnabled", Boolean.valueOf(!fulltextSearchDisabled)); 364 properties.put("clusteringEnabled", Boolean.valueOf(clusteringEnabled)); 365 return properties; 366 } 367 368 @Override 369 public boolean isConcurrentUpdateException(Throwable t) { 370 while (t.getCause() != null) { 371 t = t.getCause(); 372 } 373 if (t instanceof SQLException) { 374 String sqlState = ((SQLException) t).getSQLState(); 375 if ("23000".equals(sqlState)) { 376 // Integrity constraint violation: 1452 Cannot add or update a 377 // child row: a foreign key constraint fails 378 return true; 379 } 380 if ("40001".equals(sqlState)) { 381 // com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: 382 // Deadlock found when trying to get lock; try restarting 383 // transaction 384 return true; 385 } 386 } 387 return false; 388 } 389 390 @Override 391 public boolean isClusteringSupported() { 392 return true; 393 } 394 395 @Override 396 public boolean isClusteringDeleteNeeded() { 397 return true; 398 } 399 400 @Override 401 public String getClusterInsertInvalidations() { 402 return "CALL NX_CLUSTER_INVAL(?, ?, ?, ?)"; 403 } 404 405 @Override 406 public String getClusterGetInvalidations() { 407 return "SELECT id, fragments, kind FROM cluster_invals WHERE nodeid = ?"; 408 } 409 410 @Override 411 public boolean supportsPaging() { 412 return true; 413 } 414 415 @Override 416 public String addPagingClause(String sql, long limit, long offset) { 417 return sql + String.format(" LIMIT %d OFFSET %d", limit, offset); 418 } 419 420 @Override 421 public boolean isIdentityAlreadyPrimary() { 422 return true; 423 } 424 425 @Override 426 public String getBinaryFulltextSql(List<String> columns) { 427 return "SELECT " + StringUtils.join(columns, ", ") + " FROM `fulltext` WHERE id=?"; 428 } 429 430}