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