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