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