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 * Gets the expression to use to check tree membership. 842 * 843 * @param idColumnName the quoted name of the id column to use 844 * @param id the id, to check syntax with respect to specialized id column types 845 * @return an SQL expression with one parameters for the based id that is true if the document is under base id, or 846 * {@code null} if the query cannot match 847 */ 848 public abstract String getInTreeSql(String idColumnName, String id); 849 850 /** 851 * Does the dialect support passing ARRAY values (to stored procedures mostly). 852 * <p> 853 * If not, we'll simulate them using a string and a separator. 854 * 855 * @return true if ARRAY values are supported 856 */ 857 public boolean supportsArrays() { 858 return false; 859 } 860 861 /** 862 * Does a stored function returning an result set need to access it as a single array instead of iterating over a 863 * normal result set's rows. 864 * <p> 865 * Oracle needs this. 866 */ 867 public boolean supportsArraysReturnInsteadOfRows() { 868 return false; 869 } 870 871 /** 872 * Gets the array result as a converted array of Serializable. 873 * 874 * @since 5.9.3 875 */ 876 public Serializable[] getArrayResult(Array array) throws SQLException { 877 throw new UnsupportedOperationException(); 878 } 879 880 /** 881 * Checks if the dialect supports storing arrays of system names (for mixins for instance). 882 */ 883 public boolean supportsSysNameArray() { 884 return false; 885 } 886 887 /** 888 * Does the dialect support storing arrays in table columns. 889 * <p> 890 * 891 * @return true if ARRAY columns are supported 892 */ 893 public boolean supportsArrayColumns() { 894 return false; 895 } 896 897 /** 898 * Structured Array Subquery Abstract Class. 899 */ 900 public static abstract class ArraySubQuery { 901 protected Column arrayColumn; 902 903 protected String subQueryAlias; 904 905 public ArraySubQuery(Column arrayColumn, String subqueryAlias) { 906 this.arrayColumn = arrayColumn; 907 this.subQueryAlias = subqueryAlias; 908 } 909 910 public abstract Column getSubQueryIdColumn(); 911 912 public abstract Column getSubQueryValueColumn(); 913 914 public abstract String toSql(); 915 } 916 917 /** 918 * Gets the dialect-specific subquery for an array column. 919 */ 920 public ArraySubQuery getArraySubQuery(Column arrayColumn, String subQueryAlias) { 921 throw new QueryParseException("Array sub-query not supported"); 922 } 923 924 /** 925 * Get SQL Array Element Subscripted string. 926 */ 927 public String getArrayElementString(String arrayColumnName, int arrayElementIndex) { 928 throw new QueryParseException("Array element not supported"); 929 } 930 931 /** 932 * Gets the SQL string for an array column IN expression. 933 */ 934 public String getArrayInSql(Column arrayColumn, String cast, boolean positive, List<Serializable> params) { 935 throw new QueryParseException("Array IN not supported"); 936 } 937 938 /** 939 * Gets the SQL string for an array column LIKE expression. 940 */ 941 public String getArrayLikeSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable) { 942 throw new QueryParseException("Array LIKE not supported"); 943 } 944 945 /** 946 * Gets the SQL string for an array column ILIKE expression. 947 */ 948 public String getArrayIlikeSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable) { 949 throw new QueryParseException("Array ILIKE not supported"); 950 } 951 952 /** 953 * Factory method for creating Array objects, suitable for passing to {@link PreparedStatement#setArray}. 954 * <p> 955 * (An equivalent method is defined by JDBC4 on the {@link Connection} class.) 956 * 957 * @param type the SQL type of the elements 958 * @param elements the elements of the array 959 * @param connection the connection 960 * @return an Array holding the elements 961 */ 962 public Array createArrayOf(int type, Object[] elements, Connection connection) throws SQLException { 963 throw new SQLException("Not supported"); 964 } 965 966 /** 967 * Gets the name of the file containing the SQL statements. 968 */ 969 public abstract String getSQLStatementsFilename(); 970 971 public abstract String getTestSQLStatementsFilename(); 972 973 /** 974 * Gets the properties to use with the SQL statements. 975 */ 976 public abstract Map<String, Serializable> getSQLStatementsProperties(Model model, Database database); 977 978 /** 979 * Checks that clustering is supported. 980 */ 981 public boolean isClusteringSupported() { 982 return false; 983 } 984 985 /** 986 * Does clustering fetch of invalidations ( {@link #getClusterGetInvalidations}) need a separate delete for them. 987 */ 988 public boolean isClusteringDeleteNeeded() { 989 return false; 990 } 991 992 /** 993 * Gets the SQL to send an invalidation to the cluster. 994 * 995 * @return an SQL statement with parameters for: nodeId, id, fragments, kind 996 */ 997 public String getClusterInsertInvalidations() { 998 return null; 999 } 1000 1001 /** 1002 * Gets the SQL to query invalidations for this cluster node. 1003 * 1004 * @return an SQL statement returning a result set 1005 */ 1006 public String getClusterGetInvalidations() { 1007 return null; 1008 } 1009 1010 /** 1011 * Does the dialect support ILIKE operator 1012 */ 1013 public boolean supportsIlike() { 1014 return false; 1015 } 1016 1017 /** 1018 * Does the dialect support an optimized read security checks 1019 */ 1020 public boolean supportsReadAcl() { 1021 return false; 1022 } 1023 1024 /** 1025 * Does the dialect support SQL-99 WITH common table expressions. 1026 */ 1027 public boolean supportsWith() { 1028 return false; 1029 } 1030 1031 /** 1032 * Does the dialect have an empty string identical to NULL (Oracle). 1033 */ 1034 public boolean hasNullEmptyString() { 1035 return false; 1036 } 1037 1038 /** 1039 * Maximum number of values in a IN (?, ?, ...) statement. 1040 * <p> 1041 * Beyond this size we'll do the query in several chunks. 1042 * <p> 1043 * PostgreSQL is limited to 65535 values in a prepared statement. 1044 * <p> 1045 * Oracle is limited to 1000 expressions in a list (ORA-01795). 1046 */ 1047 public int getMaximumArgsForIn() { 1048 return 400; 1049 } 1050 1051 /** 1052 * Gets the statement to update the read acls 1053 */ 1054 public String getUpdateReadAclsSql() { 1055 return null; 1056 } 1057 1058 /** 1059 * Gets the statement to rebuild the wall read acls 1060 */ 1061 public String getRebuildReadAclsSql() { 1062 return null; 1063 } 1064 1065 /** 1066 * Gets the expression to check if access is allowed using read acls. The dialect must suppportsReadAcl. 1067 * 1068 * @param userIdCol the quoted name of the aclr_user_map user_id column to use 1069 * @return an SQL expression with one parameter (principals) that is true if access is allowed 1070 */ 1071 public String getReadAclsCheckSql(String userIdCol) { 1072 return null; 1073 } 1074 1075 /** 1076 * Gets the SQL expression to prepare the user read acls cache. This can be used to populate a table cache. 1077 * 1078 * @since 5.5 1079 * @return and SQL expression with one parameter (principals) 1080 */ 1081 public String getPrepareUserReadAclsSql() { 1082 return null; 1083 } 1084 1085 /** 1086 * Called before a table is created, when it's been determined that it doesn't exist yet. 1087 * 1088 * @return {@code false} if the table must actually not be created 1089 */ 1090 public boolean preCreateTable(Connection connection, Table table, Model model, Database database) 1091 throws SQLException { 1092 return true; 1093 } 1094 1095 /** 1096 * Gets the sql statements to call after a table has been created. 1097 * <p> 1098 * Used for migrations/upgrades. 1099 */ 1100 public List<String> getPostCreateTableSqls(Table table, Model model, Database database) { 1101 return Collections.emptyList(); 1102 } 1103 1104 /** 1105 * Called after an existing table has been detected in the database. 1106 * <p> 1107 * Used for migrations/upgrades. 1108 */ 1109 public void existingTableDetected(Connection connection, Table table, Model model, Database database) 1110 throws SQLException { 1111 } 1112 1113 /** 1114 * Checks if an exception received means that a concurrent update was detected. 1115 * 1116 * @since 5.8 1117 */ 1118 public boolean isConcurrentUpdateException(Throwable t) { 1119 return false; 1120 } 1121 1122 /** 1123 * Let the dialect processes additional statements after tables creation and conditional statements. Can be used for 1124 * specific upgrade procedure. 1125 * 1126 * @param connection 1127 */ 1128 public void performAdditionalStatements(Connection connection) throws SQLException { 1129 } 1130 1131 /** 1132 * A query that, when executed, will make at least a round-trip to the server to check that the connection is alive. 1133 * <p> 1134 * The query should throw an error if the connection is dead. 1135 */ 1136 public String getValidationQuery() { 1137 return "SELECT 1"; 1138 } 1139 1140 /** 1141 * Gets the SQL function that returns the length of a blob, in bytes. 1142 */ 1143 public String getBlobLengthFunction() { 1144 // the SQL-standard function (PostgreSQL, MySQL) 1145 return "OCTET_LENGTH"; 1146 } 1147 1148 /** 1149 * Let the dialect perform additional statements just after the connection is opened. 1150 */ 1151 public void performPostOpenStatements(Connection connection) throws SQLException { 1152 } 1153 1154 /** 1155 * Gets additional SQL statements to execute after the CREATE TABLE when creating an identity column. 1156 * <p> 1157 * Oracle needs both a sequence and a trigger. 1158 */ 1159 public List<String> getPostCreateIdentityColumnSql(Column column) { 1160 return Collections.emptyList(); 1161 } 1162 1163 /** 1164 * Checks if an identity column is already defined as a primary key and does not need a separate index added. 1165 * <p> 1166 * MySQL defines the identity column directly as primary key. 1167 */ 1168 public boolean isIdentityAlreadyPrimary() { 1169 return false; 1170 } 1171 1172 /** 1173 * True if the dialect returns the generated key for the identity from the insert statement. 1174 * <p> 1175 * Oracle needs a separate call to CURRVAL. 1176 */ 1177 public boolean hasIdentityGeneratedKey() { 1178 return true; 1179 } 1180 1181 /** 1182 * Gets the SQL query to execute to retrieve the last generated identity key. 1183 * <p> 1184 * Oracle needs a separate call to CURRVAL. 1185 */ 1186 public String getIdentityGeneratedKeySql(Column column) { 1187 return null; 1188 } 1189 1190 /** 1191 * Gets the SQL query to get the ancestors of a set of ids. 1192 * 1193 * @return null if not available 1194 */ 1195 public String getAncestorsIdsSql() { 1196 return null; 1197 } 1198 1199 /** 1200 * Gets the SQL descending sort direction with option to sort nulls last. Use to unify database behavior. 1201 * 1202 * @return DESC or DESC NULLS LAST depending on dialects. 1203 */ 1204 public String getDescending() { 1205 if (descending == null) { 1206 if (needsNullsLastOnDescSort() 1207 && Boolean.parseBoolean(Framework.getProperty(NULLS_LAST_ON_DESC_PROP, "true"))) { 1208 descending = " DESC NULLS LAST"; 1209 } else { 1210 descending = " DESC"; 1211 } 1212 } 1213 return descending; 1214 } 1215 1216 /** 1217 * Columns ignored if we see them in existing tables. 1218 */ 1219 public List<String> getIgnoredColumns(Table table) { 1220 return Collections.emptyList(); 1221 } 1222 1223 /** 1224 * Additional column definitions for CREATE TABLE. 1225 */ 1226 public String getCustomColumnDefinition(Table table) { 1227 return null; 1228 } 1229 1230 /** 1231 * Additional things to execute after CREATE TABLE. 1232 */ 1233 public List<String> getCustomPostCreateSqls(Table table) { 1234 return Collections.emptyList(); 1235 } 1236 1237 /** 1238 * SQL to soft delete documents. SQL returned has free parameters for the array of ids and time. 1239 */ 1240 public String getSoftDeleteSql() { 1241 throw new UnsupportedOperationException("Soft deletes not supported"); 1242 } 1243 1244 /** 1245 * SQL to clean soft-delete documents. SQL returned has free parameters max and beforeTime. 1246 */ 1247 public String getSoftDeleteCleanupSql() { 1248 throw new UnsupportedOperationException("Soft deletes not supported"); 1249 } 1250 1251 /** 1252 * Return the SQL to get the columns fulltext fields 1253 * 1254 * @param columns 1255 * @since 5.9.3 1256 */ 1257 public String getBinaryFulltextSql(List<String> columns) { 1258 return "SELECT " + StringUtils.join(columns, ", ") + " FROM fulltext WHERE id=?"; 1259 } 1260 1261}