001/* 002 * (C) Copyright 2006-2019 Nuxeo (http://nuxeo.com/) and others. 003 * 004 * Licensed under the Apache License, Version 2.0 (the "License"); 005 * you may not use this file except in compliance with the License. 006 * You may obtain a copy of the License at 007 * 008 * http://www.apache.org/licenses/LICENSE-2.0 009 * 010 * Unless required by applicable law or agreed to in writing, software 011 * distributed under the License is distributed on an "AS IS" BASIS, 012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 013 * See the License for the specific language governing permissions and 014 * limitations under the License. 015 * 016 * Contributors: 017 * Florent Guillaume 018 */ 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.ecm.core.api.NuxeoException; 047import org.nuxeo.ecm.core.query.QueryParseException; 048import org.nuxeo.ecm.core.storage.FulltextDescriptor; 049import org.nuxeo.ecm.core.storage.sql.ColumnType; 050import org.nuxeo.ecm.core.storage.sql.Model; 051import org.nuxeo.ecm.core.storage.sql.RepositoryDescriptor; 052import org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger; 053import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column; 054import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database; 055import org.nuxeo.ecm.core.storage.sql.jdbc.db.Join; 056import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table; 057import org.nuxeo.runtime.api.Framework; 058import org.nuxeo.runtime.cluster.ClusterService; 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("DB2", DialectDB2.class); 109 } 110 111 /** 112 * Does the dialect support an scroll API 113 * 114 * @since 8.4 115 */ 116 public boolean supportsScroll() { 117 return true; 118 } 119 120 public static final class JDBCInfo { 121 public final String string; 122 123 public final int jdbcType; 124 125 public final String jdbcBaseTypeString; 126 127 public final int jdbcBaseType; 128 129 public JDBCInfo(String string, int jdbcType) { 130 this(string, jdbcType, null, 0); 131 } 132 133 public JDBCInfo(String string, int jdbcType, String jdbcBaseTypeString, int jdbcBaseType) { 134 this.string = string; 135 this.jdbcType = jdbcType; 136 this.jdbcBaseTypeString = jdbcBaseTypeString; 137 this.jdbcBaseType = jdbcBaseType; 138 } 139 } 140 141 /** Type of id when stored in the database. */ 142 public enum DialectIdType { 143 /** VARCHAR storing a UUID as a string. */ 144 VARCHAR, 145 /** Native UUID. */ 146 UUID, 147 /** Long from sequence generated by database. */ 148 SEQUENCE, 149 } 150 151 public static JDBCInfo jdbcInfo(String string, int jdbcType) { 152 return new JDBCInfo(string, jdbcType); 153 } 154 155 public static JDBCInfo jdbcInfo(String string, int length, int jdbcType) { 156 return new JDBCInfo(String.format(string, Integer.valueOf(length)), jdbcType); 157 } 158 159 public static JDBCInfo jdbcInfo(String string, int jdbcType, String jdbcBaseTypeString, int jdbcBaseType) { 160 return new JDBCInfo(string, jdbcType, jdbcBaseTypeString, jdbcBaseType); 161 } 162 163 public static JDBCInfo jdbcInfo(String string, int length, int jdbcType, String jdbcBaseTypeString, 164 int jdbcBaseType) { 165 return new JDBCInfo(String.format(string, Integer.valueOf(length)), jdbcType, 166 String.format(jdbcBaseTypeString, Integer.valueOf(length)), jdbcBaseType); 167 } 168 169 protected final boolean storesUpperCaseIdentifiers; 170 171 protected boolean fulltextDisabled; 172 173 protected boolean fulltextSearchDisabled; 174 175 protected final boolean aclOptimizationsEnabled; 176 177 /** 178 * @since 5.7 179 */ 180 protected boolean clusteringEnabled; 181 182 /** 183 * @since 5.7 184 */ 185 protected boolean softDeleteEnabled; 186 187 protected boolean proxiesEnabled; 188 189 protected final int readAclMaxSize; 190 191 /** 192 * Creates a {@code Dialect} by connecting to the datasource to check what database is used. 193 */ 194 public static Dialect createDialect(Connection connection, RepositoryDescriptor repositoryDescriptor) { 195 DatabaseMetaData metadata; 196 String databaseName; 197 try { 198 metadata = connection.getMetaData(); 199 databaseName = metadata.getDatabaseProductName(); 200 } catch (SQLException e) { 201 throw new NuxeoException(e); 202 } 203 if (databaseName.contains("/")) { 204 // DB2/LINUX, DB2/DARWIN, etc. 205 databaseName = databaseName.substring(0, databaseName.indexOf('/')); 206 } 207 String dialectClassName = Framework.getProperty(DIALECT_CLASS); 208 if (dialectClassName == null) { 209 dialectClassName = Framework.getProperty(DIALECT_CLASS + '.' + databaseName.replace(" ", "")); 210 } 211 Class<? extends Dialect> dialectClass; 212 if (dialectClassName == null) { 213 dialectClass = DIALECTS.get(databaseName); 214 if (dialectClass == null) { 215 throw new NuxeoException("Unsupported database: " + databaseName); 216 } 217 } else { 218 Class<?> klass; 219 try { 220 ClassLoader cl = Thread.currentThread().getContextClassLoader(); 221 klass = cl.loadClass(dialectClassName); 222 } catch (ClassNotFoundException e) { 223 throw new NuxeoException(e); 224 } 225 if (!Dialect.class.isAssignableFrom(klass)) { 226 throw new NuxeoException("Not a Dialect: " + dialectClassName); 227 } 228 dialectClass = (Class<? extends Dialect>) klass; 229 } 230 Constructor<? extends Dialect> ctor; 231 try { 232 ctor = dialectClass.getConstructor(DatabaseMetaData.class, RepositoryDescriptor.class); 233 } catch (ReflectiveOperationException e) { 234 throw new NuxeoException("Bad constructor signature for: " + dialectClassName, e); 235 } 236 Dialect dialect; 237 try { 238 dialect = ctor.newInstance(metadata, repositoryDescriptor); 239 } catch (InvocationTargetException e) { 240 Throwable t = e.getTargetException(); 241 if (t instanceof NuxeoException) { 242 throw (NuxeoException) t; 243 } else { 244 throw new NuxeoException(t); 245 } 246 } catch (ReflectiveOperationException e) { 247 throw new NuxeoException("Cannot construct dialect: " + dialectClassName, e); 248 } 249 return dialect; 250 } 251 252 public Dialect(DatabaseMetaData metadata, RepositoryDescriptor repositoryDescriptor) { 253 try { 254 storesUpperCaseIdentifiers = metadata.storesUpperCaseIdentifiers(); 255 } catch (SQLException e) { 256 throw new NuxeoException(e); 257 } 258 if (repositoryDescriptor == null) { 259 fulltextDisabled = true; 260 fulltextSearchDisabled = true; 261 aclOptimizationsEnabled = false; 262 readAclMaxSize = 0; 263 clusteringEnabled = false; 264 softDeleteEnabled = false; 265 proxiesEnabled = true; 266 } else { 267 FulltextDescriptor fulltextDescriptor = repositoryDescriptor.getFulltextDescriptor(); 268 fulltextDisabled = fulltextDescriptor.getFulltextDisabled(); 269 fulltextSearchDisabled = fulltextDescriptor.getFulltextSearchDisabled(); 270 aclOptimizationsEnabled = repositoryDescriptor.getAclOptimizationsEnabled(); 271 readAclMaxSize = repositoryDescriptor.getReadAclMaxSize(); 272 if (Framework.isInitialized()) { 273 clusteringEnabled = Framework.getService(ClusterService.class).isEnabled(); 274 } 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 sb = new StringBuilder(ARRAY_SEP); 362 for (String string : strings) { 363 sb.append(string); 364 sb.append(ARRAY_SEP); 365 } 366 v = sb.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 sb = new StringBuilder(2 * bytes.length); 517 for (byte b : bytes) { 518 sb.append(HEX_DIGITS[(0xF0 & b) >> 4]); 519 sb.append(HEX_DIGITS[0x0F & b]); 520 } 521 return sb.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 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 SQL string for an INSERT ... ON CONFLICT DO NOTHING expression. 1020 * 1021 * @param columns the columns to insert (first one being the key column) 1022 * @param values the values to insert (first one being the key value) 1023 * @param outColumns the columns to use in the prepared statement 1024 * @param outValues the values to use in the prepared statement 1025 * @since 11.1 1026 */ 1027 public String getInsertOnConflictDoNothingSql(List<Column> columns, List<Serializable> values, List<Column> outColumns, 1028 List<Serializable> outValues) { 1029 return null; 1030 } 1031 1032 /** 1033 * Gets the name of the file containing the SQL statements. 1034 */ 1035 public abstract String getSQLStatementsFilename(); 1036 1037 public abstract String getTestSQLStatementsFilename(); 1038 1039 /** 1040 * Gets the properties to use with the SQL statements. 1041 */ 1042 public abstract Map<String, Serializable> getSQLStatementsProperties(Model model, Database database); 1043 1044 /** 1045 * Checks that clustering is supported. 1046 */ 1047 public boolean isClusteringSupported() { 1048 return false; 1049 } 1050 1051 /** 1052 * Does clustering fetch of invalidations ( {@link #getClusterGetInvalidations}) need a separate delete for them. 1053 */ 1054 public boolean isClusteringDeleteNeeded() { 1055 return false; 1056 } 1057 1058 /** 1059 * Gets the SQL to send an invalidation to the cluster. 1060 * 1061 * @return an SQL statement with parameters for: nodeId, id, fragments, kind 1062 */ 1063 public String getClusterInsertInvalidations() { 1064 return null; 1065 } 1066 1067 /** 1068 * Gets the SQL to query invalidations for this cluster node. 1069 * 1070 * @return an SQL statement returning a result set 1071 */ 1072 public String getClusterGetInvalidations() { 1073 return null; 1074 } 1075 1076 /** 1077 * Does the dialect support ILIKE operator 1078 */ 1079 public boolean supportsIlike() { 1080 return false; 1081 } 1082 1083 /** 1084 * Does the dialect support an optimized read security checks 1085 */ 1086 public boolean supportsReadAcl() { 1087 return false; 1088 } 1089 1090 /** 1091 * Does the dialect support SQL-99 WITH common table expressions. 1092 */ 1093 public boolean supportsWith() { 1094 return false; 1095 } 1096 1097 /** 1098 * Does the dialect have an empty string identical to NULL (Oracle). 1099 */ 1100 public boolean hasNullEmptyString() { 1101 return false; 1102 } 1103 1104 /** 1105 * Maximum number of values in a IN (?, ?, ...) statement. 1106 * <p> 1107 * Beyond this size we'll do the query in several chunks. 1108 * <p> 1109 * PostgreSQL is limited to 65535 values in a prepared statement. 1110 * <p> 1111 * Oracle is limited to 1000 expressions in a list (ORA-01795). 1112 */ 1113 public int getMaximumArgsForIn() { 1114 return 400; 1115 } 1116 1117 /** 1118 * Gets the statement to update the read acls 1119 */ 1120 public String getUpdateReadAclsSql() { 1121 return null; 1122 } 1123 1124 /** 1125 * Gets the statement to rebuild the wall read acls 1126 */ 1127 public String getRebuildReadAclsSql() { 1128 return null; 1129 } 1130 1131 /** 1132 * Gets the expression to check if access is allowed using read acls. The dialect must suppportsReadAcl. 1133 * 1134 * @param userIdCol the quoted name of the aclr_user_map user_id column to use 1135 * @return an SQL expression with one parameter (principals) that is true if access is allowed 1136 */ 1137 public String getReadAclsCheckSql(String userIdCol) { 1138 return null; 1139 } 1140 1141 /** 1142 * Gets the SQL expression to prepare the user read acls cache. This can be used to populate a table cache. 1143 * 1144 * @since 5.5 1145 * @return and SQL expression with one parameter (principals) 1146 */ 1147 public String getPrepareUserReadAclsSql() { 1148 return null; 1149 } 1150 1151 /** 1152 * Gets the sql statements to execute after the repository init (at startup). 1153 * <p> 1154 * Used for vacuum-like operations. 1155 * 1156 * @since 6.0-HF24, 7.10-HF01, 8.1 1157 */ 1158 public List<String> getStartupSqls(Model model, Database database) { 1159 return Collections.emptyList(); 1160 } 1161 1162 /** 1163 * Checks if an exception received means that a concurrent update was detected. 1164 * 1165 * @since 5.8 1166 */ 1167 public boolean isConcurrentUpdateException(Throwable t) { 1168 return false; 1169 } 1170 1171 /** 1172 * Let the dialect processes additional statements after tables creation and conditional statements. Can be used for 1173 * specific upgrade procedure. 1174 */ 1175 public void performAdditionalStatements(Connection connection) throws SQLException { 1176 } 1177 1178 /** 1179 * A query that, when executed, will make at least a round-trip to the server to check that the connection is alive. 1180 * <p> 1181 * The query should throw an error if the connection is dead. 1182 */ 1183 public String getValidationQuery() { 1184 return "SELECT 1"; 1185 } 1186 1187 /** 1188 * Gets the SQL function that returns the length of a blob, in bytes. 1189 */ 1190 public String getBlobLengthFunction() { 1191 // the SQL-standard function (PostgreSQL, MySQL) 1192 return "OCTET_LENGTH"; 1193 } 1194 1195 /** 1196 * Let the dialect perform additional statements just after the connection is opened. 1197 */ 1198 public void performPostOpenStatements(Connection connection) throws SQLException { 1199 } 1200 1201 /** 1202 * Gets additional SQL statements to execute after the CREATE TABLE when creating an identity column. 1203 * <p> 1204 * Oracle needs both a sequence and a trigger. 1205 */ 1206 public List<String> getPostCreateIdentityColumnSql(Column column) { 1207 return Collections.emptyList(); 1208 } 1209 1210 /** 1211 * Checks if an identity column is already defined as a primary key and does not need a separate index added. 1212 * <p> 1213 * MySQL defines the identity column directly as primary key. 1214 */ 1215 public boolean isIdentityAlreadyPrimary() { 1216 return false; 1217 } 1218 1219 /** 1220 * True if the dialect returns the generated key for the identity from the insert statement. 1221 * <p> 1222 * Oracle needs a separate call to CURRVAL. 1223 */ 1224 public boolean hasIdentityGeneratedKey() { 1225 return true; 1226 } 1227 1228 /** 1229 * Gets the SQL query to execute to retrieve the last generated identity key. 1230 * <p> 1231 * Oracle needs a separate call to CURRVAL. 1232 */ 1233 public String getIdentityGeneratedKeySql(Column column) { 1234 return null; 1235 } 1236 1237 /** 1238 * Gets the SQL query to get the ancestors of a set of ids. 1239 * 1240 * @return null if not available 1241 */ 1242 public String getAncestorsIdsSql() { 1243 return null; 1244 } 1245 1246 /** 1247 * Gets the SQL descending sort direction with option to sort nulls last. Use to unify database behavior. 1248 * 1249 * @return DESC or DESC NULLS LAST depending on dialects. 1250 */ 1251 public String getDescending() { 1252 if (descending == null) { 1253 if (needsNullsLastOnDescSort() 1254 && Boolean.parseBoolean(Framework.getProperty(NULLS_LAST_ON_DESC_PROP, "true"))) { 1255 descending = " DESC NULLS LAST"; 1256 } else { 1257 descending = " DESC"; 1258 } 1259 } 1260 return descending; 1261 } 1262 1263 /** 1264 * Columns ignored if we see them in existing tables. 1265 */ 1266 public List<String> getIgnoredColumns(Table table) { 1267 return Collections.emptyList(); 1268 } 1269 1270 /** 1271 * Additional column definitions for CREATE TABLE. 1272 */ 1273 public String getCustomColumnDefinition(Table table) { 1274 return null; 1275 } 1276 1277 /** 1278 * Additional things to execute after CREATE TABLE. 1279 */ 1280 public List<String> getCustomPostCreateSqls(Table table, Model model) { 1281 return Collections.emptyList(); 1282 } 1283 1284 /** 1285 * SQL to soft delete documents. SQL returned has free parameters for the array of ids and time. 1286 */ 1287 public String getSoftDeleteSql() { 1288 throw new UnsupportedOperationException("Soft deletes not supported"); 1289 } 1290 1291 /** 1292 * SQL to clean soft-delete documents. SQL returned has free parameters max and beforeTime. 1293 */ 1294 public String getSoftDeleteCleanupSql() { 1295 throw new UnsupportedOperationException("Soft deletes not supported"); 1296 } 1297 1298 /** 1299 * Return the SQL to get the columns fulltext fields 1300 * 1301 * @since 5.9.3 1302 */ 1303 public String getBinaryFulltextSql(List<String> columns) { 1304 return "SELECT " + String.join(", ", columns) + " FROM fulltext WHERE id=?"; 1305 } 1306 1307 /** 1308 * Checks if a given stored procedure exists and is identical to the passed creation SQL. 1309 * <p> 1310 * There are 3 cases to deal with, and actions to perform: 1311 * <ul> 1312 * <li>the stored procedure doesn't exist, and must be created (create the stored procedure); 1313 * <li>the stored procedure exists but is not up to date (drop the old stored procedure and re-create it); 1314 * <li>the stored procedure exists and is up to date (nothing to do). 1315 * </ul> 1316 * <p> 1317 * When there is nothing to do, {@code null} is returned. Otherwise the returned value is a list of SQL statements 1318 * to execute. Note that the SQL statements will include also INSERT statements to be executed to remember the 1319 * creation SQL itself. 1320 * 1321 * @param procName the stored procedure name 1322 * @param procCreate the creation SQL for the stored procedure 1323 * @param ddlMode the DDL mode 1324 * @param connection the connection 1325 * @param logger the logger 1326 * @param properties the statement execution properties 1327 * @return a list of SQL statements 1328 * @since 6.0-HF24, 7.10-HF01, 8.1 1329 */ 1330 public abstract List<String> checkStoredProcedure(String procName, String procCreate, String ddlMode, 1331 Connection connection, JDBCLogger logger, Map<String, Serializable> properties) throws SQLException; 1332 1333 /** 1334 * Returns the initial DDL statements to add to a DDL dump. 1335 * 1336 * @return a list of SQL statements, usually empty 1337 * @since 6.0-HF24, 7.10-HF01, 8.1 1338 */ 1339 public Collection<? extends String> getDumpStart() { 1340 return Collections.emptyList(); 1341 } 1342 1343 /** 1344 * Returns the final DDL statements to add to a DDL dump. 1345 * 1346 * @return a list of SQL statements, usually empty 1347 * @since 6.0-HF24, 7.10-HF01, 8.1 1348 */ 1349 public Collection<? extends String> getDumpStop() { 1350 return Collections.emptyList(); 1351 } 1352 1353 /** 1354 * Returns the SQL statement with proper terminator to use in a dump. 1355 * 1356 * @return the SQL statement 1357 * @since 6.0-HF24, 7.10-HF01, 8.1 1358 */ 1359 public String getSQLForDump(String sql) { 1360 return sql + ";"; 1361 } 1362 1363 /** 1364 * Does the dialect report accurate update counts for batch updates. 1365 * 1366 * @since 9.2 1367 */ 1368 public boolean supportsBatchUpdateCount() { 1369 return true; 1370 } 1371 1372 /** 1373 * Registers return parameters. 1374 * <p> 1375 * Used for Oracle "DML Returning". 1376 * 1377 * @since 10.10 1378 */ 1379 public void registerReturnParameter(PreparedStatement ps, int parameterIndex, int sqlType) throws SQLException { 1380 throw new UnsupportedOperationException(); 1381 } 1382 1383 /** 1384 * Gets return {@link ResultSet}. 1385 * <p> 1386 * Used for Oracle "DML Returning". 1387 * 1388 * @since 10.10 1389 */ 1390 public ResultSet getReturnResultSet(PreparedStatement ps) throws SQLException { 1391 throw new UnsupportedOperationException(); 1392 } 1393 1394}