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