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