001/* 002 * (C) Copyright 2006-2016 Nuxeo SA (http://nuxeo.com/) and others. 003 * 004 * Licensed under the Apache License, Version 2.0 (the "License"); 005 * you may not use this file except in compliance with the License. 006 * You may obtain a copy of the License at 007 * 008 * http://www.apache.org/licenses/LICENSE-2.0 009 * 010 * Unless required by applicable law or agreed to in writing, software 011 * distributed under the License is distributed on an "AS IS" BASIS, 012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 013 * See the License for the specific language governing permissions and 014 * limitations under the License. 015 * 016 * Contributors: 017 * Florent Guillaume 018 */ 019package org.nuxeo.ecm.core.storage.sql.jdbc.dialect; 020 021import java.io.Serializable; 022import java.lang.reflect.Constructor; 023import java.lang.reflect.InvocationTargetException; 024import java.security.MessageDigest; 025import java.security.NoSuchAlgorithmException; 026import java.sql.Array; 027import java.sql.Connection; 028import java.sql.DatabaseMetaData; 029import java.sql.PreparedStatement; 030import java.sql.ResultSet; 031import java.sql.SQLException; 032import java.sql.Timestamp; 033import java.sql.Types; 034import java.util.ArrayList; 035import java.util.Calendar; 036import java.util.Collection; 037import java.util.Collections; 038import java.util.GregorianCalendar; 039import java.util.HashMap; 040import java.util.List; 041import java.util.Map; 042import java.util.UUID; 043import java.util.concurrent.atomic.AtomicLong; 044import java.util.regex.Pattern; 045 046import org.nuxeo.common.utils.StringUtils; 047import org.nuxeo.ecm.core.api.NuxeoException; 048import org.nuxeo.ecm.core.query.QueryParseException; 049import org.nuxeo.ecm.core.storage.FulltextDescriptor; 050import org.nuxeo.ecm.core.storage.sql.ColumnType; 051import org.nuxeo.ecm.core.storage.sql.Model; 052import org.nuxeo.ecm.core.storage.sql.RepositoryDescriptor; 053import org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger; 054import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column; 055import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database; 056import org.nuxeo.ecm.core.storage.sql.jdbc.db.Join; 057import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table; 058import org.nuxeo.runtime.api.Framework; 059 060/** 061 * A Dialect encapsulates knowledge about database-specific behavior. 062 * 063 * @author Florent Guillaume 064 */ 065public abstract class Dialect { 066 067 // change to have deterministic pseudo-UUID generation for debugging 068 public static final boolean DEBUG_UUIDS = false; 069 070 // if true then debug UUIDs (above) are actual UUIDs, not short strings 071 public static final boolean DEBUG_REAL_UUIDS = false; 072 073 // for debug 074 private final AtomicLong temporaryIdCounter = new AtomicLong(0); 075 076 /** 077 * Property used to disable NULLS LAST usage when sorting DESC. This increase performance for some dialects because 078 * they can use an index for sorting when there are no NULL value. 079 * 080 * @since 5.9 081 */ 082 public static final String NULLS_LAST_ON_DESC_PROP = "nuxeo.vcs.use-nulls-last-on-desc"; 083 084 /** 085 * Store the SQL for descending order 086 * 087 * @since 5.9 088 */ 089 protected String descending; 090 091 /** 092 * System property to override the dialect to use globally instead of the one auto-detected. It can be suffixed by 093 * "." and the database name (without spaces and as returned by the database itself) to override only for a specific 094 * database. 095 * 096 * @since 5.6 097 */ 098 public static final String DIALECT_CLASS = "nuxeo.vcs.dialect"; 099 100 public static final Map<String, Class<? extends Dialect>> DIALECTS = new HashMap<>(); 101 102 static { 103 DIALECTS.put("H2", DialectH2.class); 104 DIALECTS.put("MySQL", DialectMySQL.class); 105 DIALECTS.put("Oracle", DialectOracle.class); 106 DIALECTS.put("PostgreSQL", DialectPostgreSQL.class); 107 DIALECTS.put("Microsoft SQL Server", DialectSQLServer.class); 108 DIALECTS.put("HSQL Database Engine", DialectHSQLDB.class); 109 DIALECTS.put("Apache Derby", DialectDerby.class); 110 DIALECTS.put("DB2", DialectDB2.class); 111 } 112 113 /** 114 * Does the dialect support an scroll API 115 * 116 * @since 8.4 117 */ 118 public boolean supportsScroll() { 119 return true; 120 } 121 122 public static final class JDBCInfo { 123 public final String string; 124 125 public final int jdbcType; 126 127 public final String jdbcBaseTypeString; 128 129 public final int jdbcBaseType; 130 131 public JDBCInfo(String string, int jdbcType) { 132 this(string, jdbcType, null, 0); 133 } 134 135 public JDBCInfo(String string, int jdbcType, String jdbcBaseTypeString, int jdbcBaseType) { 136 this.string = string; 137 this.jdbcType = jdbcType; 138 this.jdbcBaseTypeString = jdbcBaseTypeString; 139 this.jdbcBaseType = jdbcBaseType; 140 } 141 } 142 143 /** Type of id when stored in the database. */ 144 public enum DialectIdType { 145 /** VARCHAR storing a UUID as a string. */ 146 VARCHAR, 147 /** Native UUID. */ 148 UUID, 149 /** Long from sequence generated by database. */ 150 SEQUENCE, 151 } 152 153 public static JDBCInfo jdbcInfo(String string, int jdbcType) { 154 return new JDBCInfo(string, jdbcType); 155 } 156 157 public static JDBCInfo jdbcInfo(String string, int length, int jdbcType) { 158 return new JDBCInfo(String.format(string, Integer.valueOf(length)), jdbcType); 159 } 160 161 public static JDBCInfo jdbcInfo(String string, int jdbcType, String jdbcBaseTypeString, int jdbcBaseType) { 162 return new JDBCInfo(string, jdbcType, jdbcBaseTypeString, jdbcBaseType); 163 } 164 165 public static JDBCInfo jdbcInfo(String string, int length, int jdbcType, String jdbcBaseTypeString, 166 int jdbcBaseType) { 167 return new JDBCInfo(String.format(string, Integer.valueOf(length)), jdbcType, 168 String.format(jdbcBaseTypeString, Integer.valueOf(length)), jdbcBaseType); 169 } 170 171 protected final boolean storesUpperCaseIdentifiers; 172 173 protected boolean fulltextDisabled; 174 175 protected boolean fulltextSearchDisabled; 176 177 protected final boolean aclOptimizationsEnabled; 178 179 /** 180 * @since 5.7 181 */ 182 protected boolean clusteringEnabled; 183 184 /** 185 * @since 5.7 186 */ 187 protected boolean softDeleteEnabled; 188 189 protected boolean proxiesEnabled; 190 191 protected final int readAclMaxSize; 192 193 /** 194 * Creates a {@code Dialect} by connecting to the datasource to check what database is used. 195 */ 196 public static Dialect createDialect(Connection connection, RepositoryDescriptor repositoryDescriptor) { 197 DatabaseMetaData metadata; 198 String databaseName; 199 try { 200 metadata = connection.getMetaData(); 201 databaseName = metadata.getDatabaseProductName(); 202 } catch (SQLException e) { 203 throw new NuxeoException(e); 204 } 205 if (databaseName.contains("/")) { 206 // DB2/LINUX, DB2/DARWIN, etc. 207 databaseName = databaseName.substring(0, databaseName.indexOf('/')); 208 } 209 String dialectClassName = Framework.getProperty(DIALECT_CLASS); 210 if (dialectClassName == null) { 211 dialectClassName = Framework.getProperty(DIALECT_CLASS + '.' + databaseName.replace(" ", "")); 212 } 213 Class<? extends Dialect> dialectClass; 214 if (dialectClassName == null) { 215 dialectClass = DIALECTS.get(databaseName); 216 if (dialectClass == null) { 217 throw new NuxeoException("Unsupported database: " + databaseName); 218 } 219 } else { 220 Class<?> klass; 221 try { 222 ClassLoader cl = Thread.currentThread().getContextClassLoader(); 223 klass = cl.loadClass(dialectClassName); 224 } catch (ClassNotFoundException e) { 225 throw new NuxeoException(e); 226 } 227 if (!Dialect.class.isAssignableFrom(klass)) { 228 throw new NuxeoException("Not a Dialect: " + dialectClassName); 229 } 230 dialectClass = (Class<? extends Dialect>) klass; 231 } 232 Constructor<? extends Dialect> ctor; 233 try { 234 ctor = dialectClass.getConstructor(DatabaseMetaData.class, RepositoryDescriptor.class); 235 } catch (ReflectiveOperationException e) { 236 throw new NuxeoException("Bad constructor signature for: " + dialectClassName, e); 237 } 238 Dialect dialect; 239 try { 240 dialect = ctor.newInstance(metadata, repositoryDescriptor); 241 } catch (InvocationTargetException e) { 242 Throwable t = e.getTargetException(); 243 if (t instanceof NuxeoException) { 244 throw (NuxeoException) t; 245 } else { 246 throw new NuxeoException(t); 247 } 248 } catch (ReflectiveOperationException e) { 249 throw new NuxeoException("Cannot construct dialect: " + dialectClassName, e); 250 } 251 return dialect; 252 } 253 254 public Dialect(DatabaseMetaData metadata, RepositoryDescriptor repositoryDescriptor) { 255 try { 256 storesUpperCaseIdentifiers = metadata.storesUpperCaseIdentifiers(); 257 } catch (SQLException e) { 258 throw new NuxeoException(e); 259 } 260 if (repositoryDescriptor == null) { 261 fulltextDisabled = true; 262 fulltextSearchDisabled = true; 263 aclOptimizationsEnabled = false; 264 readAclMaxSize = 0; 265 clusteringEnabled = false; 266 softDeleteEnabled = false; 267 proxiesEnabled = true; 268 } else { 269 FulltextDescriptor fulltextDescriptor = repositoryDescriptor.getFulltextDescriptor(); 270 fulltextDisabled = fulltextDescriptor.getFulltextDisabled(); 271 fulltextSearchDisabled = fulltextDescriptor.getFulltextSearchDisabled(); 272 aclOptimizationsEnabled = repositoryDescriptor.getAclOptimizationsEnabled(); 273 readAclMaxSize = repositoryDescriptor.getReadAclMaxSize(); 274 clusteringEnabled = repositoryDescriptor.getClusteringEnabled(); 275 softDeleteEnabled = repositoryDescriptor.getSoftDeleteEnabled(); 276 proxiesEnabled = repositoryDescriptor.getProxiesEnabled(); 277 } 278 } 279 280 /** 281 * Gets the schema to use to query metadata about existing tables. 282 */ 283 public String getConnectionSchema(Connection connection) throws SQLException { 284 return null; 285 } 286 287 /** 288 * Gets the JDBC type and string from Nuxeo's type abstraction. 289 */ 290 public abstract JDBCInfo getJDBCTypeAndString(ColumnType type); 291 292 /** 293 * Check mismatches between expected and actual JDBC types read from database introspection. 294 */ 295 public boolean isAllowedConversion(int expected, int actual, String actualName, int actualSize) { 296 return false; 297 } 298 299 /** 300 * Gets a generated id if so configured, otherwise returns null. 301 */ 302 public Serializable getGeneratedId(Connection connection) throws SQLException { 303 if (DEBUG_UUIDS) { 304 if (DEBUG_REAL_UUIDS) { 305 return String.format("00000000-0000-0000-0000-%012x", 306 Long.valueOf(temporaryIdCounter.incrementAndGet())); 307 } else { 308 return "UUID_" + temporaryIdCounter.incrementAndGet(); 309 } 310 } else { 311 return UUID.randomUUID().toString(); 312 } 313 } 314 315 /** 316 * Sets a prepared statement value that is a Nuxeo main id (usually UUID). 317 * 318 * @param ps the prepared statement 319 * @param index the parameter index in the prepared statement 320 * @param value the value to set 321 */ 322 public void setId(PreparedStatement ps, int index, Serializable value) throws SQLException { 323 ps.setObject(index, value); 324 } 325 326 /** 327 * Sets a long id (sequence) from a value that may be a String or already a Long. 328 */ 329 public void setIdLong(PreparedStatement ps, int index, Serializable value) throws SQLException { 330 long l; 331 if (value instanceof String) { 332 try { 333 l = Long.parseLong((String) value); 334 } catch (NumberFormatException e) { 335 throw new SQLException("Invalid long id: " + value); 336 } 337 } else if (value instanceof Long) { 338 l = ((Long) value).longValue(); 339 } else { 340 throw new SQLException("Unsupported class for long id, class: " + value.getClass() + " value: " + value); 341 } 342 ps.setLong(index, l); 343 } 344 345 public abstract void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column) 346 throws SQLException; 347 348 public static final String ARRAY_SEP = "|"; 349 350 protected void setToPreparedStatementString(PreparedStatement ps, int index, Serializable value, Column column) 351 throws SQLException { 352 String v; 353 ColumnType type = column.getType(); 354 if (type == ColumnType.SYSNAMEARRAY) { 355 // implementation when arrays aren't supported 356 String[] strings = (String[]) value; 357 if (strings == null) { 358 v = null; 359 } else { 360 // use initial and final separator as terminator 361 StringBuilder buf = new StringBuilder(ARRAY_SEP); 362 for (String string : strings) { 363 buf.append(string); 364 buf.append(ARRAY_SEP); 365 } 366 v = buf.toString(); 367 } 368 } else { 369 v = (String) value; 370 } 371 ps.setString(index, v); 372 } 373 374 public void setToPreparedStatementTimestamp(PreparedStatement ps, int index, Serializable value, Column column) 375 throws SQLException { 376 Timestamp ts = getTimestampFromCalendar((Calendar) value); 377 // NOTE we don't pass the Calendar as third parameter to setTimestamp(), because 378 // this causes h2, Oracle and SQL Server to write incorrect dates 379 ps.setTimestamp(index, ts); // default timezone 380 } 381 382 public Timestamp getTimestampFromCalendar(Calendar value) { 383 return value == null ? null : new Timestamp(value.getTimeInMillis()); 384 } 385 386 public Timestamp[] getTimestampFromCalendar(Serializable[] value) { 387 if (value == null) { 388 return null; 389 } 390 Timestamp[] ts = new Timestamp[value.length]; 391 for (int i = 0; i < value.length; i++) { 392 ts[i] = getTimestampFromCalendar((Calendar) value[i]); 393 } 394 return ts; 395 } 396 397 public Calendar getCalendarFromTimestamp(Timestamp value) { 398 if (value == null) { 399 return null; 400 } 401 Calendar cal = new GregorianCalendar(); // default timezone 402 cal.setTimeInMillis(value.getTime()); 403 return cal; 404 } 405 406 public Calendar[] getCalendarFromTimestamp(Timestamp[] value) { 407 if (value == null) { 408 return null; 409 } 410 Calendar[] cal = new GregorianCalendar[value.length]; 411 for (int i = 0; i < value.length; i++) { 412 cal[i] = getCalendarFromTimestamp(value[i]); 413 } 414 return cal; 415 } 416 417 public abstract Serializable getFromResultSet(ResultSet rs, int index, Column column) throws SQLException; 418 419 protected Serializable getFromResultSetString(ResultSet rs, int index, Column column) throws SQLException { 420 String string = rs.getString(index); 421 if (string == null) { 422 return null; 423 } 424 ColumnType type = column.getType(); 425 if (type == ColumnType.SYSNAMEARRAY) { 426 // implementation when arrays aren't supported 427 // an initial separator is expected 428 if (string.startsWith(ARRAY_SEP)) { 429 string = string.substring(ARRAY_SEP.length()); 430 } 431 // the final separator is dropped as split does not return final 432 // empty strings 433 return string.split(Pattern.quote(ARRAY_SEP)); 434 } else { 435 return string; 436 } 437 } 438 439 protected Serializable getFromResultSetTimestamp(ResultSet rs, int index, Column column) throws SQLException { 440 return getCalendarFromTimestamp(rs.getTimestamp(index)); 441 } 442 443 public boolean storesUpperCaseIdentifiers() { 444 return storesUpperCaseIdentifiers; 445 } 446 447 public char openQuote() { 448 return '"'; 449 } 450 451 public char closeQuote() { 452 return '"'; 453 } 454 455 public String toBooleanValueString(boolean bool) { 456 return bool ? "1" : "0"; 457 } 458 459 protected int getMaxNameSize() { 460 return 999; 461 } 462 463 protected int getMaxIndexNameSize() { 464 return getMaxNameSize(); 465 } 466 467 /* 468 * Needs to be deterministic and not change between Nuxeo EP releases. Turns "field_with_too_many_chars_for_oracle" 469 * into "FIELD_WITH_TOO_MANY_C_58557BA3". 470 */ 471 protected String makeName(String name, int maxNameSize) { 472 if (name.length() > maxNameSize) { 473 MessageDigest digest; 474 try { 475 digest = MessageDigest.getInstance("MD5"); 476 } catch (NoSuchAlgorithmException e) { 477 throw new RuntimeException(e.toString(), e); 478 } 479 byte[] bytes = name.getBytes(); 480 digest.update(bytes, 0, bytes.length); 481 name = name.substring(0, maxNameSize - 1 - 8); 482 name += '_' + toHexString(digest.digest()).substring(0, 8); 483 } 484 name = storesUpperCaseIdentifiers() ? name.toUpperCase() : name.toLowerCase(); 485 name = name.replace(':', '_'); 486 return name; 487 } 488 489 /* 490 * Used for one-time names (IDX, FK, PK), ok if algorithm changes. If too long, keeps 4 chars of the prefix and the 491 * full suffix. 492 */ 493 protected String makeName(String prefix, String string, String suffix, int maxNameSize) { 494 String name = prefix + string + suffix; 495 if (name.length() > maxNameSize) { 496 MessageDigest digest; 497 try { 498 digest = MessageDigest.getInstance("MD5"); 499 } catch (NoSuchAlgorithmException e) { 500 throw new RuntimeException(e.toString(), e); 501 } 502 byte[] bytes = (prefix + string).getBytes(); 503 digest.update(bytes, 0, bytes.length); 504 name = prefix.substring(0, 4); 505 name += '_' + toHexString(digest.digest()).substring(0, 8); 506 name += suffix; 507 } 508 name = storesUpperCaseIdentifiers() ? name.toUpperCase() : name.toLowerCase(); 509 name = name.replace(':', '_'); 510 return name; 511 } 512 513 protected static final char[] HEX_DIGITS = "0123456789ABCDEF".toCharArray(); 514 515 public static String toHexString(byte[] bytes) { 516 StringBuilder buf = new StringBuilder(2 * bytes.length); 517 for (byte b : bytes) { 518 buf.append(HEX_DIGITS[(0xF0 & b) >> 4]); 519 buf.append(HEX_DIGITS[0x0F & b]); 520 } 521 return buf.toString(); 522 } 523 524 public String getTableName(String name) { 525 return makeName(name, getMaxNameSize()); 526 } 527 528 public String getColumnName(String name) { 529 return makeName(name, getMaxNameSize()); 530 } 531 532 public String getPrimaryKeyConstraintName(String tableName) { 533 return makeName(tableName, "", "_PK", getMaxNameSize()); 534 } 535 536 public String getForeignKeyConstraintName(String tableName, String foreignColumnName, String foreignTableName) { 537 return makeName(tableName + '_', foreignColumnName + '_' + foreignTableName, "_FK", getMaxNameSize()); 538 } 539 540 public String getIndexName(String tableName, List<String> columnNames) { 541 return makeName(qualifyIndexName() ? tableName + '_' : "", String.join("_", columnNames), "_IDX", 542 getMaxIndexNameSize()); 543 } 544 545 /** 546 * Gets a CREATE INDEX statement for an index. 547 * 548 * @param indexName the index name (for fulltext) 549 * @param indexType the index type 550 * @param table the table 551 * @param columns the columns to index 552 * @param model the model 553 */ 554 public String getCreateIndexSql(String indexName, Table.IndexType indexType, Table table, List<Column> columns, 555 Model model) { 556 List<String> qcols = new ArrayList<>(columns.size()); 557 List<String> pcols = new ArrayList<>(columns.size()); 558 for (Column col : columns) { 559 qcols.add(col.getQuotedName()); 560 pcols.add(col.getPhysicalName()); 561 } 562 String quotedIndexName = openQuote() + getIndexName(table.getKey(), pcols) + closeQuote(); 563 if (indexType == Table.IndexType.FULLTEXT) { 564 return getCreateFulltextIndexSql(indexName, quotedIndexName, table, columns, model); 565 } else { 566 String unique = indexType == Table.IndexType.UNIQUE ? "UNIQUE " : ""; 567 return String.format("CREATE %sINDEX %s ON %s (%s)", unique, quotedIndexName, table.getQuotedName(), 568 String.join(", ", qcols)); 569 } 570 } 571 572 /** 573 * Specifies what columns of the fulltext table have to be indexed. 574 * 575 * @return 0 for none, 1 for the synthetic one, 2 for the individual ones 576 */ 577 public abstract int getFulltextIndexedColumns(); 578 579 /** 580 * SQL Server supports only one fulltext index. 581 */ 582 public boolean supportsMultipleFulltextIndexes() { 583 return true; 584 } 585 586 /** 587 * Does the fulltext synthetic column have to be materialized. 588 */ 589 public abstract boolean getMaterializeFulltextSyntheticColumn(); 590 591 /** 592 * Gets a CREATE INDEX statement for a fulltext index. 593 */ 594 public abstract String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table, 595 List<Column> columns, Model model); 596 597 /** 598 * Get the dialect-specific version of a fulltext query. 599 * 600 * @param query the CMIS-syntax-based fulltext query string 601 * @return the dialect native fulltext query string 602 */ 603 public abstract String getDialectFulltextQuery(String query); 604 605 /** 606 * Information needed to express fulltext search with scoring. 607 */ 608 public static class FulltextMatchInfo { 609 610 public List<Join> joins; 611 612 public String whereExpr; 613 614 public String whereExprParam; 615 616 public String scoreExpr; 617 618 public String scoreExprParam; 619 620 public String scoreAlias; 621 622 public Column scoreCol; 623 } 624 625 /** 626 * Gets the SQL information needed to do a a fulltext match, either with a direct expression in the WHERE clause, or 627 * using a join with an additional table. 628 */ 629 public abstract FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch, 630 Column mainColumn, Model model, Database database); 631 632 /** 633 * Gets the SQL fragment to add after a LIKE match to specify the escaping character. 634 * 635 * @since 7.4 636 */ 637 public String getLikeEscaping() { 638 return null; 639 } 640 641 /** 642 * Gets the SQL fragment to match a mixin type. 643 */ 644 public String getMatchMixinType(Column mixinsColumn, String mixin, boolean positive, String[] returnParam) { 645 returnParam[0] = "%" + ARRAY_SEP + mixin + ARRAY_SEP + "%"; 646 return String.format("%s %s ?", mixinsColumn.getFullQuotedName(), positive ? "LIKE" : "NOT LIKE"); 647 } 648 649 /** 650 * Indicates if dialect supports paging 651 * 652 * @return true if the dialect supports paging 653 */ 654 public boolean supportsPaging() { 655 return false; 656 } 657 658 /** 659 * Returns the SQL query with a paging clause 660 * 661 * @since 5.7 (replacing getPagingClause) 662 */ 663 public String addPagingClause(String sql, long limit, long offset) { 664 throw new UnsupportedOperationException("paging is not supported"); 665 } 666 667 /** 668 * Gets the type of a fulltext column has known by JDBC. 669 * <p> 670 * This is used for setNull. 671 */ 672 public int getFulltextType() { 673 return Types.CLOB; 674 } 675 676 /** 677 * Gets the JDBC expression setting a free value for this column type. 678 * <p> 679 * Needed for columns that need an expression around the value being set, usually for conversion (this is the case 680 * for PostgreSQL fulltext {@code TSVECTOR} columns for instance). 681 * 682 * @param type the column type 683 * @return the expression containing a free variable 684 */ 685 public String getFreeVariableSetterForType(ColumnType type) { 686 return "?"; 687 } 688 689 public String getNoColumnsInsertString(Column idColumn) { 690 return "VALUES ( )"; 691 } 692 693 public String getNullColumnString() { 694 return ""; 695 } 696 697 public String getTableTypeString(Table table) { 698 return ""; 699 } 700 701 public String getAddPrimaryKeyConstraintString(String constraintName) { 702 return String.format(" ADD CONSTRAINT %s PRIMARY KEY ", constraintName); 703 } 704 705 public String getAddForeignKeyConstraintString(String constraintName, String[] foreignKeys, String referencedTable, 706 String[] primaryKeys, boolean referencesPrimaryKey) { 707 String sql = String.format(" ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s", constraintName, 708 String.join(", ", foreignKeys), referencedTable); 709 if (!referencesPrimaryKey) { 710 sql += " (" + String.join(", ", primaryKeys) + ')'; 711 } 712 return sql; 713 } 714 715 public boolean qualifyIndexName() { 716 return true; 717 } 718 719 public boolean supportsIfExistsBeforeTableName() { 720 return false; 721 } 722 723 public boolean supportsIfExistsAfterTableName() { 724 return false; 725 } 726 727 public String getCascadeDropConstraintsString() { 728 return ""; 729 } 730 731 public boolean supportsCircularCascadeDeleteConstraints() { 732 // false for MS SQL Server 733 return true; 734 } 735 736 public String getAddColumnString() { 737 return "ADD COLUMN"; 738 } 739 740 /** 741 * Does the dialect support UPDATE t SET ... FROM t, u WHERE ... ? 742 */ 743 public abstract boolean supportsUpdateFrom(); 744 745 /** 746 * When doing an UPDATE t SET ... FROM t, u WHERE ..., does the FROM clause need to repeate the updated table (t). 747 */ 748 public abstract boolean doesUpdateFromRepeatSelf(); 749 750 /** 751 * When doing a SELECT DISTINCT that uses a ORDER BY, do the keys along which we order have to be mentioned in the 752 * DISTINCT clause? 753 */ 754 public boolean needsOrderByKeysAfterDistinct() { 755 return true; 756 } 757 758 /** 759 * Whether a derived table (subselect in a FROM statement) needs an alias. 760 */ 761 public boolean needsAliasForDerivedTable() { 762 return false; 763 } 764 765 /** 766 * Whether a GROUP BY can only be used with the original column name and not an alias. 767 */ 768 public boolean needsOriginalColumnInGroupBy() { 769 return false; 770 } 771 772 /** 773 * Whether implicit Oracle joins (instead of explicit ANSI joins) are needed. 774 */ 775 public boolean needsOracleJoins() { 776 return false; 777 } 778 779 /** 780 * The dialect need an extra SQL statement to populate a user read acl cache before running the query. 781 * 782 * @since 5.5 783 */ 784 public boolean needsPrepareUserReadAcls() { 785 return supportsReadAcl(); 786 } 787 788 /** 789 * True if the dialect need an extra NULLS LAST on DESC sort. 790 * 791 * @since 5.9 792 */ 793 public boolean needsNullsLastOnDescSort() { 794 return false; 795 } 796 797 /** 798 * When using a CLOB field in an expression, is some casting required and with what pattern? 799 * <p> 800 * Needed for Derby and H2. 801 * 802 * @param inOrderBy {@code true} if the expression is for an ORDER BY column 803 * @return a pattern for String.format with one parameter for the column name and one for the width, or {@code null} 804 * if no cast is required 805 */ 806 public String getClobCast(boolean inOrderBy) { 807 return null; 808 } 809 810 /** 811 * Gets the SQL to use to refer to a column in an expression. 812 * <p> 813 * Needed for Oracle CLOB. 814 * 815 * @param column the column used in an expression 816 * @return the SQL to use to refer to it 817 * 818 * @since 10.10 819 */ 820 public String getQuotedNameForExpression(Column column) { 821 return column.getQuotedName(); 822 } 823 824 /** 825 * Get the expression to use to cast a column to a DATE type. 826 * 827 * @return a pattern for String.format with one parameter for the column name 828 * @since 5.6 829 */ 830 public String getDateCast() { 831 return "CAST(%s AS DATE)"; 832 } 833 834 /** 835 * Casts an id column to a VARCHAR type. 836 * <p> 837 * Used for uuid/varchar joins. 838 * 839 * @return the casted expression 840 * @since 5.7 841 */ 842 public String castIdToVarchar(String expr) { 843 return expr; 844 } 845 846 /** 847 * Gets the type of id when stored in the database. 848 * 849 * @since 5.7 850 */ 851 public DialectIdType getIdType() { 852 return DialectIdType.VARCHAR; 853 } 854 855 /** 856 * Gets the expression to use to check security. 857 * 858 * @param idColumnName the quoted name of the id column to use 859 * @return an SQL expression with two parameters (principals and permissions) that is true if access is allowed 860 */ 861 public abstract String getSecurityCheckSql(String idColumnName); 862 863 /** 864 * Checks if the dialect supports an ancestors table. 865 */ 866 public boolean supportsAncestorsTable() { 867 return false; 868 } 869 870 /** 871 * Checks whether {@link #getInTreeSql(String, String)} is optimized for fast results (using an ancestors or 872 * descendants table). 873 * 874 * @since 7.10, 6.0-HF21 875 */ 876 public boolean supportsFastDescendants() { 877 return false; 878 } 879 880 /** 881 * Gets the expression to use to check tree membership. 882 * 883 * @param idColumnName the quoted name of the id column to use 884 * @param id the id, to check syntax with respect to specialized id column types 885 * @return an SQL expression with one parameters for the based id that is true if the document is under base id, or 886 * {@code null} if the query cannot match 887 */ 888 public abstract String getInTreeSql(String idColumnName, String id); 889 890 /** 891 * Does the dialect support passing ARRAY values (to stored procedures mostly). 892 * <p> 893 * If not, we'll simulate them using a string and a separator. 894 * 895 * @return true if ARRAY values are supported 896 */ 897 public boolean supportsArrays() { 898 return false; 899 } 900 901 /** 902 * Does a stored function returning an result set need to access it as a single array instead of iterating over a 903 * normal result set's rows. 904 * <p> 905 * Oracle needs this. 906 */ 907 public boolean supportsArraysReturnInsteadOfRows() { 908 return false; 909 } 910 911 /** 912 * Gets the array result as a converted array of Serializable. 913 * 914 * @since 5.9.3 915 */ 916 public Serializable[] getArrayResult(Array array) throws SQLException { 917 throw new UnsupportedOperationException(); 918 } 919 920 /** 921 * Checks if the dialect supports storing arrays of system names (for mixins for instance). 922 */ 923 public boolean supportsSysNameArray() { 924 return false; 925 } 926 927 /** 928 * Does the dialect support storing arrays in table columns. 929 * <p> 930 * 931 * @return true if ARRAY columns are supported 932 */ 933 public boolean supportsArrayColumns() { 934 return false; 935 } 936 937 /** 938 * Structured Array Subquery Abstract Class. 939 */ 940 public static abstract class ArraySubQuery { 941 protected Column arrayColumn; 942 943 protected String subQueryAlias; 944 945 public ArraySubQuery(Column arrayColumn, String subqueryAlias) { 946 this.arrayColumn = arrayColumn; 947 this.subQueryAlias = subqueryAlias; 948 } 949 950 public abstract Column getSubQueryIdColumn(); 951 952 public abstract Column getSubQueryValueColumn(); 953 954 public abstract String toSql(); 955 } 956 957 /** 958 * Gets the dialect-specific subquery for an array column. 959 */ 960 public ArraySubQuery getArraySubQuery(Column arrayColumn, String subQueryAlias) { 961 throw new QueryParseException("Array sub-query not supported"); 962 } 963 964 /** 965 * Get SQL Array Element Subscripted string. 966 */ 967 public String getArrayElementString(String arrayColumnName, int arrayElementIndex) { 968 throw new QueryParseException("Array element not supported"); 969 } 970 971 /** 972 * Gets the SQL string for an array column IN expression. 973 */ 974 public String getArrayInSql(Column arrayColumn, String cast, boolean positive, List<Serializable> params) { 975 throw new QueryParseException("Array IN not supported"); 976 } 977 978 /** 979 * Gets the SQL string for an array column LIKE expression. 980 */ 981 public String getArrayLikeSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable) { 982 throw new QueryParseException("Array LIKE not supported"); 983 } 984 985 /** 986 * Gets the SQL string for an array column ILIKE expression. 987 */ 988 public String getArrayIlikeSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable) { 989 throw new QueryParseException("Array ILIKE not supported"); 990 } 991 992 /** 993 * Factory method for creating Array objects, suitable for passing to {@link PreparedStatement#setArray}. 994 * <p> 995 * (An equivalent method is defined by JDBC4 on the {@link Connection} class.) 996 * 997 * @param type the SQL type of the elements 998 * @param elements the elements of the array 999 * @param connection the connection 1000 * @return an Array holding the elements 1001 */ 1002 public Array createArrayOf(int type, Object[] elements, Connection connection) throws SQLException { 1003 throw new SQLException("Not supported"); 1004 } 1005 1006 /** 1007 * Gets the SQL string for an UPSERT expression. 1008 * 1009 * @param columns the columns to upsert (first one being the key column) 1010 * @param values the values to upsert (first one being the key value) 1011 * @param outcolumns the columns to use in the prepared statement 1012 * @param outValues the values to use in the prepared statement 1013 * @since 10.10 1014 */ 1015 public abstract String getUpsertSql(List<Column> columns, List<Serializable> values, List<Column> outColumns, 1016 List<Serializable> outValues); 1017 1018 /** 1019 * Gets the name of the file containing the SQL statements. 1020 */ 1021 public abstract String getSQLStatementsFilename(); 1022 1023 public abstract String getTestSQLStatementsFilename(); 1024 1025 /** 1026 * Gets the properties to use with the SQL statements. 1027 */ 1028 public abstract Map<String, Serializable> getSQLStatementsProperties(Model model, Database database); 1029 1030 /** 1031 * Checks that clustering is supported. 1032 */ 1033 public boolean isClusteringSupported() { 1034 return false; 1035 } 1036 1037 /** 1038 * Does clustering fetch of invalidations ( {@link #getClusterGetInvalidations}) need a separate delete for them. 1039 */ 1040 public boolean isClusteringDeleteNeeded() { 1041 return false; 1042 } 1043 1044 /** 1045 * Gets the SQL to send an invalidation to the cluster. 1046 * 1047 * @return an SQL statement with parameters for: nodeId, id, fragments, kind 1048 */ 1049 public String getClusterInsertInvalidations() { 1050 return null; 1051 } 1052 1053 /** 1054 * Gets the SQL to query invalidations for this cluster node. 1055 * 1056 * @return an SQL statement returning a result set 1057 */ 1058 public String getClusterGetInvalidations() { 1059 return null; 1060 } 1061 1062 /** 1063 * Does the dialect support ILIKE operator 1064 */ 1065 public boolean supportsIlike() { 1066 return false; 1067 } 1068 1069 /** 1070 * Does the dialect support an optimized read security checks 1071 */ 1072 public boolean supportsReadAcl() { 1073 return false; 1074 } 1075 1076 /** 1077 * Does the dialect support SQL-99 WITH common table expressions. 1078 */ 1079 public boolean supportsWith() { 1080 return false; 1081 } 1082 1083 /** 1084 * Does the dialect have an empty string identical to NULL (Oracle). 1085 */ 1086 public boolean hasNullEmptyString() { 1087 return false; 1088 } 1089 1090 /** 1091 * Maximum number of values in a IN (?, ?, ...) statement. 1092 * <p> 1093 * Beyond this size we'll do the query in several chunks. 1094 * <p> 1095 * PostgreSQL is limited to 65535 values in a prepared statement. 1096 * <p> 1097 * Oracle is limited to 1000 expressions in a list (ORA-01795). 1098 */ 1099 public int getMaximumArgsForIn() { 1100 return 400; 1101 } 1102 1103 /** 1104 * Gets the statement to update the read acls 1105 */ 1106 public String getUpdateReadAclsSql() { 1107 return null; 1108 } 1109 1110 /** 1111 * Gets the statement to rebuild the wall read acls 1112 */ 1113 public String getRebuildReadAclsSql() { 1114 return null; 1115 } 1116 1117 /** 1118 * Gets the expression to check if access is allowed using read acls. The dialect must suppportsReadAcl. 1119 * 1120 * @param userIdCol the quoted name of the aclr_user_map user_id column to use 1121 * @return an SQL expression with one parameter (principals) that is true if access is allowed 1122 */ 1123 public String getReadAclsCheckSql(String userIdCol) { 1124 return null; 1125 } 1126 1127 /** 1128 * Gets the SQL expression to prepare the user read acls cache. This can be used to populate a table cache. 1129 * 1130 * @since 5.5 1131 * @return and SQL expression with one parameter (principals) 1132 */ 1133 public String getPrepareUserReadAclsSql() { 1134 return null; 1135 } 1136 1137 /** 1138 * Gets the sql statements to execute after the repository init (at startup). 1139 * <p> 1140 * Used for vacuum-like operations. 1141 * 1142 * @since 6.0-HF24, 7.10-HF01, 8.1 1143 */ 1144 public List<String> getStartupSqls(Model model, Database database) { 1145 return Collections.emptyList(); 1146 } 1147 1148 /** 1149 * Checks if an exception received means that a concurrent update was detected. 1150 * 1151 * @since 5.8 1152 */ 1153 public boolean isConcurrentUpdateException(Throwable t) { 1154 return false; 1155 } 1156 1157 /** 1158 * Let the dialect processes additional statements after tables creation and conditional statements. Can be used for 1159 * specific upgrade procedure. 1160 */ 1161 public void performAdditionalStatements(Connection connection) throws SQLException { 1162 } 1163 1164 /** 1165 * A query that, when executed, will make at least a round-trip to the server to check that the connection is alive. 1166 * <p> 1167 * The query should throw an error if the connection is dead. 1168 */ 1169 public String getValidationQuery() { 1170 return "SELECT 1"; 1171 } 1172 1173 /** 1174 * Gets the SQL function that returns the length of a blob, in bytes. 1175 */ 1176 public String getBlobLengthFunction() { 1177 // the SQL-standard function (PostgreSQL, MySQL) 1178 return "OCTET_LENGTH"; 1179 } 1180 1181 /** 1182 * Let the dialect perform additional statements just after the connection is opened. 1183 */ 1184 public void performPostOpenStatements(Connection connection) throws SQLException { 1185 } 1186 1187 /** 1188 * Gets additional SQL statements to execute after the CREATE TABLE when creating an identity column. 1189 * <p> 1190 * Oracle needs both a sequence and a trigger. 1191 */ 1192 public List<String> getPostCreateIdentityColumnSql(Column column) { 1193 return Collections.emptyList(); 1194 } 1195 1196 /** 1197 * Checks if an identity column is already defined as a primary key and does not need a separate index added. 1198 * <p> 1199 * MySQL defines the identity column directly as primary key. 1200 */ 1201 public boolean isIdentityAlreadyPrimary() { 1202 return false; 1203 } 1204 1205 /** 1206 * True if the dialect returns the generated key for the identity from the insert statement. 1207 * <p> 1208 * Oracle needs a separate call to CURRVAL. 1209 */ 1210 public boolean hasIdentityGeneratedKey() { 1211 return true; 1212 } 1213 1214 /** 1215 * Gets the SQL query to execute to retrieve the last generated identity key. 1216 * <p> 1217 * Oracle needs a separate call to CURRVAL. 1218 */ 1219 public String getIdentityGeneratedKeySql(Column column) { 1220 return null; 1221 } 1222 1223 /** 1224 * Gets the SQL query to get the ancestors of a set of ids. 1225 * 1226 * @return null if not available 1227 */ 1228 public String getAncestorsIdsSql() { 1229 return null; 1230 } 1231 1232 /** 1233 * Gets the SQL descending sort direction with option to sort nulls last. Use to unify database behavior. 1234 * 1235 * @return DESC or DESC NULLS LAST depending on dialects. 1236 */ 1237 public String getDescending() { 1238 if (descending == null) { 1239 if (needsNullsLastOnDescSort() 1240 && Boolean.parseBoolean(Framework.getProperty(NULLS_LAST_ON_DESC_PROP, "true"))) { 1241 descending = " DESC NULLS LAST"; 1242 } else { 1243 descending = " DESC"; 1244 } 1245 } 1246 return descending; 1247 } 1248 1249 /** 1250 * Columns ignored if we see them in existing tables. 1251 */ 1252 public List<String> getIgnoredColumns(Table table) { 1253 return Collections.emptyList(); 1254 } 1255 1256 /** 1257 * Additional column definitions for CREATE TABLE. 1258 */ 1259 public String getCustomColumnDefinition(Table table) { 1260 return null; 1261 } 1262 1263 /** 1264 * Additional things to execute after CREATE TABLE. 1265 */ 1266 public List<String> getCustomPostCreateSqls(Table table, Model model) { 1267 return Collections.emptyList(); 1268 } 1269 1270 /** 1271 * SQL to soft delete documents. SQL returned has free parameters for the array of ids and time. 1272 */ 1273 public String getSoftDeleteSql() { 1274 throw new UnsupportedOperationException("Soft deletes not supported"); 1275 } 1276 1277 /** 1278 * SQL to clean soft-delete documents. SQL returned has free parameters max and beforeTime. 1279 */ 1280 public String getSoftDeleteCleanupSql() { 1281 throw new UnsupportedOperationException("Soft deletes not supported"); 1282 } 1283 1284 /** 1285 * Return the SQL to get the columns fulltext fields 1286 * 1287 * @since 5.9.3 1288 */ 1289 public String getBinaryFulltextSql(List<String> columns) { 1290 return "SELECT " + String.join(", ", columns) + " FROM fulltext WHERE id=?"; 1291 } 1292 1293 /** 1294 * Checks if a given stored procedure exists and is identical to the passed creation SQL. 1295 * <p> 1296 * There are 3 cases to deal with, and actions to perform: 1297 * <ul> 1298 * <li>the stored procedure doesn't exist, and must be created (create the stored procedure); 1299 * <li>the stored procedure exists but is not up to date (drop the old stored procedure and re-create it); 1300 * <li>the stored procedure exists and is up to date (nothing to do). 1301 * </ul> 1302 * <p> 1303 * When there is nothing to do, {@code null} is returned. Otherwise the returned value is a list of SQL statements 1304 * to execute. Note that the SQL statements will include also INSERT statements to be executed to remember the 1305 * creation SQL itself. 1306 * 1307 * @param procName the stored procedure name 1308 * @param procCreate the creation SQL for the stored procedure 1309 * @param ddlMode the DDL mode 1310 * @param connection the connection 1311 * @param logger the logger 1312 * @param properties the statement execution properties 1313 * @return a list of SQL statements 1314 * @since 6.0-HF24, 7.10-HF01, 8.1 1315 */ 1316 public abstract List<String> checkStoredProcedure(String procName, String procCreate, String ddlMode, 1317 Connection connection, JDBCLogger logger, Map<String, Serializable> properties) throws SQLException; 1318 1319 /** 1320 * Returns the initial DDL statements to add to a DDL dump. 1321 * 1322 * @return a list of SQL statements, usually empty 1323 * @since 6.0-HF24, 7.10-HF01, 8.1 1324 */ 1325 public Collection<? extends String> getDumpStart() { 1326 return Collections.emptyList(); 1327 } 1328 1329 /** 1330 * Returns the final DDL statements to add to a DDL dump. 1331 * 1332 * @return a list of SQL statements, usually empty 1333 * @since 6.0-HF24, 7.10-HF01, 8.1 1334 */ 1335 public Collection<? extends String> getDumpStop() { 1336 return Collections.emptyList(); 1337 } 1338 1339 /** 1340 * Returns the SQL statement with proper terminator to use in a dump. 1341 * 1342 * @return the SQL statement 1343 * @since 6.0-HF24, 7.10-HF01, 8.1 1344 */ 1345 public String getSQLForDump(String sql) { 1346 return sql + ";"; 1347 } 1348 1349 /** 1350 * Does the dialect report accurate update counts for batch updates. 1351 * 1352 * @since 9.2 1353 */ 1354 public boolean supportsBatchUpdateCount() { 1355 return true; 1356 } 1357 1358 /** 1359 * Registers return parameters. 1360 * <p> 1361 * Used for Oracle "DML Returning". 1362 * 1363 * @since 10.10 1364 */ 1365 public void registerReturnParameter(PreparedStatement ps, int parameterIndex, int sqlType) throws SQLException { 1366 throw new UnsupportedOperationException(); 1367 } 1368 1369 /** 1370 * Gets return {@link ResultSet}. 1371 * <p> 1372 * Used for Oracle "DML Returning". 1373 * 1374 * @since 10.10 1375 */ 1376 public ResultSet getReturnResultSet(PreparedStatement ps) throws SQLException { 1377 throw new UnsupportedOperationException(); 1378 } 1379 1380}