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