001/* 002 * (C) Copyright 2006-2017 Nuxeo (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 * Benoit Delbosc 019 */ 020package org.nuxeo.ecm.core.storage.sql.jdbc.dialect; 021 022import java.io.IOException; 023import java.io.Reader; 024import java.io.Serializable; 025import java.lang.reflect.Constructor; 026import java.lang.reflect.InvocationTargetException; 027import java.lang.reflect.Method; 028import java.sql.Array; 029import java.sql.Connection; 030import java.sql.DatabaseMetaData; 031import java.sql.PreparedStatement; 032import java.sql.ResultSet; 033import java.sql.SQLException; 034import java.sql.Statement; 035import java.sql.Types; 036import java.util.ArrayList; 037import java.util.Arrays; 038import java.util.Collections; 039import java.util.HashMap; 040import java.util.HashSet; 041import java.util.LinkedList; 042import java.util.List; 043import java.util.Locale; 044import java.util.Map; 045import java.util.Set; 046 047import javax.transaction.xa.XAException; 048 049import org.apache.commons.lang3.ArrayUtils; 050import org.apache.commons.lang3.StringUtils; 051import org.apache.commons.logging.Log; 052import org.apache.commons.logging.LogFactory; 053import org.nuxeo.ecm.core.NXCore; 054import org.nuxeo.ecm.core.api.NuxeoException; 055import org.nuxeo.ecm.core.api.repository.FulltextConfiguration; 056import org.nuxeo.ecm.core.api.security.SecurityConstants; 057import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer; 058import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer.FulltextQuery; 059import org.nuxeo.ecm.core.storage.sql.ColumnType; 060import org.nuxeo.ecm.core.storage.sql.Model; 061import org.nuxeo.ecm.core.storage.sql.RepositoryDescriptor; 062import org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger; 063import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column; 064import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database; 065import org.nuxeo.ecm.core.storage.sql.jdbc.db.Join; 066import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table; 067import org.nuxeo.runtime.datasource.ConnectionHelper; 068 069/** 070 * Oracle-specific dialect. 071 * 072 * @author Florent Guillaume 073 */ 074public class DialectOracle extends Dialect { 075 076 private static final Log log = LogFactory.getLog(DialectOracle.class); 077 078 private Constructor<?> arrayDescriptorConstructor; 079 080 private Constructor<?> arrayConstructor; 081 082 private Method arrayGetLongArrayMethod; 083 084 protected Class<PreparedStatement> oraclePreparedStatementClass; 085 086 protected Method oraclePreparedStatementRegisterReturnParameter; 087 088 protected Method oraclePreparedStatementGetReturnResultSet; 089 090 protected final String fulltextParameters; 091 092 protected boolean pathOptimizationsEnabled; 093 094 protected int pathOptimizationsVersion = 0; 095 096 private static final String DEFAULT_USERS_SEPARATOR = "|"; 097 098 protected String usersSeparator; 099 100 protected final DialectIdType idType; 101 102 protected String idSequenceName; 103 104 protected int majorVersion; 105 106 protected XAErrorLogger xaErrorLogger; 107 108 protected static class XAErrorLogger { 109 110 protected final Class<?> oracleXAExceptionClass; 111 112 protected final Method m_xaError; 113 114 protected final Method m_xaErrorMessage; 115 116 protected final Method m_oracleError; 117 118 protected final Method m_oracleSQLError; 119 120 public XAErrorLogger() throws ReflectiveOperationException { 121 oracleXAExceptionClass = Thread.currentThread() 122 .getContextClassLoader() 123 .loadClass("oracle.jdbc.xa.OracleXAException"); 124 m_xaError = oracleXAExceptionClass.getMethod("getXAError"); 125 m_xaErrorMessage = oracleXAExceptionClass.getMethod("getXAErrorMessage", m_xaError.getReturnType()); 126 m_oracleError = oracleXAExceptionClass.getMethod("getOracleError"); 127 m_oracleSQLError = oracleXAExceptionClass.getMethod("getOracleSQLError"); 128 } 129 130 public void log(XAException e) throws ReflectiveOperationException { 131 int xaError = ((Integer) m_xaError.invoke(e)).intValue(); 132 String xaErrorMessage = (String) m_xaErrorMessage.invoke(xaError); 133 int oracleError = ((Integer) m_oracleError.invoke(e)).intValue(); 134 int oracleSQLError = ((Integer) m_oracleSQLError.invoke(e)).intValue(); 135 StringBuilder builder = new StringBuilder(); 136 builder.append("Oracle XA Error : ").append(xaError).append(" (").append(xaErrorMessage).append("),"); 137 builder.append("Oracle Error : ").append(oracleError).append(","); 138 builder.append("Oracle SQL Error : ").append(oracleSQLError); 139 log.warn(builder.toString(), e); 140 } 141 142 } 143 144 public DialectOracle(DatabaseMetaData metadata, RepositoryDescriptor repositoryDescriptor) { 145 super(metadata, repositoryDescriptor); 146 try { 147 majorVersion = metadata.getDatabaseMajorVersion(); 148 } catch (SQLException e) { 149 throw new NuxeoException(e); 150 } 151 fulltextParameters = repositoryDescriptor == null ? null 152 : repositoryDescriptor.getFulltextAnalyzer() == null ? "" : repositoryDescriptor.getFulltextAnalyzer(); 153 pathOptimizationsEnabled = repositoryDescriptor != null && repositoryDescriptor.getPathOptimizationsEnabled(); 154 if (pathOptimizationsEnabled) { 155 pathOptimizationsVersion = repositoryDescriptor.getPathOptimizationsVersion(); 156 } 157 usersSeparator = repositoryDescriptor == null ? null 158 : repositoryDescriptor.usersSeparatorKey == null ? DEFAULT_USERS_SEPARATOR 159 : repositoryDescriptor.usersSeparatorKey; 160 String idt = repositoryDescriptor == null ? null : repositoryDescriptor.idType; 161 if (idt == null || "".equals(idt) || "varchar".equalsIgnoreCase(idt)) { 162 idType = DialectIdType.VARCHAR; 163 } else if (idt.toLowerCase().startsWith("sequence")) { 164 idType = DialectIdType.SEQUENCE; 165 if (idt.toLowerCase().startsWith("sequence:")) { 166 String[] split = idt.split(":"); 167 idSequenceName = split[1].toUpperCase(Locale.ENGLISH); 168 } else { 169 idSequenceName = "HIERARCHY_SEQ"; 170 } 171 } else { 172 throw new NuxeoException("Unknown id type: '" + idt + "'"); 173 } 174 xaErrorLogger = newXAErrorLogger(); 175 initReflection(); 176 } 177 178 protected XAErrorLogger newXAErrorLogger() { 179 try { 180 return new XAErrorLogger(); 181 } catch (ReflectiveOperationException e) { 182 log.warn("Cannot initialize xa error loggger", e); 183 return null; 184 } 185 } 186 187 // use reflection to avoid linking dependencies 188 private void initReflection() { 189 try { 190 Class<?> arrayDescriptorClass = Class.forName("oracle.sql.ArrayDescriptor"); 191 arrayDescriptorConstructor = arrayDescriptorClass.getConstructor(String.class, Connection.class); 192 Class<?> arrayClass = Class.forName("oracle.sql.ARRAY"); 193 arrayConstructor = arrayClass.getConstructor(arrayDescriptorClass, Connection.class, Object.class); 194 arrayGetLongArrayMethod = arrayClass.getDeclaredMethod("getLongArray"); 195 196 Class<PreparedStatement> opsClass = (Class<PreparedStatement>) Class.forName( 197 "oracle.jdbc.OraclePreparedStatement"); 198 oraclePreparedStatementClass = opsClass; 199 oraclePreparedStatementRegisterReturnParameter = opsClass.getMethod("registerReturnParameter", int.class, int.class); 200 oraclePreparedStatementGetReturnResultSet = opsClass.getMethod("getReturnResultSet"); 201 } catch (ClassNotFoundException e) { 202 // query syntax unit test run without Oracle JDBC driver 203 return; 204 } catch (ReflectiveOperationException e) { 205 throw new NuxeoException(e); 206 } 207 } 208 209 @SuppressWarnings({ "boxing", "resource" }) 210 @Override 211 public void registerReturnParameter(PreparedStatement ps, int parameterIndex, int sqlType) throws SQLException { 212 PreparedStatement ops = ps.unwrap(oraclePreparedStatementClass); 213 try { 214 oraclePreparedStatementRegisterReturnParameter.invoke(ops, parameterIndex, sqlType); 215 } catch (ReflectiveOperationException e) { 216 throw new NuxeoException(e); 217 } 218 } 219 220 @SuppressWarnings("resource") 221 @Override 222 public ResultSet getReturnResultSet(PreparedStatement ps) throws SQLException { 223 PreparedStatement ops = ps.unwrap(oraclePreparedStatementClass); 224 try { 225 return (ResultSet) oraclePreparedStatementGetReturnResultSet.invoke(ops); 226 } catch (ReflectiveOperationException e) { 227 throw new NuxeoException(e); 228 } 229 } 230 231 @Override 232 public String getNoColumnsInsertString(Column idColumn) { 233 // INSERT INTO foo () VALUES () or DEFAULT VALUES is not legal for Oracle, you need at least one column 234 return String.format("(%s) VALUES (DEFAULT)", idColumn.getQuotedName()); 235 } 236 237 @Override 238 public String getConnectionSchema(Connection connection) throws SQLException { 239 String user; 240 try (Statement st = connection.createStatement()) { 241 String sql = "SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL"; 242 log.trace("SQL: " + sql); 243 try (ResultSet rs = st.executeQuery(sql)) { 244 rs.next(); 245 user = rs.getString(1); 246 } 247 } 248 log.trace("SQL: -> " + user); 249 return user; 250 } 251 252 @Override 253 public String getCascadeDropConstraintsString() { 254 return " CASCADE CONSTRAINTS"; 255 } 256 257 @Override 258 public String getAddColumnString() { 259 return "ADD"; 260 } 261 262 @Override 263 public JDBCInfo getJDBCTypeAndString(ColumnType type) { 264 switch (type.spec) { 265 case STRING: 266 if (type.isUnconstrained()) { 267 return jdbcInfo("NVARCHAR2(2000)", Types.VARCHAR); 268 } else if (type.isClob() || type.length > 2000) { 269 return jdbcInfo("NCLOB", Types.CLOB); 270 } else { 271 return jdbcInfo("NVARCHAR2(%d)", type.length, Types.VARCHAR); 272 } 273 case BOOLEAN: 274 return jdbcInfo("NUMBER(1,0)", Types.BIT); 275 case LONG: 276 return jdbcInfo("NUMBER(19,0)", Types.BIGINT); 277 case DOUBLE: 278 return jdbcInfo("DOUBLE PRECISION", Types.DOUBLE); 279 case TIMESTAMP: 280 return jdbcInfo("TIMESTAMP", Types.TIMESTAMP); 281 case BLOBID: 282 return jdbcInfo("VARCHAR2(250)", Types.VARCHAR); 283 case BLOB: 284 return jdbcInfo("BLOB", Types.BLOB); 285 // ----- 286 case NODEID: 287 case NODEIDFK: 288 case NODEIDFKNP: 289 case NODEIDFKMUL: 290 case NODEIDFKNULL: 291 case NODEIDPK: 292 case NODEVAL: 293 switch (idType) { 294 case VARCHAR: 295 return jdbcInfo("VARCHAR2(36)", Types.VARCHAR); 296 case SEQUENCE: 297 return jdbcInfo("NUMBER(10,0)", Types.INTEGER); 298 default: 299 } 300 throw new AssertionError("Unknown id type: " + idType); 301 case SYSNAME: 302 case SYSNAMEARRAY: 303 return jdbcInfo("VARCHAR2(250)", Types.VARCHAR); 304 case TINYINT: 305 return jdbcInfo("NUMBER(3,0)", Types.TINYINT); 306 case INTEGER: 307 return jdbcInfo("NUMBER(10,0)", Types.INTEGER); 308 case AUTOINC: 309 return jdbcInfo("NUMBER(10,0)", Types.INTEGER); 310 case FTINDEXED: 311 return jdbcInfo("CLOB", Types.CLOB); 312 case FTSTORED: 313 return jdbcInfo("NCLOB", Types.CLOB); 314 case CLUSTERNODE: 315 return jdbcInfo("VARCHAR(25)", Types.VARCHAR); 316 case CLUSTERFRAGS: 317 return jdbcInfo("VARCHAR2(4000)", Types.VARCHAR); 318 } 319 throw new AssertionError(type); 320 } 321 322 @Override 323 public boolean isAllowedConversion(int expected, int actual, String actualName, int actualSize) { 324 // Oracle internal conversions 325 if (expected == Types.DOUBLE && actual == Types.FLOAT) { 326 return true; 327 } 328 if (expected == Types.VARCHAR && actual == Types.NVARCHAR) { 329 return true; 330 } 331 if (expected == Types.VARCHAR && actual == Types.OTHER && actualName.equals("NVARCHAR2")) { 332 return true; 333 } 334 if (expected == Types.CLOB && actual == Types.NCLOB) { 335 return true; 336 } 337 if (expected == Types.CLOB && actual == Types.OTHER && actualName.equals("NCLOB")) { 338 return true; 339 } 340 if (expected == Types.BIT && (actual == Types.DECIMAL || actual == Types.NUMERIC) && actualSize == 1) { 341 return true; 342 } 343 if (expected == Types.TINYINT && (actual == Types.DECIMAL || actual == Types.NUMERIC) && actualSize == 3) { 344 return true; 345 } 346 if (expected == Types.INTEGER && (actual == Types.DECIMAL || actual == Types.NUMERIC) && actualSize == 10) { 347 return true; 348 } 349 if (expected == Types.BIGINT && (actual == Types.DECIMAL || actual == Types.NUMERIC) && actualSize == 19) { 350 return true; 351 } 352 if (expected == Types.BIGINT && (actual == Types.DECIMAL || actual == Types.NUMERIC) && actualSize == 38) { 353 return true; 354 } 355 // CLOB vs VARCHAR compatibility 356 if (expected == Types.VARCHAR && actual == Types.NCLOB) { 357 return true; 358 } 359 if (expected == Types.VARCHAR && actual == Types.OTHER && actualName.equals("NCLOB")) { 360 return true; 361 } 362 if (expected == Types.CLOB && actual == Types.VARCHAR) { 363 return true; 364 } 365 if (expected == Types.CLOB && actual == Types.NVARCHAR) { 366 return true; 367 } 368 if (expected == Types.CLOB && actual == Types.OTHER && actualName.equals("NVARCHAR2")) { 369 return true; 370 } 371 return false; 372 } 373 374 @Override 375 public Serializable getGeneratedId(Connection connection) throws SQLException { 376 if (idType != DialectIdType.SEQUENCE) { 377 return super.getGeneratedId(connection); 378 } 379 String sql = String.format("SELECT %s.NEXTVAL FROM DUAL", idSequenceName); 380 try (Statement s = connection.createStatement()) { 381 try (ResultSet rs = s.executeQuery(sql)) { 382 rs.next(); 383 return Long.valueOf(rs.getLong(1)); 384 } 385 } 386 } 387 388 @Override 389 public void setId(PreparedStatement ps, int index, Serializable value) throws SQLException { 390 switch (idType) { 391 case VARCHAR: 392 ps.setObject(index, value); 393 break; 394 case SEQUENCE: 395 setIdLong(ps, index, value); 396 break; 397 default: 398 throw new AssertionError("Unknown id type: " + idType); 399 } 400 } 401 402 @Override 403 public void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column) 404 throws SQLException { 405 switch (column.getJdbcType()) { 406 case Types.VARCHAR: 407 case Types.CLOB: 408 setToPreparedStatementString(ps, index, value, column); 409 return; 410 case Types.BIT: 411 ps.setBoolean(index, ((Boolean) value).booleanValue()); 412 return; 413 case Types.TINYINT: 414 case Types.SMALLINT: 415 ps.setInt(index, ((Long) value).intValue()); 416 return; 417 case Types.INTEGER: 418 case Types.BIGINT: 419 ps.setLong(index, ((Number) value).longValue()); 420 return; 421 case Types.DOUBLE: 422 ps.setDouble(index, ((Double) value).doubleValue()); 423 return; 424 case Types.TIMESTAMP: 425 setToPreparedStatementTimestamp(ps, index, value, column); 426 return; 427 case Types.BLOB: 428 ps.setBytes(index, (byte[]) value); 429 return; 430 case Types.OTHER: 431 ColumnType type = column.getType(); 432 if (type.isId()) { 433 setId(ps, index, value); 434 return; 435 } else if (type == ColumnType.FTSTORED) { 436 ps.setString(index, (String) value); 437 return; 438 } 439 throw new SQLException("Unhandled type: " + column.getType()); 440 default: 441 throw new SQLException("Unhandled JDBC type: " + column.getJdbcType()); 442 } 443 } 444 445 @Override 446 @SuppressWarnings("boxing") 447 public Serializable getFromResultSet(ResultSet rs, int index, Column column) throws SQLException { 448 switch (column.getJdbcType()) { 449 case Types.VARCHAR: 450 return getFromResultSetString(rs, index, column); 451 case Types.CLOB: 452 // Oracle cannot read CLOBs using rs.getString when the ResultSet is 453 // a ScrollableResultSet (the standard OracleResultSetImpl works 454 // fine). 455 Reader r = rs.getCharacterStream(index); 456 if (r == null) { 457 return null; 458 } 459 StringBuilder sb = new StringBuilder(); 460 try { 461 int n; 462 char[] buffer = new char[4096]; 463 while ((n = r.read(buffer)) != -1) { 464 sb.append(new String(buffer, 0, n)); 465 } 466 } catch (IOException e) { 467 log.error("Cannot read CLOB", e); 468 } 469 return sb.toString(); 470 case Types.BIT: 471 return rs.getBoolean(index); 472 case Types.TINYINT: 473 case Types.SMALLINT: 474 case Types.INTEGER: 475 case Types.BIGINT: 476 return rs.getLong(index); 477 case Types.DOUBLE: 478 return rs.getDouble(index); 479 case Types.TIMESTAMP: 480 return getFromResultSetTimestamp(rs, index, column); 481 case Types.BLOB: 482 return rs.getBytes(index); 483 } 484 throw new SQLException("Unhandled JDBC type: " + column.getJdbcType()); 485 } 486 487 @Override 488 protected int getMaxNameSize() { 489 return 30; 490 } 491 492 @Override 493 /* Avoid DRG-11439: index name length exceeds maximum of 25 bytes */ 494 protected int getMaxIndexNameSize() { 495 return 25; 496 } 497 498 @Override 499 public String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table, List<Column> columns, 500 Model model) { 501 return String.format( 502 "CREATE INDEX %s ON %s(%s) INDEXTYPE IS CTXSYS.CONTEXT " 503 + "PARAMETERS('%s SYNC (ON COMMIT) TRANSACTIONAL')", 504 quotedIndexName, table.getQuotedName(), columns.get(0).getQuotedName(), fulltextParameters); 505 } 506 507 protected static final String CHARS_RESERVED_STR = "%${"; 508 509 protected static final Set<Character> CHARS_RESERVED = new HashSet<>( 510 Arrays.asList(ArrayUtils.toObject(CHARS_RESERVED_STR.toCharArray()))); 511 512 @Override 513 public String getDialectFulltextQuery(String query) { 514 query = query.replace("*", "%"); // reserved, words with it not quoted 515 FulltextQuery ft = FulltextQueryAnalyzer.analyzeFulltextQuery(query); 516 if (ft == null) { 517 return "DONTMATCHANYTHINGFOREMPTYQUERY"; 518 } 519 return FulltextQueryAnalyzer.translateFulltext(ft, "OR", "AND", "NOT", "{", "}", CHARS_RESERVED, "", "", true); 520 } 521 522 // SELECT ..., (SCORE(1) / 100) AS "_nxscore" 523 // FROM ... LEFT JOIN fulltext ON fulltext.id = hierarchy.id 524 // WHERE ... AND CONTAINS(fulltext.fulltext, ?, 1) > 0 525 // ORDER BY "_nxscore" DESC 526 @Override 527 public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch, 528 Column mainColumn, Model model, Database database) { 529 String indexSuffix = model.getFulltextIndexSuffix(indexName); 530 Table ft = database.getTable(Model.FULLTEXT_TABLE_NAME); 531 Column ftMain = ft.getColumn(Model.MAIN_KEY); 532 Column ftColumn = ft.getColumn(Model.FULLTEXT_FULLTEXT_KEY + indexSuffix); 533 String score = String.format("SCORE(%d)", nthMatch); 534 String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch); 535 FulltextMatchInfo info = new FulltextMatchInfo(); 536 if (nthMatch == 1) { 537 // Need only one JOIN involving the fulltext table 538 info.joins = Collections.singletonList(new Join(Join.INNER, ft.getQuotedName(), null, null, 539 ftMain.getFullQuotedName(), mainColumn.getFullQuotedName())); 540 } 541 info.whereExpr = String.format("CONTAINS(%s, ?, %d) > 0", ftColumn.getFullQuotedName(), nthMatch); 542 info.whereExprParam = fulltextQuery; 543 info.scoreExpr = String.format("(%s / 100)", score); 544 info.scoreAlias = openQuote() + "_nxscore" + nthSuffix + closeQuote(); 545 info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null); 546 return info; 547 } 548 549 @Override 550 public boolean getMaterializeFulltextSyntheticColumn() { 551 return true; 552 } 553 554 @Override 555 public int getFulltextIndexedColumns() { 556 return 1; 557 } 558 559 @Override 560 public String getLikeEscaping() { 561 return " ESCAPE '\\'"; 562 } 563 564 @Override 565 public boolean supportsUpdateFrom() { 566 throw new UnsupportedOperationException(); 567 } 568 569 @Override 570 public boolean doesUpdateFromRepeatSelf() { 571 throw new UnsupportedOperationException(); 572 } 573 574 @Override 575 public boolean needsOriginalColumnInGroupBy() { 576 // http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2080424 577 // The alias can be used in the order_by_clause but not other clauses in 578 // the query. 579 return true; 580 } 581 582 @Override 583 public boolean needsOracleJoins() { 584 return true; 585 } 586 587 @Override 588 public String getClobCast(boolean inOrderBy) { 589 return "CAST(%s AS NVARCHAR2(%d))"; 590 } 591 592 @Override 593 public String getQuotedNameForExpression(Column column) { 594 String sql = super.getQuotedNameForExpression(column); 595 if (column.getJdbcType() == Types.CLOB) { 596 sql = "TO_CHAR(" + sql + ")"; 597 } 598 return sql; 599 } 600 601 @Override 602 public boolean supportsReadAcl() { 603 return aclOptimizationsEnabled; 604 } 605 606 @Override 607 public String getPrepareUserReadAclsSql() { 608 return "{CALL nx_prepare_user_read_acls(?)}"; 609 } 610 611 @Override 612 public String getReadAclsCheckSql(String userIdCol) { 613 return String.format("%s = nx_hash_users(?)", userIdCol); 614 } 615 616 @Override 617 public String getUpdateReadAclsSql() { 618 return "{CALL nx_update_read_acls}"; 619 } 620 621 @Override 622 public String getRebuildReadAclsSql() { 623 return "{CALL nx_rebuild_read_acls}"; 624 } 625 626 @Override 627 public String getSecurityCheckSql(String idColumnName) { 628 return String.format("NX_ACCESS_ALLOWED(%s, ?, ?) = 1", idColumnName); 629 } 630 631 @Override 632 public boolean supportsFastDescendants() { 633 return pathOptimizationsEnabled; 634 } 635 636 @Override 637 public String getInTreeSql(String idColumnName, String id) { 638 String idParam; 639 switch (idType) { 640 case VARCHAR: 641 idParam = "?"; 642 break; 643 case SEQUENCE: 644 // check that it's really an integer 645 if (id != null && !StringUtils.isNumeric(id)) { 646 return null; 647 } 648 idParam = "CAST(? AS NUMBER(10,0))"; 649 break; 650 default: 651 throw new AssertionError("Unknown id type: " + idType); 652 } 653 654 if (pathOptimizationsVersion == 2) { 655 return String.format("EXISTS(SELECT 1 FROM ancestors WHERE hierarchy_id = %s AND ancestor = %s)", 656 idColumnName, idParam); 657 } else if (pathOptimizationsVersion == 1) { 658 // using nested table optim 659 return String.format("EXISTS(SELECT 1 FROM ancestors WHERE hierarchy_id = %s AND %s MEMBER OF ancestors)", 660 idColumnName, idParam); 661 } else { 662 // no optimization 663 return String.format( 664 "%s in (SELECT id FROM hierarchy WHERE LEVEL>1 AND isproperty = 0 START WITH id = %s CONNECT BY PRIOR id = parentid)", 665 idColumnName, idParam); 666 } 667 } 668 669 @Override 670 public String getUpsertSql(List<Column> columns, List<Serializable> values, List<Column> outColumns, 671 List<Serializable> outValues) { 672 Column keyColumn = columns.get(0); 673 Serializable keyValue = values.get(0); 674 Table table = keyColumn.getTable(); 675 StringBuilder sql = new StringBuilder(); 676 sql.append("MERGE INTO "); 677 sql.append(table.getQuotedName()); 678 sql.append(" USING DUAL ON ("); 679 sql.append(keyColumn.getQuotedName()); 680 sql.append(" = ?)"); 681 outColumns.add(keyColumn); 682 outValues.add(keyValue); 683 sql.append(" WHEN MATCHED THEN UPDATE SET "); 684 for (int i = 1; i < columns.size(); i++) { 685 if (i != 1) { 686 sql.append(", "); 687 } 688 sql.append(columns.get(i).getQuotedName()); 689 sql.append(" = ?"); 690 outColumns.add(columns.get(i)); 691 outValues.add(values.get(i)); 692 } 693 sql.append(" WHEN NOT MATCHED THEN INSERT ("); 694 for (int i = 0; i < columns.size(); i++) { 695 if (i != 0) { 696 sql.append(", "); 697 } 698 sql.append(columns.get(i).getQuotedName()); 699 } 700 sql.append(") VALUES ("); 701 for (int i = 0; i < columns.size(); i++) { 702 if (i != 0) { 703 sql.append(", "); 704 } 705 sql.append("?"); 706 outColumns.add(columns.get(i)); 707 outValues.add(values.get(i)); 708 } 709 sql.append(")"); 710 return sql.toString(); 711 } 712 713 @Override 714 public boolean isClusteringSupported() { 715 return true; 716 } 717 718 /* 719 * For Oracle we don't use a function to return values and delete them at the same time, because pipelined functions 720 * that need to do DML have to do it in an autonomous transaction which could cause consistency issues. 721 */ 722 @Override 723 public boolean isClusteringDeleteNeeded() { 724 return true; 725 } 726 727 @Override 728 public String getClusterInsertInvalidations() { 729 return "{CALL NX_CLUSTER_INVAL(?, ?, ?, ?)}"; 730 } 731 732 @Override 733 public String getClusterGetInvalidations() { 734 return "SELECT id, fragments, kind FROM cluster_invals WHERE nodeid = ?"; 735 } 736 737 @Override 738 public boolean supportsPaging() { 739 return true; 740 } 741 742 @Override 743 public String addPagingClause(String sql, long limit, long offset) { 744 return String.format( 745 "SELECT * FROM (SELECT /*+ FIRST_ROWS(%d) */ a.*, ROWNUM rnum FROM (%s) a WHERE ROWNUM <= %d) WHERE rnum > %d", 746 limit, sql, limit + offset, offset); 747 } 748 749 @Override 750 public boolean supportsWith() { 751 return false; 752 } 753 754 @Override 755 public boolean supportsArrays() { 756 return true; 757 } 758 759 @Override 760 public boolean supportsArraysReturnInsteadOfRows() { 761 return true; 762 } 763 764 @Override 765 public Serializable[] getArrayResult(Array array) throws SQLException { 766 Serializable[] ids; 767 if (array.getBaseType() == Types.NUMERIC) { 768 long[] longs; 769 try { 770 longs = (long[]) arrayGetLongArrayMethod.invoke(array); 771 } catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) { 772 throw new RuntimeException(e); 773 } 774 ids = new Serializable[longs.length]; 775 for (int i = 0; i < ids.length; i++) { 776 ids[i] = Long.valueOf(longs[i]); 777 } 778 } else { 779 ids = (Serializable[]) array.getArray(); 780 } 781 return ids; 782 } 783 784 @Override 785 public boolean hasNullEmptyString() { 786 return true; 787 } 788 789 @Override 790 public Array createArrayOf(int type, Object[] elements, Connection connection) throws SQLException { 791 if (elements == null || elements.length == 0) { 792 return null; 793 } 794 String typeName; 795 switch (type) { 796 case Types.VARCHAR: 797 typeName = "NX_STRING_TABLE"; 798 break; 799 case Types.OTHER: // id 800 switch (idType) { 801 case VARCHAR: 802 typeName = "NX_STRING_TABLE"; 803 break; 804 case SEQUENCE: 805 typeName = "NX_INT_TABLE"; 806 break; 807 default: 808 throw new AssertionError("Unknown id type: " + idType); 809 } 810 break; 811 default: 812 throw new AssertionError("Unknown type: " + type); 813 } 814 connection = ConnectionHelper.unwrap(connection); 815 try { 816 Object arrayDescriptor = arrayDescriptorConstructor.newInstance(typeName, connection); 817 return (Array) arrayConstructor.newInstance(arrayDescriptor, connection, elements); 818 } catch (ReflectiveOperationException e) { 819 throw new SQLException(e); 820 } 821 } 822 823 @Override 824 public String getSQLStatementsFilename() { 825 return "nuxeovcs/oracle.sql.txt"; 826 } 827 828 @Override 829 public String getTestSQLStatementsFilename() { 830 return "nuxeovcs/oracle.test.sql.txt"; 831 } 832 833 @Override 834 public Map<String, Serializable> getSQLStatementsProperties(Model model, Database database) { 835 Map<String, Serializable> properties = new HashMap<>(); 836 switch (idType) { 837 case VARCHAR: 838 properties.put("idType", "VARCHAR2(36)"); 839 properties.put("idTypeParam", "VARCHAR2"); 840 properties.put("idArrayType", "NX_STRING_TABLE"); 841 properties.put("idNotPresent", "'-'"); 842 properties.put("sequenceEnabled", Boolean.FALSE); 843 break; 844 case SEQUENCE: 845 properties.put("idType", "NUMBER(10,0)"); 846 properties.put("idTypeParam", "NUMBER"); 847 properties.put("idArrayType", "NX_INT_TABLE"); 848 properties.put("idNotPresent", "-1"); 849 properties.put("sequenceEnabled", Boolean.TRUE); 850 properties.put("idSequenceName", idSequenceName); 851 break; 852 default: 853 throw new AssertionError("Unknown id type: " + idType); 854 } 855 properties.put("aclOptimizationsEnabled", Boolean.valueOf(aclOptimizationsEnabled)); 856 properties.put("pathOptimizationsEnabled", Boolean.valueOf(pathOptimizationsEnabled)); 857 properties.put("pathOptimizationsVersion1", pathOptimizationsVersion == 1); 858 properties.put("pathOptimizationsVersion2", pathOptimizationsVersion == 2); 859 properties.put("fulltextEnabled", Boolean.valueOf(!fulltextDisabled)); 860 properties.put("fulltextSearchEnabled", Boolean.valueOf(!fulltextSearchDisabled)); 861 properties.put("clusteringEnabled", Boolean.valueOf(clusteringEnabled)); 862 properties.put("proxiesEnabled", Boolean.valueOf(proxiesEnabled)); 863 properties.put("softDeleteEnabled", Boolean.valueOf(softDeleteEnabled)); 864 if (!fulltextSearchDisabled) { 865 Table ft = database.getTable(Model.FULLTEXT_TABLE_NAME); 866 properties.put("fulltextTable", ft.getQuotedName()); 867 FulltextConfiguration fti = model.getFulltextConfiguration(); 868 List<String> lines = new ArrayList<>(fti.indexNames.size()); 869 for (String indexName : fti.indexNames) { 870 String suffix = model.getFulltextIndexSuffix(indexName); 871 Column ftft = ft.getColumn(Model.FULLTEXT_FULLTEXT_KEY + suffix); 872 Column ftst = ft.getColumn(Model.FULLTEXT_SIMPLETEXT_KEY + suffix); 873 Column ftbt = ft.getColumn(Model.FULLTEXT_BINARYTEXT_KEY + suffix); 874 String line = String.format(" :NEW.%s := :NEW.%s || ' ' || :NEW.%s; ", ftft.getQuotedName(), 875 ftst.getQuotedName(), ftbt.getQuotedName()); 876 lines.add(line); 877 } 878 properties.put("fulltextTriggerStatements", String.join("\n", lines)); 879 } 880 String[] permissions = NXCore.getSecurityService().getPermissionsToCheck(SecurityConstants.BROWSE); 881 List<String> permsList = new LinkedList<>(); 882 for (String perm : permissions) { 883 permsList.add(String.format(" INTO ACLR_PERMISSION VALUES ('%s')", perm)); 884 } 885 properties.put("readPermissions", String.join("\n", permsList)); 886 properties.put("usersSeparator", getUsersSeparator()); 887 properties.put("everyone", SecurityConstants.EVERYONE); 888 return properties; 889 } 890 891 protected int getOracleErrorCode(Throwable t) { 892 try { 893 Method m = t.getClass().getMethod("getOracleError"); 894 Integer oracleError = (Integer) m.invoke(t); 895 if (oracleError != null) { 896 int errorCode = oracleError.intValue(); 897 if (errorCode != 0) { 898 return errorCode; 899 } 900 } 901 } catch (ReflectiveOperationException e) { 902 // ignore 903 } 904 if (t instanceof SQLException) { 905 return ((SQLException) t).getErrorCode(); 906 } 907 return 0; 908 } 909 910 protected boolean isConnectionClosed(int oracleError) { 911 switch (oracleError) { 912 case 28: // your session has been killed. 913 case 1033: // Oracle initialization or shudown in progress. 914 case 1034: // Oracle not available 915 case 1041: // internal error. hostdef extension doesn't exist 916 case 1089: // immediate shutdown in progress - no operations are permitted 917 case 1090: // shutdown in progress - connection is not permitted 918 case 3113: // end-of-file on communication channel 919 case 3114: // not connected to ORACLE 920 case 12571: // TNS:packet writer failure 921 case 17002: // IO Exception 922 case 17008: // Closed Connection 923 case 17410: // No more data to read from socket 924 case 24768: // commit protocol error occured in the server 925 return true; 926 } 927 return false; 928 } 929 930 @Override 931 public boolean isConcurrentUpdateException(Throwable t) { 932 // recent versions of the Oracle JDBC driver throw a SQLException 933 // whose cause, an OracleDatabaseException, is not itself a SQLException 934 Throwable cause; 935 while ((cause = t.getCause()) != null && cause instanceof SQLException) { 936 t = cause; 937 } 938 switch (getOracleErrorCode(t)) { 939 case 1: // ORA-00001: unique constraint violated 940 case 60: // ORA-00060: deadlock detected while waiting for resource 941 case 1403: // ORA-01403: no data found (for MERGE statements) 942 case 2291: // ORA-02291: integrity constraint ... violated - parent key not found 943 return true; 944 } 945 return false; 946 } 947 948 @Override 949 public String getValidationQuery() { 950 return "SELECT 1 FROM DUAL"; 951 } 952 953 @Override 954 public String getBlobLengthFunction() { 955 return "LENGTHB"; 956 } 957 958 @Override 959 public List<String> getPostCreateIdentityColumnSql(Column column) { 960 String table = column.getTable().getPhysicalName(); 961 String col = column.getPhysicalName(); 962 String seq = table + "_IDSEQ"; 963 String trig = table + "_IDTRIG"; 964 String createSeq = String.format("CREATE SEQUENCE \"%s\"", seq); 965 String createTrig = String.format("CREATE TRIGGER \"%s\"\n" // 966 + " BEFORE INSERT ON \"%s\"\n" // 967 + " FOR EACH ROW WHEN (NEW.\"%s\" IS NULL)\n" // 968 + "BEGIN\n" // 969 + " SELECT \"%s\".NEXTVAL INTO :NEW.\"%s\" FROM DUAL;\n" // 970 + "END;", trig, table, col, seq, col); 971 return Arrays.asList(createSeq, createTrig); 972 } 973 974 @Override 975 public boolean hasIdentityGeneratedKey() { 976 return false; 977 } 978 979 @Override 980 public String getIdentityGeneratedKeySql(Column column) { 981 String table = column.getTable().getPhysicalName(); 982 String seq = table + "_IDSEQ"; 983 return String.format("SELECT \"%s\".CURRVAL FROM DUAL", seq); 984 } 985 986 @Override 987 public String getAncestorsIdsSql() { 988 return "SELECT NX_ANCESTORS(?) FROM DUAL"; 989 } 990 991 @Override 992 public boolean needsNullsLastOnDescSort() { 993 return true; 994 } 995 996 @Override 997 public String getDateCast() { 998 // CAST(%s AS DATE) doesn't work, it doesn't compare exactly to DATE 999 // literals because the internal representation seems to be a float and 1000 // CAST AS DATE does not truncate it 1001 return "TRUNC(%s)"; 1002 } 1003 1004 @Override 1005 public String castIdToVarchar(String expr) { 1006 switch (idType) { 1007 case VARCHAR: 1008 return expr; 1009 case SEQUENCE: 1010 return "CAST(" + expr + " AS VARCHAR2(36))"; 1011 default: 1012 throw new AssertionError("Unknown id type: " + idType); 1013 } 1014 } 1015 1016 @Override 1017 public DialectIdType getIdType() { 1018 return idType; 1019 } 1020 1021 public String getUsersSeparator() { 1022 if (usersSeparator == null) { 1023 return DEFAULT_USERS_SEPARATOR; 1024 } 1025 return usersSeparator; 1026 } 1027 1028 @Override 1029 public String getSoftDeleteSql() { 1030 return "{CALL NX_DELETE(?, ?)}"; 1031 } 1032 1033 @Override 1034 public String getSoftDeleteCleanupSql() { 1035 return "{CALL NX_DELETE_PURGE(?, ?, ?)}"; 1036 } 1037 1038 @Override 1039 public List<String> getStartupSqls(Model model, Database database) { 1040 if (aclOptimizationsEnabled) { 1041 log.info("Vacuuming tables used by optimized acls"); 1042 return Collections.singletonList("{CALL nx_vacuum_read_acls}"); 1043 } 1044 return Collections.emptyList(); 1045 } 1046 1047 @Override 1048 public List<String> checkStoredProcedure(String procName, String procCreate, String ddlMode, Connection connection, 1049 JDBCLogger logger, Map<String, Serializable> properties) throws SQLException { 1050 boolean compatCheck = ddlMode.contains(RepositoryDescriptor.DDL_MODE_COMPAT); 1051 if (compatCheck) { 1052 procCreate = "CREATE OR REPLACE " + procCreate.substring("create ".length()); 1053 return Collections.singletonList(procCreate); 1054 } 1055 try (Statement st = connection.createStatement()) { 1056 String getBody; 1057 if (procCreate.toLowerCase().startsWith("create trigger ")) { 1058 getBody = "SELECT TRIGGER_BODY FROM USER_TRIGGERS WHERE TRIGGER_NAME = '" + procName + "'"; 1059 } else { 1060 // works for TYPE, FUNCTION, PROCEDURE 1061 getBody = "SELECT TEXT FROM ALL_SOURCE WHERE NAME = '" + procName + "' ORDER BY LINE"; 1062 } 1063 logger.log(getBody); 1064 try (ResultSet rs = st.executeQuery(getBody)) { 1065 if (rs.next()) { 1066 List<String> lines = new ArrayList<>(); 1067 do { 1068 lines.add(rs.getString(1)); 1069 } while (rs.next()); 1070 String body = StringUtils.join(lines, ' '); 1071 if (normalizeString(procCreate).contains(normalizeString(body))) { 1072 logger.log(" -> exists, unchanged"); 1073 return Collections.emptyList(); 1074 } else { 1075 logger.log(" -> exists, old"); 1076 if (!procCreate.toLowerCase().startsWith("create ")) { 1077 throw new NuxeoException("Should start with CREATE: " + procCreate); 1078 } 1079 procCreate = "CREATE OR REPLACE " + procCreate.substring("create ".length()); 1080 return Collections.singletonList(procCreate); 1081 } 1082 } else { 1083 logger.log(" -> missing"); 1084 return Collections.singletonList(procCreate); 1085 } 1086 } 1087 } 1088 } 1089 1090 protected static String normalizeString(String string) { 1091 return string.replaceAll("[ \n\r\t]+", " ").trim(); 1092 } 1093 1094 @Override 1095 public String getSQLForDump(String sql) { 1096 String sqll = sql.toLowerCase(); 1097 if (sqll.startsWith("{call ")) { 1098 // transform something used for JDBC calls into a proper SQL*Plus dump 1099 return "EXECUTE " + sql.substring("{call ".length(), sql.length() - 1); // without ; or / 1100 } 1101 if (sqll.endsWith("end")) { 1102 sql += ";"; 1103 } 1104 return sql + "\n/"; 1105 } 1106 1107 @Override 1108 public boolean supportsBatchUpdateCount() { 1109 // Oracle 11 1110 // https://docs.oracle.com/cd/E18283_01/java.112/e16548/oraperf.htm#i1057545 1111 // For a prepared statement batch, it is not possible to know the number of rows affected in the database by 1112 // each individual statement in the batch. Therefore, all array elements have a value of -2. According to the 1113 // JDBC 2.0 specification, a value of -2 indicates that the operation was successful but the number of rows 1114 // affected is unknown 1115 // 1116 // Oracle 12 1117 // https://docs.oracle.com/database/121/JJDBC/oraperf.htm#JJDBC28773 1118 // For a prepared statement batch, the array contains the actual update counts indicating the number of rows 1119 // affected by each operation. 1120 return majorVersion >= 12; 1121 } 1122 1123}