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