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